day04 mysql单表查询 多表查询 pymysql的使用
day04 mysql pymysql
一.单表查询
1.having过滤
一般用作二次筛选
也可以用作一次筛选(残缺的: 只能筛选select里面有的字段)
有了where为啥还用having:
where后面不能直接用聚合函数, 要having后跟聚合函数查询
where>group by>having: 即是执行的优先级顺序,同时也是书写的顺序
having的条件, 前面select里面必须要有?
因为having是对分组之后的结果进行筛选, 你分组后有什么我才能筛选什么
where > having: 可以这样, 因为不分组, 你用where出来的就是一个大组(不同版本有区别)
> select post,count(1) from employee group by post having count(1) > 2;
> select post,group_concat(name,post),group_concat(sex) from employee group by post; //group_concat(field)函数,可查其他字段的值, 以,号连接展示 //(concatenate 连接)
查询各岗位内包含的员工个数小于2的,岗位名,岗位内员工名,员工个数
> select post,group_concat(name),count(1) from employee group by post having count(1) < 2;
查询各岗位平均薪资大于10000的岗位名,平均工资
> select post,avg(salary) from employee group by post having avg(salary) > 10000;
查询各岗位平均薪资在10000和20000之间的岗位名,平均薪资
> select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
2.order by
> select * from employee; #默认是按照id升序, 默认asc升序; desc是降序
> select * from employee order by age asc, salary asc; #先按age升序, 再按salary升序
3.limit
> select * from employee limit 3; //有一个参数,默认隐含一个0, 即(0,3)
> select * from employee limit 3,3; //(3,3): 第一个3表示起始位置(3是索引),第二个3表示步长 //(索引,步长)
用索引做分页不好, 因为查询的时候,是从开始的0开始的,查询消耗大
二.多表查询
为啥要有多表查询, 是为了程度上替代外键
准备待查询的两张表
create table department( id int, name varchar(20) );
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营');
insert into employee
(name,sex,age,dep_id)
values ('bajie','male',18,200),
('wukong','female',48,201),
('datang','male',38,201),
('xixi','female',28,202),
('haha','male',18,200),
('lele','female',18,204)
;
1.多表连接查询
> select * from employee,department; # 直接一起查询两张表: 笛卡尔积的效果
> select * from employee inner join department; # (内连接: 后面可以没有on) 使用sql提供的多表查询的语法: 笛卡尔积的效果
> select * from employee,department where employee.dep_id = department.id; #加where条件,查询预期结果
> select employee.name,department.name from employee,department where employee.dep_id = department.id; #查找指定字段
> select * from employee inner join department on employee.dep_id = department.id; #(内连接之inner: 使用sql的多表查询语法, 用on加条件)
> select * from employee left join department on employee.dep_id = department.id; #(外连接之left: 左表优先显示,在右表没有的用NULL补空)
> select * from employee right join department on employee.dep_id = department.id; #(外连接之right: 右表优先显示,在左表没有的用NULL补空)
全连接 full join, mysql没有这种语法, 但可以用union连接两个查询的语句,实现同等功能
> select * from employee left join department on employee.dep_id = department.id #(全连接之unin)
union
select * from employee right join department on employee.dep_id = department.id;
2.符合条件连接查询
找出年龄大于25岁的员工及所在部门
> select employee.name,department.name,employee.age from employee left join department on employee.dep_id = department.id where employee.age > 20;
3.子查询
是将一个查询语句嵌套在另一个查询语句中
通常是一个语句查询的结果作为另一个查询语句的条件
子查询中可以包含: in not in any all exists not exists等关键字
还可以包含: = != <> <= => < >等
子查询包含in:
查询平均年龄在25岁以上的部门名
> select * from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); # in的用法
查看不足一人的部门名
> select * from department where id not in (select dep_id from employee group by dep_id having count(id) >= 1); #not in的用法
子查询包含 < > 等
查询大于所有人平均年龄的员工名与年龄
> select name,age from employee where age > (select avg(age) from employee);
查询大于部门内平均年龄的员工名,年龄
分步操作: 先分组group by: 查平均年龄,和部门id形成临时表
on: 根据部门id相同, 把平均年龄的表和原始表连接(left join)起来
根据条件where: age > avgage查最终的结果
需要注意: as的作用: 多表查询时, 通过内置函数如avg(age)取到的字段名要 as 一个变量名, 否则后面 where时, 函数名部分会报错
> select * from employee left join (select dep_id,avg(age) as avgage from employee group by dep_id) as A on employee.dep_id = A.dep_id where employee.age > A.avgage;
子查询带exists关键字
exists关键字表示存在, 在使用exists关键字时,内层查询语句不返回查询的记录,而是返回一个真假值,true或false
当返回ture时,外层查询语句将进行查询; 当返回false时,外层查询语句不进行查询
> select * from employee where exists (select id from department where id = 200);
查询每个部门最新入职的那位员工
#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#插入记录
#三个部门：教学，销售，运营insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('bajie','male',18,'20170301','八戒一下',7300.33,401,1), #以下是教学部
('wukong','male',78,'20150302','teacher',1000000.31,401,1),
('datang','male',81,'20130305','teacher',8300,401,1),
('shaseng','male',73,'20140701','teacher',3500,401,1),
('wujing','male',28,'20121101','teacher',2100,401,1),
('wikong','female',18,'20110211','teacher',9000,401,1),
('wuneng','male',18,'19000301','teacher',30000,401,1),
('xixi','male',48,'20101111','teacher',10000,401,1),
('嘻嘻','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('乐乐','female',18,'20110312','sale',1000.37,402,2),
('呵呵','female',18,'20160513','sale',3000.29,402,2),
('哈哈','female',28,'20170127','sale',4000.33,402,2),
('程咬氢','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
> select * from employee left join (select post,max(hire_date) as maxhiredate from employee group by post) as a on employee.post = a.post where employee.hire_date = a.maxhiredate;
三.pymysql的下载和使用
1.该模块的本质就是: 一个套接字客户端软件
2.pymysql模块的下载
pip3 install pymysql
3.execute() 以及 sql注入
#coding=utf-8
#Version:python3.5.0
#Tools:Pycharm 2017.3.2
__date__ = '2019/12/12 13:37'
__author__ = 'wt'
import pymysql
username = input('请输入用户名: ')
pwd = input('请输入密码: ')
#建立链接
conn = pymysql.connect(host='127.0.0.1',
user='root',
password="",
database='db2',
port=3306,
charset='utf8',)
#创建游标
cur = conn.cursor()
#sql = 'select * from userinfo where name="%s" and password="%s"' % (username,pwd) #要加引号: 要和你写的sql语句保持一致,这里就是你的sql语句
sql = 'select * from userinfo where name=%s and password=%s'
print(sql)
# rst = cur.execute(sql)
rst = cur.execute(sql,[username,pwd]) #参数加个元组或列表, 可以把替换的字符串自动加""引号: 也可以使用()元组, 也可以是字典用%()s替换
print(rst) #打印的是 1: 1 row in set (0.00 sec) 是1行受影响的 1
#关闭游标
cur.close()
#关闭链接
conn.close()
if rst:
print('登录成功')
else:
print('登录失败')
#注射攻击 方式一: 用户名是对的, 利用 "-- 的注释作用
#> 请输入用户名: bajie" -- ;laskjdfosajf0ijfkajdsfkjasd;f
#> 请输入密码: 123
#> select * from userinfo where name="bajie" -- ;laskjdfosajf0ijfkajdsfkjasd;f" and password="123" # -- 后面的都是注释掉了,所以查出来结果是 1
#> 1
#> 登录成功
#注射攻击 方式二: 利用where 的 or属性
# 请输入用户名: sdlkjsal" or 1=1 -- asdkja;lsjasf
# 请输入密码:
# select * from userinfo where name="sdlkjsal" or 1=1 -- asdkja;lsjasf" and password=""
# 1
# 登录成功
#如何解决,防止, 注射攻击
#拼接sql语句的时候不要自己去拼接, 使用pymysql中的rst = cur.execute(sql,[username,pwd]), 已经内部解决了这个问题
内容总结:
1.单表查询
group by
having
order by
limit
2.多表查询
子查询: 内层查询的结果作为外层查询的条件
外连接
内连接: 连接匹配的行
inner join ... on
左连接: 优先显示左表记录
left join ... on
右连接: 优先显示右表记录
right join ... on
全外连接: union
3.pymysql
import pymysql
conn = pymysql.connect(
host:'127.0.0.1',
port:3306,
database:'db1',
user:'root',
password:'',
charset:'utf8',)
cur = conn.sursor()
sql = ''
rst = cur.execute(sql,())
if rst:
print('successfull')
else:
print('failed')
cur.close()
conn.close()