MYSQL回顾（单表查询相关）

VV木公子

发布于 2020-02-18 17:14:03

17.1K0

发布于 2020-02-18 17:14:03

文章被收录于专栏：TechBoxTechBox

Let's embrace 2020 for realizing our dreams and living a better year. 更好2020，让梦想落地，让更好发生。

数据准备

建表

mysql> create table employee(

    -> id int primary key auto_increment,

    -> name char(40),

    -> age int default 18,

    -> sex enum("male", "female") not null default "male",

    -> position char(20),

    -> salary float default 3000,                                                                  

    -> dep_id int                                                                                 

    -> );                                                                                      

Query OK, 0 rows affected (0.03 sec)

mysql> desc employee;

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

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

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

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

| name     | char(40)              | YES  |     | NULL    |                |

| age      | int(11)               | YES  |     | 18      |                |

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

| position | char(20)              | YES  |     | NULL    |                |

| salary   | float                 | YES  |     | 3000    |                |

| dep_id   | int(11)               | YES  |     | NULL    |                |

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

7 rows in set (0.00 sec)

插入数据

mysql> insert into employee(name, age, sex, position, salary, dep_id) values

    -> ("jack", 20, "male", "lawyer", 888888.8, 3),

    -> ("mark", 22, "male", "lawyer", 888888.8, 3),

    -> ("hank", 25, "male", "lawyer", 7777.8, 3),

    -> ("nick", 39, "male", "lawyer", 4438888.8, 3),

    -> ("jenny", 26, "female", "lawyer", 10000.8, 3),

    -> ("tony", 35, "male", "RD", 99999999, 1),

    -> ("emmy", 27, "female", "RD", 9999, 1),

    -> ("emmy", 23, "female", "finance", 5000, 2),

    -> ("lucy", 45, "female", "finance", 10000, 2)

    -> ;

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0

Where 查询

mysql> select name from employee where age > 30;

+------+

| name |

+------+

| nick |

| tony |

| lucy |

+------+

3 rows in set (0.01 sec)

group by查询

mysql> select * from employee group by dep_id;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'alpha.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：因为group by分组之后不能访问分组字段之外的字段，所以以下的select * 会报错. 换成 select dep_id from employee group by dep_id;

但是又无意义，所以此时需要用到聚合函数或者group_concat()函数

聚合函数

聚合函数聚合的是组的内容，若是没有分组，则默认是一组。聚合函数有： count()：取个数 max()：取最大值 min()：取最小值 avg()：取平均值 sum()：求和例如：

    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;</pre>

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

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

| count(dep_id) | dep_id |

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

|             5 |      3 |

|             2 |      1 |

|             2 |      2 |

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

3 rows in set (0.00 sec)

如果觉得count(dep_id)展示不友好，可以使用as关键字给该字段起别名

mysql> select count(dep_id) as dep_id_count, dep_id from employee group by dep_id;

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

| dep_id_count | dep_id |

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

|            5 |      3 |

|            2 |      1 |

|            2 |      2 |

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

3 rows in set (0.00 sec)

Where + group by查询

mysql> select count(name), dep_id from employee where salary > 5000 group by dep_id;

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

| count(name) | dep_id |

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

|           5 |      3 |

|           2 |      1 |

|           1 |      2 |

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

3 rows in set (0.00 sec)

Having查询

查询各部门员工个数小于3的部门id、部门员工姓名、员工个数

mysql> select dep_id, group_concat(name), count(id) from employee group by dep_id having count(id) < 3;

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

| dep_id | group_concat(name) | count(id) |

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

|      1 | tony,emmy          |         2 |

|      2 | emmy,lucy          |         2 |

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

2 rows in set (0.00 sec)

查询各部门平均工资大于10000的部门id、部门平均工资

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

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

| group_concat(dep_id) | avg(salary)       |

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

| 1,1                  |        50004999.5 |

| 3,3,3,3,3            | 1246889.044921875 |

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

2 rows in set (0.00 sec)

查询各部门平局工资大于10000且小于10000000的部门id、部门平均工资

mysql> select group_concat(dep_id), avg(salary) from employee group by dep_id having avg(salary) > 10000 and avg(salary) < 10000000;

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

| group_concat(dep_id) | avg(salary)       |

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

| 3,3,3,3,3            | 1246889.044921875 |

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

1 row in set (0.00 sec)

HAVING与WHERE不一样的地方在于!!!!!!

！！！执行优先级从高到低：where > group by > having

1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

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

order by查询

排序分为升序ASC和降序DESC

mysql> select * from employee order by salary ASC;

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

| id | name  | age  | sex    | position | salary    | dep_id |

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

|  8 | emmy  |   23 | female | finance  |      5000 |      2 |

|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |

|  7 | emmy  |   27 | female | RD       |      9999 |      1 |

|  9 | lucy  |   45 | female | finance  |     10000 |      2 |

|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |

|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |

|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |

|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |

|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |

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

9 rows in set (0.00 sec)

mysql> select * from employee order by salary DESC;

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

