随笔4修改MySQL 中的数据

一、Mysql 插入数据
简单的mysql insert 语句
MySQL INSERT语句允许您将一行或多行插入到表中。下面说明了INSERT语句的语法
```
INSERT INTO table(column1,column2...)
VALUES (value1,value2,...);
```
首先，在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表。
然后，将括号内的相应列的逗号分隔值放在VALUES关键字之后。

在执行插入语句前，需要具有执行INSERT语句的INSERT权限。
示例：

INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'),
       ('任务-2','2017-01-01','2017-01-02','Description 2'),
       ('任务-3','2017-01-01','2017-01-02','Description 3');

二、 insert ignore 语句
insert ignore 语句简介
当使用INSERT语句向表中添加一些行数据并且在处理期间发生错误时，INSERT语句将被中止，并返回错误消息。因此，可能不会向表中没有插入任何行。

但是，如果使用INSERT INGORE语句，则会忽略导致错误的行，并将其余行插入到表中。

INSERT INGORE语句的语法如下：

insert ignore into table(column_list)
values(value_list),
      (value_list)
      ...

请注意，IGNORE子句是MySQL对SQL标准的扩展

示例：
为了演示，我们将创建一个名为订阅者(subscribers)的新表。

USE testdb;

CREATE TABLE IF NOT EXISTS subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(50) NOT NULL UNIQUE
);

UNIQUE约束确保电子邮件列中不存在重复的电子邮件。

以下语句在subscribers表中插入一个新行：

```
INSERT INTO subscribers(email)
VALUES('guangqing@163.com');
```
上面语句它按预期那样工作。接下来，我们来执行另一个语句，将两行插入到subscribers者表中：

INSERT INTO subscribers(email)
VALUES("guangqing@163.com"), 
      ("xujiale@163.com");
它将返回一个错误：

1062 - Duplicate entry 'guangqing@163.com' for key 'email'

如错误消息中所示，电子邮件yiibai.com@gmail.com值重复而导致违反UNIQUE约束。

但是，如果您使用INSERT IGNORE语句，则其它语句将会继续执行 -

insert ignore into  subscribers(email)
VALUES("guangqing@163.com"), 
      ("xujiale@163.com");
MySQL服务器返回一条消息，显示插入一行，另一行被忽略。

Query OK, 1 row affected
Records: 2  Duplicates: 1  Warnings: 1

如果使用show warnings 语句，就会发现警告的详细信息：

三、mysql update 语句简介
我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。

下面说明了MySQL UPDATE语句的语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_name 
SET 
    column_name1 = expr1,
    column_name2 = expr2,
    ...
WHERE
    condition;

在上面UPDATE语句中：

首先，在UPDATE关键字后面指定要更新数据的表名。
其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。
第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。如果省略WHERE子句，则UPDATE语句将更新表中的所有行。
请注意，WHERE子句非常重要，所以不应该忘记指定更新的条件。有时，您可能只想改变一行; 但是，可能会忘记写上WHERE子句，导致意外更新表中的所有行。
MySQL在UPDATE语句中支持两个修饰符。
low_priority修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接。
LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。
即使发生错误，ignore修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。
示例：

update employees 
set 
email ="guangqing@163.com"
where   employeeNumber = 226;

update 多列

update employees
set 
name="jiale"
emal= "jiale@163.com"
where  employeeNumber = 226;

UPDATE customers 
SET 
    salesRepEmployeeNumber = (SELECT 
            employeeNumber
        FROM
            employees
        WHERE
            jobtitle = 'Sales Rep'
        LIMIT 1)
WHERE
    salesRepEmployeeNumber IS NULL;

如果在执行上面更新语句后，查询customers表中的数据，将看到每个客户都有一个销售代表。换句话说，以下查询不返回任何行数据。

四、update join 语法
MySQL UPDATE JOIN的语法如下：
```
UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition
```
让我们更详细地看看MySQL UPDATE JOIN语法：
- 首先，在UPDATE子句之后，指定主表(T1)和希望主表连接表(T2)。请注意，必须在UPDATE子句之后至少指定一个表。UPDATE子句后未指定的表中的数据未更新。
- 第二，指定一种要使用的连接，即INNER JOIN或LEFT JOIN和连接条件。JOIN子句必须出现在UPDATE子句之后。
- 第三，要为要更新的T1和/或T2表中的列分配新值。
- 第四，WHERE子句中的条件用于指定要更新的行。
如果您学习过了UPDATE语句教程，您可能会注意到使用以下语法更新数据交叉表的另一种方法：

