MySQL
SELECT子句的顺序
子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表中选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否
查询表的关键字顺序
select、from、order by、limit
limit
（limit x）表示返回的结果不多于x行 （limit 5,5;) 表示表示返回从第5行开始的5行，行号从0开始
order by
(order by 列名1，列名2) 表示按列排序，默认为asc升序，可以指定为desc降序
where
(列名 BETWEEN a AND b) (列名 is NULL) 操作符（AND，OR，IN）其中AND操作符的优先级更高，OR=IN
LIke匹配整列
在使用"_"通配符时，尾空格可能导致匹配错误，"%"没办法匹配NULL值，
如果使用其他操作符能达到相同目的，就不要使用通配符，因为花费的时间更长
尽量不要用在搜索模式的开始处，这样是最慢的
REGEXP正则表达式在列值中匹配，不区分大小写，如果需要，(REGEXP BINARY)
(列名 REGEXP '.000') "."为特殊字符，可以匹配任意字符
(列名 REGEXP '1000 | 2000') "|"为"or"的意思
([123] ton) 为"1 ton"or"2 ton"or"3 ton"
([^12] ton) 其中[^12]将匹配除"1","2"以外的字符串
[0-9] 将匹配0到9的全部字符
匹配特殊字符需要转义如"\\.","\\-"这些
预定义的字符集：[:alnum:] 任意字母和数字 [:alpha:] 任意字母 [:blank:] 空格和制表 等等
重复字符元：* 无限制个数 + 至少一个 ? 0个或者1个 {n} 指定数目匹配 {n,} 不少于指定数目的匹配 {n,m} 匹配数目的范围，m不大于255
定位元字符：^ 文本的开始 $ 文本的结尾 [[:<:]] 词的开始 [[:>:]] 词的结尾
创建计算字段以及别名
SELECT Concat(列名,'(',列名,')') 考虑到右侧空格，一般会用RTrim()函数来包裹列名，类似的还有LTrim(),Trim()
通常别名用"AS"关键字连接跟在列名后面，客户机可以按照别名引用列
SELECT 语句可以进行算术计算，SELECT Now(); 可以返回当前时间
使用数据处理函数
文本处理函数
UPPER(s) 将字符串转换为大写
LOWER(s) 将字符串 s 的所有字母变成小写字母
TRIM(s) 去掉字符串 s 开始和结尾处的空格
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串
LENGTH(s) 返回字符串s的字符数
mysql> SELECT platformName,Upper(platformName) AS new_data FROM roleinfo ORDER BY platformName; +---------------+---------------+ | platformName | new_data | +---------------+---------------+ | ainjigame_app | AINJIGAME_APP | | cinjigame | CINJIGAME | | xinjigame_app | XINJIGAME_APP | +---------------+---------------+ mysql> SELECT SUBSTRING_INDEX('a*b','*',1) AS new_data; +----------+ | new_data | +----------+ | a | +----------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING_INDEX('a*b','*',-1)AS new_data; +----------+ | new_data | +----------+ | b | mysql> SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; +---------------+ | ExtractString | +---------------+ | UNO | +---------------+ mysql> SELECT platformName,LENGTH(platformName) AS new_data FROM roleinfo WHERE sex =0; +--------------+----------+ | platformName | new_data | +--------------+----------+ | cinjigame | 9 | +--------------+----------+
日期和时间函数
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
DATE_FORMAT(d,f) 按表达式f的要求显示日期d：返回一个格式化的日期或时间串
DAY(d) 返回日期值d的天数部分
DAYOFWEEK(d) 日期 d 今天是星期几：对于一个日期，返回对应的星期几
DAYOFYEAR(d) 计算日期 d 是本年的第几天
HOUR(t) 返回 t 中的小时部分
LOCALTIME() 返回当前日期和时间
NOW() 返回当前日期和时间
MINUTE(t) 返回 t 中的分钟值
MONTHNAME(d) 返回日期当中的月份名称，如 Janyary
MONTH(d) 返回日期d中的月份值，1 到 12
SECOND(t) 返回 t 中的秒钟值
TIMEDIFF(time1, time2) 计算时间差值
WEEK(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53
YEAR(d) 返回一个日期的年份部分
TIME(d) 返回一个日期时间的时间部分
数据经常需要用日期进行过滤。用日期进行过滤时需要注意一些别的问题和使用特殊函的MYSQL函数：
首先需要注意的是MYSQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是使用WHERE子句进行过滤，日期格式必须为yyyy-mm-dd。虽然其他格式也行，但是这是首选格式，因为他排除了多义性。
mysql> SELECT sex,createTime FROM roleinfo WHERE createTime = 1528216525;
+-----+------------+
| sex | createTime |
+-----+------------+
| 1 | 1528216525 |
+-----+------------+
mysql> SELECT sex,createTime FROM roleinfo WHERE createTime = 1528216525;
+-----+------------+
| sex | createTime |
+-----+------------+
| 1 | 1528216525 |
+-----+------------+
mysql> SELECT sex,logintime FROM roleinfo WHERE logintime = "2019-01-25";
+-----+------------+
| sex | logintime |
+-----+------------+
| 1 | 2019-01-25 |
+-----+------------+
mysql> SELECT sex,changetime FROM roleinfo WHERE Date(changetime) = "2019-01-15";
+-----+---------------------+
| sex | changetime |
+-----+---------------------+
| 1 | 2019-01-15 22:40:22 |
+-----+---------------------+
注：
例4中使用了Date()函数：指示MYSQL仅将给出的日期与列中值得日期部分进行比较，而不是将给出的日期与整个列值进行比较。当然也存在一个Time()函数用于值比较日期时间值中的时间部分
MySQL数据库中的Date,DateTime,TimeStamp和Time类型
DATETIME类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值，支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。（“支持”意味着尽管更早的值可能工作，但不能保证他们可以。）
DATE类型用在你仅需要日期值时，没有时间部分。MySQL检索并且以'YYYY-MM-DD'格式显示DATE值，支持的范围是'1000-01-01'到'9999-12-31'。
TIMESTAMP列类型提供一种类型，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。
TIME数据类型表示一天中的时间。MySQL检索并且以"HH:MM:SS"格式显示TIME值。支持的范围是'00:00:00'到'23:59:59'。
汇总数据（自动忽略NULL值）
| AVG() | 返回某列的平均值，AVG( DISTINCT level)，可以指定为计算不同值的行，也可以不指定(ALL) |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
分组数据
依据(GROUP BY 列名) 使用了GROUP BY，就不必指定要计算和估值的每个组了，系统会自动完成。GROUP BY子句指示指示MySQL分组数据，然后都每个组而不是整个结果集进行聚集
关于GROUP BY使用，请注意以下规则：
1、GROUP BY子句可以包含任意数目的列，这使得可以对分组进行嵌套，为数据分组提供更细致的控制
2、如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。即：在建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据）
3、GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在select中使用表达式，则必须在GROUP BY子句中指定相同的表达式（不能使用别名）
4、除了聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出
5、如果分组列中具有null值，则null将作为一个分组返回（如果列中有多行null值，他们将分为一组）
6、GROUP BY子句必须出现在WHERE子句之后，ORBER BY子句之前
PS：使用with rollup关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。
过滤分组
1、除了使用GROUP BY分组数据外，MYSQL还允许过滤分组，规定包括哪些分组，排除哪些分组。为了得出这种数据，必须基于完整的分组而不是个别的行进行过滤
2、在进行分组过滤时不能使用WHERE关键字，因为WHERE过滤指定的是行而不是分组，事实上WHERE没有分组的概念。为此MYSQL提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE，唯一的区别是WHERE过滤行，而HAVING过滤分组
注：一般使用group by子句时，也应该给出order by子句，这是保证数据正确排序的唯一方法（千万不要依赖group by排序数据）
分组数据 分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算 创建分组 在MySQL中，分组是在SELECT语句中的GROUP BY子句中建立的 例12： mysql> SELECT total_price,count(*) AS new_price FROM price GROUP BY total_price; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1002 | 2 | | 1003 | 4 | | 1004 | 2 | | 1005 | 2 | +-------------+-----------+ 注： 1、上面例子中指定了两个列，total_price为目标列(用于分组的列)，new_price为计算字段(由count(*)函数建立)。GROUP BY子句指示MYSQL按total_price排序并分组数据，这导致对每个total_price计算而不是对整个表(列)计算 2、因为使用了GROUP BY，就不必指定要计算和估值的每个组了，系统会自动完成。GROUP BY子句指示指示MySQL分组数据，然后都每个组而不是整个结果集进行聚集 关于GROUP BY使用，请注意以下规则： 1、GROUP BY子句可以包含任意数目的列，这使得可以对分组进行嵌套，为数据分组提供更细致的控制 2、如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。即：在建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据） 3、GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数），如果在select中使用表达式，则必须在GROUP BY子句中指定相同的表达式（不能使用别名） 4、除了聚集计算语句外，select语句中的每个列都必须在GROUP BY子句中给出 5、如果分组列中具有null值，则null将作为一个分组返回（如果列中有多行null值，他们将分为一组） 6、GROUP BY子句必须出现在WHERE子句之后，ORBER BY子句之前 PS：使用with rollup关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。 过滤分组 1、除了使用GROUP BY分组数据外，MYSQL还允许过滤分组，规定包括哪些分组，排除哪些分组。为了得出这种数据，必须基于完整的分组而不是个别的行进行过滤 2、在进行分组过滤时不能使用WHERE关键字，因为WHERE过滤指定的是行而不是分组，事实上WHERE没有分组的概念。为此MYSQL提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE，唯一的区别是WHERE过滤行，而HAVING过滤分组 例13： mysql> SELECT total_price,count(*) AS new_price FROM price GROUP BY total_price HAVING count(*)>= 3; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1003 | 4 | +-------------+-----------+ 注： 这条SQL语句中的having子句过滤出count（*）>=3（3个以上的分组）的那些分组 HAVING和WHERE的区别： 1、WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤；where排除的行不包括在分组中（这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组） 2、WHERE过滤行，而HAVING过滤分组 同时使用HAVING和WHERE 例14： mysql> SELECT total_price,count(*) AS new_price FROM price WHERE current_price=8 GROUP BY total_price; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1003 | 4 | | 1004 | 1 | +-------------+-----------+ 例14_1: mysql> SELECT total_price,count(*) AS new_price FROM price WHERE current_price=8 GROUP BY total_price HAVING count(*)>= 2; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1003 | 4 | +-------------+-----------+ 注： WHERE子句过滤出current_price=8的行，然后按照total_price分组数据，HAVING子句再过滤出计数大于等于2的分组 group by和order by的区别： 虽然GROUP BY和ORDER BY经常完成相同的 工作，但它们之间有很大的不同 ORDER BY GROUP BY 排序产生的输出 分组行，但输出可能不是分组的顺序 任意列都可以使用，甚至非选择的列也可以使用 只可使用选择列或表达式列，而且必须使用每个选择列表达式 不一定需要 如果与聚集函数一起使用列(或表达式)，则必须使用 注：一般使用group by子句时，也应该给出order by子句，这是保证数据正确排序的唯一方法（千万不要依赖group by排序数据） 例15： mysql> SELECT total_price,SUM(current_price) AS new_price FROM price GROUP BY total_price; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1002 | 10 | | 1003 | 39 | | 1004 | 13 | | 1005 | 6 | +-------------+-----------+ 例15_1: mysql> SELECT total_price,SUM(current_price) AS new_price FROM price GROUP BY total_price HAVING SUM(current_price) >= 6 ORDER BY new_price; +-------------+-----------+ | total_price | new_price | +-------------+-----------+ | 1005 | 6 | | 1002 | 10 | | 1004 | 13 | | 1003 | 39 | +-------------+-----------+ ———————————————— 版权声明：本文为CSDN博主「不怕猫的耗子A」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_39314932/article/details/86563487
使用子查询
使用子查询作为条件过滤
应当适当缩进一便于阅读，而且子查询的select应当与where语句的列匹配
作为计算字段使用子查询
此时的子查询对检索出来的数据执行一次
表联结
记得过滤语句，否则会出现笛卡尔积
联结表时首选 (from 表名 INNER JOIN 表名 on 过滤条件) 而不是 (from 表名,表名 where 过滤条件)
自联结
将同一个表取两个别名：(from 表名 AS pi,表名 AS p2 where 过滤条件)
外部联结[RIGHT|LEFT]必须选择一个
[RIGHT|LEFT] OUTER JOIN right以及left决定返回右边或者左边的全部列，另一边没有与之匹配的列时用NULL代替
使用带聚集函数的联结
可以将聚集函数放在select语句里面
组合查询
使用UNION将需要的select语句连接，需要有同样的列，一般来说，UNION的组合查询一般可以用（where or）语句代替。
有一种情况例外，当需要满足条件的语句都出现时，（where or）语句会自动删除一样的行，使用（UNION ALL）则不会
全文本搜索
最经常使用的引擎（ENGINE=MyISAM）支持，（ENGINE=InnoDB）不支持
如果需要开启全文本搜索，一般需要在create表的时候使用FULLTEXT(列名,列名)的方式，或者在稍后指定（这种情况下所有已有的数据必须立即索引），列名可不唯一，定义之后MySQL会自动维护该索引，在CRU时自动更新
使用方式（where Match(列名) Against('需要匹配的片段')）
此方式对比于like关键字的优势在于返回的数据会有理想的优先级，并且速度上快于like
使用查询扩展
（where Match(列名) Against('需要匹配的片段') WITH QUERY EXPANSION）此方式不仅会返回包含的关键字，还会返回包含关键字的语句的很可能相关的语句
布尔文本搜索
即使没有定义FULLTEXT索引，也可以使用，但这是一种很缓慢的操作，其性能将随着数据量的增加而降低
（where Match(列名) Against('需要匹配的片段') IN BOOLEAN MODE）
布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不存在
> 包含，并且增加等级值
< 包含，并且减小等级值
（） 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“ ” 定义一个短语（与单个词的列表不一样，它匹配整个短语，以便包含或排除这个短语）
全文本搜索的注意事项
在索引全文本数据时，短语被忽略且从索引中排除。短语定义为具有3个或3个以下字符的词。可以修改。
MySQL带有一个内建的非用词（stopWord）列表，这些词在索引全文本数据时被忽略。可以覆盖这个列表。
许多词的出现频率很高，搜索它们会返回太多的结果，意义不大，因此，MySQL规定了一个50%规则：如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。（50%规则不适用于布尔文本搜索）
如果表中的行数少于3行，则全文本搜索不返回任何结果，因为每个词都不出现或者出现在50%以上的行中。
忽略词中的单引号，如don’t的索引为dont。
不具有词分隔符的语言（如：汉语和日语），不能恰当的返回全文本搜索结果。
仅在MyISAM数据库引擎中支持全文本搜索。
表的CURD
插入数据
通常来说，插入数据是比较耗时的，如果需要，可以使用（INSERT LOW_PRIORITY INTO）来降低优先级，同时也适用于UPDATE和DELETE语句
插入语句还可以和select语句一起使用如：
INSERT INTO 表名（列名，.......）SELECT（列名，.......）FROM 表名；
同时select语句也可以包含where语句，需要注意的是，此时的列名的个数需要一样，且MySQL不关心列名的顺序
更新语句
一次更新多条语句时，如果一行出现错误，则会回滚到之前的状态。如果不想回滚，希望可以继续更新，可以使用IGNORE关键字，(UPDATE IGNORE 表名; )
删除语句
不带where语句的delete关键字会删除表的所有行，并不会删除表，如果确实想删除表的所有行，应该使用(TRUNCATE TABLE 表名;)速度会更快
DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。
总结
当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。
创建表
如果想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS
使用AUTO_INCREMENT,
一个表只允许一个AUTO_INCREMENT的列，且必须被索引(比如通过它成为主键)
如果想覆盖AUTO_INCREMENT，只需要指定一个表中没有的值即可
通常你不知道AUTO_INCREMENT生成的值是多少，如果需要，可以使用(select last_insert_id();)此语句返回最后一个AUTO_INCREMENT值
表的约束
外键约束
事务
通常是默认开启事务的，所以不会回滚
事务保证了数据的一致性
- 要么都成功
- 要么都失败
对于没有开启自动提交的数据，是可以回滚的，一旦提交了之后，就不可以回滚，体现了MySQL的持久性
自动提交：@@autocommit=1;
手动提交：commit;
回滚：rollback;
开启事务的两种方式
- begin;
- start transaction;
事务的四大特征:
-
A 原子性:事务是最小的单位，不可以在分割。
-
C 一致性:事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败。
-
I 隔离性:事务1和事务2之间是具有隔离性的。
- D 持久性:事务一旦结束(commit,rollback)，就不可以返回。事务开启:
修改默认提交
- 1.set autocommit=0;
- 2. begin;
- 3. start transaction;
事务手动回滚:rollback;
事务的隔离性
事务的隔离性越高，则性能越差，系统默认的隔离级别是REPEATABLE READ
查看系统隔离性
select @@global.transaction_isolation;
查看会话隔离性
select @@transaction_isolation;
修改系统隔离性
set global transaction isolation level read committed;
1：read uncommitted
会出现脏读现象，即可以读到还未被提交的事务，如果未被提交的事务rollback，会发生不可预料的事情，应当避免发生脏读的发生
2：read committed
会出现不可重复读现象，即事务a对于事务b的insert语句提交之后，在事务a做操作的时候，读取不到事务b的insert数据
3：repeatable read
会出现幻读现象，已经被事务a提交的数据，事务b用select检索不出来，但又没办法insert
4：serializable
会出现超时的情况，当事务a开启并且还没有commit的时候，如果需要insert，这会造成等待，如果时间久，则可以失败
存储过程
很多公司禁止使用
参数类型
- in 有参数默认
- out 有返回
- inout 两个的结合