| id | name  | age  | sex    | position | salary    | dep_id |

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

|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |

|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |

|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |

|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |

|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |

|  9 | lucy  |   45 | female | finance  |     10000 |      2 |

|  7 | emmy  |   27 | female | RD       |      9999 |      1 |

|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |

|  8 | emmy  |   23 | female | finance  |      5000 |      2 |

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

9 rows in set (0.00 sec)

查询所有员工信息，先按照部门id升序，如果id相同则按照年龄降序

mysql> select * from employee order by dep_id ASC, age DESC;

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

| id | name  | age  | sex    | position | salary    | dep_id |

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

|  6 | tony  |   35 | male   | RD       | 100000000 |      1 |

|  7 | emmy  |   27 | female | RD       |      9999 |      1 |

|  9 | lucy  |   45 | female | finance  |     10000 |      2 |

|  8 | emmy  |   23 | female | finance  |      5000 |      2 |

|  4 | nick  |   39 | male   | lawyer   |   4438890 |      3 |

|  5 | jenny |   26 | female | lawyer   |   10000.8 |      3 |

|  3 | hank  |   25 | male   | lawyer   |    7777.8 |      3 |

|  2 | mark  |   22 | male   | lawyer   |    888889 |      3 |

|  1 | jack  |   20 | male   | lawyer   |    888889 |      3 |

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

9 rows in set (0.00 sec)

查询各部门平均工资大于100000的部门id、平均工资，结果按平均工资升序

mysql> select dep_id, avg(salary) from employee group by dep_id having avg(salary) > 100000 order by avg(salary) ASC;

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

| dep_id | avg(salary)       |

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

|      3 | 1246889.044921875 |

|      1 |        50004999.5 |

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

2 rows in set (0.00 sec)

limit查询

limit限制查询的记录条数

查询工资大于10000的的前三名员工信息，并按降序排列

mysql> select * from employee where salary > 10000 order by salary DESC limit 3;

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

| id | name | age  | sex  | position | salary    | dep_id |

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

|  6 | tony |   35 | male | RD       | 100000000 |      1 |

|  4 | nick |   39 | male | lawyer   |   4438890 |      3 |

|  1 | jack |   20 | male | lawyer   |    888889 |      3 |

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

3 rows in set (0.00 sec)

limit分页查询

每页3条，查询第一页：

mysql> select * from employee order by salary limit 0, 3;

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

|  3 | hank |   25 | male   | lawyer   | 7777.8 |      3 |

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

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

3 rows in set (0.00 sec)

每页三条，查询第二页：

mysql> select * from employee order by salary limit 3, 3;

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

| id | name  | age  | sex    | position | salary  | dep_id |

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

|  9 | lucy  |   45 | female | finance  |   10000 |      2 |

|  5 | jenny |   26 | female | lawyer   | 10000.8 |      3 |

|  1 | jack  |   20 | male   | lawyer   |  888889 |      3 |

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

3 rows in set (0.01 sec)

每页3条，查询第三页：

mysql> select * from employee order by salary limit 6, 3;

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

| id | name | age  | sex  | position | salary    | dep_id |

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

|  2 | mark |   22 | male | lawyer   |    888889 |      3 |

|  4 | nick |   39 | male | lawyer   |   4438890 |      3 |

|  6 | tony |   35 | male | RD       | 100000000 |      1 |

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

3 rows in set (0.00 sec)

正则表达式查询

查询所有员工中以em开头的员工信息：

^代表开头

mysql> select * from employee where name REGEXP '^em';

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

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

2 rows in set (0.00 sec)

查询所有员工中以ck结尾的员工信息:

$代表结尾

mysql> select * from employee where name REGEXP 'ck$';

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

| id | name | age  | sex  | position | salary  | dep_id |

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

|  1 | jack |   20 | male | lawyer   |  888889 |      3 |

|  4 | nick |   39 | male | lawyer   | 4438890 |      3 |

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

2 rows in set (0.00 sec)

查询所有员工姓名包含2个连续m的员工信息：

mysql> SELECT * FROM employee WHERE name REGEXP 'm{2}';

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

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

2 rows in set (0.00 sec)

查询所有员工中姓名以emm开头且已y结尾的员工信息：

mysql> select * from employee where name regexp '^emm.*[y]$’;

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

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

2 rows in set (0.00 sec)

查询所有员工中姓名以emm开头且已i或y结尾的员工信息：

mysql> select * from employee where name regexp '^emm.*[iy]$';

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

| 10 | emmi |   20 | female | finance  |  20000 |      2 |

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

3 rows in set (0.00 sec)

另外还有一个模糊查询：like 但是like只有下划线_和百分号%

Like关键字模糊匹配姓名以emm开头的记录

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

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

| id | name | age  | sex    | position | salary | dep_id |

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

|  7 | emmy |   27 | female | RD       |   9999 |      1 |

|  8 | emmy |   23 | female | finance  |   5000 |      2 |

| 10 | emmi |   20 | female | finance  |  20000 |      2 |

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

3 rows in set (0.00 sec)