1. 表的增删查改

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete(删除)

1.1 Create

insert into `tb_name` (column, ...) values
	(value_list),
	(value_list),
	...;

多行插入

insert into `tb_name` (column, ...) values
	(`data1`, `data2`, `data3`),
	(`data1`, `data2`, `data3`);

全列插入

insert into `tb_name` values (`data1`, `data2`, `data3`);

指定列插入

insert into `tb_name` (`field1`, `field2`) values (`data1`, `data2`);

插入否则更新

主键或者唯一键具有唯一性，可能会导致我们后续插入数据失败的问题。插入否则更新即插入成功就插入，如果失败就是更新。

insert into `tb_name` (`field1`, `field2`, ...) values (`data1`, `data2`, ...)
	on duplicate key update `field1`=`data1`, `field2`=`data2`, ...;

替换

替换就是不冲突就插入，冲突就替换，可能替换多行。

replace into `tb_name` (`field1`, `field2`, ...) values (`data1`, `data2`, ...);

1.2 Retrieve

select
	[distinct]
	*
	[from `tb_name`]
	[where ...]
	[order by colum [asc | desc], ...]
	limit ...

全列查询

select * from `tb_name`;

指定列查询

select `field1`, `field2`,... from `tb_name`;

添加表达式查询

select `field1`, `field2`, `expression` as `alias_name` from `tb_name`;

为字段起别名

select `field` as `alias_name` from `tb_name`;

结果去重

是否重复是对整条记录而言。

select distinct `field1`, `field2`, ... from `tb_name`;

结果排序

select `field1`, `field2` [, ...] from `tb_name` order by `field` asc;  # 升序
select `field1`, `field2` [, ...] from `tb_name` order by `field` desc; # 降序

没有排序的查询其返回的顺序是未定义的。NULL视为比任何值都小的值。

where子句

比较运算符	解释
> , >= , < , <=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如 NULL = NULL 的结果是 false(0)
<=>	等于，NULL安全，例如 NULL <=> NULL 的结果是 true(1)
!= , <>	不等于
between a0 and a1	范围匹配，满足 [a0, a1] 区间，返回 TRUE(1)
in (option …)	如果是 option 中的任意一个，返回 TRUE(1)
is null	是NULL
is not null	不是NULL
like	模糊匹配。% 表示任意(>=0)个字符， _ 表示任意一个字符
逻辑运算符	解释
and	与，多个条件必须都为 true(1)，结果才是 true(1)
or	或，任意一个条件为 true(1)，结果为 true(1)
not	非，条件为 true(1)，结果为 false(0)

select `field` from `tb_name` where `field` between `data1` and `data2`;

select `field` from `tb_name` where `field` in (`data1`,`data2`);

select `field` from `tb_name` where `field` like 'data%'; # 模糊匹配%多个
select `field` from `tb_name` where `field` like 'data_'; # 模糊匹配_单个

分页查询

# 筛选下标从0开始
select `field` [, ...] from `tb_name` limit `n`; # 从0开始筛选n条记录
select `field` [, ...] from `tb_name` limit `s`, `n`; # 从s开始筛选n条记录
select `field` [, ...] from `tb_name` limit `n` offset `s`; # 从s开始筛选n条记录

1.3 Update

update是对查询到的结果进行列值更新。

update `tb_name` set column = expr, column = expr, ...
	[where ...]
	[order by ...]
	[limit ...]

1.4 Delete

delete from `tb_name`
	[where ...]
	[order by ...]
	[limit ...]

截断表

truncate [table] `tb_name`;

目前来看，delete只会将表内容清除，不会清空表中字段的自增记录，而truncate会。

1.5 插入查询结果

insert into `tb_bak` select * from `tb_name`; # 插入查询结果

通常可以用来对表去重。

# 1. 建立备份表
create table duplicate_table_bak like duplicate_table;
# 2. 将去重结果导入备份表
insert into duplicate_table_bak  select distinct * from duplicate_table;
# 3. 删除表
rename table duplicate_table to old_duplicate_table;
# 4. 转正备份表
rename table duplicate_table_bak to duplicate_table;

