select name,score,score*2as double_score from student
条件查询–where
select name,score from student where name='鱼皮'
条件查询–运算符
select name,age from student where name !='热dog'
条件查询–空值
select name,age,score from student where age isnotnull
条件查询–模糊查询
select name,score from student where name notlike'%李%'
条件查询–逻辑运算
select name,score from student where name like'%李%'or score>500
去重
selectdistinct class_id,exam_num from student
排序
select name,age,score from student orderby score desc, age asc
截断和便宜
select name,age from student orderby age limit1,3
条件分支
select name,casewhen(age>60)then'老同学'when(age>20)then'年轻'else'小同学'endas age_level from student orderby name
时间函数
select name,date()as 当前日期 from student
字符串处理
select id,name,upper(name)as upper_name from student where name='热dog'
聚合函数
selectsum(score)as total_score,avg(score)as avg_score,max(score)as max_score,min(score)as min_score from student
单字段分组
select class_id,avg(score)as avg_score from student groupby class_id
多字段分组
select class_id,exam_num,count('*')as total_num from student groupby class_id,exam_num
having子句
select class_id,sum(score)as total_score from student groupby class_id having total_score>150
关联查询–cross join
select s.name as student_name, s.age as student_age, c.id as class_id, c.name as class_name from student as s crossjoin class as c
关联查询–inner join
select s.name as student_name,s.age as student_age,c.id as class_id,c.name as class_name,c.levelas class_level from student as s innerjoin class as c on s.class_id = c.id
关联查询–outer join
select s.name as student_name,s.age as student_age,c.id as class_id,c.name as class_name,c.levelas class_level from student as s leftjoin class as c on s.class_id = c.id
子查询
select name,score,class_id from student where class_id in(selectdistinct id from class
)
子查询exists
select name,age,class_id from student wherenotexists(select1from class where student.class_id=class.id
)
聚合查询
select name,age,score,class_id from student
unionallselect name,age,score,class_id from student_new
