Python--MySql系列 --查询数据
今日内容:
先创建一个表,然后方便下边的操作:
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,
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_comment VARCHAR(100),
post VARCHAR(50),
salary DOUBLE(15,2),
office INT,
depart_id INT
) CHARSET utf8;
INSERT INTO employee (name,sex,age,hire_date,post_comment,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)
;
一 单表查询的语法:
#单表查询语法:
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee;
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS('
二 where约束条件
#where 约束条件
1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_,
%表示任意多字符
_表示任意一个字符
5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
select id,name from employee where post='teacher';
select id,name from employee where id >=3 and id < 5;
select id,name from employee where id <=3 or id >= 5;
select id,name from employee where id between 3 and 5;
select id,name from employee where id >=3 and id <= 5;
select id,name,age from employee
where age = 60 or age = 70 or age = 80 or age = 18;
select id,name,age from employee
where age not in (60,70,80,18,'aaa');
select id,name,age from employee
where name like 'e%';
select id,name,age from employee
where name not like '___';
#null并不是空字符串
mysql> select * from employee where not post_comment is null;
Empty set (0.00 sec)
#练习
1. 查看岗位是teacher的员工姓名、年龄
select name,age from employee where post='teacher';
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from employee where post='teacher' and age > 30;
3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select name,age,salary from employee
where post='teacher' and salary between 9000 and 10000;
4. 查看岗位描述不为NULL的员工信息
select * from employee where not post_comment is null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary*12 from employee where post='teacher' and name like 'jin%';
三 分组group by
#单表查询语法:
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
#统计:每个部门的员工数
select post,count(id) from employee group by post;
#强调:
#1、分组之后,select只能查看到分组的字段,要想查组内内容
#不能直接查看,需要借助于聚合函数max,min,avg,sum,count
#2、分组的目的是为类以组为单位来处理记录,而不是处理单独的记录
#3、如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
#查询总员工数:没有分组,默认整体一组
select count(id) from employee;
#查看每个部门的员工数
select post,count(id) from employee group by post;
#查看teacher部门的员工数
select count(id) from employee where post='teacher';
#查看每个部门的最高工资
select post,max(salary) from employee group by post;
select post,min(salary) from employee group by post;
select post,avg(salary) from employee group by post;
select post,sum(salary) from employee group by post;
select post,count(id) from employee group by post;
select post,group_concat(name) from employee group by post;
select post,group_concat('薪资',salary) from employee group by post;
#only_full_group_by
select @@global.sql_mode;
set global sql_mode = 'only_full_group_by';
select * from employee group by post;
select post from employee group by post;
select post,name from employee group by post;
#练习
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(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. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;
#查看组内最高工资的人名
select employee.post,employee.name from employee inner join (
select post,max(salary) salary from employee group by post ) as t1
on employee.post=t1.post and t1.salary = employee.salary
;
四 having过滤条件
#单表查询语法:
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
select * from employee where salary > 10000;
#错误:having是在分组之后的,意味着,只能用分组的字段或者聚合函数作为过滤条件
select post from employee
group by post
having salary > 10000
;
select * from employee
having salary > 10000;
select * from employee
having count(id) > 10000;
#取出员工数大于3的部门
select post from employee group by post having count(id) > 3;
#取出id是大于10的,员工数大于3的部门
select post from employee where id > 10 group by post having count(id) > 3;
#练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
五 dictinct去重
#单表查询语法:
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
六 order by排序
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
#默认升序
select * from employee order by age asc;
select * from employee order by age;
#降序
select * from employee order by age desc;
#先按照age升序排,如果age相同,则id降序排序
select * from employee order by age asc,id desc;
#查看每个部门的最高工资
select post,max(salary) from employee group by post order by max(salary);
select post,max(salary) as m from employee group by post order by m;
#取出平均工资>10000的部门以及它的平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#错误,执行having时,select还没有执行呢
select post,avg(salary) as x from employee group by post having x > 10000;
#取出平均工资>10000的部门以及它的平均工资
select post,avg(salary) as x from employee
group by post
having avg(salary) > 10000
order by x desc
;
select post,avg(salary) as x from employee
group by post
having avg(salary) > 10000
order by avg(salary) desc
;
#错误
select post,avg(salary) as x from employee
group by post
having avg(salary) > 10000
order by post
;
select post,avg(salary) as x from employee
group by post
having avg(salary) > 10000
order by x
;
七 limit限制条数
select distinct 字段1,字段2,字段3。。。。 from 表名
where 约束条件
group by 分组的字段
having 过滤条件
order by 排序字段
limit 限制条件;
select * from employee limit 3;
#取出工资最高的那个员工信息
select * from employee order by salary desc limit 1;
#总共的计数数:30123
#一页只能显示:30
select * from employee limit 0,3;
select * from employee limit 3,3;
select * from employee limit 6,3;