Mysql笔记

微冷 2021/9/27 mysql

笔记不为详细，但求面全

SQL 指结构化查询语言
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

# 一、运算符和函数

# 1、字符函数

转义字符：escape

select * from table_name where title like '\_%'; #查询title中以下划线开头的，因为_是单个任意字符匹配，所以要转移
或者
select * from table_name where title like '$_%' escape '$'; #指定$为转移标识

1
2
3

!=, <>, <=>

# != 和 <> 等价都为不等于,但是有些版本不支持前者
# <=> 是安全等于,可判断null 即 <=> null 和 is null等价,而且<=>还可以跟非null的其他各种类型的数据

1
2

length：返回的是数据的字节数

select length('123') l;
# 3
select length('abc123') l;
# 6
select length('abc123你好') l;
# 12,因为utf8mb4下一个汉字三个字节
# 查看字符集
show variables like '%char%'

1
2
3
4
5
6
7
8

substr/substring

select substring('0123456789', '6') s1, substr('0123456789', '6') s2;
s1      s2
56789   56789
# 可以看出下表是从1开始计算的，这个可以从 instr来推敲一下：
select instr('0123456789', '6') s1, instr('0123456789', 'a') s2;
s1      s2
7       0
# instr返回的是第一次出现的位置，如果从0计算那，6的应该是6但确实7也就是从1开始计算的，而0就做了没有匹配时候的返回值

SELECT SUBSTRING('0123456789', -6) s1, SUBSTRING('0123456789', -6, 3) s2
s1        s2
456789    456
# 负数：从字符串的倒数第n个字符开始读取直至结束
# 第三个参数：截取几个字符

1
2
3
4
5
6
7
8
9
10
11
12
13
14

substring_index(str, delim, count)按关键字进行读取

# substring_index(被截取字符串，关键字，关键字出现的次数)
SELECT SUBSTRING_INDEX('www.weileng.top', '.', 2)
> www.weileng
# 从开始到第几次出现关键字  之前  的字符

SELECT SUBSTRING_INDEX('www.weileng.top', '.', -1)
> top
# 负数：表示从倒数第几次出现关键字  之后  的字符

# 如果关键字不存在则返回整个字符串

1
2
3
4
5
6
7
8
9
10

trim
- trim(str:varchar)
- trim([{both | leading | trailing }] [remstr:varchar] from str:varchar )

参数	说明
both	两边去掉，默认
leading	只去掉前边
trailing	只去掉后边
remstr	要去掉的字符串，默认为空格
from str	要处理的字符串

select trim(' abc     ');
> abc
select trim('a' from 'aaaaaa123aaaaaa');
> 123
select length(trim(leading from '     abc    '));
> 7
select trim(trailing 'a' from 'aaaaaa123aaaaaa');
> aaaaaa123

1
2
3
4
5
6
7
8

lpad(str:varchar, len:int, padstr: varchar)
- 用指定的字符padstr左填充到指定长度len,如果len小于str的长度，则变成从左侧开始截取，保留长度为len

select lpad('abc', 5, '12345');
> 12abc
select lpad('abcdef', 3, '12345');
> abc

1
2
3
4

rpad(str:varchar, len:int, padstr: varchar)
- 用指定的字符padstr右填充到指定长度len,如果len小于str的长度，则变成从左侧开始截取，保留长度为len

select rpad('abc', 5, '12345');
> abc12
select rpad('abcdef', 3, '12345');
> abc

1
2
3
4

CONCAT(str1,str2,...) 连接函数

select CONCAT('a','b','c')
> abc

select CONCAT(1, 2.3, 4)
> 12.34

SELECT CONCAT('My', NULL, 'QL');
> null

1
2
3
4
5
6
7
8

left(str, length)从左开始截取字符串

# left(被截取字符串， 截取长度)
SELECT LEFT('www.weileng.top', 3)
> www

1
2
3

right(str, length)从右开始截取字符串

# right(被截取字符串， 截取长度)
SELECT RIGHT('www.weileng.top', 3)
> top

1
2
3

# 2、数学函数

以下所有数学函数中都有类型说明，用错了类型后将导致不可预期的错误！！！

round(X:decimal) 四舍五入
round(X:decimal, D:int) 四舍五入，指定保留几位小数

select round(1.1234)
> 1

select round(1.994, 4)
> 1.9940

