CRZMASTER的SQL学习_char(n)、date、time、datetime、decimal等转换为对应的数据类型-CSDN博客

本文链接：https://blog.csdn.net/m0_63121064/article/details/135038705

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	`SELECT * FROM 学生表 WHERE name LIKE` `'张__'//查询姓“张”且名字是3个字的学生姓名。`

%：匹配0个或多个字符；

1	`SELECT * FROM 学生表 WHERE 姓名 LIKE ‘张%’//查询学生表中姓‘张’的学生的详细信息。`

[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达 )；

1	`SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%’//查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。`

[^ ]：不匹配[ ]中的任意一个字符。

1	`SELECT * FROM 学生表 WHERE 学号 LIKE` `'%[^235]'` `//从学生表表中查询学号的最后一位不是2、3、5的学生信息。`

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）