Python基础学习（38）数据的操作单表查询 where条件分组聚合

时间：2020-10-12 00:34:12 阅读：50 评论：0 收藏：0 [点我收藏+]

Python基础学习（39）数据的操作单表查询 where条件分组聚合

一、今日内容大纲

数据的操作
单表查询
where条件
分组聚合

二、数据的操作

首先我们创建一个表以供增删改操作：

create table t1(
    id int primary key auto_increment,
    username char(12) not null,
    sex enum(‘male‘, ‘female‘) default ‘male‘,
    hobby set(‘上课‘, ‘写作业‘, ‘考试‘) not null
);

技术分享图片

增加

# 增加数据
insert into t1 values(1, ‘大壮‘, ‘male‘, ‘上课‘);
insert into t1 values(2, ‘都详细‘, ‘male‘, ‘写作业,考试‘);
insert into t1 values(3, ‘b个‘, ‘male‘, ‘写作业‘), (4, ‘装波‘, ‘male‘, ‘考试‘);
insert into t1(username, hobby) values(‘样的杠‘,‘上课,写作业,考试‘);

# 也可以从其它表中调取数据
# 创建t2
create table t2(
    id int,
    name char(12)
);
# 从t1调取数据加入t2
insert into t2(id, name) select id, username from t1;

删除

# 删除
delete from t1 where id = 3;
delete from t2;  # 会删除表,但不会删除自增字段的偏移量
truncate table t1;  # 会清空表和自增偏移量

修改

# 修改
update t1 set id=1, hobby=‘写作业,考试‘ where id = 2;

# 另外也可以依靠navicat可视化工具实现对数据的可视化，利用pymysql模块实现对数据的操作

三、单表查询

首先进行建表数据准备：

company.employee
    员工id      id                  int
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
id int not null unique auto_increment,
emp_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 |
| emp_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(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部
(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),
(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),
(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),
(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),
(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),
(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),
(‘成龙‘,‘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)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

单表查询具体操作如下：

# 表单查询
select * from employee;
select id, emp_name from employee;

# 重命名关键字
select id, emp_name as name from employee;
select id, emp_name name from employee;
select id i, emp_name name from employee;

# 去重查询
select distinct post from employee;
select distinct age, sex from employee;  # 只有两个字段同时相同才会被去重

# 四则运算
select emp_name, salary*12 as annual_salary from employee;

# 拼接
select concat(emp_name,‘:‘,salary) from employee;  # 显示为 emp_name:salary
select concat_ws(‘|‘, id, emp_name, salary) from employee;  # 显示为 id|emp_name|salary

# 条件判断：case语句(无法筛选数据使其不显示)
select
    (
        case
        when emp_name = ‘jingliyang‘ then
            emp_name
        when emp_name = ‘alex‘ then
            concat(emp_name,‘_BIGSB‘)
        else
            concat(emp_name,‘_SB‘)
        end
    ) as new_name
from
    employee;

# 练习：
# 1.查出所有员工的名字、薪资，格式为：
# <名字:egon>   <薪资:3000>
# 2.查出所有的岗位(去掉重复)
# 3.查出所有员工的名字以及他们的年薪，年薪的字段名为annual_year
select concat(‘<名字:‘, emp_name, ‘>   <薪资:‘, salary, ‘>‘) from employee;
select distinct post from employee;
select emp_name, salary*12 as annual_salary from employee

四、where条件

where主要用于筛选所有符合条件的行，主要支持：

比较运算符：> < >= <= <> != 等；
值区间：between 50 and 100表示"值在50到100之间";
多个值：in(80, 90, 100)表示“值为80、90或100”;
模糊查找：
- like ‘a%‘表示“以a为开头的字符”，统配符%表示任意多字符，_表示一个字符；
- regex ‘regex‘表示“所有正则表达式对应为‘regex‘的字符串”;
逻辑运算符：not or and 优先级递减；

# 练习
# 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select emp_name, age, salary from employee where post = ‘teacher‘ and salary not in(10000, 9000, 30000);
# 查看岗位是teacher且名字不是jin开头的员工姓名、年薪
select emp_name, salary from employee where post = ‘teacher‘ and emp_name not like ‘jin%‘;

五、分组聚合

分组（group by）

分组会删除所有重复情况。根据分组，可以求得这个组的总人数、最大值、最小值、平均值、求和，但是求出来的值只和分组字段对应，不和其他任何字段对应，这个时候查出来的所有其他字段都不生效；

如我们按照post分组：
```
select * from employee group by post;
```
返回结果如下：

聚合函数

聚合函数主要和分组搭配食用，可以求取分组的各种统计情况：

count()：求个数；
max()：求最大值；
min()：求最小值；
sum()：求和；
avg()：求平均值；
group_concat()：将所有分组所查元素拼接；

# group_concat()可以将所有查到的元素拼接到一起看
select post, group_concat(emp_name) from employee group by post;

返回结果如下：

技术分享图片

# 小练习：
# 1.查询岗位名及岗位包含的所有员工名字
select post, group_concat(emp_name) from employee group by post;
# 2.查询岗位名及各岗位内包含的员工个数
select post, count(id) from employee group by post;
# 3.查询公司内男员工和女员工的个数
select sex, count(id) from employee group by sex;
# 4.查询岗位名及各岗位的平均薪资
select post, avg(salary) from employee group by post;
# 5.查询岗位名及各岗位的最低薪资
select post, min(salary) from employee group by post;
# 6.查询岗位名及各岗位的最高薪资
select post, max(salary) from employee group by post;
# 7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from employee group by sex;

过滤语句（having）

having主要使用也是用于过滤select from 的输出结果，但是与where不同的是，having可以使用聚合函数而where不可以；适合去筛选符合条件的某一组数据，而不是某一行数据；需要先分组再过滤，如求薪资大于xx的部门，求人数大于xx的性别，求人数大于xx的年龄等；
```
# 查询平均薪资大于10000的岗位名及平均薪资
select post, avg(salary) from employee group by post having avg(salary) > 10000;
# 查询各岗位内包含的员工个数小于2的岗位名，岗位内包含员工名字、个数
select post, group_concat(emp_name), count(id) from employee group by post having count(id) < 2;
```

查询排序（order by）及limit的用法

主要用于查询结果的排序，时间也可参与排序：

# order by 查询排序(时间也可以参与排序)
# 从小到大排序薪资
select * from employee order by salary;
# 从大到小排序薪资
select * from employee order by salary desc;
# 先从小到大排年龄，年龄相同的情况下从大到小排序薪资
select * from employee order by age, salary desc;
# 取薪资最高的人
select * from employee order by salary desc limit 1;
# 取薪资前三的人
select * from employee order by salary desc limit 3;
# 取薪资第三的人
select * from employee order by salary desc limit 2, 1;
# limit m, n:从m+1项开始，取n项；如果不写m，m默认为0；（limit m offset n与之同义）

Python基础学习（38）数据的操作单表查询 where条件分组聚合

原文：https://www.cnblogs.com/raygor/p/13800023.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)

Python基础学习（38） 数据的操作 单表查询 where条件 分组聚合

Python基础学习（39） 数据的操作 单表查询 where条件 分组聚合

一、今日内容大纲

二、数据的操作

三、单表查询

四、where条件

五、分组聚合

Python基础学习（38）数据的操作单表查询 where条件分组聚合

Python基础学习（39）数据的操作单表查询 where条件分组聚合