本文详细列举了MySQL中常用的SQL语句，包括连接数据库、数据导出、系统查询、数据库与表的操作、字段类型以及内置函数等。同时，还介绍了MySQL的内置系统函数，如字符串、数值、日期等函数，并简述了MySQL的安全策略，如数据备份、权限分配和错误信息处理。此外，提到了事务、存储过程、触发器、视图和游标等高级特性。
目录
整数类型 tinyint、smallint、mediumint、int、bigint
【常用的SQL语句】
连接和导出相关
mysql -h192.168.0.201 -P3306 -uroot -p123456 -D数据库名 #命令行连接数据库
mysqldump -uroot -p123456 test > db.sql #导出整个test数据库数据到test.sql文件
mysqldump -uroot -p123456 test users> db.sql #导出test库下面的users表的数据（后面加上表名）
mysqldump -uroot -p123456 test users --where="score>=60" > db.sql #导出某个表的数据，包含where 条件
mysqldump -uroot -e "set names 'utf8';select a,b,c from test.tbname where type=2" > db.sql -p123456 #自定义SQL语句查询后直接导出
mysqldump --set-gtid-purged=OFF -h127.0.0.1 -uroot -p123456 dbname --ignore-table=dbname.tb1 --ignore-table=dbname.tb2 > ./db.sql #导出数据库但是排除tb1表和tb2表shell 脚本自定义导入SQL语句，用下面的命令
/usr/local/mysql/bin/mysql -uroot -p123456 -e "
use dbname
source db.sql
select * from dev
quit"mysqldumpslow慢日志分析工具。
-s 排序选项：c 查询次数 r 返回记录行数 t 查询时间
-t 只显示top n条查询
mysqldumpslow -s r -t 10000 slow-queries.log >analysis.log系统相关查询
SET PASSWORD = PASSWORD('123456'); #修改root密码
SET @@PROFILING=1;[SQL语句]; SHOW PROFILES; #查看MySQL执行效率
SHOW TABLE STATUS; #查看表的一些信息，例如当前自增的编号
SHOW TABLE STATUS FROM tablename LIKE '%user%'; #查看某个表或某些表的信息
SELECT table_schema FROM information_schema.TABLES WHERE table_name = '表名'; #查找某个表在哪个库
SHOW variables; #查看当前mysql的配置变量
show variables like '%slow' #慢查询日志是否打开
show variables like 'max_connections' #查看MySQL允许的最大连接数
show global status #查看系统当前状态，返回的com_xxx表示xxx语句执行的次数，例如com_select.
show global status like '%slow' #查看慢查询的条数
show status like 'Handler_read%' #查看索引的使用情况：
show processlist; #显示当前所有连接的工作状态数据库和表的操作
创建库和表
创建数据库：CREATE DATABASE IF NOT EXISTS shop;
查看数据库：SHOW DATABASES;
删除数据库：DROP DATABASES IF EXISTS shop;
查看当前数据库中的数据表：SHOW TABLES;
查看表结构： DESC item;
查看注释标识：SHOW CREATE TABLE item;
删除数据表：DROP TABLE IF EXISTS item;
重命名表：RENAME TABLE tb_name TO new_tb_name, tb_name2 TO new_tb_name2;
把dbx中的表全部复制到dby：
CREATE TABLE dby.user LIKE dbx.user
INSERT INTO dby.user SELECT * FROM dbx.userALTER修改字段和索引
添加字段：ALTER TABLE tbname ADD username varchar(5) NOT NULL DEFAULT '' COMMENT 'remaks';
修改字段的信息：ALTER table tbname MODIFY username varchar(20) NOT NULL default '' comment '备注' [AFTER fieldname | FIRST] ;
替换字段为新值：ALTER table tbname CHANGE username userinfo int(11) NOT NULL DEFAULT 0 COMMENT '备注';
删除字段：ALTER TABLE tbname DROP COLUMN userinfo;
更改表的自动增长(auto_increment)的值：ALTER TABLE users AUTO_INCREMENT = 1000;
添加主键索引：ALTER TABLE tbname ADD PRIMARY KEY s1(column)
添加唯一索引：ALTER TABLE tbname ADD UNIQUE s2(column)
添加普通索引：ALTER TABLE tbname ADD [INDEX|KEY] idx_name (column1,column2,column3)
添加全文索引：ALTER TABLE tbname ADD FULLTEXT s3(column)
删除索引：ALTER TABLE tbname DROP index s1;增删改查操作示例
查询数据：SELECT * FROM user WHERE id=1;
插入数据： INSERT INTO user(id,name) VALUES (1,’renxing’);
修改数据： UPDATE user SET name=’wahaha’ WHERE id=1;
批量修改数据：UPDATE tbName SET name = CASE myid WHEN 2 THEN 'Hello' WHEN 3 THEN 'world' END WHERE myid IN (2,3);
删除数据：DELETE FROM user WHERE id=1;
清空表：TRUNCATE user;
内连接INNER JOIN：返回与连接条件相匹配的数据行
SELECT * FROM a INNER JOIN b ON a.id=b.id;
左外连接LEFT JOIN，左表的数据全部显示
SELECT * FROM a LEFT JOIN b ON a.id=b.id;
右外连接RIGHT JOIN，右表的数据全部显示
SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
分组查询：查询user表中 user_name字段值重复的数据及重复次数
select user_name,count(*) as count from user group by user_name having count>1;在record表中随机获取10条数据：
最慢：SELECT * FROM `record` ORDER BY RAND() limit 10
理想：SELECT * FROM `record` WHERE record_id >= RAND()*10000 ORDER BY record_id LIMIT 10; #这里的10000可以提前查出max(record_id)关联查询
1、交叉连接 CROSS JOIN，没有任何关联条件，结果是笛卡尔积：SELECT * FROM A,B
2、内连接 INNER JOIN，可以缩写为JOIN，获取两个表中字段匹配关系的记录。
SELECT * FROM A,B WHERE A.id=B.id 或者
SELECT * FROM A INNER JOIN B ON A.id=B.id
内连接分为三类：
等值连接：ON A.id=B.id
不等值连接：ON A.id > B.id
自连接：SELCT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid
3、外连接 LEFT JOIN，左表数据全部显示；RIGHT JOIN 右表数据全部显示
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，获取右表所有记录，即使左表没有对应匹配的记录。
4、联合查询 UNION / UNION ALL，把多个结果集集中在一起，列数要相等，UNION相同的记录行会合并，UNION ALL相同的行不会合并。比如 t1表和t2表都有(2, b)、(3, c)这两条记录，使用UNION合并后的结果集就把它俩去重了，但是使用UNION ALL 会保留重复记录。
SELECT * FROM A UNION SELECT * FROM B UNION …
5、全连接 FULL JOIN：（MySQL不支持全连接，但可以如下实现）
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id
子查询
SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)按照子查询执行的次数，可以将子查询分成关联子查询和非关联子查询，其中非关联子查询与主查询的执行无关，只需要执行一次即可，而关联子查询，则需要将主查询的字段值传入子查询中进行关联查询。
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)关联子查询通常也会和 EXISTS/NOT EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。
SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)集合比较子查询的作用是与另一个查询结果集进行比较，可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符:
IN: 判断是否在集合中
ANY: 需要与比较操作符一起使用，与子查询返回的任何值做比较
ALL: 需要与比较操作符一起使用，与子查询返回的所有值做比较
SOME: 实际上是ANY的别名，作用相同，一般常使用ANY
SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id) //IN
SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002) //ANY
SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002) //ANY将子查询作为计算字段:
SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team //查询相同的 team_id 在 player 这张表中所有的球员数量是多少。其它查询
SELECT COUNT(DISTINCT field) FROM tbname; #获取某个字段去重后的数据总数
SELECT * FROM tbname WHERE name like 'abc___'; #查询关键词abc后面跟3个字符
#聚合查询:
group by 字段... having 条件；
总数 count()；总和 sum()；平均值 avg()；最大值 max()；最小值 min() mysql字段类型
整数类型 tinyint、smallint、mediumint、int、bigint
属性 unsigned 设置是否为正数，int(11) 指定宽度，不会限制值的合法范围，只会影响显示字符的个数。
设置了zerofill之后，存储“12”，int(3)的结果是“012”，int(5)的结果是“00012”。
字节占用情况：smallint 2字节，int 4字节，bigint 8字节。
常用的四种数值类型的比较：
tinyint：从 0 到 255 的整型数据。存储大小为 1 字节。
smallint：从 -2^15 (-32,768) 到 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。
int：从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。
bigint：从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字），存储大小为 8 个字节。bigint已经有长度了，在mysql建表中的length，只是用于显示的位数。
问题：int(20) 中20的含义
答案：是指显示字符的长度，不影响内部存储，只是当定义了ZEROFILL时，前面补多少个 0
问题：int(4) 和 int(8) 的区别
答案：假设插入”123”，使用zerofill后，int(4)其实是“0123”，int(8)是“00000123”，当没有加zerofill时候两者是没有任何区别的。括号中的数字表示的是显示宽度，而不是限制列内保存值的范围的。
实数类型 float、double、decimal
decimal可以存储比bigint还大的整数，可以用于存储精确的小数；float和double类型支持使用标准的浮点进行近似计算。
float类型的数据，只分配了32位的存储空间，对于double类型值分配了64位。float(M, D)两个参数的意义：表示一共能存M位，其中小数点后占D位。比如float(3,1)表示一共3位，其中小数点后1位数字。
如何选择float，double，decimal？
1、float：如果要表示的浮点型数据转成二进制之后能被32位float存储，或者可以容忍截断。
2、double：如果要表示的浮点型数据转成二进制之后能被64位double存储，或者可以容忍截断。
3、double已经满足大部分浮点型数据的存储精度要求，如果还要更精确则可以使用decimal定点型存储，比如一些科学数据、精度要求很高的金额。
4、最好存储int 或者bigint，返回给业务的时候再除以精度值。比如存储单位“分”，100分=1元。
深入分析：
1. 如果一个float型数据转成二进制后的第32位之后都是0，那么数据是准的；如果一个float型数据转成二进制后的第32位之后不全为0，则数据就会存在误差。
2. 对于精度比较高的东西，比如金额，建议使用decimal类型，不要考虑float, double, 因为容易产生误差；numeric和decimal同义，numeric将自动转成decimal。DECIMAL从MySQL 5.1引入，列的声明语法是DECIMAL(M,D)。
3. float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。如DECIMAL(5,2) 的最大值为9999.99，因为有7个字节可用。
更多细节参考：mysql float数据类型和decimal数据类型_donghaixiaolongwang的博客-CSDN博客
字符串类型 varchar、char、text、blob
varchar类型用于存储可变长字符串，比定长类型更节省空间。
char是定长的，根据定义的字符串长度分配足够的空间，会根据需要采用空格进行填充以方便比较。char适合存储很短的字符串，或者所有值都接近同一个长度。
对于经常变更的数据，char比varchar更好，char不容易产生碎片。对于非常短的列，char比varchar在存储空间上更有效率。
varchar和char的长度，如果存储内容超出指定长度，都会被截断。
尽量避免使用BLOB/TEXT类型，查询会使用临时表，导致严重的性能开销。
TEXT类型不能有默认值。
问题：varchar与char的区别
答案：char是固定长度的字符类型，分配多少空间，就占用多长空间。varchar是可变长度的字符类型，内容有多大就占用多大的空间，能有效节省空间。由于varchar类型是可变的，所以在数据长度改变的时候，服务器要进行额外的操作，所以效率比char类型低。
问题：varchar(50) 中50的含义
答案：最多存放50个字符，varchar(50) 和 varchar(200) 存储”hello"所占空间一样，但后者在排序时会消耗更多内存，因为order by col 采用fixed_length计算col长度。
问题：char(4) 和 varchar(4) 的区别
答案：char定义的是固定长度，长度范围为0-255。varchar是变长长度，长度范围为0-65535。
问题：char(10) 能否存进去10个中文文字？
答案：nchar(10)能存10个汉字，char(10)只能存5个汉字，1个汉字占2个字节。char(10)固定宽度10个字符，nchar(10)支持多种语言的固定宽度10个字符。varchar(10)最多宽度10个字符，nvarchar(10)支持多种语言最多宽度10个字符。
枚举类型 ENUM
把不重复的数据存储为一个预定义的集合，有时可以使用ENUM代替常用的字符串类型。ENUM存储非常紧凑，会把列表值压缩到一个或两个字节，ENUM在内部存储时，其实存的是整数。其实一般在业务开发中，能用ENUM的场景也可以用tinyint存储，比如性别可以存储成1（表示男）和2（表示女）。
日期和时间类型
一般时间可以存储成时间戳(int类型)，如果需要直接存储时间格式，建议尽量使用timestamp，空间效率高于dateTime。
MySQL关于日期和时间的一些用法：
/*上个月同一天的当前时间*/
select date_sub(now(),interval 1 month);
/*上个月同一天的当前时间的时间戳*/
select UNIX_TIMESTAMP(date_sub(now(),interval 1 month));
/*上个月的第一天*/
select date_sub(date_sub(date_format(now(),'%y-%m-%d 08:00:00'),interval extract( day from now())-1 day),interval 1 month);
/*上个月的最后一天*/
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month);
/*这个月的第一天*/
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 0 month);
/*这个月的最后一天*/
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval -1 month) as date;【MySQL中的内置系统函数】
字符串函数
CONCAT(S1,S2....SN): 把传入的参数连接成一个字符串
INSERT(str, x, y, insert): 将字符串 X位置开始，y个字符串长度替换为字符串 insert
LOWER(str) UPPER(str): 将字符串转为小或或大写
LEFT(str, x) RIGHT(str, x): 分别返回最左边的 X字符，和最右边的X个字符，如果第二个参数为 NULL则什么也不返回
LPAD(str, n, pad), RPAD(str,n,pad): 用字符串 pad对str 最左边和最友边进行填充，直到长度为 n个字符长度
TRIM(str) LTRIM(str) Rtrim(str): 去掉字符串两边，左边和右边字符串的空格
replace(str, a,b) ：用字符串 b替换字符串str中的所有出现的字符串 a
strcmp(s1, s2): 如果S1比 S2小，返回-1 ， 如果 S1比S2 大则返回 1， 如果S1==S2 0
substring(str, x, y): 返回字符串中的第 x位置起y 个字符串长度的字符。
数值函数
abs(x)：返回X 的绝对值
ceil(x): 返回大于 X的最小整数 2.1 2.5 2.9 3
floor(x): 返回小于 X的最大整数 2.1 2.5 2.9 2
mod(x, y): 返回x/y的模
rand() 0-1 之间
round(x,y): 返回参数 X的四舍五入的有y位小数的值
truncate(x,y): 返回数字 x截断为y 位小数的结果
日期函数
curdate()
curtime()
now()
unix_timestamp(date)
from_unixtime
week()
year()
hour()
minute()
流程控制函数
if(value, t f)
ifnull(value1, value2)
case when [value1] then[result1]...else[default]end
case when ...then
其它函数
database()
version()
user()
inet_aton(ip)
inet_ntoa()
password()
md5()
mysql内置函数速记整理
[字符串函数] CONCAT(S1,S2....SN)；INSERT(str, x, y, insert)；LOWER(str) UPPER(str)；LEFT(str, x) RIGHT(str, x)；LPAD(str, n, pad), RPAD(str,n,pad)；TRIM (str) LTRIM(str) Rtrim(str);replace(str, a,b);strcmp(s1, s2);substring(str, x, y)。
[数值函数] ABS(x)；ceil(x)；floor(x)；mod(x, y)；rand()；round(x,y)；truncate(x,y)。
[日期函数] curdate()；curtime()；now()；unix_timestamp(date)；from_unixtime；week()；year()；hour()；minute()。
[流程控制函数] if(value, t f)；ifnull(value1, value2)；case when [value1] then[result1]...else[default]end；case when ...then。
[其它函数] database()；version()；user()；inet_aton(ip)；inet_ntoa()；password()；md5()。
【mysql安全策略简述】
SQL查询的安全方案：
1. 在程序中（比如PHP）使用预处理语句防止SQL注入
2. 写入数据库的数据要进行特殊字符的转义
3. 查询错误信息不要反回给用户，将错误记录到日志
MySQL的其他安全设置：
1. 定期做好数据备份
2. 不给查询用户root全选，合理分配权限
3. 关闭远程访问数据库的权限
4. 修改root口令，不用默认口令，使用较复杂的口令
5. 删除多余的用户
6. 限制用户对数据文件的访问权限
7. 限制普通用户浏览其他库
SQL注入的防范（以PHP为例）：
login.php?user=admin&pwd=admin or 1=1
① 接收整型参数，使用intval()函数处理
② 接收字符串参数，使用addslashes() 过滤所有的引号和反斜线
③ 转义或者过滤一些特殊字符，如%
④ 做好数据备份注意：PHP端尽量使用 PDO 对数据库进行相关操作，PDO 拥有定义预处理语句很好的支持的方法，MySQLi 也有，但是可扩展性不如 PDO，效率略高于 PDO。
【其它】
事务
如果所有操作都成功，事务提交（commit）；如果有其中一个操作失败，事务回滚（rollback）。
目前MySQL中只有InnoDB和BDB这两种存储引擎支持事务。
开始事务：START TRANSACTION 或者 BEGIN。
提交事务：COMMIT
回滚事务：ROLLBACK
关于事务的具体细节，后面还会讲。
存储过程
为以后的使用而保存的一条或多条SQL语句的集合，存储过程就是有业务逻辑和流程的集合。可以在存储过程中创建表、更新数据、删除等。
创建存储过程：CREATE PROCEDURE
执行存储过程：CALL
查看存储过程的创建语法： SHOW CREATE PROCEDURE
查看存储过程的特性：SHOW PROCEDURE STATUS
删除存储过程：DROP PROCEDURE [IF EXISTS] proc_name
触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
创建触发器：CREATE TRIGGER
查看触发器的相关信息：SHOW TRIGGERS
删除触发器：DROP TRIGGER trigger_name
视图
创建视图：CREATE VIEW
修改视图：ALTER VIEW
删除视图: DROP VIEW
查看创建视图的语法： SHOW CREATE VIEW
游标
声明游标：DECLARE cursor_name CURSOR FOR select_statement
打开游标：OPEN cursor_name
从游标结果集中获取数据行。FETCH cursor_name INTO var_name [,var_name] ...
关闭游标：CLOSE cursor_name
【MySQL各版本的重要区别】
MySQL 5.5：
- InnoDB代替MyISAM成为MySQL默认的存储引擎。
- 引入utf8mb4字符集，可用来存储emoji表情。
- 引入metadata locks（元数据锁）。
MySQL 5.6：
- GTID复制、无损复制、延迟复制、基于库级别的并行复制。
- 对TIME, DATETIME和TIMESTAMP进行了重构，可支持小数秒。
- 全文索引。
- EXPLAIN可用来查看DELETE，INSERT，REPLACE，UPDATE等DML操作的执行计划，在此之前，只支持SELECT操作。
- Redo Log总大小的限制从之前的4G扩展至512G。
- Undo Log可保存在独立表空间中，因其是随机IO，更适合放到SSD中。但仍然不支持空间的自动回收。
- InnoDB内部的性能提升，包括拆分kernel mutex，引入独立的刷新线程，可设置多个purge线程。
MySQL 5.7：
- InnoDB Cluster
- InnoDB原生支持分区表，在此之前，是通过ha_partition接口来实现的。
- 原生支持JSON类型，并引入了众多JSON函数。
- 引入了新的逻辑备份工具-mysqlpump，支持表级别的多线程备份。
- 引入了新的客户端工具-mysqlsh，其支持三种语言：JavaScript, Python and SQL。
MySQL 8.0：
- 引入了原生的，基于InnoDB的数据字典。数据字典表位于mysql库中，对用户不可见。
- 不可见索引（Invisible index）和 降序索引。
- 默认字符集由latin1修改为utf8mb4。
- Redo Log的优化，包括允许多个用户线程并发写入log buffer，可动态修改innodb_log_buffer_size的大小。
- SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句中引入NOWAIT和SKIP LOCKED选项，解决电商场景热点行问题。
- 引入了更多细粒度的权限来替代SUPER权限，现在授予SUPER权限会提示warning。
- BLOB, TEXT, GEOMETRY和JSON字段允许设置默认值。
369
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
