一、连接

使用如下两表来进行测试：
mysql> desc users1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| uid| tinyint(3) unsigned | YES | | NULL||
| uname | varchar(255)| YES | | NULL||
| gid| tinyint(3) unsigned | YES | | NULL||
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> desc groups1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| gid| tinyint(3) unsigned | YES | | NULL||
| gname | varchar(255)| YES | | NULL||
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

[@more@]mysql> select * from users1;
+------+----------+------+
| uid | uname| gid |
+------+----------+------+
|0 | root |0 |
| 201 | yuegao| 101 |
| 202 | fengsong | 102 |
| 201 | yuegao| 200 |
+------+----------+------+
4 rows in set (0.00 sec)

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
|0 | root |
| 101 | dba|
| 200 | guest |
+------+-------+
3 rows in set (0.00 sec)

1.交叉连接

mysql> select * from users1 cross join groups1;
+------+----------+------+------+-------+
| uid | uname| gid | gid | gname |
+------+----------+------+------+-------+
|0 | root |0 |0 | root |
|0 | root |0 | 101 | dba|
|0 | root |0 | 200 | guest |
| 201 | yuegao| 101 |0 | root |
| 201 | yuegao| 101 | 101 | dba|
| 201 | yuegao| 101 | 200 | guest |
| 202 | fengsong | 102 |0 | root |
| 202 | fengsong | 102 | 101 | dba|
| 202 | fengsong | 102 | 200 | guest |
| 201 | yuegao| 200 |0 | root |
| 201 | yuegao| 200 | 101 | dba|
| 201 | yuegao| 200 | 200 | guest |
+------+----------+------+------+-------+
12 rows in set (0.00 sec)

或

mysql> select * from users1, groups1;
+------+----------+------+------+-------+
| uid | uname| gid | gid | gname |
+------+----------+------+------+-------+
|0 | root |0 |0 | root |
|0 | root |0 | 101 | dba|
|0 | root |0 | 200 | guest |
| 201 | yuegao| 101 |0 | root |
| 201 | yuegao| 101 | 101 | dba|
| 201 | yuegao| 101 | 200 | guest |
| 202 | fengsong | 102 |0 | root |
| 202 | fengsong | 102 | 101 | dba|
| 202 | fengsong | 102 | 200 | guest |
| 201 | yuegao| 200 |0 | root |
| 201 | yuegao| 200 | 101 | dba|
| 201 | yuegao| 200 | 200 | guest |
+------+----------+------+------+-------+
12 rows in set (0.00 sec)

2.内连接

mysql> select * from users1 inner join groups1 on users1.gid = groups1.gid;
+------+--------+------+------+-------+
| uid | uname | gid | gid | gname |
+------+--------+------+------+-------+
|0 | root|0 |0 | root |
| 201 | yuegao | 101 | 101 | dba|
| 201 | yuegao | 200 | 200 | guest |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)

如果连接列名称相同，可以使用如下形式：

mysql> select * from users1 inner join groups1 using(gid);
+------+------+--------+-------+
| gid | uid | uname | gname |
+------+------+--------+-------+
|0 |0 | root| root |
| 101 | 201 | yuegao | dba|
| 200 | 201 | yuegao | guest |
+------+------+--------+-------+
3 rows in set (0.00 sec)

这样两个表的连接列只会显示一个。而最常用的形式：

mysql> select * from users1, groups1 where users1.gid = groups1.gid;
+------+--------+------+------+-------+
| uid | uname | gid | gid | gname |
+------+--------+------+------+-------+
|0 | root|0 |0 | root |
| 201 | yuegao | 101 | 101 | dba|
| 201 | yuegao | 200 | 200 | guest |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)

3.外连接

左外连接：

mysql> select * from users1 left join groups1 on users1.gid = groups1.gid;
+------+----------+------+------+-------+
| uid | uname| gid | gid | gname |
+------+----------+------+------+-------+
|0 | root |0 |0 | root |
| 201 | yuegao| 101 | 101 | dba|
| 202 | fengsong | 102 | NULL | NULL |
| 201 | yuegao| 200 | 200 | guest |
+------+----------+------+------+-------+
4 rows in set (0.00 sec)

如果连接列名称相同，可以使用如下形式：

mysql> select * from users1 left join groups1 using(gid);
+------+------+----------+-------+
| gid | uid | uname| gname |
+------+------+----------+-------+
|0 |0 | root | root |
| 101 | 201 | yuegao| dba|
| 102 | 202 | fengsong | NULL |
| 200 | 201 | yuegao| guest |
+------+------+----------+-------+
4 rows in set (0.00 sec)

这样只会显示左侧表的连接列。

右外连接：

