day48
day48
01.昨日内容回顾
查询主要关键字
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 in (18000,17000);
where salary not in (18000,17000);
# 模糊匹配 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 group 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;
"""
python复习
','.join([111,222,333,444])
"""
# as 语法
1 可以给展示字段起别名
2 可以给表起别名
# 聚合函数
max
min
sum
count
avg
聚合函数必须在分组之后使用
having
# 用法跟where一模一样 只不过他是作用域分组之后的再次筛选
...group by post having avg(salary) > 30000;
distinct
# 数据必须是一模一样
select distinct post from emp;
order by
# 排序 默认是升序
order by salary; === order by salary asc;
oerder by salary desc;
order by salary asc,age desc; # 还支持多个字段备用比较
limit
"""
限制数据的展示条数 效果就是分页的效果
"""
select * from emp limit 5;
limit 5;
limit 5,5 第一个参数是起始位置 第二个参数是条数
regexp
"""
正则是一门独立语言
在python中如果你想使用正则,需要借助于re模块
面试题
1.re模块中常用的方法
findall:分组优先展示
^j.*(n|y)$
不会展示所有正则表达式匹配到的内容
而仅仅展示括号内正则表达式匹配到的内容
match:从头匹配
search:从整体匹配
2.贪婪匹配与非贪婪匹配
正则表达式默认都是贪婪匹配的
将贪婪变成非贪婪只需要在正则表达式后面加?
.* 贪婪
.*? 非贪婪
"""
select * from emp where name regexp '^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
union
左右全书展示 没有对应的就用null补全
# 子查询
"""
子查询就是我们平时解决问题的思路 分步处理
将一张表的查询结果当做另外一条sql语句的查询条件
(当做条件的时候 用括号括起来)
select * from emp where id in (select id from dep);
"""
总结
# 书写sql语句的时候 select后面先用*占位 之后写完再改
# 在写较为复杂的sql语句的时候 不要想着一口气写完 写一点查一点(只要涉及到数据相关的语法都不应该一次性写完 不太现实)
# 在做多表查询的时候 联表操作和子查询可能会结合使用
02.知识点补充(exists)
# 查询平均年龄在25岁以上的部门
"""只要是多表查询 就有两种思路 联表 子查询"""
# 联表操作
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);
select * from emp where exists
(select id from dep where id >300);
03.今日内容概要
navicat可视化界面操作数据库
数据库查询题目讲解(多表操作)
python如何操作Mysql(pymysql模块)
sql注入问题
pymysql模块增删改查数据操作
04.navicat基本使用
pass
05.sql查询练习题
init.sql文件
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
从init.sql中导入数据
# 准备表、记录
create database db5;
use db5;
source D:\init.sql
逆向数据库到模型
多表查询题
第一题
# 一.查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要的表 course表 teacher表
select * from course;
select * from teacher;
select
course.cname,teacher.tname
from course
inner join teacher on course.teacher_id = teacher.tid;
# 二.查询平均成绩大于八十分的同学的姓名和平均成绩
06.pymysql基本使用
模块安装
python 胶水语言、调包侠(贬义词>>>褒义词)
"""
python这门语言本身并不牛逼 牛逼的是支持该语言的各种功能强大的模块、软件
"""
# 后期在使用python编程的时候 很多看似比较复杂功能可能都已经有相应的模块
模块名字 pymysql
下载模块
1.命令行
pip3 install pymysql
# 豆瓣源
pip install pymysql -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com
2.借助于pycharm
3.python解释器配置文件
模块基本使用
import pymysql
# 链接服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='wang123',
database='db5',
charset='utf8' # 编码千万不要加-
)
# cusor = conn.cursor() # 产生一个游标对象(用来执行命令的)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 括号里面放cursor=pymysql.cursors.DictCursor让数据自动组织成字典
# 编写SQL语句
sql = "select * from teacher"
# 发送给服务端
res = cursor.execute(sql)
# print(res) # 返回的是当前sql语句所影响的行数
# 获取命令的执行结果
# print(cursor.fetchone()) # 只拿一条
# print(cursor.fetchall()) # 拿到所有
# print(cursor.fetchmany(2)) # 可以指定拿几条
print(cursor.fetchone())
print(cursor.fetchone()) # 读取数据类似于文件光标的移动
cursor.scroll(1, 'relative') # 相对于光标所在的位置继续往后移动1位
cursor.scroll(1, 'absolute') # 相对数据的开头往后继续移动1位
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
07.sql注入及解决方法
准备数据
create table userinfo (id int primary key auto_increment,name varchar(32),password int);
insert into userinfo(name,password) values('wangmiaolu',123),('yujian',456),('xionghuijun',789);
select * from userinfo;
sql注入
"""
什么是sql注入?
通过输入一堆特殊的字符串,来改变sql语句的执行逻辑.从而改变程序的执行逻辑,绕过认证环节
如何解决上述问题?
敏感信息不要自己拼接,利用模块来防止注入,例如pymysql模块
"""
# sql注入问题实例
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='wang123',
database='db5',
charset='utf8mb4'
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 获取用户输入
username = input('用户名>>：').strip() # username = wangmiaolu' -- sjkfjkdjfkd
# username = input('用户名>>：').strip() # username = xxx' or 1=1 ksadhdahds
password = input('密码>>：').strip() # password=""
sql = "select * from userinfo where name='%s' and password='%s'" % (username, password)
print(sql)
res = cursor.execute(sql)
data = cursor.fetchall()
print(data)
if res:
print('登录成功')
else:
print('登录失败')
cursor.close()
conn.close()
如何解决sql注入?
# 解决办法:在服务端防止sql注入问题：不要自己拼接字符串，让pymysql模块去拼接,在pymysql中execute就能帮你过滤
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 获取用户输入
username = input('用户名>>：').strip() # username = wangmiaolu' -- sjkfjkdjfkd
# username = input('用户名>>：').strip() # username = xxx' or 1=1 ksadhdahds
password = input('密码>>：').strip() # password=""
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
rows = cursor.execute(sql, (username, password)) # 交由excute自动拼接 自动筛选
data = cursor.fetchall()
print(data)
if rows:
print('登录成功')
else:
print('登录失败')
