集合,增删改查,事務

UNION集合操作

UNION用于把兩個或者多個select查詢的結果集合并成一個。

例：得到那些有罰款或者擔任隊長，或者兩個條件都符合的球員的編号。

select playerno from teams
UNION
select playerno from penalties

UNION ALL 和 UNION的差別是：前者不去掉結果集中重複的行。

DML語句和事務

DML：DATA MANIPULATION LANGUAGE（資料操縱語言），

由 INSERT、UPDATE、DELETE 等語句構成，用來修改表中的資料

INSERT語句

1、在表名後面列出所有的列名

例1：插入一個新的球隊到teams表中

2、在表名後面省略所有的列名

這種寫法要求VALUES子句中的值必須按照列在表結構中的順序來一一指派。

INSERT INTO teams
  VALUES(,,'third');

3、在表名後面隻列出部分的列名

所有沒有明确指派的列，将通過隐式指派自動得到null值。

例2：添加一個新球員

INSERT INTO players(playerno,NAME,initials,sex,joined,street,town)
  VALUES(,'Jones','GG','M',,'Green Way','Stratford');

4.使用字面量NULL給列賦空值

INSERT  INTO players(playerno,NAME,initials,birth_date,sex,joined,street,houseno,postcode,town,phoneno,leagueno)
  VALUES(,'Tom','CC',NULL,'M',,'Green Way',NULL,NULL,'Stratford',NULL,NULL);

VALUES子句中除了字面量，還可以使用函數、計算、标量子查詢等

例4：建立一個表，存儲球員數量和罰款總額

CREATE TABLE totals(
   numberplayers INTEGER NOT NULL,
   sumpenalties DECIMAL(,) NOT NULL
);

INSERT INTO totals(numberplayers,sumpenalties)
  VALUES((SELECT count(*) FROM players),
                 (SELECT sum(amount) FROM penalties));

注意：子查詢必須放在單獨的小括号中

6.一條INSERT語句可以插入多個行：

例5：添加4個新的球隊

INSERT INTO teams(teamno,playerno,division)
  VALUES (,,'third'),(,,'fourth'),(,,'fourth'),(,,'sixth');

注意：這種文法隻要有1行出錯，則插入全部取消

INSERT語句中可以使用IGNORE選項來當INSERT語句出錯時，不顯示錯誤消息。INSERT語句不會執行。

例6：向teams表中再次添加1号球隊

7.帶子查詢的INSERT語句

例7：建立表recr_players，儲存非參賽球員的編号、名字、城市和電話号碼

CREATE TABLE recr_players(
  playerno  SMALLINT NOT NULL,
  name  char() NOT NULL,
  town  char() NOT NULL,
  phoneno  char() ,
  PRIMARY KEY(playerno)
);

将聯盟會員号為空的球員插入到recr_players表中

INSERT INTO recr_players
SELECT  playerno, NAME, town, phoneno
FROM  players
WHERE  leagueno IS NULL;
共 4 行受到影響

也可以把本表中的行再次添加到本表中。注意主鍵值不要重複

例8：把那些罰款額大于平均罰款額的所有罰款添加到penalties表中。

INSERT INTO penalties SELECT paymentno + ,playerno,payment_date,amount 
FROM penalties
WHERE amount > (SELECT avg(amount) FROM penalties);

共 4 行受到影響

UPDATE語句

UPDATE 表名 … SET 列名 … WHERE

例1：把95号球員的聯盟會員号碼改為2000

UPDATE  players
SET leagueno = 
WHERE playerno = ;
共 1 行受到影響

例2：把所有的罰款增加5%

UPDATE penalties
SET amount = amount*; 
共 8 行受到影響

例3：把住在Stratford的球員的獲勝局數設為0

UPDATE matches
SET won = 
WHERE playerno IN(SELECT playerno
FROM players
WHERE town='Stratford');
共 4 行受到影響

先用select找到，再用update寫，比如上題：

UPDATE其他文法

注意，在SET子句的子查詢中，不允許通路要更新的表

例5：在每筆罰款中減去平均罰款額。以下寫法不允許

UPDATE penalties
SET amount = amount - (SELECT avg(amount)
FROM penalties);


錯誤代碼： 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '– (select avg(amount)                           From penalties)' at line

正确：

應該分兩步來做：

SET @avg_amount := (SELECT avg(amount) FROM penalties);
UPDATE penalties
SET amount = amount - @avg_amount;

共 8 行受到影響

UPDATE語句中可以使用ORDER BY子句，要求以排序的順序來依次更新行。這在某些場景可能有用。例如，如果想要把所有罰款的罰款編号都加1，如果從罰款編号為1的行開始更新，要麼就會發生主鍵值重複異常。如果從罰款編号最大的行開始更新，就沒有問題。

例6：把所有罰款的編号增加1

UPDATE  penalties
SET  paymentno = paymentno + 
ORDER BY  paymentno DESC;
共 8 行受到影響

例8：把一個first分級球隊的所有比賽的獲勝局數設為0，并把first分級球隊的隊長編号改為112

