数据库常用的函数
select CONCAT(id,"|",phone,"|",nickname),IFNULL(update_by,0) from t_user;#CONCAT:把表记录输出为一行,IFNULL:字段为空时,9表示
select * from t_role ORDER BY company_id ASC,id DESC;#安装company_id升序,当company_id一样时,按照id降序
SELECT course_no,MAX( stu_score) FROM t_score
GROUP BY course_no HAVING MAX( stu_score) >= 125; #查询各科的最高成绩《不低于125分)
SELECT stu_no, SUM(stu_score) FROM t_score
GROUP BY stu_no ORDER BY SUM(stu_score) DESCLIMIT 0,3; #查询显示前三名总成绩
1、
SELECT CLASS,SEX,AVG(SCORE) FROM test_avg WHERE SCORE > 80 GROUP BY SEX,CLASS;
2.1两种写法
select c.* from class a left join student_class b on a.c_id=b.s_id
left join student c on b.s_id_id=c.s_id
where a.c_name = '一班' and c.score>80 or c.score in (60,61,62)
select s_id,name,score,c_name from student where score>80 or score in(60,61,62) and s_id in(select a.s_id from student_class a,class b where a.c_id=b.c_id and a.c_name='一班');
2.2
select c_name,count(c_id),avg(score) from class a
left join student_class b on a.c_id=b.s_id
left join student c on b.s_id_id=c.s_id
where c.sex='女' group by c.name
1、
select a.order_no,a.create_by,b.goods_id,b.goods_count from
jlc_order_item a , jlc_order_detail b
where a.uuid=b.uuid and b.goods_id=9527
2.
select order_type,count(*) order_count from jlc_order_item
where price>500 group by order_type