数据库之 MySQL—— 50个查询系列

浅浅的花香味﹌ 2022-02-09 08:51 282阅读 0赞

个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有)

一个项目涉及到的50个Sql语句  
问题及描述：  
\--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 分数

> select \* from Student  
> select \* from Course  
> select \* from Teacher  
> select \* from SC

**--创建测试数据**

create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
    
    create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10))
    
    create table Teacher(Tid varchar(10),Tname nvarchar(10))
    
    create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1))
    
    --------------------------------插入数据-------------------------------------------------
    
    insert into student
    select '1000','张无忌',18,'男' union
    select '1001','周芷若',19,'女' union
    select '1002','杨过',19,'男' union
    select '1003','赵敏',18,'女' union
    select '1004','小龙女',17,'女' union
    select '1005','张三丰',18,'男' union
    select '1006','令狐冲',19,'男' union
    select '1007','任盈盈',20,'女' union
    select '1008','岳灵珊',19,'女' union
    select '1009','韦小宝',18,'男' union
    select '1010','康敏',17,'女' union
    select '1011','萧峰',19,'男' union
    select '1012','黄蓉',18,'女' union
    select '1013','郭靖',19,'男' union
    select '1014','周伯通',19,'男' union
    select '1015','瑛姑',20,'女' union
    select '1016','李秋水',21,'女' union
    select '1017','黄药师',18,'男' union
    select '1018','李莫愁',18,'女' union
    select '1019','冯默风',17,'男' union
    select '1020','王重阳',17,'男' union
    select '1021','郭襄',18,'女' 
    go
    
    insert  into teacher
    select '001','姚明' union
    select '002','叶平' union
    select '003','叶开' union
    select '004','孟星魂' union
    select '005','独孤求败' union
    select '006','裘千仞' union
    select '007','裘千尺' union
    select '008','赵志敬' union
    select '009','阿紫' union
    select '010','郭芙蓉' union
    select '011','佟湘玉' union
    select '012','白展堂' union
    select '013','吕轻侯' union
    select '014','李大嘴' union
    select '015','花无缺' union
    select '016','金不换' union
    select '017','乔丹'
    go
    
    insert into course
    select '001','企业管理','002' union
    select '002','马克思','008' union
    select '003','UML','006' union
    select '004','数据库','007' union
    select '005','逻辑电路','006' union
    select '006','英语','003' union
    select '007','电子电路','005' union
    select '008','思想概论','004' union
    select '009','西方哲学史','012' union
    select '010','线性代数','017' union
    select '011','计算机基础','013' union
    select '012','AUTO CAD制图','015' union
    select '013','平面设计','011' union
    select '014','Flash动漫','001' union
    select '015','Java开发','009' union
    select '016','C#基础','002' union
    select '017','Oracl数据库原理','010'
    go
    
    insert into SC
    select '1001','003',90 union
    select '1001','002',87 union
    select '1001','001',96 union
    select '1001','010',85 union
    select '1002','003',70 union
    select '1002','002',87 union
    select '1002','001',42 union
    select '1002','010',65 union
    select '1003','006',78 union
    select '1003','003',70 union
    select '1003','005',70 union
    select '1003','001',32 union
    select '1003','010',85 union
    select '1003','011',21 union
    select '1004','007',90 union
    select '1004','002',87 union
    select '1005','001',23 union
    select '1006','015',85 union
    select '1006','006',46 union
    select '1006','003',59 union
    select '1006','004',70 union
    select '1006','001',99 union
    select '1007','011',85 union
    select '1007','006',84 union
    select '1007','003',72 union
    select '1007','002',87 union
    select '1008','001',94 union
    select '1008','012',85 union
    select '1008','006',32 union
    select '1009','003',90 union
    select '1009','002',82 union
    select '1009','001',96 union
    select '1009','010',82 union
    select '1009','008',92 union
    select '1010','003',90 union
    select '1010','002',87 union
    select '1010','001',96 union
    
    select '1011','009',24 union
    select '1011','009',25 union
    
    select '1012','003',30 union
    select '1013','002',37 union
    select '1013','001',16 union
    select '1013','007',55 union
    select '1013','006',42 union
    select '1013','012',34 union
    select '1000','004',16 union
    select '1002','004',55 union
    select '1004','004',42 union
    select '1008','004',34 union
    select '1013','016',86 union
    select '1013','016',44 union
    select '1000','014',75 union
    select '1002','016',100 union
    select '1004','001',83 union
    select '1008','013',97
    go

