常见SQL整理

基础语法

查询

全表查询

select * from student

选择查询

select name,age from student

别名

select name as 学生姓名,age as 学生年龄 from student

常量和运算

select name,score,score*2 as 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 is not null

条件查询–模糊查询

select name,score from student where name not like '%李%'

条件查询–逻辑运算

select name,score from student where name like '%李%' or score>500

去重

select distinct class_id,exam_num from student

排序

select name,age,score from student order by score desc, age asc

截断和便宜

select name,age from student order by age limit 1,3

条件分支

select name, case when (age>60) then '老同学' when (age>20) then '年轻' else '小同学' end as age_level from student order by name

时间函数

select name, date() as 当前日期 from student

字符串处理

select id,name,upper(name) as upper_name from student where name='热dog'

聚合函数

select sum(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 group by class_id

多字段分组

select class_id,exam_num,count('*') as total_num from student group by class_id,exam_num

having子句

select class_id,sum(score) as total_score from student group by 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 cross join 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.level as class_level from student as s inner join 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.level as class_level from student as s left join class as c on s.class_id = c.id

子查询

select name,score,class_id from student where class_id in (
    select distinct id from class
)

子查询exists

select name,age,class_id from student where not exists (
    select 1 from class where student.class_id=class.id
)

聚合查询

select name,age,score,class_id from student
union all
select name,age,score,class_id from student_new