首页 > 编程学习 > python学习Day48

python学习Day48

发布时间:2022/8/18 23:12:09

Day 48

今日内容概要

  • Navicat可视化软件
  • 多表查询练习题
  • python操作MySQL
  • 获取命令的执行结果
  • SQL注入问题
    • 基础用户登录SQL语句(记忆)
  • 知识点额外补充

今日内容详细

1.Navicat可视化软件
可以充当很多数据库软件的客户端 最主要的用于MySQL
该软件可以用一种可视化界面直观看到库、表、内容,可以用鼠标来代替用SQL语句,可以提升效率。

1.下载
	正版收费
	可以下破解版
2.主要功能介绍
	左边链接处右键可以'新建数据库',字符集选utf8mb4(比utf8功能多),排序规则不要写。
    左边表右键可以'新教表',主键需要在键的位置俺空格,最下面点自动递增。
    添加完数据后记得F5刷新一下
    #如果想建一对一的表,则需要在SQL预览上自己添加unique
    
    注释有三种:--单行注释、#单行注释、/*多行注释*/
2.多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级

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

-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
#1.需要成绩表、学生表
#2.查看每张表中的数据
-- select * from student;
-- select * from score;
#3.先查询成绩表中平均成绩大于80分的数据
#3.1按照学生编号分组 利用聚合函数avg求出所有学生编号对应的学生成绩
-- select student_id,avg(num) from score group by student_id;
#3.2基于上述分组后的结果筛选出平均成绩大于80的数据,并把字段名重命名为avg_num
-- select student_id,avg(num)as avg_num from score group by student_id having avg(num)>80;
'针对select后面通过函数或者表达式编写的字段为了后续取值方便 一般需要重命名成普通字段'
#4.从上述sql语句的结果中获取平均分字段和学生表中的名字字段,需要把两张表连起来
-- 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;
'将SQL语句当做表来使用 连接的时候需要使用as起一个表名 方便后续点里面的字段'
#5.显示出超过平均80分的学生名字和平均分
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.查看需要用到的表 老师表 课程表 分数表 学生表
select * from student;
select * from score;
select * from course;
select * from teacher;
#2.有两种解题思路:
#(1.查其他老师的课程然后一步步查到学生 2.查报了该老师课的学生编号然后取反)推荐2
#3.获取李平老师的课程id号
-- select tid from teacher where tname='李平老师'
#4.根据老师编号去课程表中筛选该老师的课程编号
-- 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.查看需要用到的表 课程表 学生表 成绩表
-- select * from student;
-- select * from score;
-- select * from course;
#2.先获取物理和体育课程的id号
-- select cid from course where cname in ('物理','体育');
#3.根据课程id号先去成绩表中过滤掉没有选择这些课程的数据
-- select * from score where course_id in(select cid from course where cname in ('物理','体育'));
#4.基于上述表统计出每个学生id报了几门课,以学生id分组过滤掉只报名一门的学生
-- select score.student_id from score where course_id in(select cid from course where cname in ('物理','体育'))group by score.student_id having count(score.course_id)=1;
#5.根据上述学生id在学生表中获取学生姓名
select sname from student where sid in (select score.student_id from score where course_id in(select cid from course where cname in ('物理','体育'))group by score.student_id having count(score.course_id)=1);

-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
#1.查看需要用到的表 班级表 学生表 成绩表
-- select * from class;
-- select * from student;
-- select * from score;
#2.筛选出分数小于60的数据
-- select * from score where num<60;
#3.按照学生id分组,计数统计课程id大于等于2的学生id
-- select student_id from score where num<60 group by student_id having count(course_id)>=2;
#4.由于需要查看学生姓名和班级 所以先把班级表与学生表连接起来
-- select * from class inner join student on class.cid=student.class_id;
#5.在上述表中筛选出之前获取到的学生id数据,显示学生姓名与班级
select student.sname,class.caption from class inner join student on class.cid=student.class_id where student.sid in (select student_id from score where num<60 group by student_id having count(course_id)>=2);
3.python操作MySQL
第三方模块下载命令:pip3 install pymysql

