MySQL从入门到精通——实战45例
一、创建数据库和数据表
CREATE DATABASE guantu; # 创建数据库
USE guantu; #选择数据库
# 学生表
CREATE TABLE Student (
st_id VARCHAR ( 20 ),
st_name VARCHAR ( 20 ) NOT NULL DEFAULT '',
st_birth VARCHAR ( 20 ) NOT NULL DEFAULT '',
st_sex VARCHAR ( 10 ) NOT NULL DEFAULT '',
PRIMARY KEY ( st_id )
);
#成绩表
CREATE TABLE Score (
st_id VARCHAR ( 20 ),
sc_id VARCHAR ( 20 ),
sc_score INT ( 3 ),
PRIMARY KEY ( st_id, sc_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 )
);
二、插入数据
#插入学生数据
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 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 );
#插入课程数据
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', '王五' );
三、实战案例
# 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
st.*,
sc.sc_score
FROM
Student st #
#查询"01"课程
#
LEFT JOIN Score sc ON st.st_id = sc.st_id
AND sc.sc_id = '01' #
#查询"02"课程
#
LEFT JOIN Score sc2 ON st.st_id = sc2.st_id
AND sc2.sc_id = '02' #
#查询"01"课程比"02"课程
#
WHERE
sc.sc_score > sc2.sc_score
# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT
st.*,
sc.sc_score
FROM
Student st #
#查询"01"课程
#
LEFT JOIN Score sc ON st.st_id = sc.st_id
AND sc.sc_id = '01' #
#查询"02"课程
#
LEFT JOIN Score sc2 ON st.st_id = sc2.st_id
AND sc2.sc_id = '02' #
#查询"01"课程比"02"课程
#
WHERE
sc.sc_score < sc2.sc_score
# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.st_id,st.st_name,ROUND(AVG(sc.sc_score),2)
FROM Student st
#
#查询"01"课程
#
LEFT JOIN Score sc ON st.st_id = sc.st_id
#
#按照学生编号分组
#
GROUP BY st.st_id
#
#平均成绩大于等于60分
#
HAVING AVG(sc.sc_score) >= 60
# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT st.st_id,st.st_name,ROUND(AVG(sc.sc_score),2)
FROM Student st
#
#查询"01"课程
#
LEFT JOIN Score sc ON st.st_id = sc.st_id
#
#按照学生编号分组
#
GROUP BY st.st_id
#
#平均成绩大于等于60分
#
HAVING AVG(sc.sc_score) < 60 OR AVG(sc.sc_score) IS NULL
# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
st.st_id,
st.st_name,
COUNT( sc.sc_id ),
SUM( sc.sc_score )
FROM
Student st #
#左外连接
#
LEFT JOIN Score sc ON st.st_id = sc.st_id #
#按照学生编号分组
#
GROUP BY
st.st_id
# 6、查询"李"姓老师的数量
SELECT
t.t_name,
COUNT( t.t_id )
FROM
Teacher t
WHERE
t.t_name LIKE '%李%'
GROUP BY
t.t_id
# 7、查询学过"张三"老师授课的同学的信息
SELECT
st.*,
sc.sc_id,
c.c_name,
t.t_name
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
LEFT JOIN Teacher t ON t.t_id = c.t_id
WHERE
t.t_name = '张三'
SELECT
st.*,
sc.sc_id
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
c.c_id IN (
SELECT
c.c_id
FROM
Course c
LEFT JOIN Teacher t ON c.t_id = t.t_id
WHERE
t.t_name = '李四'
)
# *8、查询没学过"张三"老师授课的同学的信息
SELECT
st.*
FROM
Student st
WHERE
st.st_id NOT IN (
SELECT
st.st_id
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
LEFT JOIN Course c ON sc.sc_id = c.c_id
LEFT JOIN Teacher t ON c.t_id = t.t_id
WHERE
t.t_name = '张三'
)
# *9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
st.*,
sc.sc_id '01',
sc2.sc_id '02'
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
AND sc.sc_id = '01'
LEFT JOIN Score sc2 ON st.st_id = sc2.st_id
AND sc2.sc_id = '02'
WHERE
sc.sc_id = '01'
AND sc2.sc_id = '02'
# 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT
st.*
FROM
Student st
JOIN Score sc ON st.st_id = sc.st_id
AND sc.sc_id = '01'
WHERE st.st_id NOT IN (
SELECT
st.st_id
FROM
Student st
JOIN Score sc2 ON st.st_id = sc2.st_id
AND sc2.sc_id = '02'
)
#查询没学过"张三"老师授课的同学的信息
-- 张三老师教的课
SELECT st.*
FROM Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Teacher t ON t.t_id = sc.sc_id
WHERE t.t_name = '张三'
-- 有张三老师课成绩的st.s_id
SELECT st.st_id
FROM Student st
WHERE st.st_id IN (
SELECT st.st_id
FROM Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Teacher t ON t.t_id = sc.sc_id
WHERE t.t_name = '张三' )
-- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
SELECT st.*
FROM Student st
WHERE st.st_id NOT IN (
SELECT st.st_id
FROM Student st
WHERE st.st_id IN (
SELECT st.st_id
FROM Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Teacher t ON t.t_id = sc.sc_id
WHERE t.t_name = '张三' )
)
# 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT
st.*
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
WHERE
sc.sc_id = ANY (
SELECT
sc.sc_id
FROM
Score sc
WHERE
sc.st_id = '01')
# 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT
st.*
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
GROUP BY
st.st_id
HAVING
GROUP_CONCAT( sc.sc_id ) = (
SELECT
GROUP_CONCAT( sc.sc_id )
FROM
Score sc
WHERE
sc.st_id = '01')
# 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
st.*
FROM
Student st
WHERE
st.st_id NOT IN (
SELECT
st.st_id
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
LEFT JOIN Course c ON sc.sc_id = c.c_id
LEFT JOIN Teacher t ON t.t_id = sc.sc_id
WHERE
t.t_name = '张三'
)
# 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SELECT st.st_id,st.st_name,ROUND(AVG(sc.sc_score),2)
FROM Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
WHERE sc.sc_score < 60
GROUP BY sc.st_id
HAVING COUNT(sc.sc_score) >= 2
# 16、检索"01"课程分数小于60,按分数降序排列的学生信息
SELECT
st.*,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
AND sc.sc_id = '01'
WHERE
sc.sc_score < 60
ORDER BY
sc.sc_score DESC;
# 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
st.st_id,
st.st_name,
sc.sc_score '语文',
sc2.sc_score '数学',
sc3.sc_score '英语',
ROUND( AVG( sc4.sc_score ), 2 )
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
AND sc.sc_id = '01'
LEFT JOIN Score sc2 ON sc2.st_id = st.st_id
AND sc2.sc_id = '02'
LEFT JOIN Score sc3 ON sc3.st_id = st.st_id
AND sc3.sc_id = '03'
LEFT JOIN Score sc4 ON sc4.st_id = st.st_id
GROUP BY
st.st_id
ORDER BY
AVG( sc4.sc_score ) DESC;
# *18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
# 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT
c.c_name,
sc.sc_id,
MAX( sc.sc_score ),
MIN( sc.sc_score ),
ROUND( AVG( sc.sc_score ), 2 )
FROM
Score sc
LEFT JOIN Course c ON c.c_id = sc.sc_id
GROUP BY
sc.sc_id
# *19、按各科成绩进行排序,并显示排名(实现不完全)
SELECT
a.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '01'
GROUP BY st.st_id,st.st_name
ORDER BY sc.sc_score DESC
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '02'
GROUP BY st.st_id,st.st_name
ORDER BY sc.sc_score DESC
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '03'
GROUP BY st.st_id,st.st_name
ORDER BY sc.sc_score DESC
) c
# 20、查询学生的总成绩并进行排名
SELECT
st.*,
SUM( sc.sc_score )
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
GROUP BY
st.st_id
ORDER BY
sum( sc.sc_score ) DESC;
# 21、查询不同老师所教不同课程平均分从高到低显示
SELECT
t.t_id,
t.t_name,
ROUND( AVG( sc.sc_score ), 2 )
FROM
Teacher t
LEFT JOIN Course c ON c.t_id = t.t_id
LEFT JOIN Score sc ON sc.sc_id = c.c_id
GROUP BY
t.t_id
ORDER BY
ROUND( AVG( sc.sc_score ), 2 ) DESC;
# 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT
a.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '01'
LIMIT 1,
2
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '02'
LIMIT 1,
2
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '03'
LIMIT 1,
2
) c
# *23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0]及所占百分比
SELECT
sc.sc_id '课程编号',
COUNT( sc.sc_id ) '人数',
c.c_name '课程名称',
((
SELECT
COUNT( sc.sc_id )
FROM
Score sc
WHERE
c.c_id = sc.sc_id
AND sc.sc_score < 100 AND sc.sc_score > 85
) / (
SELECT
COUNT( sc.sc_id )
FROM
Score sc
WHERE
c.c_id = sc.sc_id
AND sc.sc_score < 100 AND sc.sc_score > 0
)) '[100,85]'
FROM
Score sc
LEFT JOIN Course c ON c.c_id = sc.sc_id
GROUP BY
sc.sc_id
# 24、查询学生平均成绩及其名次
SELECT
st.st_id,
st.st_name,
ROUND(
AVG( sc.sc_score ))
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
GROUP BY
st.st_id,
st.st_name
ORDER BY
AVG( sc.sc_score ) DESC;
# 25、查询各科成绩前三名的记录
SELECT
a.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '01'
LIMIT 0,
3
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '02'
LIMIT 0,
3
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '03'
LIMIT 0,
3
) c
# 26、查询每门课程被选修的学生数
SELECT
c.c_id,
c.c_name,
COUNT( sc.sc_id )
FROM
Course c
LEFT JOIN Score sc ON sc.sc_id = c.c_id
GROUP BY
c.c_id
# 27、查询出只有两门课程的全部学生的学号和姓名
SELECT
st.st_id,
st.st_name
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
GROUP BY
st.st_id
HAVING
COUNT( sc.sc_id ) = 2
# 28、查询男生、女生人数
SELECT
st.st_sex,
COUNT( st.st_sex )
FROM
Student st
GROUP BY
st.st_sex
# 29、查询名字中含有"风"字的学生信息
SELECT
st.*
FROM
Student st
WHERE
st.st_name LIKE '%风%'
# 31、查询1990年出生的学生名单
SELECT
st.*
FROM
Student st
WHERE
st.st_birth LIKE '%1990%'
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT
c.c_name,
ROUND(
AVG( sc.sc_score ))
FROM
Course c
LEFT JOIN Score sc ON c.c_id = sc.sc_id
GROUP BY
c.c_name
ORDER BY
ROUND(
AVG( sc.sc_score )) DESC;
# 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
st.st_id,
st.st_name,
ROUND(
AVG( sc.sc_score ))
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
GROUP BY
st.st_id,
st.st_name
HAVING
ROUND(
AVG( sc.sc_score )) > 85
# 34、查询课程名称为"数学",且分数低于60的学生姓名和分数
SELECT
c.c_name,
st.st_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_score < 60
AND c.c_name = '数学'
# 35、查询所有学生的课程及分数情况
SELECT
st.*,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
# 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT
st.st_name,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_score > 70
# 37、查询不及格的课程
SELECT
st.st_id,
st.st_name,
c.c_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_score < 60
# 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT
st.st_id,
st.st_name,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
WHERE
sc.sc_id = '01'
AND sc.sc_score > 70
# 39、求每门课程的学生人数
SELECT
c.c_name,
COUNT( sc.sc_id )
FROM
Course c
LEFT JOIN Score sc ON sc.sc_id = c.c_id
GROUP BY
c.c_name
# 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT
st.*,
sc.sc_score
FROM
Student st
LEFT JOIN Score sc ON st.st_id = sc.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
LEFT JOIN Teacher t ON t.t_id = c.t_id
WHERE
t.t_name = '张三'
LIMIT 0,1
# 42、查询每门功成绩最好的前两名
SELECT a.*
FROM (
SELECT st.*,c.c_name,sc.sc_score
FROM Course c
LEFT JOIN Score sc ON sc.sc_id = c.c_id AND c.c_name = '语文'
LEFT JOIN Student st ON st.st_id = sc.st_id
LIMIT 0,2 ) a
UNION ALL
SELECT b.*
FROM (
SELECT st.*,c.c_name,sc.sc_score
FROM Course c
LEFT JOIN Score sc ON sc.sc_id = c.c_id AND c.c_name = '数学'
LEFT JOIN Student st ON st.st_id = sc.st_id
LIMIT 0,2 ) b
UNION ALL
SELECT c.*
FROM (
SELECT st.*,c.c_name,sc.sc_score
FROM Course c
LEFT JOIN Score sc ON sc.sc_id = c.c_id AND c.c_name = '英语'
LEFT JOIN Student st ON st.st_id = sc.st_id
LIMIT 0,2 ) c
# 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT c.c_id, COUNT(sc.sc_id)
FROM Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
GROUP BY c.c_id
HAVING COUNT(sc.sc_id) > 5
ORDER BY c.c_id, COUNT(sc.sc_id) DESC;
# 44、检索至少选修两门课程的学生学号
SELECT
st.st_id,
st.st_name,
COUNT( sc.sc_id )
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
GROUP BY
st.st_id
HAVING
COUNT( sc.sc_id ) >= 2
# 45、查询选修了全部课程的学生信息
SELECT
st.st_id,
st.st_name,
COUNT( sc.sc_id )
FROM
Student st
LEFT JOIN Score sc ON sc.st_id = st.st_id
LEFT JOIN Course c ON c.c_id = sc.sc_id
GROUP BY
st.st_id
HAVING
COUNT( c.c_id ) = 3