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查询题目，一定要先明确到底需要几张表。

1. 先查询jason所在的部门编号

select dep_id from emp where name='jason';

1. 根据部门编号查询部门名称

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内部封装了很多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()

Original: https://www.cnblogs.com/JZjuechen/p/15929638.html
Author: JZEason
Title: MySQL之多表查询、Navicat及pymysql