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

实验内容

基本表

基本表的定义

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

copysql
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)
);

基本表的修改

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

1. 添加一个新列Ssex2，大小同Ssex,有NOT NULL约束
2. 扩大Sname 的存储空间到 CHAR(20)
3. 丢掉Ssex
   MySQL 语法与标准SQL 略有不同
   执行以下语句后：

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

基本表的删除

copysql
DROP TABLE <表名>

copysql
DROP TABLE Student;

索引

建立索引

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

copysql
CREATE UNIQUE INDEX INDEX_SNAME
	ON Student(SName);

copysql
SHOW INDEX FROM Student;

删除索引

copysql
DROP INDEX<索引名>

copysql
ALTER TABLE Student
	DROP INDEX INDEX_SNAME;

数据查询

单表查询

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

copysql
SELECT Sno,Sname
	FROM Student

得出结果如下

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

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

copysql
SELECT * FROM Student
	WHERE Sname LIKE "李_"

连接查询

copysql
<基表名> AS <别名>

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

可见，70-80分的同学被选了出来

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

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

嵌套查询

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

由结果可见其成功

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

可见，选出了所有没有选C语言（课号为0701）的同学

copysql
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
    )

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

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

故可知选出了张三（学号1800），他是选了C语言（课号0701）的成绩最低的同学

集合查询

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

可见选择成功

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

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

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

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

数据更新

插入

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

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

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

再次查询SC内容结果如左

修改

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

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

copysql
UPDATE Student SET Sage = Sage+1;

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

copysql
DELETE FROM Student
	WHERE Sdept = "计算机"

视图

创建视图

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

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

视图查询

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

视图更新

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

数据控制

授权

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

copysql
GRANT UPDATE(Grade)
	ON TABLE SC
	TO User4;

copysql
SELECT User FROM mysql.user;

可见，User4成功被创建

权限回收

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

copysql
REVOKE UPDATE
	ON TABLE SC
	FROM User4；