mySQL(12)-单表查询

  mysql下单表查询、更新的一般用法，总结MARK了一下。真正到工程中就把字段改改，套路是一样的。

有个常犯的错误要特别注意：
mysql是不允许一边update 一边select 的。详见第5题。

-- 1.查询姓“朱”的学生名单
-- 单表查询+返回值(原始字段)
-- 单表：student；返回值：名字(snmae 原始字段)
-- note：_'单一字符'  '%'任意字符 
    select * from student where sname like "朱%"
		
-- 2.查询同名同性学生名单，并统计同名人数
-- 单表查询+返回值(原始字段+原始字段+计算值)
--  单表查询 : student；返回值：名字(sname 原始字段)，性别（ssex 原始字段），人数(计算值)，
--  note： 分组：同名同姓： 人数 group by s.sname,s.ssex having count(*)>=2
--         去重：count(*)>=2
	 
   select sname ,ssex,count(*) from student  group by sname ,ssex having count(*)>=2
		
-- 3.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
-- 单表查询+返回值(原始字段+计算值)
-- 单表查询：sc；返回值：课程号(sc.cid原始字段) 平均成绩（avg(score)计算值)
-- 每门课平均成绩    分组 avg(score) group by cid
--                  重命令 avg(score) as score
-- 一个查询两种排序  升序 order by asc 降序 cid desc  
-- 
 select sc.cid, avg(sc.score) as score from sc group by sc.cid order by score, sc.cid

-- 4.按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 单表查询+返回值(原始字段，计算字段)
-- 单表查询： sc ； 返回值: id,avg(score),(及格率 计算值)
-- note: 及格率：当前科目及格人数/当前科目总人数
--        group by 两次聚合（当前科目-所有科目） 

 -- 当前科目
 -- select count(*)from sc sc2 where  sc2.cid=sc.cid and sc2.core>=60)/count(*) from sc group by sc.cid
 -- 如果没有sc2写成 所有科目
 -- (select count(*) from sc  where  sc.core>=60)/count(*) from sc group by    sc.cid查询的是所有科目，而不是当前科目	
    
   select sc.cid ,avg(sc.score)，                                   -- 结果是id 和平均成绩
   (select count(*) from sc sc2 where sc2.cid=sc.cid and sc2.core>=60) /
	 count(*)  from sc group by sc.cid                              -- 及格率
   
 -- 5.把“sc”表中“黄观”老师教的课的成绩都更改为此课程的平均成绩
 -- 单表修改
 -- 错误的解题思路：查询，修改 不允许的。
 --   update sc set score=(select avg(score) from sc)

 -- 1.查找老师教的课 返回的是 课程cid c.cid 
   -- select  c.cid from course c inner join teacher t on c.tid=t.tid
                               -- where t.tname="黄观"
 -- 2.改为平均成绩
   -- update sc,(select sc.cid as cid,avg(sc.score) as score from sc group by sc.cid) t     --把（）里查询的结果起个别名t 
 update sc,  
(select cid,avg(score) score from sc group by sc.cid) t -- 课程ID,平均成绩
              set sc.score=t.score                -- 分数相同 修改的是sc 表的score ,数据来源是t表的score
              where sc.cid =t.cid                 -- sc表和t表是要关联的
			  and sc.cid in                       -- 黄观老师教的课
 (select c.cid from course c inner join  teacher t on c.tid=t.tid where t.tname='黄观') 
 
 
-- 6.查询平均成绩大于60分的同学的学号和平均成绩
-- 单表查询+返回值(原始字段，计算字段）
-- 单表操作  sc ； 返回的是  列值和计算值  sc.cid ,avg(sc.core)
-- note:  筛选：学号分组  group by sc.sid
--              平均分    avg(sc.score)>60
--        先分组再计算，所以用having 不用where
	select sc.sid,avg(sc.score) from sc group by sc.sid having avg(sc.score)>60

 

备注：数据表和题目是网上找来，注释和总结是我写的，希望对大家写sql时有所启发与帮助。