> **数据库、表结构与数据，链接地址：**
> 
> **链接：https://pan.baidu.com/s/1K8ikM\_HmJYpUy70ClJyb3Q  
> 提取码：se3b**  
>

**--1、查询“001”课程比“002”课程成绩高的所有学生的学号；**

第一步明确主表和次表。

我们要查询的是学生的学号，那么主表就是学生表（student）

SELECT s.Sid 
    from student s
     where
    -- SC是次表，我们用来筛选数据
     (SELECT sc.score FROM SC sc WHERE sc.Cid='001'and s.Sid=sc.Sid) 
      >
    (SELECT sc.score FROM SC sc WHERE sc.Cid='002' and s.Sid=sc.Sid)

\--2、查询平均成绩大于60分的同学的学号和平均成绩；

select s.Sname,t1.*
    from student s,
       (select sc.Sid,avg(sc.score) avgscore
    		from sc sc 
    		group by sc.Sid
    		HAVING avgscore > 60 ) t1
    where t1.Sid = s.Sid

\--3、查询所有同学的学号、姓名、选课数、总成绩；

select s.Sname,s.Sid,
       (select count(sc.Cid)  from sc where sc.Sid=s.Sid) countsc,
       (select sum(sc.score)  from sc where sc.Sid=s.Sid) sumsc
    from student s

\--4、查询姓“李”的老师的个数；

SELECT
    	count(*),
    	t.Tname
    FROM
    	teacher t
    WHERE
    	t.Tname LIKE '李%'

\--5、查询没学过“叶平”老师课的同学的学号、姓名；

SELECT Sid,Sname FROM student
      WHERE Sid NOT IN
       (
        SELECT Sid FROM SC sc
         INNER JOIN course cu ON sc.Cid=cu.Cid
         INNER JOIN teacher tc ON cu.Tid=tc.Tid    
          WHERE tc.Tname='叶平'
       )

\--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select  s.Sid,s.Sname
    from student s ,
    	(select sc1.Sid 
    	from (select sc.Sid from sc where sc.Cid = '001' ) sc1 ,
    	     (select sc.Sid from sc where sc.Cid = '002' ) sc2
    	where sc1.Sid = sc2.Sid ) t1
    where s.Sid = t1.Sid

\--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT  s.Sid,s.Sname
    from student s
    where not EXISTS
    (
    select c.Cid from course c where c.Tid =    
    			(select t.Tid from teacher t where t.Tname ="叶平")
           and c.Cid not in 
    			(select sc.Cid from sc where sc.Sid = s.Sid)
    )

\--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT s.Sid,s.Sname
    from student s
     where
    -- SC是次表，我们用来筛选数据
     (SELECT sc.score FROM SC sc WHERE sc.Cid='002'and s.Sid=sc.Sid) 
      <
    (SELECT sc.score FROM SC sc WHERE sc.Cid='001' and s.Sid=sc.Sid)

\--9、查询所有课程成绩小于60分的同学的学号、姓名；

select s.Sid,s.Sname
    from student s
    where s.Sid not in (
    select sc.Sid from sc where  sc.score > 60 and s.Sid = sc.Sid)

分析：

>           1、先查询有课程大于60分的同学的学号
> 
>            2、使用 not 进行反向选择

\--10、查询没有学全所有课的同学的学号、姓名；

select s.Sid,s.Sname
    from student s
    where (select count(*) from sc where s.Sid=sc.Sid)
        <
         (select count(*) from course)

\--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

select s.Sid,s.Sname from student s 
    where s.Sid in 
    (
    select DISTINCT  sc.Sid  from sc where sc.Cid 
        in (select sc.Cid from sc where sc.Sid="1001")
    )

分析：

>    1、先根据 1001 号同学的Sid 查找到他所学的课程的编号，
> 
>    2、根据课程编号查找到所有学过该课程的 学生编号 Sid
> 
>    3、只要有学过其中至少一门以上课程的学生为符合条件筛选