select round(1.994, 1)
> 2.0 

select round(-1.994, 1)
> -2.0

1
2
3
4
5
6
7
8
9
10
11

truncate(X:decimal, D:int) 截断

select truncate(1.11, 4)
> 1.1100

select truncate(1.12349999, 4)
> 1.1234

1
2
3
4
5

ceiling(X:decimal)/ceil(X:decimal) 向上取整，返回>=X的最小整数

select ceiling(100.0009)
> 101

select ceiling(100)
> 100

select ceil(99.0004)
> 100

1
2
3
4
5
6
7
8

floor(X:decimal) 向下取整，返回<=X的最大整数

select floor(100.0009)
> 100

select floor(100)
> 100

select floor(99.0004)
> 99

1
2
3
4
5
6
7
8

mod(M:int, N:int) 取模/取余同%计算，计算规则：M-M/N*N

select mod(10,3)
> 1

1
2

# 3、日期函数：

now() 当前日期+时间

select now();
> 2021-03-31 10:47:22

1
2

curdate() 当前日期没有时间

select curdate();
> 2021-03-31

1
2

curtime() 当前时间没有日期

select curtime();
> 10:47:22

1
2

str_to_date(str:varchar, format:varchar) 字符串按照指定格式化转换成时间

参数	说明
%Y	四位的年份
%y	两位的年份
%m	两位的月份，01，02，，，
%c	月份，1，2，，，10，11
%d	日，01，02
%e	日，1，2
%H	24小时制，时
%h	12小时制，时
%I	12小时制，时
%i	分钟，00，，，59
%s	秒，00，，，59
%f	豪秒，000000..999999

::: details不常用参数说明

参数	说明
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%%	A literal % character
%x	x, for any “x” not listed above

:::

select str_to_date('2022-02-22', '%Y-%m-%d');

DATE_FORMAT(date:date, format:varchar) 时间按照指定格式返回
- format格式同上

select date_format(now(), '%Y年%m月%d日')
> 2021年03月31日

SELECT DATE_FORMAT(now(), '%W %M %Y');
> Wednesday March 2021

1
2
3
4
5

DATE(expr:datetime)：提取日期或日期时间表达式的日期部分,
- 返回值是DATE类型

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

1
2

DATEDIFF(expr1, expr2): expr1和expr2之间的时间差,在计算中仅使用值的日期部分。

select now(),  DATEDIFF(now(), '2020-01-01');
> 2021-03-31 14:04:52     455

1
2

DATE_ADD(date,INTERVAL expr unit)/ DATE_SUB(date,INTERVAL expr unit) 日期加法/日期减法，返回值还是一个日期
- date：要操作的日期
- expr：间隔值，可以是负数
- unit：单位
官网就返回值做了特别说明：
- The return value depends on the arguments:
- DATE if the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts).
- DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS.
- String otherwise.
- To ensure that the result is DATETIME, you can use CAST() to convert the first argument to DATETIME.
个人翻译如下：
- 如果是date（第一个参数）是DATE格式，计算就只涉及日期部分YEAR, MONTH, and DAY，不涉及时间部分，返回值是DATE；
- 如果date（第一个参数）是DATETIME(or TIMESTAMP) 格式，或者是DATE格式但是unit参数部分用的是HOURS, MINUTES, or SECONDS，返回值是DATETIME
- 其他情况返回字符串
- 为了保证返回结果是DATETIME，可以使用CASR()把第一个参数date进行强制类型转换

unit值	expr表达式
MICROSECOND	MICROSECONDS
SECOND	SECOND
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
> 2018-05-02  #string
SELECT DATE_ADD(DATE('2018-05-01'),INTERVAL 1 DAY);
> 2018-05-02  #DATE 

SELECT DATE_ADD('2020-12-31 23:59:59', INTERVAL 1 SECOND);
> 2021-01-01 00:00:00

SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
> 2101-01-01 00:01:00

SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
> 2024-12-30 22:58:59

1
2
3
4
5
6
7
8
9
10
11
12
13

以上可以date + INTERVAL expr unit 或者 date - INTERVAL expr unit进行查询计算
- + 的有两中方式
- - 的只能放在右边

SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
> '2019-01-01 00:00:00'

SELECT INTERVAL 1 DAY + '2018-12-31';
> '2019-01-01'

SELECT '2025-01-01' - INTERVAL 1 SECOND;
> '2024-12-31 23:59:59'

