mysql-单表查询

py3study

发布于 2018-08-03 10:47:53

4.3K0

发布于 2018-08-03 10:47:53

文章被收录于专栏：python3

语法：

一、单表查询的语法
   SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数
二、关键字的执行优先级（重点）

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

创建公司员工表，表的字段和数据类型

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

创建表,设置字段的约束条件

mysql> create table employee(id int primary key 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 varchar(50),post_comment varchar(100),salary double(15,2),office int,depart_id int);

Query OK, 0 rows affected (0.39 sec)

查看表结构

mysql> desc employee;

+--------------+-----------------------+------+-----+---------+----------------+

+--------------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | enum('male','female') | NO | | male | |

| age | int(3) unsigned | NO | | 28 | |

| office | int(11) | YES | | NULL | |

| depart_id | int(11) | YES | | NULL | |

+--------------+-----------------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

插入记录

三个部门：教学，销售，运营

mysql> insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values

('zhangsan','male',18,'20170301','公关部',7300.33,401,1), #以下是教学部

('lisi','male',78,'20150302','teacher',1000000.31,401,1),

('wangwu','male',81,'20130305','teacher',8300,401,1),

('zhuliu','male',73,'20140701','teacher',3500,401,1),

('chenqi','male',28,'20121101','teacher',2100,401,1),

('wangba','female',18,'20110211','teacher',9000,401,1),

('shenjiu','male',18,'19000301','teacher',30000,401,1),

