-
Notifications
You must be signed in to change notification settings - Fork 6
/
content.html
1391 lines (1388 loc) · 100 KB
/
content.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<h1 style="text-align:center">SQL教程</h1><div class="x-wiki-content x-main-content"><p>这是小白的零基础SQL教程。</p>
<p>什么是SQL？简单地说，SQL就是访问和处理关系数据库的计算机标准语言。也就是说，无论用什么编程语言（Java、Python、C++……）编写程序，只要涉及到操作关系数据库，比如，一个电商网站需要把用户和商品信息存入数据库，或者一个手机游戏需要把用户的道具、通关信息存入数据库，都必须通过SQL来完成。</p>
<p>所以，现代程序离不开关系数据库，要使用关系数据库就必须掌握SQL。</p>
<p>在本教程中，你将学到关系数据库的基本概念，如何使用SQL操作数据库，以及一种最流行的开源数据库MySQL的基本安装和使用方法。</p>
<h3>教程特色：可以在线运行！</h3>
<p>你可以在线直接输入并运行SQL，然后观察运行结果。当然，这个在线效果是通过集成了<a href="http://alasql.org/">AlaSQL</a>这个JavaScript库实现的，它并不会保存结果，刷新页面，数据库就会恢复到初始状态。</p>
<h3>NoSQL</h3>
<p>你可能还听说过NoSQL数据库，也就是非SQL的数据库，包括MongoDB、Cassandra、Dynamo等等，它们都不是关系数据库。有很多人鼓吹现代Web程序已经无需关系数据库了，只需要使用NoSQL就可以。但事实上，SQL数据库从始至终从未被取代过。回顾一下NoSQL的发展历程：</p>
<ul>
<li>1970: NoSQL = We have no SQL</li>
<li>1980: NoSQL = Know SQL</li>
<li>2000: NoSQL = No SQL!</li>
<li>2005: NoSQL = Not only SQL</li>
<li>2013: NoSQL = No, SQL!</li>
</ul>
<p>今天，SQL数据库仍然承担了各种应用程序的核心数据存储，而NoSQL数据库作为SQL数据库的补充，两者不再是二选一的问题，而是主从关系。所以，无论使用哪种编程语言，无论是Web开发、游戏开发还是手机开发，掌握SQL，是所有软件开发人员所必须的。</p>
<p>不要再犹豫了！从现在开始，坚持一周，拿下SQL！</p>
<h3>关于作者</h3>
<p><a href="http://weibo.com/liaoxuefeng">廖雪峰</a>，十年软件开发经验，业余产品经理，精通Java/Python/Ruby/Visual Basic/Objective C等，对开源框架有深入研究，著有《Spring 2.0核心技术与最佳实践》一书，多个业余开源项目托管在<a href="https://github.com/michaelliao">GitHub</a>，欢迎微博交流：</p>
<p>使用窄屏手机的童鞋，请点击左上角“目录”查看教程：</p>
<p><img alt="menu" src="./files/attachments/1311543585144897/l.jpg"/></p>
</div><h1 style="text-align:center">关系数据库概述</h1><div class="x-wiki-content x-main-content"><p>为什么需要数据库？</p>
<p>因为应用程序需要保存用户的数据，比如Word需要把用户文档保存起来，以便下次继续编辑或者拷贝到另一台电脑。</p>
<p>要保存用户的数据，一个最简单的方法是把用户数据写入文件。例如，要保存一个班级所有学生的信息，可以向文件中写入一个CSV文件：</p>
<pre><code>id,name,gender,score
1,小明,M,90
2,小红,F,95
3,小军,M,88
4,小丽,F,88
</code></pre>
<p>如果要保存学校所有班级的信息，可以写入另一个CSV文件。</p>
<p>但是，随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：</p>
<ul>
<li>读写文件并解析出数据需要大量重复代码；</li>
<li>从成千上万的数据中快速查询出指定数据需要复杂的逻辑。</li>
</ul>
<p>如果每个应用程序都各自写自己的读写数据的代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不相同，数据难以复用。</p>
<p>所以，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心：</p>
<pre><code class="language-ascii">┌──────────────┐
│ application │
└──────────────┘
▲│
││
read││write
││
│▼
┌──────────────┐
│ database │
└──────────────┘
</code></pre>
<p>这样一来，编写应用程序的时候，数据读写的功能就被大大地简化了。</p>
<h3>数据模型</h3>
<p>数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：</p>
<ul>
<li>层次模型</li>
<li>网状模型</li>
<li>关系模型</li>
</ul>
<p>层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：</p>
<pre><code class="language-ascii"> ┌─────┐
│ │
└─────┘
│
┌───────┴───────┐
│ │
┌─────┐ ┌─────┐
│ │ │ │
└─────┘ └─────┘
│ │
┌───┴───┐ ┌───┴───┐
│ │ │ │
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│ │ │ │ │ │ │ │
└─────┘ └─────┘ └─────┘ └─────┘
</code></pre>
<p>网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：</p>
<pre><code class="language-ascii"> ┌─────┐ ┌─────┐
┌─│ │──────│ │──┐
│ └─────┘ └─────┘ │
│ │ │ │
│ └──────┬─────┘ │
│ │ │
┌─────┐ ┌─────┐ ┌─────┐
│ │─────│ │─────│ │
└─────┘ └─────┘ └─────┘
│ │ │
│ ┌─────┴─────┐ │
│ │ │ │
│ ┌─────┐ ┌─────┐ │
└──│ │─────│ │──┘
└─────┘ └─────┘
</code></pre>
<p>关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：</p>
<pre><code class="language-ascii">┌─────┬─────┬─────┬─────┬─────┐
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
└─────┴─────┴─────┴─────┴─────┘
</code></pre>
<p>随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。</p>
<p>为什么关系数据库获得了最广泛的应用？</p>
<p>因为相比层次模型和网状模型，关系模型理解和使用起来最简单。</p>
<p>关系数据库的关系模型是基于数学理论建立的。我们把域（Domain）定义为一组具有相同数据类型的值的集合，给定一组域D1,D2,...,Dn，它们的笛卡尔集定义为D1×D2×……×Dn={(d1,d2,...,dn)|di∈Di,i=1,2,...,n}，
而D1×D2×……×Dn的子集叫作在域D1,D2,...,Dn上的关系，表示为R(D1,D2,...,Dn)，这里的R表示$#%&^@!&$#;!~％¥%:(……算了，根本讲不明白，大家也不用理解。</p>
<p>基于数学理论的关系模型虽然讲起来挺复杂，但是，基于日常生活的关系模型却十分容易理解。我们以学校班级为例，一个班级的学生就可以用一个表格存起来，并且定义如下：</p>
<table>
<thead>
<tr><th>ID</th><th>姓名</th><th align="right">班级ID</th><th align="right">性别</th><th align="right">年龄</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>小明</td><td align="right">201</td><td align="right">M</td><td align="right">9</td></tr>
<tr><td>2</td><td>小红</td><td align="right">202</td><td align="right">F</td><td align="right">8</td></tr>
<tr><td>3</td><td>小军</td><td align="right">202</td><td align="right">M</td><td align="right">8</td></tr>
<tr><td>4</td><td>小白</td><td align="right">201</td><td align="right">F</td><td align="right">9</td></tr>
</tbody>
</table>
<p>其中，班级ID对应着另一个班级表：</p>
<table>
<thead>
<tr><th>ID</th><th>名称</th><th>班主任</th></tr>
</thead>
<tbody>
<tr><td>201</td><td>二年级一班</td><td>王老师</td></tr>
<tr><td>202</td><td>二年级二班</td><td>李老师</td></tr>
</tbody>
</table>
<p>通过给定一个班级名称，可以查到一条班级记录，根据班级ID，又可以查到多条学生记录，这样，二维表之间就通过ID映射建立了“一对多”关系。</p>
<h3>数据类型</h3>
<p>对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：</p>
<table>
<thead>
<tr><th>名称</th><th>类型</th><th>说明</th></tr>
</thead>
<tbody>
<tr><td>INT</td><td>整型</td><td>4字节整数类型，范围约+/-21亿</td></tr>
<tr><td>BIGINT</td><td>长整型</td><td>8字节整数类型，范围约+/-922亿亿</td></tr>
<tr><td>REAL</td><td>浮点型</td><td>4字节浮点数，范围约+/-10<sup>38</sup></td></tr>
<tr><td>DOUBLE</td><td>浮点型</td><td>8字节浮点数，范围约+/-10<sup>308</sup></td></tr>
<tr><td>DECIMAL(M,N)</td><td>高精度小数</td><td>由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算</td></tr>
<tr><td>CHAR(N)</td><td>定长字符串</td><td>存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串</td></tr>
<tr><td>VARCHAR(N)</td><td>变长字符串</td><td>存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串</td></tr>
<tr><td>BOOLEAN</td><td>布尔类型</td><td>存储True或者False</td></tr>
<tr><td>DATE</td><td>日期类型</td><td>存储日期，例如，2018-06-22</td></tr>
<tr><td>TIME</td><td>时间类型</td><td>存储时间，例如，12:20:59</td></tr>
<tr><td>DATETIME</td><td>日期和时间类型</td><td>存储日期+时间，例如，2018-06-22 12:20:59</td></tr>
</tbody>
</table>
<p>上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，<code>REAL</code>又可以写成<code>FLOAT(24)</code>。还有一些不常用的数据类型，例如，<code>TINYINT</code>（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如<code>JSON</code>。</p>
<p>选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，<code>BIGINT</code>能满足整数存储的需求，<code>VARCHAR(N)</code>能满足字符串存储的需求，这两种类型是使用最广泛的。</p>
<h3>主流关系数据库</h3>
<p>目前，主流的关系数据库主要分为以下几类：</p>
<ol>
<li>商用数据库，例如：<a href="https://www.oracle.com">Oracle</a>，<a href="https://www.microsoft.com/sql-server/">SQL Server</a>，<a href="https://www.ibm.com/db2/">DB2</a>等；</li>
<li>开源数据库，例如：<a href="https://www.mysql.com/">MySQL</a>，<a href="https://www.postgresql.org/">PostgreSQL</a>等；</li>
<li>桌面数据库，以微软<a href="https://products.office.com/access">Access</a>为代表，适合桌面应用程序使用；</li>
<li>嵌入式数据库，以<a href="https://sqlite.org/">Sqlite</a>为代表，适合手机应用和桌面程序。</li>
</ol>
<h3>SQL</h3>
<p>什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。</p>
<p>虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为<code>PL/SQL</code>，Microsoft把自己扩展的SQL称为<code>T-SQL</code>。</p>
<p>现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。</p>
<p>总的来说，SQL语言定义了这么几种操作数据库的能力：</p>
<p><strong>DDL：Data Definition Language</strong></p>
<p>DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。</p>
<p><strong>DML：Data Manipulation Language</strong></p>
<p>DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。</p>
<p><strong>DQL：Data Query Language</strong></p>
<p>DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。</p>
<h3>语法特点</h3>
<p>SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。</p>
<p>所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。</p>
<pre><code class="language-choice">SQL的全称是：
----
Strange Question Language
Structured Question Language
(x) Structured Query Language
</code></pre>
</div><h1 style="text-align:center">安装MySQL</h1><div class="x-wiki-content x-main-content"><p>MySQL是目前应用最广泛的开源关系数据库。MySQL最早是由瑞典的MySQL AB公司开发，该公司在2008年被SUN公司收购，紧接着，SUN公司在2009年被Oracle公司收购，所以MySQL最终就变成了Oracle旗下的产品。</p>
<p>和其他关系数据库有所不同的是，MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，常用的包括：</p>
<ul>
<li>InnoDB：由Innobase Oy公司开发的一款支持事务的数据库引擎，2006年被Oracle收购；</li>
<li>MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。</li>
</ul>
<p>MySQL接口和数据库引擎的关系就好比某某浏览器和浏览器引擎（IE引擎或Webkit引擎）的关系。对用户而言，切换浏览器引擎不影响浏览器界面，切换MySQL引擎不影响自己写的应用程序使用MySQL的接口。</p>
<p>使用MySQL时，不同的表还可以使用不同的数据库引擎。如果你不知道应该采用哪种引擎，记住总是选择<em>InnoDB</em>就好了。</p>
<p>因为MySQL一开始就是开源的，所以基于MySQL的开源版本，又衍生出了各种版本：</p>
<h3>MariaDB</h3>
<p>由MySQL的创始人创建的一个开源分支版本，使用XtraDB引擎。</p>
<h3>Aurora</h3>
<p>由Amazon改进的一个MySQL版本，专门提供给在AWS托管MySQL用户，号称5倍的性能提升。</p>
<h3>PolarDB</h3>
<p>由Alibaba改进的一个MySQL版本，专门提供给在<a href="https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=cz36baxa">阿里云</a>托管的MySQL用户，号称6倍的性能提升。</p>
<p>而MySQL官方版本又分了好几个版本：</p>
<ul>
<li>Community Edition：社区开源版本，免费；</li>
<li>Standard Edition：标准版；</li>
<li>Enterprise Edition：企业版；</li>
<li>Cluster Carrier Grade Edition：集群版。</li>
</ul>
<p>以上版本的功能依次递增，价格也依次递增。不过，功能增加的主要是监控、集群等管理功能，对于基本的SQL功能是完全一样的。</p>
<p>所以使用MySQL就带来了一个巨大的好处：可以在自己的电脑上安装免费的Community Edition版本，进行学习、开发、测试，部署的时候，可以选择付费的高级版本，或者云服务商提供的兼容版本，而不需要对应用程序本身做改动。</p>
<h3>安装MySQL</h3>
<p>要在Windows或Mac上安装MySQL，首先从MySQL官方网站下载最新的MySQL Community Server版本：</p>
<p><a href="https://dev.mysql.com/downloads/mysql/">https://dev.mysql.com/downloads/mysql/</a></p>
<p>选择对应的操作系统版本，下载安装即可。在安装过程中，MySQL会自动创建一个<code>root</code>用户，并提示输入<code>root</code>口令。</p>
<p>要在Linux上安装MySQL，可以使用发行版的包管理器。例如，Debian和Ubuntu用户可以简单地通过命令<code>apt-get install mysql-server</code>安装最新的MySQL版本。</p>
<h3>运行MySQL</h3>
<p>MySQL安装后会自动在后台运行。为了验证MySQL安装是否正确，我们需要通过<code>mysql</code>这个命令行程序来连接MySQL服务器。</p>
<p>在命令提示符下输入<code>mysql -u root -p</code>，然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为<code>mysql></code>。</p>
<p>输入<code>exit</code>退出MySQL命令行。注意，MySQL服务器仍在后台运行。</p>
</div><h1 style="text-align:center">关系模型</h1><div class="x-wiki-content x-main-content"><p>我们已经知道，关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。</p>
<p>表的每一行称为记录（Record），记录是一个逻辑意义上的数据。</p>
<p>表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。</p>
<p>字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为<code>NULL</code>。注意<code>NULL</code>表示字段数据不存在。一个整型字段如果为<code>NULL</code>不表示它的值为<code>0</code>，同样的，一个字符串型字段为<code>NULL</code>也不表示它的值为空串<code>''</code>。</p>
<pre><code class="language-?">通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。
</code></pre>
<p>和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。</p>
<p>例如，一个班级表：</p>
<table>
<thead>
<tr><th>ID</th><th>名称</th><th>班主任</th></tr>
</thead>
<tbody>
<tr><td>201</td><td>二年级一班</td><td>王老师</td></tr>
<tr><td>202</td><td>二年级二班</td><td>李老师</td></tr>
</tbody>
</table>
<p>每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”：</p>
<table>
<thead>
<tr><th>ID</th><th>姓名</th><th align="right">班级ID</th><th align="right">性别</th><th align="right">年龄</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>小明</td><td align="right">201</td><td align="right">M</td><td align="right">9</td></tr>
<tr><td>2</td><td>小红</td><td align="right">202</td><td align="right">F</td><td align="right">8</td></tr>
<tr><td>3</td><td>小军</td><td align="right">202</td><td align="right">M</td><td align="right">8</td></tr>
<tr><td>4</td><td>小白</td><td align="right">201</td><td align="right">F</td><td align="right">9</td></tr>
</tbody>
</table>
<p>反过来，如果我们先在学生表中定位了一行记录，例如<code>ID=1</code>的小明，要确定他的班级，只需要根据他的“班级ID”对应的值<code>201</code>找到班级表中<code>ID=201</code>的记录，即二年级一班。所以，学生表和班级表是“多对一”的关系。</p>
<p>如果我们把班级表分拆得细一点，例如，单独创建一个教师表：</p>
<table>
<thead>
<tr><th>ID</th><th>名称</th><th>年龄</th></tr>
</thead>
<tbody>
<tr><td>A1</td><td>王老师</td><td>26</td></tr>
<tr><td>A2</td><td>张老师</td><td>39</td></tr>
<tr><td>A3</td><td>李老师</td><td>32</td></tr>
<tr><td>A4</td><td>赵老师</td><td>27</td></tr>
</tbody>
</table>
<p>班级表只存储教师ID：</p>
<table>
<thead>
<tr><th>ID</th><th>名称</th><th>班主任ID</th></tr>
</thead>
<tbody>
<tr><td>201</td><td>二年级一班</td><td>A1</td></tr>
<tr><td>202</td><td>二年级二班</td><td>A3</td></tr>
</tbody>
</table>
<p>这样，一个班级总是对应一个教师，班级表和教师表就是“一对一”关系。</p>
<p>在关系数据库中，关系是通过<em>主键</em>和<em>外键</em>来维护的。我们在后面会分别深入讲解。</p>
</div><h1 style="text-align:center">主键</h1><div class="x-wiki-content x-main-content"><p>在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，<code>students</code>表的两行记录：</p>
<table>
<thead>
<tr><th>id</th><th>class_id</th><th>name</th><th>gender</th><th>score</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>小明</td><td>M</td><td>90</td></tr>
<tr><td>2</td><td>1</td><td>小红</td><td>F</td><td>95</td></tr>
</tbody>
</table>
<p>每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。</p>
<p>对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为<em>主键</em>。</p>
<p>例如，假设我们把<code>name</code>字段作为主键，那么通过名字<code>小明</code>或<code>小红</code>就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。</p>
<p>对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。</p>
<p>由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。</p>
<p>所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。</p>
<p>因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均<em>不可</em>用作主键。</p>
<p>作为主键最好是完全业务无关的字段，我们一般把这个字段命名为<code>id</code>。常见的可作为<code>id</code>字段的类型有：</p>
<ol>
<li>
<p>自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；</p>
</li>
<li>
<p>全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似<code>8f55d96b-8acc-4636-8cb8-76bf8abc2f57</code>。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。</p>
</li>
</ol>
<p>对于大部分应用来说，通常自增类型的主键就能满足需求。我们在<code>students</code>表中定义的主键也是<code>BIGINT NOT NULL AUTO_INCREMENT</code>类型。</p>
<pre><code class="language-!">如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
</code></pre>
<h3>联合主键</h3>
<p>关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。</p>
<p>对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：</p>
<table>
<thead>
<tr><th>id_num</th><th>id_type</th><th>other columns...</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>A</td><td>...</td></tr>
<tr><td>2</td><td>A</td><td>...</td></tr>
<tr><td>2</td><td>B</td><td>...</td></tr>
</tbody>
</table>
<p>如果我们把上述表的<code>id_num</code>和<code>id_type</code>这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。</p>
<p>没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。</p>
<h3>小结</h3>
<p>主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许<code>NULL</code>。</p>
<p>可以使用多个列作为联合主键，但联合主键并不常用。</p>
</div><h1 style="text-align:center">外键</h1><div class="x-wiki-content x-main-content"><p>当我们用主键唯一标识记录时，我们就可以在<code>students</code>表中确定任意一个学生的记录：</p>
<table>
<thead>
<tr><th>id</th><th>name</th><th>other columns...</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>小明</td><td>...</td></tr>
<tr><td>2</td><td>小红</td><td>...</td></tr>
</tbody>
</table>
<p>我们还可以在<code>classes</code>表中确定任意一个班级记录：</p>
<table>
<thead>
<tr><th>id</th><th>name</th><th>other columns...</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>一班</td><td>...</td></tr>
<tr><td>2</td><td>二班</td><td>...</td></tr>
</tbody>
</table>
<p>但是我们如何确定<code>students</code>表的一条记录，例如，<code>id=1</code>的小明，属于哪个班级呢？</p>
<p>由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个<code>classes</code>的记录可以对应多个<code>students</code>表的记录。</p>
<p>为了表达这种一对多的关系，我们需要在<code>students</code>表中加入一列<code>class_id</code>，让它的值与<code>classes</code>表的某条记录相对应：</p>
<table>
<thead>
<tr><th>id</th><th>class_id</th><th>name</th><th>other columns...</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>小明</td><td>...</td></tr>
<tr><td>2</td><td>1</td><td>小红</td><td>...</td></tr>
<tr><td>5</td><td>2</td><td>小白</td><td>...</td></tr>
</tbody>
</table>
<p>这样，我们就可以根据<code>class_id</code>这个列直接定位出一个<code>students</code>表的记录应该对应到<code>classes</code>的哪条记录。</p>
<p>例如：</p>
<ul>
<li>小明的<code>class_id</code>是<code>1</code>，因此，对应的<code>classes</code>表的记录是<code>id=1</code>的一班；</li>
<li>小红的<code>class_id</code>是<code>1</code>，因此，对应的<code>classes</code>表的记录是<code>id=1</code>的一班；</li>
<li>小白的<code>class_id</code>是<code>2</code>，因此，对应的<code>classes</code>表的记录是<code>id=2</code>的二班。</li>
</ul>
<p>在<code>students</code>表中，通过<code>class_id</code>的字段，可以把数据与另一张表关联起来，这种列称为<code>外键</code>。</p>
<p>外键并不是通过列名实现的，而是通过定义外键约束实现的：</p>
<pre><code>ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
</code></pre>
<p>其中，外键约束的名称<code>fk_class_id</code>可以任意，<code>FOREIGN KEY (class_id)</code>指定了<code>class_id</code>作为外键，<code>REFERENCES classes (id)</code>指定了这个外键将关联到<code>classes</code>表的<code>id</code>列（即<code>classes</code>表的主键）。</p>
<p>通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果<code>classes</code>表不存在<code>id=99</code>的记录，<code>students</code>表就无法插入<code>class_id=99</code>的记录。</p>
<p>由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，<code>class_id</code>仅仅是一个普通的列，只是它起到了外键的作用而已。</p>
<p>要删除一个外键约束，也是通过<code>ALTER TABLE</code>实现的：</p>
<pre><code>ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
</code></pre>
<p>注意：删除外键约束并没有删除外键这一列。删除列是通过<code>DROP COLUMN ...</code>实现的。</p>
<h3>多对多</h3>
<p>通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。</p>
<p>多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：</p>
<p><code>teachers</code>表：</p>
<table>
<thead>
<tr><th>id</th><th>name</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>张老师</td></tr>
<tr><td>2</td><td>王老师</td></tr>
<tr><td>3</td><td>李老师</td></tr>
<tr><td>4</td><td>赵老师</td></tr>
</tbody>
</table>
<p><code>classes</code>表：</p>
<table>
<thead>
<tr><th>id</th><th>name</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>一班</td></tr>
<tr><td>2</td><td>二班</td></tr>
</tbody>
</table>
<p>中间表<code>teacher_class</code>关联两个一对多关系：</p>
<table>
<thead>
<tr><th>id</th><th>teacher_id</th><th>class_id</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>1</td></tr>
<tr><td>2</td><td>1</td><td>2</td></tr>
<tr><td>3</td><td>2</td><td>1</td></tr>
<tr><td>4</td><td>2</td><td>2</td></tr>
<tr><td>5</td><td>3</td><td>1</td></tr>
<tr><td>6</td><td>4</td><td>2</td></tr>
</tbody>
</table>
<p>通过中间表<code>teacher_class</code>可知<code>teachers</code>到<code>classes</code>的关系：</p>
<ul>
<li><code>id=1</code>的张老师对应<code>id=1,2</code>的一班和二班；</li>
<li><code>id=2</code>的王老师对应<code>id=1,2</code>的一班和二班；</li>
<li><code>id=3</code>的李老师对应<code>id=1</code>的一班；</li>
<li><code>id=4</code>的赵老师对应<code>id=2</code>的二班。</li>
</ul>
<p>同理可知<code>classes</code>到<code>teachers</code>的关系：</p>
<ul>
<li><code>id=1</code>的一班对应<code>id=1,2,3</code>的张老师、王老师和李老师；</li>
<li><code>id=2</code>的二班对应<code>id=1,2,4</code>的张老师、王老师和赵老师；</li>
</ul>
<p>因此，通过中间表，我们就定义了一个“多对多”关系。</p>
<h3>一对一</h3>
<p>一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。</p>
<p>例如，<code>students</code>表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表<code>contacts</code>，我们就可以得到一个“一对一”关系：</p>
<table>
<thead>
<tr><th>id</th><th>student_id</th><th>mobile</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>135xxxx6300</td></tr>
<tr><td>2</td><td>2</td><td>138xxxx2209</td></tr>
<tr><td>3</td><td>5</td><td>139xxxx8086</td></tr>
</tbody>
</table>
<p>有细心的童鞋会问，既然是一对一关系，那为啥不给<code>students</code>表增加一个<code>mobile</code>列，这样就能合二为一了？</p>
<p>如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，<code>contacts</code>表就不存在对应的记录。实际上，一对一关系准确地说，是<code>contacts</code>表一对一对应<code>students</code>表。</p>
<p>还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表<code>user_info</code>和用户详细信息表<code>user_profiles</code>，大部分时候，只需要查询<code>user_info</code>表，并不需要查询<code>user_profiles</code>表，这样就提高了查询速度。</p>
<h3>小结</h3>
<p>关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。</p>
</div><h1 style="text-align:center">索引</h1><div class="x-wiki-content x-main-content"><p>在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。</p>
<p>索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。</p>
<p>例如，对于<code>students</code>表：</p>
<table>
<thead>
<tr><th>id</th><th>class_id</th><th>name</th><th>gender</th><th>score</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>小明</td><td>M</td><td>90</td></tr>
<tr><td>2</td><td>1</td><td>小红</td><td>F</td><td>95</td></tr>
<tr><td>3</td><td>1</td><td>小军</td><td>M</td><td>88</td></tr>
</tbody>
</table>
<p>如果要经常根据<code>score</code>列进行查询，就可以对<code>score</code>列创建索引：</p>
<pre><code>ALTER TABLE students
ADD INDEX idx_score (score);
</code></pre>
<p>使用<code>ADD INDEX idx_score (score)</code>就创建了一个名称为<code>idx_score</code>，使用列<code>score</code>的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：</p>
<pre><code>ALTER TABLE students
ADD INDEX idx_name_score (name, score);
</code></pre>
<p>索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如<code>gender</code>列，大约一半的记录值是<code>M</code>，另一半是<code>F</code>，因此，对该列创建索引就没有意义。</p>
<p>可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。</p>
<p>对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。</p>
<h3>唯一索引</h3>
<p>在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。</p>
<p>但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设<code>students</code>表的<code>name</code>不能重复：</p>
<pre><code>ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
</code></pre>
<p>通过<code>UNIQUE</code>关键字我们就添加了一个唯一索引。</p>
<p>也可以只对某一列添加一个唯一约束而不创建唯一索引：</p>
<pre><code>ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
</code></pre>
<p>这种情况下，<code>name</code>列没有索引，但仍然具有唯一性保证。</p>
<p>无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。</p>
<h3>小结</h3>
<p>通过对数据库表创建索引，可以提高查询速度。</p>
<p>通过创建唯一索引，可以保证某一列的值具有唯一性。</p>
<p>数据库索引对于用户和应用程序来说都是透明的。</p>
</div><h1 style="text-align:center">在线SQL</h1><div class="x-wiki-content x-main-content"><p>为了便于在线练习，我们提供了一个在线运行SQL的功能。实际上这是在浏览器页面运行的一个JavaScript编写的内存型SQL数据库<a href="http://alasql.org/">AlaSQL</a>。不必运行MySQL等实际的数据库软件，即可在线编写并执行SQL语句。</p>
<p>可以在此测试执行最简单的SQL语句：</p>
<pre><code class="language-x-sql">-- 以双减号开头的是注释
----
SELECT * FROM students;
</code></pre>
<p>请注意，在页面加载时，<code>students</code>表和<code>classes</code>表就自动被创建并填入了若干数据。由于数据只存在于浏览器的内存中，因此，如果修改了数据，重新刷新页面后，数据会重置为初始值。</p>
</div><h1 style="text-align:center">查询数据</h1><div class="x-wiki-content x-main-content"><p>在关系数据库中，最常用的操作就是查询。</p>
<h3>准备数据</h3>
<p>为了便于讲解和练习，我们先准备好了一个<code>students</code>表和一个<code>classes</code>表，它们的结构和数据如下：</p>
<p><code>students</code>表存储了学生信息：</p>
<table>
<thead>
<tr><th>id</th><th>class_id</th><th>name</th><th>gender</th><th>score</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>1</td><td>小明</td><td>M</td><td>90</td></tr>
<tr><td>2</td><td>1</td><td>小红</td><td>F</td><td>95</td></tr>
<tr><td>3</td><td>1</td><td>小军</td><td>M</td><td>88</td></tr>
<tr><td>4</td><td>1</td><td>小米</td><td>F</td><td>73</td></tr>
<tr><td>5</td><td>2</td><td>小白</td><td>F</td><td>81</td></tr>
<tr><td>6</td><td>2</td><td>小兵</td><td>M</td><td>55</td></tr>
<tr><td>7</td><td>2</td><td>小林</td><td>M</td><td>85</td></tr>
<tr><td>8</td><td>3</td><td>小新</td><td>F</td><td>91</td></tr>
<tr><td>9</td><td>3</td><td>小王</td><td>M</td><td>89</td></tr>
<tr><td>10</td><td>3</td><td>小丽</td><td>F</td><td>85</td></tr>
</tbody>
</table>
<p><code>classes</code>表存储了班级信息：</p>
<table>
<thead>
<tr><th>id</th><th>name</th></tr>
</thead>
<tbody>
<tr><td>1</td><td>一班</td></tr>
<tr><td>2</td><td>二班</td></tr>
<tr><td>3</td><td>三班</td></tr>
<tr><td>4</td><td>四班</td></tr>
</tbody>
</table>
<p>请注意，和<code>MySQL</code>的持久化存储不同的是，由于我们使用的是<a href="http://alasql.org/">AlaSQL</a>内存数据库，两张表的数据在页面加载时导入，并且只存在于浏览器的内存中，因此，刷新页面后，数据会重置为上述初始值。</p>
<h3>MySQL</h3>
<p>如果你想用MySQL练习，可以<a href="https://raw.githubusercontent.com/michaelliao/learn-sql/master/mysql/init-test-data.sql">下载</a>这个<a href="https://github.com/michaelliao/learn-sql/blob/master/mysql/init-test-data.sql">SQL脚本</a>，然后在命令行运行：</p>
<pre><code>$ mysql -u root -p < init-test-data.sql
</code></pre>
<p>就可以自动创建<code>test</code>数据库，并且在<code>test</code>数据库下创建<code>students</code>表和<code>classes</code>表，以及必要的初始化数据。</p>
<p>和内存数据库不同的是，对MySQL数据库做的所有修改，都会保存下来。如果你希望恢复到初始状态，可以再次运行该脚本。</p>
</div><h1 style="text-align:center">基本查询</h1><div class="x-wiki-content x-main-content"><p>要查询数据库表的数据，我们使用如下的SQL语句：</p>
<pre><code>SELECT * FROM <表名>
</code></pre>
<p>假设表名是<code>students</code>，要查询<code>students</code>表的所有行，我们用如下SQL语句：</p>
<pre><code class="language-x-sql">-- 查询students表的所有数据
----
SELECT * FROM students;
</code></pre>
<p>使用<code>SELECT * FROM students</code>时，<code>SELECT</code>是关键字，表示将要执行一个查询，<code>*</code>表示“所有列”，<code>FROM</code>表示将要从哪个表查询，本例中是<code>students</code>表。</p>
<p>该SQL将查询出<code>students</code>表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。</p>
<p>要查询<code>classes</code>表的所有行，我们用如下SQL语句：</p>
<pre><code class="language-x-sql">-- 查询classes表的所有数据
----
SELECT * FROM classes;
</code></pre>
<p>运行上述SQL语句，观察查询结果。</p>
<p><code>SELECT</code>语句其实并不要求一定要有<code>FROM</code>子句。我们来试试下面的<code>SELECT</code>语句：</p>
<pre><code class="language-x-sql">-- 计算100+200
----
SELECT 100+200;
</code></pre>
<p>上述查询会直接计算出表达式的结果。虽然<code>SELECT</code>可以用作计算，但它并不是SQL的强项。但是，不带<code>FROM</code>子句的<code>SELECT</code>语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条<code>SELECT 1;</code>来测试数据库连接。</p>
<h3>小结</h3>
<p>使用SELECT查询的基本语句<code>SELECT * FROM <表名></code>可以查询一个表的所有行和所有列的数据。</p>
<p>SELECT查询的结果是一个二维表。</p>
</div><h1 style="text-align:center">条件查询</h1><div class="x-wiki-content x-main-content"><p>使用<code>SELECT * FROM <表名></code>可以查询到一张表的所有记录。但是，很多时候，我们并不希望获得所有记录，而是根据条件选择性地获取指定条件的记录，例如，查询分数在80分以上的学生记录。在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。</p>
<p>SELECT语句可以通过<code>WHERE</code>条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成<code>WHERE</code>条件就是<code>SELECT * FROM students WHERE score >= 80</code>。</p>
<p>其中，<code>WHERE</code>关键字后面的<code>score >= 80</code>就是条件。<code>score</code>是列名，该列存储了学生的成绩，因此，<code>score >= 80</code>就筛选出了指定条件的记录：</p>
<pre><code class="language-x-sql">-- 按条件查询students:
----
SELECT * FROM students WHERE score >= 80;
</code></pre>
<p>因此，条件查询的语法就是：</p>
<pre><code>SELECT * FROM <表名> WHERE <条件表达式>
</code></pre>
<p>条件表达式可以用<code><条件1> AND <条件2></code>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：</p>
<ul>
<li>条件1：根据score列的数据判断：<code>score >= 80</code>；</li>
<li>条件2：根据gender列的数据判断：<code>gender = 'M'</code>，注意<code>gender</code>列存储的是字符串，需要用单引号括起来。</li>
</ul>
<p>就可以写出<code>WHERE</code>条件：<code>score >= 80 AND gender = 'M'</code>：</p>
<pre><code class="language-x-sql">-- 按AND条件查询students:
----
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
</code></pre>
<p>第二种条件是<code><条件1> OR <条件2></code>，表示满足条件1或者满足条件2。例如，把上述<code>AND</code>查询的两个条件改为<code>OR</code>，查询结果就是“分数在80分或以上”或者“男生”，满足任意之一的条件即选出该记录：</p>
<pre><code class="language-x-sql">-- 按OR条件查询students:
----
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
</code></pre>
<p>很显然<code>OR</code>条件要比<code>AND</code>条件宽松，返回的符合条件的记录也更多。</p>
<p>第三种条件是<code>NOT <条件></code>，表示“不符合该条件”的记录。例如，写一个“不是2班的学生”这个条件，可以先写出“是2班的学生”：<code>class_id = 2</code>，再加上<code>NOT</code>：<code>NOT class_id = 2</code>：</p>
<pre><code class="language-x-sql">-- 按NOT条件查询students:
----
SELECT * FROM students WHERE NOT class_id = 2;
</code></pre>
<p>上述<code>NOT</code>条件<code>NOT class_id = 2</code>其实等价于<code>class_id <> 2</code>，因此，<code>NOT</code>查询不是很常用。</p>
<p>要组合三个或者更多的条件，就需要用小括号<code>()</code>表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：</p>
<pre><code class="language-x-sql">-- 按多个条件查询students:
----
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
</code></pre>
<p>如果不加括号，条件运算按照<code>NOT</code>、<code>AND</code>、<code>OR</code>的优先级进行，即<code>NOT</code>优先级最高，其次是<code>AND</code>，最后是<code>OR</code>。加上括号可以改变优先级。</p>
<h3>常用的条件表达式</h3>
<table>
<thead>
<tr><th>条件</th><th>表达式举例1</th><th>表达式举例2</th><th>说明</th></tr>
</thead>
<tbody>
<tr><td>使用=判断相等</td><td>score = 80</td><td>name = 'abc'</td><td>字符串需要用单引号括起来</td></tr>
<tr><td>使用>判断大于</td><td>score > 80</td><td>name > 'abc'</td><td>字符串比较根据ASCII码，中文字符比较根据数据库设置</td></tr>
<tr><td>使用>=判断大于或相等</td><td>score >= 80</td><td>name >= 'abc'</td><td></td></tr>
<tr><td>使用<判断小于</td><td>score < 80</td><td>name <= 'abc'</td><td></td></tr>
<tr><td>使用<=判断小于或相等</td><td>score <= 80</td><td>name <= 'abc'</td><td></td></tr>
<tr><td>使用<>判断不相等</td><td>score <> 80</td><td>name <> 'abc'</td><td></td></tr>
<tr><td>使用LIKE判断相似</td><td>name LIKE 'ab%'</td><td>name LIKE '%bc%'</td><td>%表示任意字符，例如'ab%'将匹配'ab'，'abc'，'abcd'</td></tr>
</tbody>
</table>
<pre><code class="language-choice">查询分数在60分(含)～90分(含)之间的学生可以使用的WHERE语句是：
----
WHERE score >= 60 OR score <= 90
(x) WHERE score >= 60 AND score <= 90
WHERE score IN (60, 90)
(x) WHERE score BETWEEN 60 AND 90
WHERE 60 <= score <= 90
</code></pre>
<h3>小结</h3>
<p>通过<code>WHERE</code>条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录。</p>
</div><h1 style="text-align:center">投影查询</h1><div class="x-wiki-content x-main-content"><p>使用<code>SELECT * FROM <表名> WHERE <条件></code>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。</p>
<p>如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用<code>SELECT 列1, 列2, 列3 FROM ...</code>，让结果集仅包含指定列。这种操作称为投影查询。</p>
<p>例如，从<code>students</code>表中返回<code>id</code>、<code>score</code>和<code>name</code>这三列：</p>
<pre><code class="language-x-sql">-- 使用投影查询
----
SELECT id, score, name FROM students;
</code></pre>
<p>这样返回的结果集就只包含了我们指定的列，并且，结果集的列的顺序和原表可以不一样。</p>
<p>使用<code>SELECT 列1, 列2, 列3 FROM ...</code>时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是<code>SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...</code>。</p>
<p>例如，以下<code>SELECT</code>语句将列名<code>score</code>重命名为<code>points</code>，而<code>id</code>和<code>name</code>列名保持不变：</p>
<pre><code class="language-x-sql">-- 使用投影查询，并将列名重命名：
----
SELECT id, score points, name FROM students;
</code></pre>
<p>投影查询同样可以接<code>WHERE</code>条件，实现复杂的查询：</p>
<pre><code class="language-x-sql">-- 使用投影查询+WHERE条件：
----
SELECT id, score points, name FROM students WHERE gender = 'M';
</code></pre>
<h3>小结</h3>
<p>使用<code>SELECT *</code>表示查询表的所有列，使用<code>SELECT 列1, 列2, 列3</code>则可以仅返回指定列，这种操作称为投影。</p>
<p><code>SELECT</code>语句可以对结果集的列进行重命名。</p>
</div><h1 style="text-align:center">排序</h1><div class="x-wiki-content x-main-content"><h3>排序</h3>
<p>我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照<code>id</code>排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上<code>ORDER BY</code>子句。例如按照成绩从低到高进行排序：</p>
<pre><code class="language-x-sql">-- 按score从低到高
----
SELECT id, name, gender, score FROM students ORDER BY score;
</code></pre>
<p>如果要反过来，按照成绩从高到底排序，我们可以加上<code>DESC</code>表示“倒序”：</p>
<pre><code class="language-x-sql">-- 按score从高到低
----
SELECT id, name, gender, score FROM students ORDER BY score DESC;
</code></pre>
<p>如果<code>score</code>列有相同的数据，要进一步排序，可以继续添加列名。例如，使用<code>ORDER BY score DESC, gender</code>表示先按<code>score</code>列倒序，如果有相同分数的，再按<code>gender</code>列排序：</p>
<pre><code class="language-x-sql">-- 按score, gender排序:
----
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
</code></pre>
<p>默认的排序规则是<code>ASC</code>：“升序”，即从小到大。<code>ASC</code>可以省略，即<code>ORDER BY score ASC</code>和<code>ORDER BY score</code>效果一样。</p>
<p>如果有<code>WHERE</code>子句，那么<code>ORDER BY</code>子句要放到<code>WHERE</code>子句后面。例如，查询一班的学生成绩，并按照倒序排序：</p>
<pre><code class="language-x-sql">-- 带WHERE条件的ORDER BY:
----
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
</code></pre>
<p>这样，结果集仅包含符合<code>WHERE</code>条件的记录，并按照<code>ORDER BY</code>的设定排序。</p>
<h3>小结</h3>
<p>使用<code>ORDER BY</code>可以对结果集进行排序；</p>
<p>可以对多列进行升序、倒序排序。</p>
</div><h1 style="text-align:center">分页查询</h1><div class="x-wiki-content x-main-content"><h3>分页</h3>
<p>使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。</p>
<p>要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。</p>
<p>因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过<code>LIMIT <M> OFFSET <N></code>子句实现。我们先把所有学生按照成绩从高到低进行排序：</p>
<pre><code class="language-x-sql">-- 按score从高到低
----
SELECT id, name, gender, score FROM students ORDER BY score DESC;
</code></pre>
<p>现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用<code>LIMIT 3 OFFSET 0</code>：</p>
<pre><code class="language-x-sql">-- 查询第1页
----
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
</code></pre>
<p>上述查询<code>LIMIT 3 OFFSET 0</code>表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。</p>
<p>如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把<code>OFFSET</code>设定为3：</p>
<pre><code class="language-x-sql">-- 查询第2页
----
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
</code></pre>
<p>类似的，查询第3页的时候，<code>OFFSET</code>应该设定为6:</p>
<pre><code class="language-x-sql">-- 查询第3页
----
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;
</code></pre>
<p>查询第4页的时候，<code>OFFSET</code>应该设定为9:</p>
<pre><code class="language-x-sql">-- 查询第4页
----
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;
</code></pre>
<p>由于第4页只有1条记录，因此最终结果集按实际数量1显示。<code>LIMIT 3</code>表示的意思是“最多3条记录”。</p>
<p>可见，分页查询的关键在于，首先要确定每页需要显示的结果数量<code>pageSize</code>（这里是3），然后根据当前页的索引<code>pageIndex</code>（从1开始），确定<code>LIMIT</code>和<code>OFFSET</code>应该设定的值：</p>
<ul>
<li><code>LIMIT</code>总是设定为<code>pageSize</code>；</li>
<li><code>OFFSET</code>计算公式为<code>pageSize * (pageIndex - 1)</code>。</li>
</ul>
<p>这样就能正确查询出第N页的记录集。</p>
<p>如果原本记录集一共就10条记录，但我们把<code>OFFSET</code>设置为20，会得到什么结果呢？</p>
<pre><code class="language-x-sql">-- OFFSET设定为20
----
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;
</code></pre>
<p><code>OFFSET</code>超过了查询的最大数量并不会报错，而是得到一个空的结果集。</p>
<h3>注意</h3>
<p><code>OFFSET</code>是可选的，如果只写<code>LIMIT 15</code>，那么相当于<code>LIMIT 15 OFFSET 0</code>。</p>
<p>在MySQL中，<code>LIMIT 15 OFFSET 30</code>还可以简写成<code>LIMIT 30, 15</code>。</p>
<p>使用<code>LIMIT <M> OFFSET <N></code>分页时，随着<code>N</code>越来越大，查询效率也会越来越低。</p>
<h3>小结</h3>
<p>使用<code>LIMIT <M> OFFSET <N></code>可以对结果集进行分页，每次查询返回结果集的一部分；</p>
<p>分页查询需要先确定每页的数量和当前页数，然后确定<code>LIMIT</code>和<code>OFFSET</code>的值。</p>
<h3>思考</h3>
<p>在分页查询之前，如何计算一共有几页？</p>
</div><h1 style="text-align:center">聚合查询</h1><div class="x-wiki-content x-main-content"><p>如果我们要统计一张表的数据量，例如，想查询<code>students</code>表一共有多少条记录，难道必须用<code>SELECT * FROM students</code>查出来然后再数一数有多少行吗？</p>
<p>这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。</p>
<p>仍然以查询<code>students</code>表一共有多少条记录为例，我们可以使用SQL内置的<code>COUNT()</code>函数查询：</p>
<pre><code class="language-x-sql">-- 使用聚合查询:
----
SELECT COUNT(*) FROM students;
</code></pre>
<p><code>COUNT(*)</code>表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是<code>COUNT(*)</code>。</p>
<p>通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：</p>
<pre><code class="language-x-sql">-- 使用聚合查询并设置结果集的列名为num:
----
SELECT COUNT(*) num FROM students;
</code></pre>
<p><code>COUNT(*)</code>和<code>COUNT(id)</code>实际上是一样的效果。另外注意，聚合查询同样可以使用<code>WHERE</code>条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：</p>
<pre><code class="language-x-sql">-- 使用聚合查询并设置WHERE条件:
----
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
</code></pre>
<p>除了<code>COUNT()</code>函数外，SQL还提供了如下聚合函数：</p>
<table>
<thead>
<tr><th>函数</th><th>说明</th></tr>
</thead>
<tbody>
<tr><td>SUM</td><td>计算某一列的合计值，该列必须为数值类型</td></tr>
<tr><td>AVG</td><td>计算某一列的平均值，该列必须为数值类型</td></tr>
<tr><td>MAX</td><td>计算某一列的最大值</td></tr>
<tr><td>MIN</td><td>计算某一列的最小值</td></tr>
</tbody>
</table>
<p>注意，<code>MAX()</code>和<code>MIN()</code>函数并不限于数值类型。如果是字符类型，<code>MAX()</code>和<code>MIN()</code>会返回排序最后和排序最前的字符。</p>
<p>要统计男生的平均成绩，我们用下面的聚合查询：</p>
<pre><code class="language-x-sql">-- 使用聚合查询计算男生平均成绩:
----
SELECT AVG(score) average FROM students WHERE gender = 'M';
</code></pre>
<p>要特别注意：如果聚合查询的<code>WHERE</code>条件没有匹配到任何行，<code>COUNT()</code>会返回0，而<code>SUM()</code>、<code>AVG()</code>、<code>MAX()</code>和<code>MIN()</code>会返回<code>NULL</code>：</p>
<pre><code class="language-x-sql">-- WHERE条件gender = 'X'匹配不到任何行:
----
SELECT AVG(score) average FROM students WHERE gender = 'X';
</code></pre>
<pre><code class="language-choice">每页3条记录，如何通过聚合查询获得总页数？
----
SELECT COUNT(*) / 3 FROM students;
SELECT FLOOR(COUNT(*) / 3) FROM students;
(x) SELECT CEILING(COUNT(*) / 3) FROM students;
</code></pre>
<h3>分组</h3>
<p>如果我们要统计一班的学生数量，我们知道，可以用<code>SELECT COUNT(*) num FROM students WHERE class_id = 1;</code>。如果要继续统计二班、三班的学生数量，难道必须不断修改<code>WHERE</code>条件来执行<code>SELECT</code>语句吗？</p>
<p>对于聚合查询，SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询：</p>
<pre><code class="language-x-sql">-- 按class_id分组:
----
SELECT COUNT(*) num FROM students GROUP BY class_id;
</code></pre>
<p>执行这个查询，<code>COUNT()</code>的结果不再是一个，而是3个，这是因为，<code>GROUP BY</code>子句指定了按<code>class_id</code>分组，因此，执行该<code>SELECT</code>语句时，会把<code>class_id</code>相同的列先分组，再分别计算，因此，得到了3行结果。</p>
<p>但是这3行结果分别是哪三个班级的，不好看出来，所以我们可以把<code>class_id</code>列也放入结果集中：</p>
<pre><code class="language-x-sql">-- 按class_id分组:
----
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
</code></pre>
<p>这下结果集就可以一目了然地看出各个班级的学生人数。我们再试试把<code>name</code>放入结果集：</p>
<pre><code class="language-x-sql">-- 按class_id分组:
----
SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;
</code></pre>
<p>不出意外，执行这条查询我们会得到一个语法错误，因为在任意一个分组中，只有<code>class_id</code>都相同，<code>name</code>是不同的，SQL引擎不能把多个<code>name</code>的值放入一行记录中。因此，聚合查询的列中，只能放入分组的列。</p>
<pre><code class="language-!">注意：AlaSQL并没有严格执行SQL标准，上述SQL在浏览器可以正常执行，但是在MySQL、Oracle等环境下将报错，请自行在MySQL中测试。
</code></pre>
<p>也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：</p>
<pre><code class="language-x-sql">-- 按class_id, gender分组:
----
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
</code></pre>
<p>上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数。</p>
<h3>练习</h3>
<p>请使用一条SELECT查询查出每个班级的平均分：</p>
<pre><code class="language-x-sql">-- 查出每个班级的平均分，结果集应当有3条记录:
----
SELECT 'TODO';
</code></pre>
<p>请使用一条SELECT查询查出每个班级男生和女生的平均分：</p>
<pre><code class="language-x-sql">-- 查出每个班级的平均分，结果集应当有6条记录:
----
SELECT 'TODO';
</code></pre>
<h3>小结</h3>
<p>使用SQL提供的聚合查询，我们可以方便地计算总数、合计值、平均值、最大值和最小值；</p>
<p>聚合查询也可以添加<code>WHERE</code>条件。</p>
</div><h1 style="text-align:center">多表查询</h1><div class="x-wiki-content x-main-content"><p>SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：<code>SELECT * FROM <表1> <表2></code>。</p>
<p>例如，同时从<code>students</code>表和<code>classes</code>表的“乘积”，即查询数据，可以这么写：</p>
<pre><code class="language-x-sql">-- FROM students, classes:
----
SELECT * FROM students, classes;
</code></pre>
<p>这种一次查询两个表的数据，查询的结果也是一个二维表，它是<code>students</code>表和<code>classes</code>表的“乘积”，即<code>students</code>表的每一行与<code>classes</code>表的每一行都两两拼在一起返回。结果集的列数是<code>students</code>表和<code>classes</code>表的列数之和，行数是<code>students</code>表和<code>classes</code>表的行数之积。</p>
<p>这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。</p>
<p>你可能还注意到了，上述查询的结果集有两列<code>id</code>和两列<code>name</code>，两列<code>id</code>是因为其中一列是<code>students</code>表的<code>id</code>，而另一列是<code>classes</code>表的<code>id</code>，但是在结果集中，不好区分。两列<code>name</code>同理</p>
<p>要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的<code>id</code>和<code>name</code>列起别名：</p>
<pre><code class="language-x-sql">-- set alias:
----
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
</code></pre>
<p>注意，多表查询时，要使用<code>表名.列名</code>这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用<code>表名.列名</code>这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：</p>
<pre><code class="language-x-sql">-- set table alias:
----
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
</code></pre>
<p>注意到<code>FROM</code>子句给表设置别名的语法是<code>FROM <表名1> <别名1>, <表名2> <别名2></code>。这样我们用别名<code>s</code>和<code>c</code>分别表示<code>students</code>表和<code>classes</code>表。</p>
<p>多表查询也是可以添加<code>WHERE</code>条件的，我们来试试：</p>
<pre><code class="language-x-sql">-- set where clause:
----
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
</code></pre>
<p>这个查询的结果集每行记录都满足条件<code>s.gender = 'M'</code>和<code>c.id = 1</code>。添加<code>WHERE</code>条件后结果集的数量大大减少了。</p>
<h3>小结</h3>
<p>使用多表查询可以获取M x N行记录；</p>
<p>多表查询的结果集可能非常巨大，要小心使用。</p>
</div><h1 style="text-align:center">连接查询</h1><div class="x-wiki-content x-main-content"><p>连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。</p>
<p>例如，我们想要选出<code>students</code>表的所有学生信息，可以用一条简单的SELECT语句完成：</p>
<pre><code class="language-x-sql">-- 选出所有学生
----
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
</code></pre>
<p>但是，假设我们希望结果集同时包含所在班级的名称，上面的结果集只有<code>class_id</code>列，缺少对应班级的<code>name</code>列。</p>
<p>现在问题来了，存放班级名称的<code>name</code>列存储在<code>classes</code>表中，只有根据<code>students</code>表的<code>class_id</code>，找到<code>classes</code>表对应的行，再取出<code>name</code>列，就可以获得班级名称。</p>
<p>这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现：</p>
<pre><code class="language-x-sql">-- 选出所有学生，同时返回班级名称
----
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
</code></pre>
<p>注意INNER JOIN查询的写法是：</p>
<ol>
<li>先确定主表，仍然使用<code>FROM <表1></code>的语法；</li>
<li>再确定需要连接的表，使用<code>INNER JOIN <表2></code>的语法；</li>
<li>然后确定连接条件，使用<code>ON <条件...></code>，这里的条件是<code>s.class_id = c.id</code>，表示<code>students</code>表的<code>class_id</code>列与<code>classes</code>表的<code>id</code>列相同的行需要连接；</li>
<li>可选：加上<code>WHERE</code>子句、<code>ORDER BY</code>等子句。</li>
</ol>
<p>使用别名不是必须的，但可以更好地简化查询语句。</p>
<p>那什么是内连接（INNER JOIN）呢？先别着急，有内连接（INNER JOIN）就有外连接（OUTER JOIN）。我们把内连接查询改成外连接查询，看看效果：</p>
<pre><code class="language-x-sql">-- 使用OUTER JOIN
----
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
</code></pre>
<p>执行上述RIGHT OUTER JOIN可以看到，和INNER JOIN相比，RIGHT OUTER JOIN多了一行，多出来的一行是“四班”，但是，学生相关的列如<code>name</code>、<code>gender</code>、<code>score</code>都为<code>NULL</code>。</p>
<p>这也容易理解，因为根据<code>ON</code>条件<code>s.class_id = c.id</code>，<code>classes</code>表的id=4的行正是“四班”，但是，<code>students</code>表中并不存在class_id=4的行。</p>
<p>有RIGHT OUTER JOIN，就有LEFT OUTER JOIN，以及FULL OUTER JOIN。它们的区别是：</p>
<p>INNER JOIN只返回同时存在于两张表的行数据，由于<code>students</code>表的<code>class_id</code>包含1，2，3，<code>classes</code>表的<code>id</code>包含1，2，3，4，所以，INNER JOIN根据条件<code>s.class_id = c.id</code>返回的结果集仅包含1，2，3。</p>
<p>RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以<code>NULL</code>填充剩下的字段。</p>
<p>LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的<code>class_name</code>是<code>NULL</code>：</p>
<pre><code class="language-x-sql">-- 先增加一列class_id=5:
INSERT INTO students (class_id, name, gender, score) values (5, '新生', 'M', 88);
-- 使用LEFT OUTER JOIN
----
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;
</code></pre>
<p>最后，我们使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL：</p>
<pre><code class="language-x-sql">-- 使用FULL OUTER JOIN
----
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;
</code></pre>
<p>对于这么多种JOIN查询，到底什么使用应该用哪种呢？其实我们用图来表示结果集就一目了然了。</p>
<p>假设查询语句是：</p>
<pre><code>SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
</code></pre>
<p>我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：</p>
<p><img alt="inner-join" src="./files/attachments/1246892164662976/l.jpg"/></p>
<p>LEFT OUTER JOIN是选出左表存在的记录：</p>
<p><img alt="left-outer-join" src="./files/attachments/1246893588481376/l.jpg"/></p>
<p>RIGHT OUTER JOIN是选出右表存在的记录：</p>
<p><img alt="right-outer-join" src="./files/attachments/1246893609222688/l.jpg"/></p>
<p>FULL OUTER JOIN则是选出左右表都存在的记录：</p>
<p><img alt="full-outer-join" src="./files/attachments/1246893632359424/l.jpg"/></p>
<h3>小结</h3>
<p>JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；</p>
<p>INNER JOIN是最常用的一种JOIN查询，它的语法是<code>SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...></code>；</p>
<p>JOIN查询仍然可以使用<code>WHERE</code>条件和<code>ORDER BY</code>排序。</p>
</div><h1 style="text-align:center">修改数据</h1><div class="x-wiki-content x-main-content"><p>关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了<code>SELECT</code>语句的详细用法。</p>
<p>而对于增、删、改，对应的SQL语句分别是：</p>
<ul>
<li>INSERT：插入新记录；</li>
<li>UPDATE：更新已有记录；</li>
<li>DELETE：删除已有记录。</li>
</ul>
<p>我们将分别讨论这三种修改数据的语句的使用方法。</p>
</div><h1 style="text-align:center">INSERT</h1><div class="x-wiki-content x-main-content"><p>当我们需要向数据库表中插入一条新记录时，就必须使用<code>INSERT</code>语句。</p>
<p><img alt="insert-brain" src="./files/attachments/1250124915420384/l.jpg"/></p>
<p><code>INSERT</code>语句的基本语法是：</p>
<pre><code>INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
</code></pre>
<p>例如，我们向<code>students</code>表插入一条新记录，先列举出需要插入的字段名称，然后在<code>VALUES</code>子句中依次写出对应字段的值：</p>
<pre><code class="language-x-sql">-- 添加一条新记录
----
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);
-- 查询并观察结果:
SELECT * FROM students;
</code></pre>
<p>注意到我们并没有列出<code>id</code>字段，也没有列出<code>id</code>字段对应的值，这是因为<code>id</code>字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在<code>INSERT</code>语句中也可以不出现。</p>
<p>要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。也就是说，可以写<code>INSERT INTO students (score, gender, name, class_id) ...</code>，但是对应的<code>VALUES</code>就得变成<code>(80, 'M', '大牛', 2)</code>。</p>
<p>还可以一次性添加多条记录，只需要在<code>VALUES</code>子句中指定多个记录值，每个记录是由<code>(...)</code>包含的一组值：</p>
<pre><code class="language-x-sql">-- 一次性添加多条新记录
----
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);
SELECT * FROM students;
</code></pre>
<h3>小结</h3>
<p>使用<code>INSERT</code>，我们就可以一次向一个表中插入一条或多条记录。</p>
</div><h1 style="text-align:center">UPDATE</h1><div class="x-wiki-content x-main-content"><p>如果要更新数据库表中的记录，我们就必须使用<code>UPDATE</code>语句。</p>
<p><img alt="update-sql" src="./files/attachments/1217854943153408/l.jpg"/></p>
<p><code>UPDATE</code>语句的基本语法是：</p>
<pre><code>UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
</code></pre>
<p>例如，我们想更新<code>students</code>表<code>id=1</code>的记录的<code>name</code>和<code>score</code>这两个字段，先写出<code>UPDATE students SET name='大牛', score=66</code>，然后在<code>WHERE</code>子句中写出需要更新的行的筛选条件<code>id=1</code>：</p>
<pre><code class="language-x-sql">-- 更新id=1的记录
----
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;
</code></pre>
<p>注意到<code>UPDATE</code>语句的<code>WHERE</code>条件和<code>SELECT</code>语句的<code>WHERE</code>条件其实是一样的，因此完全可以一次更新多条记录：</p>
<pre><code class="language-x-sql">-- 更新id=5,6,7的记录
----
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;
</code></pre>
<p>在<code>UPDATE</code>语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：</p>
<pre><code class="language-x-sql">-- 更新score<80的记录
----
UPDATE students SET score=score+10 WHERE score<80;