数据库之多表查询
多表连接：笛卡尔积（交叉连接）、内连接、左连接、右连接
一 多表数据
create table dep( id int primary key auto_increment, name varchar(16), work varchar(16) ); create table emp( id int primary key auto_increment, name varchar(16), salary float, dep_id int ); insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车'); insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);
二 笛卡尔积（交叉连接）
''' # 需求: # 查看每位员工的部门的所有信息 select * from emp; select * from dep; # 子查询, 最终结果只能显示单表的信息, 但需求是同时显示两张表的信息 => 先将两张表合成一张表 select * from emp where dep_id in (select id from dep); ''' ''' 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}} 交叉查询: select * from emp, dep; | select * from emp course join dep; +----+----------+--------+--------+----+-----------+--------+ | id | name | salary | dep_id | id | name | work | +----+----------+--------+--------+----+-----------+--------+ | 1 | egon | 3 | 2 | 1 | 市场部 | 销售 | | 1 | egon | 3 | 2 | 2 | 教学部 | 授课 | | 1 | egon | 3 | 2 | 3 | 管理部 | 开车 | | 2 | yanghuhu | 2 | 2 | 1 | 市场部 | 销售 | | 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 | | 2 | yanghuhu | 2 | 2 | 3 | 管理部 | 开车 | | 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 | | 3 | sanjiang | 10 | 1 | 2 | 教学部 | 授课 | | 3 | sanjiang | 10 | 1 | 3 | 管理部 | 开车 | | 4 | owen | 88888 | 2 | 1 | 市场部 | 销售 | | 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 | | 4 | owen | 88888 | 2 | 3 | 管理部 | 开车 | | 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 | | 5 | liujie | 8 | 1 | 2 | 教学部 | 授课 | | 5 | liujie | 8 | 1 | 3 | 管理部 | 开车 | | 6 | yingjie | 1.2 | 0 | 1 | 市场部 | 销售 | | 6 | yingjie | 1.2 | 0 | 2 | 教学部 | 授课 | | 6 | yingjie | 1.2 | 0 | 3 | 管理部 | 开车 | +----+----------+--------+--------+----+-----------+--------+ #=============================================== ''' ''' 做了筛选, 结果<=完整数据, 非笛卡尔积 select * from emp, dep where db2.emp.dep_id = db2.dep.id; # 同sql语句上表现是从两张表拿数据 +----+----------+--------+--------+----+-----------+--------+ | id | name | salary | dep_id | id | name | work | +----+----------+--------+--------+----+-----------+--------+ | 1 | egon | 3 | 2 | 2 | 教学部 | 授课 | | 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 | | 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 | | 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 | | 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 | +----+----------+--------+--------+----+-----------+--------+ #=============================================== # 注意: 同时查询两张表形成新的表,可以称之为虚拟表, 原表与表之间可能存在重复字段, 同时使用时需要明确所属表,必要时还需明确所属数据库 '''
三 内连接
''' 内连接结果为两张表右对应关系的数据（emp有dep没有，emp没有dep有的记录均不会被虚拟表表示）。 语法：左表 inner join 右表 on 两表有关联的字段的条件，on就是产生对于关系的即连接的依据。 ''' eg1:利用内连接查找出每位员工的部门的所有信息 select * from emp inner join dep on emp.dep_id =dep.id; +----+----------+--------+--------+----+-----------+--------+ | id | name | salary | dep_id | id | name | work | +----+----------+--------+--------+----+-----------+--------+ | 1 | egon | 3 | 2 | 2 | 教学部 | 授课 | | 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 | | 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 | | 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 | | 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 | +----+----------+--------+--------+----+-----------+--------+
四 左连接
''' left join on 左连接：在内连接的基础上只保留左表特有的记录 语法 ： 左表 left join 右表 on 两表有关联的字段的条件 ''' eg2:利用左连接，找到所有员工的部门信息 select emp.name'员工',dep.name'部门',dep.work'工作' from emp left join dep on emp.dep_id = dep.id; +----------+-----------+--------+ | 员工 | 部门 | 工作 | +----------+-----------+--------+ | sanjiang | 市场部 | 销售 | | liujie | 市场部 | 销售 | | egon | 教学部 | 授课 | | yanghuhu | 教学部 | 授课 | | owen | 教学部 | 授课 | | yingjie | NULL | NULL | +----------+-----------+--------+
五 右连接
''' right join on 右连接：在内连接的基础上只保留右表特有的记录 语法: 左表 right join 右表 on 两表有关联的字段的条件 ''' eg3:利用右连接找出所有人员的部门信息 select emp.name'员工',dep.name'部门',dep.work'工作' from emp right join dep on emp.dep_id = dep.id; +----------+-----------+--------+ | 员工 | 部门 | 工作 | +----------+-----------+--------+ | egon | 教学部 | 授课 | | yanghuhu | 教学部 | 授课 | | sanjiang | 市场部 | 销售 | | owen | 教学部 | 授课 | | liujie | 市场部 | 销售 | | NULL | 管理部 | 开车 | +----------+-----------+--------+
六 全连接
''' 全连接：在内连接的基础上分别保留左表和右表的内容 语法：mysql 没有 full join on 语法，但是可以通过去重达到效果 ''' eg4： 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 | salary | dep_id | id | name | work | +------+----------+--------+--------+------+-----------+--------+ | 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 | | 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 | | 1 | egon | 3 | 2 | 2 | 教学部 | 授课 | | 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 | | 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 | | 6 | yingjie | 1.2 | 0 | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 3 | 管理部 | 开车 | +------+----------+--------+--------+------+-----------+--------+
练习
1.查询每一位员工对应的工作职责
2.查询每一个部门下的员工们及员工职责
1.查询每一位员工对应的工作职责 利用左连接的方式：select emp.name'员工',dep.work'工作职责'from emp left join dep on emp.dep_id = dep.id; +----------+--------------+ | 员工 | 工作职责 | +----------+--------------+ | sanjiang | 销售 | | liujie | 销售 | | egon | 授课 | | yanghuhu | 授课 | | owen | 授课 | | yingjie | NULL | +----------+--------------+ #=============================================================== 利用右连接的方式:select emp.name'员工',dep.work'工作职责'from emp right join dep on emp.dep_id = dep.id; +----------+--------------+ | 员工 | 工作职责 | +----------+--------------+ | egon | 授课 | | yanghuhu | 授课 | | sanjiang | 销售 | | owen | 授课 | | liujie | 销售 | | NULL | 开车 | +----------+--------------+ #=============================================================== 利用内连接的方式:select emp.name'员工',dep.work'工作职责'from emp inner join dep on emp.dep_id = dep.id; +----------+--------------+ | 员工 | 工作职责 | +----------+--------------+ | egon | 授课 | | yanghuhu | 授课 | | sanjiang | 销售 | | owen | 授课 | | liujie | 销售 | +----------+--------------+
2.查询每一个部门下的员工们及员工职责 不分组：select dep.name'部门',emp.name'员工',dep.work'工作职责'from emp right join dep on emp.dep_id = dep.id; +-----------+----------+--------------+ | 部门 | 员工 | 工作职责 | +-----------+----------+--------------+ | 教学部 | egon | 授课 | | 教学部 | yanghuhu | 授课 | | 市场部 | sanjiang | 销售 | | 教学部 | owen | 授课 | | 市场部 | liujie | 销售 | | 管理部 | NULL | 开车 | +-----------+----------+--------------+ #============================================================= 这样做达不到我们的要求，因此分组 分组的过程：1.每一个部门 => dep的信息全部保留，因此需要分组 2.员工职责 dep.work ，由于分组根据dep.id，因此需要用聚合函数 3.员工们 emp.name ,由于员工有很多个，所以需要拼接group_concat 分组： select max(dep.name),max(dep.work),group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep.id; +---------------+---------------+------------------------+ | max(dep.name) | max(dep.work) | group_concat(emp.name) | +---------------+---------------+------------------------+ | 市场部 | 销售 | sanjiang,liujie | | 教学部 | 授课 | owen,egon,yanghuhu | | 管理部 | 开车 | NULL | +---------------+---------------+------------------------+
七 pycharm 中数据库的连接
''' 过程: 1. 建立连接 2. 设置一个可以执行sql语句的对象 - 游标 3. 通过该对象执行sql语句得到执行结果 4. 端口连接 ''' import pymysql # 1. 建立连接 conn = pymysql.connect(host="localhost", port=3306, db='db2', user='root', password='root') # 2. 设置游标 cursor = conn.cursor(pymysql.cursors.DictCursor) # 设置游标的具体类型, DictCursor拿到字段名 # 3. 执行sql语句 sql = 'select * from emp' res = cursor.execute(sql) # 结果的行数 print(res) # 需求: 具体的一条条记录 tag = cursor.fetchone() print(tag) print(tag['salary']) tag = cursor.fetchone() print(tag) # cursor.scroll(1, mode='relative') # 指针相对于上一次位置往后偏移1条记录 cursor.scroll(res - 1, mode='absolute') # 指针绝对, 游标永远从头开始偏移 tags = cursor.fetchall() print(tags) cursor.close() conn.close()
''' sql语法中 注释: /**/ | -- | # 什么是sql注入: 通过书写sql包含(注释相关的)特殊字符, 让原有的sql执行顺序发生改变, 从而改变执行得到的sql 目的: 绕过原有的sql安全认证, 达到对数据库攻击的目的 ''' import pymysql conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2') cursor = conn.cursor(pymysql.cursors.DictCursor) # 登录 # 得到用户输入的账户密码 usr = input("请输入用户名: ") #abc pwd = input("请输入密码: ") #123 # sql注入 # 1.知道用户名: abc" -- hehe | ooo # select * from user where usr="abc" -- hehe" and pwd="ooo" # 2.不知道用户名 aaa" or 1=1 -- hehe | 000 # select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000" # 处理方式 # 对输入的账户密码做完全处理 => 不可能形成达到sql注入的特殊语法 => 正则 # 和数据库的账户密码进行配对 # sql = 'select * from user where usr="%s" and pwd="%s"' % (usr, pwd) # select * from user where usr="abc" and pwd="123" # res = cursor.execute(sql) # pymysql已经处理了sql注入 sql = 'select * from user where usr=%s and pwd=%s' res = cursor.execute(sql, (usr, pwd)) # print(res) if res: print('login success') else: print('login failed') cursor.close() conn.close()
import pymysql conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2') cursor = conn.cursor(pymysql.cursors.DictCursor) # 增 # sql1 = 'insert into user(usr, pwd) values (%s, %s)' # cursor执行sql语句,在内存中完成了对数据的插入, 但不能将数据存放到硬盘 # 会将id完成自增 # 插入单条 # res = cursor.execute(sql1, ("opq", "123")) # 插入多条 #res = cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")]) # print(res) # 将内存中的数据提交给硬盘, 完成真实意义上的数据存储 # conn.commit() # 删 # sql2 = 'delete from user where usr=%s' # res = cursor.execute(sql2, ("aaa")) # print(res) # conn.commit() # 改 sql3 = 'update user set pwd=%s where usr=%s' res = cursor.execute(sql3, ("222", "bbb")) conn.commit() cursor.close() conn.close()
杜绝秃头！！！