pymysql连接MySQL服务端基础代码:(意味着数据库从文件变成了MySQL)
import pymysql
#1.连接服务端
conn=pymysql.connect(
    host='127.0.0.1',#ip地址
    port=3306,#MySQL的端口号一般都是3306
    user='root',
    password='123',
    database='db2',#使用哪个库
    charset='utf8mb4',#字符编码
    autocommit=True) #执行增、删、改操作自动执行conn.commit(必须有,因为MySQL中有一个二次确认机制,有了后则会直接修改)
#2.产生一个游标对象(等待输入命令)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)#把查询出来的数据组成字典形式
#3.编写SQL语句
# sql1='insert into userinfo(name,pwd)values("小5","555")'#增
# sql1='delete from userinfo' #删
sql1='update userinfo set name="小五" where id=5'#改
#4.发送给服务端(执行SQL语句)
cursor.execute(sql1)
#5.获取命令的执行结果
res=cursor.fetchall()#获取所有结果(字典),上面写了要组成字典形式
print(res)
4.获取命令的执行结果
cursor.fetchone()   # 获取结果集中第一条数据
cursor.fetchall()   # 获取结果集中所有数据
cursor.fetchmany(2)  # 获取结果集中指定2条的数据

'''类似于文件光标的概念'''
cursor.scroll(1, mode='relative') #基于当前'光标'位置往后移动一位开始
cursor.scroll(0, mode='absolute') #基于数据集开头的位置往后移0位开始

image

5.SQL注入问题
前戏发现:
	1.只需要用户名即可登录
 	2.不需要用户名和密码也能登录

问题出在:
	SQL注入
	select * from userinfo where name='jason' -- haha' and pwd=''
	select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''
本质问题为:
	利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
解决措施:
	#针对需要用户输入的数据 在执行SQL语句execute后写上要处理的语句和数据,可自动识别%s并过滤掉各种符号。
    sql = 'select * from userinfo where name=%s and pwd=%s'
    cursor.execute(sql, (username, password))#可自动识别%s 并自动过滤各种符合 然后合并数据
    
补充:
	#当需要用户输入多条数据时可以用cursor.executemany()
	sql='insert into userinfo(name,pwd) values(%s,%s)'
	cursor.executemany(sql,[('张三','123'),('李四','123'),('王五','123')])
5.1基础用户登录SQL语句
基础的用户登录SQL语句

import pymysql
#1.连接服务端
conn=pymysql.connect(
    host='127.0.0.1',#ip地址
    port=3306,#MySQL的端口号一般都是3306
    user='root',
    password='123',
    database='db2',#使用哪个库
    charset='utf8mb4',#字符编码
    autocommit=True) #执行增、删、改操作自动执行conn.commit
#2.产生一个游标对象(等待输入命令)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)#把查询出来的数据组成字典形式
#3.获取用户数据
username=input('name:').strip()
password=input('pwd:').strip()
#4.编写SQL语句
sql="select * from userinfo where name=%s and pwd=%s"
#5.执行SQL语句
cursor.execute(sql,(username,password))#可自动识别%s 并自动过滤各种符合 然后合并数据
#6.获取执行结果
res=cursor.fetchall()
if res:
    print('登录成功')
    print(res)
else:
    print('用户名或密码错误')
6.知识点额外补充
1.as语法
	给字段起别名、起表名
2.comment语法
	给表、字段添加注释信息
 	create table server(id int) comment '这个server意思是服务器表'
	create table t1(
    	id int comment '用户编号',
		name varchar(16) comment '用户名'
    	) comment '用户表';
	"""
	查看注释的地方
		show create table 
		use information_schema
	"""
3.concat、concat_ws语法
	concat用于分组之前多个字段数据在一格的拼接
    #select concat(name,'|',pwd) from userinfo;
 	concat_ws如果有多个字段 并且分隔符一致 可以使用该方法减少代码
    #select concat_ws('|',name,pwd,xxx) from userinfo;
4.exists语法
	select * from userinfo where exists (select * from t1 where id<100)
	exists后面的sql语句如果有结果那么执行exists前面的sql语句,
	                  如果没有结果则什么都不执行

作业

1.整理今日内容及博客
2.完成软件安装及课堂练习知识消化
3.利用pymysql编写用户注册登录功能
4.自行完成查询练习题部分即可
	https://www.cnblogs.com/Dominic-Ji/p/10875493.html
Copyright © 2010-2022 mfbz.cn 版权所有 |关于我们| 联系方式|豫ICP备15888888号