SQL實戰：多表查詢篇

文章目錄

多表查詢
- 創建練習用的數據庫
- 鏈接/連接查詢
- - 交叉連接
  - 自然連接
  - 內連接(取交集)
  - 外連接
  - - 左外連接/右外連接
  - 自連接
- 子查詢
- 聯合查詢
- 總結
數據庫的備份和恢復
- 命令行操作

多表查詢

-- 獲得 alice的 部門所在城市
select * from staff where name='alice';	-- 獲得dept_id=1
select city from dept where id = 1;mysql> select * from staff where name='alice';-- 獲得dept_id=1
+----+-------+--------+------------+---------+-------+------+----------+----------+
| id | name  | gender | birth      | dept_id | level | mgr  | salary   | bonus    |
+----+-------+--------+------------+---------+-------+------+----------+----------+
|  1 | alice | female | 1999-04-26 |       1 | L4    | NULL | 20000.00 | 50000.00 |
+----+-------+--------+------------+---------+-------+------+----------+----------+
1 row in set (0.00 sec)mysql> select city from dept where id = 1;
+--------+
| city   |
+--------+
| 北京   |
+--------+
1 row in set (0.00 sec)

創建練習用的數據庫

-- 創建用于練習的數據庫-- 班級
create table class(id int(11) NOT NULL primary key AUTO_INCREMENT,class_name varchar(10)
);-- 成績
create table score(id int(11) NOT NULL primary key AUTO_INCREMENT,student_id int(11),class_id int(11),chinese float,english float
); 
-- 學生信息
create table student(id int(11) NOT NULL primary key AUTO_INCREMENT,stduent_name varchar(20),mobile varchar(20)
);
-- 劇本 
create table script(id int(11) NOT NULL primary key auto_increment,play_name varchar(20),play_position varchar(20)
);
-- 演出表
create table `show`(id int NOT NULL primary key auto_increment,student_id int,script_id int
);-- 班級插入數據
insert into class values(NULL, 1);
insert into class values(NULL, 2);-- 成績表插入數據
insert into score  values(NULL, 1, 1, 80, 60);
insert into score  values(NULL, 2, 1, 70, 50);
insert into score  values(NULL, 3, 2, 60, 90);
insert into score  values(NULL, 4, 2, 80, 50);-- 學生信息插入數據
insert into student values(NULL, '小明', '111');
insert into student values(NULL, '小紅', '222');
insert into student values(NULL, '小黑', '333');
insert into student values(NULL, '小百', '444');-- 劇本表插入數據
insert into script values(NULL, '體育', '操場');
insert into script values(NULL, '語文', '教室');-- 演出表插入數據 
insert into `show` values(NULL, 1, 1);
insert into `show` values(NULL, 1, 2);
insert into `show` values(NULL, 2, 1);
insert into `show` values(NULL, 4, 1);

鏈接/連接查詢

交叉連接

