MySQL之多表查詢、Navicat及pymysql-有解無憂

一、多表查詢

1.1 資料準備

-- 建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);


-- 插入資料
insert into dep values
(200,'技術'),
(201,'人力資源'),
(202,'銷售'),
(203,'運營'),
(205,'保潔');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

如何查詢jason所在的部門名稱？

首先，涉及到SQL查詢題目，一定要先明確到底需要幾張表，

先查詢jason所在的部門編號

select dep_id from emp where name='jason';

根據部門編號查詢部門名稱
```
select name from dep where id=(select dep_id from emp where name='jason');
```
一條SQL陳述句的查詢結果既可以看成是一張表也可以看成是查詢條件，

補充：

MySQL的兩種注釋語法：

# 注釋

-- 注釋

1.2 多表查詢思路

子查詢

即將SQL陳述句的結果當做另外一條SQL陳述句的查詢條件，對應到日常生活中就是我們常見的解決問題的方式：分步操作
連表操作：通過連接將需要使用到的表拼接成一張大表，之后基于單表查詢完成
- inner join：內連接
- left join：左連接
- right join：右連接
- union：全連接
涉及到多表查詢的時候，為了避免表欄位重復，需要在欄位名的前面加上表名限制，及使用表名.欄位名的方式加以區分，

-- inner join:只拼接兩張表中共有的部分(有對應關系)
select * from emp inner join dep on emp.dep_id = dep.id;

-- left join:以左表為基準展示所有的內容，沒有的用NULL填充
select * from emp left join dep on emp.dep_id = dep.id;

-- right join:以右表為基準展示所有的內容，沒有的用NULL填充
select * from emp right join dep on emp.dep_id = dep.id;

-- union:左右表所有的資料都在 沒有的用NULL填充
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

"""
疑問:上述操作一次只能連接兩張表 如何做到多張表?
	將兩張表的拼接結果當成一張表與跟另外一張表做拼接
	依次往復 即可拼接多張表
"""

上述操作一次只能連接兩張表，那如何做到連接多張表？

其實只需要遵循子查詢的思路就可以，即將兩張表的拼接結果當成一張表再與另外一張表做拼接即可，以此往復，即可拼接多張表，

二、Navicat可視化軟體

Navicat內部封裝了很多SQL的操作，大部分操作用戶只需要使用滑鼠點點的方式就能完成，其內部會自動構建SQL陳述句并執行，

以下是關于這款軟體的下載與詳細使用教程：

MySQL可視化軟體：Navicat的下載與使用

三、多表查詢練習題

現設有如下五張表，其涉及到的欄位名稱和所建立的表關系如下圖所示：

class表對student表：一對多
student表對course表：多對多，兩者表關系記錄在score表中，
teacher表對course表：一對多

撰寫較為復雜的SQL陳述句不要想著一次性寫完，可以邊寫邊看，

-- 1、查詢所有的課程的名稱以及對應的任課老師姓名
SELECT
	teacher.tname,
	course.cname 
FROM
	teacher
	INNER JOIN course ON teacher.tid = course.teacher_id;



-- 2、查詢平均成績大于八十分的同學的姓名和平均成績
SELECT
	student.sname,
	AVG( num ) 
FROM
	score
	INNER JOIN student ON student.sid = score.student_id 
GROUP BY
	student_id 
HAVING
	AVG( num ) > 80;
    
-- 子查詢：
-- 1.1 按照學生id分組并獲取平均成績
select student_id,avg(num) from score group by student_id;
-- 1.2 篩選出平均成績大于80的資料  (針對聚合函式的欄位結果 最好起別名防止沖突)
select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
-- 1.3 將上述SQL的結果與student表拼接
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id;



-- 3、查詢沒有報李平老師課的學生姓名
-- 1.先查詢李平老師教授的課程編號
select course.cid from course where teacher_id = (select tid from teacher where tname ='李平老師');
-- 2.再根據課程id號篩選出所有報了對應課程的學生id號
select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id = 
(select tid from teacher where tname ='李平老師'));
-- 3.最后去學生表中根據id號取反篩選學生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
	WHERE
	course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老師' ) ) 
	);



