10.4.1where语句

#!/usr/bin/env python

-- coding:utf-8 --

​

比较运算 > < = >= <= != <>

范围筛选

# 多选一 字段名 in (值1,值2，值3) # 20000,30000,3000,19000,18000,17000 # select * from employee where salary in (20000,30000,3000,19000,18000,17000) # 在一个模糊的范围里 between 10000 and 20000 # 在一个数值区间 1w-2w之间的所有人的名字 # select emp_name from employee where salary between 10000 and 20000; # 字符串的模糊查询 like # 通配符 % 匹配任意长度的任意内容 # 通配符 _ 匹配一个字符长度的任意内容 # 正则匹配 regexp 更加细粒度的匹配的时候 # select * from 表 where 字段 regexp 正则表达式 # select * from employee where emp_name regexp '^j[a-z]{5}'

逻辑运算 - 条件的拼接

# 与 and # 或 or # 非 not # select * from employee where salary not in (20000,30000,3000,19000,18000,17000)

​

身份运算 - 关于null is null /is not null

# 查看岗位描述不为NULL的员工信息 # select * from employee where post_comment is not null;

​

查看岗位是teacher且名字是jin开头的员工姓名、年薪

#select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%' #select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'

​ 10.4.2分组group by

#!/usr/bin/env python

-- coding:utf-8 --

​

分组 group by

select * from employee group by post

会把在group by后面的这个字段，也就是post字段中的每一个不同的项都保留下来

并且把值是这一项的的所有行归为一组

​

聚合 把很多行的同一个字段进行一些统计，最终的到一个结果

# count(字段) 统计这个字段有多少项 # sum(字段) 统计这个字段对应的数值的和 # avg(字段) 统计这个字段对应的数值的平均值 # min(字段) # max(字段)

​

分组聚合

# 求各个部门的人数 # select count(*) from employee group by post # 求公司里 男生 和女生的人数 # select count(id) from employee group by sex # 求各部门的平均薪资 # 求各部门的平均年龄 # 求各部门年龄最小的 # select post,min(age) from employee group by post # 求各部门年龄最大的 # 求各部门薪资最高的 # 求各部门薪资最低的 # 求最晚入职的 # 求最早入职的 # 求各部门最晚入职的 # 求各部门最早入职的

​

求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到

但是要得到对应的人，就必须通过多表查询

​

总是根据会重复的项来进行分组

分组总是和聚合函数一起用 最大 最小 平均 求和 有多少项

​ 10.4.3having_orderby_limit

#!/usr/bin/env python

-- coding:utf-8 --

​

having 条件 # 过滤 组

​

部门人数大于3的部门

select post from employee group by post having count(*) > 3

​

1.执行顺序 总是先执行where 再执行group by分组

所以相关先分组 之后再根据分组做某些条件筛选的时候 where都用不上

2.只能用having来完成

​

平均薪资大于10000的部门

select post from employee group by post having avg(salary) > 10000

​ ​

select * from employee having age>18

​

order by

# order by 某一个字段 asc; 默认是升序asc 从小到大 # order by 某一个字段 desc; 指定降序排列desc 从大到小 # order by 第一个字段 asc,第二个字段 desc; # 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列

​

limit

# 取前n个 limit n == limit 0,n # 考试成绩的前三名 # 入职时间最晚的前三个 # 分页 limit m,n 从m+1开始取n个 # 员工展示的网页 # 18个员工 # 每一页展示5个员工 # limit n offset m == limit m,n 从m+1开始取n个

​ 10.4.4pymysql模块导入和安装

#!/usr/bin/env python

-- coding:utf-8 --

import pymysql

conn = pymysql.connect(host=‘127.0.0.1’, user=‘root’, password=“123”,

database=‘day40’)

cur = conn.cursor() # 数据库操作符 游标

# cur.execute('insert into employee(emp_name,sex,age,hire_date) ’

# ‘values (“郭凯丰”,“male”,40,20190808)’)

# cur.execute(‘delete from employee where id = 18’)

conn.commit()

conn.close()

​ ​

conn = pymysql.connect(host=‘127.0.0.1’, user=‘root’, password=“123”,

database=‘day40’)

cur = conn.cursor(pymysql.cursors.DictCursor) # 数据库操作符 游标

cur.execute('select * from employee ’

‘where id > 10’)

ret = cur.fetchone()

print(ret[‘emp_name’])

