数据库09mysql常用查询实例

SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

SELECT * FROM tb_stu WHERE month(date) = between 1 and 3 ORDER BY date ;

(1)NOT BERWEEN … AND … 对介于起始值和终止值间的数据时行查询 可改成 <起始值 AND >终止值
(2)IS NOT NULL 对非空值进行查询
(3)IS NULL 对空值进行查询
(4)NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外，指定表达式的搜索，搜索表达式可以是常量或列名，而列名可以是一组常量，但更多情况下是子查询

SELECT *，SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice  FROM tb_price GROUP BY pid ORDER BY sumprice DESC

select a.* from
 (select * from sc a where a.cno='c001') a,
 (select * from sc b where b.cno='c002') b
 where a.sno=b.sno and a.score > b.score;

select sno,avg(score) from sc  group by sno having avg(score)>60;

select * from student st where st.sno not in
(
 select distinct sno from sc s
 join course c on s.cno=c.cno
 join teacher t on c.tno=t.tno where tname='谌燕'
)

select stu.sno,stu.sname,count(sc.cno) from student stu
 left join sc on stu.sno=sc.sno
 group by stu.sno,stu.sname
 having count(sc.cno)<(select count(distinct cno)from course)

select cno,avg(score),sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率
from sc group by cno
order by avg(score) , 及格率 desc

select * from
(select sno,cno,score,row_number()over(partition by cno order by score desc) rn from sc)
where rn<4

SELECT t.stuid, 
        t.stuname, 
        t.score, 
        t.classid 
FROM stugrade t 
where t.score = (SELECT max(tmp.score) from stugrade tmp where tmp.classid=t.classid)

语句：select stuId,avg(ifnull(stuScore,0)) from score where stuId in (select stuId from score where stuScore <60 group by stuId having count(*) >2) group by stuId;