Sql常用语法大全

作者简介：☕️大家好，我是intelligent_M，一个Java后端开发者！
当前专栏：intelligent_M—— MySql ，CSDN博客。

后续会更新Java相关技术栈。
创作不易 欢迎点赞评论!!!

SQL语句

基础语法 - 查询

该篇记录了大部分常用和一些不常用的sql语法
你可以读下列sql语句 检查自己能否读懂

• 全表查询 select * from student

select * from student

• 选择查询 select name , age from student

select name , age from student

• 别名 select name as 学生姓名, age as 学生年龄 from student （as可省略）

select name as 学生姓名, age as 学生年龄 from student （as可省略）

• 常量和运算 select name, score, score*2 as double_score from student

select name, score, score*2 as double_score from student

基础语法 - 条件查询

• where查询 select name, score from student where name = ‘鱼皮’

select name, score from student where name = '鱼皮'

• 运算符 select name,age from student where name != ‘热dog’

 select name,age from student where name != '热dog'

• 空值 select name,age,score from student where age is not null

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 not like '%李%'

• 逻辑运算 select name , score from student where name like ‘%李%’ or score > 500

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

基础语法

• 去重 select distinct class_id , exam_num from student

select distinct class_id , exam_num from student

• 排序 select name, age, score from student order by score desc , age asc

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

• 截断和偏移 select name, age from student order by age asc limit 1 ,3

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

• 条件分支 select name, case when(age > 60) then ‘老同学’ when(age > 20) then ‘年轻’ else ‘小同学’ end as age_level from student order by name asc

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

函数

• 时间函数 select name ,date() 当前日期 from student

select name ,date()  from student 

• 字符串处理 select id, name, upper(name) upper_name from student where name = ‘热dog’

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

• 聚合函数 select sum(score) total_score, avg(score) avg_score,max(score) max_score, min(score) min_score from student

select sum(score) total_score, avg(score) avg_score,max(score) max_score, min(score) min_score from student

分组聚合

• 单字段分组 select class_id, avg(score) avg_score from student group by class_id

 select class_id, avg(score) avg_score from student group by class_id

• 多字段分组 select class_id, exam_num, count(*) total_num from student group by class_id, exam_num

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

• having 子句 select class_id, sum(score) total_score from student group by class_id having total_score >150

select class_id, sum(score) total_score from student group by class_id having total_score >150

查询进阶- 关联查询

• 关联查询- cross join ： select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name from student s cross join class c (cross join 可以省略)

select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name from student s cross join class c

• 关联查询- inner join ： select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s join class c on s.class_id = c.id

select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s join class c on s.class_id = c.id

• 关联查询- outer join ：select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s left join class c on s.class_id = c.id

select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s left join class c on s.class_id = c.id

查询进阶- 子查询

• 子查询 ：select name, score, class_id from student where class_id in (select distinct id from class)

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 class_id from class where class.id = student.class_id)

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

查询进阶- 组合查询

• 组合查询 ： select name , age, score, class_id from student union all select name, age, score, class_id from student_new

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

查询进阶- 开窗函数

• 开窗函数- sum over ：select id, name, age, score, class_id ,avg(score) over(partition by class_id) class_avg_score from student

select id, name, age, score, class_id ,avg(score) over(partition by class_id) class_avg_score from student

• 开窗函数- sum over order by ： select id, name, age, score, class_id, sum(score) over(partition by class_id order by score asc) class_sum_score from student

select id, name, age, score, class_id, sum(score) over(partition by class_id order by score asc) class_sum_score from student

• 开窗函数- rank ：select id, name, age, score, class_id, rank() over(partition by class_id order by score desc) as ranking from student

select id, name, age, score, class_id, rank() over(partition by class_id order by score desc) as ranking from student

• 开窗函数- row_number ：select id, name, age, score, class_id, row_number() over(partition by class_id order by score desc) as row_number from student

select id, name, age, score, class_id, row_number() over(partition by class_id order by score desc) as row_number from student

• 开窗函数- lag/lead ：select id, name, age, score, class_id, lag(name, 1,null) over(partition by class_id order by score desc) as prev_name, lead(name, 1 , null) over(partition by class_id order by score desc) as next_name from student

select id, name, age, score, class_id, lag(name, 1,null) over(partition by class_id order by score desc) as prev_name, lead(name, 1 , null) over(partition by class_id order by score desc) as next_name from student