修改MySQL中的数据

一：insert语句

1.介绍

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

　　

2.准备环境

　　

3.简单insert语句

　　

4.插入多行

　　在这种形式中，每行的值列表用逗号分隔。

　　

　　如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表

　　

5.具有SELECT子句的MySQL INSERT

　　可以使用INSERT和SELECT子句完全或部分复制表。

　　

　　

6.INSERT与ON DUPLICATE KEY UPDATE

　　如果新行违反主键(PRIMARY KEY)或UNIQUE约束，MySQL会发生错误。

　　但是，如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL将插入新行或使用新值更新原行记录。

　　

　　=》

　　

　　=》问题

　　这个取决与on duplicate key update后面的值，如果id是2，因为后面加1，id的值变成3，在表中id=3是有的，所以还会继续报错。

二：insert ignore

1.介绍　　

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

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

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

　　所以当使用INSERT IGNORE语句来执行插入数据时，MySQL只发出警告而不是发出错误，以防发生错误退出其它数据无法插入。

2.准备数据

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

　　

3.示例

　　先插入一条记录

　　

　　使用ignore：

　　

　　结果：

　　

4.strict模式

　　当STRICT模式打开时，如果您尝试将无效值插入到表中，MySQL将返回错误并中止INSERT语句。

　　但是，如果使用INSERT IGNORE语句，则MySQL将发出警告而不是错误。 此外，它将尝试调整值以使其在插入表之前有效。

　　说明：

　　　　例如表中的字段的长度限制是6.

　　　　当数据插入的时候是7。

　　　　不会报错，只会将字符串截取，剩下6个字符被插入。

三：Update语句

1.介绍　　

　　使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。　

• 首先，在UPDATE关键字后面指定要更新数据的表名。
• 其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。
• 第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

　　支持的修饰符：

• LOW_PRIORITY修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接。 LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。
• 即使发生错误，IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。

 　　

2.示例

　　使用yiibaidb数据库中的emplee表。

　　

　　

　　修改数据：

　　

　　验证数据

　　

3.update多列

　　要更新多列中的值，需要在SET子句中指定分配。

　　

四：update join

1.介绍

　　使用MySQL UPDATE JOIN语句来执行跨表更新。

　　

2.相同的意思的语句

　　在这个UPDATE语句与具有隐式INNER JOIN子句的UPDATE JOIN工作相同

　　

3.准备数据

CREATE DATABASE IF NOT EXISTS empdb;

USE empdb;
-- create tables
CREATE TABLE merits (
    performance INT(11) NOT NULL,
    percentage FLOAT NOT NULL,
    PRIMARY KEY (performance)
);

CREATE TABLE employees (
    emp_id INT(11) NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(255) NOT NULL,
    performance INT(11) DEFAULT NULL,
    salary FLOAT DEFAULT NULL,
    PRIMARY KEY (emp_id),
    CONSTRAINT fk_performance FOREIGN KEY (performance)
        REFERENCES merits (performance)
);
-- insert data for merits table
INSERT INTO merits(performance,percentage)
VALUES(1,0),
      (2,0.01),
      (3,0.03),
      (4,0.05),
      (5,0.08);
-- insert data for employees table
INSERT INTO employees(emp_name,performance,salary)      
VALUES('Mary Doe', 1, 50000),
      ('Cindy Minsu', 3, 65000),
      ('Sue Greenspan', 4, 75000),
      ('Grace Dell', 5, 125000),
      ('Nancy Johnson', 3, 85000),
      ('John Doe', 2, 45000),
      ('Lily Bush', 3, 55000);

4.INNER JOIN子句的示例　　

　　假设想根据员工的工作表现来调整员工的工资。

　　因此，优点百分比存储在merits表中，您必须使用UPDATE INNER JOIN语句根据存储在merits表中的百分比来调整employees表中员工的工资。

　　

5.具有LEFT JOIN的MySQL UPDATE JOIN示例

　　假设公司又雇用了两名新员工：

　　

　　因为这些员工是新员工，所以他们的绩效(performance)数据不可用或为NULL。现在 employees表中的数据。

　　这时：

　　要计算新员工的工资，不能使用UPDATE INNER JOIN语句，因为它们的绩效数据在merits表中不可用。

　　要使用UPDATE LEFT JOIN来实现了。

　　当UPDATE LEFT JOIN语句在另一个表中没有相应行时，就会更新表中的一行。

　　做法：

