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与之同义）