Back

SQL basics practice

Tue, May 24 20165 min read
Nathaniel

实验环境

硬件环境

  • 处理器: 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 中不支持 `INTERSECT` 和 `MINUS`运算
故使用`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
执行成功,但是结果不方便验证

Comments(0)

Continue with
to comment