专栏首页TechBoxMYSQL回顾(单表查询相关)

MYSQL回顾(单表查询相关)

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)

关键字执行顺序

重点中的重点:单表查询关键字的执行顺序(优先级)

from

where

group by

having

select

distinct

order by

limit

image.png

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MYSQL回顾(表关系相关)

    比如有两个表,分别是书籍表和出版社表。书籍和出版社是典型的多对一关系,即一本书只能由一个出版社出版,一个出版社可以出版多本书。则书籍表应该有一个外键press_...

    VV木公子
  • 3分钟实现iOS语言本地化/国际化(图文详解)前言配置需要国际化的语言(一)应用名称本地化/国际化(二)代码中字符串的本地化(三)多人开发情况下的字符串本地化(四)图片本地化(五)查看/切换本地语言

    VV木公子
  • 详解React Native渲染原理

    在《一篇文章详解React Native初始化和通信机制》中我们详细的介绍了React Native的初始化和通信机制。如果对通信机制不了的的读者可以先去阅读通...

    VV木公子
  • [spark] 数据本地化及延迟调度

    Spark数据本地化即移动计算而不是移动数据,而现实又是残酷的,不是想要在数据块的地方计算就有足够的资源提供,为了让task能尽可能的以最优本地化级别(Loca...

    UFO
  • 邮箱验证激活账号

    晚上没宵夜
  • 【DB笔试面试703】在Oracle中,怎么杀掉特定的数据库会话?

    “ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;”或者“ALTER SYSTEM DISCONNECT S...

    小麦苗DBA宝典
  • “半路出家”的Kaggle Grandmaster:如何正确打开数据科学竞赛?

    凭借这一成绩,Vladimir也荣获了Kaggle的最高荣誉——竞赛超级大师(Competitions Grandmaster)。Kaggle至今已成立8年,注...

    大数据文摘
  • “半路出家”的Kaggle大师:如何正确打开数据科学竞赛?

    凭借这一成绩,Vladimir也荣获了Kaggle的最高荣誉——竞赛超级大师(Competitions Grandmaster)。Kaggle至今已成立8年,注...

    用户2769421
  • Android浏览器跨域数据窃取和Intent Scheme攻击

    我们接下来要介绍的这个漏洞,其影响了Android版本4.4以下的自带浏览器和一些其他特定的Android浏览器,它允许黑客读取sqlite格式的cookie数...

    FB客服
  • 【刘文彬】区块链 + 大数据:EOS存储

    原文链接:醒者呆的博客园,https://www.cnblogs.com/Evsward/p/storage.html

    圆方圆学院

扫码关注云+社区

领取腾讯云代金券