-
Notifications
You must be signed in to change notification settings - Fork 56
/
sql数据库入门.html
1713 lines (1541 loc) · 79.5 KB
/
sql数据库入门.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
<!DOCTYPE html>
<html>
<head>
<!-- 2016-02-18 四 20:59 -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>sql数据库入门(sqlite3)</title>
<meta name="generator" content="Org-mode">
<meta name="author" content="万泽(德山书生)">
<meta name="description" content="制作者邮箱：[email protected]"
>
<style type="text/css">
body {
padding-right: 30px;
padding-left: 30px;
margin-right: auto;
margin-left: auto;
}
@media (min-width: 768px) {
body {
width: 732px;
}
}
@media (min-width: 992px) {
body {
width: 948px;
}
}
@media (min-width: 1200px) {
body {
width: 1140px;
}
}
.title {
display: block;
text-align: center;
padding: 10px;
}
.center-block {
display: block;
margin-left: auto;
margin-right: auto;
}
.underline{
text-decoration: underline;
}
#content {
display: block;
margin-left: 10%;
margin-right: 10%;
}
code {
padding: 2px 4px;
color: #c7254e;
background-color: #f9f2f4;
border-radius: 4px;
}
pre {
display: block;
padding: 9.5px;
margin: 0 0 10px;
font-size: 13px;
line-height: 1.42857143;
color: #333;
word-break: break-all;
/*word-wrap: break-word;*/
word-wrap: normal; /* horizontal scrolling */
background-color: #f5f5f5;
border: 1px solid #ccc;
border-radius: 4px;
}
blockquote {
padding: 10px 20px;
margin: 0 0 20px;
font-size: 17.5px;
border-left: 5px solid #eee;
}
p{
text-indent:2em;
line-height:150%;
}
li, dt{
margin-top: 0.5em;
margin-bottom: 0.5em;
}
video{
width: 95%;
margin-left: auto;
margin-right: auto;
}
figure p{
text-indent:0em;
}
img{
max-width: 95%;
}
figure{
text-align: center;
}
/* class */
.framed{
max-width:700px;
border:1px solid ;
padding: 1em;
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
box-sizing: border-box;
}
.notecard{
width: 320px;
position:relative;
right: -215px;
padding: 1em;
margin:0 auto;
border: solid 1px;
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
box-sizing: border-box;
}
/*
table
*/
table {
border-collapse: collapse;
border-spacing: 0;
margin: 16px 0;
empty-cells: show;
border: 1px solid #ccc;
width: 100%;
display: table;
}
table tr {
border-bottom: 1px solid #ddd;
}
table th,table td{
padding:8px;
}
table tr:nth-child(even) {
background-color: #f1f1f1;
}
.highlight .hll { background-color: #ffffcc }
.highlight { background: #f8f8f8; }
.highlight .c { color: #8f5902; font-style: italic } /* Comment */
.highlight .err { color: #a40000; border: 1px solid #ef2929 } /* Error */
.highlight .g { color: #000000 } /* Generic */
.highlight .k { color: #204a87; font-weight: bold } /* Keyword */
.highlight .l { color: #000000 } /* Literal */
.highlight .n { color: #000000 } /* Name */
.highlight .o { color: #ce5c00; font-weight: bold } /* Operator */
.highlight .x { color: #000000 } /* Other */
.highlight .p { color: #000000; font-weight: bold } /* Punctuation */
.highlight .ch { color: #8f5902; font-style: italic } /* Comment.Hashbang */
.highlight .cm { color: #8f5902; font-style: italic } /* Comment.Multiline */
.highlight .cp { color: #8f5902; font-style: italic } /* Comment.Preproc */
.highlight .cpf { color: #8f5902; font-style: italic } /* Comment.PreprocFile */
.highlight .c1 { color: #8f5902; font-style: italic } /* Comment.Single */
.highlight .cs { color: #8f5902; font-style: italic } /* Comment.Special */
.highlight .gd { color: #a40000 } /* Generic.Deleted */
.highlight .ge { color: #000000; font-style: italic } /* Generic.Emph */
.highlight .gr { color: #ef2929 } /* Generic.Error */
.highlight .gh { color: #000080; font-weight: bold } /* Generic.Heading */
.highlight .gi { color: #00A000 } /* Generic.Inserted */
.highlight .go { color: #000000; font-style: italic } /* Generic.Output */
.highlight .gp { color: #8f5902 } /* Generic.Prompt */
.highlight .gs { color: #000000; font-weight: bold } /* Generic.Strong */
.highlight .gu { color: #800080; font-weight: bold } /* Generic.Subheading */
.highlight .gt { color: #a40000; font-weight: bold } /* Generic.Traceback */
.highlight .kc { color: #204a87; font-weight: bold } /* Keyword.Constant */
.highlight .kd { color: #204a87; font-weight: bold } /* Keyword.Declaration */
.highlight .kn { color: #204a87; font-weight: bold } /* Keyword.Namespace */
.highlight .kp { color: #204a87; font-weight: bold } /* Keyword.Pseudo */
.highlight .kr { color: #204a87; font-weight: bold } /* Keyword.Reserved */
.highlight .kt { color: #204a87; font-weight: bold } /* Keyword.Type */
.highlight .ld { color: #000000 } /* Literal.Date */
.highlight .m { color: #0000cf; font-weight: bold } /* Literal.Number */
.highlight .s { color: #4e9a06 } /* Literal.String */
.highlight .na { color: #c4a000 } /* Name.Attribute */
.highlight .nb { color: #204a87 } /* Name.Builtin */
.highlight .nc { color: #000000 } /* Name.Class */
.highlight .no { color: #000000 } /* Name.Constant */
.highlight .nd { color: #5c35cc; font-weight: bold } /* Name.Decorator */
.highlight .ni { color: #ce5c00 } /* Name.Entity */
.highlight .ne { color: #cc0000; font-weight: bold } /* Name.Exception */
.highlight .nf { color: #000000 } /* Name.Function */
.highlight .nl { color: #f57900 } /* Name.Label */
.highlight .nn { color: #000000 } /* Name.Namespace */
.highlight .nx { color: #000000 } /* Name.Other */
.highlight .py { color: #000000 } /* Name.Property */
.highlight .nt { color: #204a87; font-weight: bold } /* Name.Tag */
.highlight .nv { color: #000000 } /* Name.Variable */
.highlight .ow { color: #204a87; font-weight: bold } /* Operator.Word */
.highlight .w { color: #f8f8f8; text-decoration: underline } /* Text.Whitespace */
.highlight .mb { color: #0000cf; font-weight: bold } /* Literal.Number.Bin */
.highlight .mf { color: #0000cf; font-weight: bold } /* Literal.Number.Float */
.highlight .mh { color: #0000cf; font-weight: bold } /* Literal.Number.Hex */
.highlight .mi { color: #0000cf; font-weight: bold } /* Literal.Number.Integer */
.highlight .mo { color: #0000cf; font-weight: bold } /* Literal.Number.Oct */
.highlight .sb { color: #4e9a06 } /* Literal.String.Backtick */
.highlight .sc { color: #4e9a06 } /* Literal.String.Char */
.highlight .sd { color: #8f5902; font-style: italic } /* Literal.String.Doc */
.highlight .s2 { color: #4e9a06 } /* Literal.String.Double */
.highlight .se { color: #4e9a06 } /* Literal.String.Escape */
.highlight .sh { color: #4e9a06 } /* Literal.String.Heredoc */
.highlight .si { color: #4e9a06 } /* Literal.String.Interpol */
.highlight .sx { color: #4e9a06 } /* Literal.String.Other */
.highlight .sr { color: #4e9a06 } /* Literal.String.Regex */
.highlight .s1 { color: #4e9a06 } /* Literal.String.Single */
.highlight .ss { color: #4e9a06 } /* Literal.String.Symbol */
.highlight .bp { color: #3465a4 } /* Name.Builtin.Pseudo */
.highlight .vc { color: #000000 } /* Name.Variable.Class */
.highlight .vg { color: #000000 } /* Name.Variable.Global */
.highlight .vi { color: #000000 } /* Name.Variable.Instance */
.highlight .il { color: #0000cf; font-weight: bold } /* Literal.Number.Integer.Long */
</style>
</head>
<body>
<div id="content">
<header>
<h1 class="title">sql数据库入门(sqlite3)</h1>
</header><nav id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgheadline3">1. 前言</a>
<ul>
<li><a href="#orgheadline1">1.1. 基本术语</a></li>
<li><a href="#orgheadline2">1.2. 安装sqlite3</a></li>
</ul>
</li>
<li><a href="#orgheadline21">2. 第一个例子</a>
<ul>
<li><a href="#orgheadline4">2.1. 新建一个表格</a></li>
<li><a href="#orgheadline5">2.2. 字段数据类型</a></li>
<li><a href="#orgheadline9">2.3. 字段约束词</a>
<ul>
<li><a href="#orgheadline6">2.3.1. PRIMARY KEY</a></li>
<li><a href="#orgheadline7">2.3.2. NOT NULL</a></li>
<li><a href="#orgheadline8">2.3.3. DEFAULT</a></li>
</ul>
</li>
<li><a href="#orgheadline10">2.4. 更改表格属性</a></li>
<li><a href="#orgheadline11">2.5. 删除表格</a></li>
<li><a href="#orgheadline12">2.6. 插入记录</a></li>
<li><a href="#orgheadline13">2.7. 删除记录</a></li>
<li><a href="#orgheadline14">2.8. 查看当前日期</a></li>
<li><a href="#orgheadline15">2.9. 检索单个列</a></li>
<li><a href="#orgheadline16">2.10. 检索多个列</a></li>
<li><a href="#orgheadline17">2.11. 排序</a></li>
<li><a href="#orgheadline18">2.12. 按多个列排序</a></li>
<li><a href="#orgheadline19">2.13. 降序排序</a></li>
<li><a href="#orgheadline20">2.14. 过滤数据</a></li>
</ul>
</li>
<li><a href="#orgheadline29">3. 第二个例子</a>
<ul>
<li><a href="#orgheadline22">3.1. 创建表格</a></li>
<li><a href="#orgheadline23">3.2. 外键引用</a></li>
<li><a href="#orgheadline24">3.3. 主键声明的另一方法</a></li>
<li><a href="#orgheadline25">3.4. 其他sql语句类型声明有时不修改也可</a></li>
<li><a href="#orgheadline26">3.5. check约束模拟mysql的enum类型</a></li>
<li><a href="#orgheadline27">3.6. 插入数据</a></li>
<li><a href="#orgheadline28">3.7. 演示例子的补充信息</a></li>
</ul>
</li>
<li><a href="#orgheadline30">4. 简单的SQL查询</a></li>
<li><a href="#orgheadline32">5. 联接</a>
<ul>
<li><a href="#orgheadline31">5.1. 内部联接</a></li>
</ul>
</li>
<li><a href="#orgheadline36">6. 视图</a>
<ul>
<li><a href="#orgheadline33">6.1. 新建视图</a></li>
<li><a href="#orgheadline34">6.2. 删除视图</a></li>
<li><a href="#orgheadline35">6.3. 更新视图</a></li>
</ul>
</li>
<li><a href="#orgheadline38">7. 附录</a>
<ul>
<li><a href="#orgheadline37">7.1. 参考资料</a></li>
</ul>
</li>
</ul>
</div>
</nav>
<div class="outline-2">
<h2 id="orgheadline3">前言</h2>
<div class="outline-text-2" id="text-1">
<p>
本文主要介绍SQL数据库的最核心的基础知识，用sqlite3作为实验对象。postgresql和mysql则另外再撰文分析。
</p>
</div>
<div class="outline-3">
<h3 id="orgheadline1">基本术语</h3>
<div class="outline-text-3" id="text-1-1">
<p>
数据库（database）简单的理解就是一个文件柜。然后DBMS（database management system）是数据库管理系统。而所谓的 <strong>table</strong> 表你可以将其看作文件柜里的一个结构化了的文件。在表格里面的数据都是 <span class="underline">按行存储</span> 的，这个要在头脑中牢记。然后一行数据称之为一个 <strong>记录</strong> 。SQL（Structured Query Language）的意思是结构化查询语言。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline2">安装sqlite3</h3>
<div class="outline-text-3" id="text-1-2">
<p>
在ubuntu下可以简单的用apt-get命令来安装：
</p>
<div class="highlight"><pre><span></span>sudo apt-get install sqlite3
</pre></div>
<p>
sqlite3的配置没什么好说的，如果你在终端输入 <code>sqlite3</code> ，应该就能够进去了。
</p>
</div>
</div>
</div>
<div class="outline-2">
<h2 id="orgheadline21">第一个例子</h2>
<div class="outline-text-2" id="text-2">
<p>
sqlite3和postgresql不同，其没有客户端/服务器的概念，就是直接的文件管理，sqlite3的数据库就是一个文件，然后如果连接到某个不存在的文件，就会自动创建对应的数据库文件。删除数据库当然就是删除数据库文件即可，这一块sqlite3很简单，比如说你如下运行：
</p>
<div class="highlight"><pre><span></span>sqlite3 mydb
</pre></div>
<p>
现在我们往这个数据库里面加入一点东西:
</p>
<pre>
wanze@wanze-ubuntu64:~/桌面$ sqlite3 mydb
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table mytable(id integer primary key, name text);
sqlite> insert into mytable(id,name) values(1,"Micheal");
sqlite> select * from mytable;
1|Micheal
sqlite> .table
mytable
sqlite> .database
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/wanze/桌面/mydb
sqlite> .header on
sqlite> select * from mytable;
id|name
1|Micheal
</pre>
<p>
这个例子首先连接之前创建的mydb数据库，然后通过CREATE TABLE语句来创建database数据库里面的一个表，接下来用INSERT INTO语句来给某个表插入一些数据，然后用SELECT语句来查看这些数据。
</p>
<p>
然后 <code>.database</code> 列出当前连接的数据库信息， <code>.table</code> 列出当前表格的信息， <code>.header on</code> 显示SQL表格头， <code>.quit</code> 退出sqlite3命令行， 你还可以通过 <code>.help</code> 来查看更多相关信息。
</p>
</div>
<div class="outline-3">
<h3 id="orgheadline4">新建一个表格</h3>
<div class="outline-text-3" id="text-2-1">
<p>
如下所示就是创建表格的命令格式:
</p>
<pre>
CREATE TABLE mytable(
id INTEGER PRIMARY KEY,
name TEXT);
</pre>
<p>
这里的mytable就是具体创建的表格名字，然后接下来每一行定义了具体表格的某一个字段或者说某一列。第一个是字段的名字，第二个是字段的数据类型定义，后面可选的还可以跟上其他一些约束词。下面先就字段的数据类型做出一些说明。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline5">字段数据类型</h3>
<div class="outline-text-3" id="text-2-2">
<p>
按照sqlite3官方文档的 <a href="https://www.sqlite.org/datatype3.html">介绍</a> ，其就支持五种数据类型：
</p>
<dl class="org-dl">
<dt>NULL</dt><dd>空值</dd>
<dt>INTEGER</dt><dd>整型</dd>
<dt>REAL</dt><dd>浮点型</dd>
<dt>TEXT</dt><dd>字符串型</dd>
<dt>BLOB</dt><dd>字节流型</dd>
</dl>
<p>
然后sqlite3关于你的类型声明字符串还建立了一套语法糖规则，具体语法糖规则如下所示:
</p>
<ol class="org-ol">
<li>如果没有类型声明，则视为none affinity;</li>
<li>如果在声明字符串中看到"int"（不区分大小写）这个子字符串，那么就视为integer affinity；</li>
<li>继续，接下来如果找到"char"或者"clob"或者"text"，则视为text affinity；所以varchar(80)会被简单视为text affinity;</li>
<li>继续，接下来如果找到"blob"，则视为blob，如果没数据类型声明，则视为none affinity;</li>
<li>继续，接下来如果找到"real"或者"floa"或者"doub"，则视为float affinity;</li>
<li>然后其他的都视为numeric affinity。</li>
</ol>
<p>
这里什么affinity是sqlite特有的概念，比如text affinity和前面的内置TEXT字符串型是不同的，其可能对应的是NULL，TEXT或BLOB。然后如果输入的数值则会自动将其转换成为字符串型。然后NUMERIC会自动分配成为INTEGER或REAL型，如果输入的字符串还负责转化。等等，总之我们在心里知道sqlite3在类型声明上是很灵活的就行了，具体使用还是严格按照自己喜欢的一套类型声明即可，比如这五个: <strong>null int float text blob</strong> 或者 <strong>null integer float varchar blob</strong> 等等。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline9">字段约束词</h3>
<div class="outline-text-3" id="text-2-3">
<p>
请看下面这个例子:
</p>
<pre>
sqlite> CREATE TABLE products(
...> id int PRIMARY KEY,
...> name text NOT NULL,
...> quantity int NOT NULL DEFAULT 1,
...> price real NOT NULL);
</pre>
</div>
<div class="outline-4">
<h4 id="orgheadline6">PRIMARY KEY</h4>
<div class="outline-text-4" id="text-2-3-1">
<p>
一个表格只能有一个PRIMARY KEY，被PRIMARY KEY约束的字段值必须唯一且不为空，从而使其能够成为本表格中各个记录的唯一标识。表格中可以有一个列或者几个列被选定为primary key。值得一提的是 <code>integer primary key</code> 自动有了自动分配的属性，也就是大家清楚的id那一列，即使不赋值，也会自动添加。
</p>
</div>
</div>
<div class="outline-4">
<h4 id="orgheadline7">NOT NULL</h4>
<div class="outline-text-4" id="text-2-3-2">
<p>
约束该字段不可取空值，也就是该字段必须赋值的意思。
</p>
</div>
</div>
<div class="outline-4">
<h4 id="orgheadline8">DEFAULT</h4>
<div class="outline-text-4" id="text-2-3-3">
<p>
指定该字段的默认值。
</p>
</div>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline10">更改表格属性</h3>
<div class="outline-text-3" id="text-2-4">
<p>
sqlite3更改表格属性的功能是很有限的，就只有两个，一个是更改表格名字，还一个是新建一个字段。所以sqlite3主要还是靠CREATE TABLE的时候就把各个字段属性设置好。具体使用就是使用 <code>ALTER TABLE</code> 语句，如下所示:
</p>
<pre>
ALTER TABLE tablename RENAME TO new_tablename;
ALTER TABLE tablename ADD COLUMN column_name column_datatype;
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline11">删除表格</h3>
<div class="outline-text-3" id="text-2-5">
<p>
删除前请慎重。
</p>
<pre>
DROP TABLE tablename;
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline12">插入记录</h3>
<div class="outline-text-3" id="text-2-6">
<p>
插入一条记录如前所示使用 <code>INSERT INTO</code> 语句，具体如下所示：
</p>
<pre>
INSERT INTO products (product_no, name, price) VALUES (1, ’Cheese’, 9.99);
</pre>
<p>
这是推荐的风格，后面圆括号跟着的列名不一定是按照顺序来，只是和后面的值一一对应，而且一条记录里面的所有列也不需要都列出来，不写的会按照默认值来处理。INSERT INTO语句是通用的。
</p>
<p>
如果只是需要简单插入新的一行的数据那么可以直接使用之前的insert into语句。
</p>
<pre>
sqlite> insert into mytable(age) values(6);
sqlite> select * from mytable;
id name age
---------- ---------- ----------
1 Alice
2 Betty
3 Cassie
4 Doris
5 Emily
6 Abby
7 Bella
8 6
</pre>
<p>
不过这并不满足这里的要求，下面的语句用于更新某个特定的表格的数据：
</p>
<pre>
sqlite> update mytable set age=18 where id =1;
sqlite> select * from mytable;
id name age
---------- ---------- ----------
1 Alice 18
2 Betty
3 Cassie
4 Doris
5 Emily
6 Abby
7 Bella
8 6
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline13">删除记录</h3>
<div class="outline-text-3" id="text-2-7">
<pre>
DELETE FROM products WHERE price = 10;
DELETE FROM products;
</pre>
<p>
注意：第二个语句表内所有记录都将被删除！ DELETE FROM 语句是通用的。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline14">查看当前日期</h3>
<div class="outline-text-3" id="text-2-8">
<pre>
sqlite> SELECT current_date;
2015-06-16
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline15">检索单个列</h3>
<div class="outline-text-3" id="text-2-9">
<p>
为了后面讲解方便，这里根据前面的第一个例子简单创建一个数据库，具体代码如下：
</p>
<pre>
~$ sqlite3 test.db
sqlite> CREATE TABLE mytable(id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO mytable(id,name) values(1,'Alice');
sqlite> INSERT INTO mytable(id,name) values(2,'Betty');
sqlite> INSERT INTO mytable(id,name) values(3,'Cassie');
sqlite> INSERT INTO mytable(id,name) values(4,'Doris');
sqlite> INSERT INTO mytable(id,name) values(5,'Emily');
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM mytable;
id name
---------- ----------
1 Alice
2 Betty
3 Cassie
4 Doris
5 Emily
</pre>
<p>
检索单个列的语法如下：
</p>
<pre>
sqlite> SELECT id FROM mytable;
id
----------
1
2
3
4
5
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline16">检索多个列</h3>
<div class="outline-text-3" id="text-2-10">
<p>
多个列就是写上多个列名，列名之间用逗号隔开。检索所有列就是列名用通配符"*"来匹配所有列。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline17">排序</h3>
<div class="outline-text-3" id="text-2-11">
<p>
用SELECT语句默认情况是没有排序的，如果需要排序则需要使用ORDER BY字句。现在又插入几个新名字：
</p>
<pre>
sqlite> INSERT INTO mytable(id,name) values(6,'Abby');
sqlite> INSERT INTO mytable(id,name) values(7,'Bella');
</pre>
<p>
如果我们需要输入结果按照name来排序则：
</p>
<pre>
sqlite> SELECT id,name FROM mytable ORDER BY name;
id name
---------- ----------
6 Abby
1 Alice
7 Bella
2 Betty
3 Cassie
4 Doris
5 Emily
</pre>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline18">按多个列排序</h3>
<div class="outline-text-3" id="text-2-12">
<p>
首先更新之前的表格的一些数据。
</p>
<pre>
sqlite> ALTER TABLE mytable ADD COLUMN age int;
sqlite> UPDATE mytable set age=18 WHERE id=1;
sqlite> update mytable set age=20 where id =2;
sqlite> update mytable set age=6 where id =3;
sqlite> update mytable set age=25 where id =4;
sqlite> update mytable set age=30 where id =5;
sqlite> update mytable set age=66 where id =6;
sqlite> update mytable set age=20 where id =7;
sqlite> INSERT INTO mytable(id,name,age) values(8,'Alice',20);
sqlite> SELECT * FROM mytable;
id name age
---------- ---------- ----------
1 Alice 18
2 Betty 20
3 Cassie 6
4 Doris 25
5 Emily 30
6 Abby 66
7 Bella 20
8 Alice 20
</pre>
<p>
现在开始按多个列排序：
</p>
<pre>
sqlite> SELECT * FROM mytable ORDER BY name , age;
id name age
---------- ---------- ----------
6 Abby 66
1 Alice 18
8 Alice 20
7 Bella 20
2 Betty 20
3 Cassie 6
4 Doris 25
5 Emily 30
</pre>
<p>
我们看到首先按name排序，如果名字相同则按age排序。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline19">降序排序</h3>
<div class="outline-text-3" id="text-2-13">
<p>
ORDER BY字句默认排序是升序，如果想要其为降序则使用DESC关键词，如下所示:
</p>
<pre>
sqlite> SELECT * FROM mytable ORDER BY name , age DESC;
id name age
---------- ---------- ----------
6 Abby 66
8 Alice 20
1 Alice 18
7 Bella 20
2 Betty 20
3 Cassie 6
4 Doris 25
5 Emily 30
</pre>
<p>
DESC关键词要放在想要降序排序的列的后面。如果想要多个列降序排序，则那些列 <span class="underline">都要加上DESC关键词</span> 。
</p>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline20">过滤数据</h3>
<div class="outline-text-3" id="text-2-14">
<p>
SQL用WHERE字句来达到查询时过滤数据的功能，如果同时有WHERE字句和ORDER BY字句，则ORDER BY字句要放在后面。
</p>
<p>
一个简单的例子如下所示:
</p>
<pre>
sqlite> SELECT * FROM mytable WHERE age < 30;
id name age
---------- ---------- ----------
1 Alice 18
2 Betty 20
3 Cassie 6
4 Doris 25
7 Bella 20
8 Alice 20
</pre>
<p>
如果后面的值是字符串则需要加上单引号，如: 'string' ，这些比较符号的含义都是一目了然的，如: = , <> , != , < , <= , !< , > , >= ,!> 。此外还有 <strong>BETWEEN</strong> 在两个值之间， <strong>IS NULL</strong> 为NULL值。
</p>
<p>
BETWEEN的使用如下:
</p>
<pre>
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
</pre>
<p>
NULL 无值，它与字段包含0，空字符串等不同。 <strong>IS NULL</strong> 用法如下:
</p>
<pre>
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
</pre>
</div>
</div>
</div>
<div class="outline-2">
<h2 id="orgheadline29">第二个例子</h2>
<div class="outline-text-2" id="text-3">
<p>
第二个例子将更加大型和更具现实意义。我们将通过创建一个sql文件，刷入sqlite3命令的形式来创建这个数据库。
</p>
<div class="highlight"><pre><span></span>sqlite3 sqlite3_learning_example.db < sqlite3_learning_example.sql
</pre></div>
</div>
<div class="outline-3">
<h3 id="orgheadline22">创建表格</h3>
<div class="outline-text-3" id="text-3-1">
<div class="highlight"><pre><span></span><span class="go">create table if not exists department</span>
<span class="go"> (dept_id int primary key,</span>
<span class="go"> name varchar not null</span>
<span class="go"> );</span>
</pre></div>
<p>
sqlite3的 <code>int primary key</code> 一起是个约束词，这样写就不用写not null或者autoincrement了，然后sqlite3和mysql不一样，其约束词就是直接跟在列名后面的。
</p>
<div class="highlight"><pre><span></span><span class="go">create table if not exists branch</span>
<span class="go"> (branch_id int primary key,</span>
<span class="go"> name varchar not null,</span>
<span class="go"> address varchar,</span>
<span class="go"> city varchar,</span>
<span class="go"> state varchar,</span>
<span class="go"> zip varchar</span>
<span class="go"> );</span>
</pre></div>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline23">外键引用</h3>
<div class="outline-text-3" id="text-3-2">
<p>
外键引用在sqlite3中必须加上这一行设置:
</p>
<pre>
PRAGMA foreign_keys = ON;
</pre>
<p>
然后后面加入外键引用的语法和mysql有点类似，除了没有constraint pk_product_type这一描述外。然后sqlite3里面并没有 <strong>date</strong> 类型的，其内部会自动处理为text,int或real等类型。但我们在声明的时候还是可以这样写的，然后值得一提的是sqlite3里面有一些date相关函数支持。
</p>
<p>
关于外键引用更多信息请参看官方文档的 <a href="https://www.sqlite.org/foreignkeys.html">这里</a> 。
</p>
<div class="highlight"><pre><span></span><span class="go">PRAGMA foreign_keys = ON;</span>
<span class="go">create table if not exists employee</span>
<span class="go"> (emp_id int primary key,</span>
<span class="go"> fname varchar not null,</span>
<span class="go"> lname varchar not null,</span>
<span class="go"> start_date date not null,</span>
<span class="go"> end_date date,</span>
<span class="go"> superior_emp_id int ,</span>
<span class="go"> dept_id int ,</span>
<span class="go"> title varchar,</span>
<span class="go"> assigned_branch_id int ,</span>
<span class="go"> foreign key (superior_emp_id) references employee (emp_id),</span>
<span class="go"> foreign key (dept_id) references department (dept_id),</span>
<span class="go"> foreign key (assigned_branch_id) references branch (branch_id)</span>
<span class="go"> );</span>
</pre></div>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline24">主键声明的另一方法</h3>
<div class="outline-text-3" id="text-3-3">
<p>
这里演示了主键声明的另一方法，和mysql语句很接近了。
</p>
<div class="highlight"><pre><span></span><span class="go">create table if not exists product_type</span>
<span class="go"> (product_type_cd varchar not null,</span>
<span class="go"> name varchar not null,</span>
<span class="go"> primary key (product_type_cd)</span>
<span class="go"> );</span>
</pre></div>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline25">其他sql语句类型声明有时不修改也可</h3>
<div class="outline-text-3" id="text-3-4">
<p>
比如在下面语句中 <code>varchar(10)</code> 这种写法是mysql里面的，我们在这里没有将其修改为 <code>varchar</code> 或 <code>text</code> ，因为前面说过sqlite3有很灵活的语法糖规则，直接输入 <code>varchar(10)</code> 和改成 <code>varchar</code> 没有本质区别，所以我们在这里可以图省心就不修改了。
</p>
<div class="highlight"><pre><span></span><span class="go">create table if not exists product</span>
<span class="go">(product_cd varchar(10) not null,</span>
<span class="go"> name varchar(50) not null,</span>
<span class="go"> product_type_cd varchar(10) not null,</span>
<span class="go"> date_offered date,</span>
<span class="go"> date_retired date,</span>
<span class="go"> foreign key (product_type_cd)</span>
<span class="go"> references product_type (product_type_cd),</span>
<span class="go"> primary key (product_cd)</span>
<span class="go">);</span>
</pre></div>
</div>
</div>
<div class="outline-3">
<h3 id="orgheadline26">check约束模拟mysql的enum类型</h3>
<div class="outline-text-3" id="text-3-5">
<p>
mysql提供了enum类型，在sqlite3中没有，要达到类似的效果只有如下用 <strong>check</strong> 约束。 参考了 <a href="http://stackoverflow.com/questions/5299267/how-to-create-enum-type-in-sqlite">这个网页</a> 。然后这里mysql的integer类型声明也没改了，等同于int。
</p>
<div class="highlight"><pre><span></span><span class="go">create table if not exists customer</span>
<span class="go">(cust_id integer not null ,</span>
<span class="go"> fed_id varchar(12) not null,</span>
<span class="go"> cust_type_cd not null check(cust_type_cd in ('I','B')),</span>
<span class="go"> address varchar(30),</span>
<span class="go"> city varchar(20),</span>
<span class="go"> state varchar(20),</span>
<span class="go"> postal_code varchar(10),</span>
<span class="go"> primary key (cust_id)</span>
<span class="go">);</span>
</pre></div>
<p>
然后我们继续:
</p>
<div class="highlight"><pre><span></span><span class="go">create table if not exists individual</span>
<span class="go">(cust_id integer not null,</span>
<span class="go"> fname varchar(30) not null,</span>
<span class="go"> lname varchar(30) not null,</span>
<span class="go"> birth_date date,</span>
<span class="go"> foreign key (cust_id)</span>
<span class="go"> references customer (cust_id),</span>
<span class="go"> primary key (cust_id)</span>
<span class="go">);</span>
<span class="go">create table if not exists business</span>
<span class="go">(cust_id integer not null,</span>
<span class="go"> name varchar(40) not null,</span>
<span class="go"> state_id varchar(10) not null,</span>
<span class="go"> incorp_date date,</span>
<span class="go"> foreign key (cust_id)</span>
<span class="go"> references customer (cust_id),</span>
<span class="go"> primary key (cust_id)</span>
<span class="go">);</span>
<span class="go">create table if not exists officer</span>
<span class="go">(officer_id smallint not null ,</span>
<span class="go"> cust_id integer not null,</span>
<span class="go"> fname varchar(30) not null,</span>
<span class="go"> lname varchar(30) not null,</span>
<span class="go"> title varchar(20),</span>
<span class="go"> start_date date not null,</span>
<span class="go"> end_date date,</span>
<span class="go"> foreign key (cust_id)</span>
<span class="go"> references business (cust_id),</span>
<span class="go"> primary key (officer_id)</span>
<span class="go">);</span>
<span class="go">create table if not exists account</span>
<span class="go">(account_id integer not null ,</span>
<span class="go"> product_cd varchar(10) not null,</span>
<span class="go"> cust_id integer not null,</span>
<span class="go"> open_date date not null,</span>
<span class="go"> close_date date,</span>
<span class="go"> last_activity_date date,</span>
<span class="go"> status check(status in ('ACTIVE','CLOSED','FROZEN')),</span>
<span class="go"> open_branch_id smallint ,</span>
<span class="go"> open_emp_id smallint ,</span>
<span class="go"> avail_balance float(10,2),</span>
<span class="go"> pending_balance float(10,2),</span>
<span class="go"> foreign key (product_cd)</span>
<span class="go"> references product (product_cd),</span>
<span class="go"> foreign key (cust_id)</span>
<span class="go"> references customer (cust_id),</span>
<span class="go"> foreign key (open_branch_id)</span>
<span class="go"> references branch (branch_id),</span>
<span class="go"> foreign key (open_emp_id)</span>
<span class="go"> references employee (emp_id),</span>
<span class="go"> primary key (account_id)</span>
<span class="go">);</span>
<span class="go">create table if not exists transaction_t</span>
<span class="go">(txn_id integer not null ,</span>
<span class="go"> txn_date datetime not null,</span>
<span class="go"> account_id integer not null,</span>