Mysql数据的增删改查——Mysql初级(二)

 

前言:

不管你是一个开发工程师还是测试工程师，或是你是运维工程师，假如我们使用的是关系型数据库的话，可能最多使用到的可能就是数据库中的增删改查了(CRUD)。此文是本系列里面的第二篇文章，第一篇文章主要讲解的是：Mysql表结构的增删改查(CRUD)——Mysql初级(一),假如你也感兴趣的话，可以前往查阅，当然也可以提出你宝贵的意见和建议。此文主要致力于Mysql数据层面的增删改查。首先会先讲解MySQL CRUD语法，主要参考资料是Mysql的官方文档(中文版)。其次，会针对性的提供私人定制的基础案列，用以说明问题。然后会讲解在开发的过程中容易忽视的问题分享，以及关于CRUD的一些高级操作。最后总结关于CRUD的注意事项。这就是本文的行文思路，废话不多说，咱们直接上干货吧！

---

数据准备:

1、表结构建立:

-- 1、建立测试表
DROP TABLE IF EXISTS T_MYSQL_CRUD; -- 这种方式是删了重建
CREATE TABLE IF NOT EXISTS T_MYSQL_CRUD(
   id INT UNSIGNED AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、数据插入:

-- 2、添加测试数据
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《C语言》','马云',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《C#语言》','马化腾',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《Java》','李彦宏',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《Mysql》','刘强东',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《演员的自我修养》','周星驰',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《神雕侠侣》','金庸',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《武林外传》','阿猫',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《某海堡垒站》','阿狗',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《Python基础》','粉红',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《Django入门》','*',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《JavaScrppt高级》','马克思',NOW());
INSERT INTO T_MYSQL_CRUD(title,author,date) VALUES('《Spring学习手册》','二哈',NOW());

3、数据概览

 

 一、数据插入(INSERT)

 1、语法分析:

Mysql数据插入的方式有多种，在不同的应用场景下，我们可能使用不同的插入方式，这样即可以最大限度的节省时间又可以提高程序的性能。以下是Mysql数据插入的语法概览，在接下来的时间会分别讲解，稍安勿躁。代码来源于Mysql官方文档。

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

使用INSERT ... VALUES和 INSERT ... SET 语句的形式插入基于明确指定的值的行。 使用INSERT ... SELECT形式插入从另一个或多个表中选择行(这就是常用的SELECT FOR INSERT)。 INSERT使用 ON DUPLICATE KEY UPDATE子句可以使现有行更新，如果要插入的行将导致UNIQUE索引中的重复或PRIMARY KEY冲突。 在MySQL 5.7中，DELAYED关键字被接受，但被服务器忽略。在后续的文章中回具体讨论。

1)、INSERT ... SELECT语法

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

使用INSERT ... SELECT，您可以根据SELECT 语句的结果快速地将多行插入到表中，该语句可以从一个或多个表中进行选择。例如：

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

在使用INSERT ... SELECT语法时需要注意的点有:

a、 在使用IGNORE忽略会导致重复键违规的行。也就是说，在使用关键字IGNORE关键字的时候会忽略唯一键的行为。

 b、 该INSERT语句的目标表 可能出现在查询部分的FROM子句中 SELECT。在从同一个表中选择并插入时，MySQL创建一个内部临时表来存放来自这些表的行SELECT，然后将这些行插入到目标表中。但是，不能使用 INSERT INTO t ... SELECT ... FROM t因为 t是TEMPORARY 表，因为TEMPORARY表不能在同一语句中引用两次。

c、AUTO_INCREMENT 标明的列不受影响。

d、为确保二进制日志可用于重新创建原始表，MySQL不允许并发插入INSERT ... SELECT语句

e、为避免SELECT在INSERT引用同一个表时引用不明确的列引用问题 ， 请为该SELECT部分中使用的每个表提供唯一的别名，并使用适当的别名限定该 部分中的列名。
     

案列演示: 

这里为了方便演示，需要再创建一张表，索性就命名未: T_MYSQL_CRUD_COPY.

DROP TABLE IF EXISTS T_MYSQL_CRUD_COPY; 
CREATE TABLE IF NOT EXISTS T_MYSQL_CRUD_COPY(
   id INT UNSIGNED AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   date DATE,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用INSERT ... SELECT进行数据插入:

INSERT INTO T_MYSQL_CRUD_COPY SELECT * FROM T_MYSQL_CRUD;

执行日志和结果。

针对以上的特殊情况感兴趣的朋友可以自行实验。

2)、INSERT ... ON DUPLICATE KEY UPDATE语法

