MySQL 经典练习题（上）

已默认会安装和登录MySQL，此处不再赘述。

创建数据集

1	CREATE DATABASE `test`;
2	USE `test`;
3	DROP TABLE IF EXISTS course;
4	CREATE TABLE course(
5	cno INT(11) NOT NULL,
6	cname VARCHAR(20) NOT NULL,
7	tno INT(11) NOT NULL,
8	PRIMARY KEY (cno)
9	);
10	INSERT INTO course(cno,cname,tno)
11	VALUES
12	(1,'企业管理',3),
13	(2,'马克思',1),
14	(3,'UML',2),
15	(4,'数据库',5),
16	(5,'物理',8),
17	(6,'计算机',8);
18	DROP TABLE IF EXISTS sc;
19	CREATE TABLE sc(
20	sno INT(11) NOT NULL,
21	cno INT(11) NOT NULL,
22	score INT(11) NOT NULL
23	);
24	INSERT INTO sc(sno,cno,score)
25	VALUES (1,1,80),(1,2,86),(1,3,83),(1,4,89),(2,1,50),(2,2,36),(2,3,43),(2,4,59),(3,1,50),(3,2,96),(3,3,73),(3,4,69),(4,1,90),(4,2,36),(4,3,88),(4,4,99),(5,1,90),(5,2,96),(5,3,98),(5,4,99),(6,1,70),(6,2,66),(6,3,58),(6,4,79),(6,5,79),(6,6,79),(7,1,80),(7,2,76),(7,3,68),(7,4,59),(7,5,89),(1,5,80),(1,6,90);
26	DROP TABLE IF EXISTS student;
27	CREATE TABLE student(
28	sno INT(11) NOT NULL,
29	sname VARCHAR(20) NOT NULL,
30	sage DATETIME NOT NULL,
31	ssex CHAR(2) NOT NULL,
32	PRIMARY KEY (sno)
33	);
34	INSERT INTO student(sno,sname,sage,ssex)
35	VALUES
36	(1,'张三','1980-01-23 00:00:00','男'),
37	(2,'李四','1982-12-12 00:00:00','男'),
38	(3,'张飒','1981-09-09 00:00:00','男'),
39	(4,'莉莉','1983-03-23 00:00:00','女'),
40	(5,'王弼','1982-06-21 00:00:00','男'),
41	(6,'王丽','1984-10-10 00:00:00','女'),
42	(7,'刘香','1980-12-22 00:00:00','女');
43	DROP TABLE IF EXISTS teacher;
44	CREATE TABLE teacher(
45	tno INT(3) NOT NULL,
46	tname VARCHAR(20) NOT NULL,
47	PRIMARY KEY (tno)
48	);
49	INSERT INTO teacher(tno,tname)
50	VALUES(1,'张老师'),(2,'王老师'),(3,'李老师'),(4,'赵老师'),(5,'刘老师'),(6,'向老师'),(7,'李文静'),(8,'叶平');

题目练习

1.查询课程1的成绩比课程2的成绩高的所有学生的学号

SELECT
	a.*
FROM
	sc AS a
	LEFT JOIN
	sc AS b
	ON 
		a.sno = b.sno
WHERE
	a.cno = 1 AND
	b.cno = 2 AND
	a.score > b.score;

2.查询平均成绩大于60分的同学的学号和平均成绩

SELECT
	sno,
	AVG( score ) AS score 
FROM
	sc 
GROUP BY
	sno 
HAVING
	score > 60;

3.查询所有同学的学号、姓名、选课数、总成绩

SELECT
	sst.sno AS '学号',
	sst.sname AS '姓名',
	COUNT( ssc.cno ) AS '选课数',
	SUM( ssc.score ) AS '总成绩'
FROM
	sc AS ssc,
	student AS sst 
WHERE
	ssc.sno = sst.sno 
GROUP BY
	sst.sno;

4.查询姓“李”的老师的个数

SELECT
	COUNT(teacher.tname) AS "个数"
FROM
	teacher
WHERE 
	teacher.tname LIKE "李%";

5.查询没学过“叶平”老师课的同学的学号、姓名

SELECT
	sno,
	sname
FROM
	student
