MySQL：语法速查手册【连续更新...】

一、界说根本表

1、常用的完整性束缚

• 主码束缚
  

1. primary key

复制代码

• 外键束缚
  

1. foreign key

复制代码

• 唯一性束缚
  

1. unique

复制代码

• 非空性束缚
  

1. not null

复制代码

• 取值束缚
  

1. check

复制代码

2、例题

【例1】建立一个“门生”表Student，由学号Sno、姓名Sname、性别Ssex、年龄Sage、地点系Sdept五个属性组成。此中学号不能为空，值是唯一的，而且姓名取值也唯一

1. create table Student(        Sno char(5) not null
2. unique
3. ,    Sname char(20) unique
4. ,    Ssex char(1),    Sage number,    Sdept char(15));

复制代码

【例2】建立一个“门生选课”表SC，它由学号Sno、课程号Cno，修课结果Grade组成，此中(Sno, Cno)为主码

1. create table SC(
2.         Sno char(5),
3.     Cno char(3),
4.     Grade number,
5.     Primary key(Sno, Cno)
6. );

复制代码

---

二、修改根本表

1、语法格式

1. ALTER TABLE <表名>
2. [ ADD <新列名> <数据类型> [ 完整性约束 ] ]
3. [ DROP <完整性约束名> ]
4. [ MODIFY <列名> <数据类型> ];

复制代码

• ADD子句：增加新列和新的完整性束缚条件
  
• DROP子句：删除指定的完整性束缚条件
  
• MODIFY子句：用于修改列名和数据类型
  

2、例题

【例1】向Student表增加 “入学时间” 列，其数据类型为日期型

1. alter table Student add Scome date;

复制代码

【例2】将年龄的数据类型改为字符型

1. alter table Student modify Sage char;

复制代码

【例3】删除门生姓名必须取唯一值的束缚

1. alter table Student drop unique
2. (Sname);

复制代码

---

三、删除根本表

1、语法格式

1. drop table <表名>

复制代码

2、例题

【例1】删除Student表

1. drop table Student;

复制代码

四、建立与删除索引

1、语法格式

1. -- 建立索引
2. CREATE [UNIQUE] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
3. -- 删除索引
4. DROP INDEX <索引名>;

复制代码

2、例题

【例1】为门生 - 课程数据库中的Student，Course，SC三个表建立索引。

• Student表按学号升序建唯一索引
  
• Course表按课程号升序建唯一索引
  
• SC表按学号升序和课程号降序建唯一索引
  

1. create unique
2. index Stusno on Student(Sno);create unique
3. index Coucno on Course(Cno);create unique
4. index SCno on SC(Sno ASC, Cno DESC);

复制代码

【例2】删除Student表的Stusname索引

1. drop index Stusname;

复制代码

五、查询

1. SELECT [ALL|DISTINCT]   <目标列表达式> [<别名>]
2.                  [，<目标列表达式>[<别名>]] …
3. FROM    <表名或视图名>[<别名>]
4.                 [， <表名或视图名>[<别名>] ] …
5. [ WHERE <条件表达式> ]
6. [ GROUP BY <列名> [, <列名>] …
7.                   [ HAVING <条件表达式> ] ]
8. [ ORDER BY <列名> [, <列名>] … [ ASC|DESC ] ];

复制代码

① from 子句 —— 指定查询对象(根本表或视图)
② where 子句：指定查询条件
③ group by 子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组
④ having 短句：筛选出满意指定条件的组
⑤ order by 子句：对查询结果表按指定列值的升序或降序排序
   以下是示例数据库

1. 学生 - 课程数据库
2. 学生表：
3.         Student(Sno，Sname，Ssex，Sage，Sdept)
4. 课程表：
5.         Course(Cno，Cname，Cpno，Ccredit)
6. 学生选课表：
7.          SC(Sno，Cno，Grade)

复制代码

1、单表查询