UPDATE T1, T2
SET T1.c2 = T2.c2,
      T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

示例：

UPDATE employees e
        INNER JOIN
    merits  m ON e.performance = m.performance 
SET 
    salary = salary + salary * percentage; -- 执行连接更新

因为省略了UPDATE语句中的WHERE子句，所以employees表中的所有记录都被更新。

要计算新员工的工资，不能使用UPDATE INNER JOIN语句，因为它们的绩效数据在merits表中不可用。这就是为什么要使用UPDATE LEFT JOIN来实现了。

当UPDATE LEFT JOIN语句在另一个表中没有相应行时，就会更新表中的一行。
例如，您可以使用以下语句将新雇员的工资增加1.5％：

UPDATE employees e
        LEFT JOIN
    merits  n ON e.performance = m.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    m.percentage IS NULL;

五、delete 语句
要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法：
```
DELETE FROM table_name
WHERE condition;
```
上面查询语句中 -
- 首先，指定删除数据的表(table_name)。
- 其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除
请注意，WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行。
对于具有外键约束的表，当从父表中删除行记录时，子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。
假设要删除officeNumber为4的员工，则使用DELETE语句与WHERE子句作为以下查询：
```
delete from employees where officeCode = 4;
```
要删除employees表中的所有行，请使用不带WHERE子句的DELETE语句，如下所示：
```
delete from employees;
```
示例：
下语句按客户名称按字母排序客户，并删除前10个客户：
```
DELETE FROM customers
ORDER BY customerName
LIMIT 10;
```
类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户：

delete from customers where  country = 'France'  
order by customerName limit 5;

六、on delete cascade 语句
on delete cascade 示例：
面来看一些使用MySQL ON DELETE CASCADE的例子。

假设有两张表：建筑物(buildings)和房间(rooms)。在这个数据库模型中，每个建筑物都有一个或多个房间。然而，每个房间只属于一个建筑物。没有建筑物则房间是不会存在的。

建筑物和房间表之间的关系是一对多(1：N)，如下面的数据库图所示：
当我们从buildings表中删除一行时，还要删除rooms表中引用建筑物表中行的行。例如，当删除建筑编号(building_no)为2的行记录时，在buildings表上执行如下查询：
```
delete from buildings
where building_no =2;
```
我们希望rooms表中涉及到建筑物编号2的行记录也将被删除(讲得通俗一点：假设2号楼倒塌了，那么2号楼的房间应该也就不存在了)。以下是演示MySQL ON DELETE CASCADE参考操作如何工作的步骤。
第一步，创建buildings表，如下创建语句：

use testdb;
create table buildings(
building_no  int primary key auto_increment,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL) engine=innodb charset=utf8;

第二步，创建rooms表，如下创建语句：

create table rooms(
room_no int primary key auto_increment,room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,foreign key(building_no)
references buildings (building_no)
on delete cascade)engine=innodb 
default charset=utf8;

第三步，将一些数据插入到buildings表，如下插入语句：

INSERT INTO buildings(building_name,address)
VALUES('海南大厦','海口市国兴大道1234号'),
      ('万达水城','海口市大同路1200号');

第四步，查询buildings表中的数据：

select * from buildings;
+-------------+---------------+----------------------+
| building_no | building_name | address              |
+-------------+---------------+----------------------+
|           1 | 海南大厦      | 海口市国兴大道1234号 |
|           2 | 万达水城      | 海口市大同路1200号   |
+-------------+---------------+----------------------+
2 rows in set

现在可以看到，在建筑物表中有两行记录。

第五步，将一些数据插入到rooms表，如下插入语句：

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);

第六步，查询rooms表中的数据：

mysql> select * from rooms;
+---------+---------------+-------------+
| room_no | room_name     | building_no |
+---------+---------------+-------------+
|       1 | Amazon        |           1 |
|       2 | War Room      |           1 |
|       3 | Office of CEO |           1 |
|       4 | Marketing     |           2 |
|       5 | Showroom      |           2 |
+---------+---------------+-------------+
5 rows in set

