修改MySQL中的資料

weixin_34120274發表於2018-01-20

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.準備資料

 1 CREATE DATABASE IF NOT EXISTS empdb;
 2 
 3 USE empdb;
 4 -- create tables
 5 CREATE TABLE merits (
 6     performance INT(11) NOT NULL,
 7     percentage FLOAT NOT NULL,
 8     PRIMARY KEY (performance)
 9 );
10 
11 CREATE TABLE employees (
12     emp_id INT(11) NOT NULL AUTO_INCREMENT,
13     emp_name VARCHAR(255) NOT NULL,
14     performance INT(11) DEFAULT NULL,
15     salary FLOAT DEFAULT NULL,
16     PRIMARY KEY (emp_id),
17     CONSTRAINT fk_performance FOREIGN KEY (performance)
18         REFERENCES merits (performance)
19 );
20 -- insert data for merits table
21 INSERT INTO merits(performance,percentage)
22 VALUES(1,0),
23       (2,0.01),
24       (3,0.03),
25       (4,0.05),
26       (5,0.08);
27 -- insert data for employees table
28 INSERT INTO employees(emp_name,performance,salary)      
29 VALUES('Mary Doe', 1, 50000),
30       ('Cindy Minsu', 3, 65000),
31       ('Sue Greenspan', 4, 75000),
32       ('Grace Dell', 5, 125000),
33       ('Nancy Johnson', 3, 85000),
34       ('John Doe', 2, 45000),
35       ('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.準備資料

 1 USE testdb;
 2 
 3 CREATE TABLE buildings (
 4     building_no INT PRIMARY KEY AUTO_INCREMENT,
 5     building_name VARCHAR(255) NOT NULL,
 6     address VARCHAR(255) NOT NULL
 7 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 8 
 9 CREATE TABLE rooms (
10     room_no INT PRIMARY KEY AUTO_INCREMENT,
11     room_name VARCHAR(255) NOT NULL,
12     building_no INT NOT NULL,
13     FOREIGN KEY (building_no)
14         REFERENCES buildings (building_no)
15         ON DELETE CASCADE
16 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
17 
18 INSERT INTO buildings(building_name,address)
19 VALUES('海南大廈','海口市國興大道1234號'),
20       ('萬達水城','海口市大同路1200號');
21 
22 INSERT INTO rooms(room_name,building_no)
23 VALUES('Amazon',1),
24       ('War Room',1),
25       ('Office of CEO',1),
26       ('Marketing',2),
27       ('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觸發器。