实验环境
硬件环境
- 处理器: 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 StudentADD 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_SNAMEON Student(SName);
使用如下语句查看创建的
INDEX
(MySQL特有方法)SHOW INDEX FROM Student;

由图可知,
Student
表上现在存在 主键索引
和 我们刚刚创建成功的 Sname
上的索引删除索引
语法
DROP INDEX<索引名>
实践
ALTER TABLE StudentDROP INDEX INDEX_SNAME;

由图知,刚刚建立的
SName
上的INDEX_SNAME
被删除数据查询
单表查询
语法
SELECT [ALL|DISTINCT] <目标表列>FROM <基本表或视图名>[,<基本表或视图名>][WHERE <条件表达式>][GROUP BY<列名1> [HAVING <条件表达式1>]][ORDER BY<列名>[ASC|DESC]]
实践

基于之前的
Student
表,自行添加了部分数据现在打算进行以下几步操作:
1.选出所有的计算机专业的同学 的学号和名字
SELECT Sno,SnameFROM Student
得出结果如下 

可见成功地选出了专业为
计算机
的4位同学2.选出在18-20岁之间的女同学
SELECT * FROM StudentWHERE (Ssex = "女") AND (Sage BETWEEN 18 AND 20)

可见成功选出了18-20岁的女同学
3.计算出 按专业分组的 同学们的平均年龄
SELECT Sdept,avg(Sage) FROM StudentGROUP BY Sdept

可见成功计算出了各个专业的同学的平均年龄
4.找出姓李的 名字为2个字 的同学
SELECT * FROM StudentWHERE Sname LIKE "李_"

可见,
李梅梅
并未在其中,说明选择成功连接查询
涉及2个及2个以上表的查询的时候,需要连接后再查询,一般连接条件是两表中的同名属性


Course表,SC表内容如上所示
语法
有时候要一个表自身进行连接查询时,要用AS 来定义别名
<基表名> AS <别名>
实践
打算进行的操作:
1.查询成绩在70到80分的学生的学号和姓名
SELECT SC.Sno,Student.SnameFROM Student,SCWHERE (Grade BETWEEN 70 AND 80) ANDSC.Sno = Student.Sno;

(为防止二义性,
SELECT
处的列名需指定表名)2.查询成绩在90分及以上的学生的学号、姓名和对应的课程名
SELECT SC.Sno,Student.Sname,CnameFROM Student,SC,CourseWHERE Grade >= 90 ANDSC.Sno = Student.Sno ANDSC.Cno = Course.Cno

可见,成功地选出了成绩在90分及以上的同学的信息
3.查询和
赵刚
在同一个系的学生SELECT S2.*FROM Student AS S1,Student AS S2WHERE 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 StudentWHERE Sdept IN(SELECT Sdept FROM StudentWHERE Sname = "刘美")

2.查询没有选课号为
0701
的同学SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT * FROM SCWHERE Cno = "0701" ANDSC.Sname = Student.Sname)

0701
)的同学3.查询选了课号为
0702
的课且成绩比赵志
高的同学SELECT Student.Sname,SC.Grade FROM Student,SCWHERE Cno = "0702" ANDStudent.Sno = SC.Sno ANDGrade > (SELECT Grade FROM SC,StudentWHERE Student.Sname = "赵志" ANDCno = "0702" AND Student.Sno = SC.SNo)
假如SC表中没有Sname域的情况下应该如上说写,如果有则可以简化如下
SELECT Sname,Grade FROM SCWHERE Cno = "0702" ANDGrade > (SELECT Grade FROM SCWHERE Sname = "赵志")

可见的确找出了选了
计算机网络
(课号)0702
成绩比赵志
高的同学4.查询选了
0701
号课的 成绩最低 的同学的学号SELECT Sno FROM SCWHERE 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 SCWHERE Cno = "0506"UNIONSELECT Sno FROM StudentWHERE Sdept = "计算机"

既有
计算机
专业的同学,也有选了机械CAD
(课号0506
)的同学2.查询计算机专业的同学里面没有选
0701
号课的同学由于MySQL 中不支持
INTERSECT
和 MINUS
运算故使用
INNER JOIN
和 <>
来替换SELECT Student.SName FROM Student,SCWHERE Student.Sno = SC.Sno ANDSdept = "计算机" AND Cno <> "0701"

MINUS
相同的功能3.查询计算机专业的同学里面选了
0701
号课的同学SELECT SC.Sno,Student.SnameFROM 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;

说明成功把Student表里面的
Sno
,Sname
重用了修改
注意在使用
UPDATE
语句时,一次可以修改一个或多个列的值若省略
WHERE
子句,则默认修改表中所有元组的值,因此使用时一定要小心语法
UPDATE <>SET 列名1=<表达式1>[,列名2=<表达式2>]...[WHERE <条件表达式>];
实践
1.把选了
C语言
课的学生的成绩清空UPDATE SCSET Grade = NULLWHERE Cno = (SELECT Cno FROM CourseWHERE Cname = "C语言")

可见,所有选了
C语言
课的学生的成绩被清空了2.把所有学生的年龄增加1岁
UPDATE Student SET Sage = Sage+1;

由图可见,所有同学的年龄都增加了1岁
删除
注意,如果省略
WHERE
表达式,将会删除表中所有的元组,结果为空表语法
DELETE FROM <表名>[WHERE <条件表达式>];
实践
1.删除所有学计算机的同学的信息
DELETE FROM StudentWHERE Sdept = "计算机"

可见,
计算机
专业的同学的信息已被删除视图
创建视图
视图能够反映数据库的变化,对视图操作时才会生成数据
语法
CREATE VIEW<视图名>[(<列名1>[,<列名2>]...)]AS <SELECT 语句>[WITH CHECK OPTION];
实践
1.创建年龄小于23岁的学生的视图,并要求数据更新时进行检查
(基于刚刚增加1岁后的
Student
表,计算机
专业学生恢复)CREATE VIEW Sage_23AS SELECT * FROM Student WHERE Sage < 23WITH CHECK OPTION;


可见,视图创建成功,
VIEW
列表里面多了一个sage_23
,内容如上视图查询
1.查询计算机系年龄小于23岁的学生
实践
SELECT * FROM Sage_23WHERE Sdept = "计算机"

可见,成功进行了视图上的查询
视图更新
1.通过视图
Sage_23
插入学生刘敏的信息("2041","刘敏",21,"女","数学")
实践
INSERT INTO Sage_23VALUES("2041","刘敏",21,"女","数学");


如图所示,刘敏的信息成功插入了视图 ,也插入了
Student
表数据控制
授权
语法
GRANT {<权限1>,<权限2>,...}ON[主人.]<表名或视图名>TO{<用户名1>,<用户名2>,...| PUBLIC}[WITH CHECK OPTION]
实践
1.授予用户
User4
对表SC
中的列Grade
的修改权GRANT UPDATE(Grade)ON TABLE SCTO User4;
使用下面语句查看存在的用户:
SELECT User FROM mysql.user;

User4
成功被创建权限回收
语法
REVOKE {<权限1>,<权限2>,...}ON[主人.]<表名或视图名>FROM{<用户名1>,<用户名2>,...| PUBLIC}[RESTRICT|CASCADE]
CASCADE
表示级联操作回收权限RESTRICT
选项表示,用户没有将拥有的权限转授给他人是才能回收实践
1.回收刚才授予User4的对SC表的修改权 (MySQL不支持以上两条指令)
REVOKE UPDATEON TABLE SCFROM User4;

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