您的位置: 首页  >  IT文章  >  数据库开发——MySQL——单表查询 一、单表查询的语法 二、关键字的执行优先级(重点) 三、简单查询 四、where约束 五、分组查询 六、having 过滤 七、查询排序 order by 八、限制查询的记录数 limit 九、使用正则表达式查询

数据库开发——MySQL——单表查询 一、单表查询的语法 二、关键字的执行优先级(重点) 三、简单查询 四、where约束 五、分组查询 六、having 过滤 七、查询排序 order by 八、限制查询的记录数 limit 九、使用正则表达式查询

分类: IT文章 • 2024-04-28 13:44:11 
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
然后根据where指定的约束条件，去表中取出一条条记录

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

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

5.select
执行select

6.distinct
去重

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

8.limit
限制结果的显示条数

三、简单查询

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

创建表：

create table employee(
	id int not null unique 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);

查看表结构：

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.04 sec)

插入记录：

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
	('Alex0','male',78,'20150302','teacher',1000000,401,1),
	('Alex1','male',81,'20130305','teacher',8300,401,1),
	('Alex2','male',73,'20140701','teacher',3500,401,1),
	('Alex3','male',28,'20121101','teacher',2100,401,1),
	('Alex4','male',18,'20110211','teacher',9000,401,1),
	('Alex5','male',18,'19000301','teacher',30000,401,1),

	('Coco0','female',48,'20150311','sale',3000.13,402,2),
	('Coco1','female',38,'20101101','sale',2000.35,402,2),
	('Coco2','female',18,'20110312','sale',1000.37,402,2),
	('Coco3','female',18,'20160513','sale',3000.29,402,2),
	('Coco4','female',28,'20170127','sale',4000.33,402,2),
	
	('Bei0','male',28,'20160311','operation',10000.13,403,3), 
	('Bei1','male',18,'19970312','operation',20000,403,3),
	('Bei2','female',18,'20130311','operation',19000,403,3),
	('Bei3','male',18,'20150411','operation',18000,403,3),
	('Bei4','female',18,'20140512','operation',17000,403,3);

简单查询：

mysql> select id, name, sex, age, salary from employee;
+----+-------+--------+-----+------------+
| id | name  | sex    | age | salary     |
+----+-------+--------+-----+------------+
|  1 | Alex0 | male   |  78 | 1000000.00 |
|  2 | Alex1 | male   |  81 |    8300.00 |
|  3 | Alex2 | male   |  73 |    3500.00 |
|  4 | Alex3 | male   |  28 |    2100.00 |
|  5 | Alex4 | male   |  18 |    9000.00 |
|  6 | Alex5 | male   |  18 |   30000.00 |
|  7 | Coco0 | female |  48 |    3000.13 |
|  8 | Coco1 | female |  38 |    2000.35 |
|  9 | Coco2 | female |  18 |    1000.37 |
| 10 | Coco3 | female |  18 |    3000.29 |
| 11 | Coco4 | female |  28 |    4000.33 |
| 12 | Bei0  | male   |  28 |   10000.13 |
| 13 | Bei1  | male   |  18 |   20000.00 |
| 14 | Bei2  | female |  18 |   19000.00 |
| 15 | Bei3  | male   |  18 |   18000.00 |
| 16 | Bei4  | female |  18 |   17000.00 |
+----+-------+--------+-----+------------+
16 rows in set (0.00 sec)

避免重复distinct：

mysql> select distinct age from employee;
+-----+
| age |
+-----+
|  78 |
|  81 |
|  73 |
|  28 |
|  18 |
|  48 |
|  38 |
+-----+
7 rows in set (0.01 sec)

通过四则运算查询：

mysql> select name, salary * 12 as annual_salary from employee;
+-------+---------------+
| name  | annual_salary |
+-------+---------------+
| Alex0 |   12000000.00 |
| Alex1 |      99600.00 |
| Alex2 |      42000.00 |
| Alex3 |      25200.00 |
| Alex4 |     108000.00 |
| Alex5 |     360000.00 |
| Coco0 |      36001.56 |
| Coco1 |      24004.20 |
| Coco2 |      12004.44 |
| Coco3 |      36003.48 |
| Coco4 |      48003.96 |
| Bei0  |     120001.56 |
| Bei1  |     240000.00 |
| Bei2  |     228000.00 |
| Bei3  |     216000.00 |
| Bei4  |     204000.00 |
+-------+---------------+
16 rows in set (0.10 sec)

