SQL基础练习

基于MySQL的常用语句的练习

Posted by Nathaniel on 2016-05-24

实验环境

硬件环境

  • 处理器: i5-5257U 2.7-2.9GHz
  • 内存: 8 GB 1867MHz DDR3
  • 硬盘: APPLE SSD SM0 256G

软件环境

  • 操作系统: OSX 10.11.4
  • DBMS版本: MySQL 5.7.12 Community Server (GPL)
  • DBMS图形界面: MySQL Workbench 6.3

实验内容

基本表

基本表的定义

语法

​ 定义基本表的语句格式为:

CREATE TABLE 关系名(
属性名1 数据类型1
属性名2 数据类型2
<完整性约束1>
<完整性约束2>
);

实践

​ 执行如下语句之后

CREATE TABLE Student(
Sno CHAR(6),
Sname CHAR(8) NOT NULL,
Sage INT,
Ssex CHAR(2),
Sdept CHAR(12),
PRIMARY KEY(Sno),
CHECK (Sage > 0)
);

B_CREATE1B_CREATE2

观察Workbench中结果可见,Student表创建成功了,下属列也都正确,完整性约束也已应用。

基本表的修改

语法

​ 已经创建好的表,可以修改其结构:

ALTER TABLE <表名>
ADD<列名><数据类型>[<完整性约束>]
DROP<列名>[CASCADE|RESTRICT]
ALTER<列名><数据类型>

实践

​ 基于刚才的结果,打算进行以下操作:

  1. 添加一个新列Ssex2,大小同Ssex,有NOT NULL约束

  2. 扩大Sname 的存储空间到 CHAR(20)

  3. 丢掉Ssex

    ​MySQL 语法与标准SQL 略有不同

    ​执行以下语句后:

ALTER TABLE Student
ADD Ssex2 CHAR(2) NOT NULL,
MODIFY COLUMN Sname CHAR(20),
DROP COLUMN Ssex

B_ALTER1

故可知,新列添加成功,Ssex丢弃成功,Sname变为CHAR(20)

基本表的删除

语法

DROP TABLE <表名>

实践

DROP TABLE Student;

B_DELETE1

执行后可见,Student表成功被删除

索引

建立索引

语法

​ 建立索引的语句格式是:

CREATE [UNIQUE][CLUSTER] INDEX<索引名>
ON <基本表名>(<列名>[次序],<列名>[次序]);

实践

​ 基于之前Student 表:

CREATE UNIQUE INDEX INDEX_SNAME
ON Student(SName);

​ 使用如下语句查看创建的 INDEX(MySQL特有方法)

SHOW INDEX FROM Student;

I_CREATE1

由图可知,Student表上现在存在 主键索引 和 我们刚刚创建成功的 Sname 上的索引

删除索引

语法

DROP INDEX<索引名>

实践

ALTER TABLE Student
DROP INDEX INDEX_SNAME;

I_DELETE1

由图知,刚刚建立的 SName上的INDEX_SNAME 被删除

数据查询

单表查询

语法

SELECT [ALL|DISTINCT] <目标表列>
FROM <基本表或视图名>[,<基本表或视图名>]
[WHERE <条件表达式>]
[GROUP BY<列名1> [HAVING <条件表达式1>]]
[ORDER BY<列名>[ASC|DESC]]

实践

D_SELECT1.0

​ 基于之前的 Student表,自行添加了部分数据

现在打算进行以下几步操作:

1.选出所有的计算机专业的同学 的学号和名字

SELECT Sno,Sname
FROM Student

得出结果如下 D_SELECT1.1

可见成功地选出了专业为计算机的4位同学

2.选出在18-20岁之间的女同学

SELECT * FROM Student
WHERE (Ssex = "女") AND (Sage BETWEEN 18 AND 20)

D_SELECT1.2

可见成功选出了18-20岁的女同学

3.计算出 按专业分组的 同学们的平均年龄

SELECT Sdept,avg(Sage) FROM Student
GROUP BY Sdept

D_SELECT1.3

可见成功计算出了各个专业的同学的平均年龄

4.找出姓李的 名字为2个字 的同学

SELECT * FROM Student
WHERE Sname LIKE "李_"

D_SELECT1.4

可见,李梅梅并未在其中,说明选择成功

连接查询

​ 涉及2个及2个以上表的查询的时候,需要连接后再查询,一般连接条件是两表中的同名属性