如果指定ON DUPLICATE KEY UPDATE 要插入的行和标志位UNIQUE(唯一值)索引或者PRIMARY KEY(主键冲突)，将会执行UPDATE操作。例如，如果列a声明为UNIQUE并且包含该值 1，则以下两个语句具有相似的效果：

-- 语句1
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

-- 语句2
UPDATE t1 SET c=c+1 WHERE a=1;

对于自动增量列的InnoDB 表，其效果是不相同的a。对于自动增量列，INSERT 语句会增加自动增量值，但 UPDATE不会。

如果列b也是唯一的， INSERT则相当于这个 UPDATE语句：

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2几个行相匹配，只有 一个行被更新。通常，您应该尽量避免ON DUPLICATE KEY UPDATE 在具有多个唯一索引的表上使用子句。如果一个表包含一AUTO_INCREMENT列并INSERT ... ON DUPLICATE KEY UPDATE插入或更新一行，该LAST_INSERT_ID()函数将返回该AUTO_INCREMENT值。该ON DUPLICATE KEY UPDATE子句可以包含多个列分配，用逗号分隔。

在ON DUPLICATE KEY UPDATE子句中的赋值表达式中，可以使用values(str) 函数来引用语句部分的 列值 。换句话说， 在子句中指的是将要插入的值，没有发生重复键冲突。这个函数在多行插入中特别有用。该 函数仅在子句或 语句中有意义，否则返回 。例： VALUES(col_name)INSERTINSERT ... ON DUPLICATE KEY UPDATEVALUES(col_name)ON DUPLICATE KEY UPDATEcol_nameVALUES()ON DUPLICATE KEY UPDATEINSERTNULL。

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

以上语句和如下语句相同:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

对于INSERT ... SELECT语句，这些规则适用于SELECT可以在ON DUPLICATE KEY UPDATE子句中引用的可接受形式的查询表达式：

引用来自单个表（可能是派生表）的查询的列。

引用多个表上联接的查询中的列。

引用DISTINCT 查询中的列。

引用其他表中的列，只要 SELECT不使用 GROUP BY。一个副作用是您必须限定对非唯一列名称的引用。
来自a的列的引用 UNION不能可靠地工作。要解决此限制，请将UNION其重写 为派生表，以便将其行视为单表结果集。例如，这个语句可能会产生不正确的结果：

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

相反，使用重写UNION派生表的等效语句 ：

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

将查询重写为派生表的技术还支​​持引用GROUP BY 查询中的列。

3)、INSERT DELAYED语法

INSERT DELAYED ...

该语句的DELAYED选项 INSERT是对标准SQL的MySQL扩展。在以前的MySQL版本中，它可以用于某些类型的表（例如 MyISAM），这样当客户端使用时 INSERT DELAYED，它可以立即从服务器获得一个好的行，并且当表不是被任何其他线程使用。

DELAYED插入和替换在MySQL 5.6中被弃用。在MySQL 5.7中， DELAYED不支持。服务器识别但忽略DELAYED关键字，将插入处理为非延迟插入，并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED警告（“ 不再支持INSERT DELAYED。语句已转换为INSERT ”）。该DELAYED 关键字计划在未来版本中删除。

 

二、数据删除(DELETE)

DELETE 是从表中删除行的DML语句。

1)、单表删除语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

DELETE语句的主要功能是从表中删除行，并返回所删除的行数。假如需要检查已经删除的行数，可以调用Mysql的内置函数ROW_COUNT()。

删除演示:

执行删除操作(删除id=13的数据):

-- 执行删除一行数据
DELETE FROM T_MYSQL_CRUD WHERE 1 = 1 AND ID=13;

ROW_COUNT()函数案例演示:

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

当你想要在IDE(比如Navicat)测试ROW_COUNT()的时候，需要将删除语句和查询语句共同执行才能明显的效果，否则执行SELECT ROW_COUNT()返回的可能就是-1;

DELETE FROM T_MYSQL_CRUD WHERE 1 = 1 AND ID=12;
SELECT ROW_COUNT();

如果DELETE语句中带WHERE语句的话就是属于条件删除，仅删除待条件的行。如果不带WHERE的话就会删除所有的行(PS:在生产环境使用DELETE的时候千万要谨慎，不然就只能跑路了)。where_condition是一个表达式，每一行被删除的结果都是true。

如果ORDER BY指定了子句，则按照指定的顺序删除行。该 LIMIT子句限制了可以删除的行数。这些条款适用于单表删除，但不适用于多表删除。

2)、多表删除语法

 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

(1)、TRUNCATE TABLE

