SQL 语法总结

完整性约束

  • NOT NULL 列值非空
  • DEFAULT 列的默认值
  • UNIQUE 限制列取值不能重复
  • CHECK 限制列的取值范围
  • PRIMARY KEY 主键
  • FOREIGN KEY 外码

定义主码约束

Sno char(10) PRIMARY KEY;


// 或者是在表的最后

PRIMARY KEY (Sno);

定义外码约束

FOREIGN KEY (Sno) REFERENCES student (Sno),
FOREIGN KEY (Cno) REFERENCES course (Cno)

删除表

DROP TABLE student;

修改表的定义

>1 ALTER TABLE student
>2 ALTER COLUMN Sno varchar(20);            // 修改列定义

>1 ALTER TABLE student                      
>2 ADD job varchar(20) NULL;                // 添加列
    
>1 ALTER TABLE student
>2 DROP COLUMN job;                         // 删除列

>1 ALTER TABLE student
>2 ADD constraint CK_stuAge CHECK(Sage between 15 and 40)         // 增加约束

>1 ALTER TABLE student
>2 DROP contstraint CK_stuAge;              // 删除约束


创表完成之后的约束补充和修改

假设现在有了work雇员表和job工作表。

// 创建
1> CREATE TABLE work (
2> workerid char(7) NOT NULL,
3> workername char(10) ,
4> worknum char(8),
5> money int,
6> phone char(11) NOT NULL
7> );
8> go



1> CREATE TABLE job(
2> worknum char(8),
3> mixmoney int,
4> maxmoney int,
5> );
6> go

然后为其补充一些必要的约束

主码约束
// 为work表添加主码约束
1> ALTER TABLE work
2> ADD CONSTRAINT M_PK   // M_PK是自己起的约束名
3> PRIMARY KEY(workerid);
4> go
// 为job表添加主码约束
// 但是之前没有为该列加 NOT NULL ,所以先添加一个NOT NULL的约束


1> ALTER TABLE job
2> ALTER COLUMN worknum char(8) NOT NULL;
3> go

// 在为job表添加主码约束
1> ALTER TABLE job
2> ADD CONSTRAINT PK_JOB
3> PRIMARY KEY (worknum);
4> go
1>
UNIQUE 约束
// 为雇员表worker的 phone 添加UNIQUE约束,使得其值不能有重复
1> ALTER TABLE work
2> ADD CONSTRAINT WORK_UK
3> UNIQUE (phone);
4> go
1>

外码约束
// 为雇员表work 的 工作编号worknum 添加外码约束
1> ALTER TABLE work
2> ADD CONSTRAINT FK_JOBID
3> FOREIGN KEY (worknum) REFERENCES job (worknum);
4> go
1>
DEFAULT 约束
// 为雇员的工资提默认值

1> ALTER TABLE work
2> ADD CONSTRAINT DF_work
3> DEFAULT 1000 FOR money;
4> go
1>
CHECK 约束
// 雇员工资表的工资最低是1000

1> ALTER TABLE work
2> ADD CONSTRAINT CHK_money
3> CHECK (money>=1000);
4> go
1>

// 最低工资 < 最高工资
1> ALTER TABLE job
2> ADD CONSTRAINT CHK_job
3> CHECK (mixmoney<=maxmoney);
4> go
1>

插入数据
// 先往job表中插入一条数据

1> INSERT INTO job VALUES ('1',1000,1900);
2> go

(1 行受影响)


// 在往work中插入几条数据
1> INSERT INTO work VALUES ('1','张锦杰','1',1400,'13752127826');
2> go

(1 行受影响)

数据查询SELECT

查询表的全部

// 查询work表全部信息

1> SELECT * FROM work;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826

(1 行受影响)
// 查询job表全部信息

1> SELECT * FROM job;
2> go
worknum Dmixmoney   Dmaxmoney
--------D-----------D-----------
1       D       1000D       1900