\--12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；

select s.Sid,s.Sname
    from student s
    where s.Sid in (
    
    select DISTINCT sc.Sid from sc where sc.Cid not in (
        select sc.Cid from sc where sc.Sid = '1001')
    )

\--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩； (从子查询中获取父查询中的表名，这样也行？？？？)

UPDATE sc
    SET score = (SELECT
                   a.avgScore
                 FROM (SELECT
                         AVG(sc_1.score)    avgScore
                       FROM sc sc_1,(SELECT * FROM sc )c
                       WHERE c.Cid = sc_1.Cid) a)
    WHERE Cid IN(SELECT
                    course.Cid
                  FROM course,
                    teacher
                  WHERE teacher.Tname = '叶平'
                      AND teacher.Tid = course.Tid);

\--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

select Sid from sc where Sid not in (
    select Sid from sc where Cid not in (
    SELECT Cid from sc where Sid = "1002"))
    GROUP BY Sid 
    HAVING count(*) = (select count(*) from sc where Sid = "1002")

分析：

>  1、使用双重否定你的方式，快速且方便的获得结果；
> 
>  2、第一重not in 否定获取不包括 1002 同学所学课程的Sid ；
> 
>  3、第二重 not in 否定获取所学课程最少一个且为 1002 同学所学的课程的 Sid；
> 
>  4、使用 group by 分组函数去重
> 
>  5、获取 1002 同学所学课程的 count （\*）进行函数运算，满足 count (\*) 数量大小的说明 1002 号同学所学的课程都有学

\--15、删除学习“叶平”老师课的SC表记录；

DELETE sc 
    from course c,teacher t,sc
    where c.Cid = sc.Cid and t.Tid = c.Tid and t.Tname="叶平"

\--16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、'002'号课的平均成绩；

INSERT sc 
    select Sid,"002",
    (select avg(score)
    from sc 
    where Cid="002") 
    from student 
    where Sid 
    not in (
    select Sid
    from sc
    where Cid = "003")

> **分析：** 
> 
>     1、 子表 : 编号‘003“ 课程的同学学号，从 sc 表中查 where Cid= ”003“
> 
>     2、 子表： 编号”002“课程的平均成绩 ，从 sc表中查，where Cid ="002"
> 
>     3、 没有上过”003“课程 使用 not in   （子表1）
> 
>     4、 插入操作： insert sc(表名） ，插入记录，由2可知，插入课程为 “002”，使用select Sid ,"002"，score 使用 “002” 平均值，即 子表 2 作为临时表 。

\--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT
    	t.Sid AS 学生ID,
    	(
    		SELECT
    			score
    		FROM
    			sc
    		WHERE
    			sc.Sid = t.Sid
    		AND Cid = "004"
    	) AS 数据库,
    	(
    		SELECT
    			score
    		FROM
    			sc
    		WHERE
    			sc.Sid = t.Sid
    		AND Cid = "001"
    	) AS 企业管理,
    	(
    		SELECT
    			score
    		FROM
    			sc
    		WHERE
    			sc.Sid = t.Sid
    		AND Cid = "006"
    	) AS 英语,
    	count(*) AS 有效课程数,
    	avg(t.score) AS 平均成绩
    FROM
    	sc AS t
    GROUP BY
    	t.Sid
    ORDER BY
    	avg(t.score) DESC

分析：

>  1、使用select 查询 sc 表，并where 查询 对应的 Cid ，分别查询到  004、001、006三个课程
> 
>   2、有效课程数，就是学生的选修课程，使用count(\*)可以获得
> 
>   3、平均成绩为当前查询到的学生对象的 score 的avg（score）值
> 
>   4、使用 as 为每一个返回的参数赋予 别名   ： t.Sid as 学生ID
> 
>   5、主表查询， from sc as t ，t 为主表别名，使用 t.Sid 即学生号作为分组函数的关键；使用 avg(t.score) 获取学生总课程平均分，并使用 order by 进行排序，降序为 desc
> 
>  6、显示学生的数据库、企业管理、英语 成绩 需将 t.Sid 和 临时表 sc 中的 sc.Sid 进行关联， 即 sc.Sid = t.Sid

\--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT
    	l.Cid AS 课程ID,
    	l.score AS 最高分,
    	r.score AS 最低分
    FROM
    	sc l,
    	sc AS r
    WHERE
    	l.Cid = r.Cid
    AND l.score = (
    	SELECT
    		max(il.score)
    	FROM
    		sc AS il,
    		student AS im
    	WHERE
    		l.Cid = il.Cid
    	AND im.Sid = il.Sid
    	GROUP BY
    		il.Cid
    )
    AND r.score = (
    	SELECT
    		min(ir.score)
    	FROM
    		sc AS ir
    	WHERE
    		r.Cid = ir.Cid
    	GROUP BY
    		ir.Cid
    )
    GROUP BY
    	l.Cid

\--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数??)

