SQL面试题总结

数据库常用的函数

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