MySQL 50题

表的介绍

本文中共用到了四张表：学生表、课程表、教师表、成绩表

1.学生表

student(s_id,s_name,s_age,s_sex)
s_id : 学?编号、s_name: 学?姓名、s_age: 出?年?、s_sex: 学?性别

2.课程表

course(c_id,c_name,t_id)?
c_id: 课程编号、c_name: 课程名称、t_id: 教师编号

3.教师表

teacher(t_id,t_name)
t_id: 教师编号、t_name: 教师姓名

4.成绩表

sc(s_id,c_id,score)
s_id: 学生编号、c_id: 课程编号、score: 分数

创建数据库

本文中我创建了库名为MySQL50的数据库

CREATE DATABASE IF NOT EXISTS MySQL50;

使用该数据库

避免创建表时创建错位置，所以先使用该数据库

USE MySQL50;

创建表

1.学生表

CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT, 
s_name VARCHAR(10),
s_age DATETIME,
s_sex VARCHAR(10)
);

insert into student(s_name,s_age,s_sex) values 
( '赵雷' , '1990-01-01' , '男'),
( '钱电' , '1990-12-21' , '男'),
( '孙风' , '1990-04-10' , '男'),
( '李云' , '1990-04-06' , '男'),
( '周梅' , '1991-12-01' , '女'),
( '吴兰' , '1992-03-01' , '女'),
( '郑竹' , '1989-07-01' , '女'),
( '王菊' , '1990-01-20' , '女');

2.课程表

CREATE TABLE course(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(10),
t_id INT(10)
);

INSERT INTO course(c_name,t_id) VALUES  
('数学' , '01'),
('语文' , '02'),
('英语' , '03');

3.教师表

CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(10)
);

INSERT INTO teacher(t_name) VALUES
('张三'),
('李四'),
('王五');

4.成绩表

CREATE TABLE sc(
s_id INT(10),
c_id INT(10),
score INT(10)
);

INSERT INTO sc(s_id,c_id,score) VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

1.查询课程1比课程2成绩高的学生的信息以及课程1课程2成绩

SELECT a1.s_id,a1.s_name,a1.s_sex,b1.s1 AS "课程1成绩",b1.s2 AS "课程2成绩" FROM student a1, (
SELECT a.s_id AS s_id,a.score AS s1,b.score AS s2 FROM sc a,sc b
WHERE a.c_id=1 AND b.c_id=2 AND a.s_id=b.s_id
)b1  
WHERE s1>s2 AND a1.s_id=b1.s_id;								

2.找出平均分大于60分的学生的学号和平均分

select s_id,avg(score) AS "平均分" from sc group by s_id having avg(score)>60;

3.查询每个学生的课程总数及所有课程的总成绩

SELECT s_id,COUNT(*) AS "课程总数",SUM(score) AS "总分"FROM sc GROUP BY s_id;	

4.查询李姓老师的人数

SELECT COUNT(*)AS "人数" FROM teacher WHERE t_name LIKE "李%";		

5.查询学过张三老师的学生的信息

select * from student where s_id in(
select s_id from sc where c_id=(
select c_id from course where t_id=(
select t_id from teacher where t_name="张三")
) );

6.查询学过即学过课程1有学过课程2的学生信息（姓名，ID）

SELECT c.s_name AS "姓名",c.s_id AS "ID" FROM student AS c,
(SELECT a.s_id AS s_id FROM (SELECT * FROM sc WHERE c_id=1) a ,(SELECT * FROM sc WHERE c_id=2) b 
WHERE a.s_id=b.s_id)d 
WHERE c.s_id=d.s_id;

7.查询学过课程1但没有学过课程2的学生信息（姓名，ID）

SELECT c.s_name AS "姓名",c.s_id  AS "ID"FROM student AS c,
(SELECT  DISTINCT(a.s_id) AS s_id FROM (SELECT * FROM sc WHERE c_id=1)a,(SELECT * FROM sc WHERE s_id NOT IN (SELECT s_id FROM sc WHERE c_id=2))b WHERE a.s_id =b.s_id)d
WHERE c.`s_id`=d.s_id;

8.查询没有学全所有课程的学生的信息（姓名，ID）

SELECT a.s_name AS "姓名",b.s_id AS "ID" FROM student AS a,sc AS b 
WHERE a.s_id=b.s_id GROUP BY a.s_id HAVING COUNT(*)<(SELECT COUNT(DISTINCT c_name)AS a FROM course);

9.查询至少有一门课程与学号为1的学生相同的学生信息（姓名，ID）

SELECT  a.s_name AS "姓名",a.s_id AS "ID" FROM student AS a,
(SELECT DISTINCT(s_id) FROM sc WHERE c_id IN(SELECT c_id FROM sc WHERE s_id=1))b WHERE a.s_id=b.s_id;

10.查询与1号同学所修课程数相同的同学学号，课程数

SELECT s_id,COUNT(*) FROM sc WHERE c_id  IN(SELECT c_id FROM sc WHERE s_id=1)  GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE s_id=1);

11.查询没学过张三老师课的学生的学号

SELECT DISTINCT s_id FROM sc WHERE s_id NOT IN( SELECT s_id FROM sc WHERE c_id=(SELECT c_id FROM course WHERE t_id=(SELECT t_id FROM teacher WHERE t_name="张三") ) );

12.查询成绩不及格的课程数大于2的学生的学号和课程数

SELECT s_id,COUNT(*) FROM sc WHERE sc<60 GROUP BY s_id HAVING COUNT(*)>=2;

13.查询课程1小于60分的学生的学号，成绩按降序排序

SELECT s_id,score FROM sc WHERE score<60  AND c_id=1 ORDER BY score DESC;

14.按平均成绩高低显示所有学生所有课程的成绩及平均成绩

SELECT a.*,b.avg FROM  sc AS a ,(SELECT s_id,AVG(score) AS AVG FROM sc GROUP BY s_id )b WHERE a.s_id =b.s_id ORDER BY b.avg DESC;

15.查询各科成绩最高分、最低分和平均分

select c_id,max(score) as "最高分",min(score) as "最低分",avg(score) as "平均分" from sc  group by c_id;

未完待续！