SQL数据库设计与操作实战：学生、课程、教师信息管理

本示例将创建学生、课程、教师信息管理数据库，并演示各种SQL操作。

1. 创建student表：

CREATE TABLE student (
  sno CHAR(8) PRIMARY KEY,
  sname VARCHAR(20) NOT NULL,
  ssex VARCHAR(2) NOT NULL,
  sbirthday DATE NOT NULL,
  sdept VARCHAR(20) NOT NULL
);

2. 创建course表：

CREATE TABLE course (
  cno CHAR(4) PRIMARY KEY,
  cname VARCHAR(40) NOT NULL,
  ccredit FLOAT NOT NULL
);

3. 在teacher表中录入数据：

INSERT INTO teacher VALUES ('01','李然','男','讲师','2012.07.01');
INSERT INTO teacher VALUES ('02','张雨','男','副教授','2008.07.01');
INSERT INTO teacher VALUES ('03','夏天','女','讲师','2013.07.01');
INSERT INTO teacher VALUES ('04','高兴','女','讲师','2013.07.01');
INSERT INTO teacher VALUES ('05','苏迪','男','副教授','2004.07.01');
INSERT INTO teacher VALUES ('06','高亚宁','女','教授','2001.07.01');
INSERT INTO teacher VALUES ('07','高嘉','女','助教','2020.07.01');

4. 在course表中添加任课教师的编号tno字段：

ALTER TABLE course ADD COLUMN tno CHAR(2);

5. 删除sc表的记录：'20190005,05,56'：

DELETE FROM sc WHERE sno = '20190005' AND cno = '05';

6. 设置sc表的主键为sno和cno字段：

ALTER TABLE sc ADD PRIMARY KEY (sno, cno);

7. 设置tno为teacher表的主键：

ALTER TABLE teacher ADD PRIMARY KEY (tno);

8. 设置course表的外键tno关联于teacher表的主键tno：

ALTER TABLE course ADD CONSTRAINT fk_teacher_tno FOREIGN KEY (tno) REFERENCES teacher(tno);

9. 查询2020级的所有学生信息：

SELECT * FROM student WHERE sno LIKE '2020%';

10. 查询公安政治学院或智慧警务学院的所有女生信息：

SELECT * FROM student WHERE sdept IN ('公安政治学院', '智慧警务学院') AND ssex = '女';

11. 查询teacher表中有几类职称：

SELECT COUNT(DISTINCT title) FROM teacher;

12. 查询每门课程的选课人数，显示课程号和选课人数：

SELECT cno, COUNT(*) AS num FROM sc GROUP BY cno;

13. 查询所选课程总学分最高的学生信息，显示学号、姓名和学分：

SELECT s.sno, s.sname, SUM(c.ccredit) AS total_credit
FROM student s, course c, sc
WHERE s.sno = sc.sno AND c.cno = sc.cno
GROUP BY s.sno, s.sname
ORDER BY total_credit DESC
LIMIT 1;

14. 查询各门课程的平均分、最高分和最低分，显示课程号、课程名称、平均分、最高分和最低分：

SELECT c.cno, c.cname, AVG(sc.score) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score
FROM course c, sc
WHERE c.cno = sc.cno
GROUP BY c.cno, c.cname;

15. 查询智慧警务学院选修了课程的学生学号、姓名、课程名称和成绩：

SELECT s.sno, s.sname, c.cname, sc.score
FROM student s, course c, sc
WHERE s.sno = sc.sno AND c.cno = sc.cno AND s.sdept = '智慧警务学院';

16. 查询成绩不及格的学生的学号、姓名、课程号、课程名称和成绩：

SELECT s.sno, s.sname, c.cno, c.cname, sc.score
FROM student s, course c, sc
WHERE s.sno = sc.sno AND c.cno = sc.cno AND sc.score < 60;

17. 查询学生的所有信息及其选课信息：

SELECT s.*, c.cname, sc.score
FROM student s, course c, sc
WHERE s.sno = sc.sno AND c.cno = sc.cno;

18. 创建“ts”视图，显示教师及其所教课程的信息，包括教师编号，教师姓名，课程名称：

CREATE VIEW ts AS
SELECT t.tno, t.tname, c.cname
FROM teacher t, course c
WHERE t.tno = c.tno;

以上示例演示了如何使用SQL语句创建数据库表、插入数据、修改数据、删除数据以及进行各种查询操作。通过这些操作，我们可以实现对学生、课程、教师信息的管理，并进行各种统计分析。