多表查询、Navicat软件、PyMySQL模块

一、多表查询的两种方法

1.准备工作

1.创建数据库

create databases db4

2.使用数据库

use db4

3.表数据准备

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

);

4.插入数据

insert into dep values

(200,'技术'),

(201,'人力资源'),

(202,'销售'),

(203,'运营'),

(205,'财务');

insert into emp(name,sex,age,dep_id) values

('jason','male',18,200),

('dragon','female',48,201),

('kevin','male',18,201),

('nick','male',28,202),

('owen','male',18,203),

('jerry','female',18,204);

2.第一种:连表操作

*****1. inner join  # 内链接 只链接两张表共有的部分

    select * from emp inner join dep on emp.dep_id=dep.id

2. left join  # 左链接 以左表为基准展示所有的数据若没有数据用NULL补充

   select * from emp left join dep on emp.dep_id=dep.id

3. right jion  # 右链接 以右表为基准展示所有的数据若没有数据用NULL补充

   select * from emp right join dep on emp.dep_id=dep.id

4. union  # 全链接 了解即可

   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

3.第二种:子查询

将一条SQL语句用括号当成另一条SQL语句的查询条件 子查询类似于日常生活中的解决问题的思路

    题目:求姓名是某某的员工部门名称

    思路：

        步骤一:先根据员工姓名获取部门编号

            select dep_id from emp where name='jason'

        步骤二:再根据部门编号获取部门名称

            select name from dep where id=200

    真正的步骤:

         select name from dep where id=(select dep_id from emp where name='jason');

总结与结论:

连接表操作之后可以实现连续连接N多张表 大概思路就是将拼接之后的表起别名当成第一张表与其他表连接 以此类推

实际操作过程中 大部分情况下两种方法配合着使用可能性更多一些 但是具体用的时候根据实际需求再选择方法即可。

二、多表查询练习题

1.课堂多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名

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

3、查询没有报李平老师课的学生姓名

4、查询没有同时选修物理课程和体育课程的学生姓名

5、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)

2.以上练习题对应的SQL语句详细

1、查询所有的课程的名称以及对应的任课老师姓名

# 1.先确定需要用到几张表  课程表 分数表

# 2.预览表中的数据 做到心中有数

-- select * from course;

-- select * from teacher;

# 3.确定多表查询的思路 连表 子查询 混合操作

-- SELECT

-- 	teacher.tname,

-- 	course.cname

-- 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,

-- 	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.先确定需要用到几张表  老师表 课程表 分数表 学生表

# 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 = '李平老师'

-- 					)

-- 			)

-- 	)

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

-- 	) 

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

	);

三、小知识点补充说明

1.concat与cancat_ws

concat用于分组之前

select concat(name,'|',sex) from emp;

concat_ws拼接多个字段并且中间的连接符一致

select concat('|',name,sex,age,dep_id) from emp;

2.表相关SQL补充

alter table 表名 rename 新表名；  # 修改表名

alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 添加字段

alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;

alter table 表名 add 字段名 字段类型(数字) 约束条件 first;  # 修改字段

alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件；

alter table 表名 modify 字段名 新字段类型(数字) 约束条件；

alter table 表名 drop 字段名；  # 删除字段

四、可视化软件Navicat

第三方开发的用来充当数据库客户端的简单快捷的操作界面 底层的本质还是SQL 能够操作数据库对的第三方可视化软件有很多 其中针对MySQL最出名的就Navicat

1.软件下载

	直接在浏览器下载即可 但是这款软件是收费的 要么花钱买 要么破解

    当然会直接选择破解 哈哈哈哈

2.破解方式

	详细破解过程 戳这里>>>https://www.bilibili.com/read/cv16884052

3.常用操作

    有些功能可能需要自己修改SQL预览

    创建库、表、记录、外键

    逆向数据库到模型、模型创建

    新建查询可以编写SQL语句并自带提示功能

    SQL语句注释语法

    --、#、\**\

    运行、转储SQL文件

1.连接数据库操作



2.创建数据库操作



3.创建表操作



4.转储数据保存操作

五、python操作MySQL

1.pycharm安装第三方模块pymysql的详细步骤

1.先点击file 再点击settings

2.先点击project:项目名 再点击PythonInterpreter

3.左键双击第三方模块列表框调出模块搜索对话框

4.输入PyMySQL模块名

5.给Spscify version打对勾

6.点击 Install Package 就可以

2.python操作MySQL的基本操作

import pymysql

# 1.链接MySQL服务端

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='1998',

    db='db4',

    charset='utf8mb4',

    autocommit=True

)

# 2.产生游标对象

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内步写参数 结果是元组 写了之后[{},{}]

# 3.遍写SQL语句

sql = 'select * from emp;'

# 4.发送SQL语句

affect_rows = cursor.execute(sql)

print(affect_rows)

# 5.获取SQL语句执行之后的结果

res = cursor.fetchall()

print(res)

3.pymysql模块 知识点补充说明

1.获取数据

    fetchall()  # 获取所有的结果

    fetchone() # 获取结果集的第一个数据

    fetchmany(5)  # 获取指定数量的结果集

    cursor.scroll(1,'relative')  # 基于当前位置往后移动

    cursor.scroll(0,'absolute')  # 基于数据的开头往后移动

2.增删改查

    autocommit=True  # 直接配置(自动确认) 强烈推荐 这样就安心进行增删改查了

    conn.commit()  # 需要写代码(二次确认) 不太推荐 因为容易忘记

最新文章

1. 关于eclipse删除servers之后，不能新建其所对应版本的Servers
2. Xcode5和ios7下交叉编译ffmpeg
3. 使用OpenXML将Excel内容读取到DataTable中
4. Codeforces Round #369 (Div. 2) D. Directed Roads (DFS)
5. [最短路][模版]P1346 电车
6. SQL运行时间
7. 生成式模型之 GAN
8. 【NOIP2013】传染病控制
9. com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field 异常
10. 先安装VS后安装IIS，注册IIS方法
11. 使用CocoaPods时遇到 Permission denied 问题
12. Byte数组和字符串相互转换的问题
13. Exception、Error、运行时异常与一般异常有何异同
14. Fedora 19关闭防火墙
15. Azure HDInsight 上的 Spark 群集配合自定义的Python来分析网站日志
16. Repository HDU2846
17. Linux-centos6.8下关闭防火墙
18. oracle中的异常处理方法
19. 《selenium2 python 自动化测试实战》（17）——几个cookies操作
20. 判断无向图两点间是否存在长度为K的路径

热门文章

1. Java的lamda表达式/函数式接口/流式计算
2. ECON 模式
3. Jenkins 运行权限问题
4. python_flask开发环境设置
5. 洛谷P2886 [USACO07NOV]Cow Relays G （矩阵乘法与路径问题）
6. windows C++ call ADB command
7. 使用 etcdadm 快速、弹性部署 etcd 集群
8. vue-axios删除操作
9. 上下文管理器 context managet
10. 成熟企业级开源监控解决方案Zabbix6.2关键功能实战-下