从上面行记录中可以看到，building_no=1的建筑有3个房间，以及building_no=2有2个房间。

第七步，删除编号为2的建筑物：

DELETE FROM buildings WHERE building_no = 2;

第八步，查询 rooms表中的数据 -

mysql> DELETE FROM buildings WHERE building_no = 2;
Query OK, 1 row affected

mysql> SELECT * FROM rooms;
+---------+---------------+-------------+
| room_no | room_name     | building_no |
+---------+---------------+-------------+
|       1 | Amazon        |           1 |
|       2 | War Room      |           1 |
|       3 | Office of CEO |           1 |
+---------+---------------+-------------+
3 rows in set

可以看到，表中只剩下引用building_no=1的记录了，引用building_no=2的所有行记录都被自动删除了。

```
请注意，ON DELETE CASCADE仅支持使用存储引擎支持外键(如InnoDB)的表上工作。 
某些表类型不支持诸如MyISAM的外键，因此应该在使用MySQL ON DELETE CASCADE引用操作的表上选择适当的存储引擎。
```
查找受MySQL ON DELETE CASCADE操作影响的表的技巧

有时，当要从表中删除数据时，知道哪个表受到MySQL ON DELETE CASCADE参考操作的影响是有用的。可从information_schema数据库中的referential_constraints表中查询此数据，如下所示：

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'

例如，要使用示例数据库(testdb，因为上面两个表是建立在testdb数据库之上的)中的CASCADE删除规则查找与建筑表相关联的表，请使用以下查询：

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'testdb'
        AND referenced_table_name = 'buildings'
        AND delete_rule = 'CASCADE'

七、delete 语句使用 inner join子句
MySQL还允许在DELETE语句中使用INNER JOIN子句来从表中删除和另一个表中的匹配的行记录。

例如，要从符合指定条件的T1和T2表中删除行记录，请使用以下语句：
```
delete t1,t2 from t1 inner join  t2
on t1.key=t2.key where condition;
```
请注意，将T1和T2表放在DELETE和FROM关键字之间。如果省略T1表，DELETE语句仅删除T2表中的行记录。同样，如果省略了T2表，DELETE语句将只删除T1表中的行记录。

表达式T1.key = T2.key指定了将被删除的T1和T2表之间的匹配行记录的条件。

WHERE子句中的条件确定T1和T2表中要被删除的行记录。
示例：
以下语句删除t1表中id=1的行，并使用DELETE ... INNER JOIN语句删除t2表中的ref=1的行记录：

delete t1,t2 from t1 inner join t2
on t1.id=t2.ref where t1.id =1;

每个客户都有零个或多个订单。但是，每个订单都属于唯一的一个客户。

可以使用DELETE语句与LEFT JOIN子句来清理客户数据。以下声明删除未下订单的客户：

delete customers from customers
left join  orders ON customers.customerNumber = orders.customerNumber 
where orderNumber is null;

八、replace 语句简介
MySQL REPLACE语句是标准SQL的MySQL扩展。 MySQL REPLACE语句的工作原理如下：
- 如果给定行数据不存在，那么MySQL REPLACE语句会插入一个新行。
- 如果给定行数据存在，则REPLACE语句首先删除旧行，然后插入一个新行。在某些情况下，REPLACE语句仅更新现有行。
MySQL使用PRIMARY KEY或UNIQUE KEY索引来要确定表中是否存在新行。如果表没有这些索引，则REPLACE语句等同于INSERT语句。

要使用MySQL REPLACE语句，至少需要具有INSERT和DELETE权限。

请注意，有一个REPLACE字符串函数，它不是本教程中说述的REPLACE语句。

示例：
假设我们要将纽约市的人口更新为1008256，可以使用UPDATE语句如下：

update cities set population = 1008256 where id=1;

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM cities;
+----+-------------+------------+
| id | name        | population |
+----+-------------+------------+
|  1 | New York    |    1008256 |
|  2 | Los Angeles |    3694825 |
|  3 | Shanghai    |    1923400 |
+----+-------------+------------+

UPDATE语句的确按照预期更新了数据。

之后，使用REPLACE声明将洛杉矶市的人口更新为3696820。

replace into citises(id,population) values(2,3696820);
Query OK, 2 rows affected
上面执行返回结果中提示：2 rows affected，说明有两行数据受影响。

最后，再次查询城市表的数据来验证替换的结果。

