多表查询和pymysql模块的使用

目录

• 多表查询两种方法
  
  
  
  • 思想
  
  
  • 子查询
  
  
  • 连表操作(重要)
  
  
  
  


• 多表查询练习题


• python操作MySQL
  
  
  
  • 安装
  
  
  • 模块基本使用
  
  
  • 移动游标
  
  
  • SQL注入问题
    
    
    
    • 问题
    
    
    • 解决方法
    
    
    
    
  
  
  • pymysql对数据库的增删改查
  
  
  • pymysql进行注册登陆
  
  
  
  

多表查询两种方法

思想

• 先确定需要用到几张表


• 再看是否要展示不同表中的数据(是两个及以上不同表中的数据就要用连表)


• 不要想着一步写完，拆分多分几步就好写了

数据准备

# 数据准备
#建表
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),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
问题: 查询jason所在的部门名称


子查询

涉及到SQL查询题目 一定要先明确到底需要几张表
1.先查询jason所在的部门编号
select dep_id from emp where name='jason';
2.根据部门编号查询部门名称
select name from dep where id=(select dep_id from emp where name='jason');
"""一条SQL语句的查询结果既可以看成是一张表也可以看成是查询条件"""
"""大白话:就是我们日常生活中解决问题的方式>>>:分步操作"""


连表操作(重要)

拼接表

格式:
select * from 表1 inner join 表2 on 表1.相关联字段=表2.相关联字段
inner join 内连接
left join 左连接
right join 右连接
union 全连接

下图的dep_id 和 id 就是两张表有关系的字段,所以用这两个字段拼接
涉及到多表操作的时候 为了避免表字段重复 需要在字段名的前面加上表名限制

'''
上述操作一次只能连接两张表 如何做到多张表?
将两张表的拼接结果当成一张表与跟另外一张表做拼接
依次往复 即可拼接多张表
'''


先将查询涉及到的表拼接成一张大表 之后基于单表查询
eg:
比如上述题目只涉及到两张表,所以先把两张表拼接起来
然后再根据题目筛选出相应的数据
# 先连表
select * from emp inner join dep on emp.dep_id=dep.id; 结果在上图
# 对数据进行筛选
select emp.name from emp inner join dep on emp.dep_id=dep.id where emp.name='jason';


连表拓展:

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
# 先连表
SELECT * from course inner join teacher on course.cid=teacher.tid;
# 再显示课程的名称以及对应的任课老师姓名
SELECT teacher.tname,course.cname from course inner join teacher on course.cid=teacher.tid;


2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定是需要两张表 学生表和分数表 先取出平均成绩
SELECT student_id,avg(num) from score group by student_id;
# 2.在筛选出平均成绩大于80的
SELECT student_id,avg(num) as avg_num from score group by student_id HAVING avg(num) >80 ;
# 3.再和student表连接
SELECT * 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,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.先确定是老师表和课程表和分数表和学生表4张表
# 2. 先找李平老师的tid
SELECT tid from teacher WHERE tname='李平老师'
# 3.再用tid去课程表里找李平老师的课程cid
SELECT cid from course WHERE teacher_id=(SELECT tid from teacher WHERE tname='李平老师')
# 4.再用cid去成绩表中找选李平老师课的学生id
SELECT student_id from score WHERE course_id in (SELECT cid from course WHERE teacher_id=(SELECT tid from teacher WHERE tname='李平老师'))
# 5.因为有很多课程，会有学生选多门课的可能，所以要去重，留下的就是选李平的课的学生id
SELECT DISTINCT student_id from score WHERE course_id in (SELECT cid from course WHERE teacher_id=(SELECT tid from teacher WHERE tname='李平老师'))
# 6.然后用学生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.先确定是学生表分数表和课程表3个表
# 2. 再找到物理和体育的id
-- SELECT cid from course WHERE cname in ('物理','体育');
# 3.再用cid去分数表中找学生id
-- SELECT * from score WHERE course_id in (SELECT cid from course WHERE cname in ('物理','体育'))
# 4.因为现在是要么报了一门 要么两门都报了 所以用count进行筛选 拿到学生id
-- 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
# 再用学生id去学生表中拿到学生姓名
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.先确定是分数表、学生表和班级表3个表 然后查询低于60的学生id
SELECT * from score WHERE num <60
# 2.用学生id进行分组 对课程id进行计数 大于等于2就是挂科超过2门的
SELECT student_id from score WHERE num <60 GROUP BY student_id HAVING count(course_id) >=2
# 3.因为要去学生姓名和班级 是两个表的字段 所以要连表操作
SELECT student.sname,class.caption from student INNER JOIN class on student.class_id=class.cid
# 4.最后把学生id带入当限制条件就行了
SELECT student.sname,class.caption from student INNER JOIN class on student.class_id=class.cid WHERE sid in (SELECT student_id from score WHERE num <60 GROUP BY student_id HAVING count(course_id) >=2)