(1 行受影响)

为了配以下的操作,需要多添加几条信息

// 查询如下

1> SELECT * FROM work;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
2       D长官        D1       D       1600D1224123314
3       D妹妹        D1       D       1300D122432414
4       D妹ds妹      D1       D       1700D12223323414
5       D撒爱        D1       D       1900D1223414
6       D张爱爱       D1       D       1100D1324977734

(6 行受影响)

查询某几列

1> SELECT workerid,workername FROM work;
2> go
workeridDworkername
--------D----------
1       D张锦杰
2       D长官
3       D妹妹
4       D妹ds妹
5       D撒爱
6       D张爱爱

(6 行受影响)

查询出经过计算的列

1> SELECT workerid, money+1000 FROM work;
2> go
workeridD
--------D-----------
1       D       2400
2       D       2600
3       D       2300
4       D       2700
5       D       2900
6       D       2100

(6 行受影响)

// 会产生一个无名的列

常量列

1> SELECT workerid, '我是常量列成员', money+1000  FROM work;
2> go
workeridD              D
--------D--------------D-----------
1       D我是常量列成员       D       2400
2       D我是常量列成员       D       2600
3       D我是常量列成员       D       2300
4       D我是常量列成员       D       2700
5       D我是常量列成员       D       2900
6       D我是常量列成员       D       2100

(6 行受影响)

// 产生两个无名列

为无名列指定名字

1> SELECT workerid AS 主键, '我是常量内容' AS 常量 , money+1000 AS 总金额 FROM work;
2> go
主键     D常量          D总金额
-------D------------D-----------
1      D我是常量内容      D       2400
2      D我是常量内容      D       2600
3      D我是常量内容      D       2300
4      D我是常量内容      D       2700
5      D我是常量内容      D       2900
6      D我是常量内容      D       2100

(6 行受影响)

去除掉取值相同的行

>1 SELECT DISTINCT Sno FROM SC;
>2 go

// 去除掉重复的行,使用了关键字DISTINCT

查询满足条件的元组

  • WHERE 用法
  • 常用的查询条件
查询条件 谓词
比较 = > < <= >= != 以及 NOT+前面的比较用算符
确定范围 BETWEEN AND , NOT BETWEEN AND
确定集合 IN , NOTIN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL
多重条件 AND, OR
  • 比较大小
1> SELECT * FROM work  WHERE money=1400;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826

(1 行受影响)


// 取反操作:NOT money < 1200 
// 正向操作:money >= 1200
// 需要注意的:取反操作比正向操作执行效率低,因为取反先是执行正向操作,然后从全体数据中排除这部分。因此取反操作需要经过两个步骤。
  • 确定范围
1> SELECT * FROM work WHERE  money  BETWEEN 1200 AND 1700;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
2       D长官        D1       D       1600D1224123314
3       D妹妹        D1       D       1300D122432414
4       D妹ds妹      D1       D       1700D12223323414

(4 行受影响)

//  BETWEEN 1200 AND 1700;  相当于是  同时满足 >=1200 <=1700
// 相反操作

1> SELECT * FROM work WHERE  money NOT  BETWEEN 1200 AND 1700;
2> GO
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
5       D撒爱        D1       D       1900D1223414
6       D张爱爱       D1       D       1100D1324977734

(2 行受影响)

对于日期的比较:

SELECT * FROM books WHERE time BETWEEN '2009/3/12'  AND  '2010/4/23';

// 对于日期类型的常量,需要用单引号引起来,并且年月日之间要用 '/' '-' 分割符来分割
 
  • 确定集合
1> SELECT *  FROM work WHERE money IN ('1400','1500','1700');
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
4       D妹ds妹      D1       D       1700D12223323414

(2 行受影响)
  • 字符串匹配
