目录
多表查询的思路
表数据准备
表数据准备
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),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
select * from emp,dep; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
多表查询的两种方法
方式一：连表操作
inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id; # 只连接两张表中公有的数据部分
left join 左连接
select * from emp left 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 right join dep on emo.dep_id=dep.id;
以左右表为基准 展示所有的数据 各自没有的全部NULL填充
学会了连表操作之后也就可以连接N多张表
思路：将拼接之后的表起别名当成一张表再去与其他表拼接 在起别名 在于其他表拼接 其次往复即可
方式二：子查询
将一条SQL语句括起来当成另外一条SQL语句的查询条件
题目：求姓名是jason的员工部门名称
子查询类似我们日常生活中解决问题的方式：分步操作
步骤一：先根据jason获取部门名称
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');
'''
很多时候多表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
'''
小知识点补充数说明
-
concat与concat_ws
concat用于分组之前的字段拼接操作 select concat(name,'$',sex) from emp; concat_ws拼接多个字段并且中间的连接符一致 select concat_ws('|',name,sex,age,dep_id) from emp; -
exists
sql1 exists sql2 sql2有结果的情况下才会执行qsl1 否则不执行sql1 返回空数据 -
表相关SQL补充
# 修改表名
alter table 表名 rename 新表名;
# 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
# 修改字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
# 修改字段类型
lter table 表名 modify 字段名 新字段类型(数字) 约束条件;
# 删除字段
alter table 表名 drop 字段名;
可视化软件Navicat
第三方开发的用来充当数据库客户端的简单快捷的操作界面
无论第三方软件有多么的花里胡哨 底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
2.破解方式
先试用在破解、直接下载破解版(老版本)、修改试用日期
3.常用操作
有些功能可能需要自己修改SQL预览
创建库、表、记录、外键
逆向数据库到模型、模型创建
新建查询可以编写SQL语句并自带提示功能
SQL语句注释语法
--、#、\**\
运行、转储SQL文件
多表查询练习题
编写复杂的SQL不要想着一口气写完 一定要先明确思路 然后一步步查一步步补
- 查询所有与的课程的名称以及对应的任课老师姓名
- 查询平均成绩大于八十分得同学的姓名和平均成绩、
- 查询没有报李平老师课的学生姓名
- 查询没有同时选修物理课程和体育课程的学生姓名
- 查询挂科超过两门(包括两门)的学生姓名和班级
python 操作MySQL
pymysql模块
pip3 install pymysql
import pymysql
# 1.连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
)
# 2.产生游标对象
# cursor = conn.cursor() # 括号内不填写额外参数 数据是元组 指定性不强 [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
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:注意三者都有类似于文件光标移动的特性
2.增删改查
autocommit=True # 针对增删 该自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认