1
2
3
4
5
6
7
8

其他时间函数 (opens new window) 常用的时间函数个人感觉上面的差不多了，如果想知道的更多可自行查看官方文档

# 4、条件函数等

if(expr1:any, expr2:any, expr3:any)

select if(1=2, '1==2', '1!=2')
> 1!=2

1
2

case when
- 格式1：类比java中swich

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或者语句n
end

例子
select case 10 % 3
           when 1 then '10%3=1'
           when 2 then '10%3=2'
           end result
> 10%3=1

1
2
3
4
5
6
7
8
9
10
11
12
13

格式2：类比java中多重if

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值1或语句2
...
else 要显示的值n或者语句n
end

1
2
3
4
5
6

# 二、常见低级错误

# 1、逗号问题

select columnA columnB from mytable;

因为as关键字是可选的，如上语句，如果没有写逗号，原本想查询两列结果第二列成了第一列的别名；

# 2、隐式转换

前提：有表mytabl字段bpn为varchar(20)且有索引。

select * from mytable b where b.bpn = 123456789;

以上语句会执行成功，但索引会失效，因为mysql的策略是将字符串转换为数字后再比较，函数作用于表字段，索引失效。所以在写sql时在已经确定字段类型的情况下要按照字段类型写参数。

# 3、mysql中的更新表问题

UPDATE del_user du 
SET du.create_time = now( ) 
WHERE
	du.id IN ( SELECT id FROM del_user b WHERE b.email is null )

1
2
3
4

上面的语句如果实在oracle数据库中是没有问题的，可以直接执行，如果是mysql中就会报错；如果去网上查询就会说再嵌套一层查询，问解决了，新的问题也来了，如果数据量特别大呢？

UPDATE del_user du 
SET du.create_time = now( ) 
WHERE
	du.id IN ( select id from (SELECT id FROM del_user b WHERE b.email is null) t )

1
2
3
4

# 三、常用命令

mysql -u root -p

USE 数据库名;

SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM 数据表;

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G;   # 加上 \G，查询结果按列打印

1
2
3
4
5
6
7
8
9
10
11

# 四、MySQL 索引

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

索引类型

普通索引 INDEX
唯一索引 UNIQUE INDEX，列(可多列)中的值必须唯一，允许null值，也允许null多次
主键 PRIMARY KEY，主键索引值必须是唯一的，且不能为NULL
FULLTEXT，全文索引

索引常见语句

CREATE INDEX indexName ON table_name (column_name);

DROP INDEX [indexName] ON mytable; 

ALTER table tableName ADD INDEX indexName(columnName);

SHOW INDEX FROM table_name \G; # 加上 \G，查询结果按列打印

1
2
3
4
5
6
7

# 五、数据库用户

注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
注意：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
注意：在注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

# 六、数据库信息查看

查看该数据库实例下所有库大小，得到的结果是以MB为单位

select table_schema,
       sum(data_length) / 1024 / 1024 as data_length,
       sum(index_length) / 1024 / 1024 as index_length,
       sum(data_length+index_length)/1024/1024 as sum
from information_schema.tables

1
2
3
4
5

查看该实例下各个库大小

select table_schema,
       sum(data_length + index_length) / 1024 / 1024 as total_mb,
       sum(data_length) / 1024 / 1024                as data_mb,
       sum(index_length) / 1024 / 1024               as index_mb,
       count(*)                                      as tables,
       curdate()                                     as today
from information_schema.tables
group by table_schema
order by 2 desc;

1
2
3
4
5
6
7
8
9

查看单个库的大小

select concat(truncate(sum(data_length) / 1024 / 1024, 2), 'mb')     as data_size,
       concat(truncate(sum(max_data_length) / 1024 / 1024, 2), 'mb') as max_data_size,
       concat(truncate(sum(data_free) / 1024 / 1024, 2), 'mb')       as data_free,
       concat(truncate(sum(index_length) / 1024 / 1024, 2), 'mb')    as index_size
from information_schema.tables
where table_schema = 'mysql';

1
2
3
4
5
6

查看单库下所有表的状态

select table_name,
       (data_length / 1024 / 1024)                  as data_mb,
       (index_length / 1024 / 1024)                 as index_mb,
       ((data_length + index_length) / 1024 / 1024) as all_mb,
       table_rows
