五

今日内容概要

  • 内容回顾
  • 小知识点补充
  • navicat可视化界面操作数据库
  • 数据库查询题目讲解（多表操作）
  • Python如何操作MySQL（pymysql模块）
  • sql注入问题
  • pymysql模块增删改查数据操作

内容回顾

  • 查询关键字
select distinct 字段1，字段2...from 表名
	where 分组之前的筛选条件
    groud 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= or salary=17000;
where salary in (1800,1700);
where salary not in (1800,1700);
where salary*20;

# 模糊匹配like
"""
% 任意多个字符
_ 任意单个字符
"""
where name like '%mode';
where name like '____';
where char_length(name)=4;

# 针对null数据，判断的时候用is，不要用=
where post_comment is null;
  • group by
# 分组的应用场景非常多
	每个
    平均
    最大
    最小
    ...
"""
分组之后只能直接获取到分组的依据，其他字段都无法直接获取
set global sql_mode='only_full_group_by'
"""
select * from emp group_by post; # 报错
select post from emp gruop_by post;

# group_concat:帮助我们获取到分组之外的字段信息，并且可以拼接多个字段
select post,group_concat(salary,':',name) from emp;
# concat:分组之前帮助我们获取字段信息并且可以拼接多个字段
select concat(name,':') from emp;
# concat_ws：如果多个字段之间的连接符号是相同的情况下，你可以用concat_ws来完成
select concat_ws(':',name,age,sex) from emp;

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

# 聚合函数
	max
    min
    sum
    count
    avg
  聚合函数必须在分组后使用
  • having
# 用法跟where一模一样，只不过他是作用于分组之后的再次筛选
...group_by post having avg(salary)>300;
  • distinct
# 数据必须是一模一样的情况下才能去重
  • order by
# 排序，默认是升序
order by salary;  ==  order by salary asc;
order by salary desc;

order by salary asc,age desc;  # 支持多个字段比较
  • limit
"""
限制数据的展示条数，效果就是分页的效果
"""
select * from emo limit 5;

limit 5;
limit 5,5;  # 第一个参数是起始位置，第二个是条数
  • regexp
"""
正则是一门独立的语言
"""
select * from emp where name regxp '^j.*n$'
  • 多表查询
# 联表操作
select * from emp,dep;  笛卡尔积
	inner join
    	只拼接两种表中公有的部分
        select *from emp inner join dep
        	on emp.dep_id = dep.id
    left join
    	左表数据全部展示，没有对应的用null补全
    right join
    	右表数据全部展示，没有对应的用null补全
    union
    	左右全部补全，没有对应的值用null补全
# 子查询
"""
就是平时我们平时解决问题的思路，分步处理
将一张表的查询语句当做另外一条SQL语句的查询条件
（当做条件的时候用括号括起来）
"""
  • 总结
# 书写SQL语句的时候 select后面先用*占位，之后写完在补全
# 在写较为复杂的SQL语句的时候，不要想着一口气写完
# 在做多表查询的时候，联表操作和子查询可能会结合使用

  • 小知识点补充

# 查询平均年龄在二十五岁以上的部门名称
"""只要是多表查询，就有两种思路  联表 子查询"""
# 联表
	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
        having avg(age)>25);
        
# 关键字exist（了解）
	只返回布尔值 True False
    返回True的时候外层查询语句执行
    返回False的时候外层查询语句不执行
    select * from emp where exists
    	(select id from dep where id>3);

今日内容详细

安装
直接百度搜索，有破解的也有非破解
非破解的有试用期，你如果不嫌麻烦，就试用非破解
到期后重新安装在使用，或者破解一下也非常简单
https://www.cr173.com/soft/126934.html
    
navicat能够充当多个数据库的客户端

anvicat图形化界面有时候反应速度比较慢，刷新一下

当你有一些需求该软件无法满足的时候，就自己动手
提示
"""
1、MySQL是不区分大小写的
2、MySQL建议所有的关键字写大写
3、MySQL注释有两种
	--
	#
4、在navicat中如何快速的注释和解注释
	ctrl+?  加注释，再来一次就是解注释
"""

练习题

"""
在解决SQL查询问题的时候不要慌
慢慢的分步骤去敲，一点点的来，东拼西凑的
"""

-- 1、查询所有的课程名称以及对应的任课老师姓名
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teachaer_id = teacher.tid;


-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN (
	SELECT
		score.student_id,
		avg( num ) AS avg_num 
	FROM
		score
		INNER JOIN student ON score.student_id = student.sid 
	GROUP BY
		student_id 
	HAVING
		AVG( num )> 80 
	) AS t1 ON student.sid = t1.student_id;


-- 3、查询没有报李平老师课的学生姓名
SELECT
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
	WHERE
	score.course_id IN ( SELECT teacher.tid FROM teacher INNER JOIN course ON teacher.tid = course.teachaer_id WHERE teacher.tname = '李平老师' ));


-- 4、查询没有同时选修物理课程和体育课程的学生姓名
# 1、先查物理和体育课的ID号
# 2、再去获取所有选了物理和体育的学生数据
# 3、按照学生分组，利用聚合函数count筛选出只选了一门的学生
# 4、依照学生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、查询挂科超过三门（包括三门）的学生姓名和班级
# 1、先筛选出所有分数小于60的数据
# 2、按照学生分组，对数据进行分组，获取大于等于二的数据
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 )>= 3 );

pymysql模块

"""
支持Python代码操作数据库MySQL
"""
pip3 install pymysql

sql注入问题

"""
利用一些语法的特性，书写一些特殊的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- watatfawefaw and password=''

select * from user where name='xxx' or 1=1 --fwasdgaes and password=''
"""
日常生活中很多软件在注册的时候不让有特殊符号
因为怕你构造出特定语句入侵数据库，不安全

# 敏感的数据不要自己做拼接，交给execute即可
# 结合数据库完成一个用户的登录功能
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='db_user',  # 一定要指定库
    charset='utf8'  # 编码千万不要加-，会报错
)  # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

username = input('your name>>>')
password = input('your password>>>')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据，先用%s占位，只后将需要拼接的数据直接交给execute方法即可
rows = cursor.execute(sql, (username, password))  # 自动识别SQL里面的%s用后面元组里面的数据替换
if rows:
    print('登录成功')
    print(cursor.fetchall())
else:
    print('登录失败')
posted @   7七柒  阅读(26)  评论(0)  编辑  收藏  举报
相关博文：
· 第六章：模型层
· 第二章：Django的基础操作及配置
· mysql05--联合查询实例、Navicat软件、pymsql模块、sql注入
· MySQL数据库学习笔记
· 05 MySQL数据库（navicat软件+Python链接MySQL）
阅读排行：
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
点击右上角即可分享
微信分享提示