Navicat可视化软件及多表查询的方法
一、多表查询的两种方法
1.连表操作
1.内连接
inner join ----->> 只连接两张表中公有的数据部分
select * from emp inner join dep on emp.dep_id = dep.id;
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+--------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
2.左连接
left join ------>> 以左表为基准，展示左表所有的数据，如果没有对应项则用NULL填充
select * from emp left join dep on emp.dep_id = dep.id;
mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+--------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+--------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
3.右连接
right join ------>> 以右表为基准，展示右表所有的数据，如果没有对应项就用NULL填充
select * from emp right join dep on emp.dep_id = dep.id;
mysql> select * from emp right join dep on emp.dep_id = dep.id;
+------+--------+--------+------+--------+-----+--------------+
| id | name | sex | age | dep_id | id | name |
+------+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 财务 |
+------+--------+--------+------+--------+-----+--------------+
6 rows in set (0.00 sec)
4.全连接
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;
mysql> 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 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | 财务 |
+------+--------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
ps；在做多表连接时，将拼接之后的表起别名当成一张表再去与其他表拼接，再起别名当成一张表，再与其他表拼接，往复即可2.子查询
思路：将一条SQL语句用括号括起来，当成另外一条SQL语句的查询条件
题目：求姓名是jason的员工部门名称
解决方法：分布解决
步骤1：先根据jason 获取部门编号
select dep_id from emp where name = 'jason';
步骤2：再根据部门编号获取部门名称
select name from dep where id = 200;
select name from dep where id = (select dep_id from emp where name = 'jason');
mysql> select name from dep where id = (select dep_id from emp where name = 'jason');
+--------+
| name |
+--------+
| 技术 |
+--------+
1 row in set (0.00 sec)二、小知识点补充说明
1.concat与concat_ws
1.1concat用于分组之前的字段拼接操作
select concat(name,'|',age) from emp;
mysql> select concat(name,'|',age) from emp;
+----------------------+
| concat(name,'|',age) |
+----------------------+
| jason|18 |
| dragon|48 |
| kevin|18 |
| nick|28 |
| owen|18 |
| jerry|18 |
+----------------------+
6 rows in set (0.00 sec)
1.2concat_ws用于拼接多个字段且中间的连接符一致
select concat_ws('|',name,sex,age) from emp;
mysql> select concat_ws('|',name,sex,age) from emp;
+-----------------------------+
| concat_ws('|',name,sex,age) |
+-----------------------------+
| jason|male|18 |
| dragon|female|48 |
| kevin|male|18 |
| nick|male|28 |
| owen|male|18 |
| jerry|female|18 |
+-----------------------------+
6 rows in set (0.00 sec)
2.exists xxx是否存在
sql1 exists sql2
sql2有结果的情况下才会执行sql1 ,否则不执行sql1,返回空数据
select * from dep where exists (select * from emp where emp.id > 100);
mysql> select * from dep where exists (select * from emp where emp.id > 100);
Empty set (0.00 sec)
3.表相关的SQL补充
alter table 表名 rename 新表名; # 修改表名
alter table 表名 add 字段名 字段类型 约束条件; # 添加字段
alter table 表名 add 字段名 字段类型 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型 约束条件 frist;
alter table 表名 change 旧字段名 新字段名 字段类型 约束条件;
alter table 表名 add 字段名 字段类型 约束条件;
alter table 表名 modify 字段名 字段类型 约束条件; # modify---> 修改
alter table 表名 drop 字段名; # 删除字段三、可视化软件Navicat
1.下载
Navicat有很多版本，由于好用方便很多人都使用所以它是收费的。正版可以免费体验14天。针对这种图形化软件，版本越新越好。
1）下载网址：(最新版16)
http://www.navicat.com.cn/download/navicat-premium
2）下载：
双击运行下载的软件包，然后点击下一步，然后点击同意许可，选择一个合适的目录(文件层级少，最好D(除了C盘)根目录下容易查找)，盘符的大小要足够，然后直接下一步，记住创建桌面图标，以便我们双击桌面图标便可以直接打开软件。2.初步连接
打开Navicat点击连接>>>选择mysql>>>连接名可以写可不写，主机自己用就写localhost，用户名就是mysql登录写的用户名，密码就是登录密码>>>先点左下角的测试连接，如果Navicat到数据库的线变成绿色，说明连接成功，点击右下角的确定(完成)3.基本使用
建数据库：右键点击我们连接好的localhost_3306>>>选择新建数据库>>>写上数据库名，字符集(字符编码)写utf8或者utf8mb4(功能更多，支持表情)，排序规则不用选择>>>点击确定4.基本操作
建表：双击点击我们新建好的数据库>>>选择表右键新建表>>>输入建表的字段，字段类型>>>点击保存
设置主键：勾选'不是null'，然后在'键'输入空格或者点击一下，正下方勾选'自动递增'>>>若再添加字段可以按住'tab'键或者点击上方的'添加字段'(Navicat非常智能，按住'tab'键会自动补全数据类型)
设置外键：右键点击表选择'设计表'，连接表上方的选择栏选择外键，输入外键名，字段点击表右方的'...'选择字段，'被引用模式'就是本身所在的数据库，'被引用的表(父)'选择右方下拉栏选择表(就是主表有主键的表)，'被引用的字段'选择右方的'...'选择字段(设置为主键的字段)，删除时和更新时选择'CASCADE'表示级联更新级联删除
查看表：双击点击表名
查看表结构：右键点击表>>>点击选择'设计表'
查看表与表之间的联系：右键点击选择表所在的数据库名>>>点击选择'逆向数据库到模型'>>>表之间有线连接表示两张表右联系
查询：点击连接的右方的'新建查询'>>>手动写入SQL语句(可在下拉栏选择查询的数据库)
'''
注意：
选中要运行的语句点击运行按钮可查询单条的SQL语句
若直接点击运行，则执行整个查询框里面的所有SQL语句
SQL中的注释：(快捷键：Ctrl+？)
-- 单行注释
# 单行注释
/*注释内容*/ 多行注释
'''四、多表查询练习题
1.查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表 课程表 分数表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
select course.cname,teacher.tname from course INNER JOIN teacher on course.teacher_id = teacher.tid
演示：
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
SELECT student.sname,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;
演示：
SELECT
student.sname,
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.先确定需要用到几张表 老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
SELECT sname from student WHERE sid not in (SELECT DISTINCT student_id from score WHERE course_id in (
SELECT cid from course WHERE teacher_id = (SELECT tid from teacher WHERE tname = '李平老师')))
演示：
SELECT
sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' )))4.查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表 学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
SELECT sname from student WHERE sid in (SELECT student_id from score WHERE course_id in (SELECT cid from course WHERE cname in ('物理','体育')) GROUP BY student_id HAVING count(course_id) = 1)
演示：
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
student_id
HAVING
count( course_id ) = 1
)5.查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表 分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT student.sname,class.caption from student INNER JOIN class on student.class_id = class.cid
WHERE student.sid in (
SELECT student_id from score WHERE num < 60 GROUP BY student_id HAVING count(course_id) >= 2)
演示：
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 )五、python操作MySQL
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4',
charset='utf8',
autocommit=True # 针对增删改查进行自动确认
)
#2.产生游标对象
# cursor = conn.cursor()
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#3.编写sql语句
sql = 'select * from teacher'
#4.发送sql语句
affect_rows = cursor.execute(sql)
print(affect_rows)
#5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)六、pymysql补充说明
1.获取数据
fetchall() # 获取所有的结果
fetchone() # 获取结果集的第一个数据
fetchmany() # 获取指定数量的结果集
# 类似于文件光标移动的特性
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=True # 针对增删改查进行自动确认
conn.commit() # 二次确认手机扫一扫
移动阅读更方便
你可能感兴趣的文章