mysql> select * from student;
+----+--------------+--------+
| id | stduent_name | mobile |
+----+--------------+--------+
|  1 | 小明         | 111    |
|  2 | 小紅         | 222    |
|  3 | 小黑         | 333    |
|  4 | 小百         | 444    |
+----+--------------+--------+
4 rows in set (0.00 sec)mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
|  1 |          1 |        1 |      80 |      60 |
|  2 |          2 |        1 |      70 |      50 |
|  3 |          3 |        2 |      60 |      90 |
|  4 |          4 |        2 |      80 |      50 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)-- 交叉連接 會生成兩個表數據相乘那么多條數據 4*4=16
mysql> select * from student cross join score;
+----+--------------+--------+----+------------+----------+---------+---------+
| id | stduent_name | mobile | id | student_id | class_id | chinese | english |
+----+--------------+--------+----+------------+----------+---------+---------+
|  4 | 小百         | 444    |  1 |          1 |        1 |      80 |      60 |
|  3 | 小黑         | 333    |  1 |          1 |        1 |      80 |      60 |
|  2 | 小紅         | 222    |  1 |          1 |        1 |      80 |      60 |
|  1 | 小明         | 111    |  1 |          1 |        1 |      80 |      60 |
|  4 | 小百         | 444    |  2 |          2 |        1 |      70 |      50 |
|  3 | 小黑         | 333    |  2 |          2 |        1 |      70 |      50 |
|  2 | 小紅         | 222    |  2 |          2 |        1 |      70 |      50 |
|  1 | 小明         | 111    |  2 |          2 |        1 |      70 |      50 |
|  4 | 小百         | 444    |  3 |          3 |        2 |      60 |      90 |
|  3 | 小黑         | 333    |  3 |          3 |        2 |      60 |      90 |
|  2 | 小紅         | 222    |  3 |          3 |        2 |      60 |      90 |
|  1 | 小明         | 111    |  3 |          3 |        2 |      60 |      90 |
|  4 | 小百         | 444    |  4 |          4 |        2 |      80 |      50 |
|  3 | 小黑         | 333    |  4 |          4 |        2 |      80 |      50 |
|  2 | 小紅         | 222    |  4 |          4 |        2 |      80 |      50 |
|  1 | 小明         | 111    |  4 |          4 |        2 |      80 |      50 |
+----+--------------+--------+----+------------+----------+---------+---------+
16 rows in set (0.00 sec)-- 加條件進行過濾 留下匹配的數據
mysql> select -> *-> from student -> cross join score -> where student.id = score.student_id;
+----+--------------+--------+----+------------+----------+---------+---------+
| id | stduent_name | mobile | id | student_id | class_id | chinese | english |
+----+--------------+--------+----+------------+----------+---------+---------+
|  1 | 小明         | 111    |  1 |          1 |        1 |      80 |      60 |
|  2 | 小紅         | 222    |  2 |          2 |        1 |      70 |      50 |
|  3 | 小黑         | 333    |  3 |          3 |        2 |      60 |      90 |
|  4 | 小百         | 444    |  4 |          4 |        2 |      80 |      50 |
+----+--------------+--------+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)

語法上是會先產生笛卡爾集，然后再進行過濾。（如果數據較多會性能很差）

但是服務器中會有優化器會進行優化。

自然連接

按照兩個表中具有相同名字的列，進行匹配的。

mysql> select * from class;
+----+------------+
| id | class_name |
+----+------------+
|  1 | 1          |
|  2 | 2          |
+----+------------+
2 rows in set (0.00 sec)mysql> select * from script;
+----+-----------+---------------+
| id | play_name | play_position |
+----+-----------+---------------+
|  1 | 體育      | 操場          |
|  2 | 語文      | 教室          |
+----+-----------+---------------+
2 rows in set (0.00 sec)-- 自然連接 會將兩個表中具有相同的列的數據進行連接  都匹配一遍和交叉連接有點像
mysql> select * from class natrual join script;
+----+------------+----+-----------+---------------+
| id | class_name | id | play_name | play_position |
+----+------------+----+-----------+---------------+
|  2 | 2          |  1 | 體育      | 操場          |
|  1 | 1          |  1 | 體育      | 操場          |
|  2 | 2          |  2 | 語文      | 教室          |
|  1 | 1          |  2 | 語文      | 教室          |
+----+------------+----+-----------+---------------+
4 rows in set (0.00 sec)

內連接(取交集)

內連接優化器會劃分主表和副表。底層就是兩個for循環 for(主表 for(副表) ) 但是在查詢副表的時候有時候會使用3-4次io操作進行查找，又是會一個一個遍歷，后面會詳細講。

內連接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選，我們前面學習的查詢都是內連接（隱式內連接），也是在開發過程中使用的最多的連接查詢。

表1 inner join 表2 [on 表1和表2的關聯條件] [代表可選]

在這里插入圖片描述

