https://segmentfault.com/a/1190000021673842 https://www.jianshu.com/p/8780996a4b99 MySQL 从入门到实践

MySQL逻辑架构

image.png
ALTER TABLE

  1. -- 在column_name下面新增new_column_name
  2. ALTER TABLE [`db_name`.]`table_name` 
  3.   ADD COLUMN `new_column_name` INT(4) [UNSIGNED DEFAULT 100 NOT NULL] COMMENT '列含义说明' AFTER `column_name`;
  5. -- 修改column_name
  6. ALTER TABLE [`db_name`.]`table_name` 
  7.   CHANGE `column_name` `new_column_name` INT(10) [UNSIGNED NOT NULL] COMMENT '列含义说明';

INSERT INTO

语法

INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

INSERT INTO SELECT

  1. INSERT INTO `[table_name1]` (`field1`,`field2`)
  2. SELECT `field1` ,`field2` FROM `[table_name2]` [WHERE Clause];

REPLACE INTO

语法

REPLACE INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

REPLACE INTO SELECT

  1. REPLACE INTO `[table_name1]` (`field1`,`field2`)
  2. SELECT `field1` ,`field2` FROM `[table_name2]` [WHERE Clause];

UPDATE

语法

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

UPDATE SELECT GROUP

  1. UPDATE `[table_name1]` a 
  2.     JOIN (
  3.         SELECT b.`id` AS id,SUM(`amount`) AS amount FROM `[table_name1]` a 
  4.             JOIN `[table_name2]` d ON a.`id` = b.`id`
  5.         GROUP BY b.`id`
  6.     ) b ON a.`id` = b.`id`
  7. SET a.`max_receive_rate`=b.`max_receive_rate`
  8. [WHERE Clause];

DELETE

语法

DELETE FROM table_name [WHERE Clause]

SELECT

语法

SELECT [SELECT 选项] 字段列表[字段别名] FROM 数据源 [WHERE 条件字句] [GROUP BY 字句] [HAVING 字句] [ORDER BY 字句] [LIMIT 字句]

LIMIT

  1. -- 例如：取表中的第5条到第7条的三条数据
  2. SELECT * FROM 表名 LIMIT 4,3
  3. -- 说明：4表示从第四条开始取，不包含第四条，3表示取条数据
  4. -- 如果要取第一条数据到第5条就是
  5. SELECT * FROM 表名 LIMIT 0,5
  6. -- 如果要取一条数据就是
  7. SELECT * FROM 表名 LIMIT 1

DISTINCT

SELECT DISTINCT [SELECT 选项] FROM 数据源

复制表结构及其数据

CREATE TABLE table_name_new
AS SELECT * FROM table_name_old

仅复制表结构

CREATE TABLE table_name_new
AS SELECT * FROM table_name_old WHERE 1=2; 或者： CREATE TABLE table_name_new
LIKE table_name_old

仅复制表数据

两个表结构一致： INSERT INTO table_name_new
SELECT * FROM table_name_old 如果两个表结构不一样： INSERT INTO table_name_new(column1,column2…)
SELECT column1,column2… FROM table_name_old

BETWEEN

SELECT column_name(s) FROM table_name WHERE column_name [NOT] BETWEEN value1 AND value2;

UNION

UNION 语法：

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2; 注：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION ALL 语法：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2; 注：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

JOIN

语法

image.png

CONCAT

CONCAT()

功能：将多个字符串连接成一个字符串。 语法：CONCAT(str1, str2,…) 注：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

CONCAT_WS()

功能：与CONCAT()相似，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator） 语法：CONCAT_WS(SEPARATOR, str1, str2, …) 注：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

GROUP_CONCAT()

功能：将GROUP BY产生的同一个分组中的值连接起来，返回一个字符串结果。 语法：GROUP_CONCAT( [DISTINCT] 要连接的字段 [ORDER BY 排序字段 ASC/DESC ] [SEPARATOR ‘分隔符’] ) 注：通过使用DISTINCT可以排除重复值；如果希望对结果中的值进行排序，可以使用ORDER BY子句；SEPARATOR是一个字符串值，缺省为一个逗号。

当数据太大，group_concat超出了默认值1024，超过就会截断，group_concat查询出来的数据就是不全。
解决：

  1. 查看当前 mysql group_concat_max_len：

    show variables like ‘group_concat_max_len’;

结果如下：
image.png
可以看到当前值为1024.

  1. 在MySQL配置文件中添加配置：group_concat_max_len = -1 （-1为最大值或根据实际需求设置长度），配置后需要重启MySQL服务

如果是生产环境下，不能擅自重启MySQL服务，则可以通过语句设置group_concat的作用范围，如：

set session group_concat_max_len = -1

DATEDIFF

