mysql表關(guān)系

mysql數(shù)據(jù)庫

知識要點：

單表查詢

子查詢

聯(lián)表查詢

事務(wù)

在進行查詢之前，我們要先建好關(guān)系表，并往數(shù)據(jù)表中插入些數(shù)據(jù)。為查詢操作做好準備。

五張關(guān)系表的創(chuàng)建：

#創(chuàng)建并進入數(shù)據(jù)庫：

mysql>CREATEDATABASE`info`;

QueryOK,1rowaffected (0.00sec)

mysql>USE`info`;

Databasechanged

?

#創(chuàng)建學院表：

mysql>CREATETABLE`department`(

->`id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL

?? -> );

QueryOK,0rows affected (0.69sec)

#創(chuàng)建學生表：

mysql>CREATETABLE`students`(

->`s_id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL,

->`d_id`INT,

->FOREIGNKEY(`d_id`)REFERENCES`department`(`id`)

?? -> );

QueryOK,0rows affected (0.65sec)

#創(chuàng)建學生的詳細信息表：

mysql>CREATETABLE`stu_details`(

->`s_id`INTPRIMARYKEY,

->`age`INT,

->`gender`CHAR(1)

?? -> ,

->FOREIGNKEY(`s_id`)REFERENCES`students`(`s_id`)

?? -> );

QueryOK,0rows affected (0.55sec)

#創(chuàng)建課程表：