　　　　您可以使用以下语句将新雇员的工资增加1.5％。

　　　　

五：delete语句

1.介绍

　　使用MySQL DELETE语句从单个表中删除数据。

　　

2.示例

　　

　　

3.与limit结合

　　如果要限制要删除的行数，则使用LIMIT子句。

　　这个时候会删除开始的m行。

　　

六：on delete cascade语句

1.介绍

　　使用MySQL ON DELETE CASCADE引用操作来执行外键从多个相关表中删除数据。

　　ON DELETE CASCADE对于外键的引用操作，可以实现在从父表中删除数据时自动删除子表中的数据。

　　ON DELETE CASCADE仅支持使用存储引擎支持外键(如InnoDB)的表上工作。 某些表类型不支持诸如MyISAM的外键，因此应该在使用MySQL ON DELETE CASCADE引用操作的表上选择适当的存储引擎。

2.需求

　　假设有两张表：建筑物(buildings)和房间(rooms)。 在这个数据库模型中，每个建筑物都有一个或多个房间。 

　　

　　当我们从buildings表中删除一行时，还要删除rooms表中引用建筑物表中行的行。

3.准备数据

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 DEFAULT CHARSET=utf8;

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;

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

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

4.示例

　　DELETE FROM buildings WHERE building_no = 2;

　　

5.查找受MySQL ON DELETE CASCADE操作影响的表的技巧

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

　　

　　

七：delete join

1.介绍

　　使用MySQL DELETE JOIN语句来从多个表中删除数据。

　　

2.示例

　　

　　每个用户有多个订单，每个订单属于一个唯一的用户。

　　示例一：

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

　　

　

八：replace语句

1.介绍

　　REPLACE语句是标准SQL的MySQL扩展。 MySQL REPLACE语句的工作原理如下：

• 如果给定行数据不存在，那么MySQL REPLACE语句会插入一个新行。
• 如果给定行数据存在，则REPLACE语句首先删除旧行，然后插入一个新行。 在某些情况下，REPLACE语句仅更新现有行。

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

　　MySQL使用PRIMARY KEY或UNIQUE KEY索引来要确定表中是否存在新行。

 2.准备数据

USE testdb;

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    population INT NOT NULL
);
INSERT INTO cities(name,population)
    VALUES('New York',8008278),
   ('Los Angeles',3694825),
   ('Shanghai',1923400);

3.示例

　　现在name列为NULL。 您可能期望name列的值保持不变。但是，REPLACE语句不这样做。

　　

　　

4.示例二

　　

　　

5.注意：

　　请注意，没有出现在REPLACE语句中的列将使用默认值插入相应的列。

　　 如果列具有NOT NULL属性并且没有默认值，并且您如果没有在REPLACE语句中指定该值，则MySQL将引发错误。这是REPLACE和INSERT语句之间的区别。

6.对于有update意思的replace的其他写法。

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

　　

　　例如：

　　

7.注意点

• 如果您开发的应用程序不仅支持MySQL数据库，而且还支持其他关系数据库管理系统(RDBMS)，则应避免使用REPLACE语句，因为其他RDBMS可能不支持。代替的作法是在事务中使用DELETE和INSERT语句的组合。

• 如果在具有触发器的表中使用了REPLACE语句，并且发生了重复键错误的删除，则触发器将按以下顺序触发：在删除前删除，删除之后，删除后，如果REPLACE语句删除当前 行并插入新行。 如果REPLACE语句更新当前行，则触发BEFORE UPDATE和AFTER UPDATE触发器。

九：Prepared语句

1.介绍　　

　　之前的MySQL版本4.1，查询以文本格式发送到MySQL服务器。

　　之后，MySQL服务器使用文本协议将数据返回给客户端。MySQL必须完全解析查询，并将结果集转换为字符串，然后再将其返回给客户端。

　　文本协议具有严重的性能问题。

　　为了解决这个问题，MySQL自版本4.1以来添加了一个名为prepare语句的来实现一些新功能。

　　prepare语句利用客户端/服务器二进制协议。 它将包含占位符(?)的查询传递给MySQL服务器。

2.说明

　　

　　当MySQL使用不同的productcode值执行此查询时，不必完全解析查询。

　　 因此，这有助于MySQL更快地执行查询，特别是当MySQL多次执行查询时。

　　因为prepare语句使用占位符(?)，这有助于避免SQL注入的问题，从而使您的应用程序更安全一些。

3.语法

　　

4.示例