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 条件 分组聚合
来源: http://www.bubuko.com/infodetail-3682119.html