数据库 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 92

19001 2 85

19001 3 88

19002 2 90

19002 3 80

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

-- 未优化前

select Sno from SC;

selct all Sno from SC;

结果： Sno

19001

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;

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

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

select distinct Sno from SC where Grade < 60;

确定范围

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

升序：ASC降序：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;

三、自身连接查询

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

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

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

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

② 嵌套查询分类

不相关子查询

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

夸智网

数据库 MySQL：语法速查手册【持续更新...】

温故知新，遇见WPF/WinForms，自动记忆和还原WinFroms窗体大小和位置及状态

hive job优化策略 hive 任务优化策略 hive job优化【大数据Hive】hive 优化策略之job任务优化

发表评论取消回复

夸智网

数据库 MySQL：语法速查手册【持续更新...】

温故知新，遇见WPF/WinForms，自动记忆和还原WinFroms窗体大小和位置及状态

hive job优化策略 hive 任务优化策略 hive job优化 【大数据Hive】hive 优化策略之job任务优化

相关文章

发表评论取消回复

hive job优化策略 hive 任务优化策略 hive job优化【大数据Hive】hive 优化策略之job任务优化