SELECT
    	sc.Cid AS 课程id,
    	FORMAT(avg(sc.score), 2) AS 平均成绩,
    	CONCAT(
    		FORMAT(
    			100 * SUM(
    				CASE
    				WHEN sc.score >= 60 THEN
    					1
    				ELSE
    					0
    				END
    			) / COUNT(*),
    			2
    		),
    		'%'
    	) AS 及格率
    FROM
    	sc
    GROUP BY
    	sc.Cid
    ORDER BY
    	AVG(sc.score),
    	100 * SUM(
    		CASE
    		WHEN sc.score >= 60 THEN
    			1
    		ELSE
    			0
    		END
    	) / COUNT(*) DESC

\--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

select sum(sc2.企业管理)/count(sc2.企业管理) as 企业管理平均分, CONCAT(FORMAT(100*SUM(case when sc2.企业管理>=60 then 1 else 0 end)/COUNT(*),2),'%') as 企业管理及格率,
    	sum(sc2.马克思)/count(sc2.马克思) as 马克思平均分, CONCAT(FORMAT(100*SUM(case when sc2.马克思>=60 then 1 else 0 end)/COUNT(*),2),'%') as 马克思及格率,
    	sum(sc2.UML)/count(sc2.UML) as UML平均分, CONCAT(FORMAT(100*SUM(case when sc2.UML>=60 then 1 else 0 end)/COUNT(*),2),'%') as UML及格率,
    	sum(sc2.数据库)/count(sc2.数据库) as 数据库平均分, CONCAT(FORMAT(100*SUM(case when sc2.数据库>=60 then 1 else 0 end)/COUNT(*),2),'%') as 数据库及格率
    from (
        select 
    	(case when sc.Cid=001 then sc.score else null end) as 企业管理,
    	(case when sc.Cid=002 then sc.score else null end) as 马克思,
    	(case when sc.Cid=003 then sc.score else null end) as UML,
    	(case when sc.Cid=004 then sc.score else null end) as 数据库
        from sc
    ) sc2;

\--21、查询不同老师所教不同课程平均分从高到低显示

SELECT
    	max(t.Tid) AS 教师id,
    	max(t.Tname) AS 教师名字,
    	c.Cid AS 课程id,
    	MAX(c.Cname) AS 课程名称,
    	AVG(s.score) AS 平均成绩
    FROM
    	sc AS s,
    	course AS c,
    	teacher AS t
    WHERE
    	t.Tid = c.Tid
    OR c.Cid = s.Cid -- 根据老师id找到课程id
    GROUP BY
    	c.Cid -- 根据课程id分组
    ORDER BY
    	AVG(score) DESC -- 根据平均分排序

\--22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004） 格式：\[学生ID\],\[学生姓名\],企业管理,马克思,UML,数据库,平均成绩

\--23、统计列印各科成绩,各分数段人数:课程ID,课程名称,\[100-85\],\[85-70\],\[70-60\],\[ <60\]

\--24、查询学生平均成绩及其名次

\--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

\--26、查询每门课程被选修的学生数

\--27、查询出只选修了一门课程的全部学生的学号和姓名

\--28、查询男生、女生人数

\--29、查询姓“张”的学生名单

\--30、查询同名同性学生名单，并统计同名人数

\--31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

\--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

\--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