SQL basics practice

实验环境

硬件环境

处理器: 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) );

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

基本表的修改

语法

已经创建好的表，可以修改其结构：

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

实践

基于刚才的结果，打算进行以下操作：

添加一个新列Ssex2，大小同Ssex,有NOT NULL约束
扩大Sname 的存储空间到 CHAR(20)
丢掉Ssex

MySQL 语法与标准SQL 略有不同

执行以下语句后：

ALTER TABLE Student 	ADD Ssex2 CHAR(2) NOT NULL,

 MODIFY COLUMN Sname CHAR(20),

 DROP COLUMN Ssex

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

基本表的删除

语法

DROP TABLE <表名>

实践

DROP TABLE Student;

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

索引

建立索引

语法

建立索引的语句格式是：

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

实践

基于之前Student 表：

CREATE UNIQUE INDEX INDEX_SNAME 	ON Student(SName);

使用如下语句查看创建的

INDEX（MySQL特有方法）

SHOW INDEX FROM Student;

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

删除索引

语法

DROP INDEX<索引名>

实践

ALTER TABLE Student 	DROP INDEX INDEX_SNAME;

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

数据查询

单表查询

语法

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

实践

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

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

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

SELECT Sno,Sname 	FROM Student

得出结果如下

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

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

SELECT

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

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

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

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

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

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

SELECT

* FROM Student 	WHERE Sname LIKE "李_"

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

连接查询

涉及2个及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;

可见，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

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

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

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

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

嵌套查询

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

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

| 谓词

| 用法

| | ------- | :----------------------------------------------------------- | | IN

| 存在量词，表示任意一个，在后续集中有一个成立则成立，可用于逻辑关系运算 | | ALL

| 全称量词，表示所有的，在后续集中必须全部成立才成立

打算实现的操作：

实践

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

SELECT

* FROM Student 	WHERE Sdept IN(

 SELECT Sdept FROM Student 		WHERE Sname = "刘美"

 )

由结果可见其成功

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

SELECT Sname FROM Student 	WHERE NOT EXISTS( 		SELECT

* FROM SC

 WHERE Cno = "0701" AND

 SC.Sname = Student.Sname

 )

可见，选出了所有没有选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 = "赵志"

 )

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

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

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

 )

故可知选出了张三（学号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 = "计算机"

可见选择成功

既有计算机专业的同学，也有选了机械CAD（课号0506）的同学

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

由于MySQL 中不支持 `INTERSECT` 和 `MINUS`运算

故使用`INNER JOIN` 和 `<>`来替换

SELECT Student.SName FROM Student,SC 	WHERE Student.Sno = SC.Sno AND

 Sdept = "计算机" AND Cno <> "0701"

故知，实现了与MINUS相同的功能

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

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

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

数据更新

插入

可以一次插入一个或多个元组，有2种格式，如下：

语法

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

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

实践

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

INSERT INTO SC (Sno,Sname)

 SELECT Sno,Sname FROM Student;

再次查询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语言"

 )

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

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

UPDATE Student SET Sage = Sage+1;

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

删除

注意，如果省略

WHERE表达式，将会删除表中所有的元组，结果为空表

语法

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

实践

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

DELETE FROM Student 	WHERE Sdept = "计算机"

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

视图

创建视图

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

语法

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;

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

视图查询

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

实践

SELECT

* FROM Sage_23 	WHERE Sdept = "计算机"

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

视图更新

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

实践

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

如图所示，刘敏的信息成功插入了视图 ,也插入了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;

可见，User4成功被创建

权限回收

语法

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

`CASCADE` 表示级联操作回收权限

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

实践

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

REVOKE UPDATE 	ON TABLE SC 	FROM User4；

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