-- 4、查詢沒有同時選修物理課程和體育課程的學生姓名(只篩選了報了一門課程的，兩門和一門沒報的都不要)
-- 1.先獲取兩門課程的id號
select course.cid from course where cname in ('物理','體育');
-- 2.然后去分數表中先篩選出所有報了物理和體育課程的學生id(包含兩門和一門)
select * from score where course_id in (select course.cid from course where cname in ('物理','體育'));
-- 3.再篩選出只報了一門的學生id(按照學生id分組，然后計數，并過濾出計數結果為1的資料)
select score.student_id from score where course_id in (select course.cid from course where cname in ('物理','體育')) group by score.student_id having count(score.course_id) = 1;
-- 4.最后根據學生id號去student表中篩選學生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		course_id IN ( SELECT course.cid FROM course WHERE cname IN ( '物理', '體育' ) ) 
	GROUP BY
		score.student_id 
	HAVING
	count( score.course_id ) = 1 
	);



-- 5、查詢掛科超過兩門(包括兩門)的學生姓名和班級
-- 1.先篩選出小于60分的資料
select * from score where num < 60;
-- 2.再按照學生id分組，統計掛科數量，篩選出掛科超過兩門的學生id
select student_id from score where num < 60 group by student_id having count(course_id) >=2;
-- 3.最后通過連接student和class表，查詢所需資料
SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );

更多練習可以參考該篇博客：https://www.cnblogs.com/Dominic-Ji/p/10875493.html

四、Python操作MySQL模塊：pymysql

4.1 基本使用

該模塊為第三方模塊，需要下載使用：pip3 install pymysql

import pymysql


# 創建連接，可以連接到MySQL服務端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_5',
    charset='utf8'
)
# 生成一個游標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 定義SQL陳述句
sql = 'select * from userinfo'
# 執行SQL陳述句
cursor.execute(sql)
# 獲取回傳結果
res = cursor.fetchall()
print(res)

4.2 SQL注入問題

import pymysql

# 創建鏈接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='456852',
    database='mydb',
    charset='utf8'
)
# 生成一個游標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
# 獲取用戶名和密碼
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 出現問題SQL陳述句
# sql = "select * from userinfo where name='%s' and password='%s';" % (username, password)
# cursor.execute(sql)
# 針對核心資料 不要自己拼接 交由execute方法幫你篩選再拼接
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
# 執行SQL陳述句
cursor.execute(sql, (username, password))
res = cursor.fetchall()
if res:
    print(res)
    print('登錄成功')
else:
    print('用戶名或密碼錯誤')

SQL注入問題的產生，是由于特殊符號的組合會產生特殊的效果，從而避免常規的邏輯，

在實際生活中，尤其是在注冊用戶名的時候，會非常明顯的提示你很多特殊符號不能用，其內部原因也是一樣的，

結論:

涉及到敏感資料部分，盡量不要自己拼接，交給現成的方法拼接即可；

SQL注入問題的解決方式：execute方法自動幫你解決；

4.3 功能補充

import pymysql

# 創建鏈接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='456852',
    database='mydb',
    charset='utf8',
    autocommit=True  # 涉及到增刪改 自動二次確認
)
# 生成一個游標物件
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 讓資料自動組織成字典
sql1 = 'select * from userinfo'
sql2 = 'insert into userinfo(name,password) values(%s,%s)'
sql3 = 'update userinfo set name="jasonNB" where id=1'
sql4 = 'delete from userinfo where id=2'

# 1.查詢陳述句可以正常執行并獲取結果
# cursor.execute(sql1)
# 2.插入陳述句能夠執行 但是并沒有影響表資料
# cursor.execute(sql2,('jackson',666))
# 3.更新陳述句能夠執行 但是并沒有影響表資料
# res = cursor.execute(sql3)
# print(res)
# 4.洗掉陳述句能夠執行 但是并沒有影響表資料
# res = cursor.execute(sql4)
# print(res)

'''針對增刪改操作 需要二次確認才可生效'''
# cursor.execute(sql2,('jackson',666))
# conn.commit()
# cursor.execute(sql3)
# conn.commit()
# cursor.execute(sql4)
# conn.commit()

# 執行多次SQL陳述句
cursor.executemany(sql2, [('jason111', 123), ('jason222', 321), ('jason333', 222)]) 

# 主動關閉鏈接 釋放資源
# conn.close()

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/431460.html

標籤：其他

上一篇：MongoDB學習筆記：應用程式設計

下一篇：MySQL之視圖、觸發器、事務、索引及其他知識補充