MySQL連接方式有哪些

這篇文章主要介紹“MySQL連接方式有哪些”，在日常操作中，相信很多人在MySQL連接方式有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL連接方式有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

一、連接

   使用如下兩表來進行測試：
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在解決死鎖時撤銷了事務，要使用內建的恢復程序來重新執行事務。

到此，關于“MySQL連接方式有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！