MySQL设计学生选课系统（关系型数据库概论）

-- 创建数据库Student_Course_System 默认字符集 utf-8
CREATE DATAbase Student_Course_System DEFAULT CHARSET utf8;
-- 切换数据库
USE Student_Course_System

-- 创建学院信息表
CREATE TABLE tb_college(
	col_id INT NOT NULL PRIMARY KEY COMMENT '编号',
	col_name VARCHAr(50) NOT NULL COMMENT '名称',
	website VARCHAr(1024) COMMENT '官网'
);

-- 创建学生信息表
-- alter table tb_student change column stu_sex stu_sex int;
CREATE TABLE tb_student(
	stu_id INT PRIMARY KEY NOT NULL COMMENT '学号',
	stu_name VARCHAr(20) NOT NULL COMMENT '姓名',
	stu_sex BIT DEFAULT 1 COMMENT '性别',
	stu_birth DATE COMMENT '生日',
	col_id INT NOT NULL COMMENT '所属学院',
	FOREIGN KEY (col_id) REFERENCES tb_college (col_id)
);

-- 创建教师信息表
CREATE TABLE tb_teacher(
	tea_id INT PRIMARY KEY NOT NULL COMMENT '工号',
	tea_name VARCHAr(50) NOT NULL COMMENT '姓名',
	tea_title VARCHAr(10) DEFAULT '助教' COMMENT '职称',
	col_id INT NOT NULL COMMENT '所属学院',
	FOREIGN KEY (col_id) REFERENCES tb_college (col_id)
);

-- 创建课程信息表
CREATE TABLE tb_course(
	cou_id INT PRIMARY KEY NOT NULL COMMENT '编号',
	cou_name VARCHAr(50) NOT NULL COMMENT '名称',
	cou_credit INT NOT NULL COMMENT '学分',
	tea_id INT NOT NULL COMMENT '授课老师',
	FOREIGN KEY (tea_id) REFERENCES tb_teacher (tea_id)
);

-- 创建选课信息表
-- DROp TABLE tb_score;
CREATE TABLE tb_score(
	sc_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '选课记录编号',
	stu_id INT NOT NULL COMMENT '选课学生',
	cou_id INT NOT NULL COMMENT '所选课程',
	sc_date DATETIME COMMENT '选课时间',
	sc_mark DECIMAL(4, 1) COMMENT '考试成绩',
	FOREIGN KEY (stu_id) REFERENCES tb_student (stu_id),
	FOREIGN KEY (cou_id) REFERENCES tb_course (cou_id)
);

-- 添加唯一性约束（一个学生选某个课程只能选一次）
ALTER TABLE tb_score ADD ConSTRAINT uni_score_stuid_couid UNIQUE(stu_id, cou_id);

-- 向学院信息表中添加信息
INSERT INTO tb_college VALUES
	('1001', '计算机科学与工程学院', 'https://www.abc.com'),
	('1002', '外国语言文学学院', 'https://www.def.com'),
	('1003', '国际经济与贸易学院', 'https://www.ghj.com');

-- 向学生信息表中添加信息
INSERT INTO tb_student VALUES
	(201001001, '张山', 1, '1998-02-20', 1001),
	(201001002, '张柳柳', 0, '1998-09-21', 1001),
	(201001003, '李立群', 1, '1999-11-12', 1001),
	(201001004, '王建军', 1, '1997-09-13', 1001),
	(201001005, '刘丽芳', 0, '1998-07-04', 1001),
	(201002001, '杨飞飞', 0, '1998-09-20', 1002),
	(201002002, '黄立功', 1, '1997-10-12', 1002),
	(201002003, '陈一新', 1, '1999-05-19', 1002),
	(201003001, '王建军', 1, '1999-09-13', 1003),
	(201003002, '刘丽芳', 0, '1998-06-09', 1003);

-- 向教师信息表中添加信息
INSERT INTO tb_teacher VALUES
	(100110, '王建国', '教授', 1001),
	(100121, '李剑锋', '副教授', 1001),
	(100219, '陈恺恺', '讲师', 1002),
	(100204, '黄奕芳', '教授', 1002),
	(100321, '郭建', '讲师', 1003);

-- 向课程信息表添加信息
-- update tb_course set cou_id=1002803 where cou_name='英语语法概论';
INSERT INTO tb_course VALUES
	(1001901, '数据库系统概论', 2, 100110),
	(1001902, '计算机网络', 3, 100121),
	(1001903, 'C程序设计', 2, 100110),
	(1002801, '标准日语', 3, 100219),
	(1002802, '日本文化发展史', 1, 100219),
	(1002803, '英语语法概论', 1, 100204),
	(1003701, '会计学概论', 1, 100321),
	(1003702, '经济学基础概论', 2, 100321);

-- 向选课信息表添加信息
INSERT INTO tb_score VALUES
	(1, 201001001, 1001901, NOW(), 95),
	(2, 201001001, 1001902, NOW(), 89),
	(3, 201001002, 1001901, NOW(), 91),
	(4, 201001003, 1001902, NOW(), 78),
	(5, 201001003, 1001903, NOW(), 87),
	(6, 201001004, 1001901, NOW(), 88),
	(7, 201002001, 1002801, NOW(), 95),
	(8, 201002002, 1002802, NOW(), 65),
	(9, 201002003, 1002803, NOW(), 82),
	(10, 201003001, 1003701, NOW(), 97),
	(11, 201003001, 1003702, NOW(), 87),
	(12, 201003002, 1003701, NOW(), 88),
	(13, 201003002, 1003702, NOW(), 83);

