1.表结构详情
--1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
--3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
--4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
2.建表与数据导入
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
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' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
3.SQL
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 01课程,02课程同时存在
-- 查询学生的01课程,02课程(三张表:学生表,01课程表,02课程表)
SELECT * FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid=01 AND sc2.cid=02
-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT * FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid=01 AND sc2.cid=02
AND sc1.score>sc2.score
-- reference
SELECT s.*,sc1.score ,sc2.score
FROM student s,sc sc1,sc sc2
WHERE s.sid=sc1.sid AND s.sid=sc2.sid
AND sc1.cid='01' AND sc2.cid='02'
AND sc1.score>sc2.score
-- 01课程,02课程不同时存在
SELECT * FROM student s
LEFT JOIN sc sc1 ON s.sid=sc1.sid AND sc1.cid='01'
LEFT JOIN sc sc2 ON s.sid=sc2.sid AND sc2.cid='02'
WHERE IFNULL(sc1.score,0) > IFNULL(sc2.score,0)
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 学生都存在成绩
-- 查询学生和成绩
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学生和成绩平均成绩
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc sc
WHERE s.sid=sc.sid GROUP BY s.sid
-- 平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,AVG(sc.score) FROM student s,sc sc
WHERE s.sid=sc.sid GROUP BY s.sid
HAVING AVG(sc.score)>=60
ORDER BY s.sid
-- DECIMAL(5,2),则该字段可以存储-999.99~999.99,最大值为999.99。
-- 也就是说D表示的是小数部分长度,(M-D)表示的是整数部分长度
-- CAST(源数据)别名 功能-->起别名
-- reference
SELECT a.SID , a.Sname , CAST(AVG(b.score) AS DECIMAL(18,2)) avg_score
FROM Student a , sc b
WHERE a.SID = b.SID
GROUP BY a.SID , a.Sname
HAVING CAST(AVG(b.score) AS DECIMAL(18,2)) >= 60
ORDER BY a.SID
-- 学生不存在成绩
-- 查询学生与成绩
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
-- 查询学生与平均成绩
SELECT *,AVG(sc.score) FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
-- 平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.sname,IFNULL(AVG(sc.score),0) FROM student s LEFT JOIN sc sc ON s.sid=sc.sid
GROUP BY s.sid
HAVING IFNULL(AVG(sc.score),0)>60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 5.1、查询所有有成绩的SQL
-- 查询学生与成绩表
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学生与成绩表 选课总数
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
-- 查询学生与成绩表 选课总数 所有课程的总成绩
SELECT s.sid,s.sname,COUNT(sc.cid) 选课总数,SUM(sc.score)总成绩
FROM student s,sc sc
WHERE s.sid=sc.sid
GROUP BY s.sid
-- reference
SELECT a.SID 学生编号 , a.Sname 学生姓名 , COUNT(b.CID) 选课总数, SUM(score) 所有课程的总成绩
FROM Student a , SC b
WHERE a.SID = b.SID
GROUP BY a.SID,a.Sname
ORDER BY a.SID
-- 5.2、查询所有(包括有成绩和无成绩)的SQL
SELECT s.sid,s.sname,COUNT(sc.cid) 选课总数,SUM(IFNULL(sc.score,0))总成绩
FROM student s LEFT JOIN sc sc
ON s.sid=sc.sid
GROUP BY s.sid
-- reference
SELECT a.SID 学生编号 , a.Sname 学生姓名 , COUNT(b.CID) 选课总数, SUM(score) 所有课程的总成绩
FROM Student a LEFT JOIN SC b
ON a.SID = b.SID
GROUP BY a.SID,a.Sname
ORDER BY a.SID
-- 6、查询"李"姓老师的数量
-- 方法1
SELECT COUNT(*) FROM teacher t WHERE tname LIKE '李%'
SELECT COUNT(tname) FROM teacher t WHERE tname LIKE '李_'
-- 方法2
-- reference
SELECT COUNT(Tname) 李姓老师的数量 FROM Teacher WHERE LEFT(Tname,1) = '李'
-- 7、查询学过"张三"老师授课的同学的信息
-- 7、查询学过"张三"老师授课的同学的信息
-- 老师授课
SELECT * FROM teacher t,course c WHERE t.tid=c.tid
-- 张三老师授课
SELECT * FROM teacher t,course c WHERE t.tid=c.tid AND t.tname='张三'
-- 学生与成绩表
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 学过"张三"老师授课的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT c.cid FROM teacher t,course c
WHERE t.tid=c.tid AND t.tname='张三')
-- 方式二(推荐)
-- 查询学过"张三"老师授课的同学的信息
-- 四张表 老师,课程,学生,成绩
-- 去除笛卡尔积
-- 课程表关联老师 c.tid=t.tid
-- 成绩表关联学生 sc.sid=s.sid
-- 成绩表关联课程 sc.cid=c.cid
-- 查询老师授课的同学的信息
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
-- 查询学过"张三"老师授课的同学的信息
SELECT s.* FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三'
-- reference
SELECT DISTINCT Student.* FROM Student , SC , Course , Teacher
WHERE Student.SID = SC.SID AND SC.CID = Course.CID AND Course.TID = Teacher.TID
AND Teacher.Tname = '张三'
ORDER BY Student.SID
-- 8、查询没学过"张三"老师授课的同学的信息
-- 查询学过"张三"老师授课的同学的信息
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三'
-- 错误 方式一:
-- 这样只是在查询 李四,王五老师授课的信息 该生有没有被张三老师教过并没有查询
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND (t.tname='李四' OR t.tname='王五')
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname IN('李四','王五')
SELECT * FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname NOT IN('张三')
-- 方式二:
SELECT * FROM student s2
WHERE s2.sid NOT IN(SELECT s.sid FROM student s,sc sc,teacher t,course c
WHERE sc.sid=s.sid AND c.tid=t.tid AND sc.cid=c.cid
AND t.tname='张三')
-- reference
SELECT m.* FROM Student m
WHERE SID NOT IN (SELECT DISTINCT SC.SID FROM SC , Course , Teacher
WHERE SC.CID = Course.CID AND Course.TID = Teacher.TID AND Teacher.Tname = '张三')
ORDER BY m.SID
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 查询学生课程信息(两张表)
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- 查询学过01,02课程的学生课程信息
-- 这样查询的是学过01或02课程的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid IN(01,02)
-- 查询学过编号为"01"或编号为"02"的课程的同学的信息
SELECT * FROM student s
WHERE s.sid IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid IN(01,02))
-- 总结 思路很重要 不先想清楚 写了也白写
-- 思路 先找出学过01课程的学生 再学过01课程的学生中再找学过02课程的学生
-- 学过01课程的学生
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s.* FROM student s,sc sc
WHERE s.sid=sc.sid AND sc.cid=02
AND s.sid IN(SELECT sc.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01)
SELECT s.* FROM student s,sc sc
WHERE s.sid=sc.sid AND sc.cid=02
AND EXISTS(SELECT 1 FROM sc sc2 WHERE sc2.sid=sc.sid AND sc2.cid=01)
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
SELECT *,9 FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=01
-- 方法1
-- reference
SELECT Student.* FROM Student , SC
WHERE Student.SID = SC.SID AND SC.CID = '01'
AND EXISTS (SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 方法2
-- reference
SELECT Student.* FROM Student , SC
WHERE Student.SID = SC.SID AND SC.CID = '02'
AND EXISTS (SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '01') ORDER BY Student.SID
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 查询学过01课程的学生
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01' -- 123456
-- 学过编号为"02"的课程的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02' -- 123457
-- 没有学过编号为"02"的课程的同学的信息
-- 错误:SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid not in (02)
SELECT * FROM student s WHERE sid
NOT IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=02)
-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid IN(
SELECT s.sid FROM student s WHERE sid
NOT IN(SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid=02))
-- 改进
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid NOT IN(
SELECT s.sid FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02')
-- 再改进(新增成绩表2 用来记录02课程的信息 共用前面的学生表
-- 这里的共用仅为表的引用 而非结果集 结果集相互独立 不受影响)
-- 用 AND等条件连接的子句 都是并列同时执行 如果有括弧 如in() 则每次先执行括弧里面的子句
-- 将结果作为整体条件 与前面的条件并列
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND s.sid NOT IN(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='02' -- 123457
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01' -- 123456
-- 6
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND NOT EXISTS(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
-- 12345
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid AND sc.cid='01'
AND EXISTS(
SELECT s.sid FROM sc sc2 WHERE s.sid=sc2.sid AND sc2.cid='02')
-- reference
-- 方法1
SELECT Student.* FROM Student , SC WHERE Student.SID = SC.SID AND SC.CID = '01'
AND NOT EXISTS (
SELECT 1 FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 这里 SELECT 后的变量可以任意更改 仅仅为了满足语法需求
-- 方法2
SELECT Student.* FROM Student , SC WHERE Student.SID = SC.SID AND SC.CID = '01'
AND Student.SID NOT IN (
SELECT SC_2.SID FROM SC SC_2 WHERE SC_2.SID = SC.SID AND SC_2.CID = '02') ORDER BY Student.SID
-- 11、查询没有学全所有课程的同学的信息
-- 分析 分为两种情况 修了课程的学生和没修课程的学生 当然可以用外连接统一到一起查询
-- 1查询课程数量
SELECT COUNT(*) FROM course c
-- 2查询学生所修课程数量
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
-- 3查询所修课程数小于总课程数的学生
SELECT *,COUNT(*) FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING COUNT(*)<3
SELECT *,COUNT(*) 课程总数 FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING 课程总数<(SELECT COUNT(*) FROM course c)
SELECT *,COUNT(*) 课程总数 FROM student s,sc sc WHERE s.sid=sc.sid GROUP BY s.sid
HAVING 课程总数<ALL(SELECT COUNT(*) FROM course c)
-- 改进
SELECT * FROM student s LEFT JOIN sc sc ON s.sid=sc.sid GROUP BY s.sid
HAVING COUNT(*)<(SELECT COUNT(*)FROM course c)
-- reference
SELECT Student.* FROM Student LEFT JOIN SC ON Student.SID = SC.SID
GROUP BY Student.SID , Student.Sname , Student.Sage , Student.Ssex
HAVING COUNT(CID) < (SELECT COUNT(CID) FROM Course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- 所有学生的课程信息
SELECT * FROM student s,sc sc WHERE s.sid=sc.sid
-- "01"的同学所学的课程
SELECT *,sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01'
SELECT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01')
GROUP BY s.sid
-- 改进
SELECT DISTINCT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc.cid FROM student s,sc sc WHERE s.sid=sc.sid AND s.sid='01')
-- 再改进
SELECT DISTINCT s.* FROM student s,sc sc WHERE s.sid=sc.sid
AND sc.cid IN(SELECT sc2.cid FROM sc sc2 WHERE sc2.sid='01')
-- ref
SELECT DISTINCT Student.* FROM Student , SC WHERE Student.SID = SC.SID
AND SC.CID IN (SELECT CID FROM SC WHERE SID = '01') AND Student.SID <> '01'
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 查询其它同学的课程信息
SELECT * FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid
-- 统计其它同学的课程的数
SELECT s.*,COUNT(*)FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid GROUP BY sc.sid
-- 统计01学生所学课程数
SELECT COUNT(*) FROM sc WHERE sc.sid='01'
-- 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s.* FROM student s,sc sc WHERE s.sid <> '01' AND s.sid=sc.sid GROUP BY sc.sid
HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 以上写法有问题 这样统计的只是学科数目上与01学生相同而不区分科目 所以并不是完全相同
-- 当用06同学测试时错误就很明显了
SELECT s.* FROM student s,sc sc WHERE s.sid <> '06' AND s.sid=sc.sid GROUP BY sc.sid
HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='06')
-- 改进
-- 分析完全相同 即数量相同并且课程也相同
-- 01学生所学课程
SELECT sc.cid FROM sc sc WHERE sc.sid='01'
-- 统计01学生所学课程数
SELECT COUNT(*) FROM sc WHERE sc.sid='01'
-- 1统计包含01学生课程的其他学生课程
SELECT * FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
-- 2统计包含01学生课程的其他学生课程数量
SELECT *,COUNT(*) FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid
-- 3统计课程,课程数量与01学生相同的其他学生课程信息
SELECT *,COUNT(*) FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 4统计课程,课程数量与01学生相同的其他学生id
SELECT sc.sid FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01')
-- 5查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT * FROM student s WHERE s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'01' AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01'))
SELECT * FROM student s WHERE
s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'01'
AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='01')
GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE sc.sid='01'))
SELECT * FROM student s WHERE
s.sid IN(SELECT sc.sid FROM sc WHERE sc.sid<>'06'
AND sc.cid IN(SELECT sc.cid FROM sc sc WHERE sc.sid='06