10.10 多表連接查詢 10.101 內連接 把兩張表有對應關係的記錄連接成一張虛擬表 #應用： select * from emp,dep where emp.dep_id = dep.id and dep.name = "技術"; select * from emp inner join de ...
10.10 多表連接查詢
10.101 內連接
把兩張表有對應關係的記錄連接成一張虛擬表
select * from emp,dep； #連接兩張表的笛卡爾積 select * from emp,dep where emp.dep_id = dep.id; # 不推薦用where連接表 select * from emp inner join dep on emp.dep_id = dep.id; #推薦 +----+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術 | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術 | +----+-----------+--------+------+--------+------+--------------+
#應用： select * from emp,dep where emp.dep_id = dep.id and dep.name = "技術"; select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技術"; +----+-----------+------+------+--------+------+--------+ | id | name | sex | age | dep_id | id | name | +----+-----------+------+------+--------+------+--------+ | 1 | egon | male | 18 | 200 | 200 | 技術 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術 | +----+-----------+------+------+--------+------+--------+應用
10.102 左連接
在內連接的基礎上，保留左邊沒有對應關係的記錄
select * from emp left join dep on emp.dep_id = dep.id; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術 | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | +----+------------+--------+------+--------+------+--------------+
10.103 右連接
在內連接的基礎上，保留右邊沒有對應關係的記錄
select * from emp right join dep on emp.dep_id = dep.id; +------+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術 | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術 | | NULL | NULL | NULL | NULL | NULL | 203 | 運營 | +------+-----------+--------+------+--------+------+--------------+
10.104 全連接
在內連接的基礎上，保留左、右邊沒有對應關係的記錄，並去重
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; +------+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技術 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技術 | | 2 | alex | female | 48 | 201 | 201 | 人力資源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力資源 | | 4 | yuanhao | female | 28 | 202 | 202 | 銷售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 運營 | +------+------------+--------+------+--------+------+--------------+
補充：多表連接可以不斷地與虛擬表連接
#查找各部門最高工資 select t1.* from emp as t1 inner join (select post,max(salary) as ms from emp group by post) as t2 on t1.post = t2.post where t1.salary = t2.ms;View Code
10.11 子查詢
把一個查詢語句用括弧括起來，當做另外一條查詢語句的條件去用，稱為子查詢
#查詢技術部員工的名字 select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技術";#連接查詢 select name from emp where dep_id =(select id from dep where name="技術"); #子查詢 +-----------+ | name | +-----------+ | egon | | liwenzhou | +-----------+ #查詢平均年齡在25歲以上的部門名 #子查詢 select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25); select dep.name from emp inner join dep on emp.dep_id = dep.id #連接查詢 group by dep.name having avg(age) > 25; +--------------+ | name | +--------------+ | 人力資源 | | 銷售 | +--------------+ #查詢每個部門最新入職的那位員工 select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from (emp as t1) inner join (select post,max(hire_date) as max_date from emp group by post) as t2 #拿到最大雇佣時間 on t1.post = t2.post where t1.hire_date = t2.max_date; +----+--------+-----------------------------------------+---- | id | name | post | hire_date | post | max_date | +----+--------+-----------------------------------------+----- | 1 | egon | 外交大使 | 2017-03-01 | 外交大使 | 2017-03-01 | | 2 | alex | teacher | 2015-03-02 | teacher | 2015-03-02 | | 13 | 格格 | sale | 2017-01-27 | sale | 2017-01-27 | | 14 | 張野 | operation| 2016-03-11 | operation| 2016-03-11 | +----+--------+-----------------------------------------+-----
exists( )：括弧內的值存在時滿足條件
select * from emp where exists (select id from dep where id > 3); #找到所有
10.12 pymysql模塊的使用
10.121 pymysql查
import pymysql #pip3 install pymysql conn=pymysql.connect( #連接 host='127.0.0.1', port=3306, user='root', password='', database='db2', charset='utf8') cursor=conn.cursor(pymysql.cursors.DictCursor)#以字典形式顯示表的記錄 rows=cursor.execute('show tables;') #1 顯示受影響的行數(row),此處為有表的條數 print(rows) rows=cursor.execute('select * from emp;') #18 此處rows為emp表內有記錄的條數 print(rows) print(cursor.fetchone()) #查看一條記錄 一個字典{key:value} print(cursor.fetchmany(2)) #查看多條記錄 [{key:value},] #print(cursor.fetchall()) #查看所有記錄 強調：下一次查找是接著上一次查找的位置繼續 cursor.scroll(0,'absolute') #絕對移動，以0位置為參照顯示 print(cursor.fetchone()) cursor.scroll(1,'relative') #相對移動，相對當前位置移動1條記錄 print(cursor.fetchone()) cursor.close()#游標 conn.close()
10.122 防止sql註入問題
在服務端防止sql註入問題：不要自己拼接字元串，讓pymysql模塊去拼接，pymysql拼接時會過濾非法字元
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='', database='db2', charset='utf8' ) cursor=conn.cursor(pymysql.cursors.DictCursor) inp_user=input('用戶名>>：').strip() #inp_user="" inp_pwd=input('密碼>>：').strip() #inp_pwd="" sql="select * from user where username=%s and password=%s" print(sql) rows=cursor.execute(sql,(inp_user,inp_pwd))#輸入的用戶名和密碼中的非法字元會被過濾掉 if rows: print('登錄成功') else: print('登錄失敗') cursor.close() conn.close()View Code
10.123 pymysql增刪改
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='', database='db2', charset='utf8') cursor=conn.cursor(pymysql.cursors.DictCursor) sql='insert into user(username,password) values(%s,%s)' #插入單行記錄 rows=cursor.execute(sql,('EGON','123456')) print(rows) print(cursor.lastrowid) #顯示當前最後一行的id sql='insert into user(username,password) values(%s,%s)' #一次插入多行記錄 rows=cursor.executemany(sql,[('lwz','123'),('evia','455'),('lsd','333')]) print(rows) rows=cursor.execute('update user set username="alexSB" where id=2')#修改記錄 print(rows) conn.commit() # 只有commit提交才會完成真正的修改 cursor.close() conn.close()
