建表，插入数据，简单查询，数据操纵，视图实验答案在
mysql数据库习题2 - 掘金 (juejin.cn)里面。
索引：
创建索引：INDEX 索引名(字段名)
给已经存在的表添加索引：CREATE INDEX 索引名 ON 表名(字段名);
修改表时候创建索引：ALTER TABLE 表名 ADD INDEX 索引名(字段名)；
查看：SHOW CREATE TABLE 表名;
删除：DROP INDEX 索引名 ON 表名;
-- 1. 在学生作业管理数据库中查询某位老师所讲授的课程信息，因此可以在课程表上创建一个关于授课老师cteacher的一个非聚集索引 id_teacher来优化查询速度
-- 聚集 nun变成CLUSTERED
-- nonclustered
create INDEX cteacher ON course(cNo);
-- 2. 查询学生作业管理数据库中课程表的索引信息
SHOW index from course;
-- 3. 删除学生作业管理数据库中课程表的cteacher上的索引
drop index cteacher on course ;
存储过程（1，2），函数（3，4），游标（5）
创建 CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 数据类型) BEGIN …… END
重点：第5个游标，因为多条数据，所以需要用循环
DECLARE DONE BOOLEAN DEFAULT 0; # 设置循环标志，默认值为 0
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1; # 该语句表示游标遍历完后，设置循环标志为 1
IF DONE <> 1 THEN # 循环条件
-- 1..创建一带参数的存储过程p1，根据学生姓名查询该学号、姓名及所选修的课程号、课程名、成绩。并用“张艳”去调用该存储过程。
DELIMITER // # 声明结束符
DROP PROCEDURE p1 //
CREATE PROCEDURE p1(in name char(7))
BEGIN
select s.sNo 学号,s.sName 姓名,c.cNo,c.cName 课程名,swork1 作业1成绩,swork2 作业2成绩,swork3 作业3成绩
from workk,student s,course c
where c.cNo=workk.cNo and s.sNo=workk.sNo and s.sName=name;
END // # 存储过程定义结束
CALL p1('张艳') // # 注意此时“//”才是结束符
-- 2.删除存储过程p1
DROP PROCEDURE p1 ;
-- 3. 在学生作业管理数据库中，创建一个自定义函数F_Number，用来返回某门课程（传入参数为“课程名称”）的选课人数。并用“计算机应用基础”去调用该函数。
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER // # 恢复结束符
CREATE FUNCTION F_Number(cses varchar(20))
RETURNS INT
BEGIN
DECLARE x INT DEFAULT 0;
SET x =(select COUNT(*) from course,student,workk where course.cNo=workk.cNo and student.sNo=workk.sNo and course.cName=cses );
RETURN x;
END;
//
DELIMITER ;
SELECT F_Number('计算机应用基础');
-- 4. 删除自定义函数F_Number
DROP FUNCTION F_Number;
-- 5.定义一个游标，将学生表中所有学生的姓名、年龄显示出来
DELIMITER $ # 使用 $ 作为结束符
# 存储过程建立
create PROCEDURE youbiaoguocheng()
BEGIN
# 定义变量姓名、年龄
DECLARE nname VARCHAR(20);
DECLARE age int;
DECLARE DONE BOOLEAN DEFAULT 0; # 设置循环标志，默认值为 0
# 定义游标
DECLARE getmsg CURSOR FOR
select sName 姓名,year(NOW())-year(birthday) 年龄 from student;
-- 结果有七条，所以循环
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1; # 该语句表示游标遍历完后，设置循环标志为 1
# 打开游标
open getmsg;
REPEAT # 循环开始
# 从游标中取值
FETCH getmsg INTO nname,age;
IF DONE <> 1 THEN # 循环条件
# 显示结果
SELECT nname,age;
END IF; # IF语句结束
UNTIL DONE END REPEAT; # 结束循环
CLOSE getmsg;
END $
DELIMITER ; # 恢复 MySQL 的默认结束符
CALL youbiaoguocheng();
触发器
创建：CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 需要建立触发器的表名 FOR EACH ROW 一条 SQL 语句或存储过程;
删除;DROP TRIGGER 触发器名;
-- 1.使用SQL命令在学生表中创建一个删除触发器，当删除表student中的一个学生的信息时，学生作业表中相应的成绩数据也会删除。
-- 代码+测试截图
DELIMITER $
CREATE TRIGGER d1 after delete
on student
for EACH ROW
BEGIN
delete from student where workk.sNo=student.sNo;
end $
DELIMITER ;
SHOW TRIGGERS;
-- 2.使用SQL命令在学生表中创建一个修改触发器，当student表中的sno信息修改时，自动将学生作业表中表中的相应信息也修改。
-- 代码+测试截图
CREATE TRIGGER demo2 after update
on student
for EACH ROW
update student set workk.sNo=student.sNo;
SHOW TRIGGERS;
-- 3.为Student表的Sno字段添加一个check约束，使学号满足如下条件：
-- 1）学号首位为0.。
-- 2）学号后3位为数字字符。
-- 代码+测试截图
DESC student;
SELECT * FROM information_schema.TABLE_CONSTRAINTS;
ALTER TABLE student
ADD CONSTRAINT CK_sNo CHECK (
sNo like '[0][0-9][0-9][0-9]'
);
-- 4. 使用SQL命令在在前几次实验所使用的数据库中新建一个教师信息表，表名为Teacher，字段包括Tno(教师编号)，Tname(姓名)，Tsex(性别)，Ttitle(职称)，完整性包括：设定Tno为主键，Tname非空，Tsex的取值范围为“男”或“女”。
-- 实现代码
CREATE TABLE Teacher
(
Tno CHAR(9),
Tname CHAR(20) not null,
Tsex CHAR(2),
check(Tsex in('男','女')),
Ttitle CHAR(20),
PRIMARY KEY (Tno)
);
-- 5.对Teacher表增加一个约束，Ttitle属性的取值范围为（'助教','讲师','副教授','教授'），并插入一条记录：
-- 实现代码
ALTER TABLE Teacher
ADD CONSTRAINT C4 CHECK (Ttitle in ('助教','讲师','副教授','教授'));
DESC Teacher;
insert into Teacher values('2016003','刘阳','男','研究员');
数据库安全
-- 以下操作均在学生作业管理系统数据库中进行。（全部写出SQL语句）
-- 1. 创建数据库用户user_1和user_2，密码都为1234（假设服务器名为localhost）。
-- CREATE USER '账号' [IDENTIFIED BY '密码'];
USE MYSQL;
CREATE USER user_1@LOCALHOST IDENTIFIED BY '1234';
CREATE USER user_2@LOCALHOST IDENTIFIED BY '1234';
-- 这个是查看用户
SELECT user, host FROM user;
-- 2. 将用户user_2的名称修改为user_3。
rename user user_2@localhost to user_3@localhost;
-- 3. 将用户user_3的密码修改为123456。
ALTER USER 'user_3'@'localhost' IDENTIFIED BY '123456';
-- 其中 FLUSH PRIVILEGES 表示刷新用户权限。
FLUSH PRIVILEGES;
-- 4. 删除用户user_3。
drop user user_3@localhost;
-- 5. 以user_1用户身份登录MySQL。
-- 这个语句在window或者linux里面使用，不是mysql里面
mysql -uuser_1 -p1234
-- 6. 授予用户user_1对学生作业管理系统数据库中student表的所有操作权限及查询操作权限。
GRANT all on table scdb.student to user_1@localhost;
-- 7. 授予用户user_1对student表进行插入、修改、删除操作权限。
grant insert,update,DELETE on table scdb.student to user_1@localhost;
-- 8. 授予用户user_1对数据库学生作业管理系统的所有权限。
grant all on table scdb.* to user_1@localhost;
-- 9. 授予user_1在course表上的select权限，并允许其将该权限授予其他用户。
grant select on table scdb.course to user_1@localhost;
-- 10. 回收user_1的student表上的select权限。
REVOKE select on scdb.student from user_1@localhost;
-- 查看权限
-- SHOW GRANTS FOR 用户名@主机名;
SHOW GRANTS FOR user_1@localhost;
-- 回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
