mysql資料庫查詢操作

標籤：聯表查詢   tar   一個   inner   ref   l資料庫   name   order by   new   

### mysql資料庫

#### 知識要點：

1. 單表查詢
2. 子查詢
3. 聯表查詢
4. 事務

在進行查詢之前，我們要先建好關係表，並往資料表中插入些資料。為查詢操作做好準備。

##### 五張關係表的建立：

```mysql
#建立並進入資料庫：
mysql> CREATE DATABASE `info`;
Query OK, 1 row affected (0.00 sec)
mysql> USE `info`;
Database changed

#建立學院表：
mysql> CREATE TABLE `department`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.69 sec)
#建立學生表：
mysql> CREATE TABLE `students`(
-> `s_id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL,
-> `d_id` INT,
-> FOREIGN KEY(`d_id`) REFERENCES `department` (`id`)
-> );
Query OK, 0 rows affected (0.65 sec)
#建立學生的詳細資料表：
mysql> CREATE TABLE `stu_details`(
-> `s_id` INT PRIMARY KEY,
-> `age` INT,
-> `gender` CHAR(1)
-> ,
-> FOREIGN KEY(`s_id`) REFERENCES `students` (`s_id`)
-> );
Query OK, 0 rows affected (0.55 sec)
#建立課程表：
mysql> CREATE TABLE `course`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.50 sec)
#建立中間表：
mysql> CREATE TABLE `select`(
-> `s_id` INT,
-> `c_id` INT,
-> PRIMARY KEY (`s_id`,`c_id`),
-> FOREIGN KEY (`s_id`) REFERENCES `students`(`s_id`),
-> FOREIGN KEY (`c_id`) REFERENCES `course`(`id`)
-> );
Query OK, 0 rows affected (0.84 sec)
#查看當前存在的表：
mysql> SHOW TABLES;
+----------------+
| Tables_in_info |
+----------------+
| course |
| department |
| select |
| stu_details |
| students |
+----------------+
5 rows in set (0.00 sec)
```

##### 往資料表中添加資料

```mysql
#往學院表中添加資料：
mysql> INSERT INTO `department`(`name`)
-> VALUES(‘外國語‘),
-> (‘藝術‘),
-> (‘電腦‘),
-> (‘化工‘)
-> ;
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
#往學生表中添加資料：
mysql> INSERT INTO `students`(`name`,`d_id`)
-> VALUES(‘小明‘,1),
-> (‘小紅‘,3),
-> (‘小花‘,3),
-> (‘小新‘,4)
-> ;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
#往學生詳細資料表中添加資料：
mysql> INSERT INTO stu_details
-> VALUES(1,18,‘m‘),
-> (4,20,‘m‘),
-> (3,16,‘f‘),
-> (2,19,‘f‘)
-> ;
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
#往課程表中添加資料：
mysql> INSERT INTO `course`
-> (`name`)VALUES
-> (‘心理學‘),
-> (‘佛學‘),
-> (‘近代史‘),
-> (‘音樂鑒賞‘)
-> ;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
#查看中間表的結構：
mysql> DESC `select`;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s_id | int(11) | NO | PRI | NULL | |
| c_id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)
#往中間表中添加資料
mysql> INSERT INTO `select`
-> VALUES(1,2),
-> (1,4),
-> (2,1),
-> (2,4),
-> (4,1),
-> (4,2),
-> (4,4)
-> ;
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
```

### 查詢

##### 查詢所有記錄

`SELECT * FROM tb_name;`

##### 查詢選中列記錄

`SELECT col_name1,col_name2 FROM tb_name; `

##### 查詢指定條件下的記錄

`SELECT col_name FROM tb_name WHERE 條件`

##### 查詢後為列取別名

`SELECT col_name AS new_name FROM tab_name`

```mysql
#查詢所有記錄：
mysql> SELECT * FROM `students`;
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 1 | 小明 | 1 |
| 2 | 小紅 | 3 |
| 3 | 小花 | 3 |
| 4 | 小新 | 4 |
+------+--------+------+
4 rows in set (0.00 sec)

#查詢選中列記錄
mysql> SELECT name,d_id FROM students;
+--------+------+
| name | d_id |
+--------+------+
| 小明 | 1 |
| 小紅 | 3 |
| 小花 | 3 |
| 小新 | 4 |
+--------+------+
4 rows in set (0.00 sec)

#查詢指定條件下的記錄
mysql> SELECT * FROM students WHERE `name`=‘小紅‘;
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 2 | 小紅 | 3 |
+------+--------+------+
1 row in set (0.00 sec)

