MySQL数据库基础5

今日内容概要

• 多表查询的两种方法
• 小知识点补充
• 可视化软件Navicat
• 多表查询练习题
• python操作MySQL

今日内容详细

多表查询的两种方法

方式1:连表操作
	inner join    内连接
	select * from emp inner join dep on emp.dep_id=dep.id;
	只连接两张表中公有的数据部分

	left join     左连接
	select * from emp left join join dep on emp.dep_id=dep.id;
	以左表为基准 展示左表中所有的数据 如果没有对应项则用NULL填充

	right join    右连接
	select * from emp right join dep on emp.dep_id=dep.id;
	以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充

	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

'''
学会了连表操作之后也就可以连接N多张表
	思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
'''

方式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');

'''
很多时候多表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
'''

小知识点补充说明

1.concat与concat_ws
	concat用于分组之前的字段拼接操作
		select concat(name,'$',sex) from emp;
	concat_ws拼接多个字段并且中间的连接符一致
		select concat_ws('|',name,sex,age,dep_id) from emp;

2.exists
	sql1 exists sql2
		sql2有结果的情况下才会执行sql1 否则不执行sql1 返回空数据

3.表相关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.浏览器搜索Navicat直接下载
	版本很多 能够充当的数据库客户端也很多
2.破解方式
	先使用再破解 直接下载破解版(老版本) 修改试用日期
3.常用操作
	有些功能可能需要自己修改SQL预览
		创建库 表 记录 外键
		逆向数据库到模型 模型创建
		新建查询可以编写SQL语句并自带提示功能
		SQL语法注释语句
			单行注释
				--  # 
			多行注释
				\**\
		运行 转储SQL文件

多表查询练习题

'''
编写复杂的SQL不要想着一口气写完
一定要先明确思路 然后一步步写一步步查一步步补
'''
"""
1.查询所有的课程的名称以及对应的任课老师姓名
2.查询平均成绩大于八十分的同学的姓名和平均成绩
3.查询没有报名李平老师课的学生姓名
4.查询没有同时选修物理课程和体育课程的学生姓名
5.查询挂科超过两门(包括两门)的学生姓名和班级
"""

1.查询所有的课程的名称以及对应的任课老师姓名
	步骤1:先确定需要用到几张表 课程表 分数表
	步骤2:预览表中的数据 做到心中有数
	步骤3:确定多表查询的思路 连表 子查询 混合操作
	SELECT
		course.cname,
		teacher.tname 
	FROM
		teacher
		INNER JOIN course ON teacher.tid = course.teacher_id;
2.查询平均成绩大于八十分的同学的姓名和平均成绩
	步骤1:先确定需要用到几张表 分数表 学生表
	步骤2:预览表中的数据 做到心中有数
	步骤3:根据已知条件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 然后取反即可
	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
	步骤5:根据课程id筛选出所有跟物理 体育相关的学生id
	步骤6:统计每个学生报了的课程数 筛选出等于1的
	步骤6:子查询获取学生姓名
	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:预览表数据
	步骤3:先筛选掉大于60的数据
	步骤4:统计每个学生的挂科次数
	步骤5:筛选次数大于等于2的数据
	步骤6:连接班级表与学生表 然后基于学生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);

python操作MySQL

pymysql模块
	是第三方模块 需要自己安装
	pip3 install pymysql

import pymysql

1.连接MySQL服务端
conn = pymysql.connect(
	host='127.0.0.1',  # mysql服务端地址
	port=3306,   # 端口
	user='root',  # 用户
	password='',  # 密码
	db='db4',   # 要操作的数据库
	charset='utf8mb4'   # 编码
)

2.产生游标对象
cursor = conn.cursor()   # 括号内不填写额外参数 数据是元祖 指定性不强
# 指定后 数据是列表套字典
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

3.编写SQL语句
sql = 'select * from score;'  # 也可以不写分号

4.发送sql语句
affect_rows = cursor.execute(sql)   # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)

5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

pymysql补充说明

1.获取数据
	fetchall()   获取所有的结果
	fetchone()   获取结果集的第一个数据
	fetchmany()  获取指定数量的结果集
	ps:注意三者都有类似于文件光标移动的特性

	cursor.scroll(1,'relative')   # 基于当前位置往后移动
	cursor.scroll(0,'absolute')   # 基于数据的开头往后移动

2.增删改查
	autocommit=True   # 针对增删改自动确认(直接配置)
	conn.commit()    # 针对增删改需要二次确认(代码确认)