行业知识
MySQL：语法速查手册【持续更新...】
一、定义基本表
1、常用的完整性约束
- 主码约束
primary key
- 外键约束
foreign key
- 唯一性约束
unique
- 非空性约束
not null
- 取值约束
check
2、例题
【例1】建立一个“学生”表Student，由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一
create table Student(Sno char(5) not null unique, Sname char(20) unique, Ssex char(1), Sage number, Sdept char(15));
【例2】建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码
create table SC(Sno char(5), Cno char(3), Grade number, Primary key(Sno, Cno));
二、修改基本表
1、语法格式
ALTER TABLE [ ADD [ 完整性约束 ] ][ DROP ][ MODIFY ];
- ADD子句：增加新列和新的完整性约束条件
- DROP子句：删除指定的完整性约束条件
- MODIFY子句：用于修改列名和数据类型
2、例题
【例1】向Student表增加 “入学时间” 列，其数据类型为日期型
alter table Student add Scome date;
【例2】将年龄的数据类型改为字符型
alter table Student modify Sage char;
【例3】删除学生姓名必须取唯一值的约束
alter table Student drop unique(Sname);
三、删除基本表
1、语法格式
drop table
2、例题
【例1】删除Student表
drop table Student;
四、建立与删除索引
1、语法格式
-- 建立索引CREATE [UNIQUE] INDEX ON ([][,[] ]…);-- 删除索引DROP INDEX ;
2、例题
【例1】为学生 - 课程数据库中的Student，Course，SC三个表建立索引。
-
Student表按学号升序建唯一索引
-
Course表按课程号升序建唯一索引
-
SC表按学号升序和课程号降序建唯一索引
create unique index Stusno on Student(Sno);create unique index Coucno on Course(Cno);create unique index SCno on SC(Sno ASC, Cno DESC);
【例2】删除Student表的Stusname索引
drop index Stusname;
五、查询
SELECT [ALL|DISTINCT] [] [，[]] …FROM [] [， [] ] …[ WHERE ][ GROUP BY [, ] … [ HAVING ] ][ ORDER BY [, ] … [ ASC|DESC ] ];
① from 子句 —— 指定查询对象(基本表或视图)
② where 子句：指定查询条件
③ group by 子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组
④ having 短句：筛选出满足指定条件的组
⑤ order by 子句：对查询结果表按指定列值的升序或降序排序
以下是示例数据库
学生 - 课程数据库学生表： Student(Sno，Sname，Ssex，Sage，Sdept)课程表： Course(Cno，Cname，Cpno，Ccredit)学生选课表： SC(Sno，Cno，Grade)
1、单表查询
① 选择表中的若干列
《查询指定列》
【例1】查询全体学生的学号与姓名
select Sno, Sname from Student;
【例2】查询全体学生的姓名、学号、所在系
select Sname, Sno, Sdept from Student;
《查询全部列》
【例】查询全体学生的详细记录
select Sno，Sname，Ssex，Sage，Sdept from Student；select * from Student;
《查询经过计算的值》
【例】 查全体学生的姓名及其出生年份（列别名）
select Sname, Sage as 2021-Sage from Student;
② 选择表中的若干元组
《消除取值重复的行》
在SELECT子句中使用 DISTINCT 短语
数据如下：
Sno Cno Grade----- --- ----19001 1 9219001 2 8519001 3 8819002 2 9019002 3 80
- 查询选修了课程的学生学号
-- 未优化前select Sno from SC;selct all Sno from SC; 结果： Sno 19001 19001 19001 19002 19002--------------------------- 优化后select distinct Sno from SC; 结果： Sno 19001 19002
《查询满足条件的元组》
- 比较大小
=, >, =, , !
【例1】查询计算机系全体学生的名单
select Sname from Student where Sdep = 'CS';
【例2】查询所有年龄在20岁以下的学生姓名及其年龄
select Sname, Sage from Student where age = 20;
【例3】查询考试成绩有不及格的学生的学号
select distinct Sno from SC where Grade
- 确定范围
between ... and ...not between ... and ...
【例1】查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
select Sname, Sdept, Sage from Student where Sage between 20 and 23;
【例2】查询年龄不在20~23岁之间的学生姓名、系别和年龄
select Sname, Sdept, Sage from Student where not Sage between 20 and 23;
- 确定集合
IN NOT IN
【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
select Sname, Ssex from Student where Sdept in ('IS', 'MA', 'CS');- 字符串匹配
LIKE 或 NOT LIKE[NOT] LIKE ‘’ [ESCAPE ‘ ’]
- 固定模版字符串
【例】查询学号为95001的学生的详细情况
select * from Student where Sno like '19001';select * from Student where Sno = '19001';
- 通配符
- % —— 代表任意长度
- _ —— 代表单个字符
【例1】查询所有姓刘学生的姓名、学号和性别
select Sname, Sno, Ssex from Student Sname like '刘%';
【例2】 查询姓 “欧阳” 且全名为三个汉字的学生的姓名
select Sname from Student where Sname like '欧阳_';
【例3】查询名字中第2个字为"阳"字的学生的姓名和学号
select Sname, Sno from Student where like '_阳%';
【例4】查询所有不姓刘的学生姓名
select Sname from Student Sname not like '刘%';
- 转义字符（escape短语）
【例】查询DB_Design课程的课程号和学分
select Cno, Ccredit from Course where Cname like 'DB\_Design' escape '\';
- 涉及空值的查询
is null 或 is not null
【例】某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
select Sno, Cno from SC where Grade is null;
【例】查所有有成绩的学生学号和课程号
select Sno, Cno from SC where Grade is not null;-- 有成绩即不为空
- 多重条件查询
- 用逻辑运算符AND和OR来联结多个查询条件
- AND的优先级高于OR
- 可以用括号改变优先级
- 可用来实现多种其他谓词
- [NOT] IN [NOT]
- BETWEEN … AND …
【例1】查询计算机系年龄在20岁以下的学生姓名
select Sname from Student where Sdept = 'CS' and Sage
【例2】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
select Sname, Ssex from Student where Sdept in ('IS', 'MA',' CS');【例3】查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄
select Sname, Sdept, Sage from Student where Sage between 20 and 23;
③ 对查询结果排序
使用order by子句
- 升序：ASC
- 降序：DESC
【例1】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
select Sno, Grade from SC where Cno = '3' order by Grade desc;
结果：
Sno Grade------- -------190101902419007 9219003 8219010 8219009 7519014 6119002 55
【例2】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
select * from Student where Sdept, Sage desc;
④ 使用集合函数
- 计数
COUNT（[DISTINCT|ALL] *）COUNT（[DISTINCT|ALL] ）
- 计算总和
SUM（[DISTINCT|ALL] ）
- 计算平均值
AVG（[DISTINCT|ALL] ）
- 求最大值
MAX（[DISTINCT|ALL] ）
- 求最小值
MIN（[DISTINCT|ALL] ）
【例1】查询学生总人数
select count(*) from Student;
【例2】查询选修了课程的学生人数
select count(distinct Sno) from SC;-- 注：用DISTINCT以避免重复计算学生人数
【例3】计算1号课程的学生 平均 成绩
select AVG(Grade) from SC where Cno = '1';
【例4】查询选修1号课程的学生 最高 分数
select max(Grade) from SC where Cno = '1';
⑤ 对查询结果分组
未对查询结果分组，集合函数将作用于 整个查询结果
对查询结果分组后，集合函数将 分别作用于每个组
====> 使用GROUP BY子句分组
【例1】求各个课程号及相应的选课人数
select Cno, count(Cno) from Sc Group by Cno;--------------------------------------------结果:Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48
【例2】求各个课程号及相应的课程成绩在90分以上的学生人数
select Cno, Count(Sno) from Sc where Grade > 90 Group by Cno;----------------------------------------结果Cno COUNT(Sno) 1 13 2 7 4 3 5 8
====> 使用HAVING短语筛选最终输出结果
【例1】查询选修了3门以上课程的学生学号
select Sno from SC Group by Sno Having Count(*) > 3;
【例2】 查询有3门以上课程在90分以上的学生的学号及90分以上的课程数
select Sno, Count(*) from SC where Grade > 90 Group by Sno Having Count(*) > 3;
HAVING短语与WHERE子句的区别？
- WHERE子句作用于基表或视图，从中选择满足条件的元组
- HAVING短语作用于组，从中选择满足条件的组
2、连接查询
同时涉及多个表的查询称为连接查询
一、广义笛卡尔积
select Student.*, Sc.* from Student, SC;-- .*代表这个表中所有的字段
二、等值与非等值连接查询
连接运算符为 = 的连接操作
[.] = [.] -- 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀
select Student.*, Sc.* from Student, SC where Student.Sno == SC.Sno;
三、自身连接查询
一个表与其自己进行连接，称为表的自身连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性，因此必须使用别名前缀
【例】查询每一门课的间接先修课（即先修课的先修课）
select a.Cno, b.Cno from Course a, Course b where a.Cpon = b.Cno;
四、外连接查询
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
【例】以学生为主体，查询每个学生及其选修课程的情况 (用外连接)
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student，SC WHERE Student.Sno = SC.Sno(+);
五、复合条件连接查询
WHERE子句中含多个连接条件时，称为复合条件连接
假设学校中性别相同的学生不会重名。现如下设计学生表和选课表：
Std(Sname, Ssex, Sage, Sdept)StdC(Sname, Ssex, Cno, Grade)
【例1】查询选修2号课程且成绩在90分以上的所有学生的姓名，性别及所在系
select Sname, Ssex, Sdept from Std, StdC where Std.Sname = StdC.Sname and Std.Ssex = StdC.Ssex and StdC.Cno = '2' and StdC.Grade > 90;
【例2】查询每个学生的学号、姓名、选修的课程名及成绩（ Student，SC，Course ）
select Student.Sno, Student.Sname, Course.Cname, SC.Grade from Student, SC, Course where Student.Sno = SC.Sno and SC.Cno = Course.Cno;
3、嵌套查询
① 嵌套查询概述
【一个查询块】：一个SELECT-FROM-WHERE语句
【嵌套查询】：将一个查询块嵌套在另一个查询块的 WHERE子句 或 HAVING短语 的条件中的查询
-- 查询所有选修了2号课程的学生姓名-- 外层查询/父查询select Sname from Student where Sno in ( -- 内层查询/子查询select Sno from SC where Cno = '02');
-
子查询的限制
- 不能使用order by子句
-
层层嵌套方式反映了 SQL语言的结构化
-
有些嵌套查询可以用连接运算替代
select Sname from Student, SC where Student.Sno = SC.Sno and Cno = '02';
② 嵌套查询分类
- 不相关子查询
- 子查询的查询条件不依赖于父查询
- 相关子查询
- 子查询的查询条件依赖于父查询
③ 嵌套查询求解方法
一、不相关子查询
【例】查询与“刘晨”在同一个系学习的学生
-- 确定 “刘晨” 所在系名select Sdept from Student where Sname = '刘晨';-- 查找所有在IS系学习的学生select Sno, Sname, sdept from Student where Sdept = 'IS';-- 合并查询select Sno, Sname, Sdept from Student where Sdept in ( select Sdept from Student where Sname = '刘晨';)
二、 相关子查询
【例】查询所有选修了1号课程的学生学号、姓名。用嵌套查询
select Sno, Sname from Student where exists (select * from SC where Sno = Student.Sno and Cno = '1');-- 注: EXISTS 关键字在 SQL 中用于检查子查询是否至少会返回一行数据,它通常与相关子查询一起使用。
④ 引出子查询的谓词
- 带有IN谓词的子查询
【例】查询选修了课程名为“信息系统”的学生学号和姓名
-- 最后在Student表中根据这个学生的学号取到相关学生的姓名select Sno, Sname from Student where Sno in ( -- 然后根据所找到的课程号在SC中算选出选修了3号课程的学生学号select Sno from SC where Cno in ( -- 首先在Course表中找出课程名为 "信息系统" 的课程号 select Cno from Course where Cname = '信息系统' ));
- 连接查询
select Sno, Sname from Student, SC, Course where Student.Sno = SC.Sno and SC.cno = Course.cnoand Course.Cname = '信息系统'
- 带有比较运算符的子查询
【例】查询与 “刘晨” 在同一个系学习的学生
select Sno, Sname, Sdept from Student where Sdept = (select Sdept from Student where Sname = '刘晨');
- 带有ANY或ALL谓词的子查询
-
ANY：任意一个值
-
ALL：所有值
需要配合使用比较运算符: > ANY大于子查询结果中的某个值 > ALL大于子查询结果中的所有值 = ANY大于等于子查询结果中的某个值 >= ALL大于等于子查询结果中的所有值 连接查询
- 由于带EXISTS量词的相关子查询只关心【内层查询是否有返回值】，并不需要查具体值，因此其效率并不一定低于其他形式的查询
例4：查询选修了课程的学生姓名
-- Way1:select Sname from Student where exists (select * from SC where Sno = Student.Sno);-- Way2:select Sname from Student, SC where Student.Sno = SC.Sno;-- Way3:select Sname from Student where sno in (select distinct sno from sc);
例5：查询选修了全部课程的学生姓名
-- 查询没有出现不选所有课的学生select Sname from Student where not exists ( select * from Course where not exists ( select * from SC where Sno = Student.Sno and Cno = Course.cno ));
例6：查询至少选修了学生19002选修的全部课程的学生号码
-- 不存在这样的课程y, 学生19002选了，但是学生x却没有选select distinct Sno from SC SCX where not exists (select * from SC SCY where SCY.Sno = '19002' and not exists ( select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno ));
4、集合查询
① 并操作（union）
UNION-- 参加UNION操作的各结果表的列数必须相同; 对应项的数据类型也必须相同
例1：查询计算机科学系的学生及年龄不大于19岁的学生
-- way1: 并select * from Student where Sdept = 'CS' union select * from Student where Sage 查询学校中未与教师同名的学生姓名
-- 使用exceptselect distinct Sname from Student exceptselect distinct Sname from Teacher;-- 用 in 谓词select distinct from Student where Sname not in (select Sname from Teacher;)
④ 对集合操作结果的排序
任何情况下，ORDER BY子句只能出现在最后
5、小结
SELECT [ALL|DISTINCT] [别名] [ ， [别名]] …FROM [别名] [ ， [别名]] …[WHERE ][GROUP BY [，] ... [HAVING ]][ORDER BY [ASC|DESC] [， [ASC|DESC] ] … ];
-
- 求最小值
- 求最大值
- 计算平均值
- 计算总和
- 转义字符（escape短语）
- 通配符
- 查询选修了课程的学生学号
-
- 取值约束
- 非空性约束
- 唯一性约束
- 外键约束
下一篇：搜狐邮箱怎么开启imap服务器?