定义显示格式（concat函数）：

mysql> select concat("姓名：", name, " 年薪：", salary * 12) as annual_salary from employee;
+-------------------------------------+
| annual_salary                       |
+-------------------------------------+
| 姓名：Alex0 年薪：12000000.00       |
| 姓名：Alex1 年薪：99600.00          |
| 姓名：Alex2 年薪：42000.00          |
| 姓名：Alex3 年薪：25200.00          |
| 姓名：Alex4 年薪：108000.00         |
| 姓名：Alex5 年薪：360000.00         |
| 姓名：Coco0 年薪：36001.56          |
| 姓名：Coco1 年薪：24004.20          |
| 姓名：Coco2 年薪：12004.44          |
| 姓名：Coco3 年薪：36003.48          |
| 姓名：Coco4 年薪：48003.96          |
| 姓名：Bei0 年薪：120001.56          |
| 姓名：Bei1 年薪：240000.00          |
| 姓名：Bei2 年薪：228000.00          |
| 姓名：Bei3 年薪：216000.00          |
| 姓名：Bei4 年薪：204000.00          |
+-------------------------------------+
16 rows in set (0.00 sec)

四、where约束

where可以使用简单的表达式：

1.比较运算符：><>= <= <> !=

2.between 80 and 100 值在80到100之间

3.in(80,90,100) 值是80或90或100

4.like 'egon%'
	pattern可以是%或_，
		%表示任意多字符
		_表示一个字符

5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

1.单条件查询

mysql> select name from employee where post = "sale";
+-------+
| name  |
+-------+
| Coco0 |
| Coco1 |
| Coco2 |
| Coco3 |
| Coco4 |
+-------+
5 rows in set (0.00 sec)

2.多条件查询：

mysql> select name, salary from employee where post = "teacher" and salary > 10000;
+-------+------------+
| name  | salary     |
+-------+------------+
| Alex0 | 1000000.00 |
| Alex5 |   30000.00 |
+-------+------------+
2 rows in set (0.02 sec)

3.关键字between and：

mysql> select name, salary from employee where salary between 10000 and 20000;
+------+----------+
| name | salary   |
+------+----------+
| Bei0 | 10000.13 |
| Bei1 | 20000.00 |
| Bei2 | 19000.00 |
| Bei3 | 18000.00 |
| Bei4 | 17000.00 |
+------+----------+
5 rows in set (0.00 sec)

4.关键字is null(判断某个字段是否为null不能用等号，需要用is)：

mysql> select name, post from employee where post is null;
Empty set (0.00 sec)

mysql> select name, post from employee where post is not null;
+-------+-----------+
| name  | post      |
+-------+-----------+
| Alex0 | teacher   |
| Alex1 | teacher   |
| Alex2 | teacher   |
| Alex3 | teacher   |
| Alex4 | teacher   |
| Alex5 | teacher   |
| Coco0 | sale      |
| Coco1 | sale      |
| Coco2 | sale      |
| Coco3 | sale      |
| Coco4 | sale      |
| Bei0  | operation |
| Bei1  | operation |
| Bei2  | operation |
| Bei3  | operation |
| Bei4  | operation |
+-------+-----------+
16 rows in set (0.00 sec)

5.关键字in集合查询：

mysql> select name, salary from employee where salary in (2100, 3500, 4000, 5000, 9000);
+-------+---------+
| name  | salary  |
+-------+---------+
| Alex2 | 3500.00 |
| Alex3 | 2100.00 |
| Alex4 | 9000.00 |
+-------+---------+
3 rows in set (0.00 sec)

5.关键字like模糊查询：

mysql> select * from employee where name like "Al%";
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  1 | Alex0 | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.00 |    401 |         1 |
|  2 | Alex1 | male |  81 | 2013-03-05 | teacher | NULL         |    8300.00 |    401 |         1 |
|  3 | Alex2 | male |  73 | 2014-07-01 | teacher | NULL         |    3500.00 |    401 |         1 |
|  4 | Alex3 | male |  28 | 2012-11-01 | teacher | NULL         |    2100.00 |    401 |         1 |
|  5 | Alex4 | male |  18 | 2011-02-11 | teacher | NULL         |    9000.00 |    401 |         1 |
|  6 | Alex5 | male |  18 | 1900-03-01 | teacher | NULL         |   30000.00 |    401 |         1 |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
6 rows in set (0.37 sec)