当您不需要知道已删除行的数量时，可以使用TRUNCATE TABLE语句执行表清空操作。和DELETE语句相比，TRUNCATE TABLE删除数据的速度更快，只是该语句是不可以条件删除的。相对于DELETE语句比较，TRUNCATE TABLE是事务不安全的。因此，当存在事务控制的时候只能使用DELETE语句去执行删除，而不能使用TRUNCATE TABLE。

 

DELETE支持使用该PARTITION选项的显式分区选择，该选项采用从中选择要删除的行的一个或多个分区或子分区（或两者）的逗号分隔名称的列表。未包括在列表中的分区将被忽略。给定一个t带有分区名称的分区表p0，执行该语句与 执行该表时DELETE FROM t PARTITION (p0)具有相同的效果ALTER TABLE t TRUNCATE PARTITION (p0); 在这两种情况下，分区p0中的所有行都被删除。

PARTITION可以与WHERE条件一起使用 ，在这种情况下，条件仅在列出的分区中的行上进行测试。例如， DELETE FROM t PARTITION (p0) WHERE c < 5 仅从p0条件c < 5为true的分区中删除行; 任何其他分区中的行不会被检查，因此不会受到影响 DELETE。

该PARTITION选项也可以用在多表DELETE语句中。在选项中，每个表格最多可以使用一个这样的 FROM选项。

(2)、删除顺序

如果DELETE语句包含一个 ORDER BY子句，则按照子句指定的顺序删除行。这主要是有用的LIMIT。例如，以下语句查找与该WHERE子句匹配的行，对它们进行排序timestamp_column，并删除第一个（最早的）一个：

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY 也有助于按照所需的顺序删除行，以避免引用完整性违规。

(3)、多表删除

您可以在DELETE语句中指定多个表，以 根据WHERE子句中的条件从一个或多个表中删除行 。你不能使用ORDER BY或LIMIT在一个多表 DELETE中。

对于第一个多表语法，只删除FROM子句前列出的表中的匹配行。对于第二个多表语法，只删除FROM子句（子句之前USING）中列出的表中的匹配行。

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

要么

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

三、数据更新(UPDATE)

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

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

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

 

四、数据更新(SELECT)

 1)、SELECT语法 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT用于检索从一个或多个表中选择的行，并可以包含 UNION语句和子查询。最常用的 SELECT语句是这些：

每个select_expr表示您想要检索的列。必须至少有一个 select_expr。

table_references指示从中检索行的表格。

该WHERE条件如果给出，则表示行必须满足选择的条件。 where_condition是一个表达式，对于每个要被选择的行，计算结果为true。如果没有WHERE子句，则语句选择所有行 。

在WHERE表达式中，除了汇总（汇总）函数，您可以使用MySQL支持的任何函数和运算符。SELECT 也可以用来检索不参考任何表而计算出的行。

mysql> SELECT 1 + 1;
        -> 2

DUAL在没有引用表的情况下， 您可以指定为虚拟表名称：

mysql> SELECT 1 + 1 FROM DUAL;
        -> 2

通常，使用的子句必须按照语法描述中显示的顺序给出。例如，一个 HAVING条款必须在任何 GROUP BY条款之后，在任何ORDER BY条款之前。例外情况是，该 INTO子句可以显示在语法描述中，也可以紧跟在 select_expr列表之后。

2)、SELECT中可以使用的条件运算符

where-clause：布尔条件表达式
= # 等值比较
<=> # 等值比较，包括与NULL的安全比较
<>或!= # 不等值比较
<，<=，>，>= # 其它比较符
IS NULL # 是否为空值
IS NOT NULL # 是否不为空值
LIKE # 支持的通配符有%和_
RLIKE或REGEXP # 正则表达式匹配
IN # 判指定字段的值是否在给定的列表中
BETWEEN … AND …  # 在某取值范围内

3)、组合查询条件

NOT，!  # 非
AND，&& # 和
OR，|| # 或

4)、聚合函数查询

SUM() # 求总和
AVG() # 求平均值
MAX() # 求最大值
MIN() # 求最小值
COUNT() # 求记录总数
#注：count(*)效率最低，可指定某一字段求总数，如count(Name)

5)、各字句类型

where # 条件过滤
group by # 对查询结果分组
having # 对group by的结果进行条件过滤
order by # 排序
limit # 限制输出行数，如”limit 2“表示只显示前2行，“limit 2,3”表示偏移前2行，显示3-5行
distinct # 指定的结果相同的只显示一次
sql_cache # 缓存于查询缓存中
sql_no_cache # 不缓存查询结果

五、总结

以上就是本文的主要内容了，更多的是理论知识铺垫。由于都是简单的CRUD操作，因此就不用太多的案例来解释了。