#查詢後為列取別名
mysql> SELECT name AS `姓名` ,d_id AS 學院id FROM students WHERE s_id>=2;
+--------+----------+
| 姓名 | 學院id |
+--------+----------+
| 小紅 | 3 |
| 小花 | 3 |
| 小新 | 4 |
+--------+----------+
3 rows in set (0.00 sec)
```

##### 排序`ORDER BY`

`ASC`升序(預設) `DESC`降序

```mysql
#查詢學生的選修表（中間表）
mysql> SELECT * FROM `select`;
+------+------+
| s_id | c_id |
+------+------+
| 2 | 1 |
| 4 | 1 |
| 1 | 2 |
| 4 | 2 |
| 1 | 4 |
| 2 | 4 |
| 4 | 4 |
+------+------+
7 rows in set (0.00 sec)

#按學生學號升序輸出
mysql> SELECT * FROM `select` ORDER BY `s_id`;
+------+------+
| s_id | c_id |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 4 |
+------+------+
7 rows in set (0.00 sec)

#按課程id降序輸出：
mysql> SELECT * FROM `select` ORDER BY `c_id` DESC;
+------+------+
| s_id | c_id |
+------+------+
| 4 | 4 |
| 2 | 4 |
| 1 | 4 |
| 4 | 2 |
| 1 | 2 |
| 4 | 1 |
| 2 | 1 |
+------+------+
7 rows in set (0.00 sec)
```

##### 限制顯示資料的數量`LIMIT`

```mysql
#按學生學號升序輸出的前4條資料
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4;
+------+------+
| s_id | c_id |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
+------+------+
4 rows in set (0.00 sec)
#指定的返回的資料的位置和數量
mysql> SELECT * FROM `select` ORDER BY `s_id` LIMIT 4,2;
+------+------+
| s_id | c_id |
+------+------+
| 4 | 1 |
| 4 | 2 |
+------+------+
2 rows in set (0.00 sec)
```

##### 分組查詢`GROUP BY`

```
例：
對學生表中學院欄進行分組，並統計學院的學生人數：
mysql> SELECT d_id AS 學院id,count(d_id) AS 學生個數 FROM students GROUP BY `d_id`;
+----------+--------------+
| 學院id | 學生個數 |
+----------+--------------+
| 1 | 1 |
| 3 | 2 |
| 4 | 1 |
+----------+--------------+
3 rows in set (0.00 sec)

