数据库 第三次作业

CREATE DATABASE IF NOT EXISTS mydbll_stu;
USE mydbll_stu;
CREATE TABLE student (
                         id INT(10) NOT NULL UNIQUE PRIMARY KEY,
                         name VARCHAR(20) NOT NULL,
                         sex VARCHAR(4),
                         birth YEAR,
                         department VARCHAR(20),
                         address VARCHAR(50)
);

CREATE TABLE score (
                       id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
                       stu_id INT(10) NOT NULL,
                       c_name VARCHAR(20),
                       grade INT(10)
);


INSERT INTO student VALUES
                        (901, '张三丰', '男', 2002, '计算机系', '北京市海淀区'),
                        (902, '周全有', '男', 2000, '中文系', '北京市昌平区'),
                        (903, '张思维', '女', 2003, '中文系', '湖南省永州市'),
                        (904, '李广昌', '男', 1999, '英语系', '辽宁省新市'),
                        (905, '王静', '男', 2004, '英语系', '福建省厦门市'),
                        (906, '王心凌', '女', 1998, '计算机系', '湖南省衡阳市');

INSERT INTO score VALUES
                      (NULL, 901, '计算机', 98),
                      (NULL, 901, '英语', 80),
                      (NULL, 902, '计算机', 65),
                      (NULL, 902, '中文', 88),
                      (NULL, 903, '中文', 95),
                      (NULL, 904, '计算机', 70),
                      (NULL, 904, '英语', 92),
                      (NULL, 905, '英语', 94),
                      (NULL, 906, '计算机', 49),
                      (NULL, 906, '英语', 83);
　　
-- 1. 查询所有记录
SELECT * FROM student;
SELECT * FROM score;

-- 2. 查询student表的第2条到5条记录
SELECT * FROM student LIMIT 1,4;

-- 3. 查询计算机系和英语系的学生信息
SELECT * FROM student WHERE department IN ('计算机系', '英语系');

-- 4. 查询年龄小于22岁的学生信息（假设当前年份为2023年）
SELECT * FROM student WHERE (2023 - birth) < 22;

-- 5. 查询每个院系的人数
SELECT department, COUNT(*) AS 人数 FROM student GROUP BY department;

-- 6. 查询每个科目的最高分
SELECT c_name, MAX(grade) AS 最高分 FROM score GROUP BY c_name;

-- 7. 查询李广昌的考试科目和成绩
SELECT c_name, grade FROM score
WHERE stu_id = (SELECT id FROM student WHERE name = '李广昌');

-- 8. 连接查询所有学生信息和考试信息
SELECT s.*, sc.c_name, sc.grade
FROM student s
         INNER JOIN score sc ON s.id = sc.stu_id;

-- 9. 计算每个学生的总成绩
SELECT stu_id, SUM(grade) AS 总成绩 FROM score GROUP BY stu_id;

-- 10. 计算每个科目的平均成绩
SELECT c_name, AVG(grade) AS 平均分 FROM score GROUP BY c_name;

-- 11. 查询计算机成绩低于95的学生信息
SELECT s.*
FROM student s
         INNER JOIN score sc ON s.id = sc.stu_id
WHERE sc.c_name = '计算机' AND sc.grade < 95;

-- 12. 计算机成绩从高到低排序
SELECT * FROM score
WHERE c_name = '计算机'
ORDER BY grade DESC;

-- 13. 合并查询学号
SELECT id AS 学号 FROM student
UNION
SELECT stu_id AS 学号 FROM score;

-- 14. 查询姓张或姓王的学生信息及成绩
SELECT s.name, s.department, sc.c_name, sc.grade
FROM student s
         INNER JOIN score sc ON s.id = sc.stu_id
WHERE s.name LIKE '张%' OR s.name LIKE '王%';

-- 15. 查询湖南学生的信息及成绩
SELECT s.name, (2023 - s.birth) AS 年龄, s.department, sc.c_name, sc.grade
FROM student s
         INNER JOIN score sc ON s.id = sc.stu_id
WHERE s.address LIKE '湖南省%';