学生，成绩，老师，课程
student (学生)，score(成绩)，teacher(老师)，sc(课程)
- 创建表
--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 分数
*/
--创建测试数据
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);
- sql查询
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT s.*,c1.`score` "01课程",c2.`score` "02课程"
FROM student s,sc c1,sc c2
WHERE c1.`SID`=s.`SID` AND c2.`SID`=s.`SID`
AND c1.`CID`="01" AND c2.`CID`="02" AND c1.`score`>c2.`score`
-- 1.2、所有学生的01和02课程，没有为null
SELECT s.*,sc.`score` "01",c2.`score` "02" FROM student s LEFT JOIN sc ON s.`SID`=sc.`SID` AND sc.`CID`="01"
LEFT JOIN sc c2 ON s.`SID`=c2.`SID` AND c2.`CID`="02"
-- 必须存在‘01’课程，可以存在‘02’课程（但要显示02课程）
SELECT c1.*,c2.`score` "02" FROM
(SELECT s.*,sc.`score` "01" FROM student s,sc WHERE s.`SID`=sc.`SID` AND sc.`CID`="01") c1
LEFT JOIN sc c2 ON c1.`SID`=c2.`SID` AND c2.`CID`="02"
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.*,CAST(AVG(sc.`score`) AS DECIMAL(18,2)) "ascore" FROM student s,sc WHERE s.`SID`=sc.`SID` GROUP BY s.`SID` HAVING ascore>=60
-- 4.1、查询在sc表存在成绩的学生信息的SQL语句。
SELECT s.* FROM student s,sc WHERE s.`SID`=sc.`SID` GROUP BY s.`SID` -- 方式一
SELECT * FROM student s WHERE EXISTS (SELECT * FROM sc WHERE sc.`SID`=s.`SID`) -- 方式二
-- 4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
SELECT * FROM student s WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.`SID`=s.`SID`)
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s.*,COUNT(1) "选课总数",SUM(sc.`score`) "总成绩"
FROM student s,sc
WHERE s.`SID`=sc.`SID`
GROUP BY s.`SID`
-- 5.2、查询所有(包括有成绩和无成绩)的SQL。
SELECT s.*,COUNT(1) "选课总数",SUM(sc.`score`) "总成绩"
FROM student s LEFT JOIN sc ON s.`SID`=sc.`SID`
GROUP BY s.`SID`
-- 6、查询"李"姓老师的数量
SELECT COUNT(1) FROM teacher WHERE tname LIKE '李%'
-- 7、查询学过"张三"老师授课的同学的信息
SELECT *
FROM student s,teacher t,course c ,sc
WHERE t.tname="张三" AND t.tid= c.tid AND c.cid=sc.`CID` AND sc.sid=s.`SID`
-- 8、查询没学过"张三"老师授课的同学的信息
SELECT * FROM student st WHERE NOT EXISTS
(SELECT *
FROM sc,course c,teacher t
WHERE st.`SID`=sc.`SID` AND sc.`CID`=c.cid AND c.tid=t.tid AND t.tname="张三")
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * FROM student s,sc c1,sc c2 WHERE s.`SID`=c1.`SID` AND s.`SID`=c2.`SID`
AND c1.`CID`="01" AND c2.`CID`="02"
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM student s WHERE EXISTS(SELECT * FROM sc c1 WHERE c1.`SID`=s.`SID` AND c1.`CID`="01")
AND NOT EXISTS (SELECT * FROM sc c2 WHERE s.`SID`=c2.`SID` AND c2.`CID`="02")
-- 11、查询没有学全所有课程的同学的信息
SELECT s.* FROM student s WHERE NOT EXISTS (
SELECT * FROM sc c1,sc c2,sc c3
WHERE s.`SID`=c1.`SID` AND s.`SID`=c2.`SID` AND s.`SID`=c3.sid
AND c1.`CID`="01" AND c2.`CID`="02" AND c3.cid="03")
SELECT * FROM student s,sc WHERE s.`SID`=sc.`SID`
GROUP BY s.`SID` HAVING COUNT(sc.`CID`)<(SELECT COUNT(1) FROM course)
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT s.*
FROM student s,sc
WHERE s.sid=sc.`SID` AND sc.cid IN(SELECT cid FROM sc WHERE sid="01")
AND s.`SID`<>"01"
-- <> 代表：不包括
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT * FROM student s,sc c1,sc c2,sc c3
WHERE s.`SID`=c1.`SID` AND s.`SID`=c2.`SID` AND s.`SID`=c3.sid
AND c1.`CID`="01" AND c2.`CID`="02" AND c3.cid="03" AND s.`SID`<> "01"
-- 14、查询没学过"张三"老师讲授的任意一门课程的学生姓名
SELECT * FROM student WHERE sid NOT IN (
SELECT s.sid FROM student s,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="张三"))
SELECT * FROM student WHERE sid NOT IN (
SELECT s.sid FROM student s,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="张三"))
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.`SID`,s.`Sname`, CAST(AVG(c1.`score`) AS DECIMAL(18,2)) avg_score FROM student s,sc c1
WHERE s.`SID`=c1.`SID` AND s.`SID` IN
(SELECT sid FROM sc WHERE score<60 GROUP BY sid HAVING COUNT(1)>=2)
GROUP BY s.`SID`
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT student.* , sc.CID , sc.score FROM student , sc
WHERE student.SID = SC.SID AND sc.score < 60 AND sc.CID = '01'
ORDER BY sc.score DESC
-- 17、(完美perfect)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.*,c1.`score` "01",c2.score "02",c3.score "03",ss.avg_score FROM student s
LEFT JOIN (SELECT sid,CAST(AVG(score) AS DECIMAL(18,2)) avg_score FROM sc GROUP BY sid) ss ON ss.sid=s.`SID`
LEFT JOIN sc c1 ON s.`SID`=c1.`SID` AND c1.`CID`="01"
LEFT JOIN sc c2 ON s.`SID`=c2.`SID` AND c2.`CID`="02"
LEFT JOIN sc c3 ON s.`SID`=c3.sid AND c3.cid="03"
GROUP BY s.`SID`
ORDER BY ss.avg_score DESC
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT c.cid,c.cname,MAX(sc.`score`) "最高分",MIN(sc.`score`) "最低分",CAST(AVG(sc.`score`) AS DECIMAL(18,2)) "平均分",
CAST(jg.co/COUNT(1)*100 AS DECIMAL(18,2)) "及格率",CAST(zd.cz/COUNT(1)*100.0 AS DECIMAL(18,2)) "中等率"
FROM sc LEFT JOIN course c ON sc.`CID`=c.cid
LEFT JOIN (SELECT cid,COUNT(1) co FROM sc WHERE score>=60 GROUP BY cid) jg ON jg.cid=c.cid
LEFT JOIN (SELECT cid,COUNT(1) cz FROM sc WHERE score>=70 AND score <80 GROUP BY cid) zd ON zd.cid=c.cid
GROUP BY c.cid
SELECT m.CID "课程编号" , m.Cname "课程名称" ,
MAX(n.score) "最高分" ,
MIN(n.score) "最低分" ,
CAST(AVG(n.score) AS DECIMAL(18,2)) "平均分" ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 60)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 及格率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 70 AND score < 80 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 中等率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 80 AND score < 90 )*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优良率 ,
CAST((SELECT COUNT(1) FROM SC WHERE CID = m.CID AND score >= 90)*100.0 / (SELECT COUNT(1) FROM SC WHERE CID = m.CID) AS DECIMAL(18,2)) 优秀率
FROM Course m , SC n
WHERE m.CID = n.CID
GROUP BY m.CID , m.Cname
ORDER BY m.CID
select * from user,(select DATE_FORMAT(create_time,‘%Y-%m-%d’) as ‘od’ from user where create_time between ‘2019-08-01’ and ‘2019-08-31’ order by create_time limit 1) as oneday
where datediff(create_time,oneday.od)=0
小sql
-- 查询今天的数据
create_time BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)
扫一扫
599
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
