随笔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语句来执行带占位符的查询，以提高查询的速度，并使您的查询更安全。