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
  • 29
    点赞
  • 28
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Intelligent_M

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值