mysql> select * from employee where name like "Al_";
Empty set (0.00 sec)

mysql> select * from employee where name like "Al__";
Empty set (0.00 sec)

mysql> select * from employee where name like "Al___";
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  1 | Alex0 | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.00 |    401 |         1 |
|  2 | Alex1 | male |  81 | 2013-03-05 | teacher | NULL         |    8300.00 |    401 |         1 |
|  3 | Alex2 | male |  73 | 2014-07-01 | teacher | NULL         |    3500.00 |    401 |         1 |
|  4 | Alex3 | male |  28 | 2012-11-01 | teacher | NULL         |    2100.00 |    401 |         1 |
|  5 | Alex4 | male |  18 | 2011-02-11 | teacher | NULL         |    9000.00 |    401 |         1 |
|  6 | Alex5 | male |  18 | 1900-03-01 | teacher | NULL         |   30000.00 |    401 |         1 |
+----+-------+------+-----+------------+---------+--------------+------------+--------+-----------+
6 rows in set (0.00 sec)

五、分组查询

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

分组指的是：将所有记录按照某个相同字段进行归类。

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

ONLY_FULL_GROUP_BY

查看sql_mode的默认配置：

mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.11 sec)

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

set global sql_mode='ONLY_FULL_GROUP_BY';

quit

重新启动MySQL。

mysql> select post, count(id) from employee group by post;
+-----------+-----------+
| post      | count(id) |
+-----------+-----------+
| teacher   |         6 |
| sale      |         5 |
| operation |         5 |
+-----------+-----------+
3 rows in set (0.02 sec)

GROUP BY

单独使用GROUP BY关键字分组

mysql> select post from employee group by post;
+-----------+
| post      |
+-----------+
| teacher   |
| sale      |
| operation |
+-----------+
3 rows in set (0.00 sec)

注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用

mysql> select post, group_concat(name) from employee group by post;
+-----------+-------------------------------------+
| post      | group_concat(name)                  |
+-----------+-------------------------------------+
| operation | Bei0,Bei1,Bei2,Bei3,Bei4            |
| sale      | Coco0,Coco1,Coco2,Coco3,Coco4       |
| teacher   | Alex0,Alex1,Alex2,Alex3,Alex4,Alex5 |
+-----------+-------------------------------------+
3 rows in set (0.00 sec)

GROUP BY与聚合函数一起使用

mysql> select post, count(id) as count from employee group by post;
+-----------+-------+
| post      | count |
+-----------+-------+
| teacher   |     6 |
| sale      |     5 |
| operation |     5 |
+-----------+-------+
3 rows in set (0.00 sec)

聚合函数

SELECT COUNT(*) FROM employee;		# 统计所有员工数量

SELECT MAX(salary) FROM employee;	# 筛选最高工资

SELECT MIN(salary) FROM employee;	# 筛选最低工资

SELECT AVG(salary) FROM employee;	# 计算平均工资

SELECT SUM(salary) FROM employee;	# 计算所有工资和

六、having 过滤

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

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

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

小练习

  1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select post, group_concat(name), count(id) from employee group by post having count(id) < 6;
+-----------+-------------------------------+-----------+
| post      | group_concat(name)            | count(id) |
+-----------+-------------------------------+-----------+
| operation | Bei0,Bei1,Bei2,Bei3,Bei4      |         5 |
| sale      | Coco0,Coco1,Coco2,Coco3,Coco4 |         5 |
+-----------+-------------------------------+-----------+
2 rows in set (0.00 sec)
  1. 查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post, avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 175483.333333 |
| operation |  16800.026000 |
+-----------+---------------+
2 rows in set (0.00 sec)
  1. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
+-----------+--------------+
| post      | avg(salary)  |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)

七、查询排序 order by

按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

八、限制查询的记录数 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条

九、使用正则表达式查询

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

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

相关推荐