mysql-单表查询

发布时间：2018-06-13 21:31:19编辑：Run阅读（4534）

语法：

一、单表查询的语法
   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;