// 可以使用LIKE进行模糊查找

  • 匹配字符串可以是字符常量,也可以是通配符。
  1. _ 下划线, 匹配任意一个字符
  2. % 匹配0或者多个字符
  3. [] 匹配[]中的任意一个字符
  4. [^] 不匹配[]中的任意一个字符
// 匹配姓张的人

1> SELECT * FROM work WHERE workername LIKE '张%';
4> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
6       D张爱爱       D1       D       1100D1324977734

(2 行受影响)
//匹配姓张、李、撒的人
1> SELECT * FROM work WHERE workername LIKE '[张李撒]%';
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
5       D撒爱        D1       D       1900D1223414
6       D张爱爱       D1       D       1100D1324977734

(3 行受影响)
// 不姓张也不姓王的
1> SELECT * FROM work WHERE workername NOT LIKE '[张王]%';
2> go

或者

1> SELECT * FROM work WHERE workername  LIKE '[^张王]%';
2> go
1> SELECT * FROM work WHERE workername  LIKE '[张_]'; // 姓张且是两个字
2> go
// 去掉指定列的尾随的空格

1> SELECT * FROM work WHERE workername LIKE '[张__]';
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------

(0 行受影响)

// 为什么是无呢?

// 因为我们实际数据库的时候,给workername是char定长类型的,因此,如果不够定长,就用空格填充了,这就使得匹配无法成功。

如何解决呢?


SQL Server 提供了  rtrim(列名) 来去掉尾随的空格
1> SELECT * FROM work WHERE rtrim(workername) LIKE '[张_]%';
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
6       D张爱爱       D1       D       1100D1324977734

(2 行受影响)
// 查询最后一位不是 2.3.5

1> SELECT * FROM work WHERE workername LIKE '%[^235]';
2> go
// 转义字符
ESCAPE 关键字

1> SELECT * FROM work WHERE workername LIKE '%30!%%' ESCAPE '!';
2> go


// 以!转义%  意思是查找包含30%的字符串

1> SELECT * FROM work WHERE workername LIKE '%!_%' ESCAPE '!';
2> go

// 以!转义_  意思是查找包含30%的字符串
  • 涉及空值
//
SELECT * FROM SC WHERE grade IS NOT NULL;
SELECT * FROM SC WHERE grade IS  NULL;
  • 多重条件查找

就是 ANDOR

SELECT Sname FROM Student
WHERE Sdep = '计算机' AND Sage < 20

// AND 的 优先级比 OR 高,因此需要使用括号
SELECT Sname FROM Student
WHERE ( Sdep = '计算机' OR Sdep = '信息') AND Sage < 20

排序

  • 按照升序进行排列
1> SELECT * FROM work ORDER BY money ASC;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
6       D张爱爱       D1       D       1100D1324977734
3       D妹妹        D1       D       1300D122432414
1       D张锦杰       D1       D       1400D13752127826
2       D长官        D1       D       1600D1224123314
4       D妹ds妹      D1       D       1700D12223323414
5       D撒爱        D1       D       1900D1223414

(6 行受影响)
  • 降序排列
1> SELECT * FROM work ORDER BY money DESC;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
5       D撒爱        D1       D       1900D1223414
4       D妹ds妹      D1       D       1700D12223323414
2       D长官        D1       D       1600D1224123314
1       D张锦杰       D1       D       1400D13752127826
3       D妹妹        D1       D       1300D122432414
6       D张爱爱       D1       D       1100D1324977734

(6 行受影响)

使用聚合函数来汇总数据

SQL提供的聚合函数有:

  1. COUNT(*) 统计表中元组的个数
  2. COUNT (DISTINCT sno) 统计本列非空值个数
  3. SUM (列名) 计算列的总和
  4. AVG (列名) 计算列的平均值
  5. MAX (列名) 列的最大值
  6. MIN (列名) 列的最小值
// 元组总个数
1> SELECT COUNT(*)  FROM work;
2> go

-----------
          6

(1 行受影响)

// 统计本列非空值个数
1> SELECT COUNT(DISTINCT money) FROM work;
2> go