mysql>CREATETABLE`course`(

->`id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL

?? -> );

QueryOK,0rows affected (0.50sec)

#創(chuàng)建中間表：

mysql>CREATETABLE`select`(

->`s_id`INT,

->`c_id`INT,

->PRIMARYKEY(`s_id`,`c_id`),

->FOREIGNKEY(`s_id`)REFERENCES`students`(`s_id`),

->FOREIGNKEY(`c_id`)REFERENCES`course`(`id`)

?? -> );

QueryOK,0rows affected (0.84sec)

#查看當前存在的表：

mysql>SHOWTABLES;

+----------------+

| Tables_in_info |

+----------------+

| course ? ? ? ? |

| department ? ? |

|select|

| stu_details ?? |

| students ? ? ? |

+----------------+

5rowsinset(0.00sec)

往數(shù)據(jù)表中添加數(shù)據(jù)

#往學院表中添加數(shù)據(jù)：

mysql>INSERTINTO`department`(`name`)

->VALUES('外國語'),

-> ? ? ? ('藝術(shù)'),

-> ? ? ? ('計算機'),

-> ? ? ? ('化工')

?? -> ;

QueryOK,4rows affected (0.11sec)

Records:4Duplicates:0Warnings:0

#往學生表中添加數(shù)據(jù)：

mysql>INSERTINTO`students`(`name`,`d_id`)

->VALUES('小明',1),

-> ? ? ? ('小紅',3),

-> ? ? ? ('小花',3),

-> ? ? ? ('小新',4)

?? -> ;

QueryOK,4rows affected (0.09sec)

Records:4Duplicates:0Warnings:0

#往學生詳細信息表中添加數(shù)據(jù)：

mysql>INSERTINTOstu_details

->VALUES(1,18,'m'),

-> ? ? ? (4,20,'m'),

-> ? ? ? (3,16,'f'),

-> ? ? ? (2,19,'f')

?? -> ;

QueryOK,4rows affected (0.11sec)

Records:4Duplicates:0Warnings:0

#往課程表中添加數(shù)據(jù)：

mysql>INSERTINTO`course`

-> (`name`)VALUES

-> ('心理學'),

-> ('佛學'),

-> ('近代史'),

-> ('音樂鑒賞')

?? -> ;

QueryOK,4rows affected (0.08sec)

Records:4Duplicates:0Warnings:0

#查看中間表的結(jié)構(gòu)：

mysql>DESC`select`;

+-------+---------+------+-----+---------+-------+

|Field| Type ?? |Null|Key|Default| Extra |

+-------+---------+------+-----+---------+-------+

| s_id? |int(11) |NO| PRI |NULL| ? ? ? |

| c_id? |int(11) |NO| PRI |NULL| ? ? ? |

+-------+---------+------+-----+---------+-------+

2rowsinset(0.03sec)

#往中間表中添加數(shù)據(jù)

mysql>INSERTINTO`select`

->VALUES(1,2),

-> ? ? ? (1,4),

-> ? ? ? (2,1),

-> ? ? ? (2,4),

-> ? ? ? (4,1),

-> ? ? ? (4,2),

-> ? ? ? (4,4)

?? -> ;

QueryOK,7rows affected (0.06sec)

Records:7Duplicates:0Warnings: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>SELECT*FROM`students`;

+------+--------+------+

| s_id | name ? | d_id |

+------+--------+------+

|1| 小明 ? |1|

|2| 小紅 ? |3|

|3| 小花 ? |3|

|4| 小新 ? |4|

+------+--------+------+

4rowsinset(0.00sec)

?

#查詢選中列記錄

mysql>SELECTname,d_idFROMstudents;

+--------+------+

| name ? | d_id |

+--------+------+

| 小明 ? |1|

| 小紅 ? |3|

| 小花 ? |3|

| 小新 ? |4|

+--------+------+

4rowsinset(0.00sec)

?

#查詢指定條件下的記錄

mysql>SELECT*FROMstudentsWHERE`name`='小紅';

+------+--------+------+

| s_id | name ? | d_id |

+------+--------+------+

|2| 小紅 ? |3|

+------+--------+------+

1rowinset(0.00sec)

?

#查詢后為列取別名

mysql>SELECTnameAS`姓名`,d_idAS學院idFROMstudentsWHEREs_id>=2;

+--------+----------+

| 姓名 ? | 學院id ? |

+--------+----------+

| 小紅 ? |3|

| 小花 ? |3|

| 小新 ? |4|

+--------+----------+

3rowsinset(0.00sec)

排序ORDER BY

ASC升序(默認)? DESC降序

#查詢學生的選修表（中間表）

mysql>SELECT*FROM`select`;

+------+------+

| s_id | c_id |

+------+------+

|2|1|

|4|1|

|1|2|

|4|2|

|1|4|

|2|4|

|4|4|

+------+------+

7rowsinset(0.00sec)

?

#按學生學號升序輸出

mysql>SELECT*FROM`select`ORDERBY`s_id`;

+------+------+

| s_id | c_id |

+------+------+

|1|2|

|1|4|

|2|1|

|2|4|

|4|1|

|4|2|

|4|4|

+------+------+

7rowsinset(0.00sec)

?

#按課程id降序輸出：

mysql>SELECT*FROM`select`ORDERBY`c_id`DESC;

+------+------+

| s_id | c_id |

+------+------+

|4|4|

|2|4|

|1|4|

|4|2|

|1|2|

|4|1|

|2|1|

+------+------+

7rowsinset(0.00sec)

限制顯示數(shù)據(jù)的數(shù)量LIMIT

#按學生學號升序輸出的前4條數(shù)據(jù)

mysql>SELECT*FROM`select`ORDERBY`s_id`LIMIT4;

+------+------+

| s_id | c_id |

+------+------+

|1|2|

|1|4|

|2|1|

|2|4|

+------+------+

4rowsinset(0.00sec)

#指定的返回的數(shù)據(jù)的位置和數(shù)量

mysql>SELECT*FROM`select`ORDERBY`s_id`LIMIT4,2;

+------+------+

| s_id | c_id |

+------+------+

|4|1|

|4|2|

+------+------+

2rowsinset(0.00sec)

分組查詢GROUP BY

例：

對學生表中學院欄進行分組，并統(tǒng)計學院的學生人數(shù)：

mysql> SELECT d_id AS 學院id,count(d_id) AS 學生個數(shù) FROM students GROUP BY `d_id`;

+----------+--------------+

| 學院id ? | 學生個數(shù) ? ? |

+----------+--------------+

| ? ? ?? 1 | ? ? ? ? ?? 1 |

| ? ? ?? 3 | ? ? ? ? ?? 2 |

| ? ? ?? 4 | ? ? ? ? ?? 1 |

+----------+--------------+

3 rows in set (0.00 sec)

?

HAVING分組條件

HAVING 后的字段必須是SELECT后出現(xiàn)過的

mysql> SELECT d_id AS 學院id,count(d_id) AS 學生個數(shù) FROM students GROUP BY `d_id`HAVING? 學生個數(shù)=1;

+----------+--------------+

| 學院id ? | 學生個數(shù) ? ? |

+----------+--------------+

| ? ? ?? 1 | ? ? ? ? ?? 1 |

| ? ? ?? 4 | ? ? ? ? ?? 1 |

+----------+--------------+

2 rows in set (0.01 sec)

查詢中一些較為常見的函數(shù)

#求最大年齡

mysql>SELECTMAX(`age`)FROM`stu_details`;

+------------+

|MAX(`age`) |

+------------+

|20|

+------------+

1rowinset(0.03sec)

#求最小年齡

mysql>SELECTMIN(`age`)FROM`stu_details`;

+------------+

|MIN(`age`) |

+------------+

|16|

+------------+

1rowinset(0.00sec)

#求和

mysql>SELECTSUM(`age`)FROM`stu_details`;

+------------+

|SUM(`age`) |

+------------+

|73|

+------------+

1rowinset(0.05sec)

#求平均數(shù)

mysql>SELECTAVG(`age`)FROM`stu_details`;

+------------+

|AVG(`age`) |

+------------+

|18.2500|

+------------+

1rowinset(0.00sec)

子查詢

出現(xiàn)在其他SQL語句內(nèi)的SELECT字句。

1)嵌套在查詢內(nèi)部2)必須始終出現(xiàn)在圓括號內(nèi)3)可以包含多個關(guān)鍵字或條件

#查找出大于平均年齡的數(shù)據(jù)

mysql>SELECT*FROM`stu_details`WHERE`age`>18.25;

+------+------+--------+

| s_id | age? | gender |

+------+------+--------+

|2|19| f ? ?? |

|4|20| m ? ?? |

+------+------+--------+

2rowsinset(0.00sec)

#將平均數(shù)的SQL語句作為子查詢放入上一條語句中

mysql>SELECT*FROM`stu_details`WHERE`age`>(SELECTAVG(`age`)FROM`stu_details`);

+------+------+--------+

| s_id | age? | gender |

+------+------+--------+

|2|19| f ? ?? |

|4|20| m ? ?? |

+------+------+--------+

2rowsinset(0.10sec)

聯(lián)表查詢

內(nèi)連接[INNER| CROSS] JOIN

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

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

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

#無條件內(nèi)連接：

mysql>SELECT*FROM`students`INNERJOIN`department`;

+------+--------+------+----+-----------+

| s_id | name ? | d_id | id | name ? ?? |

+------+--------+------+----+-----------+

|1| 小明 ? |1|1| 外國語 ?? |

|2| 小紅 ? |3|1| 外國語 ?? |

|3| 小花 ? |3|1| 外國語 ?? |

|4| 小新 ? |4|1| 外國語 ?? |

|1| 小明 ? |1|2| 藝術(shù) ? ?? |

|2| 小紅 ? |3|2| 藝術(shù) ? ?? |

|3| 小花 ? |3|2| 藝術(shù) ? ?? |

|4| 小新 ? |4|2| 藝術(shù) ? ?? |

|1| 小明 ? |1|3| 計算機 ?? |

|2| 小紅 ? |3|3| 計算機 ?? |

|3| 小花 ? |3|3| 計算機 ?? |

|4| 小新 ? |4|3| 計算機 ?? |

|1| 小明 ? |1|4| 化工 ? ?? |

|2| 小紅 ? |3|4| 化工 ? ?? |

|3| 小花 ? |3|4| 化工 ? ?? |

|4| 小新 ? |4|4| 化工 ? ?? |

+------+--------+------+----+-----------+

16rowsinset(0.04sec)

#有條件內(nèi)連接：

mysql>SELECT*FROM`students`INNERJOIN`department`

->ONd_id=id;

+------+--------+------+----+-----------+

| s_id | name ? | d_id | id | name ? ?? |

+------+--------+------+----+-----------+

|1| 小明 ? |1|1| 外國語 ?? |

|2| 小紅 ? |3|3| 計算機 ?? |

|3| 小花 ? |3|3| 計算機 ?? |

|4| 小新 ? |4|4| 化工 ? ?? |

+------+--------+------+----+-----------+

4rowsinset(0.03sec)

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

左外連接：兩張表做連接的時候，在連接條件不匹配的時候留下左表中的數(shù)據(jù)，而右表中的數(shù)據(jù)以NULL填充

右外連接對兩張表做連接的時候，在連接條件不匹配的時候留下右表中的數(shù)據(jù)，而左表中的數(shù)據(jù)以NULL填充

#往學生表中添加數(shù)據(jù)，只添加名字

mysql>INSERTINTOstudents(name)

->VALUES('xixi');

QueryOK,1rowaffected (0.11sec)

#查看所有學生表數(shù)據(jù)

mysql>SELECT*FROMstudentS;

+------+--------+------+

| s_id | name ? | d_id |

+------+--------+------+

|1| 小明 ? |1|

|2| 小紅 ? |3|

|3| 小花 ? |3|

|4| 小新 ? |4|

|5| xixi ? |NULL|

+------+--------+------+

5rowsinset(0.00sec)

#使用內(nèi)連接加條件只能看到有分配好學院的學生的信息；

mysql>SELECT*FROMstudentsINNERJOINdepartment

->ONd_id=id;

+------+--------+------+----+-----------+

| s_id | name ? | d_id | id | name ? ?? |

+------+--------+------+----+-----------+

|1| 小明 ? |1|1| 外國語 ?? |

|2| 小紅 ? |3|3| 計算機 ?? |

|3| 小花 ? |3|3| 計算機 ?? |

|4| 小新 ? |4|4| 化工 ? ?? |

+------+--------+------+----+-----------+

4rowsinset(0.02sec)

#使用左連接把學生的數(shù)據(jù)全取出來，該學生沒有學院信息的用NULL填充

mysql>SELECT*FROMstudentsLEFTJOINdepartment

->ONd_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|

+------+--------+------+------+-----------+

5rowsinset(0.00sec)

#使用右外連接把目前還沒有學生的學院的數(shù)據(jù)也顯示出來

mysql>SELECT*FROMstudentsRIGHTJOINdepartment

->ONd_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| 藝術(shù) ? ?? |

+------+--------+------+----+-----------+

5rowsinset(0.00sec)

?

mysql>

查詢SQL的優(yōu)化

MySQL的執(zhí)行順序

1.FROM: 對FROM的左邊的表和右邊的表計算笛卡爾積。產(chǎn)生虛表VT1

?

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

?

3.JOIN： 如果指定了OUTERJOIN（比如leftjoin、rightjoin），那么保留表中未匹配的行就會作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3, rugfrom子句中包含兩個以上的表的話，那么就會對上一個join連接產(chǎn)生的結(jié)果VT3和下一個表重復(fù)執(zhí)行步驟1~3這三個步驟，一直到處理完所有的表為止。

?

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

?

5.GROUPBY: 根據(jù)groupby子句中的列，對VT4中的記錄進行分組操作，產(chǎn)生VT5.

?

6.CUBE |ROLLUP: 對表VT5進行cube或者rollup操作，產(chǎn)生表VT6.

?

7.HAVING： 對虛擬表VT6應(yīng)用having過濾，只有符合的記錄才會被 插入到虛擬表VT7中。

?

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

?

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

?

10.ORDERBY: 將虛擬表VT9中的記錄按照進行排序操作，產(chǎn)生虛擬表VT10.

?

11.LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT11, 并將結(jié)果返回。

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

SELECT

? ? s.`name`,

? ? e.`name`

FROM

? ? `students`s

LEFTJOIN(

? ? ? ? SELECT

? ? ? ? ? ? se.`s_id`,

? ? ? ? ? ? c.`name`

? ? ? ? FROM

? ? ? ? ? ? `select`se

? ? ? ? JOIN`course`cONse.`c_id`= c.`id`

? ? ) eONs.`id`=e.`stu_id`

?

?

SELECT

? ? *

FROM

? ? `student`s

WHERE

? ? s.`dep_id`= (

? ? ? ? SELECT

? ? ? ? ? ? `id`

? ? ? ? FROM

? ? ? ? ? ? `department`d

? ? ? ? WHERE

? ? ? ? ? ? d.`name`='外國語學院'

? ? )

在這兩個例子中，第一個SQL中的子表只會被查詢一次，但是在第二個SQL中，子表會被執(zhí)行n次，這個n取決student表中的數(shù)據(jù)條數(shù)，如果子表的數(shù)據(jù)量很大的話，那么SQL的執(zhí)行速度會十分慢。

這是典型的通過執(zhí)行順序來優(yōu)化SQL，除此之外，要想SQL執(zhí)行快一點，應(yīng)該盡量避免模糊匹配，如：like,in,not in 等這些匹配條件。

還有幾點建議給大家：

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

2.建立合適的索引

3.使用合適的存儲引擎

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

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

事務(wù)

為了保證數(shù)據(jù)庫記錄的更新從一個一致性狀態(tài)變更為另一個一致性狀態(tài)使用事務(wù)來處理是非常必要。

例：

創(chuàng)建一張銀行賬戶的表

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

假設(shè)現(xiàn)在用戶小明在商店買了500元東西，現(xiàn)在要轉(zhuǎn)賬給商店，那么就需要從小明的賬戶上減去500，然后在商店的用戶上加上500，但是如果在減500的過程中出現(xiàn)了系統(tǒng)故障，再重新啟動后發(fā)現(xiàn)小明的錢扣了，但商店卻沒有收到，這時候就會出現(xiàn)數(shù)據(jù)變動不一致。對于這種數(shù)據(jù)的修改我們需要的就是要么同時修改成功，要么同時修改失敗，所以這就需要用事務(wù)來進行出來。

START TRANSACTION：開始一個新的事務(wù)

COMMIT：提交當前事務(wù)，做出永久改變

ROLLBACK：回滾當前事務(wù)，放棄修改

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;使數(shù)據(jù)的修改不生效，回到事務(wù)前的狀態(tài)：

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后，數(shù)據(jù)修改成功，ROLLBACK也沒法回到之前了。

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容