-- 查询学生信息表的所有信息
SELECT * FROM tb_student;
SELECt * FROM tb_teacher;
-- 查询显示学生信息表的学号和姓名
SELECt stu_id, stu_name FROM tb_student;
-- 别名
SELECt stu_id AS '学号', stu_name AS '姓名' FROM tb_student;
-- 将性别1显示为男，性别0显示为女
SELECt stu_name AS '姓名', CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS '性别' FROM tb_student;
-- 查询性别为女的学生的姓名和出生日期
SELECt stu_name, stu_birth FROM tb_student WHERe stu_sex=0;
-- 查询出生在1999年的学生的姓名和出生日期
SELECt stu_name, stu_birth FROM tb_student WHERe stu_birth BETWEEN '1999-01-01' AND '1999-12-31';

-- 模糊查询
-- 查询张姓学生的姓名和学号
SELECt stu_name, stu_id FROM tb_student WHERe stu_name LIKE '张%';
-- 查询张姓且名字为两字的学生的姓名和学号
SELECt stu_name, stu_id FROM tb_student WHERe stu_name LIKE '张_';
-- 查询姓名中包含 立 或者 飞 的学生的姓名和学号
SELECt stu_name, stu_id FROM tb_student WHERe stu_name LIKE '%立%' OR stu_name LIKE '%飞%';
-- 结果去重
SELECt DISTINCT sc_date FROM tb_score;
-- 按出生日期降序排序
SELECt stu_name, stu_birth FROM tb_student WHERe stu_sex=1 ORDER BY stu_birth DESC;  -- asc
-- 查询男学生的姓名和年龄并按照出生日期降序排序
SELECt stu_name AS '姓名', YEAR(NOW()) - YEAR(stu_birth) AS '年龄' FROM tb_student WHERe stu_sex=1 ORDER BY stu_birth DESC;

-- 聚合函数，maxmincountsumavg..... 
SELECt MIN(stu_birth) FROM tb_student;
SELECt COUNT(stu_id) FROM tb_student;
SELECt COUNT(stu_id), stu_sex FROM tb_student WHERe stu_sex=1;
SELECt COUNT(stu_id), stu_sex FROM tb_student GROUP BY stu_sex;
SELECt AVG(sc_mark), cou_id FROM tb_score WHERe cou_id=1001901;
SELECt AVG(sc_mark), stu_id FROM tb_score WHERe stu_id=201001001;
SELECt stu_id, AVG(sc_mark) FROM tb_score GROUP BY stu_id;
-- 分组之前的筛选可以使用where写在group by的前面，但是筛选在分组之后的需要使用having
SELECt stu_id, AVG(sc_mark) FROM tb_score GROUP BY stu_id HAVINg AVG(sc_mark)>=90;

-- 嵌套查询（子查询）
SELECt stu_name, stu_birth FROM tb_student WHERe stu_birth = (SELECt MIN(stu_birth) FROM tb_student);
-- 查询选了两门及以上课程的学生姓名
SELECt stu_name FROM tb_student WHERe stu_id IN (SELECt stu_id FROM tb_score GROUP BY stu_id HAVINg COUNT(*) >= 2);
-- 连接查询（查询学生姓名、课程名称以及考试成绩）
SELECt stu_name, cou_name, sc_mark FROM tb_student t1, tb_course t2, tb_score t3 WHERe t1.stu_id=t3.stu_id AND t2.cou_id=t3.cou_id; 
-- 查询选课学生的姓名和平均成绩
SELECt stu_name, avg_mark FROM tb_student t1, (SELECt stu_id , AVG(sc_mark) AS avg_mark FROM tb_score GROUP BY stu_id) t2 
WHERe t1.stu_id=t2.stu_id;

-- 索引
-- 没建立索引之前，用explain查看详细的查询过程，可以发现查询type是all，查询的行数是全部行
-- 负向条件，如<>，模糊查询，不可以使用索引
EXPLAIN SELECt stu_name FROM tb_student WHERe stu_name='陈一新';
-- 索引，可以加快查询速度，相当于加上目录，也就是以空间换时间，但是索引会拖慢增删的速度
-- 索引应该建在最常用的查询列上
-- 给学生表的学生姓名建立索引
CREATE INDEX idx_stu_name ON tb_student (stu_name);
DROp INDEX idx_stu_name ON tb_student;
-- 建立索引之后，用explain查看详细的查询过程，可以发现查询type是ref，查询的行数是匹配行
EXPLAIN SELECT stu_name FROM tb_student WHERe stu_name='陈一新';

-- 视图，相当于查询的快照，对于下面例子，相当于把查询记录下来，下次可以直接通过视图查看
-- 视图还可以限制用户权限，仅仅给与用户给定的信息查询权限，表的其他信息无权限查看
CREATE VIEW vw_tb_student AS
SELECt stu_id, stu_name, stu_birth FROM tb_student WHERe stu_sex=1;
-- 通过视图查看查询的信息
SELECt * FROM vw_tb_student;
-- 删除视图
DROp VIEW vw_tb_student;

-- DCL: 创建新用户，用户名aaaa，连接密码123456
CREATE USER 'aaaa'@'%' IDENTIFIED BY '23456';
-- 授予用户针对数据库Student_Course_System的所有权限
GRANT ALL PRIVILEGES ON Student_Course_System.* TO 'aaaa'@'%';
-- 召回权限,召回用户的插入和删除权限
REVOKE INSERT, DELETE ON Student_Course_System.* FROM 'aaaa'@'%';