-----------
          6

(1 行受影响)


//  设置列名 
1> SELECT COUNT(*) AS 工人数 , AVG(money) AS 平均收入 FROM work;
2> go
工人数        D平均收入
-----------D-----------
          6D       1500

(1 行受影响)

// 使用MAX 和 MIN
1> SELECT MAX(money) AS '最高收入' , MIN(money) AS '最低收入' FROM work;
2> go
最高收入       D最低收入
-----------D-----------
       1900D       1100

(1 行受影响)

对查询结果进行分组统计

// 现在work表是这个样子
1> SELECT * FROM work;
2> go
workeridDworkernameDworknum Dmoney      Dphone
--------D----------D--------D-----------D-----------
1       D张锦杰       D1       D       1400D13752127826
2       D长官        D1       D       1600D1224123314
3       D妹妹        D1       D       1300D122432414
4       D妹ds妹      D1       D       1700D12223323414
5       D撒爱        D1       D       1900D1223414
6       D张爱爱       D1       D       1100D1324977734
7       D大苏打       D2       D       1500D341212312
8       D阿斯顿       D2       D       1600D3123123
9       D误区二       D2       D       1700D32424

// 首先对员工表进行按照职位分组,分为两组,然后统计每组的数据
1> SELECT COUNT(*) AS 职位员工数 ,AVG(money) 平均收入
2> FROM work GROUP BY worknum;
3> go
职位员工数      D平均收入
-----------D-----------
          6D       1500
          3D       1600

(2 行受影响)
1>

// 再来一次
1> SELECT worknum,  COUNT(*) AS 职位员工数 ,AVG(money) 平均收入
2> FROM work GROUP BY worknum;
3> go
worknum D职位员工数      D平均收入
--------D-----------D-----------
1       D          6D       1500
2       D          3D       1600

(2 行受影响)



// 选择每个系的女生的人数
// 再来一个小李子
SELECT Sdept, Count(*) 女生人数 FROM student
WHERE Sex = '女'
GROUP BY Sdep;

使用HAVING子句
HAVING 语句对分组后的数据在进行筛选,可以使用统计函数,但是在WHERE 语句中是无法使用统计函数的。

// 拿书上的例子来说
SELECT Sno , COUNT(*) AS 选课门数 FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;

// 查询选修了三门以上的学生的学号和选课数

多表连接查询
连接查询包括内连接、外连接、交叉连接,在这里我们只涉及内连接、 和外连接

// 这里我们需要创建三张表
// 学生表
1> CREATE TABLE student(
2> Sno varchar(8) PRIMARY KEY,
3> Sname varchar(10) NOT NULL,
4> Sage tinyint,
5> Ssex char(2),
6> Sdept char(20)
7> );
8> go
1>var


// 课程表
1> CREATE TABLE course(
2> Cno varchar(6) PRIMARY KEY,
3> Cname varchar(10) NOT NULL,
4> Credit tinyint,
5> Semester tinyint,
6> );
7> go


// 选课表
1> CREATE TABLE SC(
2> Sno varchar(8) NOT NULL,
3> Cno varchar(6) NOT NULL,
4> grade tinyint ,
5> PRIMARY KEY(Sno, Cno),
6> FOREIGN KEY(Sno) REFERENCES student (Sno),
7> FOREIGN KEY(Cno) REFERENCES course (Cno)
8> );
9> go
1>



// 然后小编在数据库中添加了几条数据

图片描述
图片描述
图片描述

