MySQL:语法速查手册【持续更新...】
作者:mmseoamin日期:2023-12-13

MySQL:语法速查手册【持续更新...】,在这里插入图片描述,第1张

MySQL:语法速查手册【持续更新...】,在这里插入图片描述,第2张

一、定义基本表

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         92
                19001       2         85
                19001       3         88
                19002       2         90
                19002       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. 比较大小
                  =, >, <, >=, <=, != 或 <>, >, !<
                  

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

                  select Sname from Student where Sdep = 'CS';
                  

                  【例2】查询所有年龄在20岁以下的学生姓名及其年龄

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

                  【例3】查询考试成绩有不及格的学生的学号

                  select distinct Sno from SC where Grade < 60;
                  
                  1. 确定范围
                  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;
                  
                  1. 确定集合
                  IN <值表>
                  NOT IN <值表>
                  

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

                  select Sname, Ssex from Student where Sdept in ('IS', 'MA', 'CS');
                  
                  1. 字符串匹配
                  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 '\';
                          
                          1. 涉及空值的查询
                          is null 或 is not null
                          

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

                          select Sno, Cno from SC where Grade is null;
                          

                          【例】查所有有成绩的学生学号和课程号

                          select Sno, Cno from SC where Grade is not null;	-- 有成绩即不为空
                          
                          1. 多重条件查询
                          • 用逻辑运算符AND和OR来联结多个查询条件
                            • AND的优先级高于OR
                            • 可以用括号改变优先级
                            • 可用来实现多种其他谓词
                              • [NOT] IN [NOT]
                              • BETWEEN … AND …

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

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

                                【例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子句

                                1. 升序:ASC
                                2. 降序:DESC

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

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

                                结果:

                                Sno        Grade
                                -------    -------
                                19010
                                19024
                                19007       92
                                19003       82
                                19010       82
                                19009       75
                                19014       61
                                19002       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>.]<列名1>  =  [<表名2>.]<列名2>
                                             -- 任何子句中引用表1和表2中同名属性时,都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀
                                            
                                            select Student.*, Sc.* from Student, SC where Student.Sno == SC.Sno;
                                            

                                            三、自身连接查询

                                            一个表与其自己进行连接,称为表的自身连接

                                            1. 需要给表起别名以示区别
                                            2. 由于所有属性名都是同名属性,因此必须使用别名前缀

                                            【例】查询每一门课的间接先修课(即先修课的先修课)

                                            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';
                                                  

                                                  ② 嵌套查询分类

                                                  1. 不相关子查询
                                                  • 子查询的查询条件不依赖于父查询
                                                    1. 相关子查询
                                                    • 子查询的查询条件依赖于父查询

                                                      ③ 嵌套查询求解方法

                                                      一、不相关子查询

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

                                                      -- 确定 “刘晨” 所在系名
                                                      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 中用于检查子查询是否至少会返回一行数据,它通常与相关子查询一起使用。
                                                      

                                                      ④ 引出子查询的谓词

                                                      1. 带有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.cno
                                                        		and Course.Cname = '信息系统'
                                                        
                                                        1. 带有比较运算符的子查询

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

                                                        select Sno, Sname, Sdept from Student where Sdept = (
                                                        	select Sdept from Student where Sname = '刘晨'
                                                        );
                                                        
                                                        1. 带有ANY或ALL谓词的子查询
                                                        • ANY:任意一个值

                                                        • ALL:所有值

                                                          需要配合使用比较运算符:
                                                              > ANY	大于子查询结果中的某个值       
                                                               > ALL	大于子查询结果中的所有值
                                                              < ANY	小于子查询结果中的某个值    
                                                              < ALL	小于子查询结果中的所有值
                                                              >= ANY	大于等于子查询结果中的某个值    
                                                              >= ALL	大于等于子查询结果中的所有值
                                                              <= ANY	小于等于子查询结果中的某个值    
                                                              <= ALL	小于等于子查询结果中的所有值
                                                              = ANY	等于子查询结果中的某个值        
                                                              =ALL	等于子查询结果中的所有值(通常没有实际意义)
                                                              !=(或<>)ANY	不等于子查询结果中的某个值
                                                              !=(或<>)ALL	不等于子查询结果中的任何一个值
                                                          

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

                                                          select Sname, Sage from Student where Sage < any(
                                                          	select Sage from Student where Sdept = 'IS'
                                                          ) and Sdept <> 'IS';
                                                          
                                                          1. 带有EXISTS谓词的子查询

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

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

                                                              例1:查询所有选修了1号课程的学生姓名

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

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

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

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

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

                                                              效率对比

                                                              • 不相关子查询 > 相关子查询 > 连接查询
                                                              • 由于带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 <= 19;
                                                                -- way2: 或
                                                                select distinct from Student where Sdept = 'CS' or Sage < 19;
                                                                

                                                                例2:查询学校中所有师生的姓名

                                                                select Sname from Student union
                                                                	select Sname from Teacher;
                                                                

                                                                ② 交操作(intersect)

                                                                例1:查询选修课程1的学生集合与选修课程2的学生集合的交集

                                                                -- 使用insersect
                                                                select Sno from SC where Cno = '1' intersect
                                                                	select Sno from SC where Cno = '2';
                                                                -- 用 in 谓词
                                                                select Sno from SC where Cno = '1' and Sno in (
                                                                	select Sno from SC where Cno = '2'
                                                                )
                                                                

                                                                例2:查询学生姓名与教师姓名的交集

                                                                -- 使用insersect
                                                                select distinct Sname from Student intersect
                                                                	select distinct Sname from Teacher;
                                                                -- 用 in 谓词
                                                                select distinct from Student where Sname in (
                                                                	select Sname from Teacher;
                                                                )
                                                                

                                                                ③ 差操作(except, minus)

                                                                例1:查询学生姓名与教师姓名的差 ——> 查询学校中未与教师同名的学生姓名

                                                                -- 使用except
                                                                select distinct Sname from Student except
                                                                	select 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 <列名1>[,<列名1’>] ...
                                                                		   [HAVING <条件表达式>]]
                                                                [ORDER BY <列名2> [ASC|DESC] 
                                                                           [,<列名2’> [ASC|DESC] ] …  ];
                                                                

                                                                MySQL:语法速查手册【持续更新...】,在这里插入图片描述,第3张