mysql> select * from users1 right join groups1 on users1.gid = groups1.gid;
+------+--------+------+------+-------+
| uid | uname | gid | gid | gname |
+------+--------+------+------+-------+
|0 | root|0 |0 | root |
| 201 | yuegao | 101 | 101 | dba|
| 201 | yuegao | 200 | 200 | guest |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)

如果连接列名称相同，可以使用如下形式：

mysql> select * from users1 right join groups1 using(gid);
+------+-------+------+--------+
| gid | gname | uid | uname |
+------+-------+------+--------+
|0 | root |0 | root|
| 101 | dba| 201 | yuegao |
| 200 | guest | 201 | yuegao |
+------+-------+------+--------+
3 rows in set (0.00 sec)

这样只会显示右侧表的连接列。

外连接与分组结合使用，列出每个成员所属的组和每个组包含的成员：

mysql> select u.uname, group_concat(g.gname separator ',') from users1 as u left join groups1 as g u
sing(gid) group by u.uid;
+----------+-------------------------------------+
| uname| group_concat(g.gname separator ',') |
+----------+-------------------------------------+
| root | root|
| yuegao| dba,guest|
| fengsong | NULL|
+----------+-------------------------------------+
3 rows in set (0.00 sec)

mysql> select g.gname, group_concat(u.uname separator ',') from users1 as u right join groups1 as g
using(gid) group by g.gid;
+-------+-------------------------------------+
| gname | group_concat(u.uname separator ',') |
+-------+-------------------------------------+
| root | root|
| dba| yuegao |
| guest | yuegao |
+-------+-------------------------------------+
3 rows in set (0.00 sec)

4.自连接

mysql> select * from processes1;
+------+------------------+------+
| pid | pname| ppid |
+------+------------------+------+
|1 | init |0 |
| 2915 | crond|1 |
| 3020 | hald |1 |
| 3021 | hald-runner | 3020 |
| 4707 | gnome-terminal|1 |
| 4709 | gnome-pty-helper | 4707 |
| 4710 | bash | 4707 |
+------+------------------+------+
7 rows in set (0.00 sec)

对上表进行自连接查询，返回进程名和其父进程名：

mysql> select a.pname as pname, b.pname as ppname from processes1 as a, processes1 as b where a.ppid
= b.pid;
+------------------+----------------+
| pname| ppname |
+------------------+----------------+
| crond| init|
| hald | init|
| gnome-terminal| init|
| hald-runner | hald|
| gnome-pty-helper | gnome-terminal |
| bash | gnome-terminal |
+------------------+----------------+
6 rows in set (0.00 sec)

mysql> select a.pname as pname, b.pname as ppname from processes1 as a left join processes1 as b on
a.ppid = b.pid;
+------------------+----------------+
| pname| ppname |
+------------------+----------------+
| init | NULL|
| crond| init|
| hald | init|
| hald-runner | hald|
| gnome-terminal| init|
| gnome-pty-helper | gnome-terminal |
| bash | gnome-terminal |
+------------------+----------------+
7 rows in set (0.00 sec)


二、联合

mysql> select * from groups1;
+------+-------+
| gid | gname |
+------+-------+
|0 | root |
| 101 | dba|
| 200 | guest |
+------+-------+
3 rows in set (0.02 sec)

mysql> create table groups2 as select * from groups1 where 0 = 1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into groups2 values (102, 'vip');
Query OK, 1 row affected (0.00 sec)

对groups1和groups2进行联合查询：

mysql> select * from groups1 union select * from groups2;
+------+-------+
| gid | gname |
+------+-------+
|0 | root |
| 101 | dba|
| 200 | guest |
| 102 | vip|
+------+-------+
4 rows in set (0.00 sec)

mysql> select * from groups1 where gid <> 0 union select * from groups2;
+------+-------+
| gid | gname |
+------+-------+
| 101 | dba|
| 200 | guest |
| 102 | vip|
+------+-------+
3 rows in set (0.00 sec)

进行联合必须满足两个基本条件：

1> 每个查询返回的字段的数量必须相同。
2> 每个查询返回的字段的数据类型必须相互符合。

UNION运算符会自动消除联合中的重复记录，包括同一个表中的重复记录：

mysql> insert into groups1 values (0, 'root');
Query OK, 1 row affected (0.00 sec)

mysql> insert into groups2 values (0, 'root');
Query OK, 1 row affected (0.00 sec)

mysql> select * from groups1 union select * from groups2;
+------+-------+
| gid | gname |
+------+-------+
|0 | root |
| 101 | dba|
| 200 | guest |
| 102 | vip|
+------+-------+
4 rows in set (0.00 sec)

要返回联合中的所有记录，可以使用UNION ALL：

mysql> select * from groups1 union all select * from groups2;
+------+-------+
| gid | gname |
+------+-------+
|0 | root |
| 101 | dba|
| 200 | guest |
|0 | root |
| 102 | vip|
|0 | root |
+------+-------+
6 rows in set (0.00 sec)


