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
6
40k
2018-11-データベース / 2018-11 database
データベースの概要を知る。
データベースに求められる特性を知る。
SQLの概要を把握する。
自分でSQLを書けるようになる。
Cybozu
PRO
July 17, 2018
Tweet
Share
More Decks by Cybozu
See All by Cybozu
サイボウズ 開発本部採用ピッチ / Cybozu Engineer Recruit
cybozuinsideout
PRO
9
44k
テクニカルライティング
cybozuinsideout
PRO
4
250
サイボウズのアジャイルクオリティ2024
cybozuinsideout
PRO
3
220
モブに早く慣れたい人のためのガイド2024
cybozuinsideout
PRO
3
280
モバイル
cybozuinsideout
PRO
3
150
ソフトウェアライセンス
cybozuinsideout
PRO
4
140
ソフトウェアテスト
cybozuinsideout
PRO
3
220
自動テスト
cybozuinsideout
PRO
3
230
Docker入門2024
cybozuinsideout
PRO
3
390
Other Decks in Programming
See All in Programming
Outline View in SwiftUI
1024jp
1
160
役立つログに取り組もう
irof
27
8.7k
Snowflake x dbtで作るセキュアでアジャイルなデータ基盤
tsoshiro
2
430
JaSST 24 九州:ワークショップ(は除く)実践!マインドマップを活用したソフトウェアテスト+活用事例
satohiroyuki
0
260
弊社の「意識チョット低いアーキテクチャ」10選
texmeijin
5
23k
PHP でアセンブリ言語のように書く技術
memory1994
PRO
1
150
アジャイルを支えるテストアーキテクチャ設計/Test Architecting for Agile
goyoki
7
2.8k
Amazon Neptuneで始めてみるグラフDB-OpenSearchによるグラフの全文検索-
satoshi256kbyte
4
330
Vue SFCのtemplateでTypeScriptの型を活用しよう
tsukkee
3
1.5k
NSOutlineView何もわからん:( 前編 / I Don't Understand About NSOutlineView :( Pt. 1
usagimaru
0
150
VR HMDとしてのVision Pro+ゲーム開発について
yasei_no_otoko
0
100
Tuning GraphQL on Rails
pyama86
2
1k
Featured
See All Featured
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
43
6.6k
The MySQL Ecosystem @ GitHub 2015
samlambert
250
12k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
27
4.2k
Become a Pro
speakerdeck
PRO
24
5k
Reflections from 52 weeks, 52 projects
jeffersonlam
346
20k
Designing for humans not robots
tammielis
249
25k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.2k
[RailsConf 2023] Rails as a piece of cake
palkan
51
4.9k
Fashionably flexible responsive web design (full day workshop)
malarkey
404
65k
Learning to Love Humans: Emotional Interface Design
aarron
272
40k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
167
49k
Optimising Largest Contentful Paint
csswizardry
33
2.9k
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 /