UPDATE matches m,teams t
SET m.won = ,
t.playerno = 
WHERE t.teamno = m.teamno
AND t.division = 'first';
共 6 行受到影響

MySQL首先執行一個二表連接配接查詢，從兩個表中找到滿足連接配接條件 t.teamno = m.teamno 的所有行，然後對這些行分别進行更新。

使用一條語句更新多個表的優點是：要麼兩個表都更新，要麼兩個表都不更新。

REPLACE語句

1.替代已有的行

2.REPLACE語句是INSERT語句的一個變種。當添加新行時，如果主鍵值重複，那麼就覆寫表中已有的行。如果沒有主鍵值重複，則插入該行

例1：添加一個新的球員。如果主鍵值已經存在，則覆寫該行

REPLACE INTO players(playerno,NAME,initials, sex,joined,street,town) 
VALUES(,'john','GG','M',,'Green Way', 'Startford');

DELETE語句

delete語句執行的時候是一行一行的。

例 1：删除44号球員的罰款

DELETE FROM penalties
WHERE playerno=;

帶子查詢

例2：删除球員，條件是他們加入俱樂部的年份晚于來自于Stratford的球員加入俱樂部的平均年份

CREATE TABLE players_copy2
AS SELECT * FROM players;

DELETE FROM players_copy1
WHERE joined > (
SELECT avg(joined)
FROM players_copy2
WHERE town = 'Stratford');
[ 正确執行。]

注意，在WHERE子句的子查詢中，不允許通路要删除行的表。

DELETE FROM players_copy1
WHERE joined > (
SELECT avg(joined)
FROM players_copy1
WHERE town = 'Stratford');
錯誤代碼： 1093
You can't specify target table 'players_copy1' for update in FROM clause

用在DELETE語句中的ORDER BY子句和LIMIT子句的含義和用在UPDATE語句中是類似的。

例3：删除4個最高的罰款

DELETE FROM penalties
ORDER BY amount DESC,playerno ASC
LIMIT ;

共 4 行受到影響

從多個表中删除行：

例4：從teams和matches表中删除所有3号球隊的行

DELETE  teams, matches
FROM  teams, matches
WHERE  teams.teamno = matches.teamno
AND  teams.teamno=;

兩個表中滿足連接配接條件teams.teamno = matches.teamno和過濾條件teams.teamno=3的所有行被删除。

TRUNCATE語句

功能：清空一張（大）表的更有效的方法，速度比DELETE快得多！

用法：

資料庫事務

事務：transaction

一個資料庫事務由一條或者多條sql語句構成，它們形成一個邏輯的工作單元。這些sql語句要麼全部執行成功，要麼全部執行失敗。

事務是保證資料的完整性和一緻性的重要手段。

事務類型

DML事務：由一條或者多條DML語句構成
DDL事務：總是由一條DDL語句構成
DCL事務：總是由一條DCL語句構成

事務的開始和結束

在MySQL中，系統變量@@autocommit預設是打開的，這意味着任何1條SQL語句都會開始一個事務，語句執行完後事務自動結束。實際使用中，應該使用SET語句來關閉自動送出，否則一個事務不可能由多條SQL語句構成。

SHOW  VARIABLES  LIKE  '%autocommit%';
SET @@autocommit=;
SHOW  VARIABLES  LIKE  '%autocommit%‘;

對于DDL（create、alter、drop等開頭的語句）和DCL（grant、revoke語句）事務，在執行每條語句之前和之後，MySQL會自動執行一條COMMIT語句，是以事務是自動開始和結束的。自動送出打開或者關閉對這些事務沒有影響。

對于DML事務，在自動送出關閉的情況下，事務的開始分為隐式開始和顯式開始：

隐式開始：程式的第一條DML語句執行時或者在COMMIT或ROLLBACK語句之後執行第一條DML語句時，自動開始一個新的事務。
顯式開始：發出STRAT TRANSACTION語句。該語句會自動關閉自動送出，當事務結束後，autocommit變量恢複到原來的值。

DML事務的結束

COMMIT語句：成功送出。事務所做的全部工作被永久地儲存到磁盤上。
ROLLBACK語句：失敗復原。事務所做的全部工作被撤銷，表中的資料不受事務操作的影響。

其它事務控制語句:

SAVEPOINT identifier ：儲存點指令，用來在事務中做一個标記，專門提供給rollback to語句使用
ROLLBACK TO [SAVEPOINT]

identifier：復原到儲存點。專門用來撤銷事務所做的部分工作：儲存點之後所做的工作全部撤銷。該語句并不結束事務

事務示例：

CREATE TABLE trans_demo(
   id INT,
   NAME VARCHAR()
);
START TRANSACTION;
INSERT INTO trans_demo(id,NAME) VALUES(,'zs');
INSERT INTO trans_demo(id,NAME) VALUES(,'ls');
SAVEPOINT a;
UPDATE trans_demo SET NAME='ww' WHERE id=;
SAVEPOINT b;
DELETE FROM trans_demo WHERE id=;
-- 下面決定如何結束事務
ROLLBACK TO b;
ROLLBACK TO a;
ROLLBACK;

