五

今日内容概要

内容回顾
小知识点补充
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);

今日内容详细

Navicat软件

安装

直接百度搜索，有破解的也有非破解
非破解的有试用期，你如果不嫌麻烦，就试用非破解
到期后重新安装在使用，或者破解一下也非常简单
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 @ 2023-01-31 20:28 7七柒阅读(34) 评论(0) 收藏举报

刷新页面返回顶部

liuhousheng

五

今日内容概要

内容回顾

小知识点补充

今日内容详细

Navicat软件

安装

提示

练习题

pymysql模块

sql注入问题

公告