mysql> -- 獲取每個人的人名，以及這個人的語文成績
mysql> select -> student.student_name, score.chinese-> from student -> inner join score-> on student.id = score.student_id;
+--------------+---------+
| student_name | chinese |
+--------------+---------+
| 小明         |      80 |
| 小紅         |      70 |
| 小黑         |      60 |
| 小百         |      80 |
+--------------+---------+
4 rows in set (0.00 sec)-- 內連接也可以多表連接
-- 查詢每個人的班級信息 姓名 語文 英語成績
selectcl.class_name, st.student_name, sc.english, sc.chinese
from student st
inner join score sc on st.id = sc.student_id
inner join class cl on sc.class_id = cl.id;mysql> select-> cl.class_name, st.student_name, sc.english, sc.chinese-> from student st-> inner join score sc on st.id = sc.student_id-> inner join class cl on sc.class_id = cl.id;
+------------+--------------+---------+---------+
| class_name | student_name | english | chinese |
+------------+--------------+---------+---------+
| 1          | 小明         |      60 |      80 |
| 1          | 小紅         |      50 |      70 |
| 2          | 小黑         |      90 |      60 |
| 2          | 小百         |      50 |      80 |
+------------+--------------+---------+---------+
4 rows in set (0.00 sec)

外連接

外連接與內連接的區別

? （1）在外連接中可以指明主副表（主表都會進行遍歷，所以可以將數據多的表設置為副表，這樣效率會提升，內連接不能指明主副表但是也不用擔心，因為優化器會進行優化）

? （2）外連接要求主表的數據無論和副表匹配與否，都要存在在結果集中。

主副表的判斷

-- left outer join 左外連接 所以 左面的student就是主表
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id; -- right outer join 右外連接 所以 右面的score就是主表
select st.student_name, sc.chinese, sc.english
from student st
right outer join score sc on st.id = sc.student_id;

在這里插入圖片描述

左外連接/右外連接

必須加上on子句，否則會報錯。

左外連接以左表為主表，當右表中沒有記錄的時候，會全部用NULL替代。

右外連接以右表為主表，當右表中沒有記錄的時候，會全部用NULL代替。

-- 查詢一個人的人名 語文 英語成績
-- 左外連接
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id; mysql> -- 查詢一個人的人名 語文 英語成績
mysql> -- 左外連接
mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id; 
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明         |      80 |      60 |
| 小紅         |      70 |      50 |
| 小黑         |      60 |      90 |
| 小百         |      80 |      50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 右外連接
select st.student_name, sc.chinese, sc.english
from student st
right outer join score sc on st.id = sc.student_id;mysql> -- 右外連接
mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> right outer join score sc on st.id = sc.student_id;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明         |      80 |      60 |
| 小紅         |      70 |      50 |
| 小黑         |      60 |      90 |
| 小百         |      80 |      50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 從上面結果來看好像左外連接 與 右外連接好像沒有什么區別-- 我在學生表中插入一條數據
insert into student values(NULL, '小綠', 555);
-- 再次左外連接 右外連接 查看現象
-- 會驗證上面主表的數據無論與副表匹配與否都會出現在結果集中mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id; 
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明         |      80 |      60 |
| 小紅         |      70 |      50 |
| 小黑         |      60 |      90 |
| 小百         |      80 |      50 |
| 小綠         |    NULL |    NULL |
+--------------+---------+---------+
5 rows in set (0.00 sec)mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> right outer join score sc on st.id = sc.student_id; 
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明         |      80 |      60 |
| 小紅         |      70 |      50 |
| 小黑         |      60 |      90 |
| 小百         |      80 |      50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 可以將左外連接結果集再使用條件進行過濾  或者 使用內連接匹配的才會顯示
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id
where sc.english is not null;mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id-> where sc.english is not null;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明         |      80 |      60 |
| 小紅         |      70 |      50 |
| 小黑         |      60 |      90 |
| 小百         |      80 |      50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)

日常寫代碼我們都會將outer 關鍵字進行省略對內容沒有影響這是一種省略的寫法。

自連接

在這里插入圖片描述