功能：将多个字符串连接成一个字符串。
语法：DATEDIFF(date1, date2)

  1. -- 返回两个日期间隔天数
  2. SELECT DATEDIFF("2017-01-01", "2016-12-24"); -- 8
  3. SELECT DATEDIFF("2017-06-25 09:34:21", "2017-06-15 15:25:35"); -- 10

DATE_FORMAT

功能：将多个字符串连接成一个字符串。
语法：DATE_FORMAT(date, format)

  1. SET @dt = '2008-08-09 12:12:33';
  2. SELECT DATE_FORMAT(@dt, "%Y-%m-%d %H:%i:%s"); -- 2008-08-09 12:12:33
  3. SELECT DATE_FORMAT(@dt, "%M %d %Y");-- August 09 2008
  4. SELECT DATE_FORMAT(@dt, "%W %M %e %Y");-- Saturday August 9 2008

DATE_ADD

功能：将多个字符串连接成一个字符串。
语法：DATE_ADD(date, INTERVAL value addunit) 参数：addunit，必需的。要添加的间隔类型。可以是以下值之一：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
  2. SET @dt = NOW();
  4. SELECT DATE_ADD(@dt, INTERVAL 1 DAY); -- add 1 day
  5. SELECT DATE_ADD(@dt, INTERVAL 1 HOUR); -- add 1 hour
  6. SELECT DATE_ADD(@dt, INTERVAL 1 MINUTE); -- ...
  7. SELECT DATE_ADD(@dt, INTERVAL 1 SECOND);
  8. SELECT DATE_ADD(@dt, INTERVAL 1 MICROSECOND);
  9. SELECT DATE_ADD(@dt, INTERVAL 1 WEEK);
  10. SELECT DATE_ADD(@dt, INTERVAL 1 MONTH);
  11. SELECT DATE_ADD(@dt, INTERVAL 1 QUARTER);
  12. SELECT DATE_ADD(@dt, INTERVAL 1 YEAR);
  14. SELECT DATE_ADD(@dt, INTERVAL -1 DAY); -- sub 1 day
  16. SET @dt = '2008-08-09 12:12:33';
  17. SELECT DATE_ADD(@dt, INTERVAL '01:15:30' HOUR_SECOND); -- 2008-08-09 13:28:03
  18. SELECT DATE_ADD(@dt, INTERVAL '1 01:15:30' DAY_SECOND); -- 2008-08-10 13:28:03
  20. -- 查询当前日期（不含时分秒）
  21. SELECT CURDATE();
  22. SELECT CURRENT_DATE;
  24. -- 查询当前时间（不含日期）
  25. SELECT CURTIME();
  26. SELECT CURRENT_TIME();
  28. -- 获取当前日期时间（含时分秒）
  29. SELECT ADDTIME(NOW(),-TIME(NOW()));
  30. SELECT date_format(NOW(), '%Y-%m-%d 00:00:00');
  32. -- 修改单个时间单位：日期+1天(年=YEAR，月=MONTH,日=DAY,小时=HOUR，分=MINUTE,秒=SECOND)
  33. SELECT date_add(CURDATE(),INTERVAL 1 MINUTE);
  34. -- 修改单个时间单位：日期-1天(年=YEAR，月=MONTH,日=DAY,小时=HOUR，分=MINUTE,秒=SECOND)
  35. SELECT date_add(CURDATE(),INTERVAL -1 SECOND);
  37. -- 修改时分秒：
  38. SELECT ADDTIME(ADDTIME(NOW(),-TIME(NOW())),TIME('2021-02-01 11:22:33'));
  39. SELECT ADDTIME(date_format(NOW(), '%Y-%m-%d 00:00:00'),TIME('2021-02-01 11:22:33'));

DATE_SUB

功能：将多个字符串连接成一个字符串。
语法：DATE_SUB(date, INTERVAL value interval) 参数：interval，必需的。要减去的区间类型。可以是以下值之一：

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
  1. SET @dt = '2008-08-09 12:12:33';
  2. -- 从日期减去 1 天 1 小时 1 分钟 1 秒 并返回日期：
  3. SELECT DATE_SUB(@dt, INTERVAL '1 1:1:1' DAY_SECOND); -- 2008-08-08 11:11:32
  4. -- 从日期减去 15 分钟并返回日期：
  5. SELECT DATE_SUB(@dt, INTERVAL 15 MINUTE); -- 2008-08-09 11:57:33
  6. -- 从日期减去 3 小时并返回日期：
  7. SELECT DATE_SUB(@dt, INTERVAL 3 HOUR); -- 2008-08-09 09:12:33
  8. -- 将 2 个月添加到日期并返回日期：
  9. SELECT DATE_SUB(@dt, INTERVAL -2 MONTH); -- 2008-10-09 12:12:33