SQL复习题

– 1.创建表格 student 和 score
use 库名；
drop student if exsits student;

CREATE TABLE student (
id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
name VARCHAR(20) NOT NULL COMMENT ‘姓名’,
sex VARCHAR(4) COMMENT ‘性别’,
birth YEAR COMMENT ‘出生年月’,
department VARCHAR(20) NOT NULL COMMENT ‘院系’,
address VARCHAR(50) COMMENT ‘家庭地址’
);
主键 有 非空 和 唯一的特性，不需要特别写
表名以及字段 可以不用``符号

CREATE TABLE score (
id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT ‘编号’,
stu_id INT(11) NOT NULL COMMENT ‘学号’,
c_name VARCHAR(20) COMMENT ‘课程名称’,
grade INT(11) COMMENT ‘分数’
);

INSERT INTO student VALUES
(901, ‘张大’, ‘男’, 1985, ‘计算机系’, ‘北京市海淀区’),
(902, ‘张二’, ‘男’, 1986, ‘中文系’, ‘北京市昌平区’),
(903, ‘张三’, ‘女’, 1990, ‘中文系’, ‘湖南省永州市’),
(904, ‘李四’, ‘男’, 1990, ‘英语系’, ‘辽宁省阜新市’),
(905, ‘王五’, ‘女’, 1991, ‘英语系’, ‘福建省厦门市’),
(906, ‘王六’, ‘男’, 1988, ‘计算机系’, ‘湖南省衡阳市’)

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, ‘计算机’, 90),
(NULL, 906, ‘英语’, 85)

– 2. 查询学生表中所有记录
SELECT * FROM student;

– 3. 查询学生表中第2条到第4条
SELECT * FROM student LIMIT 1, 3;
SELECT * FROM student LIMIT 3 OFFSET 1;
limit n 等于 limit(0,n)，从0+1开始的n条

– 4. 学号，姓名，院系
SELECT id AS ‘学号’, name AS ‘姓名’, department AS ‘院系’ FROM student;

– 5. 计算机系 和 英文系
SELECT * FROM student WHERE department IN (‘计算机系’, ‘英语系’);
SELECT * FROM student WHERE department = ‘计算机系’ OR department = ‘英语系’;

– 6. 年龄 28 ~ 32 学生信息
SELECT * FROM student s WHERE (YEAR(NOW()) - s.birth) BETWEEN 28 AND 32;

– 7. 院系多少人
SELECT department AS ‘院系’, count(*) AS ‘人数’ FROM student GROUP BY department;
group by 按啥分组 就要查啥

– 8. 科目最高成绩
SELECT c_name AS ‘科目’, MAX(grade) AS ‘最高分’ FROM score GROUP BY c_name;

– 9. 查 李四 课目和成绩
SELECT c.c_name AS ‘科目’, c.grade AS ‘成绩’
FROM score c
LEFT JOIN student s
ON c.stu_id = s.id
WHERE s.name = ‘李四’;

– 10. 连接查询 所有学生信息 AND 考试信息
SELECT *
FROM student s
LEFT JOIN score c
ON s.id = c.stu_id

– 11. 计算每一个学生的总成绩
SELECT s.name as ‘姓名’, sum(c.grade) as ‘总分’
FROM score c
LEFT JOIN student s
ON c.stu_id = s.id
GROUP BY s.name

– 12. 每个科目的平局成绩
SELECT c_name as ‘科目名称’, AVG(grade) FROM score GROUP BY c_name;

– 13. 计算机 < 95
SELECT s.*
FROM student s
LEFT JOIN score c
ON s.id = c.stu_id
WHERE c.c_name = ‘计算机’ AND c.grade < 85;

SELECT * FROM student where id IN (
SELECT stu_id FROM score where c_name = ‘计算机’ AND grade < 85
)

– 14. 同时参加计算机和英语考试的学生信息
SELECT * FROM student WHERE id IN (
SELECT stu_id FROM score
WHERE c_name = ‘计算机’ OR c_name = ‘英语’
GROUP BY stu_id
HAVING COUNT(*) = 2
)

SELECT * FROM student WHERE id IN (
SELECT stu_id from score WHERE stu_id IN (
SELECT stu_id from score WHERE c_name = ‘计算机’
) and c_name = ‘英语’
)

– 15. 计算机考试成绩按从高到低排序
SELECT grade FROM score ORDER BY grade DESC

– 16. student score 查询出学号 合并
SELECT id FROM student
UNION
SELECT DISTINCT stu_id FROM score
distinct 去重

– 17. 查询姓张 或者 姓王的同学姓名、院系、考试科目及成绩
SELECT s.name as ‘姓名’, s.department as ‘院系’, c.c_name ‘科目’, c.grade ‘成绩’
FROM student s
LEFT JOIN score c
ON s.id = c.stu_id
WHERE s.name LIKE ‘张%’ OR s.name LIKE ‘王%’

SELECT TRIM(’ hello’);

– 18. 查询是湖南的学生姓名、年龄、院系、考试科目及成绩
SELECT s.name as ‘姓名’, YEAR(NOW()) - s.birth ‘年龄’, s.department as ‘院系’, c.c_name ‘科目’, c.grade ‘成绩’
FROM student s
LEFT JOIN score c
ON s.id = c.stu_id
WHERE s.address LIKE ‘%湖南%’