1.6 聚合函数

聚合函数的使用更多时关注数据纵向之间的关系。

函数	说明
COUNT ( [DISTINCT] expr )	返回查询到的数据的数量
SUM ( [DISTINCT] expr )	返回查询到的数据的总和，不是数字没有意义
AVG ( [DISTINCT] expr )	返回查询到的数据的平均值，不是数字没有意义
MAX ( [DISTINCTl expr )	返回查询到的数据的最大值，不是数字没有意义
MIN ( [DISTINCT] expr )	返回查询到的数据的最小值，不是数字没有意义

1.7 group by

select colum1, colum2, ... from table group by `column`, ...;

分组的目的是便于对其进行聚合统计。group by首先要对全部数据进行分组，分完组后再针对每组数据执行相同的操作。

1.8 having

having用来对聚合后的数据进行筛选。

select `colum1`, ... from table group by `column` having `condition`;

where子句几乎是最早被执行，不可能去筛选group by分组之后的数据。

1.9 OJ练习

• https://www.nowcoder.com/practice/51c12cea6a97468da149c04b7ecf362e

• https://www.nowcoder.com/practice/ae51e6d057c94f6d891735a48d1c2397

• https://www.nowcoder.com/practice/218ae58dfdcd4af195fff264e062138f

• https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c

• https://www.nowcoder.com/practice/6d4a4cff1d58495182f536c548fee1ae

• https://www.nowcoder.com/practice/4c8b4a10ca5b44189e411107e1d8bec1

• https://www.nowcoder.com/practice/72ca694734294dc78f513e147da7821e

• https://leetcode.com/problems/duplicate-emails/description/

• https://leetcode-cn.com/problems/big-countries/description/

• https://leetcode.com/problems/nth-highest-salary/description/

 

2. 内置函数

内置函数一般放在SQL语句里帮助我们执行一些逻辑。

2.1 日期函数

函数名称	描述
current_date()	获取当前日期
current_time()	获取当前时间
current_timestamp()	获取当前时间戳
date(datetime)	返回datetime的日期部分
time(datetime)	返回datetime的时间部分
date_add (date, interval x day)	在date中添加日期或时间，单位可为年月日时分秒
date_sub (date, interval x day)	在date中减去日期或时间，单位可为年月日时分秒
datediff (date2, date1)	两个日期的差，单位是天
now()	当前日期时间

2.2 字符串函数

字符串函数	说明
charset ( str )	获取字符串字符集
concat ( string1, ...)	拼接字符串
length ( string )	返回字符串的字节数
replace ( str, search_str, replace_str )	将字符串中的 replace_str 替换 search_str
substring ( str, position [, length] )	从字符串的postion位置开始截取length个字符
ucase ( string )	转换成大写
lcase ( string )	转换成小写
instr ( string, substr )	返回substr在string中出现的位置，没有返回0
left ( string, length )	从string中的左边起截取length个字符
strcmp ( string1.string2 )	逐字符比较两字符串大小
ltrim ( string ) rtrim ( string ) trim ( string )	去除前或后或前后空格

2.3 数学函数

数学函数	描述
abs ( number )	绝对值函数
bin ( decimal number )	转换二进制
hex ( decimalNumber )	十六进制
Iconv ( number, from_base, to_base )	指定进制转换
ceiling ( number )	向上去整
floor ( number )	向下去整
format ( number, decimal_places )	格式化，保留小数位数
rand ()	返回随机浮点数，范围 [ 0.0, 1.0 )
mod ( number, denominator )	取模

2.4 其他函数

其他函数	描述
user ()	返回当前用户
md5 ( string )	对字符串进行md5摘要
database ()	返回当前数据库
password ( string )	对用户加密
ifnull ( string1, string2 )	如果string1不为null则返回string1，否则返回string2