D_SELECT2.0.1 D_SELECT2.0.2

Course表,SC表内容如上所示

语法

​ 有时候要一个表自身进行连接查询时,要用AS 来定义别名

<基表名> AS <别名>

实践

打算进行的操作:

1.查询成绩在70到80分的学生的学号和姓名

SELECT SC.Sno,Student.Sname
FROM Student,SC
WHERE (Grade BETWEEN 70 AND 80) AND
SC.Sno = Student.Sno;

D_SELECT2.1可见,70-80分的同学被选了出来

(为防止二义性,SELECT处的列名需指定表名)

2.查询成绩在90分及以上的学生的学号、姓名和对应的课程名

SELECT SC.Sno,Student.Sname,Cname
FROM Student,SC,Course
WHERE Grade >= 90 AND
SC.Sno = Student.Sno AND
SC.Cno = Course.Cno

D_SELECT2.2

可见,成功地选出了成绩在90分及以上的同学的信息

3.查询和赵刚在同一个系的学生

SELECT S2.*
FROM Student AS S1,Student AS S2
WHERE S1.Sname = "赵刚" AND S2.Sdept = S1.Sdept;

D_SELECT2.3

可见,成功选出了和赵刚同专业的同学

嵌套查询

SELECT语句可以出现在查询条件中国,称为嵌套查询或子查询,子查询可以嵌套多层

​ 可以使用以下的常见的查询谓词:

谓词 用法
IN 用于判断该值是否在给定的集合中,常用于固定集 WHERE [field] IN (1,2,3)
EXITSTS 同上,但是常用于选择后的结果集,常用NOT形式 WHERE EXISTS (SELECT…)
ANY 存在量词,表示任意一个,在后续集中有一个成立则成立,可用于逻辑关系运算
ALL 全称量词,表示所有的,在后续集中必须全部成立才成立

打算实现的操作:

实践

1.查询与刘美 同一专业的同学

SELECT * FROM Student
WHERE Sdept IN(
SELECT Sdept FROM Student
WHERE Sname = "刘美"
)

D_SELECT3.1由结果可见其成功

2.查询没有选课号为0701的同学

SELECT Sname FROM Student
WHERE NOT EXISTS(
SELECT * FROM SC
WHERE Cno = "0701" AND
SC.Sname = Student.Sname
)

D_SELECT3.2可见,选出了所有没有选C语言(课号为0701)的同学

3.查询选了课号为0702的课且成绩比赵志高的同学

SELECT Student.Sname,SC.Grade FROM Student,SC
WHERE Cno = "0702" AND
Student.Sno = SC.Sno AND
Grade > (
SELECT Grade FROM SC,Student
WHERE Student.Sname = "赵志" AND
Cno = "0702" AND Student.Sno = SC.SNo
)

假如SC表中没有Sname域的情况下应该如上说写,如果有则可以简化如下

SELECT Sname,Grade FROM SC
WHERE Cno = "0702" AND
Grade > (
SELECT Grade FROM SC
WHERE Sname = "赵志"
)

D_SELECT3.3

可见的确找出了选了计算机网络(课号)0702成绩比赵志高的同学

4.查询选了0701号课的 成绩最低 的同学的学号

SELECT Sno FROM SC
WHERE Grade <= ALL(
SELECT Grade FROM SC
)

D_SELECT3.4故可知选出了张三(学号1800),他是选了C语言(课号0701)的成绩最低的同学

集合查询

常用集合操作

集合操作名 用法
UNION $\cup$ 并运算,$A \cup B = {x\mid x \in A \vee x \in B}$
INTERSECT $\cap$ 交运算,$A \cap B = {x \mid x \in A \wedge x \in B}$
MINUS $-$ 差集运算,$A - B = {x\mid x \in A \wedge X \notin B}$

实践

要实现以下操作:

1.查询计算机系学生的学号机选了0506号课的学生的并集

SELECT Sno FROM SC
WHERE Cno = "0506"
UNION
SELECT Sno FROM Student
WHERE Sdept = "计算机"

D_SELECT4.1可见选择成功

既有计算机专业的同学,也有选了机械CAD(课号0506)的同学

2.查询计算机专业的同学里面没有选0701号课的同学

​ 由于MySQL 中不支持 INTERSECTMINUS运算

​ 故使用INNER JOIN<>来替换