-- 知道誰的英語成績 小于 id為1的同學的成績-- 可以怎么理解呢？
-- 就是可以把s1 s2 看做內外循環 第一次循環找到id=1的數據 第二次循環看那個條件滿足 and后面的條件，然后保存。selects2.*
from score s1, score s2
where s1.student_id = 1 and s2.english < s1.english;mysql> select-> s2.*-> from score s1, score s2-> where s1.student_id = 1 and s2.english < s1.english;
+----+------------+----------+---- -----+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
|  2 |          2 |        1 |      70 |      50 |
|  4 |          4 |        2 |      80 |      50 |
+----+------------+----------+---------+---------+
2 rows in set (0.00 sec)

子查詢

又稱為嵌套查詢

子查詢在刪除/修改/查詢SQL操作中，用到了另一個查詢結果

在這里插入圖片描述

-- 查詢英語成績小于 小明英語成績 的同學的信息select * from student where student_name = '小明';
select * from score where student_id = 小明的id;
select * from score where english<60;-- 獲得小明的英語成績
select english
from score
where student_id = (select id from student where student_name = '小明'
);-- 一步一步查詢
mysql> select * from student where student_name = '小明';
+----+--------------+--------+
| id | student_name | mobile |
+----+--------------+--------+
|  1 | 小明         | 111    |
+----+--------------+--------+
1 row in set (0.00 sec)mysql> select english from score where student_id = 1;
+---------+
| english |
+---------+
|      60 |
+---------+
1 row in set (0.00 sec)mysql> select * from score where english<60;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
|  2 |          2 |        1 |      70 |      50 |
|  4 |          4 |        2 |      80 |      50 |
+----+------------+----------+---------+---------+
2 rows in set (0.00 sec)-- 子查詢
mysql> select -> english-> from score-> where student_id = (-> select id from student where student_name = '小明'-> );
+---------+
| english |
+---------+
|      60 |
+---------+
1 row in set (0.00 sec)-- 子查詢 用在修改操作-- 將小于小明的英語成績的同學的英語成績都改為95
update score set english = 95
where english < (select * from (select englishfrom scorewhere student_id = (select id from student where student_name='小明')));
-- 會報錯 因為服務器認為從一個表中查找數據然后對該表進行修改很危險
mysql> update score set english = 95-> where english < (-> select ->     english->    from score->     where student_id = (->     select id from student where student_name='小明'->     )-> );
-- ERROR 1093 (HY000): You can't specify target table 'score' for update in FROM clause-- 欺騙服務器
-- 將子查詢的結果起一個別名update score set english = 95
where english < (select * from (select englishfrom scorewhere student_id = (select id from student where student_name='小明')) as temp
);mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
|  1 |          1 |        1 |      80 |      60 |
|  2 |          2 |        1 |      70 |      50 |
|  3 |          3 |        2 |      60 |      90 |
|  4 |          4 |        2 |      80 |      50 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)mysql> update score set english = 95-> where english < (-> select * from ->         (->             select ->                 english->             from score->             where student_id = (->                 select id from student where student_name='小明'->                 )->          ) as temp-> );
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
|  1 |          1 |        1 |      80 |      60 |
|  2 |          2 |        1 |      70 |      95 |
|  3 |          3 |        2 |      60 |      90 |
|  4 |          4 |        2 |      80 |      95 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)

聯合查詢

把兩個sql語句查詢的結果合并到一起返回

將左查詢和右查詢進行并集操作，會去除重復的行。

在這里插入圖片描述

select * from score where chinese >= 70;
select * from score where english >= 90;-- 聯合查詢
select * from score where chinese >= 70
union
select * from score where english >= 90;

總結

在這里插入圖片描述

數據庫的備份和恢復

也可以理解為復制一個數據庫。

比如：數據庫的要備份的時候，有人也在使用數據庫，所以要加鎖，不用我們來進行加鎖，服務器會自己進行加鎖處理。

命令行操作

-- 數據庫備份：cmd命令行下
mysqldump -u root -p 數據庫名稱>文件名.sql-- 數據庫恢復
-- 1.創建數據庫并選擇該數據庫
create database dbname;-- 恢復數據
-- 1.不用連接mysql服務器，直接使用命令行操作
mysql -u root -p 數據庫<文件名.sql
-- 2.連接數據庫服務器
use dbname;
source 文件名.sql;