① 选择表中的若干列

《查询指定列》
【例1】查询全体门生的学号与姓名

1. select Sno, Sname from Student;

复制代码

【例2】查询全体门生的姓名、学号、地点系

1. select Sname, Sno, Sdept from Student;

复制代码

《查询全部列》
【例】查询全体门生的具体记录

1. select  Sno，Sname，Ssex，Sage，Sdept from Student；
2. select * from Student;

复制代码

《查询经过计算的值》
【例】 查全体门生的姓名及其出生年份（列别名）

1. select Sname, Sage as 2021-Sage from Student;

复制代码

② 选择表中的若干元组

《消除取值重复的行》
   在SELECT子句中使用 DISTINCT 短语
  数据如下：

1. Sno        Cno       Grade
2. -----      ---       ----
3. 19001       1         92
4. 19001       2         85
5. 19001       3         88
6. 19002       2         90
7. 19002       3         80

复制代码

• 查询选修了课程的门生学号
  

1. -- 未优化前
2. select Sno from SC;
3. selct all Sno from SC;
5. 结果： Sno   
6.         19001  
7.     19001  
8.     19001  
9.     19002  
10.     19002
11. -------------------------
12. -- 优化后
13. select distinct Sno from SC;
14. 结果： Sno   
15.         19001  
16.     19002

复制代码

《查询满意条件的元组》

• 比较大小
  

1. =, >, <, >=, <=, != 或 <>, >, !<

复制代码

【例1】查询计算机系全体门生的名单

1. select Sname from Student where Sdep = 'CS';

复制代码

【例2】查询全部年龄在20岁以下的门生姓名及其年龄

1. select Sname, Sage from Student where age < 20;
2. select Sname, Sage from Student where not age >= 20;

复制代码

【例3】查询考试结果有不及格的门生的学号

1. select distinct Sno from SC where Grade < 60;

复制代码

• 确定范围
  

1. between ... and ...
2. not between ... and ...

复制代码

【例1】查询年龄在20~23岁（包罗20岁和23岁）之间的门生的姓名、系别和年龄

1. select Sname, Sdept, Sage from Student where Sage between 20 and 23;

复制代码

【例2】查询年龄不在20~23岁之间的门生姓名、系别和年龄

1. select Sname, Sdept, Sage from Student where not Sage between 20 and 23;

复制代码

• 确定聚集
  

1. IN <值表>
2. NOT IN <值表>

复制代码

【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）门生的姓名和性别

1. select Sname, Ssex from Student where Sdept in ('IS', 'MA', 'CS');

复制代码

• 字符串匹配
  

