疫情环境下的网络学习笔记 python 5.7 navicat数据库，例题，sql注入

5.7

昨日回顾

  • 单表查询，查询主要关键字

    select distinct 字段1,字段2 from 表名
	where 分组之前的筛选条件
	group by 分组条件
	having 分组之后的筛选条件
	order by 排序字段1 asc, 排序字段2 desc
	limit 5,5 # 一次显示的数据量

  • where

    where id>= 3 and id<=6
where id between 3 and 6
where salary=18000 or salary=17000
where salary not in (1800,17000)

# 模糊匹配
% 任意多个字符
_ 任意当个字符
where name like '____'; # 限制查询四个字符的名字
where char_length(name) = 4;
# 判断null的时候，用is，不用=null
where post_comment is null;

  • group by

    # 分组的应用场景非常的广泛：每个，平均，最大，最小。。。
# 分组之后只能获取到分组的依据，其他字段都无法直接获取
select post from emp group by post;

# 想要在分组后拿到其他数据，使用group_concat：获取分组之外的字段信息，拼接多个字段
select post,group_concat(salary,':',name) from emp;

# concat：分组之前帮我们获取字段信息并且拼接成多个字段
select concat(name,'??') from emp;

# conncat_ws：如果多个字段连接的符号是相同的情况下，可以使用concat_ws连接
select concat_ws(':',name,age,sex) from emp;

# as 语法
# 1. 可以给展示字段起别名
# 2. 可以给表其别名

# 聚合函数：聚合函数必须在分组之后使用
max,min,sum,count,avg

  • having

    # 分组之后再进行筛选操作，用法跟where一样，跟在group by后面

  • distinct

    # 去重
# 数据必须是一模一样，才能去重
select distinct post from emp; # 不能带主键

  • order by

    # 排序，默认是升序asc
# order by salary; == order by
salary asc,

order by salary desc;
order by salary asc, age desc;
# 支持多个字段排序

  • limit

    # 限制显示数据的展示条数
select * from emp limit 5;
# 展示5条数据

limit 5,5 # 从第5条开始，展示5条

  • 正则

    # 正则是一门独立的语言，在python中使用正则要用re模块

# 例题
# 1. re模块中常用的方法：find all，match，search

# 2. match和search的区别
# match从头开始匹配，search整体匹配

# 3. findall
# findall：分组优先展示，不会展示所有正则表达式匹配到的内容，仅仅展示括号内正则表达式匹配到的内容

# 4. 贪婪匹配和非贪婪匹配
# 正则表达式默认都是贪婪匹配，要变成非贪婪，只需要在正则表达式的后面加一个问号?
.*  贪婪
.*? 非贪婪

select * from emp where name regexp '^j.*n$';

  • 多表查询

    # 连接表操作
inner join, left join, right join, union
# inner join 只拼接两张表中公有的部分
# left join 左表全部显示，右表没有对应的用null填充
# right join ：left join反过来
# union：左右全部显示

select * from emp inner join dep on emp。dep_id = dep.id # 要加上表的前缀，不然容易造成冲突

  • 子查询

    # 我们平时解决问题的思路：分布处理：上一个查询结果当作下一个查询的条件
# 当作条件的时候用括号括起来
select * from emp where id in (select id from dep);

  • 总结

    # 写sql语句的时候，select后面先用 * 占位，之后写完再改
# 在写较为复杂的sql语句的时候，不要想着一口气写完
# 在做多表查询的时候，联表操作和子查询可能会结合使用

知识点补充

  1. 查询平均年龄在25岁以上的部门名称

    # 部门表dep，员工表emp
# 只要是多表查询，就会有两种思路：联表，子查询
# 联表查询
# 1. 先拿到部门和员工表，拼接之后的结果
# 2. 按照部门分组
select dep.name from emp inner join dep
	on emp.dep_id = dep.id
	group by dep.name
	having avg(age) > 25;
	
# 涉及到多表操作的时候，一定要加上表的前缀

# 子查询
select name from dep where id in(select dep_id from emp group by dep_id 
havingavg(age) > 25);