from information_schema.tables
where table_schema = 'activit';

1
2
3
4
5
6
7

# 七、示例

前提：有个表，存在员工信息、员工部门、员工工资等信息；

查询员工工资与比自己高一位/低一位工资的差额

思路：使用lead(), lag() 函数

select ename 姓名, job 职业, deptno 部门,sal 工资,
       lead(sal, 1, 0) over(partition by deptno order by sal)  前一个,
       lag(sal, 1, 0) over(partition by deptno order by sal)  后一个,
       ifnull(lead(sal) over(partition by deptno order by sal) - sal, 0)  前一个差额,
       ifnull(sal - lag(sal) over(partition by deptno order by sal), 0)   后一个差额
from emp;

1
2
3
4
5
6

# 八、数据库join

join, inner join,straight join(让左表主导), cross join, left join, right join, full join

# join, inner join, cross join

# 仅限于mysql中，全部都会交叉结果集，常说的笛卡尔积（即，第一个表中的每一行都与第二个表中的每一行都联接在一起）
select t1.a, t2.b from t1,t2;
等价于
select t1.a, t2.b from t1 join t2;       # 没有on条件
等价于
select t1.a, t2.b from t1 inner join t2; # 没有on条件
等价于
select t1.a, t2.b from t1 cross join t2; # 没有on条件

1
2
3
4
5
6
7
8

select t1.a, t2.b from t1,t2 where t1.a=t2.a;
等价于
select t1.a, t2.b from t1 join t2 on t1.a=t2.a;
等价于
select t1.a, t2.b from t1 inner join t2 on t1.a=t2.a;
等价于
select t1.a, t2.b from t1 cross join t2 on t1.a=t2.a;

1
2
3
4
5
6
7

# 九、语句优化(均摘自官网)

WHERE Clause Optimization（where子句优化）

Removal of unnecessary parentheses:

 ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

1
2

Constant folding:

 (a<b AND b=c) AND a=5
 -> b>5 AND b=c AND a=5

1
2

Constant condition removal:

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6

1
2

For each table in a join, a simpler WHERE is constructed to get a fast WHERE evaluation for the table and also to skip rows as soon as possible.

大体意思：将每个join的"表"先进行where过滤，减少连接时候的数据

The best join combination for joining the tables is found by trying all possibilities.
If all columns in ORDER BY and GROUP BY clauses come from the same table, that table is preferred first when joining.

大体意思：写语句时就要观察语句，如果用在ORDER BY and GROUP BY中的列都在一个表，这个表作为主导表

# 十、补充

优化分为软优化和硬优化，硬优化主要是分库分表、读写分离和缓存等等。软优化即查询方面的优化一般从下面几个方面入手：

❶索引：保证索引有效性不要使用函数；不要用两边都有%的like；字段是字符串的话，需要加上单引号；尽量不要使用or条件，如果用的话索引放在or左侧，否则也会导致索引失效；
❷where条件中的子查询是不允许的，用连接查询代替。如果有一个表数据量非常多，用到的数据量却非常少，先利用子查询查出少量数据（缩小连接表的数量）作为连接的子表，此时优先使用子查询；
❸尽量用exists代替in。not in查询结果集中如果有null，主查询不返回任何数据，in是有数量限制的，枚举不能多于1000。子查询表数据量大的用exists，子查询表数据量小的用in；
❹查询字段不要使用*，把需要查询出来的字段分别列出来；
❺不需要排序的话指定不需要排序，因为mysql会使用默认排序；可以使用order by null对没有排序要求的查询禁止默认排序。
❻如果只是查询数据存不存在时，使用limit 1，这样可以提高效率；
❼尽量用union all代替union，因为union会在输出最终结果前进行排序、去重操作。 sql优化的原则：保证索引有效，减少查询次数，遵守常用规则。

MySql复制表数据 Activiti 环境搭建及部署

学也无涯

Mysql笔记

Mysql笔记

# 一、运算符和函数

# 1、字符函数

# 2、数学函数

# 3、日期函数：

# 4、条件函数等

# 二、常见低级错误

# 1、逗号问题

# 2、隐式转换

# 3、mysql中的更新表问题

# 三、常用命令

# 四、MySQL 索引

# 五、数据库用户

# 六、数据库信息查看

# 七、示例

# 八、数据库join

# join, inner join, cross join

# 九、语句优化(均摘自官网)

# 十、补充