1. LIKE 或 NOT LIKE
2. [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

复制代码

• 固定模版字符串
  

【例】查询学号为95001的门生的具体情况

1. select * from Student where Sno like '19001';
2. select * from Student where Sno = '19001';

复制代码

• 通配符
  
  
  
  • % —— 代表恣意长度
    
  • _ —— 代表单个字符
    
  
  

【例1】查询全部姓刘门生的姓名、学号和性别

1. select Sname, Sno, Ssex from Student Sname like '刘%';

复制代码

【例2】 查询姓 “欧阳” 且全名为三个汉字的门生的姓名

1. select Sname from Student where Sname like '欧阳_';

复制代码

【例3】查询名字中第2个字为"阳"字的门生的姓名和学号

1. select Sname, Sno from Student where like '_阳%';

复制代码

【例4】查询全部不姓刘的门生姓名

1. select Sname from Student Sname not like '刘%';

复制代码

• 转义字符（escape短语）
  

【例】查询DB_Design课程的课程号和学分

1. select Cno, Ccredit from Course where Cname like 'DB\_Design' escape '\';

复制代码

• 涉及空值的查询
  

1. is null 或 is not null

复制代码

【例】某些门生选修课程后没有参加考试，所以有选课记录，但没有考试结果。查询缺少结果的门生的学号和相应的课程号

1. select Sno, Cno from SC where Grade is null;

复制代码

【例】查全部有结果的门生学号和课程号

1. select Sno, Cno from SC where Grade is not null
2. ;        -- 有结果即不为空

复制代码

• 多重条件查询
  

• 用逻辑运算符AND和OR来联结多个查询条件
  
  
  
  • AND的优先级高于OR
    
  • 可以用括号改变优先级
    
  
  
• 可用来实现多种其他谓词
  
  
  
  • [NOT] IN [NOT]
    
  • BETWEEN … AND …
    
  
  

【例1】查询计算机系年龄在20岁以下的门生姓名

1. select Sname from Student where Sdept = 'CS' and Sage < 20;

复制代码

【例2】查询信息系（IS）、数学系（MA）和计算机科学系（CS）门生的姓名和性别

1. select Sname, Ssex from Student where Sdept in ('IS', 'MA',' CS');

复制代码

【例3】查询年龄在20~23岁（包罗20岁和23岁）之间的门生的姓名、系别和年龄

1. select Sname, Sdept, Sage from Student where Sage between 20 and 23;

复制代码

③ 对查询结果排序

   使用order by子句
  

• 升序：ASC
  
• 降序：DESC
  

  【例1】查询选修了3号课程的门生的学号及其结果，查询结果按分数降序排列

1. select Sno, Grade from SC where Cno = '3' order by Grade desc;

复制代码

结果：

1. Sno        Grade
2. -------    -------
3. 19010
4. 19024
5. 19007       92
6. 19003       82
7. 19010       82
8. 19009       75
9. 19014       61
10. 19002       55

复制代码

【例2】查询全体门生情况，查询结果按地点系的系号升序排列，同一系中的门生按年龄降序排列

1. select * from Student where Sdept, Sage desc;

复制代码

④ 使用聚集函数

• 计数
  

1. COUNT（[DISTINCT|ALL] *）
2. COUNT（[DISTINCT|ALL] <列名>）

复制代码

• 计算总和
  

1. SUM（[DISTINCT|ALL] <列名>）

复制代码

• 计算均匀值
  

1. AVG（[DISTINCT|ALL] <列名>）

复制代码

• 求最大值
  

1. MAX（[DISTINCT|ALL] <列名>）

复制代码

• 求最小值
  

1. MIN（[DISTINCT|ALL] <列名>）

复制代码

【例1】查询门生总人数

1. select count(*) from Student;

复制代码

【例2】查询选修了课程的门生人数

1. select count(distinct Sno) from SC;
2. -- 注：用DISTINCT以避免重复计算学生人数

复制代码

【例3】计算1号课程的门生 均匀 结果

1. select AVG(Grade) from SC where Cno = '1';

复制代码

【例4】查询选修1号课程的门生 最高 分数

1. select max(Grade) from SC where Cno = '1';

复制代码

⑤ 对查询结果分组

   未对查询结果分组，聚集函数将作用于 整个查询结果
  对查询结果分组后，聚集函数将 分别作用于每个组
  ====> 使用GROUP BY子句分组
【例1】求各个课程号及相应的选课人数

1. select Cno, count(Cno) from Sc Group by Cno;
2. --------------------------------------------
3. 结果:
4. Cno        COUNT(Sno)
5. 1             22
6. 2             34
7. 3             44
8. 4             33
9. 5             48     

复制代码

【例2】求各个课程号及相应的课程结果在90分以上的门生人数

1. select Cno, Count(Sno) from Sc where Grade > 90 Group by Cno;
2. ----------------------------------------
3. 结果
4. Cno        COUNT(Sno)
5. 1            13
6. 2             7
7. 4             3
8. 5             8  

复制代码

====> 使用HAVING短语筛选最终输出结果
【例1】查询选修了3门以上课程的门生学号

1. select Sno from SC Group by Sno Having Count(*) > 3;

复制代码

【例2】 查询有3门以上课程在90分以上的门生的学号及90分以上的课程数

1. select Sno, Count(*) from SC where Grade > 90 Group by Sno Having Count(*) > 3;

复制代码

  HAVING短语与WHERE子句的区别？
  

• WHERE子句作用于基表或视图，从中选择满意条件的元组
  
• HAVING短语作用于组，从中选择满意条件的组
  

2、连接查询

   同时涉及多个表的查询称为连接查询
  一、广义笛卡尔积

1. select Student.*, Sc.* from Student, SC;
2. -- .*代表这个表中所有的字段

复制代码

二、等值与非等值连接查询
   连接运算符为 = 的连接操作

1. [<表名1>.]<列名1>  =  [<表名2>.]<列名2>
2. -- 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀

复制代码

1. select Student.*, Sc.* from Student, SC where Student.Sno == SC.Sno;

复制代码

三、自身连接查询
   一个表与其自己举行连接，称为表的自身连接
  

• 需要给表起别名以示区别
  
• 由于全部属性名都是同名属性，因此必须使用别名前缀
  

【例】查询每一门课的间接先修课（即先修课的先修课）

1. select a.Cno, b.Cno from Course a, Course b where a.Cpon = b.Cno;

复制代码

四、外连接查询
   外连接与平凡连接的区别
  

• 平凡连接操作只输出满意连接条件的元组
  
• 外连接操作以指定表为连接主体，将主体表中不满意连接条件的元组一并输出
  

【例】以门生为主体，查询每个门生及其选修课程的情况 (用外连接)

1. SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student，SC
2.                      WHERE  Student.Sno = SC.Sno(+);

复制代码

五、复合条件连接查询
   WHERE子句中含多个连接条件时，称为复合条件连接
  假设学校中性别相同的门生不会重名。现如下设计门生表和选课表：

1. Std(Sname, Ssex, Sage, Sdept)
2. StdC(Sname, Ssex, Cno, Grade)

复制代码

【例1】查询选修2号课程且结果在90分以上的全部门生的姓名，性别及地点系

1. select Sname, Ssex, Sdept from Std, StdC
2.                 where Std.Sname = StdC.Sname and Std.Ssex = StdC.Ssex
3.                   and StdC.Cno = '2'
4.                   and StdC.Grade > 90;

复制代码

【例2】查询每个门生的学号、姓名、选修的课程名及结果（ Student，SC，Course ）

1. select Student.Sno, Student.Sname, Course.Cname, SC.Grade
2. from Student, SC, Course
3.         where Student.Sno = SC.Sno
4.     and SC.Cno = Course.Cno;

复制代码

3、嵌套查询

① 嵌套查询概述

【一个查询块】：一个SELECT-FROM-WHERE语句
【嵌套查询】：将一个查询块嵌套在另一个查询块的 WHERE子句 或 HAVING短语 的条件中的查询

---

1. -- 查询所有选修了2号课程的学生姓名
2. -- 外层查询/父查询
3. select Sname from Student where Sno in (
4.     -- 内层查询/子查询
5.         select Sno from SC where Cno = '02'       
6. );

复制代码

• 子查询的限制
  
  
  
  • 不能使用order by子句
    
  
  
• 层层嵌套方式反映了 SQL语言的结构化
  
• 有些嵌套查询可以用连接运算替代
  

1. select Sname from Student, SC where Student.Sno = SC.Sno and Cno = '02';

复制代码

② 嵌套查询分类

• 不相关子查询
  

• 子查询的查询条件不依赖于父查询
  

• 相关子查询
  

• 子查询的查询条件依赖于父查询
  

③ 嵌套查询求解方法

一、不相关子查询
【例】查询与“刘晨”在同一个系学习的门生

1. -- 确定 “刘晨” 所在系名
2. select Sdept from Student where Sname = '刘晨';
3. -- 查找所有在IS系学习的学生
4. select Sno, Sname, sdept from Student where Sdept = 'IS';
5. -- 合并查询
6. select Sno, Sname, Sdept from Student where Sdept in (
7.     select Sdept from Student where Sname = '刘晨';
8. )

复制代码

二、 相关子查询
【例】查询全部选修了1号课程的门生学号、姓名。用嵌套查询

1. select Sno, Sname from Student where exists (
2.         select * from SC where Sno = Student.Sno and Cno = '1'
3. );
4. -- 注: EXISTS 关键字在 SQL 中用于检查子查询是否至少会返回一行数据,它通常与相关子查询一起使用。

复制代码

④ 引出子查询的谓词

• 带有IN谓词的子查询
  

【例】查询选修了课程名为“信息系统”的门生学号和姓名

1. -- 最后在Student表中根据这个学生的学号取到相关学生的姓名
2. select Sno, Sname from Student where Sno in (
3.     -- 然后根据所找到的课程号在SC中算选出选修了3号课程的学生学号
4.         select Sno from SC where Cno in (
5.         -- 首先在Course表中找出课程名为 "信息系统" 的课程号
6.         select Cno from Course where Cname = '信息系统'
7.     )
8. );

复制代码

• 连接查询
  

1. select Sno, Sname from Student, SC, Course
2.                 where Student.Sno = SC.Sno and SC.cno = Course.cno
3.                 and Course.Cname = '信息系统'

复制代码

• 带有比较运算符的子查询
  

【例】查询与 “刘晨” 在同一个系学习的门生

1. select Sno, Sname, Sdept from Student where Sdept = (
2.         select Sdept from Student where Sname = '刘晨'
3. );

复制代码

• 带有ANY或ALL谓词的子查询
  

• ANY：恣意一个值
  
• ALL：全部值
  

1. 需要配合使用比较运算符:
2.     > ANY        大于子查询结果中的某个值      
3.      > ALL        大于子查询结果中的所有值
4.     < ANY        小于子查询结果中的某个值   
5.     < ALL        小于子查询结果中的所有值
6.     >= ANY        大于等于子查询结果中的某个值   
7.     >= ALL        大于等于子查询结果中的所有值
8.     <= ANY        小于等于子查询结果中的某个值   
9.     <= ALL        小于等于子查询结果中的所有值
10.     = ANY        等于子查询结果中的某个值        
11.     =ALL        等于子查询结果中的所有值（通常没有实际意义）
12.     !=（或<>）ANY        不等于子查询结果中的某个值
13.     !=（或<>）ALL        不等于子查询结果中的任何一个值

复制代码

【例】查询其他系中比信息系某一门生年龄小的门生姓名和年龄

1. select Sname, Sage from Student where Sage < any(
2.         select Sage from Student where Sdept = 'IS'
3. ) and Sdept <> 'IS';

复制代码

• 带有EXISTS谓词的子查询
  

   exists作为 where 条件时，是先对where 前的主查询举行查询，然后用主查询的结果一个一个的代入exists的子查询举行判定，如果为真则输出当前这一条主查询的结果，否则不输出
  

• 存在量词
  
• 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
  
  
  
  • 若内层查询结果 非空，则返回真值
    
  • 若内层查询结果为 空，则返回假值
    
  
  

例1：查询全部选修了1号课程的门生姓名

1. -- 嵌套查询
2. select sname from Student where exists (
3.         select * from SC where sno = Student.sno and cno = '1';
4. );
5. -- 连接查询
6. select sname from Student, SC where Student.sno = SC.sno and cno = '1';

复制代码

例2：查询没有选修了1号课程的门生姓名

1. select sname from Student where not exists (
2.         select * from SC where sno = Student.sno and cno = '1';
3. );

复制代码

例3：查询与“刘晨”在同一个系学习的门生

1. -- 不相关子查询
2. select Sno, Sname, Sdept from Student where Sdept in (
3.     select Sdept from Student where Sname = '刘晨';
4. )
5. -- 带EXISTS谓词的子查询替换
6. select Sno, Sname, Sdept from Student S1 where exists (
7.         select * from Student S2 where S2.Sdept = S1.Sdept and S2.Sname = '刘晨'
8. );

复制代码

  服从对比
  

• 不相关子查询 > 相关子查询 > 连接查询
  
• 由于带EXISTS量词的相关子查询只关心【内层查询是否有返回值】，并不需要查具体值，因此其服从并不一定低于其他情势的查询
  

例4：查询选修了课程的门生姓名

1. -- Way1:
2. select Sname from Student where exists (
3.         select * from SC where Sno = Student.Sno
4. );
5. -- Way2:
6. select Sname from Student, SC where Student.Sno = SC.Sno;
7. -- Way3:
8. select Sname from Student where sno in (
9.         select distinct sno from sc
10. );

复制代码

例5：查询选修了全部课程的门生姓名

1. -- 查询没有出现不选所有课的学生
2. select Sname from Student where not exists (
3.     select * from Course where not exists (
4.             select * from SC where Sno = Student.Sno and Cno = Course.cno
5.     )
6. );

复制代码

例6：查询至少选修了门生19002选修的全部课程的门生号码

1. -- 不存在这样的课程y, 学生19002选了，但是学生x却没有选
2. select distinct Sno from SC SCX where not exists (
3.         select * from SC SCY where SCY.Sno = '19002' and not exists (
4.             select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno
5.     )
6. );

复制代码

4、聚集查询

① 并操作（union）

1. <查询块>
2.          UNION
3. <查询块>
4. -- 参加UNION操作的各结果表的列数必须相同; 对应项的数据类型也必须相同

复制代码

例1：查询计算机科学系的门生及年龄不大于19岁的门生

1. -- way1: 并
2. select * from Student where Sdept = 'CS' union
3.         select * from Student where Sage <= 19;
4. -- way2: 或
5. select distinct from Student where Sdept = 'CS' or Sage < 19;

复制代码

例2：查询学校中全部师生的姓名

1. select Sname from Student union
2.         select Sname from Teacher;

复制代码

② 交操作（intersect）
例1：查询选修课程1的门生聚集与选修课程2的门生聚集的交集

1. -- 使用insersect
2. select Sno from SC where Cno = '1' intersect
3.         select Sno from SC where Cno = '2';
4. -- 用 in 谓词
5. select Sno from SC where Cno = '1' and Sno in (
6.         select Sno from SC where Cno = '2'
7. )

复制代码

例2：查询门生姓名与西席姓名的交集

1. -- 使用insersect
2. select distinct Sname from Student intersect
3.         select distinct Sname from Teacher;
4. -- 用 in 谓词
5. select distinct from Student where Sname in (
6.         select Sname from Teacher;
7. )

复制代码

③ 差操作（except, minus）
例1：查询门生姓名与西席姓名的差 ——> 查询学校中未与西席同名的门生姓名

1. -- 使用except
2. select distinct Sname from Student except
3.         select distinct Sname from Teacher;
4. -- 用 in 谓词
5. select distinct from Student where Sname not in (
6.         select Sname from Teacher;
7. )

复制代码

④ 对聚集操作结果的排序
   任何情况下，ORDER BY子句只能出如今末了
  5、小结

1. SELECT [ALL|DISTINCT] <目标列表达式>
2.            [别名] [ ，<目标列表达式> [别名]] …
3. FROM <表名或视图名> [别名]
4.            [ ，<表名或视图名> [别名]] …
5. [WHERE <条件表达式>]
6. [GROUP BY <列名1>[，<列名1’>] ...
7.                    [HAVING <条件表达式>]]
8. [ORDER BY <列名2> [ASC|DESC]
9.            [，<列名2’> [ASC|DESC] ] …  ];

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。