作者简介:☕️大家好,我是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