mysql-单表查询

发布时间:2018-06-13 21:31:19编辑:Run阅读(3547)

    语法:

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

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

    | Field        | Type                  | Null | Key | Default | Extra          |

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

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

    | 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    |                |

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

    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;

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

    | id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |

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

    |  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%';

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

    | id | name     | sex  | age | hire_date  | post      | post_comment | salary  | office | depart_id |

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

    |  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;  # 查询不全

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

    | id | name     | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |

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

    | 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;

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

    | id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |

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

    |  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;

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

    | id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |

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

    |  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;

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

    | id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    |  4 | zhuliu    | male   |  73 | 2014-07-01 | teacher   | NULL         |    3500.00 |    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 |

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

    18 rows in set (0.00 sec)


    小练习

    1  查询所有员工信息,先按照age升序排序,如果age相同则按照hire_date降序排序

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

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

    | id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    |  4 | zhuliu    | male   |  73 | 2014-07-01 | teacher   | NULL         |    3500.00 |    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 |

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

    18 rows in set (0.00 sec)


    2  查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

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

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

    | post      | avg(salary)   |

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

    | operation |  16800.026000 |

    | teacher   | 151842.901429 |

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

    2 rows in set (0.00 sec)


    3  查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 

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

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

    | post      | avg(salary)   |

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

    | teacher   | 151842.901429 |

    | operation |  16800.026000 |

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

    2 rows in set (0.00 sec)



    (5)limit  限制查询的记录数:

    示例:
        SELECT * FROM employee ORDER BY salary DESC 
         LIMIT 3;                    #默认初始位置为0 
    
        SELECT * FROM employee ORDER BY salary DESC
            LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
    
        SELECT * FROM employee ORDER BY salary DESC
            LIMIT 5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条


    小练习:

    分页显示,每页5条


    第一页数据

    mysql> select * from  employee limit 0,5;

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

    | id | name     | sex  | age | hire_date  | post      | post_comment | salary     | office | depart_id |

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

    |  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 |

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

    5 rows in set (0.00 sec)


    第二页数据

    mysql> select * from  employee limit 5,5;

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

    | id | name    | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |

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

    |  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.

关键字

上一篇: mysql-数据的增删改

下一篇: mysql-多表查询