集合,增删改查,事务

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表中

INSERT INTO teams(teamno,playerno,division) VALUES(3,6,'third');

2、在表名后面省略所有的列名
这种写法要求VALUES子句中的值必须按照列在表结构中的顺序来一一赋值。

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

3、在表名后面只列出部分的列名
所有没有明确赋值的列，将通过隐式赋值自动得到null值。
例2： 添加一个新球员

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

4.使用字面量NULL给列赋空值

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

1. VALUES子句中除了字面量，还可以使用函数、计算、标量子查询等
   例4： 创建一个表，存储球员数量和罚款总额

CREATE TABLE totals(
   numberplayers INTEGER NOT NULL,
   sumpenalties DECIMAL(9,2) 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 (6,7,'third'),(7,27,'fourth'),(8,39,'fourth'),(9,112,'sixth'); 

注意：这种语法只要有1行出错，则插入全部取消
INSERT语句中可以使用IGNORE选项来当INSERT语句出错时，不显示错误消息。INSERT语句不会执行。
例6：向teams表中再次添加1号球队

INSERT IGNORE INTO teams VALUES(1,39,'second');

7.带子查询的INSERT语句
例7： 创建表recr_players，保存非参赛球员的编号、名字、城市和电话号码

CREATE TABLE recr_players(
  playerno  SMALLINT NOT NULL,
  name  char(15) NOT NULL,
  town  char(10) NOT NULL,
  phoneno  char(13) ,
  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 + 100,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 = 2000
WHERE playerno = 95;
共 1 行受到影响

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

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

例3： 把住在Stratford的球员的获胜局数设为0

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

先用select找到，再用update写，比如上题：

select won from MATCHES where playerno in (select playerno from PLAYERS WHERE town='Stratford')；

UPDATE其他语法

注意，在SET子句的子查询中，不允许访问要更新的表
例5： 在每笔罚款中减去平均罚款额。以下写法不允许

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


错误代码： 1064
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 2 

正确：
应该分两步来做：

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 + 1
ORDER BY  paymentno DESC;
共 8 行受到影响

例8：把一个first分级球队的所有比赛的获胜局数设为0，并把first分级球队的队长编号改为112

UPDATE matches m,teams t
SET m.won = 0,
t.playerno = 112
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(611,'john','GG','M',1977,'Green Way', 'Startford');

DELETE语句

delete语句执行的时候是一行一行的。

例 1： 删除44号球员的罚款

DELETE FROM penalties
WHERE playerno=44;

带子查询
例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 行受到影响

从多个表中删除行：
例4： 从teams和matches表中删除所有3号球队的行

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

两个表中满足连接条件teams.teamno = matches.teamno和过滤条件teams.teamno=3的所有行被删除。

TRUNCATE语句

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

truncate table 表名

---

数据库事务

事务：transaction
一个数据库事务由一条或者多条sql语句构成，它们形成一个逻辑的工作单元。这些sql语句要么全部执行成功，要么全部执行失败 。
事务是保证数据的完整性和一致性的重要手段。

事务类型

• DML事务：由一条或者多条DML语句构成
• DDL事务：总是由一条DDL语句构成
• DCL事务：总是由一条DCL语句构成

事务的开始和结束

在MySQL中，系统变量@@autocommit默认是打开的，这意味着任何1条SQL语句都会开始一个事务，语句执行完后事务自动结束。实际使用中，应该使用SET语句来关闭自动提交，否则一个事务不可能由多条SQL语句构成 。

SHOW  VARIABLES  LIKE  '%autocommit%';
SET @@autocommit=0;
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(20)
);
START TRANSACTION;
INSERT INTO trans_demo(id,NAME) VALUES(1,'zs');
INSERT INTO trans_demo(id,NAME) VALUES(2,'ls');
SAVEPOINT a;
UPDATE trans_demo SET NAME='ww' WHERE id=1;
SAVEPOINT b;
DELETE FROM trans_demo WHERE id=2;
-- 下面决定如何结束事务
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，事务没结束。

select salary from employee empId =‘Mary’;

在事务2中，财务人员修改了Mary的工资为2000，并提交了事务

update employee set salary = 2000;  
commit;

在事务1中，Mary 再次读取自己的工资时，工资变为了2000：

select salary from employee empId =‘Mary’;

• 3、幻读（phantom read）
  
  • 在同一个事务中，同样的条件，第1次和第2次读出来的记录数不一样。

示例：
在事务1中，读取所有工资为1000的员工（10人）

Select * from employee where salary =1000;  

在事务2中，向employee表插入了一条员工记录，工资也为1000，并提交 。

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

事务1再次读取所有工资为1000的员工（11人）

Select * from employee where salary =1000; 

不可重复读和幻读： 相同：都表现为两次读取的结果不一致。 区别：不可重复读的重点是修改，幻读的重点在于新增或者删除

• 4、丢失更新（lost update）

  • 一个事务的修改覆盖了另一个事务所做的修改

    示例：以下操作按顺序执行。

    事务1读取4号球员的罚款额，为50
    事务2读取4号球员的罚款额，也为50
    事务1把罚款额增加25，并提交

Update penalties set amount=amount + 25;
Commit;

事务2把罚款额增加30，并提交

Update penalties set amount=amount + 30;
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

查看数据库及当前会话的事务隔离级别

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

分别设置数据库及当前会话的事务隔离级别

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