0


MySQL:语法速查手册【持续更新...】

在这里插入图片描述
在这里插入图片描述

一、定义基本表

1、常用的完整性约束

  • 主码约束
primarykey
  • 外键约束
foreignkey
  • 唯一性约束
unique
  • 非空性约束
notnull
  • 取值约束
check

2、例题

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

createtable Student(
    Sno char(5)notnullunique,
    Sname char(20)unique,
    Ssex char(1),
    Sage number,
    Sdept char(15));

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

createtable SC(
    Sno char(5),
    Cno char(3),
    Grade number,Primarykey(Sno, Cno));

二、修改基本表

1、语法格式

ALTERTABLE<表名>[ADD<新列名><数据类型>[ 完整性约束 ]][DROP<完整性约束名>][MODIFY<列名><数据类型>];
  • ADD子句:增加新列和新的完整性约束条件
  • DROP子句:删除指定的完整性约束条件
  • MODIFY子句:用于修改列名和数据类型

2、例题

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

altertable Student add Scome date;

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

altertable Student modify Sage char;

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

altertable Student dropunique(Sname);

三、删除基本表

1、语法格式

droptable<表名>

2、例题

【例1】删除Student表

droptable Student;

四、建立与删除索引

1、语法格式

-- 建立索引CREATE[UNIQUE]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);-- 删除索引DROPINDEX<索引名>;

2、例题

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

  • Student表按学号升序建唯一索引
  • Course表按课程号升序建唯一索引
  • SC表按学号升序和课程号降序建唯一索引
createuniqueindex Stusno on Student(Sno);createuniqueindex Coucno on Course(Cno);createuniqueindex SCno on SC(Sno ASC, Cno DESC);

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

dropindex Stusname;

五、查询

SELECT[ALL|DISTINCT]<目标列表达式>[<别名>][,<目标列表达式>[<别名>]] …
FROM<表名或视图名>[<别名>][, <表名或视图名>[<别名>]] …
[WHERE<条件表达式>][GROUPBY<列名>[,<列名>] …
                  [HAVING<条件表达式>]][ORDERBY<列名>[,<列名>] … [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 as2021-Sage from Student;

② 选择表中的若干元组

《消除取值重复的行》

在SELECT子句中使用

DISTINCT

短语

数据如下:

Sno        Cno       Grade
-----      ---       ----1900119219001285190013881900229019002380
  • 查询选修了课程的学生学号
-- 未优化前select Sno from SC;
selct all Sno from SC;

 结果: Sno   
    1900119001190011900219002--------------------------- 优化后selectdistinct Sno from SC;
 结果: Sno   
    1900119002

《查询满足条件的元组》

  1. 比较大小
=,>,<,>=,<=,!= 或 <>,>,!<

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

select Sname from Student where Sdep ='CS';

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

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

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

selectdistinct Sno from SC where Grade <60;
  1. 确定范围
between...and...notbetween...and...

【例1】查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄

select Sname, Sdept, Sage from Student where Sage between20and23;

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

select Sname, Sdept, Sage from Student wherenot Sage between20and23;
  1. 确定集合
IN<值表>NOTIN<值表>

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

select Sname, Ssex from Student where Sdept in('IS','MA','CS');
  1. 字符串匹配
LIKE 或 NOTLIKE[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 wherelike'_阳%';

【例4】查询所有不姓刘的学生姓名

select Sname from Student Sname notlike'刘%';
  • 转义字符(escape短语)

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

select Cno, Ccredit from Course where Cname like'DB\_Design'escape '\';
  1. 涉及空值的查询
isnull 或 isnotnull

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

select Sno, Cno from SC where Grade isnull;

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

select Sno, Cno from SC where Grade isnotnull;-- 有成绩即不为空
  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 between20and23;

③ 对查询结果排序

使用

order by

子句

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

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

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

结果:

Sno        Grade
-------    -------1901019024190079219003821901082190097519014611900255

【例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】查询学生总人数

selectcount(*)from Student;

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

selectcount(distinct Sno)from SC;-- 注:用DISTINCT以避免重复计算学生人数

【例3】计算1号课程的学生 平均 成绩

selectAVG(Grade)from SC where Cno ='1';

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

selectmax(Grade)from SC where Cno ='1';

⑤ 对查询结果分组

未对查询结果分组,集合函数将作用于 整个查询结果

对查询结果分组后,集合函数将 分别作用于每个组

====> 使用GROUP BY子句分组

【例1】求各个课程号及相应的选课人数

select Cno,count(Cno)from Sc Groupby Cno;--------------------------------------------
结果:
Cno        COUNT(Sno)122234344433548

【例2】求各个课程号及相应的课程成绩在90分以上的学生人数

select Cno,Count(Sno)from Sc where Grade >90Groupby Cno;----------------------------------------
结果
Cno        COUNT(Sno)113274358

====> 使用HAVING短语筛选最终输出结果

【例1】查询选修了3门以上课程的学生学号

select Sno from SC Groupby Sno HavingCount(*)>3;

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

select Sno,Count(*)from SC where Grade >90Groupby Sno HavingCount(*)>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 whereexists(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 whereexists(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 wherenotexists(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 whereexists(select*from Student S2 where S2.Sdept = S1.Sdept and S2.Sname ='刘晨');

效率对比

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

例4:查询选修了课程的学生姓名

-- Way1:select Sname from Student whereexists(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(selectdistinct sno from sc
);

例5:查询选修了全部课程的学生姓名

-- 查询没有出现不选所有课的学生select Sname from Student wherenotexists(select*from Course wherenotexists(select*from SC where Sno = Student.Sno and Cno = Course.cno
    ));

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

-- 不存在这样的课程y, 学生19002选了,但是学生x却没有选selectdistinct Sno from SC SCX wherenotexists(select*from SC SCY where SCY.Sno ='19002'andnotexists(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'unionselect*from Student where Sage <=19;-- way2: 或selectdistinctfrom Student where Sdept ='CS'or Sage <19;

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

select Sname from Student unionselect Sname from Teacher;

② 交操作(intersect)

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

-- 使用insersectselect Sno from SC where Cno ='1'intersectselect 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:查询学生姓名与教师姓名的交集

-- 使用insersectselectdistinct Sname from Student intersectselectdistinct Sname from Teacher;-- 用 in 谓词selectdistinctfrom Student where Sname in(select Sname from Teacher;)

③ 差操作(except, minus)

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

-- 使用exceptselectdistinct Sname from Student exceptselectdistinct Sname from Teacher;-- 用 in 谓词selectdistinctfrom Student where Sname notin(select Sname from Teacher;)

④ 对集合操作结果的排序

任何情况下,ORDER BY子句只能出现在最后

5、小结

SELECT[ALL|DISTINCT]<目标列表达式>[别名][ ,<目标列表达式>[别名]] …
FROM<表名或视图名>[别名][ ,<表名或视图名>[别名]] …
[WHERE<条件表达式>][GROUPBY<列名1>[,<列名1’>]...[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC][,<列名2’>[ASC|DESC]] …  ];

在这里插入图片描述

标签: mysql 数据库

本文转载自: https://blog.csdn.net/Fire_Cloud_1/article/details/134373417
版权归原作者 烽起黎明 所有, 如有侵权,请联系我们删除。

“MySQL:语法速查手册【持续更新...】”的评论:

还没有评论