('tianshi','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)

;

Query OK, 18 rows affected (0.03 sec)

Records: 18 Duplicates: 0 Warnings: 0

查询表数据

mysql> mysql> select * from employee;

+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |

| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 3 | wangwu | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |

| 4 | zhuliu | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |

| 5 | chenqi | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |

| 6 | wangba | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |

| 7 | shenjiu | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |

| 8 | tianshi | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |

| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |

| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |

| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |

| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |

| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |

| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |

| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |

| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |

+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

18 rows in set (0.00 sec)

（1）where 约束

where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之间
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

验证结果:where条件约束

1 :单条件查询

mysql> select id,name from employee where id > 5;

+----+-----------+

| id | name |

+----+-----------+

| 6 | wangba |

| 7 | shenjiu |

| 8 | tianshi |

| 9 | 歪歪 |

| 10 | 丫丫 |

| 11 | 丁丁 |

| 12 | 星星 |

| 13 | 格格 |

| 14 | 张野 |

| 15 | 程咬金 |

| 16 | 程咬银 |

| 17 | 程咬铜 |

| 18 | 程咬铁 |

+----+-----------+

13 rows in set (0.01 sec)

2 :多条件查询

mysql> select id,name,salary from employee where post='teacher' and salary>10000;

+----+---------+------------+

| id | name | salary |

+----+---------+------------+

| 2 | lisi | 1000000.31 |

| 7 | shenjiu | 30000.00 |

+----+---------+------------+

2 rows in set (0.00 sec)

3 :关键字BETWEEN AND

mysql> select name,salary from employee where salary between 10000 and 20000;

+-----------+----------+

| name | salary |

+-----------+----------+

| tianshi | 10000.00 |

| 张野 | 10000.13 |

| 程咬金 | 20000.00 |

| 程咬银 | 19000.00 |

| 程咬铜 | 18000.00 |

| 程咬铁 | 17000.00 |

+-----------+----------+

6 rows in set (0.00 sec)

mysql> select name,salary from employee where salary not between 10000 and 20000;

+----------+------------+

| name | salary |

+----------+------------+

| zhangsan | 7300.33 |

| lisi | 1000000.31 |

| wangwu | 8300.00 |

| zhuliu | 3500.00 |

| chenqi | 2100.00 |

| wangba | 9000.00 |

| shenjiu | 30000.00 |

| 歪歪 | 3000.13 |

| 丫丫 | 2000.35 |

| 丁丁 | 1000.37 |

| 星星 | 3000.29 |

| 格格 | 4000.33 |

+----------+------------+

12 rows in set (0.00 sec)

4 :注意''是空字符串，不是null

mysql> select name,post_comment from employee where post_comment='';

Empty set (0.00 sec)

5:关键字IN集合查询

mysql> select name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;

+--------+---------+

| name | salary |

+--------+---------+

| zhuliu | 3500.00 |

| wangba | 9000.00 |

+--------+---------+

2 rows in set (0.00 sec)

mysql> select name,salary from employee where salary in (3000,3500,4000,9000);

+--------+---------+

| name | salary |

+--------+---------+

| zhuliu | 3500.00 |

| wangba | 9000.00 |

+--------+---------+

2 rows in set (0.00 sec)

mysql> select name,salary from employee where salary not in (3000,3500,4000,9000);

+-----------+------------+

| name | salary |

+-----------+------------+

| zhangsan | 7300.33 |

| lisi | 1000000.31 |

| wangwu | 8300.00 |

| chenqi | 2100.00 |

| shenjiu | 30000.00 |

| tianshi | 10000.00 |

| 歪歪 | 3000.13 |

| 丫丫 | 2000.35 |

| 丁丁 | 1000.37 |

| 星星 | 3000.29 |

| 格格 | 4000.33 |

| 张野 | 10000.13 |

| 程咬金 | 20000.00 |

| 程咬银 | 19000.00 |

| 程咬铜 | 18000.00 |

| 程咬铁 | 17000.00 |

+-----------+------------+

16 rows in set (0.00 sec)

6 :关键字LIKE模糊查询

通配符’%’

mysql> select * from employee where name like 'zhang%';

+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+

+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+

| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |

+----+----------+------+-----+------------+-----------+--------------+---------+--------+-----------+

1 row in set (0.00 sec)

通配符'_'

mysql> select id,age,salary from employee where name like 'lis_';

+----+-----+------------+

| id | age | salary |

+----+-----+------------+

| 2 | 78 | 1000000.31 |

+----+-----+------------+

1 row in set (0.00 sec)

mysql> select id,age,salary from employee where name like 'li__';

+----+-----+------------+

| id | age | salary |

+----+-----+------------+

| 2 | 78 | 1000000.31 |

+----+-----+------------+

1 row in set (0.00 sec)

练习：根据上面创建的表

1 查看岗位是teacher的员工姓名、年龄

mysql> select name,age from employee where post='teacher';

+---------+-----+

| name | age |

+---------+-----+

| lisi | 78 |

| wangwu | 81 |

| zhuliu | 73 |

| chenqi | 28 |

| wangba | 18 |

| shenjiu | 18 |

| tianshi | 48 |

+---------+-----+

7 rows in set (0.00 sec)

2 查看岗位是teacher且年龄大于30岁的员工姓名、年龄

mysql> select name,age from employee where post='teacher' and age >30;

+---------+-----+

| name | age |

+---------+-----+

| lisi | 78 |

| wangwu | 81 |

| zhuliu | 73 |

| tianshi | 48 |

+---------+-----+

4 rows in set (0.00 sec)

3 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

mysql> select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;

+---------+-----+----------+

| name | age | salary |

+---------+-----+----------+

| wangba | 18 | 9000.00 |

| tianshi | 48 | 10000.00 |

+---------+-----+----------+

2 rows in set (0.00 sec)

4 查看岗位描述不为NULL的员工信息

mysql> select * from employee where post_comment is not null;

Empty set (0.00 sec)

5 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,salary from employee where post='teacher' and salary in(9000,10000,30000);

+---------+-----+----------+

| name | age | salary |

+---------+-----+----------+

| wangba | 18 | 9000.00 |

| shenjiu | 18 | 30000.00 |

| tianshi | 48 | 10000.00 |

+---------+-----+----------+

3 rows in set (0.00 sec)

6 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,salary from employee where post='teacher' and salary not in(9000,10000,30000);

+--------+-----+------------+

| name | age | salary |

+--------+-----+------------+

| lisi | 78 | 1000000.31 |

| wangwu | 81 | 8300.00 |

| zhuliu | 73 | 3500.00 |

| chenqi | 28 | 2100.00 |

+--------+-----+------------+

4 rows in set (0.00 sec)

7 查看岗位是teacher且名字是wang开头的员工姓名、年薪

mysql> select name,salary*12 from employee where post='teacher' and name like 'wang%';

+--------+-----------+

| name | salary*12 |

+--------+-----------+

| wangwu | 99600.00 |

| wangba | 108000.00 |

+--------+-----------+

2 rows in set (0.00 sec)

（2）group by 分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

#3、为何要分组呢？
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门：‘每’这个字后面的字段，就是我们分组的依据

#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助
    于聚合函数

当执行以下sql语句的时候，是以post字段查询了组中的第一条数据，没有任何意义，因为我们现在想查出当前组的多条记录

mysql> select * from employee group by post; # 查询不全

+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 1 | zhangsan | male | 18 | 2017-03-01 | 公关部 | NULL | 7300.33 | 401 | 1 |

+----+----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+

4 rows in set (0.00 sec)

由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的,如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY

mysql> set global sql_mode='ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.sql_mode;

+--------------------+

| @@global.sql_mode |

+--------------------+

| ONLY_FULL_GROUP_BY |

+--------------------+

1 row in set (0.00 sec)

继续验证通过group by分组之后，只能查看当前字段,如果想查看组内信息，需要借助于聚合函数

mysql> select post from employee group by post;

+-----------+

| post |

+-----------+

| operation |

| sale |

| teacher |

| 公关部 |

+-----------+

4 rows in set (0.00 sec)

（3）聚合函数

max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
group_concat(字段) 字段所有信息

强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

每个部门有多少个员工

mysql> select post,count(id) from employee group by post;

+-----------+-----------+

| post | count(id) |

+-----------+-----------+

| operation | 5 |

| sale | 5 |

| teacher | 7 |

| 公关部 | 1 |

+-----------+-----------+

4 rows in set (0.00 sec)

每个部门的最高薪水

mysql> select post,max(salary) from employee group by post;

+-----------+-------------+

| post | max(salary) |

+-----------+-------------+

| operation | 20000.00 |

| sale | 4000.33 |

| teacher | 1000000.31 |

| 公关部 | 7300.33 |

+-----------+-------------+

4 rows in set (0.00 sec)

每个部门的最低薪水

mysql> select post,min(salary) from employee group by post;

+-----------+-------------+

| post | min(salary) |

+-----------+-------------+

| operation | 10000.13 |

| sale | 1000.37 |

| teacher | 2100.00 |

| 公关部 | 7300.33 |

+-----------+-------------+

4 rows in set (0.00 sec)

每个部门的平均薪水

mysql> select post,avg(salary) from employee group by post;

+-----------+---------------+

| post | avg(salary) |

+-----------+---------------+

| operation | 16800.026000 |

| sale | 2600.294000 |

| teacher | 151842.901429 |

| 公关部 | 7300.330000 |

+-----------+---------------+

4 rows in set (0.00 sec)

每个部门的所有薪水

mysql> select post,sum(salary) from employee group by post;

+-----------+-------------+

| post | sum(salary) |

+-----------+-------------+

| operation | 84000.13 |

| sale | 13001.47 |

| teacher | 1062900.31 |

| 公关部 | 7300.33 |

+-----------+-------------+

4 rows in set (0.00 sec)

小练习:

1 查询岗位名以及岗位包含的所有员工名字

mysql> select post,group_concat(name) from employee group by post;

+-----------+--------------------------------------------------+

| post | group_concat(name) |

+-----------+--------------------------------------------------+

| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |

| sale | 格格,星星,丁丁,丫丫,歪歪 |

| teacher | tianshi,shenjiu,wangba,chenqi,zhuliu,wangwu,lisi |

| 公关部 | zhangsan |

+-----------+--------------------------------------------------+

4 rows in set (0.00 sec)

2 查询岗位名以及各岗位内包含的员工个数

mysql> select post,count(id) from employee group by post;

+-----------+-----------+

| post | count(id) |

+-----------+-----------+

| operation | 5 |

| sale | 5 |

| teacher | 7 |

| 公关部 | 1 |

+-----------+-----------+

4 rows in set (0.00 sec)

3 查询公司内男员工和女员工的个数

mysql> select sex,count(1) from employee group by sex;

+--------+----------+

| sex | count(1) |

+--------+----------+

| male | 10 |

| female | 8 |

+--------+----------+

2 rows in set (0.00 sec)

4 查询岗位名以及各岗位的平均薪资

mysql> select post,avg(salary) from employee group by post;

+-----------+---------------+

| post | avg(salary) |

+-----------+---------------+

| operation | 16800.026000 |

| sale | 2600.294000 |

| teacher | 151842.901429 |

| 公关部 | 7300.330000 |

+-----------+---------------+

4 rows in set (0.00 sec)

5 查询岗位名以及各岗位的最高薪资

mysql> select post,max(salary) from employee group by post;

+-----------+-------------+

| post | max(salary) |

+-----------+-------------+

| operation | 20000.00 |

| sale | 4000.33 |

| teacher | 1000000.31 |

| 公关部 | 7300.33 |

+-----------+-------------+

4 rows in set (0.00 sec)

6 查询岗位名以及各岗位的最低薪资

mysql> select post,min(salary) from employee group by post;

+-----------+-------------+

| post | min(salary) |

+-----------+-------------+

| operation | 10000.13 |

| sale | 1000.37 |

| teacher | 2100.00 |

| 公关部 | 7300.33 |

+-----------+-------------+

4 rows in set (0.00 sec)

7 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

mysql> select sex,avg(salary) from employee group by sex;

+--------+---------------+

| sex | avg(salary) |

+--------+---------------+

| male | 110920.077000 |

| female | 7250.183750 |

+--------+---------------+

2 rows in set (0.00 sec)

（4）HAVING过滤

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

mysql> select * from employee where salary>1000000;

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

1 row in set (0.00 sec)

mysql> select * from employee having salary>1000000;

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

| 2 | lisi | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

1 row in set (0.00 sec)

错误，分组后无法直接取到salary字段

mysql> select post,group_concat(name) from emp group by post having salary > 10000;

ERROR 1146 (42S02): Table 'db1.emp' doesn't exist

小练习：

1 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

mysql> select post,group_concat(name),count(1) from employee group by post having count(id) < 2;

+-----------+--------------------+----------+

| post | group_concat(name) | count(1) |

+-----------+--------------------+----------+

| 公关部 | zhangsan | 1 |

+-----------+--------------------+----------+

1 row in set (0.00 sec)

2 查询各岗位平均薪资大于10000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;

+-----------+---------------+

| post | avg(salary) |

+-----------+---------------+

| operation | 16800.026000 |

| teacher | 151842.901429 |

+-----------+---------------+

2 rows in set (0.00 sec)

3 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

mysql> select post,avg(salary) from employee group by post having 10000 < avg(salary) and avg(salary) < 20000 ;

+-----------+--------------+

| post | avg(salary) |

+-----------+--------------+

| operation | 16800.026000 |

+-----------+--------------+

1 row in set (0.00 sec)

（5）order by 查询排序

按单列排序
    SELECT * FROM employee ORDER BY age;
    SELECT * FROM employee ORDER BY age ASC;
    SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
    SELECT * from employee
        ORDER BY age ASC,
        id DESC;

验证多列排序

mysql> select * from employee order by age asc,id desc;