更多练习
https://www.cnblogs.com/Dominic-Ji/p/10875493.html


python操作MySQL

安装

python 胶水语言、调包侠(贬义词>>>褒义词)
"""
python这门语言本身并不牛逼 牛逼的是支持该语言的各种功能强大的模块、软件
"""
# 后期在使用python编程的时候 很多看似比较复杂功能可能都已经有相应的模块
模块名字 pymysql
下载模块
1.命令行
pip3 install pymysql
pip3 install pymysql -i 源地址
2.借助于pycharm
3.python解释器配置文件


模块基本使用

import pymysql
# 创建连接
cOnn= pymysql.connect(
host='127.0.0.1',
port=3306,
database='db04',
user='root',
password='123',
charset='utf8',
)
# 创建游标
cursor = conn.cursor()
# 书写sql语句
sql = "select * from userinfo"
# 执行sql语句
res = cursor.execute(sql)
print(res)
# 接收sql语句的返回结果
tup = cursor.fetchall()
print(tup)


# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让所有的返回结果组织成列表套字典的形式
# 书写sql语句
sql = "select * from userinfo"
# 执行sql语句
cursor.execute(sql)
print(cursor.fetchone())
print(cursor.fetchmany(2))
print(cursor.fetchone())


移动游标

# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让所有的返回结果组织成列表套字典的形式
# 书写sql语句
sql = "select * from userinfo"
# 执行sql语句
cursor.execute(sql)
print(cursor.fetchone())
cursor.scroll(1, 'relative')
print(cursor.fetchone())
cursor.scroll(1, 'absolute')
print(cursor.fetchall())


SQL注入问题

问题

# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 让所有的返回结果组织成列表套字典的形式
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 书写sql语句
sql = "select * from userinfo where name='%s' and pwd='%s'" % (username, password)
print(sql)
res1 = cursor.execute(sql)
# print(res1)
res = cursor.fetchone()
if res:
print('登陆成功')
else:
print('用户名或密码错误.')


解决方法

sql = "select * from userinfo where name=%s and pwd=%s"
print(sql)
res1 = cursor.execute(sql, (username, password)) # 在这传值就行了
res = cursor.fetchone()
if res:
print('登陆成功')
else:
print('用户名或密码错误.')


pymysql对数据库的增删改查

# 创建连接
cOnn= pymysql.connect(
host='127.0.0.1',
port=3306,
database='db04',
user='root',
password='123',
charset='utf8',
autocommit=True # 涉及到增删改 自动二次确认 就不用commit了
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 书写sql语句
sql = "insert into userinfo(name,pwd) values(%s, %s)"
cursor.execute(sql, ('tom', 666))
conn.commit() # 上面如果不配置autocommit=True 每次对数据库进行更新都要写这句确认才行


pymysql进行注册登陆

def get_cursor():
import pymysql
# 创建连接
cOnn= pymysql.connect(
host='127.0.0.1',
port=3306,
database='db04',
user='root',
password='123',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
return cursor
# 注册
def register(cursor):
while True:
username = input('请输入注册的用户名>>>:').strip()
password = input('请输入注册密码>>>:').strip()
re_password = input('请再次输入注册密码>>>:').strip()
if password != re_password:
print('两次密码不一致')
continue
sql = "select * from userinfo where name=%s"
cursor.execute(sql, (username,))
res = cursor.fetchone()
if res:
print('用户名重复')
return
sql1 = "insert into userinfo(name, pwd) values(%s, %s)"
cursor.execute(sql1, (username, password))
print('注册成功')
return
# 登陆
def login(cursor):
while True:
username = input('请输入用户名>>>:').strip()
password = input('请输入密码>>>:').strip()
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))
res = cursor.fetchone()
if not res:
print('用户名或密码错误')
continue
print('登陆成功')
func_dic = {'1': register, '2': login}
while True:
print('''
1.注册
2.登陆
''')
choice = input('请输入功能编号>>>:').strip()
if not choice: continue
if not choice.isdigit():
print('请输入纯数字')
if choice not in func_dic:
print('还没该功能')
cursor = get_cursor()
func_dic.get(choice)(cursor)