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;

 

转载于:https://www.cnblogs.com/chengdajing/articles/7731918.html