COMMIT或ROLLBACK語句之前資料的狀态

1.資料的修改都是在記憶體中進行的。
2.通過查詢表，目前使用者（事務）能夠檢視DML操作的結果。
3.其它使用者（事務）不能檢視目前使用者（事務）所做的DML操作的結果。這叫做不允許髒讀.（dirty read）。髒讀：一個事務讀到了另一個事務未送出的資料。已修改但未送出的資料叫做贓資料。
4.表中受影響的行被鎖定，其它使用者（事務）不能在受影響的行上修改資料

COMMIT或ROLLBACK語句之後資料的狀态

1.COMMIT之後
- 資料改變被寫到資料庫中。
- 所有使用者（事務）可以檢視事務的結果。
- 表中受影響行上的鎖被釋放，這些行現在可以被其它使用者（事務）修改。
- 事務中所有的儲存點被删除。
2.ROLLBACK之後
- 資料改變被撤銷。
- 資料先前的狀态被恢複。
- 表中受影響行上的鎖被釋放。

并發事務的4個問題

1、髒讀（dirty read）
- 一個事務讀到了另一個事務未送出的資料
2、不可重複讀（nonrepeatable read）
- 在同一個事務中，同樣的條件，你讀取過的資料再次讀取出來時發現值不一樣了。示例：
  
  在事務1中，Mary 讀取了自己的工資為1000，事務沒結束。

在事務2中，财務人員修改了Mary的工資為2000，并送出了事務

update employee set salary = ;  
commit;

在事務1中，Mary 再次讀取自己的工資時，工資變為了2000：

3、幻讀（phantom read）
- 在同一個事務中，同樣的條件，第1次和第2次讀出來的記錄數不一樣。

示例：

在事務1中，讀取所有工資為1000的員工（10人）

在事務2中，向employee表插入了一條員工記錄，工資也為1000，并送出。

Insert into employee(empId,salary) values(‘zs’,);
commit;

事務1再次讀取所有工資為1000的員工（11人）

不可重複讀和幻讀：相同：都表現為兩次讀取的結果不一緻。差別：不可重複讀的重點是修改，幻讀的重點在于新增或者删除

4、丢失更新（lost update）
- 一個事務的修改覆寫了另一個事務所做的修改
  
  示例：以下操作按順序執行。
  
  事務1讀取4号球員的罰款額，為50
  
  事務2讀取4号球員的罰款額，也為50
  
  事務1把罰款額增加25，并送出

Update penalties set amount=amount + ;
Commit;

事務2把罰款額增加30，并送出

Update penalties set amount=amount + ;
Commit;

此時，事務1認為最新的罰款額為75，但是實際上是80，事務1所做的更新操作被事務2覆寫掉了。事務1的更新丢失了

事務的隔離級别

以上所有的4個問題都可以通過不允許兩個使用者同時運作一個事務來很容易的解決掉：使用者1的事務沒有結束，使用者2的事務就不能開始，這樣就不會出錯了。但是這樣的話，資料庫的并發性能極差，不能接受。

每個事務都有一個隔離級别（isolation level），它規定了并發運作的兩個事務之間是否允許發生上面的問題。MySQL有4種事務隔離級别：

SERIALIZABLE：串行化（全不允許）
REPEATABLE READ ：可重複讀。MySQL預設級别（隻許丢失更新）
READ COMMITTED：讀已送出（不許髒讀）
READ UNCOMMITTED：讀未送出（全允許）

隔離級别	髒讀	不可重複讀	幻讀	丢失更新
read uncommitted	yes	yes	yes	yes
read committed	no	yes	yes	yes
repeatable read	no	no	no	yes
serializable	no	no	no	no

PS：一篇非常好的關于隔離級别解析的博文：

http://www.cnblogs.com/yelbosh/p/5813865.html

集合,增删改查,事務

檢視資料庫及目前會話的事務隔離級别

分别設定資料庫及目前會話的事務隔離級别

SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';

或者使用SET TRANSACTION語句來設定隔離級别

SET  [GLOBAL | SESSION]  TRANSACTION
ISOLATION LEVEL  level

其中，level:

REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE

該語句對正在運作的事務沒有影響，從下一個事務開始起作用。

在配置檔案中指定資料庫的事務隔離級别

[mysqld]
transaction-isolation = REPEATABLE-READ

集合,增删改查,事務

UNION集合操作

DML語句和事務

INSERT語句

UPDATE語句

UPDATE其他文法

REPLACE語句

DELETE語句

TRUNCATE語句

資料庫事務

事務類型

事務的開始和結束

COMMIT或ROLLBACK語句之前資料的狀态

COMMIT或ROLLBACK語句之後資料的狀态

并發事務的4個問題

事務的隔離級别

繼續閱讀

MySQL_紙上談兵之入門

MySQL 服務的啟動與停止(通過指令行的方式)