SELECT Student.SName FROM Student,SC
WHERE Student.Sno = SC.Sno AND
Sdept = "计算机" AND Cno <> "0701"

D_SELECT4.2故知,实现了与MINUS相同的功能

3.查询计算机专业的同学里面选了0701号课的同学

SELECT SC.Sno,Student.Sname
FROM Student INNER JOIN SC USING(Sno)
WHERE Sdept = "计算机" AND Cno = "0701"

D_SELECT4.3故知实现了与INTERSECT相同的功能

数据更新

插入

​ 可以一次插入一个或多个元组,有2种格式,如下:

语法

INSERT INTO <表名>[(列名1][,列名2])
VALUES(<常量1>[,<常量2>]...);
INSERT INTO <表名>[(列名1][,列名2])
<SELECT 语句>;

实践

​ 之前在进行连接查询前创建数据时,使用了如下语句:

INSERT INTO SC (Sno,Sname)
SELECT Sno,Sname FROM Student;

D_INSERT1再次查询SC内容结果如左

说明成功把Student表里面的Sno,Sname 重用了

修改

​ 注意在使用 UPDATE语句时,一次可以修改一个或多个列的值

​ 若省略 WHERE子句,则默认修改表中所有元组的值,因此使用时一定要小心

语法

UPDATE <>
SET 列名1=<表达式1>[,列名2=<表达式2>]...
[WHERE <条件表达式>];

实践

1.把选了C语言课的学生的成绩清空

UPDATE SC
SET Grade = NULL
WHERE Cno = (
SELECT Cno FROM Course
WHERE Cname = "C语言"
)

D_UPDATE1

可见,所有选了C语言课的学生的成绩被清空了

2.把所有学生的年龄增加1岁

UPDATE Student SET Sage = Sage+1;

D_UPDATE2

由图可见,所有同学的年龄都增加了1岁

删除

​ 注意,如果省略 WHERE表达式,将会删除表中所有的元组,结果为空表

语法

DELETE FROM <表名>
[WHERE <条件表达式>];

实践

1.删除所有学计算机的同学的信息

DELETE FROM Student
WHERE Sdept = "计算机"

DELETE3

可见,计算机专业的同学的信息已被删除

视图

创建视图

​ 视图能够反映数据库的变化,对视图操作时才会生成数据

语法

CREATE VIEW<视图名>[(<列名1>[,<列名2>]...)]
AS <SELECT 语句>
[WITH CHECK OPTION];

实践

1.创建年龄小于23岁的学生的视图,并要求数据更新时进行检查

​ (基于刚刚增加1岁后的Student表,计算机专业学生恢复)

CREATE VIEW Sage_23
AS SELECT * FROM Student WHERE Sage < 23
WITH CHECK OPTION;

VIEW1.1VIEW1.2

可见,视图创建成功,VIEW列表里面多了一个sage_23,内容如上

视图查询

1.查询计算机系年龄小于23岁的学生

实践

SELECT * FROM Sage_23
WHERE Sdept = "计算机"

VIEW Q1

可见,成功进行了视图上的查询

视图更新

1.通过视图Sage_23插入学生刘敏的信息("2041","刘敏",21,"女","数学")

实践

INSERT INTO Sage_23
VALUES("2041","刘敏",21,"女","数学");

VIEW I1 VIEW I2

如图所示,刘敏的信息成功插入了视图 ,也插入了Student

数据控制

授权

语法

GRANT {<权限1>,<权限2>,...}
ON[主人.]<表名或视图名>
TO{<用户名1>,<用户名2>,...| PUBLIC}
[WITH CHECK OPTION]

实践

1.授予用户User4对表SC中的列Grade的修改权

GRANT UPDATE(Grade)
ON TABLE SC
TO User4;

使用下面语句查看存在的用户:

SELECT User FROM mysql.user;

GRANT1可见,User4成功被创建

权限回收

语法

REVOKE {<权限1>,<权限2>,...}
ON[主人.]<表名或视图名>
FROM{<用户名1>,<用户名2>,...| PUBLIC}
[RESTRICT|CASCADE]

CASCADE 表示级联操作回收权限

RESTRICT 选项表示,用户没有将拥有的权限转授给他人是才能回收

实践

1.回收刚才授予User4的对SC表的修改权 (MySQL不支持以上两条指令)

REVOKE UPDATE
ON TABLE SC
FROM User4;

REVOKE

执行成功,但是结果不方便验证