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进行模糊查找
- 匹配字符串可以是字符常量，也可以是通配符。
- _ 下划线, 匹配任意一个字符
- % 匹配0或者多个字符
- [] 匹配[]中的任意一个字符
- [^] 不匹配[]中的任意一个字符
// 匹配姓张的人
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;- 多重条件查找
就是 AND 和 OR
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提供的聚合函数有：
- COUNT(*) 统计表中元组的个数
- COUNT (DISTINCT sno) 统计本列非空值个数
- SUM (列名) 计算列的总和
- AVG (列名) 计算列的平均值
- MAX (列名) 列的最大值
- 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未完待续,小编正在急稿中~~
829
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
