数据库

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),

);