// 将student 和 SC 连接
1> SELECT * FROM student INNER JOIN SC
2> ON student.Sno = SC.Sno;
3> go
Sno     DSname     DSageDSsexDSdept               DSno     DCno   Dgrade
--------D----------D----D----D--------------------D--------D------D-----
13212311D张海        D  22D男   D数学系                 D13212311Dc02   D NULL
13212311D张海        D  22D男   D数学系                 D13212311Dc04   D   89
16101132D李勇        D  19D男   D计算机系                D16101132Dc01   D   43
16101132D李勇        D  19D男   D计算机系                D16101132Dc02   D NULL
16101132D李勇        D  19D男   D计算机系                D16101132Dc03   D   78
16101321D张锦杰       D  20D男   D计算机系                D16101321Dc01   D   90
16101321D张锦杰       D  20D男   D计算机系                D16101321Dc03   D   78
16101321D张锦杰       D  20D男   D计算机系                D16101321Dc04   D   78
16123211D吴斌        D  22D男   D信息系                 D16123211Dc02   D   23
16123211D吴斌        D  22D男   D信息系                 D16123211Dc03   D   65
16123211D吴斌        D  22D男   D信息系                 D16123211Dc05   D   78

// 去掉重复的列
1> SELECT student.Sno, Sname, Sage, Ssex,Cno,grade FROM student
2> INNER JOIN SC
3> ON student.Sno = SC.Sno;
4> GO
Sno     DSname     DSageDSsexDCno   Dgrade
--------D----------D----D----D------D-----
13212311D张海        D  22D男   Dc02   D NULL
13212311D张海        D  22D男   Dc04   D   89
16101132D李勇        D  19D男   Dc01   D   43
16101132D李勇        D  19D男   Dc02   D NULL
16101132D李勇        D  19D男   Dc03   D   78
16101321D张锦杰       D  20D男   Dc01   D   90
16101321D张锦杰       D  20D男   Dc03   D   78
16101321D张锦杰       D  20D男   Dc04   D   78
16123211D吴斌        D  22D男   Dc02   D   23
16123211D吴斌        D  22D男   Dc03   D   65
16123211D吴斌        D  22D男   Dc05   D   78

// 查询计算机系的学生的选修情况
1> SELECT Sname, Cno, grade FROM student INNER  JOIN SC
2> ON student.Sno = SC.Sno
3> WHERE Sdept = "计算机系";
4> go
Sname     DCno   Dgrade
----------D------D-----
李勇        Dc01   D   43
李勇        Dc02   D NULL
李勇        Dc03   D   78
张锦杰       Dc01   D   90
张锦杰       Dc03   D   78
张锦杰       Dc04   D   78

(6 行受影响)

// 查找计算机系中选修了VB的学生信息
1> SELECT Sname, Cname, grade FROM student JOIN SC ON student.Sno = SC.Sno
2> JOIN course ON course.Cno = SC.Cno
3> WHERE Sdept ="计算机系"
4> AND Cname = "VB";
5> go
Sname     DCname     Dgrade
----------D----------D-----
李勇        DVB        D NULL

(1 行受影响)


// 统计每个系的学生考试平均成绩
1> SELECT Sdept , AVG(grade) as "平均成绩"
2> FROM student JOIN SC ON student.Sno = SC.Sno
3> GROUP BY Sdept;
4> GO
Sdept               D平均成绩
--------------------D-----------
计算机系                D         73
数学系                 D         89
信息系                 D         55


// 
1> SELECT Cno , COUNT(*) AS Total, AVG (grade), MAX(grade), MIN(grade) FROM student JOIN SC ON student.Sno = SC.Sno
2> WHERE Sdept ="计算机系"
3> GROUP BY Cno;
4> go
Cno   DTotal      D           D   D
------D-----------D-----------D---D---
c01   D          2D         66D 90D 43
c02   D          1D       NULLDNULDNUL
c03   D          2D         78D 78D 78
c04   D          1D         78D 78D 78

(4 行受影响)
// 自连接
// 选择跟李勇是一个系的其余同学
1> SELECT s2.Sname, s2.Sdept FROM student s1 JOIN  student s2
2> ON s1.Sdept = s2.Sdept
3> WHERE s1.Sname ="李勇"
4> AND s2.Sname!= "李勇";
5> go
Sname     DSdept
----------D--------------------
张锦杰       D计算机系
// 外连接
只限制一张表符合连接条件就行,不一定两张表都满足