三、子查询

子查询有不同的使用方法：

1> 在一个WHERE或者HAVING子句中使用。
2> 与比较和逻辑操作符一起使用。
3> 与IN成员测试仪器使用。
4> 与EXISTS布尔测试一起使用。
5> 在一个FROM子句中使用。
6> 与连接一起使用。
7> 与UPDATE和DELETE查询一起使用。

MySQL不允许在使用子查询读数据的同时删除或者更新相同的表数据：

mysql> update users1 set gid = 200 where gid in (select users1.gid from users1 left join groups1 usi
ng(gid) where groups1.gname is null);
ERROR 1093 (HY000): You can't specify target table 'users1' for update in FROM clause

子查询的主要优点：

1> 使查询结构化，将语句的各部分隔离。
2> 提供另一种方法来执行那些需要复杂的连接和联合的操作。
3> 在很多人的观念中，子查询比连接或联合更具有可读性。

但是，子查询可能导致RDBMS负荷过大，大幅地降低性能，特别是外部参照的情况（），应谨慎使用。
大多数情况下，子查询可以转换成连接，如下例（用的是oracle中查看表空间使用率的语句，只是为了说明问题）：

select
a.tablespace_name tsname,
round(a.bytes,2)Total_Mb,
round(a.MAXsize,2) MAXSIZE_Gb,
round(b.bytes,2)Free_Mb,
(1 - (b.bytes/a.bytes))*100 Pct_used
from
(
select tablespace_name,sum(MAXBYTES/1024/1024/1024) MAXsize,sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name
) a,
(
select tablespace_name,sum(bytes)/1024/1024 bytes
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name
order by Pct_used;

可以转换为：

select
a.tablespace_name tsname,
round(sum(a.bytes)/1024/1024,2)Total_Mb,
round(sum(a.maxbytes/1024/1024/1024),2) MAXSIZE_Gb,
round(sum(b.bytes)/1024/1024,2)Free_Mb,
(1 - (sum(b.bytes)/sum(a.bytes)))*100 Pct_used
from dba_data_files a, dba_free_space b where a.tablespace_name = b.tablespace_name
group by a.tablespace_name order by Pct_used;


四、事务

1.事务相关语句

START TRANSACTION语句用于初始化一个事务，也可以使用BEGIN或BEGIN WORK语句。
使用COMMIT语句确认所有的变化，或使用ROLLBACK语句撤销所有的变化。
MySQL使用一个平面事务模型：嵌套的事务时不允许的，开始新的事务会自动提交之前的事务。其它的一些语句也会隐藏执行一个COMMIT命令：

1> DROP DATABASE/DROP TABLE
2> CREATE INDEX/DROP INDEX
3> ALTER TABLE/RENAME TABLE
4> LOCK TABLES/UNLOCK TABLES
5> SET AUTOCOMMIT=1

另外，MySQL也支持savepoints，这里不再赘述。

2.控制事务行为

AUTOCOMMIT变量指定是否开启自动提交模式。默认值为1，MySQL把每个语句作为一个单语句事务来处理。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|1 |
+--------------+
1 row in set (0.03 sec)

TRANSACTION ISOLATION LEVEL变量指定事务的隔离等级。默认值为REPEATABLE READ。

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

可以设置的四个不同级别依照严格程度由高到低依次为：

1> SERIALIZABLE（序列化）
2> REPEATABLE READ（可重复读）
3> READ COMMITTED（提交的读）
4> READ UNCOMMITTED（未提交的读）

设置该变量的方法如下：

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.05 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation|
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

也可以直接修改tx_isolation变量的值：

mysql> set tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

3.事务和性能

在使用事务表类型时，需要通过一些方法减小对数据库性能的影响。

1)使用小事务

KISS原则－Keep It Simple, Stupid!

使事务尽可能地小，且快速地变化和退出，这样其它队列中的事务就不会被过度地延迟：

1> 在发出START TRANSACTION命令之前，确保要求的用户输入都已完成。
也就是说，不要在事务开始后因为等待用户输入而造成不必要的延迟。

2> 尝试把大的事务分成小的事务然后分别执行。

2)选择合适的隔离等级

隔离等级越高（严格），性能受到的影响越大。选择哪个隔离等级是基于对应用程序容错能力以及潜在数据错误的影响的判断，默认的REPEATABLE READ对于大多数情况都是适用的。

3)避免死锁

InnoDB表处理程序具有检查死锁情况的内建智能，当发现死锁时会撤销其中的一个事务（或释放锁定）来解决死锁。
开发者在应用程序层面可以做很多事情来避免死锁：在会话初期获得需要的所有锁定；一直以相同的顺序处理表；如果RDBMS在解决死锁时撤销了事务，要使用内建的恢复程序来重新执行事务。