数据库&MySQL
数据库&MySQL-相关书籍
- SQL必知必会-4-电子版
- MySQL技术内幕(第4版)
- MySQL必知必会-扫描版
- MySQL技术内幕 InnoDB存储引擎 第2版
- MySQL技术内幕InnoDB存储引擎-有附录
- 高性能 MySQL 第三版
- 深入浅出MySQL数据库开发优化与管理维护
- MySQL优化学习思维笔记.xmind
- MyBatis面试专题
- MySQL55题答案
- mysql面试专题
- MySQL性能优化的21个最佳实践
- MySQL优化问题.xmind
- MySQL优化学习思维笔记.xmind
- 数据库原理-第5版
- MyBatis.xmind
- Effective+MySQL之SQL语句最优化.pdf
- 程序员的SQL金典.pdf
- 高性能MySQL-第3版.pdf
- 漫画数据库.pdf
- 深入浅出MySQL++数据库开发、优化与管理维护.pdf
- 自己动手设计数据库_自己动手设计数据库.pdf
- Head.First.SQL(中文版).pdf
- MySQL必知必会(文字版).pdf
- MySQL技术内幕：SQL编程.pdf
- MySQL技术内幕(第4版).pdf
- MySQL技术内幕InnoDB存储引擎.pdf
- MySQL技术内幕InnoDB存储引擎(第1版).pdf
- MySQL王者晋级之路.pdf
- MySQL性能调优与架构设计.pdf
- Oracle数据库性能优化.pdf
- Oracle性能优化求生指南.pdf
- SQL必知必会.pdf
- SQL查询的艺术.pdf
- SQL学习指南.pdf
数据库&MySQL-资源
MyBatis批量操作
分批插入
//分批插入，一次插入的大小 |
mybatis批量更新数据三种方法效率对比
实现方式有三种
- for循环通过循环传过来的参数集合，循环出N条sql，
注意此方法需要在db链接url后面带一个参数 &allowMultiQueries=true
即： jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true - 用mysql的case when 条件判断变相的进行批量更新
- 用ON DUPLICATE KEY UPDATE进行批量更新
<!-- 批量更新第一种方法，通过接收传进来的参数list进行循环着组装sql --> |
|
sql语句for循环效率其实相当高的，因为它仅仅有一个循环体，只不过最后update语句比较多，量大了就有可能造成sql阻塞。
case when虽然最后只会有一条更新语句，但是xml中的循环体有点多，每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢，所以效率问题严重。使用的时候建议分批插入。
duplicate key update可以看出来是最快的，但是一般大公司都禁用，公司一般都禁止使用replace into和INSERT INTO … ON DUPLICATE KEY UPDATE，这种sql有可能会造成数据丢失和主从上表的自增id值不一致。而且用这个更新时，记得一定要加上id，而且values（）括号里面放的是数据库字段，不是java对象的属性字段。
根据效率，安全方面综合考虑，选择适合的很重要。
MySQL-explain-详解
explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+ |
expain出来的信息有10列
id选择标识符select_type表示查询的类型table输出结果集的表type表示表的连接类型possible_keys表示查询时，可能使用的索引key表示实际使用的索引key_len索引字段的长度ref列与索引的比较rows扫描出的行数(估算的行数)Extra执行情况的描述和说明
id
SQL执行的成功的标识，SQL从大到小的执行。
- id相同时，执行顺序由上至下，内存会认为三个表，乘积小的先执行
比如三个表：t3-t4-t5
：t3-t3-t4 - 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
explain select * from (select * from ( select * from t3 where id=123) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ |
很显然这条SQL是从里向外的执行,就是从id=3 向上执行.
select_type
SIMPLE
最简单的select(不使用UNION或子查询等) 例如:
explain select * from t3 where id=123;+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+PRIMARY
最外层的select，主查询，例如：
explain select * from (select * from t3 where id=123) a ;+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+UNION
UNION中的第二个或后面的select语句，例如：
explain select * from t3 where id=123 union all select * from t3 ;+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+DEPENDENT UNION
UNION中的第二个或后面的select语句，取决于外面的查询，例如：
explain select * from t3 where id in (select id from t3 where id=123 union all select id from t3) ;+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
|NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+UNION RESULT
UNION的结果，union语句中第二个select开始后面所有select
SUBQUERY
非最外层的select，在子查询中第一个select，例如：
explain select * from t3 where id = (select id from t3 where id=123 ) ;+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+DEPENDENT SUBQUERY
子查询中的第一个select，取决于外面的查询，例如：
explain select id from t3 where id in (select id from t3 where id=123 ) ;+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+
| 1 | PRIMARY | t3 | index | NULL | PRIMARY | 4 | NULL | 1000 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+DERIVED
派生表的select(from子句的子查询)
explain select * from (select * from t3 where id=123) a ;+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+UNCACHEABLE SUBQUERY
一个子查询的结果不能被缓存，必须重新评估外链接的第一行
table
代表表名。有时不是真实的表名,看到的是<derived**X**>(数字X是第几步执行的结果)
explain select * from (select * from ( select * from t3 where id=123) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ |
type(非常重要)
使用了哪种类别，是否使用索引.
const、eq_reg、ref、range、indexhe、ALL（从左到右，性能从好到差）
- ALL： MySQL进行全表扫描。
- index：全索引扫描。index与ALL区别为index类型只遍历索引树
- range：只检索给定范围的行，使用一个索引来选择行
- ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- eq_ref： 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
- const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
- NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
key(非常重要)
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
在不损失精确性的情况下，长度越短越好
ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows
rows列显示MySQL认为它执行查询时必须检查的行数。
Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
-- 测试Extra的filesort
explain select * from emp order by name;Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
-- explain select now() from dual;
总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。**
通过收集统计信息不可能存在结果
sql示例
建表
SET NAMES utf8mb4; |
《MySQL必知必会》读书笔记
常用命令
SHOW DATABASES;SHOW TABLES;SHOW COLUMNS FROM table;SHOW STATUS;SHOW CREATE DATABASE database;SHOW CREATE TABLE table;
ALTER TABLE vendors ADD vend_phone CHAR(20) COMMENT '手机'; – 新增列ALTER TABLE vendors DROP COLUMN vend_phone; – 删除列DROP TABLE vendors – 删除整个表TRUNCATE TABLE... – TRUNCATE TABLE语句，删除原来的表，并重新创建一个表。比DELETE删除所有行更快。RENAME TABLE tab1 TO tab2; – 重命名表ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders(orderr_num);– ALTER TABLE 定义外键
DISTINCT和GROUP BY
SELECT DISTINCT `role_id` FROM user; |
二者都可以达到去重的效果。
DISTINCT把列中的全部内容存储到内存中，可以理解为一个hash，最后的到hash中的key就可以得到结果。比较耗内存。
GROUP BY先将列排序，然后去重。排序比较耗时间。
LIMIT操作符
SELECT `role_id` FROM user LIMIT 5 OFFSET 3; |
LIKE操作符
SELECT * FROM user WHERE name LIKE 't%'; |
以上两句都是模糊匹配用户名以t开头。
%: 匹配任意0个或者多个字符。
_:一个_匹配1个任意字符，且必须有一个。
正则基本字符匹配
SELECT name FROM user WHERE name REGEXP 't'; -- t, t1, t2 |
.表示匹配任意一个字符。
LIKE和REGEXP区别:
LIKE 要求整个列匹配(使用通配符除外), REGEXP只要列中某个片段匹配即可。
假设有用户名为s123。则以下例子中, LIKE没有得到结果。
SELECT name FROM user WHERE name LIKE 's1'; -- 没有结果 |
OR匹配
SELECT name FROM user WHERE name REGEXP 's1|s2' ORDER BY name; -- s123, s2, s234 |
使用|功能上类似于SELECT中的OR语句。多个OR语句可以使用正则表达式替代，更简洁。
匹配几个字符之一
SELECT name FROM user WHERE name REGEXP 's[1238]' ORDER BY name; -- s123, s2, s234, s89 |
相当于
SELECT name FROM user WHERE name REGEXP 's1|s2|s3|s8' ORDER BY name; |
也可以添加^，来匹配除指定以外的内容
SELECT name FROM user WHERE name REGEXP 's[^1238]' ORDER BY name; -- s4, s5 |
匹配范围
SELECT name FROM user WHERE name REGEXP 's[1-8]' ORDER BY name; -- s123, s2, s89.. |
匹配特殊字符
为了匹配特殊字符，必须用\为前导。
SELECT name FROM user WHERE name REGEXP 's\\-' ORDER BY name; -- s-5 |
\\也用来引用具有特殊含义的字符
| 特殊字符 | 含义 |
|---|---|
| \f | 换页 |
| \n | 换行 |
| \r | 回车 |
| \t | 制表 |
| \v | 纵向制表 |
多数正则表达式使用\转义特殊字符，以便能使用这些字符本身。但MySQL要求用\\。
MySQL解释一个，正则表达式解释另外一个。
匹配字符类
为了方便工作，可以使用预定义的字符集
| 类 | 说明 |
|---|---|
| [:alnum:] | 任意字母和数字 ([a-zA-Z0-9]) |
| [:alpha:] | 任意字符 ([a-zA-Z]) |
| [:blank:] | 空格和指标 (\t) |
| [:cntrl:] | ASCII控制字符 (ASCII 0~31, 127) |
| [:digit:] | 任意数字 ([0-9]) |
| [:graph:] | 与[:print:]相同，但不包括空格 |
| [:lower:] | 任意小写字母 ([a-z]) |
| [:print:] | 任意可打印的数字 |
| [:punct:] | 同时不在[:alnum:][:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符 ([\t\n\r\t\v]) |
| [:upper:] | 任意大写字母 ([A-Z]) |
| [:xdigit:] | 任意十六进制数字 ([a-fA-F0-9]) |
SELECT name FROM user WHERE name REGEXP '[[:alpha:]]1' ORDER BY name; -- h1, m1, s123 |
匹配多个实例
| 字符 | 说明 |
|---|---|
| * | 0个或多个匹配 |
| + | 1个或多个匹配 ({1, }) |
| ? | 0个或1个匹配 ({0, 1}) |
| {n} | 指定数目的匹配 |
| {n, } | 不少于指定数目的匹配 |
| {n, m} | 匹配数目范围, m 不超过255 |
SELECT name FROM user WHERE name REGEXP '[[:digit:]]{4}' ORDER BY name; -- s4444, 21111 |
SELECT name FROM user WHERE name REGEXP '\\([0-9] sticks?\\)' ORDER BY name; -- 1 stick, 4 sticks （s后的？使s可选） |
定位符
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
示例1：假设要找到以字母开头的用户名
SELECT name FROM user WHERE name REGEXP '[a-zA-Z]'; |
以上语句将会在文本任意位置进行查找匹配，并不符合以字母开头这依规定. 这里可以使用^
SELECT name FROM user WHERE name REGEXP '^[a-zA-Z]'; |
^的双重用途 ^有两种用法，在集合中（用 [ 和 ] 定义），用它来否定该集合，否则，用来指串的开始处。
简单的正则表达式测试
SELECT 'hello' REGEXP '[0-9]'; -- 返回0 |
拼接 CONCAT
表中含有id, name字段，应用程序需要这样的格式 name(id)
SELECT CONCAT(name, '(', id, ')') FROM user LIMIT 1; -- s123 (1000001) |
RTRIM()函数去掉了值右边的所有空格。其余有LTRIM(), TRIM()
SELECT CONCAT(RTRIM(name), '(', id, ')') FROM user LIMIT 1; -- s123(1000001) |
MySQL算术运算符(+ - * /)
测试计算
SELECT Trim(' abc '); |
字符串函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
| CHAR_LENGTH(S) | 返回字符串s字符数 | SELECT CHAR_LENGTH(‘abc 你好’); | 6 |
| LENGTH(S) | 返回字符串s的长度 | SELECT LENGTH(‘abc 你好’); | 10 |
| CONCAT(S1,S2,…) | 合并为一个字符串 | SELECT CONCAT(‘hello’, ’ abc’); | hello abc |
| CONCAT_WS(x, s1, s2,…) | 同CONCAT,但会加上x | SELECT CONCAT_WS(’+’, ‘1’, ‘2’, ‘3’); | 1+2+3 |
| INSERT(s1, x, length, s2) | 将字符串s2替换s1的x位置开始长度为length的字符串 | SELECT INSERT(‘abcdefg’, 2, 3, ‘123’); | a123efg |
| UPPER(s) | 将字符串s的所有字母变成大写字母 | SELECT UPPER(‘abcd’); | ABCD |
| LOWER(s) | 将字符串s的所有字母变成小写字母 | SELECT LOWER(‘ABCD’); | abcd |
| LEFT(s, n) | 返回字符串s的前n个字符 | SELECT LEFT(‘abcdef’, 3); | abc |
| RIGHT(s, n) | 返回字符串s的后n个字符 | SELECT RIGHT(‘abcdef’, 3); | def |
| LPAD(s1, length, s2) | 字符串s2来填充s1的开始处，使字符串长度达到length | SELECT LPAD(‘abc’, 8, ‘123’); | 12312abc |
| RPAD(s1, length, s2) | 字符串s2来填充s1的结尾处，使字符串的长度达到length | SELECT RPAD(‘abc’, 8, ‘123’); | abc12312 |
| LTRIM(s) | 去掉字符串s开始处的空格 | SELECT LTRIM(’ abc ‘); | ‘abc ’ |
| RTRIM(s) | 去掉字符串s结尾处的空格 | SELECT RTRIM(’ abc ‘); | ’ abc’ |
| TRIM(s) | 去掉字符串s开始和结尾处的空格 | SELECT TRIM(’ abc ‘); | ‘abc’ |
| TRIM(s1 FROM s) | 去掉字符串s中开始处和结尾处的字符串s1 | SELECT TRIM(’-’ FROM ‘—hello–’); | hello |
| REPEAT(s, n) | 将字符串s重复n次 | SELECT REPEAT(‘abc’, 3); | abcabcabc |
| SPACE(n) | 返回n个空格 | SELECT SPACE(3); | ’ ’ |
| REPLACE(s, s1, s2) | 将字符串s2替代字符串s中的字符串s1 | SELECT REPLACE(‘abcdef’, ‘abc’, ‘12’); | 12def |
| STRCMP(s1, s2) | 比较字符串s1和s2 | SELECT STRCMP(‘abc’, ‘abc’); | 0 |
| STRCMP(s1, s2) | 比较字符串s1和s2 | SELECT STRCMP(‘abc’, ‘abcd’); | -1 |
| STRCMP(s1, s2) | 比较字符串s1和s2 | SELECT STRCMP(‘abc’, ‘ab’); | 1 |
| SUBSTRING(s, n, length) | 获取从字符串s中的第n个位置开始长度为length的字符串 | SELECT SUBSTRING(‘abcdefg’, 2, 3); | bcd |
| MID(s, n, length) | 同SUBSTRING | SELECT MID(‘abcdefg’, 3, 2); | cd |
| LOCATE(s1, s) | 从字符串s中获取s1的开始位置 | SELECT LOCATE(‘de’, ‘abcdefg’); | 4 |
| POSITION(s1, s) | 从字符串s中获取s1的开始位置 | SELECT POSITION(‘de’ IN ‘abcdefg’); | 4 |
| INSTR(s, s1) | 从字符串s中获取s1的开始位置 | SELECT INSTR(‘abcdefg’, ‘de’); | 4 |
| REVERSE(s) | 将字符串s的顺序反过来 | SELECT REVERSE(‘a,b,c,d,e,f’); | f,e,d,c,b,a |
| ELT(n, s1, s2, …) | 返回第n个字符串 | SELECT ELT(3, ‘abc’, ‘def’, ‘ghi’, ‘jkl’); | ghi |
| EXPORT_SET(…) | 见示例 | SELECT EXPORT_SET(6, ‘y’, ‘n’, ‘_’, 3); | n_y_y |
| FIELD(s, s1, s2, …) | 返回第一个与字符串s匹配的字符串位置 | SELECT FIELD(‘b’, ‘a’, ‘b’, ‘c’); | 2 |
| FIND_IN_SET(str, str_list) | 见示例 | SELECT FIND_IN_SET(‘4’, ‘6,5,4,3,2,1’); | 3 |
| SOUNDEX() | 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法 | Y Lee 和 Y Lie 发音相似 |
日期时间函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
| CURDATE(), CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE(); | 2017-05-11 |
| CURTIME(), CURRENT_TIME | 返回当前时间 | SELECT CURRENT_TIME(); | 19:01:11 |
| NOW() | 返回当前日期和时间 | SELECT NOW(); | 2017-05-11 19:01:30 |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 | 同上 | |
| LOCALTIME() | 返回当前日期和时间 | 同上 | |
| SYSDATE() | 返回当前日期和时间 | 同上 | |
| LOCALTIMESTAMP() | 返回当前日期和时间 | 同上 | |
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 | SELECT UNIX_TIMESTAMP(); | 1494500521 |
| UNIX_TIMESTAMP(d) | 将时间d以UNIX时间戳的形式返回 | SELECT UNIX_TIMESTAMP(‘2017-05-11 19:02:01’); | 1494500521 |
| FROM_UNIXTIME(d) | 将UNIX时间戳的时间转换为普通格式的时间 | SELECT FROM_UNIXTIME(1494500521); | 2017-05-11 19:02:01 |
| UTC_DATE() | 返回UTC日期 | SELECT UTC_DATE(); | 2017-05-11 |
| UTC_TIME() | 返回UTC时间 | SELECT UTC_TIME(); | 11:06:13 |
| MONTH(d) | 返回日期d中的月份值，1->12 | SELECT MONTH(‘2017-05-11’); | 5 |
| MONTHNAME(d) | 返回日期当中的月份名称 | SELECT MONTHNAME(‘2017-05-11’); | May |
| DAYNAME(d) | 返回日期d是星期几 | SELECT DAYNAME(‘2017-05-11 19:07:12’); | Thursday |
| DAYOFWEEK(d) | 日期d今天是星期几，1星期日，2星期一 | SELECT DAYOFWEEK(‘2017-05-11’); | 5 |
| WEEKDAY(d) | 日期d今天是星期几，0表示星期一，1表示星期二 | SELECT WEEKDAY(‘2017-05-11’); | 3 |
| WEEK(d)，WEEKOFYEAR(d) | 计算日期d是本年的第几个星期，范围是0->53 | SELECT WEEK(‘2017-05-11’); | 19 |
| DAYOFYEAR(d) | 计算日期d是本年的第几天 | SELECT DAYOFYEAR(‘2017-05-11’); | 131 |
| DAYOFMONTH(d) | 计算日期d是本月的第几天 | SELECT DAYOFMONTH(‘2017-05-11’); | 11 |
| QUARTER(d) | 返回日期d是第几季节，返回1->4 | SELECT QUARTER(‘2017-05-11’); | 2 |
| HOUR(t) | 返回t中的小时值 | SELECT HOUR(‘2017-05-11 19:11:23’); | 19 |
| MINUTE(t) | 返回t中的分钟值 | SELECT MINUTE(‘2017-05-11 19:11:23’); | 11 |
| SECOND(t) | 返回t中的秒钟值 | SELECT SECOND(‘2017-05-11 19:11:23’); | 23 |
| EXTRACT(type FROM d) | 从日期d中获取指定的值，type指定返回的值(见下文) | SELECT EXTRACT(WEEK FROM ‘2017-05-11 19:11:23’); | 19 |
| TIME_TO_SEC(t) | 将时间t转换为秒 | SELECT TIME_TO_SEC(‘19:11:23’); | 69083 |
| SEC_TO_TIME(s) | 将以秒为单位的时间s转换为时分秒的格式 | SELECT SEC_TO_TIME(69083); | 19:11:23 |
| TO_DAYS(d) | 计算日期d距离0000年1月1日的天数 | SELECT TO_DAYS(‘2017-05-11 19:11:23’); | 736825 |
| FROM_DAYS(n) | 计算从0000年1月1日开始n天后的日期 | SELECT FROM_DAYS(736825); | 2017-05-11 |
| DATEDIFF(d1,d2) | 计算日期d1->d2之间相隔的天数 | SELECT DATEDIFF(‘2017-05-11’, ‘2017-05-12’); | -1 |
| ADDDATE(d,n) | 计算其实日期d加上n天的日期 | SELECT ADDDATE(‘2017-05-11 19:11:23’, 3); | 2017-05-14 19:11:23 |
| ADDDATE(d，INTERVAL expr type) | 计算起始日期d加上一个时间段后的日期 | SELECT ADDDATE(‘2017-05-11 19:11:23’, INTERVAL 3 HOUR); | 2017-05-11 22:11:23 |
| DATE_ADD(d,INTERVAL expr type) | 同上 | SELECT DATE_ADD(‘2017-05-11 19:11:23’, INTERVAL 10 HOUR); | 2017-05-12 05:11:23 |
| SUBDATE(d,n) | 日期d减去n天后的日期 | SELECT SUBDATE(‘2017-05-12 05:11:23’, 13); | 2017-04-29 05:11:23 |
| SUBDATE(d,INTERVAL expr type) | 日期d减去一个时间段后的日期 | SELECT SUBDATE(‘2017-04-29 05:11:23’, INTERVAL 10 MINUTE); | 2017-04-29 05:01:23 |
| ADDTIME(t,n) | 时间t加上n秒的时间 | SELECT ADDTIME(‘2017-04-29 05:01:23’, 30); | 2017-04-29 05:01:53 |
| SUBTIME(t,n) | 时间t减去n秒的时间 | SELECT SUBTIME(‘2017-04-29 05:01:53’, 30); | 2017-04-29 05:01:23 |
| DATE_FORMAT(d,f) | 按表达式f的要求显示日期d | SELECT DATE_FORMAT(‘2017-04-29 05:01:23’, ‘%Y-%m-%d’); | 2017-04-29 |
| TIME_FORMAT(t,f) | 按表达式f的要求显示时间t | SELECT TIME_FORMAT(‘2017-04-29 05:01:23’, ‘%r’); | 05:01:23 AM |
DATE_ADD(date,INTERVAL expr type) 函数向日期添加指定的时间间隔。 |
type的值可以为:
MICROSECOND |
案例实践：
SELECT * FROM orders WHERE Date(order_date)='2022-02-26'; |
数值处理函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
| ABS(x) | 返回x的绝对值 | ||
| CEIL(x) | 返回大于或等于x的最小整数 | ||
| CEILING(x) | 返回大于或等于x的最小整数 | ||
| FLOOR(x) | 返回小于或等于x的最大整数 | ||
| RAND() | 返回0->1的随机数 | ||
| RAND(x) | 返回0->1的随机数，x值相同时返回的随机数相同 | ||
| SIGN(x) | 返回x的符号，x是负数、0、正数分别返回-1、0和1 | ||
| PI() | 返回圆周率(3.141593） | ||
| TRUNCATE(x, y) | 返回数值x保留到小数点后y位的值(不会四舍五入) | ||
| ROUND(x) | 返回离x最近的整数 | ||
| ROUND(x, y) | 保留x小数点后y位的值(四舍五入) | ||
| POW(x, y) | 返回x的y次方 | ||
| POWER(x, y) | 返回x的y次方 | ||
| SQRT(x) | 返回x的平方根 | ||
| EXP(x) | 返回e的x次方 | ||
| MOD(x, y) | 返回x除以y以后的余数 | ||
| LOG(x) | 返回自然对数(以e为底的对数) | ||
| LOG10(x) | 返回以10为底的对数 | ||
| RADIANS(x) | 将角度转换为弧度 | ||
| DEGREES(x) | 将弧度转换为角度 | ||
| SIN(x) | 求正弦值(参数是弧度) | ||
| ASIN(x) | 求反正弦值(参数是弧度) | ||
| COS(x) | 求余弦值(参数是弧度) | ||
| ACOS(x) | 求反余弦值(参数是弧度) | ||
| TAN(x) | 求正切值(参数是弧度) | ||
| ATAN(), ATAN2() | 求反正切值(参数是弧度) | ||
| COT() | 求余切值(参数是弧度) |
条件判断函数
IF (expr, v1, v2);
SELECT IF (1 > 0, 'Y', 'N'); -- Y |
IFNULL(v1, v2);
如果v1不为NULL, 返回v1，否则返回v2
SELECT IFNULL('a', 'b'); -- a |
系统信息函数
| 函数 | 说明 | 示例 | 结果 |
|---|---|---|---|
| VERSION() | 返回数据库的版本号 | SELECT VERSION(); | 5.7.11 |
| CONNECTION_ID() | 返回服务器的连接数 | SELECT CONNECTION_ID(); | 13 |
| DATABASE() | 返回当前数据库名 | SELECT DATABASE(); | database-learn |
| USER() | 返回当前用户 | SELECT USER(); | root@localhost |
| CHARSET(s) | 返回字符串s的字符集 | SELECT CHARSET(“123”); | utf8 |
| COLLATION(s) | 返回字符串s的字符排列方式 | SELECT COLLATION(“a123”); | utf8_general_ci |
| LAST_INSERT_ID() | 返回最近生成的AUTO_INCREMENT值 | SELECT LAST_INSERT_ID(); | 0 |
聚集函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
SELECT SUM(price*quantity) AS total_price FROM orderitems; |
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products; |
数据分组
假设要获取用户的充值次数，最低充值额度，最高充值额度，平均充值额度，可以用以下命令:
SELECT role_id, |
以上得出的是总的信息，如果要获取每个用户的这些信息，就可以使用分组了。
SELECT |
需要注意的是，GROUP BY 必须出现在 WHERE 之后，ORDER BY 之前
可以使用WITH ROLLUP得到汇总的值
SELECT |
以上在在结果的最后，会附上总的结果。
分组过滤HAVING
假设只需要得到充值2次(包含)以上用户的数据，则需要使用HAVING来过滤。
SELECT role_id, |
注意HAVING跟GROUP BY后面。
也可以同时使用WHERE和HAVING。
SELECT role_id, |
以上通过WHERE新增了条件，2017年以来充值的。
当sql_mode为ONLY_FULL_GROUP_BY需要注意
查看sql_mode值
SELECT @@sql_mode; |
结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
在sql_mode=ONLY_FULL_GROUP_BY的模式下，以下句子报错
SELECT role_id, money FROM user_pay GROUP BY role_id; |
错误: SELECT list is not in GROUP BY clause and contains nonaggregated column ...
**表中的列**，出现在SELECT中时，也得出现在GROUP BY中。
SELECT role_id, money FROM user_pay GROUP BY role_id, money; |
SELECT字句顺序
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT |
做为计算字段使用子查询
SELECT |
ANSI SQL规范首选INNER JOIN。代替没有关键字的联结
INSERT 和 INTO 之间添加关键字LOW_PRIORITY,降低优先级
INSERT LOW_PRIORITY ITNO |
单条INSERT语句处理多个插入比使用多条INSERT语句快
IGNORE关键字，即使发生错误，也继续进行更新
UPDATE IGNORE customers... |
SELECT last_insert_id() 返回最后一个自增id
MySQL 不允许使用函数作为默认值
使用默认值而不是null值，特别是对用于计算或数据分组的列更是如此
InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
MEMORY在功能等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合临时表）
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
http://dev.mysql.com/doc/refman/5.0/en/storage_engines.html
创建视图
CREATE OR REPLACE VIEW user_pay_info AS |
创建一个视图，该视图从用户表(user), 用户充值表(user_pay)获取用户基本信息，总充值额度。
创建存储过程CREATE PROCEDURE
CREATE PROCEDURE user_pay_total() |
DELIMITER // |
以上DELIMITER告诉命令行工具，使用//做为分隔符。最后一句恢复回;做为分隔符。
CALL user_pay_total(); -- 使用存储过程 CALL |
存储过程使用参数
参数可以用IN, OUT, INOUT修饰。
例1：
CREATE PROCEDURE productpricing( |
使用：
CALL productpricing(@pricelow, |
例2：
CREATE PROCEDURE ordertotal( |
CALL ordertotal(2005,@total); |
建立智能存储过程案例
你现在需要获得与以前一样的订单合计，但是，需要对合计增加营业税，不过，只是针对某些顾客(或许是你所在的城市的那些顾客)。那么，你需要做
1）获得合计(与以前一样)；
2）把营业税有条件的添加到合计中；
3）返回合计(带或不带税)；
-- Name：ordertotal |
使用：
CALL ordertotal（20005, 0, @total); |
检测存储过程
SHOW CREATE PROCEDURE user_pay_info; |
《高性能MySQL》读书笔记
