-
Notifications
You must be signed in to change notification settings - Fork 1
/
SQL入门语法总结.html
2305 lines (2054 loc) · 155 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 lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.2">
<link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
<link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
<link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
<link rel="mask-icon" href="/images/logo.svg" color="#222">
<link rel="stylesheet" href="/css/main.css">
<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">
<link rel="stylesheet" href="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.css">
<script id="hexo-configurations">
var NexT = window.NexT || {};
var CONFIG = {"hostname":"leeyuxun.github.io","root":"/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":true,"show_result":true,"style":"mac"},"back2top":{"enable":true,"sidebar":true,"scrollpercent":true},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":true,"mediumzoom":false,"lazyload":false,"pangu":true,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"./public/search.xml"};
</script>
<meta name="description" content="SQL介绍 结构化查询语言。">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL入门语法总结">
<meta property="og:url" content="https://leeyuxun.github.io/SQL%E5%85%A5%E9%97%A8%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93.html">
<meta property="og:site_name" content="Leeyuxun の note">
<meta property="og:description" content="SQL介绍 结构化查询语言。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://raw.githubusercontent.com/Leeyuxun/pic-storage/main/img/20200929225450.png">
<meta property="article:published_time" content="2020-11-02T10:14:29.000Z">
<meta property="article:modified_time" content="2023-05-07T07:37:53.510Z">
<meta property="article:author" content="李钰璕">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://raw.githubusercontent.com/Leeyuxun/pic-storage/main/img/20200929225450.png">
<link rel="canonical" href="https://leeyuxun.github.io/SQL%E5%85%A5%E9%97%A8%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93.html">
<script id="page-configurations">
// https://hexo.io/docs/variables.html
CONFIG.page = {
sidebar: "",
isHome : false,
isPost : true,
lang : 'zh-CN'
};
</script>
<title>SQL入门语法总结 | Leeyuxun の note</title>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-V3499K2XZY"></script>
<script>
if (CONFIG.hostname === location.hostname) {
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-V3499K2XZY');
}
</script>
<script>
var _hmt = _hmt || [];
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?4d72a66931dff6410b32974da2e3df61";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
</script>
<noscript>
<style>
.use-motion .brand,
.use-motion .menu-item,
.sidebar-inner,
.use-motion .post-block,
.use-motion .pagination,
.use-motion .comments,
.use-motion .post-header,
.use-motion .post-body,
.use-motion .collection-header { opacity: initial; }
.use-motion .site-title,
.use-motion .site-subtitle {
opacity: initial;
top: initial;
}
.use-motion .logo-line-before i { left: initial; }
.use-motion .logo-line-after i { right: initial; }
</style>
</noscript>
<style>mjx-container[jax="SVG"] {
direction: ltr;
}
mjx-container[jax="SVG"] > svg {
overflow: visible;
}
mjx-container[jax="SVG"][display="true"] {
display: block;
text-align: center;
margin: 1em 0;
}
mjx-container[jax="SVG"][justify="left"] {
text-align: left;
}
mjx-container[jax="SVG"][justify="right"] {
text-align: right;
}
g[data-mml-node="merror"] > g {
fill: red;
stroke: red;
}
g[data-mml-node="merror"] > rect[data-background] {
fill: yellow;
stroke: none;
}
g[data-mml-node="mtable"] > line[data-line] {
stroke-width: 70px;
fill: none;
}
g[data-mml-node="mtable"] > rect[data-frame] {
stroke-width: 70px;
fill: none;
}
g[data-mml-node="mtable"] > .mjx-dashed {
stroke-dasharray: 140;
}
g[data-mml-node="mtable"] > .mjx-dotted {
stroke-linecap: round;
stroke-dasharray: 0,140;
}
g[data-mml-node="mtable"] > svg {
overflow: visible;
}
[jax="SVG"] mjx-tool {
display: inline-block;
position: relative;
width: 0;
height: 0;
}
[jax="SVG"] mjx-tool > mjx-tip {
position: absolute;
top: 0;
left: 0;
}
mjx-tool > mjx-tip {
display: inline-block;
padding: .2em;
border: 1px solid #888;
font-size: 70%;
background-color: #F8F8F8;
color: black;
box-shadow: 2px 2px 5px #AAAAAA;
}
g[data-mml-node="maction"][data-toggle] {
cursor: pointer;
}
mjx-status {
display: block;
position: fixed;
left: 1em;
bottom: 1em;
min-width: 25%;
padding: .2em .4em;
border: 1px solid #888;
font-size: 90%;
background-color: #F8F8F8;
color: black;
}
foreignObject[data-mjx-xml] {
font-family: initial;
line-height: normal;
overflow: visible;
}
.MathJax path {
stroke-width: 3;
}
mjx-container[display="true"] {
overflow: auto hidden;
}
mjx-container[display="true"] + br {
display: none;
}
</style></head>
<body itemscope itemtype="http://schema.org/WebPage">
<div class="container use-motion">
<div class="headband"></div>
<header class="header" itemscope itemtype="http://schema.org/WPHeader">
<div class="header-inner"><div class="site-brand-container">
<div class="site-nav-toggle">
<div class="toggle" aria-label="切换导航栏">
<span class="toggle-line toggle-line-first"></span>
<span class="toggle-line toggle-line-middle"></span>
<span class="toggle-line toggle-line-last"></span>
</div>
</div>
<div class="site-meta">
<a href="/" class="brand" rel="start">
<span class="logo-line-before"><i></i></span>
<h1 class="site-title">Leeyuxun の note</h1>
<span class="logo-line-after"><i></i></span>
</a>
<p class="site-subtitle" itemprop="description">BUPT | SCSS</p>
</div>
<div class="site-nav-right">
<div class="toggle popup-trigger">
<i class="fa fa-search fa-fw fa-lg"></i>
</div>
</div>
</div>
<nav class="site-nav">
<ul id="menu" class="main-menu menu">
<li class="menu-item menu-item-home">
<a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>
</li>
<li class="menu-item menu-item-tags">
<a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>
</li>
<li class="menu-item menu-item-categories">
<a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>
</li>
<li class="menu-item menu-item-archives">
<a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>
</li>
<li class="menu-item menu-item-links">
<a href="/links/" rel="section"><i class="fa fa-link fa-fw"></i>友链</a>
</li>
<li class="menu-item menu-item-search">
<a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
</a>
</li>
</ul>
</nav>
<div class="search-pop-overlay">
<div class="popup search-popup">
<div class="search-header">
<span class="search-icon">
<i class="fa fa-search"></i>
</span>
<div class="search-input-container">
<input autocomplete="off" autocapitalize="off"
placeholder="搜索..." spellcheck="false"
type="search" class="search-input">
</div>
<span class="popup-btn-close">
<i class="fa fa-times-circle"></i>
</span>
</div>
<div id="search-result">
<div id="no-result">
<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
</div>
</div>
</div>
</div>
</div>
</header>
<main class="main">
<div class="main-inner">
<div class="content-wrap">
<div class="content post posts-expand">
<article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
<link itemprop="mainEntityOfPage" href="https://leeyuxun.github.io/SQL%E5%85%A5%E9%97%A8%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93.html">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="/images/avatar.png">
<meta itemprop="name" content="李钰璕">
<meta itemprop="description" content="安全学习笔记">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="Leeyuxun の note">
</span>
<header class="post-header">
<h1 class="post-title" itemprop="name headline">
SQL入门语法总结
</h1>
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2020-11-02 18:14:29" itemprop="dateCreated datePublished" datetime="2020-11-02T18:14:29+08:00">2020-11-02</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2023-05-07 15:37:53" itemprop="dateModified" datetime="2023-05-07T15:37:53+08:00">2023-05-07</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/%E8%AF%AD%E6%B3%95/" itemprop="url" rel="index"><span itemprop="name">语法</span></a>
</span>
</span>
<span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
<span class="post-meta-item-icon">
<i class="fa fa-eye"></i>
</span>
<span class="post-meta-item-text">阅读次数：</span>
<span id="busuanzi_value_page_pv"></span>
</span>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h1 id="SQL介绍"><a href="#SQL介绍" class="headerlink" title="SQL介绍"></a>SQL介绍</h1><ul>
<li> 结构化查询语言。<span id="more"></span></li>
<li> RDBMS 指关系型数据库管理系统，全称 Relational Database Management System。</li>
<li> RDBMS 是 SQL 的基础，同样也是所有现代数据库系统的基础，比如 MS SQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。</li>
<li> RDBMS 中的数据存储在被称为表的数据库对象中。</li>
<li> 表是相关的数据项的集合，它由列和行组成。</li>
</ul>
<h2 id="基础语法"><a href="#基础语法" class="headerlink" title="基础语法"></a>基础语法</h2><h3 id="展示、创建、使用数据库"><a href="#展示、创建、使用数据库" class="headerlink" title="展示、创建、使用数据库"></a>展示、创建、使用数据库</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">show databases; #展示数据库</span><br><span class="line">create DATABASE dbname; #创建数据库dbname</span><br><span class="line">use dbname; #使用数据库dbname</span><br></pre></td></tr></table></figure>
<h3 id="展示、创建数据表"><a href="#展示、创建数据表" class="headerlink" title="展示、创建数据表"></a>展示、创建数据表</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">show tables; #展示数据库中的所有数据表</span><br><span class="line">#创建数据表table_name</span><br><span class="line">CREATE TABLE table_name</span><br><span class="line">(</span><br><span class="line">column_name1 data_type(size),</span><br><span class="line">column_name2 data_type(size),</span><br><span class="line">column_name3 data_type(size),</span><br><span class="line">....</span><br><span class="line">);</span><br><span class="line"># column_name 参数规定表中列的名称</span><br><span class="line"># data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）</span><br><span class="line"># size 参数规定表中列的最大长度</span><br></pre></td></tr></table></figure>
<h3 id="重要的SQL命令"><a href="#重要的SQL命令" class="headerlink" title="重要的SQL命令"></a>重要的SQL命令</h3><ul>
<li> <strong>SELECT</strong> - 从数据库中提取数据</li>
<li> <strong>UPDATE</strong> - 更新数据库中的数据</li>
<li> <strong>DELETE</strong> - 从数据库中删除数据</li>
<li> <strong>INSERT INTO</strong> - 向数据库中插入新数据</li>
<li> <strong>CREATE DATABASE</strong> - 创建新数据库</li>
<li> <strong>ALTER DATABASE</strong> - 修改数据库</li>
<li> <strong>CREATE TABLE</strong> - 创建新表</li>
<li> <strong>ALTER TABLE</strong> - 变更（改变）数据库表</li>
<li> <strong>DROP TABLE</strong> - 删除表</li>
<li> <strong>CREATE INDEX</strong> - 创建索引（搜索键）</li>
<li> <strong>DROP INDEX</strong> - 删除索引</li>
</ul>
<h3 id="SELECT语句"><a href="#SELECT语句" class="headerlink" title="SELECT语句"></a><strong>SELECT语句</strong></h3><p>——用于从数据库中选取数据。</p>
<p>结果被存储在一个结果表中，称为结果集。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name,column_name </span><br><span class="line"> FROM table_name;</span><br><span class="line">#or</span><br><span class="line">SELECT * FROM table_name;</span><br></pre></td></tr></table></figure>
<h3 id="SELECT-DISTINCT-语句"><a href="#SELECT-DISTINCT-语句" class="headerlink" title="SELECT DISTINCT 语句"></a><strong>SELECT DISTINCT 语句</strong></h3><p>——用于返回唯一不同的值。</p>
<p>将重复的列变为一列</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT DISTINCT column_name,column_name</span><br><span class="line"> FROM table_name;</span><br></pre></td></tr></table></figure>
<h3 id="WHERE子句"><a href="#WHERE子句" class="headerlink" title="WHERE子句"></a><strong>WHERE子句</strong></h3><p>——用于过滤记录</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name,column_name</span><br><span class="line"> FROM table_name</span><br><span class="line"> WHERE column_name operator value;</span><br><span class="line"># example</span><br><span class="line">SELECT * FROM Websites WHERE country='CN';</span><br><span class="line">SELECT * FROM Websites WHERE id=1;</span><br></pre></td></tr></table></figure>
<ul>
<li><p>WHERE子句中的运算符</p>
<table>
<thead>
<tr>
<th align="left">运算符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">=</td>
<td align="left">等于</td>
</tr>
<tr>
<td align="left"><></td>
<td align="left">不等于，在 SQL 的一些版本中，该操作符可被写成 !=</td>
</tr>
<tr>
<td align="left">></td>
<td align="left">大于</td>
</tr>
<tr>
<td align="left"><</td>
<td align="left">小于</td>
</tr>
<tr>
<td align="left">>=</td>
<td align="left">大于等于</td>
</tr>
<tr>
<td align="left"><=</td>
<td align="left">小于等于</td>
</tr>
<tr>
<td align="left">BETWEEN</td>
<td align="left">在某个范围内</td>
</tr>
<tr>
<td align="left">LIKE</td>
<td align="left">搜索某种模式</td>
</tr>
<tr>
<td align="left">IN</td>
<td align="left">指定针对某个列的多个可能值</td>
</tr>
</tbody></table>
</li>
<li><p>WHERE子句也可以用AND OR NOT逻辑</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 优先级 ()>not>and>or</span><br><span class="line">Select * from emp where sal > 2000 and sal < 3000;</span><br><span class="line">Select * from emp where sal > 2000 or comm > 500;</span><br><span class="line">select * from emp where not sal > 1500;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="AND-amp-OR-运算符"><a href="#AND-amp-OR-运算符" class="headerlink" title="AND & OR 运算符"></a><strong>AND & OR 运算符</strong></h3><p>——用于基于一个以上的条件对记录进行过滤</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE country='CN'</span><br><span class="line"> AND alexa > 50;</span><br><span class="line"> </span><br><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE country='USA'</span><br><span class="line"> OR country='CN';</span><br><span class="line"></span><br><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE alexa > 15</span><br><span class="line"> AND (country='CN' OR country='USA');</span><br></pre></td></tr></table></figure>
<h3 id="ORDER-BY-关键字"><a href="#ORDER-BY-关键字" class="headerlink" title="ORDER BY 关键字"></a><strong>ORDER BY 关键字</strong></h3><p>——用于对结果集进行排序</p>
<p>ORDER BY 关键字默认按照<strong>升序</strong>对记录进行排序。如果需要按照<strong>降序</strong>对记录进行排序，可以使用 DESC 关键字；</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name,column_name</span><br><span class="line"> FROM table_name</span><br><span class="line"> ORDER BY column_name,column_name ASC|DESC;</span><br><span class="line"># ORDER BY 多列的时候，先按照第一个column name排序，再按照第二个column name排序</span><br></pre></td></tr></table></figure>
<h3 id="INSERT-INTO-语句"><a href="#INSERT-INTO-语句" class="headerlink" title="INSERT INTO 语句"></a><strong>INSERT INTO 语句</strong></h3><p>——用于向表中插入新记录。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#1 无需指定要插入数据的列名，只需提供被插入的值即可,但是需要列出插入行的每一列数据</span><br><span class="line">INSERT INTO table_name</span><br><span class="line"> VALUES (value1,value2,value3,...);</span><br><span class="line">#2 需要指定列名及被插入的值</span><br><span class="line">INSERT INTO table_name (column1,column2,column3,...)</span><br><span class="line"> VALUES (value1,value2,value3,...);</span><br></pre></td></tr></table></figure>
<p>insert into select 和select into from 的区别</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">insert into scorebak select * from socre where neza='neza' #插入一行,要求表scorebak 必须存在</span><br><span class="line">select * into scorebak from score where neza='neza' #也是插入一行,要求表scorebak 不存在</span><br></pre></td></tr></table></figure>
<h3 id="UPDATE-语句"><a href="#UPDATE-语句" class="headerlink" title="UPDATE 语句"></a><strong>UPDATE 语句</strong></h3><p>——用于更新表中的记录</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">UPDATE table_name</span><br><span class="line"> SET column1=value1,column2=value2,...</span><br><span class="line"> WHERE some_column=some_value; #WHERE 子句规定哪条记录or者哪些记录需要更新。如果省略了WHERE子句，所有的记录都将被更新！</span><br></pre></td></tr></table></figure>
<h3 id="DELETE-语句"><a href="#DELETE-语句" class="headerlink" title="DELETE 语句"></a><strong>DELETE 语句</strong></h3><p>——用于删除表中的记录</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">DELETE FROM table_name</span><br><span class="line"> WHERE some_column=some_value;</span><br><span class="line"># 在不删除表的情况下，删除表中所有的行，即表结构、属性、索引将保持不变</span><br><span class="line">DELETE FROM table_name;</span><br><span class="line"> # or</span><br><span class="line">DELETE * FROM table_name;</span><br></pre></td></tr></table></figure>
<h2 id="高级语法"><a href="#高级语法" class="headerlink" title="高级语法"></a>高级语法</h2><h3 id="SELECT-TOP-LIMIT-ROWNUM-子句"><a href="#SELECT-TOP-LIMIT-ROWNUM-子句" class="headerlink" title="SELECT TOP, LIMIT, ROWNUM 子句"></a><strong>SELECT TOP, LIMIT, ROWNUM 子句</strong></h3><p>SELECT TOP 子句用于规定要返回的记录的数目，对于拥有数千条记录的大型表来说，是非常有用的。</p>
<p>**注意:**并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。</p>
<ul>
<li><p>SQL Server / MS Access 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT TOP number|percent column_name(s)</span><br><span class="line"> FROM table_name;</span><br><span class="line"># example</span><br><span class="line"># 前5行数据</span><br><span class="line">select top 5 * from table</span><br><span class="line"># 后5行数据</span><br><span class="line">select top 5 * from table order by id desc # --desc 表示降序排列 asc 表示升序</span><br></pre></td></tr></table></figure></li>
<li><p>Myslq 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name</span><br><span class="line"> LIMIT number;</span><br><span class="line"># example</span><br><span class="line">SELECT * FROM Websites LIMIT 2; # 从 "Websites" 表中选取头两条记录</span><br></pre></td></tr></table></figure></li>
<li><p>Oracle 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name</span><br><span class="line"> WHERE ROWNUM <= number;</span><br></pre></td></tr></table></figure></li>
<li><p>SQL SELECT TOP PERCENT 实例</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># Microsoft SQL Server 数据库中，从 websites 表中选取前面百分之 50 的记录</span><br><span class="line">SELECT TOP 50 PERCENT * FROM Websites;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="LIKE-操作符——用于在-WHERE-子句中搜索列中的指定模式"><a href="#LIKE-操作符——用于在-WHERE-子句中搜索列中的指定模式" class="headerlink" title="LIKE 操作符——用于在 WHERE 子句中搜索列中的指定模式"></a><strong>LIKE 操作符</strong>——用于在 WHERE 子句中搜索列中的指定模式</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name</span><br><span class="line"> WHERE column_name LIKE pattern;</span><br></pre></td></tr></table></figure>
<h3 id="SQL-通配符——用于替代字符串中的任何其他字符"><a href="#SQL-通配符——用于替代字符串中的任何其他字符" class="headerlink" title="SQL 通配符——用于替代字符串中的任何其他字符"></a><strong>SQL 通配符</strong>——用于替代字符串中的任何其他字符</h3><p>在 SQL 中，通配符与 SQL LIKE 操作符一起使用，用于搜索表中的数据。</p>
<table>
<thead>
<tr>
<th align="left">通配符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">%</td>
<td align="left">替代 0 个or多个字符</td>
</tr>
<tr>
<td align="left">_</td>
<td align="left">替代一个字符</td>
</tr>
<tr>
<td align="left">[charlist]</td>
<td align="left">字符列中的任何单一字符</td>
</tr>
<tr>
<td align="left">[^charlist] or [!charlist]</td>
<td align="left">不在字符列中的任何单一字符</td>
</tr>
</tbody></table>
<ul>
<li><p>使用 SQL [charlist] 通配符</p>
<p> MySQL 中使用 <strong>REGEXP</strong> or <strong>NOT REGEXP</strong> 运算符 (or RLIKE 和 NOT RLIKE) 来操作正则表达式。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 选取 name 以 "G"、"F" or "s" 开始的所有网站</span><br><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE name REGEXP '^[GFs]';</span><br><span class="line"># 选取 name 以 A 到 H 字母开头的网站</span><br><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE name REGEXP '^[A-H]';</span><br><span class="line"># 选取 name 不以 A 到 H 字母开头的网站</span><br><span class="line">SELECT * FROM Websites</span><br><span class="line"> WHERE name REGEXP '^[^A-H]';</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="IN-操作符"><a href="#IN-操作符" class="headerlink" title="IN 操作符"></a><strong>IN 操作符</strong></h3><p>——允许在 WHERE 子句中规定多个值</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name</span><br><span class="line"> WHERE column_name IN (value1,value2,...);</span><br></pre></td></tr></table></figure>
<h3 id="BETWEEN-操作符"><a href="#BETWEEN-操作符" class="headerlink" title="BETWEEN 操作符"></a><strong>BETWEEN 操作符</strong></h3><p>——用于选取介于两个值之间的数据范围内的值，可以是数值、文本、日期等</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name</span><br><span class="line"> WHERE column_name BETWEEN value1 AND value2; # 如果是字符串or者日期，需要用单引号括起来</span><br></pre></td></tr></table></figure>
<h3 id="别名"><a href="#别名" class="headerlink" title="别名"></a><strong>别名</strong></h3><p>——为表名称or列名称指定别名。</p>
<p>基本上，创建别名是为了让列名称的可读性更强。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 列的SQL别名语法</span><br><span class="line">SELECT column_name AS alias_name #如果列名称包含空格，要求使用双引号or方括号</span><br><span class="line"> FROM table_name;</span><br><span class="line"># 表的SQL别名语法</span><br><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table_name AS alias_name;</span><br><span class="line"># 把三个列合并成一个新列</span><br><span class="line">SELECT name, CONCAT(column1, ', ', column2, ', ', column3) AS new_cloumn</span><br><span class="line"> FROM table_name;</span><br></pre></td></tr></table></figure>
<p>在下面的情况下，使用别名很有用：</p>
<ul>
<li> 在查询中涉及超过一个表</li>
<li> 在查询中使用了函数</li>
<li> 列名称很长or者可读性差</li>
<li> 需要把两个列or者多个列结合在一起</li>
</ul>
<h3 id="连接-JOIN"><a href="#连接-JOIN" class="headerlink" title="连接(JOIN)"></a><strong>连接(JOIN)</strong></h3><p>——用于把来自两个or多个表的行结合起来</p>
<p>JOIN相关的七种用法</p>
<ul>
<li> INNER JOIN（内连接）</li>
<li> LEFT JOIN（左连接）</li>
<li> RIGHT JOIN（右连接）</li>
<li> OUTER JOIN（外连接）（mysql数据库不支持）</li>
<li> LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）</li>
<li> RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）</li>
<li> OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）</li>
</ul>
<p><img src="https://raw.githubusercontent.com/Leeyuxun/pic-storage/main/img/20200929225450.png"></p>
<ol>
<li><p><strong>INNER JOIN 关键字</strong></p>
<p> ——关键字在表中存在至少一个匹配时返回行</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> INNER JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br><span class="line"># or</span><br><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>LEFT JOIN 关键字</strong></p>
<p> ——从左表（table1）返回所有的行，如果右表（table2）中没有匹配，则结果为 NULL。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> LEFT JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br><span class="line">#or </span><br><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> LEFT OUTER JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>RIGHT JOIN 关键字</strong></p>
<p> ——从右表（table2）返回所有的行，如果左表（table1）中没有匹配，则结果为 NULL。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> RIGHT JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br><span class="line">#or </span><br><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> RIGHT OUTER JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br></pre></td></tr></table></figure></li>
<li><p><strong>FULL OUTER JOIN 关键字</strong></p>
<p> ——只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。</p>
<p> MySQL中不支持 FULL OUTER JOIN</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s)</span><br><span class="line"> FROM table1</span><br><span class="line"> FULL OUTER JOIN table2</span><br><span class="line"> ON table1.column_name=table2.column_name;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="UNION-操作符"><a href="#UNION-操作符" class="headerlink" title="UNION 操作符"></a><strong>UNION 操作符</strong></h3><p>——合并两个or多个 SELECT 语句的结果</p>
<p>注意，</p>
<ul>
<li> UNION 内部的每个 SELECT 语句必须拥有相同数量的列；</li>
<li> 列也必须拥有相似的数据类型；</li>
<li> 每个 SELECT 语句中的列的顺序必须相同；</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name(s) FROM table1</span><br><span class="line">UNION</span><br><span class="line">SELECT column_name(s) FROM table2;</span><br><span class="line"># 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。</span><br><span class="line">SELECT column_name(s) FROM table1</span><br><span class="line">UNION ALL</span><br><span class="line">SELECT column_name(s) FROM table2;</span><br></pre></td></tr></table></figure>
<h3 id="SELECT-INTO-语句"><a href="#SELECT-INTO-语句" class="headerlink" title="SELECT INTO 语句"></a><strong>SELECT INTO 语句</strong></h3><p>——从一个表复制数据，然后把数据插入到另一个新表中</p>
<p>MySQL 数据库不支持 <code>SELECT ... INTO</code> 语句，但支持 <code>INSERT INTO ... SELECT</code> </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 复制所有的列插入到新表中</span><br><span class="line">SELECT *</span><br><span class="line">INTO newtable [IN externaldb]</span><br><span class="line">FROM table1;</span><br><span class="line"># 只复制希望的列插入到新表中</span><br><span class="line">SELECT column_name(s)</span><br><span class="line">INTO newtable [IN externaldb]</span><br><span class="line">FROM table1;</span><br><span class="line"># 创建一个新的空表</span><br><span class="line">SELECT *</span><br><span class="line">INTO newtable</span><br><span class="line">FROM table1</span><br><span class="line">WHERE 1=0;</span><br></pre></td></tr></table></figure>
<h3 id="INSERT-INTO-SELECT-语句"><a href="#INSERT-INTO-SELECT-语句" class="headerlink" title="INSERT INTO SELECT 语句"></a><strong>INSERT INTO SELECT 语句</strong></h3><p>——从一个表复制数据，插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 从一个表中复制所有的列插入到另一个已存在的表中</span><br><span class="line">INSERT INTO table2</span><br><span class="line">SELECT * FROM table1;</span><br><span class="line"># 只复制希望的列插入到另一个已存在的表中</span><br><span class="line">INSERT INTO table2</span><br><span class="line">(column_name(s))</span><br><span class="line">SELECT column_name(s)</span><br><span class="line">FROM table1;</span><br></pre></td></tr></table></figure>
<h3 id="约束（Constraints）"><a href="#约束（Constraints）" class="headerlink" title="约束（Constraints）"></a><strong>约束（Constraints）</strong></h3><p>——用于规定表中的数据规则。</p>
<p>如果存在违反约束的数据行为，行为会被约束终止。</p>
<p>约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE table_name</span><br><span class="line">(</span><br><span class="line">column_name1 data_type(size) constraint_name,</span><br><span class="line">column_name2 data_type(size) constraint_name,</span><br><span class="line">column_name3 data_type(size) constraint_name,</span><br><span class="line">....</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>在 SQL 中，有如下约束：</p>
<ol>
<li><p><strong>NOT NULL</strong> - 指示某列不能存储 NULL 值。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 创建表时约束</span><br><span class="line">CREATE TABLE Persons (</span><br><span class="line"> ID int NOT NULL,</span><br><span class="line"> LastName varchar(255) NOT NULL,</span><br><span class="line"> FirstName varchar(255) NOT NULL,</span><br><span class="line"> Age int</span><br><span class="line">);</span><br><span class="line"># 对存在的表添加约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">MODIFY Age int NOT NULL;</span><br><span class="line"># 删除表中的约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">MODIFY Age int NULL;</span><br></pre></td></tr></table></figure>
</li>
<li><p><strong>UNIQUE</strong> - 保证某列的每行必须有唯一的值。</p>
<p> UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。</p>
<p> PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。</p>
<p> 每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br></pre></td><td class="code"><pre><span class="line"># mysql</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">UNIQUE (P_Id)</span><br><span class="line">)</span><br><span class="line"># SQL Server / Oracle / MS Access</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL UNIQUE,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255)</span><br><span class="line">)</span><br><span class="line"># MySQL / SQL Server / Oracle / MS Access定义多列约束</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)</span><br><span class="line">)</span><br><span class="line"># ALTER TABLE 时的 SQL UNIQUE 约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD UNIQUE (P_Id);</span><br><span class="line"># 定义多列约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);</span><br><span class="line"># 撤销 UNIQUE 约束</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP INDEX uc_PersonID</span><br><span class="line"> # SQL Server / Oracle / MS Access</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP CONSTRAINT uc_PersonID</span><br></pre></td></tr></table></figure></li>
<li><p><strong>PRIMARY KEY</strong> - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。</p>
<p> 主键必须包含唯一的值。</p>
<p> 主键列不能包含 NULL 值。</p>
<p> 每个表都应该有一个主键，并且每个表只能有一个主键。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"># CREATE TABLE 时的 SQL PRIMARY KEY 约束</span><br><span class="line"># mysql </span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">PRIMARY KEY (P_Id)</span><br><span class="line">)</span><br><span class="line"># SQL Server / Oracle / MS Access</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL PRIMARY KEY,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255)</span><br><span class="line">)</span><br><span class="line"># 定义多个列的 PRIMARY KEY 约束</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># ALTER TABLE 时的 SQL PRIMARY KEY 约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD PRIMARY KEY (P_Id)</span><br><span class="line"># 定义多列</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)</span><br><span class="line"></span><br><span class="line"># 撤销 PRIMARY KEY 约束</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP PRIMARY KEY</span><br><span class="line"> # SQL Server / Oracle / MS Access</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP CONSTRAINT pk_PersonID</span><br></pre></td></tr></table></figure></li>
<li><p><strong>FOREIGN KEY</strong> - 保证一个表中的数据匹配另一个表中的值的参照完整性。</p>
<p> 一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。</p>
<p> FOREIGN KEY 约束用于预防破坏表之间连接的行为。</p>
<p> FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"># CREATE TABLE 时的 SQL FOREIGN KEY 约束</span><br><span class="line">#mysql</span><br><span class="line">CREATE TABLE Orders</span><br><span class="line">(</span><br><span class="line">O_Id int NOT NULL,</span><br><span class="line">OrderNo int NOT NULL,</span><br><span class="line">P_Id int,</span><br><span class="line">PRIMARY KEY (O_Id),</span><br><span class="line">FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)</span><br><span class="line">)</span><br><span class="line"># SQL Server / Oracle / MS Access</span><br><span class="line">CREATE TABLE Orders</span><br><span class="line">(</span><br><span class="line">O_Id int NOT NULL PRIMARY KEY,</span><br><span class="line">OrderNo int NOT NULL,</span><br><span class="line">P_Id int FOREIGN KEY REFERENCES Persons(P_Id)</span><br><span class="line">)</span><br><span class="line"># 定义多个列的 FOREIGN KEY 约束</span><br><span class="line">CREATE TABLE Orders</span><br><span class="line">(</span><br><span class="line">O_Id int NOT NULL,</span><br><span class="line">OrderNo int NOT NULL,</span><br><span class="line">P_Id int,</span><br><span class="line">PRIMARY KEY (O_Id),</span><br><span class="line">CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)</span><br><span class="line">REFERENCES Persons(P_Id)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># ALTER TABLE 时的 SQL FOREIGN KEY 约束</span><br><span class="line">ALTER TABLE Orders</span><br><span class="line">ADD FOREIGN KEY (P_Id)</span><br><span class="line">REFERENCES Persons(P_Id)</span><br><span class="line"># 定义多个列的 FOREIGN KEY 约束</span><br><span class="line">ALTER TABLE Orders</span><br><span class="line">ADD CONSTRAINT fk_PerOrders</span><br><span class="line">FOREIGN KEY (P_Id)</span><br><span class="line">REFERENCES Persons(P_Id)</span><br><span class="line"># 撤销 FOREIGN KEY 约束</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Orders</span><br><span class="line">DROP FOREIGN KEY fk_PerOrders</span><br><span class="line"> # SQL Server / Oracle / MS Access</span><br><span class="line">ALTER TABLE Orders</span><br><span class="line">DROP CONSTRAINT fk_PerOrders</span><br></pre></td></tr></table></figure></li>
<li><p><strong>CHECK</strong> - 保证列中的值符合指定的条件。</p>
<p> CHECK 约束用于限制列中的值的范围。</p>
<p> 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。</p>
<p> 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br></pre></td><td class="code"><pre><span class="line"># CREATE TABLE 时的 SQL CHECK 约束</span><br><span class="line"># mysql</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">CHECK (P_Id>0)</span><br><span class="line">)</span><br><span class="line"># SQL Server / Oracle / MS Access</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL CHECK (P_Id>0),</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255)</span><br><span class="line">)</span><br><span class="line"># 定义多个列的 CHECK 约束</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">P_Id int NOT NULL,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># ALTER TABLE 时的 SQL CHECK 约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD CHECK (P_Id>0)</span><br><span class="line"> # 定义多个列的 CHECK 约束</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')</span><br><span class="line"></span><br><span class="line"># 撤销 CHECK 约束</span><br><span class="line"> # SQL Server / Oracle / MS Access</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP CONSTRAINT chk_Person</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">DROP CHECK chk_Person</span><br></pre></td></tr></table></figure></li>
<li><p><strong>DEFAULT</strong> - 规定没有给列赋值时的默认值。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br></pre></td><td class="code"><pre><span class="line"># CREATE TABLE 时的 SQL DEFAULT 约束</span><br><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line"> P_Id int NOT NULL,</span><br><span class="line"> LastName varchar(255) NOT NULL,</span><br><span class="line"> FirstName varchar(255),</span><br><span class="line"> Address varchar(255),</span><br><span class="line"> City varchar(255) DEFAULT 'Sandnes'</span><br><span class="line">)</span><br><span class="line"># 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值</span><br><span class="line">CREATE TABLE Orders</span><br><span class="line">(</span><br><span class="line"> O_Id int NOT NULL,</span><br><span class="line"> OrderNo int NOT NULL,</span><br><span class="line"> P_Id int,</span><br><span class="line"> OrderDate date DEFAULT GETDATE()</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"># ALTER TABLE 时的 SQL DEFAULT 约束</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ALTER City SET DEFAULT 'SANDNES'</span><br><span class="line"> # SQL Server / MS Access</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City</span><br><span class="line"> # Oracle</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">MODIFY City DEFAULT 'SANDNES'</span><br><span class="line"></span><br><span class="line"># 撤销 DEFAULT 约束</span><br><span class="line"> # mysql</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ALTER City DROP DEFAULT</span><br><span class="line"> # SQL Server / Oracle / MS Access</span><br><span class="line">ALTER TABLE Persons</span><br><span class="line">ALTER COLUMN City DROP DEFAULT</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="CREATE-INDEX-语句"><a href="#CREATE-INDEX-语句" class="headerlink" title="CREATE INDEX 语句"></a><strong>CREATE INDEX 语句</strong></h3><p>——用于在表中创建索引</p>
<p>用户无法看到索引，它们只能被用来加速搜索/查询。</p>
<p><strong>注释：</strong>更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 在表上创建一个简单的索引</span><br><span class="line">CREATE INDEX index_name</span><br><span class="line">ON table_name (column_name)</span><br><span class="line"># 在表上创建一个唯一的索引</span><br><span class="line">CREATE UNIQUE INDEX index_name</span><br><span class="line">ON table_name (column_name)</span><br></pre></td></tr></table></figure>
<h3 id="Drop-子句"><a href="#Drop-子句" class="headerlink" title="Drop 子句"></a><strong>Drop 子句</strong></h3><p>——用于删除索引、表和数据库</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"># DROP INDEX 删除表中的索引</span><br><span class="line"> # MS Access</span><br><span class="line"> DROP INDEX index_name ON table_name</span><br><span class="line"> # MS SQL Server</span><br><span class="line"> DROP INDEX table_name.index_name</span><br><span class="line"> # DB2/Oracle</span><br><span class="line"> DROP INDEX index_name</span><br><span class="line"> # MySQL</span><br><span class="line"> ALTER TABLE table_name DROP INDEX index_name</span><br><span class="line"> </span><br><span class="line"># DROP TABLE 用于删除表</span><br><span class="line">DROP TABLE table_name</span><br><span class="line"># DROP DATABASE 删除数据库</span><br><span class="line">DROP DATABASE database_name</span><br><span class="line"># TRUNCATE TABLE 删除表内的数据</span><br><span class="line">TRUNCATE TABLE table_name</span><br></pre></td></tr></table></figure>
<h3 id="ALTER-TABLE-语句"><a href="#ALTER-TABLE-语句" class="headerlink" title="ALTER TABLE 语句"></a><strong>ALTER TABLE 语句</strong></h3><p>——用于在已有的表中添加、删除或修改列</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># 在表中添加列</span><br><span class="line">ALTER TABLE table_name</span><br><span class="line">ADD column_name datatype</span><br><span class="line"># 删除表中的列</span><br><span class="line">ALTER TABLE table_name</span><br><span class="line">DROP COLUMN column_name</span><br><span class="line"></span><br><span class="line"># 改变表中列的数据类型</span><br><span class="line"> # SQL Server / MS Access</span><br><span class="line">ALTER TABLE table_name</span><br><span class="line">ALTER COLUMN column_name datatype</span><br><span class="line"> # My SQL / Oracle</span><br><span class="line">ALTER TABLE table_name</span><br><span class="line">MODIFY COLUMN column_name datatype</span><br></pre></td></tr></table></figure>
<h3 id="AUTO-INCREMENT-字段"><a href="#AUTO-INCREMENT-字段" class="headerlink" title="AUTO INCREMENT 字段"></a><strong>AUTO INCREMENT 字段</strong></h3><p>——在新记录插入表中时生成一个唯一的数字</p>
<p>在每次插入新记录时，通常希望自动地创建主键字段的值。可以在表中创建一个 auto-increment 字段。</p>
<ol>
<li><p>mysql</p>
<p>下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">ID int NOT NULL AUTO_INCREMENT,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255),</span><br><span class="line">PRIMARY KEY (ID)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。</p>
<p>默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。</p>
<p>要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE Persons AUTO_INCREMENT=100</span><br></pre></td></tr></table></figure>
<p>要在 “Persons” 表中插入新记录，不必为 “ID” 列规定值（会自动添加一个唯一的值）：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Persons (FirstName,LastName)</span><br><span class="line">VALUES ('Lars','Monsen')</span><br></pre></td></tr></table></figure>
<p>上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。</p>
</li>
<li><p>SQL Server</p>
<p>下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">ID int IDENTITY(1,1) PRIMARY KEY,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。</p>
<p>在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。</p>
<p><strong>提示：</strong>要规定 “ID” 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。</p>
<p>要在 “Persons” 表中插入新记录，不必为 “ID” 列规定值（会自动添加一个唯一的值）：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Persons (FirstName,LastName)</span><br><span class="line">VALUES ('Lars','Monsen')</span><br></pre></td></tr></table></figure>
<p>上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。</p>
</li>
<li><p>Access</p>
<p>下面的 SQL 语句把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键字段：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE Persons</span><br><span class="line">(</span><br><span class="line">ID Integer PRIMARY KEY AUTOINCREMENT,</span><br><span class="line">LastName varchar(255) NOT NULL,</span><br><span class="line">FirstName varchar(255),</span><br><span class="line">Address varchar(255),</span><br><span class="line">City varchar(255)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>
<p>MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment 任务。</p>
<p>默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。</p>
<p><strong>提示：</strong>要规定 “ID” 列以 10 起始且递增 5，请把 autoincrement 改为 AUTOINCREMENT(10,5)。</p>
<p>要在 “Persons” 表中插入新记录，不必为 “ID” 列规定值（会自动添加一个唯一的值）：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Persons (FirstName,LastName)</span><br><span class="line">VALUES ('Lars','Monsen')</span><br></pre></td></tr></table></figure>
<p>上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋予一个唯一的值。”FirstName” 列会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。</p>
</li>
<li><p>Oracle</p>
<p>在 Oracle 中，代码稍微复杂一点。</p>
<p>必须通过 sequence 对象（该对象生成数字序列）创建 auto-increment 字段。</p>
<p>请使用下面的 CREATE SEQUENCE 语法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">CREATE SEQUENCE seq_person</span><br><span class="line">MINVALUE 1</span><br><span class="line">START WITH 1</span><br><span class="line">INCREMENT BY 1</span><br><span class="line">CACHE 10</span><br></pre></td></tr></table></figure>
<p>上面的代码创建一个名为 seq_person 的 sequence 对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。</p>
<p>要在 “Persons” 表中插入新记录，必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO Persons (ID,FirstName,LastName)</span><br><span class="line">VALUES (seq_person.nextval,'Lars','Monsen')</span><br></pre></td></tr></table></figure>
<p>上面的 SQL 语句会在 “Persons” 表中插入一条新记录。”ID” 列会被赋值为来自 seq_person 序列的下一个数字。”FirstName”列 会被设置为 “Lars”，”LastName” 列会被设置为 “Monsen”。</p>
</li>
</ol>
<h3 id="SQL视图"><a href="#SQL视图" class="headerlink" title="SQL视图"></a>SQL视图</h3><ol>
<li><p>SQL CREATE VIEW 语句</p>
<p> 在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。</p>
<p> 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。</p>
<p> 可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。</p>
</li>
<li><p>SQL CREATE VIEW 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW view_name AS</span><br><span class="line">SELECT column_name(s)</span><br><span class="line">FROM table_name</span><br><span class="line">WHERE condition</span><br></pre></td></tr></table></figure>
<p><strong>注释：</strong>视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。</p>
</li>
<li><p>SQL CREATE VIEW 实例</p>
<p>样本数据库 Northwind 拥有一些被默认安装的视图。</p>
<p>视图 “Current Product List” 会从 “Products” 表列出所有正在使用的产品（未停产的产品）。这个视图使用下面的 SQL 创建：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW [Current Product List] AS</span><br><span class="line">SELECT ProductID,ProductName</span><br><span class="line">FROM Products</span><br><span class="line">WHERE Discontinued=No</span><br></pre></td></tr></table></figure>
<p>可以像这样查询上面这个视图：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM [Current Product List]</span><br></pre></td></tr></table></figure>
<p>Northwind 样本数据库的另一个视图会选取 “Products” 表中所有单位价格高于平均单位价格的产品：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW [Products Above Average Price] AS</span><br><span class="line">SELECT ProductName,UnitPrice</span><br><span class="line">FROM Products</span><br><span class="line">WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)</span><br></pre></td></tr></table></figure>
<p>可以像这样查询上面这个视图：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM [Products Above Average Price]</span><br></pre></td></tr></table></figure>
<p>Northwind 样本数据库的另一个视图会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 “Product Sales for 1997” 的视图那里选取数据：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW [Category Sales For 1997] AS</span><br><span class="line">SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales</span><br><span class="line">FROM [Product Sales for 1997]</span><br><span class="line">GROUP BY CategoryName</span><br></pre></td></tr></table></figure>
<p>可以像这样查询上面这个视图：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM [Category Sales For 1997]</span><br></pre></td></tr></table></figure>
<p>也可以向查询添加条件。现在，仅仅需要查看 “Beverages” 类的销售总数：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM [Category Sales For 1997]</span><br><span class="line">WHERE CategoryName='Beverages'</span><br></pre></td></tr></table></figure></li>
<li><p>SQL 更新视图</p>
<p>可以使用下面的语法来更新视图：</p>
<ol>
<li><p>SQL CREATE OR REPLACE VIEW 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE OR REPLACE VIEW view_name AS</span><br><span class="line">SELECT column_name(s)</span><br><span class="line">FROM table_name</span><br><span class="line">WHERE condition</span><br></pre></td></tr></table></figure>
<p>现在，希望向 “Current Product List” 视图添加 “Category” 列将通过下列 SQL 更新视图：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE VIEW [Current Product List] AS</span><br><span class="line">SELECT ProductID,ProductName,Category</span><br><span class="line">FROM Products</span><br><span class="line">WHERE Discontinued=No</span><br></pre></td></tr></table></figure></li>
<li><p>SQL Server</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ] </span><br><span class="line">[ WITH <view_attribute> [ ,...n ] ] </span><br><span class="line">AS select_statement </span><br><span class="line">[ WITH CHECK OPTION ] [ ; ]</span><br><span class="line"></span><br><span class="line"><view_attribute> ::= </span><br><span class="line">{ </span><br><span class="line"> [ ENCRYPTION ]</span><br><span class="line"> [ SCHEMABINDING ]</span><br><span class="line"> [ VIEW_METADATA ] </span><br><span class="line">} </span><br></pre></td></tr></table></figure>
<p><strong>schema_name:</strong> 视图所属架构的名称。</p>
<p><strong>view_name:</strong> 要更改的视图。</p>
<p><strong>column:</strong> 将成为指定视图的一部分的一个或多个列的名称（以逗号分隔）。</p>
</li>
</ol>
</li>
<li><p>SQL 撤销视图</p>
<p>可以通过 DROP VIEW 命令来删除视图。</p>
<p>SQL DROP VIEW 语法</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP VIEW view_name</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="SQL-Date-函数"><a href="#SQL-Date-函数" class="headerlink" title="SQL Date 函数"></a>SQL Date 函数</h3><ol>
<li><p><strong>MySQL Date 函数</strong></p>
<p>下面的表格列出了 MySQL 中最重要的内建日期函数：</p>
<table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-now.html">NOW()</a></td>
<td align="left">返回当前的日期和时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-curdate.html">CURDATE()</a></td>
<td align="left">返回当前的日期</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-curtime.html">CURTIME()</a></td>
<td align="left">返回当前的时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-date.html">DATE()</a></td>
<td align="left">提取日期或日期/时间表达式的日期部分</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-extract.html">EXTRACT()</a></td>
<td align="left">返回日期/时间的单独部分</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-date-add.html">DATE_ADD()</a></td>
<td align="left">向日期添加指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-date-sub.html">DATE_SUB()</a></td>
<td align="left">从日期减去指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-datediff-mysql.html">DATEDIFF()</a></td>
<td align="left">返回两个日期之间的天数</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-date-format.html">DATE_FORMAT()</a></td>
<td align="left">用不同的格式显示日期/时间</td>
</tr>
</tbody></table>
</li>
<li><p><strong>SQL Server Date 函数</strong></p>
<p>下面的表格列出了 SQL Server 中最重要的内建日期函数：</p>
<table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-getdate.html">GETDATE()</a></td>
<td align="left">返回当前的日期和时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-datepart.html">DATEPART()</a></td>
<td align="left">返回日期/时间的单独部分</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-dateadd.html">DATEADD()</a></td>
<td align="left">在日期中添加或减去指定的时间间隔</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-datediff.html">DATEDIFF()</a></td>
<td align="left">返回两个日期之间的时间</td>
</tr>
<tr>
<td align="left"><a target="_blank" rel="noopener" href="https://www.runoob.com/sql/func-convert.html">CONVERT()</a></td>
<td align="left">用不同的格式显示日期/时间</td>
</tr>
</tbody></table>
</li>
<li><p><strong>SQL Date 数据类型</strong></p>
<ul>
<li><p><strong>MySQL</strong> 使用下列数据类型在数据库中存储日期或日期/时间值：</p>
<ul>
<li>DATE - 格式：YYYY-MM-DD</li>
<li>DATETIME - 格式：YYYY-MM-DD HH:MM:SS</li>
<li>TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS</li>
<li>YEAR - 格式：YYYY 或 YY</li>
</ul>
</li>
<li><p><strong>SQL Server</strong> 使用下列数据类型在数据库中存储日期或日期/时间值：</p>
<ul>
<li>DATE - 格式：YYYY-MM-DD</li>
<li>DATETIME - 格式：YYYY-MM-DD HH:MM:SS</li>
<li>SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS</li>
<li>TIMESTAMP - 格式：唯一的数字</li>
</ul>
<p><strong>注释：</strong>在数据库中创建一个新表时，需要为列选择数据类型！</p>
</li>
</ul>
</li>
<li><p><strong>SQL 日期处理</strong></p>
<p>如果不涉及时间部分，那么可以轻松地比较两个日期！</p>
<p>假设有如下的 “Orders” 表：</p>
<table>
<thead>
<tr>
<th align="left">OrderId</th>
<th align="left">ProductName</th>
<th align="left">OrderDate</th>
</tr>
</thead>
<tbody><tr>
<td align="left">1</td>
<td align="left">Geitost</td>
<td align="left">2008-11-11</td>
</tr>
<tr>
<td align="left">2</td>
<td align="left">Camembert Pierrot</td>
<td align="left">2008-11-09</td>
</tr>
<tr>
<td align="left">3</td>
<td align="left">Mozzarella di Giovanni</td>
<td align="left">2008-11-11</td>
</tr>
<tr>
<td align="left">4</td>
<td align="left">Mascarpone Fabioli</td>
<td align="left">2008-10-29</td>
</tr>
</tbody></table>
<p>现在，希望从上表中选取 OrderDate 为 “2008-11-11” 的记录。</p>
<p>使用下面的 SELECT 语句：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM Orders WHERE OrderDate='2008-11-11'</span><br></pre></td></tr></table></figure>
<p>结果集如下所示：</p>
<table>
<thead>
<tr>
<th align="left">OrderId</th>
<th align="left">ProductName</th>
<th align="left">OrderDate</th>
</tr>
</thead>
<tbody><tr>
<td align="left">1</td>
<td align="left">Geitost</td>
<td align="left">2008-11-11</td>
</tr>
<tr>
<td align="left">3</td>
<td align="left">Mozzarella di Giovanni</td>
<td align="left">2008-11-11</td>
</tr>
</tbody></table>
<p>现在，假设 “Orders” 表如下所示（请注意 “OrderDate” 列中的时间部分）：</p>
<table>
<thead>
<tr>
<th align="left">OrderId</th>
<th align="left">ProductName</th>
<th align="left">OrderDate</th>
</tr>
</thead>
<tbody><tr>
<td align="left">1</td>
<td align="left">Geitost</td>
<td align="left">2008-11-11 13:23:44</td>
</tr>
<tr>
<td align="left">2</td>
<td align="left">Camembert Pierrot</td>
<td align="left">2008-11-09 15:45:21</td>
</tr>
<tr>
<td align="left">3</td>
<td align="left">Mozzarella di Giovanni</td>
<td align="left">2008-11-11 11:12:01</td>
</tr>
<tr>
<td align="left">4</td>
<td align="left">Mascarpone Fabioli</td>
<td align="left">2008-10-29 14:56:59</td>
</tr>
</tbody></table>
<p>如果使用和上面一样的 SELECT 语句：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM Orders WHERE OrderDate='2008-11-11'</span><br><span class="line"># or</span><br><span class="line">SELECT * FROM Orders WHERE OrderDate='2008-11-11 00：00：00'</span><br></pre></td></tr></table></figure>
<p>那么将得不到结果！因为表中没有”2008-11-11 00:00:00”日期。如果没有时间部分，默认时间为 00:00:00。</p>
<p><strong>提示：</strong>如果希望使查询简单且更易维护，那么请不要在日期中使用时间部分！</p>
</li>
</ol>
<h3 id="SQL-NULL-值"><a href="#SQL-NULL-值" class="headerlink" title="SQL NULL 值"></a>SQL NULL 值</h3><p>NULL 值代表遗漏的未知数据。默认地，表的列可以存放 NULL 值。</p>
<ol>
<li><p><strong>SQL 的 NULL 值处理</strong></p>
<p>请看下面的 “Persons” 表：</p>
<table>
<thead>
<tr>
<th align="left">P_Id</th>
<th align="left">LastName</th>
<th align="left">FirstName</th>
<th align="left">Address</th>
<th align="left">City</th>
</tr>
</thead>
<tbody><tr>
<td align="left">1</td>
<td align="left">Hansen</td>
<td align="left">Ola</td>
<td align="left"></td>
<td align="left">Sandnes</td>
</tr>
<tr>
<td align="left">2</td>
<td align="left">Svendson</td>
<td align="left">Tove</td>
<td align="left">Borgvn 23</td>
<td align="left">Sandnes</td>
</tr>
<tr>
<td align="left">3</td>
<td align="left">Pettersen</td>
<td align="left">Kari</td>
<td align="left"></td>
<td align="left">Stavanger</td>
</tr>
</tbody></table>
<p>假如 “Persons” 表中的 “Address” 列是可选的。这意味着如果在 “Address” 列插入一条不带值的记录，”Address” 列会使用 NULL 值保存。</p>
<p>那么如何测试 NULL 值呢？</p>
<p>无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。</p>
<p>必须使用 <strong>IS NULL</strong> 和<strong>IS NOT NULL</strong> 操作符。</p>
</li>
<li><p><strong>SQL IS NULL</strong></p>
<p>如何仅仅选取在 “Address” 列中带有 NULL 值的记录呢？</p>
<p>必须使用 IS NULL 操作符：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT LastName,FirstName,Address FROM Persons</span><br><span class="line">WHERE Address IS NULL</span><br></pre></td></tr></table></figure>
<p>结果集如下所示：</p>
<table>
<thead>
<tr>
<th align="left">LastName</th>
<th align="left">FirstName</th>
<th align="left">Address</th>
</tr>
</thead>
<tbody><tr>
<td align="left">Hansen</td>
<td align="left">Ola</td>
<td align="left"></td>