mysql> SELECT  * FROM  cities;
+----+----------+------------+
| id | name     | population |
+----+----------+------------+
|  1 | New York |    1008256 |
|  2 | NULL     |    3696820 |
|  3 | Shanghai |    1923400 |
+----+----------+------------+
3 rows in set

现在name列为NULL。您可能期望name列的值保持不变。但是，REPLACE语句不这样做。在这种情况下，REPLACE语句的工作原理如下：

REPLACE语句首先使用列列表提供的信息将新行插入到cities表中。但是插入失败，因为在cities表中已经存在ID为2的行记录，因此，MySQL引发了重复键错误。
然后，REPLACE语句更新具有id列值为2指定的行记录。在正常进程中，它将先删除具有冲突id为2的旧行，然后插入一个新行。

replace 和 insert
REPLACE语句的第一种形式类似于INSERT语句，除了INSERT关键字换成REPLACE关键字以外，如下所示：

REPLACE INTO table_name(column_list)
VALUES(value_list);

例如：

```
REPLACE INTO cities(name,population)
VALUES('Phoenix',1321523);
```
请注意，没有出现在REPLACE语句中的列将使用默认值插入相应的列。如果列具有NOT NULL属性并且没有默认值，并且您如果没有在REPLACE语句中指定该值，则MySQL将引发错误。这是REPLACE和INSERT语句之间的区别。

例如，在以下语句中，仅指定name列的值，而没有指定population列。MySQL引发错误消息。因为population列不接受NULL值，而我们定义cities表时，也没有指定population列的默认值。

REPLACE INTO cities(name)
VALUES('Houston');

执行上面语句后，MySQL发出如下的错误消息：

Error Code: 1364. Field 'population' doesn't have a default value

replace 和update

REPLACE语句的第二种形式类似于UPDATE语句，如下所示：

REPLACE INTO table
SET column1 = value1,
    column2 = value2;

请注意，REPLACE语句中没有where子句。

例如，如果要将Phoenix城市的人口更新为1768980，请使用REPLACE语句，如下所示：
```
REPLACE INTO cities
SET id = 4,
    name = 'Phoenix',
    population = 1768980;
```
与UPDATE语句不同，如果不在SET子句中指定列的值，则REPLACE语句将使用该列的默认值。

replace into 和 select
REPLACE语句的第三种形式类似于insert into select语句：
```
REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;
```
假设要复制ID为1的城市行记录，可以使用REPLACE INTO SELECT语句，如下查询示例：

replace into cities(name,population)
select name,population from cities 
where id=1;

replace 语句用法

使用REPLACE语句时需要知道几个重点：
如果您开发的应用程序不仅支持MySQL数据库，而且还支持其他关系数据库管理系统(RDBMS)，则应避免使用REPLACE语句，因为其他RDBMS可能不支持。代替的作法是在事务中使用DELETE和INSERT语句的组合。
如果在具有触发器的表中使用了REPLACE语句，并且发生了重复键错误的删除，则触发器将按以下顺序触发：在删除前删除，删除之后，删除后，如果REPLACE语句删除当前行并插入新行。如果REPLACE语句更新当前行，则触发BEFORE UPDATE和AFTER UPDATE触发器。

prepared 语句简介
mysql 准备语句用法
为了使用MySQL准备语句，您需要使用其他三个MySQL语句如下：
- prepared - 准备执行的声明。
- execute - 执行由prepared语句定义的语句。
- deallocate prepare - 发布prepare语句。
下图说明了如何使用prepare语句：
```
PREPARE stmt1 FROM 'SELECT productCode, productName
                    FROM products
                    WHERE productCode = ?';

SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;

DEALLOCATE PREPARE stmt1;
```
首先，使用PREPARE语句准备执行语句。我们使用SELECT语句根据指定的产品代码从products表查询产品数据。然后再使用问号(?)作为产品代码的占位符。

接下来，声明了一个产品代码变量@pc，并将其值设置为S10_1678。

然后，使用EXECUTE语句来执行产品代码变量@pc的准备语句。

最后，我们使用DEALLOCATE PREPARE来发布PREPARE语句。

在本教程中，我们向您展示了如何使用MySQL PREPARE语句来执行带占位符的查询，以提高查询的速度，并使您的查询更安全。

Made by Guangqing Xu