Navicat可视化软件及多表查询的方法

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.tid

2.查询平均成绩大于八十分的同学的姓名和平均成绩

# 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()     # 二次确认

Navicat可视化软件及多表查询的方法的相关教程结束。