1. 基础语句
运行多端代码时加分号
1.1 select语句
1. 基础语法
select 字段名 from 表名
2. 别名语法
select 字段名 as 别名 from 表名 显示结果时候用别名代替字段名
注意:as可以省略 可用空格代替
3. 查询多列
select 字段名1,字段名2,字段名3 from 表名
4. 查询所有列
select * from 表名
5. 数据去重
select distinct 字段名 from 表名
注意:distinct本质是加在select后而不是字段前,因此放在后面的字段前面会报错。
6. select中的计算字段
select 字段名,计算字段 from 表名称
e.g:select name,gdp,population,gdp/population 人均GDP from world
注意:计算字段中的算式所涉及的字段必须是表格中包含的,或者算式本身可以独立运算
1.2 where语句
1.2.1 标准语法
select 字段名
from 表名
where 表达式
注意:文本需要英文单引号包裹,数字不需要
1.2.2 like模糊查询
_:匹配任意一个字符;
1 |
|
%:匹配0个或多个字符;
1 |
|
[ ]:匹配[ ]中的任意一个字符(若要比较的字符是连续的,则可以用连字符“-”表 达 );
1 |
|
[^ ]:不匹配[ ]中的任意一个字符。
1 |
|
1.2.3运算符
and、or和not为逻辑运算符
and :一般用于组合两个及以上表达式,要求同时满足多个查询条件
or:一般用于组合两个及以上表达式,要求满足多个查询条件中的一个
not:not一般和与其他连用例如not in,用于条件取反
and和not可以一起使用,但是默认and优先级高于or,可以使用括号来界定优先级
between and,用于查询两个值之间范围的值(包含这两个值)
当不想取得边界值的时候,可以跟and xxx != 边界值来实现 !=表示不等于
in,用于查询指定条件范围内的数据,一般为in (xxx,xxx,......),用括号将条件括起来
is null,用于查询空值(NULL),空值不同于0,也不同于null字符串
1.3 order by语句
1.3.1标准语法
select 字段名
from 表名
[where 表达式]
[order by 字段名 asc|desc]
asc指定该字段升序排序,desc为降序排序,不写则默认为升序排序
e.g:查询1984年所有获奖者的姓名和奖项科目。结果将诺贝尔化学奖和物理学奖排在最后,然后按照科目排序,再按照获奖者姓名排序
order by subject in ('chemistry','physics') , subject, winner
使用in后将其在排序中视为1,其他为0
1.4 limit语句
1.4.1标准语法
limit [位置偏移量,]行数
limit子句是可选项,行数是子句中的必选参数,参数位置偏移量是可选参数
limit通常与order by组合限制查询
limit x,n意味从x+1行开始返回n行
e.g: limit 3,4
从向下偏移3个开始取4个值
1.5 聚合函数&Group by
1.5.1 聚合函数
注意:聚合函数会忽略空值
1.5.2 group by
group by核心子句是可选项,使用该子句是为了依据相同字段值分组后进行聚合运算,常和聚合函数联用
1.6 having表达式
having 表达式 限定分组聚合后的查询行必须满足的条件
having核心子句是可选项,使用该子句是为了对group by分组后的数据进行筛选
1.7 sql运行原理
from--where--group by--having--order by--limit--select
1.8 常见函数
1.8.1 数学函数
round(x,y)——四舍五入函数
round函数对x值进行四舍五入,精确到小数点后y位
y为负值时,保留小数点左边相应的位数为0,不进行四舍五入
例如:round(3.15,1)返回3.2,round(14.15,-1)返回10
1.8.2 字符串函数
- concat(s1,s2,...)——连接字符串函数
- 任一参数为null时,则返回null
- 例如:concat('My',' ','SQL')返回My SQL,concat('My',null,'SQL')返回null
- replace(s,s1,s2)——替换函数
- replace函数使用字符串s2代替s中所有的s1
- 例如:replace('MySQLMySQL','SQL','sql')返回MysqlMysql
- left(s,n)——从左截取字符串一部分的函数
- right(s,n)——从右截取字符串一部分的函数
- left函数返回字符串s最左边n个字符
- right函数返回字符串s最右边n个字符
- substring(s,n,len)——从指定位置截取字符串一部分的函数
- 返回字符串s从第n个字符起取长度为len的子字符串,n也可以为负值,则从倒数第n个字符起取长度为len的子字符串,没有len值则取从第n个字符起到最后一位
- 例如:left('abcdefg',3)返回abc,right('abcdefg',3)返回efg,substring('abcdefg',2,3)返回bcd,substring('abcdefg',-2,3)返回fg,substring('abcdefg',2)返回bcdefg
1.8.3 数据类型转换函数
cast(x as type)——转换数据类型的函数
type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型
1.8.4 日期时间函数
- year(date)——获取年的函数
- month(date)——获取月的函数
- day(date)——获取日的函数
- 例如:year('2021-08-03')返回2021,month('2021-08-03')返回8,day('2021-08-03')返回3
- date_add(date,interval expr type)——对指定起始时间进行加操作
- date_sub(date,interval expr type)——对指定起始时间进行减操作
- type指示expr被解释的方式,type可以可以是以下值
- 例如:date_add('2021-08-03 23:59:59',interval 1 second)返回2021-08-04 24:00:00,date_sub('2021-08-03 23:59:59',interval 2 month)返回2021-06-03 23:59:59
- datediff(date1,date2)——计算两个日期之间间隔的天数
- date_format(date,format)——将日期和时间格式化
1.8.5 条件判断函数
- if(expr,v1,v2)
- case when
- case expr when v1 then r1 [when v2 then r2] ...[else rn] end
- case when v1 then r1 [when v2 then r2]...[else rn] end
2. 高级语句
2.1 窗口函数
2.1.1 标准语法
窗口函数over([partition by 字段名] [order by 字段名 asc|desc])
over()中两个子句为可选项,partition by指定分区依据,order by指定排序依据
2.1.2 排序窗口函数
rank()over()
rank()over([partition by 字段名] order by 字段名 asc|desc)
dense_rank()over()
dense_rank()over([partition by 字段名] order by 字段名 asc|desc)
row_number()over()
row_number()over([partition by 字段名] order by 字段名 asc|desc)
2.1.3 偏移分析函数
lag(字段名,偏移量[,默认值])over()
lag(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
lead(字段名,偏移量[,默认值])over()
lead(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
2.2 表连接
2.2.1 基础语法
内连接
select 字段名
from 表名1 inner join 表名2 on 表名1.字段名 = 表名2.字段名
注意:内连接inner可以省略,直接使用join默认为内连接
左连接
select 字段名
from 表名1 left join 表名2 on 表名1.字段名 = 表名2.字段名
右连接
select 字段名
from 表名1 right join 表名2 on 表名1.字段名 = 表名2.字段名
2.3 子查询
子查询本身就是一段完整的查询语句,然后用括号英文括号()包裹嵌套在主查询语句中,子查询可以多层嵌套
最常用的子查询运用在from和where子句中
子查询返回超过1行会报错
3.拓展
sp_help相关
sp_helpindex:查看表中的索引信息,
sp_help:查看有关数据库对象的摘要信息,
sp_helpdb:查看指定数据库或全部数据库信息,
sp_helptext:查看存储过程、视图、触发器等文本信息
stuff函数
STUFF(原字符, 开始位置, 删除长度, 插入字符)
查询语句select stuff('lo ina',3, 1, 've ch')结果为?
love china 从第3个位置开始删除1单位长度,插入后面的字符
大小写转化函数
lower(), upper()
lcase(), ucase()
coalesce函数
coalesce()
返回第一个非空值,如果都是空,返回空值
avg()
适用于求xx率
timestampdiff()
TIMESTAMPDIFF(unit, start_datetime, end_datetime)
unit
:计算差异的单位。可以是以下值之一:MICROSECOND
:微秒SECOND
:秒MINUTE
:分钟HOUR
:小时DAY
:天数WEEK
:周数MONTH
:月数QUARTER
:季度数YEAR
:年数
start_datetime
:开始日期或时间。end_datetime
:结束日期或时间。
date_format()
DATE_FORMAT()函数用于将日期和时间格式化为指定的字符串格式。
DATE_FORMAT(date, format)
-
date
:要格式化的日期或时间。 -
format
:要将日期或时间格式化成的字符串格式。格式可以是以下之一:格式符号 含义 %Y 四位数的年份 %m 两位数的月份 %d 两位数的日期 %H 24小时格式的小时数 %h 12小时格式的小时数 %i 两位数的分钟数 %s 两位数的秒数 %p AM或PM %W 星期的完整名称 %M 月份的完整名称 %D 带有英文后缀的日期 %T 24小时格式的时间(HH:MI:SS)
ROUND()函数
ROUND()函数用于将数值四舍五入到指定的小数位数。
ROUND(number, decimals)
number
:要进行四舍五入的数值。decimals
:保留的小数位数。如果省略此参数,则默认为0。
排除空值
过滤空值的三种方法:
(1) Where 列名 is not null
(2) Where 列名 != 'null'
(3) Where 列名 <> 'null'
在实际工作中,空字符串也会导致空值,所以在常规操作中
1 |
|