Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
2018-11-データベース / 2018-11 database
Search
Cybozu
PRO
July 17, 2018
Programming
5
40k
2018-11-データベース / 2018-11 database
データベースの概要を知る。
データベースに求められる特性を知る。
SQLの概要を把握する。
自分でSQLを書けるようになる。
Cybozu
PRO
July 17, 2018
Tweet
Share
More Decks by Cybozu
See All by Cybozu
生産性向上チームの紹介
cybozuinsideout
PRO
1
870
サイボウズQAの紹介
cybozuinsideout
PRO
1
260
試験仕様書の英語化をやってみたら試験仕様書の本質が見えてきた
cybozuinsideout
PRO
0
260
販売管理オペレーターが開発チームの一員となった話
cybozuinsideout
PRO
0
250
主体的な活動で巨大な影響範囲のテストを乗りこなしていく話
cybozuinsideout
PRO
1
260
Garoon 開発チーム / Garoon development team
cybozuinsideout
PRO
2
2.9k
OSSの脆弱性との向き合い⽅
cybozuinsideout
PRO
2
76
既存プロセスからの脱却と変化に適応するために必要なこと
cybozuinsideout
PRO
2
570
スプリント内で試験を完了させるには?アジャイル・スクラム開発に参加したQAエンジニアの悩みと対策
cybozuinsideout
PRO
1
540
Other Decks in Programming
See All in Programming
PHPはいつから死んでいるかの調査
chiroruxx
1
400
Compose-View Interop in Practice (mDevCamp 2024)
stewemetal
0
130
Git Rebase
bkuhlmann
11
1.6k
SwiftUIで使いやすいToastの作り方 / How to build a Toast system which is easy to use in SwiftUI
lovee
3
140
ゆるい個人開発のススメ
kuroppe1819
10
990
What We Can Learn From OSS
inouehi
0
420
Let's learn code review
riofujimon
1
270
TYPO3 v13 – The road to LTS: What's new and new APIs
luisasofie_xoxo
0
200
Anthropic Cookbook のおすすめレシピ
schroneko
7
950
はてなにおける CSS Modules、及び CSS Modules に足りないもの / CSS Modules in Hatena, and CSS Modules missing parts
mizdra
7
930
Rubyでたのしむクリエイティブコーディング/Enjoy Creative coding with Ruby
chobishiba
1
180
#phpcon_odawara オープン・クローズドなテストフィクスチャを求めて / open closed test fixtures
77web
3
230
Featured
See All Featured
How GitHub (no longer) Works
holman
304
140k
How to train your dragon (web standard)
notwaldorf
73
5.2k
Docker and Python
trallard
34
2.7k
Automating Front-end Workflow
addyosmani
1356
200k
Fontdeck: Realign not Redesign
paulrobertlloyd
76
4.9k
Building Effective Engineering Teams - LeadDev
addyosmani
28
1.8k
Statistics for Hackers
jakevdp
789
220k
Rails Girls Zürich Keynote
gr2m
91
13k
We Have a Design System, Now What?
morganepeng
43
6.7k
The Cost Of JavaScript in 2023
addyosmani
16
3.9k
Pencils Down: Stop Designing & Start Developing
hursman
117
11k
GraphQLの誤解/rethinking-graphql
sonatard
50
9.2k
Transcript
! !
%! $'" #( &
()
EL C l
X l C N D P l AQ S C I I I AQ
& %# ( "'"$ ! '
l l l
l l / l /
!%0 ! %0
l 75) +42 l # 1-&( l ' *3 $,6. / "
A C C l
l l D I
l
23 23 23 23 23 23 51 .
0 4
0 0 0 0 0 0 2.34 1 10
! = A l ( ) A )
1 B2 A B l 5 B
l 9 0 l 9 l 9 9 5 93
9 541 54 0 12 9 541 54
. 13 9 = 0 6 2 3 53 54 0 1 2
= l ) ) ) ) l
( I
- l ) ) ) l
l l ) ( l ( l (
V PfdcK S l CD B .)
,B 4 .) B B l 4G 4 OR G .) aMN , 4D D C l eQ , 4 Rb , 4D D C aMN L B D C l ( G D B l C 4 4 GB 4 C 4 D D B l 4
)( 3 4 1 2
- 0 7
)( 2 l 0 7 - 1 l
0 43 l 2
:- 3 0 1 l 47 2 0
D D I
n l o D1B l l a
o ( ) ( B R a R i e R B 1
)( (
C ) ( ) ) l l
L ) ) ) (/ ( l D (/ ( l
E C l E C L , ( R
l E C PS ), R l E C IN R l E C ( R E C D : A :
0/. -73394 l e atQ0/.
qL QF d S c i l 6 ## 9 73394 2 :# # 1 l k 0/. s Q q p ! ! ! ! n o ! ! fhm 7 4l belong_to organization employee
- l
SELECT [] FROM [ ]; SELECT name FROM employee;
- * l * SELECT * FROM employee;
- l SELECT [ 21 ] FROM [
0 ] WHERE []; SELECT * FROM employee WHERE joined_at >= '2010/1/1';
- ] a l [ _ d 1 ./
5 75 .7 :/05 6 /6 / :/ 276 1 5 joined_at > '2007/1/1' 726/. _ ' name = '' 6 5/ _ c e id <> 10001 2. _
- iNO f ( rL ) rL !=
ds ) nI K )( ( c , tvap . ul moh q eT ./ . ul moh q eT E6 < = > 5 : : 5 : : 6
name LIKE '%%'; % ' % l l
l l l l
) ) ( % l name LIKE
'%¥%%' ( '
- l id IN (10001, 10003) l
- 41 l l l 0 2
0 SELECT * FROM employee WHERE joined_at >= '2010/1/1' AND joined_at <= '2014/1/1';
1 1
- D E l E ) E l AE E
l C C( E D l SELECT [ ] FROM [ ] ORDER BY [ ] [ASC/DESC]; SELECT * FROM employee ORDER BY joined_at ASC;
l l 2 SELECT [ ] FROM [
ORDER BY [ ] [ASC/DESC] LIMIT []; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 2; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 1, 2;
#$ % ) l "
! SELECT [&'] FROM [(] GROUP BY []; SELECT organization_id, COUNT(*) FROM belong_to GROUP BY organization_id;
) * ( * ) * ( * ) *
( * 3 4 12 GROUP BY org_id SELECT org_id, COUNT(*) 0 0 2 3 4 1 ) *
l ) ( )
15 0 I a D l 678 047 2
a l 3 66 2 l 506837 d_ 9 : l _ l g d l ed l ced grade
SELECT class_id, COUNT(*), AVG(result), MAX(result), MIN(result) FROM grade GROUP BY
class_id;
B O RO l U H E l G
P
l SELECT * FROM [ ] GROUP BY
[ 2 ] HAVING []; SELECT organization_id, COUNT(*) AS num FROM belong_to GROUP BY organization_id HAVING num >= 2;
GROUP BY org_id SELECT org_id, COUNT(*) 0 1 3 HAVING num >= 2 2 4
" $ ! #%
- 3 4 1 2
- 0 7 - 0 3 - 0 4 7 1 2
- SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON
belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;
-
belong_to organization " $%"!# … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …
- . . .
… belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …
- . . . 143 2 0 … belong_to INNER
JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;
-
SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;
- ( "!&%$ ' %#& l "!&%#&
l "!&%$ ' %#& SELECT MAX(result) FROM grade; SELECT AVG(result) FROM grade WHERE result <> [];
- 1 SELECT AVG(result) FROM grade WHERE result <>
(SELECT MAX(result) FROM grade);
- > l < l < l <
SELECT class_id, MAX(result) FROM grade GROUP BY class_id; SELECT class_id, AVG(result) FROM grade WHERE (class_id = 1 AND result <> [1 ]) OR (class_id = 2 AND result <> [2 ]) OR … GROUP BY class_id;
- 1 1 SELECT class_id, AVG(result) FROM grade
AS g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id ) GROUP BY class_id;
SELECT class_id, AVG(result) FROM grade AS g1 WHERE [] GROUP BY class_id;
6 63
8 3 9 7 0 2 1 5 4 g1 SELECT class_id, AVG(result) FROM grade AS g1 WHERE [] GROUP BY class_id;
1 g1
WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )
g1 g2 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )
.
g2 g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )
MAX(result)
g2 g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )
36 4215 0
g2 g1 WHERE g1.result <> 100
g1 1 89 B 4 3 0 2 6 753
& Q & E C L
& Q
INSERT INTO [
] ([ ]) VALUES ([]); INSERT INTO employee (id, name, joined_at) VALUES (10001, '', '2013/04/01'), (10002, '', '2014/04/01'), (10003, '', '2007/04/01');
$# " %
! & UPDATE [ ] SET [ ] = [ ] WHERE [ ]; UPDATE employee SET joined_at = '2007/4/2' WHERE id = 10003;
DELETE
FROM [] WHERE []; TRUNCATE []; DELETE FROM [];
! !
Q l e j
c i l D P E S IC A l PX A L N N N D j
3 L : 88
Q . 4 666 0 721 2 /
& h M 1 0 S d L Q
M c a .774 888 1 : 2 /4 4
U B Ca B JF 2 26
N S 3 : / / 0 4: 1: .
9Q S : a L c
d : h .55 4 0 721 2 /