今日内容概要

  1. navicat可视化界面
  2. 数据库查询题目讲解(多表操作)
  3. python如何操作mysql：pymysql模块
  4. sql注入问题
  5. pymysql模块增删改查数据操作

类似pycharm于python，用于更方便地操作MySQL。内部封住了navicat命令，鼠标就可以完成，不用写sql语句

navicat能够充当多个数据库的客户端

当有需求没法满足的时候，自己动手写sql预览里的sql语句

逆向数据库到模型，生成一个模型界面，可以看到表与表之间

提示

  1. MySQL不区分大小写

  2. MySQL建议所有的关键字：select，group by 建议写大写

  3. MySQL注释有两种

    ctrl + ?
#

开始写题

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

    # 涉及到course，teacher表：先连起来两张表
select * from course inner join teacher on course.teacher_id = teacher.tid
# 分析需求，只想要course表下的cname和teacher表下的tname
# 改：
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid

  2. 查询平均成绩大于80分的同学的姓名和平均成绩

    # 涉及到score，student表
select * from score inner join student on score.student_id = student.sid;

# 需求：平均成绩>80，姓名，成绩：avg，在group by后使用
...

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

    # 涉及course，teacher，student表，需要分步操作
# 1. 先找李平老师的id
# 2. 再找所有报了李平老师的id
# 3. 再在学生表里面取反
select * from score_id in (
select course.cid from teacher inner join course on teacher.tid = course.teacher_id where teacher.tname = '李平老师');

# 改：
SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
WHERE
	score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ));
# 用正向思维也能写

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

    # 只要选了一门的，选了两门的和没有选的都不要
# 涉及student，course表
# 1. 先查物理和体育的id号，再去获取所有选了物理和体育的学生数据
# 2. 按照学生分组，利用聚合函数count筛选出只选了一门的学生id
# 3. 依照id获取学生姓名

SELECT
	student.sname 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		score.course_id IN (
		SELECT
			course.cid 
		FROM
			course 
		WHERE
		course.cname IN ( '物理', '体育' )) 
	GROUP BY
		score.student_id 
	HAVING
	count( score.course_id ) = 1 
	);

  5. 查询挂科超过2门(包括两门)的学生姓名和班级

    # 涉及表score，student，挂科，score.num < 60
# 1. 先筛选所有分数小于60的数据
select * from score where score.num < 60;
# 2. 按照学生分组，对学生计数，获取大于等于2的学生
group by score.student_id
having count(score.course_id )>= 2;
# 3. 拼接
# 改：
SELECT
 class.caption,
 student.sname 
FROM
 class
 INNER JOIN student ON class.cid = student.class_id 
WHERE
 student.sid IN (
 SELECT
  score.student_id 
 FROM
  score 
 WHERE
  score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2 
 );

pymysql模块

# 首先导入pumysql
import pymysql
conn = pymysql.connect(
	host = '127.0.0.1',
	post = 3306,
	user = root,
	password = '6008',
	charset = 'utf8' # 编码不要加-，就写utf8
) # 生成conn对象
cursor = conn.cursor() # 产生一个游标对象，用来帮你执行sql命令
sql - 'select * from teacher;'
res = cursor.excute(sql) # 执行
print(res)
# excute返回的结果是当前sql语句影响的行数，该返回值一般不用

# 获取命令执行的查询结果，要通过cursor的方法
print(cursor.fetchone()) # 拿一条结果

# 在生成cursor对象的时候，括号里加参数
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

print(cursor.fetchall()) # 拿到sql语句执行的所有
# 括号里面加数字，可以指定拿多少条数据

# 获取结果向文件读取一样，有个指针，移到哪就从哪开始读
# 使用scroll可以控制读取数据的光标
cursor.scroll(1) # 默认移动模式：relative，从当前位置开始前后移动
cursor.scroll(1,'absolute') # 相对于数据开头移动，相当于从头开始读，读一条数据

SQL注入

结合数据库完成一个用户登陆功能

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123456',
    database = 'day48',
    charset = 'utf8'  # 编码千万不要加-
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password))  # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('用户名密码错误')
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/telecasterfanclub/p/12843764.html