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	用于判断该值是否在给定的集合中，常用于固定集 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 = "刘美"
    )

由结果可见其成功

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；

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

Back