HAVING分組條件
HAVING 後的欄位必須是SELECT後出現過的
mysql> SELECT d_id AS 學院id,count(d_id) AS 學生個數 FROM students GROUP BY `d_id`HAVING 學生個數=1;
+----------+--------------+
| 學院id | 學生個數 |
+----------+--------------+
| 1 | 1 |
| 4 | 1 |
+----------+--------------+
2 rows in set (0.01 sec)
```

###### 查詢中一些較為常見的函數

```mysql
#求最大年齡
mysql> SELECT MAX(`age`) FROM `stu_details`;
+------------+
| MAX(`age`) |
+------------+
| 20 |
+------------+
1 row in set (0.03 sec)
#求最小年齡
mysql> SELECT MIN(`age`) FROM `stu_details`;
+------------+
| MIN(`age`) |
+------------+
| 16 |
+------------+
1 row in set (0.00 sec)
#求和
mysql> SELECT SUM(`age`) FROM `stu_details`;
+------------+
| SUM(`age`) |
+------------+
| 73 |
+------------+
1 row in set (0.05 sec)
#求平均數
mysql> SELECT AVG(`age`) FROM `stu_details`;
+------------+
| AVG(`age`) |
+------------+
| 18.2500 |
+------------+
1 row in set (0.00 sec)
```

##### 子查詢

出現在其他SQL語句內的SELECT字句。

1)嵌套在查詢內部
2)必須始終出現在圓括弧內
3)可以包含多個關鍵字或條件

```mysql
#尋找出大於平均年齡的資料
mysql> SELECT * FROM `stu_details` WHERE `age`>18.25;
+------+------+--------+
| s_id | age | gender |
+------+------+--------+
| 2 | 19 | f |
| 4 | 20 | m |
+------+------+--------+
2 rows in set (0.00 sec)
#將平均數的SQL語句作為子查詢放入上一條語句中
mysql> SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);
+------+------+--------+
| s_id | age | gender |
+------+------+--------+
| 2 | 19 | f |
| 4 | 20 | m |
+------+------+--------+
2 rows in set (0.10 sec)
```

#### 聯表查詢

##### 內串連`[INNER| CROSS] JOIN`

無條件內串連：
無條件內串連，又名交叉串連/笛卡爾串連
第一張表種的每一向會和另一張表的每一項依次組合

有條件內串連
在無條件的內串連基礎上，加上一個ON子句
當串連的時候，篩選出那些有實際意義的記錄行來進行拼接

在寫條件時注意兩張表的列名是否一樣，
如果時一樣的則要在前面加上表名，tb_name.colname這種形式存在

```mysql
#無條件內串連：
mysql> SELECT * FROM `students` INNER JOIN `department`;
+------+--------+------+----+-----------+
| s_id | name | d_id | id | name |
+------+--------+------+----+-----------+
| 1 | 小明 | 1 | 1 | 外國語 |
| 2 | 小紅 | 3 | 1 | 外國語 |
| 3 | 小花 | 3 | 1 | 外國語 |
| 4 | 小新 | 4 | 1 | 外國語 |
| 1 | 小明 | 1 | 2 | 藝術 |
| 2 | 小紅 | 3 | 2 | 藝術 |
| 3 | 小花 | 3 | 2 | 藝術 |
| 4 | 小新 | 4 | 2 | 藝術 |
| 1 | 小明 | 1 | 3 | 電腦 |
| 2 | 小紅 | 3 | 3 | 電腦 |
| 3 | 小花 | 3 | 3 | 電腦 |
| 4 | 小新 | 4 | 3 | 電腦 |
| 1 | 小明 | 1 | 4 | 化工 |
| 2 | 小紅 | 3 | 4 | 化工 |
| 3 | 小花 | 3 | 4 | 化工 |
| 4 | 小新 | 4 | 4 | 化工 |
+------+--------+------+----+-----------+
16 rows in set (0.04 sec)
#有條件內串連：
mysql> SELECT * FROM `students` INNER JOIN `department`
-> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | id | name |
+------+--------+------+----+-----------+
| 1 | 小明 | 1 | 1 | 外國語 |
| 2 | 小紅 | 3 | 3 | 電腦 |
| 3 | 小花 | 3 | 3 | 電腦 |
| 4 | 小新 | 4 | 4 | 化工 |
+------+--------+------+----+-----------+
4 rows in set (0.03 sec)
```

有條件的外串連：
{ LEFT| RIGHT } [OUTER] JOIN

左外串連：
兩張表做串連的時候，在串連條件不匹配的時候
留下左表中的資料，而右表中的資料以NULL填充

右外串連
對兩張表做串連的時候，在串連條件不匹配的時候
留下右表中的資料，而左表中的資料以NULL填充

```mysql
#往學生表中添加資料，只添加名字
mysql> INSERT INTO students(name)
-> VALUES(‘xixi‘);
Query OK, 1 row affected (0.11 sec)
#查看所有學生表資料
mysql> SELECT * FROM studentS;
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 1 | 小明 | 1 |
| 2 | 小紅 | 3 |
| 3 | 小花 | 3 |
| 4 | 小新 | 4 |
| 5 | xixi | NULL |
+------+--------+------+
5 rows in set (0.00 sec)
#使用內串連加條件只能看到有分配好學院的學生的資訊；
mysql> SELECT * FROM students INNER JOIN department
-> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | id | name |
+------+--------+------+----+-----------+
| 1 | 小明 | 1 | 1 | 外國語 |
| 2 | 小紅 | 3 | 3 | 電腦 |
| 3 | 小花 | 3 | 3 | 電腦 |
| 4 | 小新 | 4 | 4 | 化工 |
+------+--------+------+----+-----------+
4 rows in set (0.02 sec)
#使用左串連把學生的資料全取出來，該學生沒有學院資訊的用NULL填充
mysql> SELECT * FROM students LEFT JOIN department
-> ON d_id=id;
+------+--------+------+------+-----------+
| s_id | name | d_id | id | name |
+------+--------+------+------+-----------+
| 1 | 小明 | 1 | 1 | 外國語 |
| 2 | 小紅 | 3 | 3 | 電腦 |
| 3 | 小花 | 3 | 3 | 電腦 |
| 4 | 小新 | 4 | 4 | 化工 |
| 5 | xixi | NULL | NULL | NULL |
+------+--------+------+------+-----------+
5 rows in set (0.00 sec)
#使用右外串連把目前還沒有學生的學院的資料也顯示出來
mysql> SELECT * FROM students RIGHT JOIN department
-> ON d_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | id | name |
+------+--------+------+----+-----------+
| 1 | 小明 | 1 | 1 | 外國語 |
| 2 | 小紅 | 3 | 3 | 電腦 |
| 3 | 小花 | 3 | 3 | 電腦 |
| 4 | 小新 | 4 | 4 | 化工 |
| NULL | NULL | NULL | 2 | 藝術 |
+------+--------+------+----+-----------+
5 rows in set (0.00 sec)

