Mysql一些具有代表性的题目

–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

	--建表
	--学生表
	CREATE TABLE `Student`(
	    `s_id` VARCHAR(20),
	    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
	    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	    PRIMARY KEY(`s_id`)
	);
	--课程表
	CREATE TABLE `Course`(
	    `c_id`  VARCHAR(20),
	    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
	    `t_id` VARCHAR(20) NOT NULL,
	    PRIMARY KEY(`c_id`)
	);
	--教师表
	CREATE TABLE `Teacher`(
	    `t_id` VARCHAR(20),
	    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
	    PRIMARY KEY(`t_id`)
	);
	--成绩表
	CREATE TABLE `Score`(
	    `s_id` VARCHAR(20),
	    `c_id`  VARCHAR(20),
	    `s_score` INT(3),
	    PRIMARY KEY(`s_id`,`c_id`)
	);
	--插入学生表测试数据
	insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
	insert into Student values('02' , '钱电' , '1990-12-21' , '男');
	insert into Student values('03' , '孙风' , '1990-05-20' , '男');
	insert into Student values('04' , '李云' , '1990-08-06' , '男');
	insert into Student values('05' , '周梅' , '1991-12-01' , '女');
	insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
	insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
	insert into Student values('08' , '王菊' , '1990-01-20' , '女');
	--课程表测试数据
	insert into Course values('01' , '语文' , '02');
	insert into Course values('02' , '数学' , '01');
	insert into Course values('03' , '英语' , '03');
	
	--教师表测试数据
	insert into Teacher values('01' , '张老师');
	insert into Teacher values('02' , '李老师');
	insert into Teacher values('03' , '王老师');
	
	--成绩表测试数据
	insert into Score values('01' , '01' , 80);
	insert into Score values('01' , '02' , 90);
	insert into Score values('01' , '03' , 99);
	insert into Score values('02' , '01' , 70);
	insert into Score values('02' , '02' , 60);
	insert into Score values('02' , '03' , 80);
	insert into Score values('03' , '01' , 80);
	insert into Score values('03' , '02' , 80);
	insert into Score values('03' , '03' , 80);
	insert into Score values('04' , '01' , 50);
	insert into Score values('04' , '02' , 30);
	insert into Score values('04' , '03' , 20);
	insert into Score values('05' , '01' , 76);
	insert into Score values('05' , '02' , 87);
	insert into Score values('06' , '01' , 31);
	insert into Score values('06' , '03' , 34);
	insert into Score values('07' , '02' , 89);
	insert into Score values('07' , '03' , 98);
	--------------------- 

注意：聚合函数需要GROUP BY才有多条结果
Having用于聚合条件
– 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id,a.s_name,AVG(b.s_score)
FROM student a JOIN score b
ON a.s_id=b.s_id
GROUP BY a.s_id,a.s_name
HAVING AVG(b.s_score)<60

– 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*,b.s_score as ‘01语文’,c.s_score AS ‘02数学’
FROM student a JOIN score b
ON a.s_id=b.s_id AND b.c_id=‘01’
JOIN score c
ON a.s_id=c.s_id AND c.c_id=‘02’
WHERE b.s_score>c.s_score

– 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id,a.s_name,COUNT(b.c_id),SUM(b.s_score)
FROM student a LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY a.s_id,a.s_name

– 查询"李"姓老师的数量
SELECT COUNT(teacher.t_id)
FROM teacher
WHERE teacher.t_name LIKE ‘李%’

– 查询学过"张三"老师授课的同学的信息
SELECT d.*
FROM teacher a JOIN course b
ON a.t_id= b.t_id
JOIN score c
ON c.c_id = b.c_id
JOIN student d
ON d.s_id = c.s_id
WHERE a.t_name=‘张老师’

– 查询没学过"张三"老师授课的同学的信息
SELECT d.*
FROM student d
WHERE d.s_id not IN (SELECT d.s_id
FROM teacher a JOIN course b
ON a.t_id= b.t_id
JOIN score c
ON c.c_id = b.c_id
JOIN student d
ON d.s_id = c.s_id
WHERE a.t_name=‘张老师’)

– 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT a.*
FROM student a
WHERE a.s_id IN(
select b.s_id
FROM score AS b
WHERE b.c_id = ‘01’)
AND a.s_id IN(
select b.s_id
FROM score AS b
WHERE b.c_id = ‘02’
)
– 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.*
FROM student a
WHERE a.s_id IN(
select b.s_id
FROM score AS b
WHERE b.c_id = ‘01’)
AND a.s_id NOT IN(
select b.s_id
FROM score AS b
WHERE b.c_id = ‘02’
)

– 查询没有学全所有课程的同学的信息
SELECT a.*
FROM student a
WHERE a.s_id NOT in(
SELECT a.s_id
FROM score a
JOIN score b
ON a.s_id = b.s_id AND b.c_id = ‘01’
JOIN score c
ON a.s_id = c.s_id AND c.c_id = ‘02’
WHERE a.c_id = ‘03’
)