# ret = cur.fetchmany(5)

ret = cur.fetchall()

print(ret)

conn.close()

​

select * from employee where id > 10

​ ​

sql注入风险

10.4.5多表查询

#!/usr/bin/env python

-- coding:utf-8 --

多表查询

# 两张表是怎么连在一起的 # select * from emp,department; # 连表查询 # 把两张表连在一起查 # 内链接 inner join 两张表条件不匹配的项不会出现再结果中 # select * from emp inner join department on emp.dep_id = department.id; # 外连接 # 左外连接 left join 永远显示全量的左表中的数据 # select * from emp left join department on emp.dep_id = department.id; # 右外连接 right join 永远显示全量的右表中的数据 # select * from emp right join department on emp.dep_id = department.id; # 全外连接 # select * from emp left join department on emp.dep_id = department.id # union # select * from department right join emp on emp.dep_id = department.id; # 子查询 # 找技术部门的所有人的姓名 # 先找到部门表技术部门的部门id # select id from department where name = '技术'; # 再找emp表中部门id = 200 # select name from emp where dep_id = (select id from department where name = '技术');

​ # 找到技术部门和销售部门所有人的姓名 # 先找到技术部门和销售部门的的部门id # select id from department where name = ‘技术’ or name=‘销售’ # 找到emp表中部门id = 200或者202的人名 # select name from emp where dep_id in (select id from department where name = ‘技术’ or name=‘销售’); # select emp.name from emp inner join department on emp.dep_id = department.id where department.name in (‘技术’,‘销售’); ​

连接的语法

select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;

# 常用 # 内链接 # 左外链接

​

找技术部门的所有人的姓名

# select * from emp inner join department on emp.dep_id = department.id;

±—±----------±-------±-----±-------±-----±-------------+

| id | name | sex | age | dep_id | id | name |

±—±----------±-------±-----±-------±-----±-------------+

| 1 | egon | male | 18 | 200 | 200 | 技术 |

| 2 | alex | female | 48 | 201 | 201 | 人力资源 |

| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |

| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |

| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |

±—±----------±-------±-----±-------±-----±-------------+

select * from emp inner join department on emp.dep_id = department.id where department.name = ‘技术’

select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = ‘技术’

​

找出年龄大于25岁的员工以及员工所在的部门名称

select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;

​

根据age的升序顺序来连表查询emp和department

select * from emp inner join department as d on emp.dep_id = d.id order by age;

​

优先使用连表查询，因为连表查询的效率高

​ ​

练习

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

部门名 department表

select name from department where id in (

select dep_id from emp group by dep_id having avg(age)>25

);

员工表

select dep_id,avg(age) from emp group by dep_id;

select dep_id from emp group by dep_id having avg(age)>25;

​

查看不足1人的部门名(子查询得到的是有人的部门id)

查emp表中有哪些部门id

select dep_id from emp group by dep_id;

再看department表中

select * from department where id not in (???)

select * from department where id not in (select dep_id from emp group by dep_id);

​

查询大于所有人平均年龄的员工名与年龄

select * from emp where age>(select avg(age) from emp);

​

查询大于部门内平均年龄的员工名、年龄

select dep_id,avg(age) from emp group by dep_id;

select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d

on emp.dep_id = d.dep_id where emp.age > d.avg_age;

表的关系

#!/usr/bin/env python

-- coding:utf-8 --

book ：id name price author_id

author:aid name birthday gender

作者与书 一对多

create table author(

aid primary key auto_increment,

name char(12) not null,

birthday date,

gender enum(‘male’,‘female’) default ‘male’

)

create table book(

id int primary key,

name char(12) not null,

price float(5,2)

author_id int,

foreign key(author_id) references author(aid)

)

作者与书一对一

create table author(

aid primary key auto_increment,

name char(12) not null,

birthday date,

gender enum(‘male’,‘female’) default ‘male’

)

create table book(

id int primary key,

name char(12) not null,

price float(5,2)

author_id int unique,

foreign key(author_id) references author(aid)

)

作者与书多对多

create table author(

aid primary key auto_increment,

name char(12) not null,

birthday date,

gender enum(‘male’,‘female’) default ‘male’

)

create table book(

id int primary key,

name char(12) not null,

price float(5,2)

)

create table book_author(

id int primary key auto_increment,

book_id int not null,

author_id int not null,

foreign key(book_id) references book(id),

foreign key(author_id) references author(aid),

);