WHERE
	sno NOT IN
	(
	SELECT
		DISTINCT(a.sno)
	FROM
		sc AS a 
	WHERE
		a.cno > 4 /*杨平老师授课的为5和6*/
	);

6.查询同时学过课程1和课程2的同学的学号、姓名

SELECT
	a.sno AS 'xuehao',
	a.sname AS 'xingming'
FROM
	student AS a, sc
WHERE
	a.sno = sc.sno
	AND sc.cno IN (1,2)
GROUP BY
	sc.sno
HAVING COUNT(sc.cno) >=2;

7.查询学过“叶平”老师所教所有课程的所有同学的学号、姓名

SELECT
	a.sno AS 'xuehao',
	a.sname AS 'xingming'
FROM
	student AS a,
	sc AS b,
	course AS c,
	teacher AS d
WHERE
	a.sno = b.sno AND b.cno = c.cno AND c.tno = d.tno AND d.tname = '叶平'
GROUP BY a.sno
HAVING COUNT(b.cno) >=2;

8.查询所有课程成绩小于60分的同学的学号、姓名

SELECT
	student.sno AS 'xuehao',
	student.sname AS 'xingming'
FROM
	student
WHERE
	student.sno NOT IN
	(SELECT sc.sno FROM sc WHERE sc.score >=60);

9.查询没有学全所有课的同学的学号、姓名

SELECT
	student.sno AS 'xuehao',
	student.sname AS 'xingming'
FROM
	student
WHERE
	student.sno NOT IN(
	SELECT
		sc.sno
	FROM
		sc,course
	WHERE
		sc.cno = course.cno
	GROUP BY sc.sno
	HAVING COUNT(sc.cno) >=6
	);

10.查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

SELECT 
	DISTINCT
	sst.sno AS '学号',
	sst.sname AS '姓名'
FROM 
	student sst, sc
WHERE 
	sst.sno=sc.sno
	AND sst.sno!=1
	AND sc.cno IN 
	(SELECT cno FROM sc WHERE sno=1);

1	SELECT
2	a.*
3	FROM
4	sc AS a
5	LEFT JOIN
6	sc AS b
7	ON
8	a.sno = b.sno
9	WHERE
10	a.cno = 1 AND
11	b.cno = 2 AND
12	a.score > b.score;

1	SELECT
2	sno,
3	AVG( score ) AS score
4	FROM
5	sc
6	GROUP BY
7	sno
8	HAVING
9	score > 60;

1	SELECT
2	sst.sno AS '学号',
3	sst.sname AS '姓名',
4	COUNT( ssc.cno ) AS '选课数',
5	SUM( ssc.score ) AS '总成绩'
6	FROM
7	sc AS ssc,
8	student AS sst
9	WHERE
10	ssc.sno = sst.sno
11	GROUP BY
12	sst.sno;

1	SELECT
2	COUNT(teacher.tname) AS "个数"
3	FROM
4	teacher
5	WHERE
6	teacher.tname LIKE "李%";

1	SELECT
2	sno,
3	sname
4	FROM
5	student
6	WHERE
7	sno NOT IN
8	(
9	SELECT
10	DISTINCT(a.sno)
11	FROM
12	sc AS a
13	WHERE
14	a.cno > 4 /杨平老师授课的为5和6/
15	);

1	SELECT
2	a.sno AS 'xuehao',
3	a.sname AS 'xingming'
4	FROM
5	student AS a, sc
6	WHERE
7	a.sno = sc.sno
8	AND sc.cno IN (1,2)
9	GROUP BY
10	sc.sno
11	HAVING COUNT(sc.cno) >=2;

1	SELECT
2	student.sno AS 'xuehao',
3	student.sname AS 'xingming'
4	FROM
5	student
6	WHERE
7	student.sno NOT IN
8	(SELECT sc.sno FROM sc WHERE sc.score >=60);

1	SELECT
2	DISTINCT
3	sst.sno AS '学号',
4	sst.sname AS '姓名'
5	FROM
6	student sst, sc
7	WHERE
8	sst.sno=sc.sno
9	AND sst.sno!=1
10	AND sc.cno IN
11	(SELECT cno FROM sc WHERE sno=1);