INSERT

官方文档：https://dev.mysql.com/doc/refman/5.7/en/insert.html

  1. mysql> insert into t1 values(1);  -- 插入一个值
  2. Query OK, 1 row affected (0.03 sec)
  4. mysql> insert into t1 values(2),(3),(-1);  -- 插入多个值，MySQL独有
  5. Query OK, 3 rows affected (0.03 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  8. mysql> insert into t1 select 8;   -- insert XXX select XXX 语法，MySQ独有
  9. Query OK, 1 row affected (0.02 sec)
  10. Records: 1  Duplicates: 0  Warnings: 0
  12. mysql> create table t3 (a int, b int); -- 有多个列
  13. Query OK, 0 rows affected (0.15 sec)
  15. mysql> insert into t3 select 8;  -- 没有指定列，报错
  16. ERROR 1136 (21S01): Column count does not match value count at row 1
  18. mysql> insert into t3(a) select 8;  -- 指定列a
  19. Query OK, 1 row affected (0.04 sec)
  20. Records: 1  Duplicates: 0  Warnings: 0
  22. mysql> insert into t3 select 8, 9;  -- 不指定列，但是插入值匹配列的个数和类型
  23. Query OK, 1 row affected (0.03 sec)
  24. Records: 1  Duplicates: 0  Warnings: 0
  26. mysql> select * from t3;
  27. +------+------+
  28. | a    | b    |
  29. +------+------+
  30. |    8 | NULL |
  31. |    8 |    9 |
  32. +------+------+
  33. 2 rows in set (0.00 sec)
  35. mysql> insert into t3(b) select a from t2;  -- 从t2表中查询数据并插入到t3(a)中，注意指定列
  36. Query OK, 3 rows affected (0.03 sec)
  37. Records: 3  Duplicates: 0  Warnings: 0
  39. mysql> select * from t3;
  40. +------+------+
  41. | a    | b    |
  42. +------+------+
  43. |    8 | NULL |
  44. |    8 |    9 |
  45. | NULL |    5 |
  46. | NULL |    4 |
  47. | NULL |    3 |
  48. +------+------+
  49. 5 rows in set (0.00 sec)
  51. --
  52. -- 如果想快速增长表格中的数据，可以使用如下方法，使得数据成倍增长
  53. --
  54. mysql> insert into t3 select * from t3;
  55. Query OK, 5 rows affected (0.03 sec)  -- 插入了5列
  56. Records: 5  Duplicates: 0  Warnings: 0
  58. mysql> select * from t3;               
  59. +------+------+
  60. | a    | b    |
  61. +------+------+
  62. |    8 | NULL |
  63. |    8 |    9 |
  64. | NULL |    5 |
  65. | NULL |    4 |
  66. | NULL |    3 |
  67. |    8 | NULL |
  68. |    8 |    9 |
  69. | NULL |    5 |
  70. | NULL |    4 |
  71. | NULL |    3 |
  72. +------+------+
  73. 10 rows in set (0.00 sec)
  75. mysql> insert into t3 select * from t3;
  76. Query OK, 10 rows affected (0.03 sec)  -- 插入了10列，成倍增长
  77. Records: 10  Duplicates: 0  Warnings: 0
  79. mysql> select * from t3;               
  80. +------+------+
  81. | a    | b    |
  82. +------+------+
  83. |    8 | NULL |
  84. |    8 |    9 |
  85. | NULL |    5 |
  86. | NULL |    4 |
  87. | NULL |    3 |
  88. |    8 | NULL |
  89. |    8 |    9 |
  90. | NULL |    5 |
  91. | NULL |    4 |
  92. | NULL |    3 |
  93. |    8 | NULL |
  94. |    8 |    9 |
  95. | NULL |    5 |
  96. | NULL |    4 |
  97. | NULL |    3 |
  98. |    8 | NULL |
  99. |    8 |    9 |
  100. | NULL |    5 |
  101. | NULL |    4 |
  102. | NULL |    3 |
  103. +------+------+
  104. 20 rows in set (0.00 sec)

UPDATE

官方文档：https://dev.mysql.com/doc/refman/5.7/en/update.html

  1. mysql> insert into t3 select 1,2;
  2. Query OK, 1 row affected (0.03 sec)
  3. Records: 1  Duplicates: 0  Warnings: 0
  5. mysql> select * from t3;
  6. +------+------+
  7. | a    | b    |
  8. +------+------+
  9. |    1 |    2 |
  10. +------+------+
  11. 1 row in set (0.00 sec)
  13. mysql> update t3 set a=10 where a=1;
  14. Query OK, 1 row affected (0.03 sec)
  15. Rows matched: 1  Changed: 1  Warnings: 0
  17. mysql> select * from t3;
  18. +------+------+
  19. | a    | b    |
  20. +------+------+
  21. |   10 |    2 |
  22. +------+------+
  23. 1 row in set (0.00 sec)
  25. --
  26. -- 关联后更新
  27. --
  28. mysql> select * from t1;
  29. +------+
  30. | a    |
  31. +------+
  32. |   10 |
  33. |    4 |  -- 和t2中的4相等
  34. |    1 |
  35. |    2 |
  36. |    3 |  -- 和t2中的3相等
  37. |   -1 |
  38. |    8 |
  39. +------+
  40. 7 rows in set (0.00 sec)
  42. mysql> select * from t2;
  43. +------+
  44. | a    |
  45. +------+
  46. |    5 |
  47. |    4 |  -- 和t1中的4相等
  48. |    3 |  -- 和t1中的3相等
  49. +------+
  50. 3 rows in set (0.00 sec)
  52. mysql> update t1 join t2 on t1.a = t2.a set t1.a=100;  -- 先得到t1.a=t2.a的结果集
  53.                                                        -- 然后将结果集中的t1.a设置为100
  54. Query OK, 2 rows affected (0.03 sec)
  55. Rows matched: 2  Changed: 2  Warnings: 0
  57. mysql> select * from t1;
  58. +------+
  59. | a    |
  60. +------+
  61. |   10 |
  62. |  100 |  -- 该行被更新成100
  63. |    1 |
  64. |    2 |
  65. |  100 |  -- 该行被更新成100
  66. |   -1 |
  67. |    8 |
  68. +------+
  69. 7 rows in set (0.00 sec)

DELETE

官方文档：https://dev.mysql.com/doc/refman/5.7/en/delete.html

  1. mysql> delete from t3 where a is null;  -- 根据过滤条件删除
  2. Query OK, 12 rows affected (0.03 sec)
  4. mysql> select * from t3;               
  5. +------+------+
  6. | a    | b    |
  7. +------+------+
  8. |    8 | NULL |
  9. |    8 |    9 |
  10. |    8 | NULL |
  11. |    8 |    9 |
  12. |    8 | NULL |
  13. |    8 |    9 |
  14. |    8 | NULL |
  15. |    8 |    9 |
  16. +------+------+
  17. 8 rows in set (0.00 sec)
  19. mysql> delete from t3;   -- 删除整个表
  20. Query OK, 8 rows affected (0.03 sec)
  22. mysql> select * from t3;
  23. Empty set (0.00 sec)

REPLACE

官方文档：https://dev.mysql.com/doc/refman/5.7/en/replace.html

  1. mysql> create table t4(a int primary key auto_increment, b int);
  2. Query OK, 0 rows affected (0.15 sec)
  4. mysql> insert into t4 values(NULL, 10);
  5. Query OK, 1 row affected (0.02 sec)
  7. mysql> insert into t4 values(NULL, 11);
  8. Query OK, 1 row affected (0.03 sec)
  10. mysql> insert into t4 values(NULL, 12);
  11. Query OK, 1 row affected (0.03 sec)
  13. mysql> select * from t4;
  14. +---+------+
  15. | a | b    |
  16. +---+------+
  17. | 1 |   10 |
  18. | 2 |   11 |
  19. | 3 |   12 |
  20. +---+------+
  21. 3 rows in set (0.00 sec)
  23. mysql> insert into t4 values(1, 100);  -- 报错，说存在重复的主键记录 "1"
  24. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  26. mysql> replace into t4 values(1, 100); -- 替换该主键对应的值  
  27. Query OK, 2 rows affected (0.03 sec)   -- 两行记录受到影响
  29. mysql> select * from t4;
  30. +---+------+
  31. | a | b    |
  32. +---+------+
  33. | 1 |  100 |  -- 已经被替换
  34. | 2 |   11 |
  35. | 3 |   12 |
  36. +---+------+
  37. 3 rows in set (0.00 sec)
  38. -----
  39. -- replace的原理是：先delete，再insert
  40. -----
  42. mysql> replace into t4 values(5, 50);  -- 没有替换对象时，类似插入效果
  43. Query OK, 1 row affected (0.03 sec)    -- 只影响1行
  45. mysql> select * from t4;
  46. +---+------+
  47. | a | b    |
  48. +---+------+
  49. | 1 |  100 |
  50. | 2 |   11 |
  51. | 3 |   12 |
  52. | 5 |   50 |  -- 插入了1行
  53. +---+------+
  54. 4 rows in set (0.00 sec)
  56. --
  57. -- replace原理更明显的例子 
  58. --
  60. mysql> create table t6 
  61.     -> (a int primary key, 
  62.     -> b int auto_increment,   -- b是auto_increment的int型数据
  63.     -> c int, key(b));
  64. Query OK, 0 rows affected (0.15 sec)
  66. mysql> insert into t6 values(10, NULL, 100),(20,NULL,200);  -- b自增长
  67. Query OK, 2 rows affected (0.02 sec)
  68. Records: 2  Duplicates: 0  Warnings: 0
  70. mysql> select * from t6;
  71. +----+---+------+
  72. | a  | b | c    |
  73. +----+---+------+
  74. | 10 | 1 |  100 |  -- b为1
  75. | 20 | 2 |  200 |  -- b为2
  76. +----+---+------+
  77. 2 rows in set (0.00 sec)
  79. mysql> replace into t6 values(10,NULL,150);  -- 将a=10的替换掉
  80. Query OK, 2 rows affected (0.03 sec)
  82. mysql> select * from t6;
  83. +----+---+------+
  84. | a  | b | c    |
  85. +----+---+------+
  86. | 10 | 3 |  150 |  -- 替换后b从1变成了3，说明是先删除，再插入
  87. | 20 | 2 |  200 |
  88. +----+---+------+
  89. 2 rows in set (0.00 sec)
  91. -----
  93. --
  94. -- insert on duplicate效果和replace类似
  95. --
  96. mysql> insert into t4 values(1,1);  -- 插入报错，存在key为1的记录
  97. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  99. mysql> insert into t4 values(1,1) on duplicate key update b=1;  -- 带上on duplicate参数
  100.                                                                 -- 非SQL标准，不推荐
  101. Query OK, 2 rows affected (0.03 sec)
  103. mysql> select * from t4;
  104. +---+------+
  105. | a | b    |
  106. +---+------+
  107. | 1 |    1 |  -- 该行的b列从100被替换成1
  108. | 2 |   11 |
  109. | 3 |   12 |
  110. | 5 |   50 |
  111. +---+------+
  113. --
  114. -- insert ignore
  115. --
  116. mysql> insert ignore into t4 values(1,1);   -- 忽略重复的错误
  117. Query OK, 0 rows affected, 1 warning (0.00 sec)
  119. mysql> show warnings;
  120. +---------+------+---------------------------------------+
  121. | Level   | Code | Message                               |
  122. +---------+------+---------------------------------------+
  123. | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
  124. +---------+------+---------------------------------------+
  125. 1 row in set (0.00 sec)

其他知识点

更新有关系的值

  1. mysql> create table t5 (a int, b int);
  2. Query OK, 0 rows affected (0.14 sec)
  4. mysql> insert into t5 values(1,1);
  5. Query OK, 1 row affected (0.03 sec)
  7. mysql> select * from t5;
  8. +------+------+
  9. | a    | b    |
  10. +------+------+
  11. |    1 |    1 |
  12. +------+------+
  13. 1 row in set (0.00 sec)
  15. mysql> update t5 set a=a+1, b=a where a=1;
  16. Query OK, 1 row affected (0.02 sec)
  17. Rows matched: 1  Changed: 1  Warnings: 0
  19. mysql> select * from t5;
  20. +------+------+
  21. | a    | b    |
  22. +------+------+
  23. |    2 |    2 |  -- SQL Server和Oracle中得到的值是2, 1
  24. +------+------+
  25. 1 row in set (0.00 se

显示行号（RowNumber）

  1. --
  2. -- 方法一
  3. --
  4. mysql> use employees ;
  5. Reading table information for completion of table and column names
  6. You can turn off this feature to get a quicker startup with -A
  8. Database changed
  9. mysql> set @rn:=0;  -- 产生 SESSION(会话)级别的变量
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> select @rn:=@rn+1 as rownumber, emp_no, gender from employees limit 10;  -- ：= 是赋值的意思
  13. +-----------+--------+--------+
  14. | rownumber | emp_no | gender |
  15. +-----------+--------+--------+
  16. |        11 |  10001 | M      |
  17. |        12 |  10002 | F      |
  18. |        13 |  10003 | M      |
  19. |        14 |  10004 | M      |
  20. |        15 |  10005 | M      |
  21. |        16 |  10006 | F      |
  22. |        17 |  10007 | F      |
  23. |        18 |  10008 | M      |
  24. |        19 |  10009 | F      |
  25. |        20 |  10010 | F      |
  26. +-----------+--------+--------+
  27. 10 rows in set (0.00 sec)
  29. --
  30. -- 方法二 （推荐）
  31. --
  32. mysql> select @rn1:=@rn1+1 as rownumber, emp_no, gender from employees, (select @rn1:=0) as a limit 10;
  33. +-----------+--------+--------+
  34. | rownumber | emp_no | gender |
  35. +-----------+--------+--------+
  36. |         1 |  10001 | M      |
  37. |         2 |  10002 | F      |
  38. |         3 |  10003 | M      |
  39. |         4 |  10004 | M      |
  40. |         5 |  10005 | M      |
  41. |         6 |  10006 | F      |
  42. |         7 |  10007 | F      |
  43. |         8 |  10008 | M      |
  44. |         9 |  10009 | F      |
  45. |        10 |  10010 | F      |
  46. +-----------+--------+--------+
  47. 10 rows in set (0.00 sec)
  49. -- MySQL 自定义变量，根据每一记录进行变化的
  51. mysql> select @rn1:=0;
  52. +---------+
  53. | @rn1:=0 |
  54. +---------+
  55. |       0 |  -- 只有一行记录
  56. +---------+
  57. 1 row in set (0.00 sec)
  59. -- 相当于把employees和(select @rn1:=0)做了笛卡尔积，然后使用@rn1:=@rn + 1，根据每行进行累加
  61. --
  62. -- ":=" 和 "="
  63. --
  64. mysql> set @a:=1;  -- 赋值为1
  65. Query OK, 0 rows affected (0.00 sec)
  67. mysql> select @a;
  68. +------+
  69. | @a   |
  70. +------+
  71. |    1 |
  72. +------+
  73. 1 row in set (0.00 sec)
  75. mysql> set @a:=10;  -- 赋值为10
  76. Query OK, 0 rows affected (0.00 sec)
  78. mysql> select @a;
  79. +------+
  80. | @a   |
  81. +------+
  82. |   10 |
  83. +------+
  84. 1 row in set (0.00 sec)
  86. mysql> select @a=9;  -- 进行比较
  87. +------+
  88. | @a=9 |
  89. +------+
  90. |    0 |  -- 返回为False
  91. +------+
  92. 1 row in set (0.00 sec)
  94. --
  95. -- 作业：通过子查询或者其他方式，计算出employees的行号
  96. --

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/mb1gfl 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。