// 查询学生的选课情况,包括选课的和没有选的(左内连接)
1> SELECT student.Sno, Sname, Cno, grade
2> FROM student LEFT OUTER JOIN SC
3> ON student.Sno = SC.Sno;
4> go
Sno     DSname     DCno   Dgrade
--------D----------D------D-----
12122312D王大力       DNULL  D NULL
13212311D张海        Dc01   D   89
13212311D张海        Dc02   D NULL
13212311D张海        Dc04   D   89
16101132D李勇        Dc01   D   43
16101132D李勇        Dc02   D NULL
16101132D李勇        Dc03   D   78
16101321D张锦杰       Dc01   D   90
16101321D张锦杰       Dc03   D   78
16101321D张锦杰       Dc04   D   78
16123211D吴斌        Dc01   D   12
16123211D吴斌        Dc02   D   23
16123211D吴斌        Dc03   D   65
16123211D吴斌        Dc05   D   78
16213322D王明        DNULL  D NULL


// 右连接
1> SELECT student.Sno, Sname, Cno, grade
2> FROM student RIGHT OUTER JOIN SC
3> ON student.Sno = SC.Sno;
4> go
Sno     DSname     DCno   Dgrade
--------D----------D------D-----
13212311D张海        Dc01   D   89
13212311D张海        Dc02   D NULL
13212311D张海        Dc04   D   89
16101132D李勇        Dc01   D   43
16101132D李勇        Dc02   D NULL
16101132D李勇        Dc03   D   78
16101321D张锦杰       Dc01   D   90
16101321D张锦杰       Dc03   D   78
16101321D张锦杰       Dc04   D   78
16123211D吴斌        Dc01   D   12
16123211D吴斌        Dc02   D   23
16123211D吴斌        Dc03   D   65
16123211D吴斌        Dc05   D   78

(13 行受影响)

// 查询那些课程没有被选
1> SELECT Cname FROM course C LEFT JOIN SC
2> ON c.Cno = SC.Cno
3> WHERE SC.Cno IS NULL;
4> GO
Cname
----------
数据结构

(1 行受影响)

// 查询并统计选课门数
1> SELECT S.Sno AS 学号 , COUNT(SC.Cno) AS 选课门数
2> FROM student S LEFT OUTER  JOIN SC ON S.Sno = SC.Sno
3> GROUP BY S.Sno
4> ORDER BY COUNT(SC.Cno) DESC;
5> go
学号      D选课门数
--------D-----------
16123211D          4
13212311D          3
16101132D          3
16101321D          3
16213322D          0
12122312D          0

使用TOP限制结果集

// 倒叙显示 前三个
1> SELECT TOP 3 Sname , Sage, Sdept
2> FROM student
3> ORDER BY Sage DESC;
4> GO
Sname     DSageDSdept
----------D----D--------------------
张海        D  22D数学系
吴斌        D  22D信息系
王大力       D  21D数学系


// 包含并列
1> SELECT TOP 3 WITH TIES Sname, Sdept
2> FROM student
3> ORDER BY Sage DESC;
4> go
Sname     DSdept
----------D--------------------
张海        D数学系
吴斌        D信息系
王明        D信息系
王大力       D数学系

(4 行受影响)


// TOP 谓词最好是于ORDER BY 配合使用

查询选课人数最少的两门课程(不包含没有选课的人)
1> SELECT TOP 2 WITH TIES Cno, COUNT(*) 选课人数
2> FROM SC
3> GROUP BY Cno
4> ORDER BY COUNT(*) ASC;
5> GO
Cno   D选课人数
------D-----------
c05   D          1
c04   D          2

未完待续,小编正在急稿中~~

Meils
1.6k 声望157 粉丝

前端开发实践者