mysql>
```

##### 查詢SQL的最佳化

MySQL的執行順序

```mysql
1.FROM: 對FROM的左邊的表和右邊的表計算笛卡爾積。產生虛表VT1

2.ON: 對虛表VT1進行ON篩選，只有那些符合<join-condition>的行才會被記錄在虛表VT2中。

3.JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那麼保留表中未匹配的行就會作為外部行添加到虛擬表VT2中，產生虛擬表VT3, rug from子句中包含兩個以上的表的話，那麼就會對上一個join串連產生的結果VT3和下一個表重複執行步驟1~3這三個步驟，一直到處理完所有的表為止。

4.WHERE： 對虛擬表VT3進行WHERE條件過濾。只有符合<where-condition>的記錄才會被插入到虛擬表VT4中。

5.GROUP BY: 根據group by子句中的列，對VT4中的記錄進行分組操作，產生VT5.

6.CUBE | ROLLUP: 對錶VT5進行cube或者rollup操作，產生表VT6.

7.HAVING： 對虛擬表VT6應用having過濾，只有符合<having-condition>的記錄才會被 插入到虛擬表VT7中。

8.SELECT： 執行select操作，選擇指定的列，插入到虛擬表VT8中。

9.DISTINCT： 對VT8中的記錄進行去重。產生虛擬表VT9.

10.ORDER BY: 將虛擬表VT9中的記錄按照<order_by_list>進行排序操作，產生虛擬表VT10.

11.LIMIT：取出指定行的記錄，產生虛擬表VT11, 並將結果返回。
```

通過上面的執行順序不難想到，要想SQL執行更快，就必須把篩選條件盡量的往前面放。如下：

```mysql
SELECT
s.`name`,
e.`name`
FROM
`students` s
LEFT JOIN(
SELECT
se.`s_id`,
c.`name`
FROM
`select` se
JOIN `course` c ON se.`c_id` = c.`id`
) e ON s.`id`=e.`stu_id`

SELECT
*
FROM
`student` s
WHERE
s.`dep_id` = (
SELECT
`id`
FROM
`department` d
WHERE
d.`name` = ‘外國語學院‘
)
```

在這兩個例子中，第一個SQL中的子表只會被查詢一次，但是在第二個SQL中，子表會被執行n次，這個n取決student表中的資料條數，如果子表的資料量很大的話，那麼SQL的執行速度會十分慢。

這是典型的通過執行順序來最佳化SQL，除此之外，要想SQL執行快一點，應該盡量避免模糊比對，如：like,in,not in 等這些匹配條件。

還有幾點建議給大家：

1.盡量避免整表掃描，如SELECT *

2.建立合適的索引

3.使用合適的儲存引擎

4.在JOIN中，盡量用小表LEFT JOIN 大表

5.除非十分必要，盡量不要使用ORDER BY,GROUP BY 和 DISTINCT(去重)，盡量用索引來代替

##### 事務

為了保證資料庫記錄的更新從一個一致性狀態變更為另一個一致性狀態
使用事務來處理是非常必要。

```
例：
建立一張銀行賬戶的表
mysql> CREATE TABLE `account`(
-> `id` INT PRIMARY KEY AUTO_INCREMENT,
-> `name` VARCHAR(20) NOT NULL,
-> `balance` INT
-> );
Query OK, 0 rows affected (0.52 sec)
添加兩個使用者及使用者的存款的資訊
mysql> INSERT INTO `account`(`name`,`balance`)
-> VALUES(‘shangdian‘,10000),
-> (‘xiaoming‘,2000)
-> ;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0

假設現在使用者小明在商店買了500元東西，現在要轉賬給商店，那麼就需要從小明的賬戶上減去500，然後在商店的使用者上加上500，但是如果在減500的過程中出現了系統故障，再重新啟動後發現小明的錢扣了，但商店卻沒有收到，這時候就會出現資料變動不一致。對於這種資料的修改我們需要的就是要麼同時修改成功，要麼同時修改失敗，所以這就需要用事務來進行出來。

START TRANSACTION：開始一個新的事務
COMMIT：提交當前事務，做出永久改變
ROLLBACK：復原當前事務，放棄修改

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
-> SET `balance`= `balance`-50
-> WHERE `name` =‘xiaoming‘
-> ;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

使用ROLLBACK;使資料的修改不生效，回到事務前的狀態：
mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

做一次正確的操作：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`
-> SET `balance`=`balance`-50
-> WHERE `name`=‘xiaoming‘
-> ;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE `account`
-> SET `balance`=`balance`+50
->
-> WHERE `name`=‘shangdian‘
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM `account`;

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

當COMMIT後，資料修改成功，ROLLBACK也沒法回到之前了。

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
```

mysql資料庫查詢操作