纯干货——MySQL高级语句查询（有详细实验过程，可以尝试跟着做）

最新推荐文章于 2023-05-30 13:51:36 发布
最新推荐文章于 2023-05-30 13:51:36 发布
阅读量232 收藏 2
点赞数 1
分类专栏： MySQL数据库 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_48191100/article/details/109205345
版权

目录

前言

在对MySQL数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法
对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等，这些内容就是接下来要讲解的知识

一、MySQL 进阶查询

常用查询介绍

1.1 按关键字排序(ASC|DESC)

  • 使用ORDER BY语句来实现排序
  • 排序可针对一个或多个字段
    • ASC:升序，默认排序方式，可以省去不写
    • DESC:降序
    • 排序的字段可以根据具体需求进行选择，没有限制
  • ORDER BY语法结构
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

实验案例

create database player;    ###创建一张表
use player;
create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','抢宝真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白胡子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
insert into player (id,name,level) values ('199','D 丶狙击王',46);
insert into player (id,name,level) values ('298','唐三',46);
insert into player (id,name,level) values ('51','新五皇·暴雪',45);
insert into player (id,name,level) values ('272','D 丶抢人头辅助',45);

###查询等级大于等于 45 级的用户，并按降序进行排序
mysql> select id,name,level from player where level>=45 order by level desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 抢宝真多呀           |    47 |
|  15 | 新五皇·白胡子        |    46 |
|  63 | 新五皇–敬神          |    46 |
| 199 | D 丶狙击王           |    46 |
| 298 | 唐三                 |    46 |
|  51 | 新五皇·暴雪          |    45 |
| 272 | D 丶抢人头辅助       |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

对于排序要求，多数情况下一个字段就可以实现。其实 ORDER BY 语句也可以使用多个字段来进行排序，当排序的第一个字段相同的记录有多条的情况下，这些多条的记录再按 照第二个字段进行排序。

###查询等级在 45 级及以上的用户，并以 level 降序排列和 id 降序排列
mysql> select id,name,level from player where level>=45 order by level desc,id desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 抢宝真多呀           |    47 |
| 298 | 唐三                 |    46 |
| 199 | D 丶狙击王           |    46 |
|  63 | 新五皇–敬神          |    46 |
|  15 | 新五皇·白胡子        |    46 |
| 272 | D 丶抢人头辅助       |    45 |
|  51 | 新五皇·暴雪          |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

以上结果不难看出order by 后面第一个参数优先级高，level先进行倒叙排序，然后是id进行排序，并且level有一样的值的记录时，id是在level值一样的记录里进行倒叙排列。

ORDER BY 使用时要注意

  • ORDER BY 后面跟多个字段时，字段之间使用英文逗号隔开，优先级是按先后顺序而定。下面以A和B分别表示两个字段。
    • ORDER BY A,B desc 指A用升序，B用降序；
    • ORDER BY A asc,B desc 指A用升序，B用降序；
    • ORDER BY A desc,B desc 指A用降序，B用降序；

1.2 对结果进行分组(GROUP BY)

  • 使用 GROUP BY 语句来实现分组
  • 通常结合聚合函数一起使用,常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN）
  • GROUP BY除了配合聚合函数一起使用外，还可以引入WHERE子句
  • 可以按一个或多个字段对结果进行分组
  • GROUP BY的语法结构
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;

GROUP BY除了配合聚合函数一起使用外，还可以引入WHERE子句。首先通过WHERE过滤掉一部分不符合需求的查询结果，然后再对结果进行分组。如果有排序的需求， 也可以引入ORDER BY语句

实验案例

###统计等级在 45 级及以上，以等级为分组，每个等级有多少人
mysql> select count(name),level from player where level>=45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           2 |    45 |
|           4 |    46 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

从以上结果可以看出，每个等级的人数已经统计出来了，但是数量是无序的。如果结果条目变得更多，很难一眼看出哪个等级的人数是最多的，所以需要对数量进行排序。而GROUP BY 结合 ORDER BY即可实现分组并排序的查询

###询等级在 45 级及以上， 按等级进行分组，并将每个等级的人
数按降序排序
mysql> select count(name),level from player where level>=45 group by level order by count(name) desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           4 |    46 |
|           2 |    45 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

排序后的结果一目了然，方便用户获取用户最多的等级信息

1.3 限制结果条目(LIMIT)

  • 只返回SELECT查询结果的第一行或前几行
  • 使用LIMIT语句限制条目
  • LIMIT 语法结构
SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

LIMIT 的第一个参数是位置偏移量（可选参数），是设置 MySQL 从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是 0，第二条是 1，以此类推。第二个参数是设置返回记录行的最大数目。如果 SELECT 查询出的结果记录比较多，用户查看不是很方便。这个时候可以返回固定的、有限的记录数量，使用 MySQL 数据库的 LIMIT 子句即可实现。LIMIT 子句是一种简单的分页方法，它的使用减少了数据结果的返回时间，提高了执行效率，也解决了由于数据量过大从而导致的性能问题。例如，执行以下操作即可查询表的前 3 个用户的信息。

实验案例
环境创建(如果表已经创建，可以向表内插入明细数据)

create table kgc（
-> id int(4) not null primary key,
-> user_name char(10) not null,
-> level int(4) not null,
-> hobby int(4) not null);

insert into kgc (id,user_name,level,hobby) values ('1','zhangshan','88',1);
insert into kgc (id,user_name,level,hobby) values ('2','lisi','77',2);
insert into kgc (id,user_name,level,hobby) values ('3','wangwu','66',1);
insert into kgc (id,user_name,level,hobby) values ('4','zhaoliu','66',3);
insert into kgc (id,user_name,level,hobby) values ('5','tianqi','90',2);
insert into kgc (id,user_name,level,hobby) values ('6','wangba','60',2);

查询表的前 3 个用户的信息

mysql> select id,name,level from player limit 3;
+----+--------------+-------+
| id | name         | level |
+----+--------------+-------+
|  1 | 修欧拉卡     |    10 |
|  2 | 起风了       |    10 |
|  3 | 吊打低V      |    15 |
+----+--------------+-------+
3 rows in set (0.00 sec)

将查询记录按等级 level 降序排列，只取前三条记录

mysql> select id,name,level from player order by level desc limit 3;
+----+----------------------+-------+
| id | name                 | level |
+----+----------------------+-------+
| 30 | 抢宝真多呀           |    47 |
| 15 | 新五皇·白胡子        |    46 |
| 63 | 新五皇–敬神          |    46 |
+----+----------------------+-------+
3 rows in set (0.00 sec)

从第 3 条记录开始显示之后的 3 条数据

mysql> select id,name,level from player limit 2,3;
+----+------------+-------+
| id | name       | level |
+----+------------+-------+
|  3 | 吊打低V    |    15 |
|  4 | 小花       |    14 |
|  5 | 小舞       |    35 |
+----+------------+-------+
3 rows in set (0.00 sec)

1.4 设置别名(AS)

  • 使用AS语句设置别名，关键字AS可省略
  • 设置别名时，保证不能与库中其他表或字段名称冲突
  • 设置别名使用AS语法结构
SELECT column_name AS alias_name FROM table_name;

在MySQL查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，可以给字段列或表设置别名。使用的时候直使用别名，简洁明了，增强可读性。
在使用 AS 后，可以用 alias_name 代替 table_name，其中 AS 语句是可选的。AS之后的别名，主要是为表内的列或者表提供临时的名称，在查询过程中使用，库内实际的表名或字段名是不会被改变的
实验案例
在统计表内所有记录共有多少条时，使用 count（*）， 这么写不便于识别，可以将其别名设置为 number

mysql> select count(*) as number from player;
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) number from player;		###省略as是一样的结果
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

如果表的长度比较长，可以使用AS给表设置别名，在查询的过程中直接使用别名
将 player 表的别名设置成 p

mysql> select p.id,p.name from player as p limit 3;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 修欧拉卡     |
|  2 | 起风了       |
|  3 | 吊打低V      |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select p.id,p.name from player p limit 3;		###省略 as 是一样的结果
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 修欧拉卡     |
|  2 | 起风了       |
|  3 | 吊打低V      |
+----+--------------+
3 rows in set (0.00 sec)

此外，AS还可以作为连接语句的操作符
用一条 SQL语句完成在创建表tmp的时候将player表内的数据写入 tmp 表

mysql> create table tmp as select * from player;
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

注意

  • 在为表设置别名时，要保证别名不能与数据库中的其他表的名称冲突
  • 列的别名是在结果中有显示的，而表的别名在结果中没有显示，只在执行查询时使用

1.5 通配符（%、_）

  • 通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来
  • 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务
  • 常用的通配符
    • %：百分号表示零个、一个或多个字符
    • _：下划线表示单个字符
      实验案例
      创建环境
create table player (
id int(4) not null  ,
name varchar(10) not null,
level int(3) not null,
primary key (`id`));

insert into player (id,name,level) values ('238','sagou 轰总',7);
insert into player (id,name,level) values ('795','senoku',15);
insert into player (id,name,level) values ('2460','shirley',1);
insert into player (id,name,level) values ('448','useless',1);
insert into player (id,name,level) values ('713','guess',25);
insert into player (id,name,level) values ('1979','Theshy',24);
insert into player (id,name,level) values ('2237','leslieF',3);
insert into player (id,name,level) values ('1735','oooooo',1);
insert into player (id,name,level) values ('2718','ooo',1);

查询 player 表内 name 字段分别以 s 开头的名字、以 s 结尾的名字和名字中间包含 es 的字段

mysql> select id,name,level from player where name like 's%';		##name 字段以 s 开头的记录
+------+--------------+-------+
| id   | name         | level |
+------+--------------+-------+
|  238 | sagou 轰总   |     7 |
|  795 | senoku       |    15 |
| 2460 | shirley      |     1 |
+------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,level from player where name like '%s';		##name 字段以 s 结尾的记录
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
| 713 | guess   |    25 |
+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> select id,name,level from player where name like '%es%';	###name 字段中间含 es 的记录
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
|  448 | useless |     1 |
|  713 | guess   |    25 |
| 1979 | Theshy  |    24 |
| 2237 | leslieF |     3 |
+------+---------+-------+
4 rows in set (0.00 sec)

如果匹配name字段中某一个字符，可以使用%。但是MySQL提供的专门针对单个字符的通配符，就是“_”一个下划线，使用单个下划线可以替换字符串中的某个单字符
替换表内name字段开头的字符、结尾的字符或者中间的字符

mysql> select id,name,level from player where name like '_uess';	###替换开头的一个字符
+-----+-------+-------+
| id  | name  | level |
+-----+-------+-------+
| 713 | guess |    25 |
+-----+-------+-------+
1 row in set (0.00 sec)

mysql> select id,name,level from player where name like 'use____';		###替换结尾的四个字符
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select id,name,level from player where name like 'shi_ley';		###替换中间的一个字符
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2460 | shirley |     1 |
+------+---------+-------+
1 row in set (0.00 sec)

通配符“%”和“_”不仅可以单独使用，也可以组合使用
name 字段中，开头有一个字符，接着是es两个字符，后面再跟着零个、一个或多个字符，从player表中查询这样的数据

mysql> select id,name,level from player where name like '_es%';
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2237 | leslieF |     3 |
+------+---------+-------+
1 row in set (0.00 sec)

1.6 子查询（IN）

  • 也称作内查询或者嵌套查询
  • 先于主查询被执行，其结果将作为外层主查询的条件在增删改查中都可以使用子查询
  • 支持多层嵌套
  • IN语句是用来判断某个值是否在给定的结果集中

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
在开始实际的举例之前，先来学习一下IN 这个操作符的用法，IN用来判断某个值是否。在给定的结果集中，通常结合子查询来使用

<表达式> [NOT] IN <子查询>
  • 当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回 FALSE。若启用了 NOT 关键字，则返回值相反
  • 需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对
  • 多数情况下，子查询都是与SELECT语句一起使用的

实验案例
先查出等级大于等于45级的 ID，然后在判断player表内的ID是不是在这个结果集内，如果在就打印此行的名字和等级

mysql>  select name,level from player where id in (select id from player where level>=45);
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白胡子        |    46 |
| 抢宝真多呀           |    47 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙击王           |    46 |
| D 丶抢人头辅助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+
7 rows in set (0.00 sec)

从上例可以看到，子查询是被放到括号内的，这个括号是无法省略的，缺失则会报错，无法形成子查询
子查询内要查询的字段通常都是一个，查询后形成结果集供主查询使用
子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中
先清空之前使用的 tmp 表，然后通过子查询的方式将 player 的内容插入到 tmp 表中

mysql> truncate table tmp;					####清空 tmp 表
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tmp;
Empty set (0.00 sec)

mysql> insert into tmp select * from player where id in (select id from player);	####将player表的内容插入tmp 表
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> insert into tmp select * from player;			####可跟上面子查询 SQL 达到同样的效果

UPDATE语句也可以使用子查询。UPDATE内的子查询，在set更新内容时，可以是单独的一列，也可以是多列
通过子查询实现将等级大于等于 47 的用户减去 7

mysql> select id,name,level from tmp where id=30;		##ID 是 30 的用户等级是 47 级，最大级别
+----+-----------------+-------+
| id | name            | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀      |    47 |
+----+-----------------+-------+
1 row in set (0.00 sec)

mysql> update tmp set level = level - 7 where id in (select id from tmp where level >= 47);
ERROR 1093 (HY000): You can't specify target table 'tmp' for update in FROM clause

类似 select 方式的子查询，MySQL 不支持，需要多引入一层子查询

mysql> update tmp set level = level - 7 where id in (select a.id from (select id from tmp where level >= 47) a);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,name,level from tmp where id=30;
+----+-----------------+-------+
| id | name            | level |
+----+-----------------+-------+
| 30 | 抢宝真多呀      |    40 |		###可以看到等级已经少了 7 级
+----+-----------------+-------+
1 row in set (0.00 sec)

DELETE也适用于子查询
先将原来47级的用户等级恢复，然后通过子查询的方式将47级用户删除。实现方式类似 UPDATE 的子查询

mysql> update tmp set level=47 where id=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> delete from tmp where id in (select a.id from (select id from tmp where level=47) a);
Query OK, 1 row affected (0.01 sec)

mysql>select id,name,level from tmp where id=30;		###等级为 47 的用户已经被删除
Empty set (0.00 sec)

除了以上所列举的 IN 操作符，在IN前面还可以添加NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）
查询用户等级不小于 45 级的用户

mysql> select name,level from tmp where id not in (select id from tmp where level < 45); 
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白胡子        |    46 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙击王           |    46 |
| D 丶抢人头辅助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+
6 rows in set (0.01 sec)

子查询上也可以使用比较运算符（=、<、>、>=、<=），这些运算符主要是对运算符前面的表达式和后面的子查询进行比较运算
查询出名字是 shirley 的记录，并输出其 ID、名字和等级信息

mysql> select id,name,level from tmp where id = (select id from tmp where name='shirley');
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2460 | shirley |     1 |
+------+---------+-------+
1 row in set (0.00 sec)

EXIST 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回FALSE
先通过子查询判断返回是否为TRUE，如果用户shirley存在，则计算整个tmp表的总记录数量

mysql> select count(*) as number from tmp where EXISTS (select id from tmp where name='shirley'); 
+--------+
| number |
+--------+
|     18 |
+--------+
1 row in set (0.00 sec)

1.7 NULL值

  • 表示缺失的值
  • 与数字0或者空白(spaces)是不同的
  • 使用IS NULL或IS NOT NULL进行判断
  • NULL值和空值的区别
    • 空值长度为0，不占空间;NULL值的长度为NULL，占用空间
    • IS NULL无法判断空值
    • 空值使用“=”或者“<>”来处理
    • COUNT()计算时，NULL会忽略，空值会加入计算

实验案例
创建一个表test，该表包含不设置NOT NULL的字段，然后向表中插入不同的记录值，其中包括NULL值和实际有值的记录，
最后通过 SELECT 查询字段中包括 NULL 和不包括 NULL 的记录值

mysql>drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql>CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`level` int(10) NOT NULL,
`coin` int(32),
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into test(name,level) values('aa',10);			###插入的记录中不包括 coin 字段
Query OK, 1 row affected (0.01 sec)

mysql>insert into test(name,level,coin) values('ab',20,100);		###插入的记录中包括 coin 字段
Query OK, 1 row affected (0.00 sec)

mysql>select * from test where coin is null;			##查询 coin 字段为空值的记录
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+
|    1 | aa	|	10 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from test where coin is not null;			###查询coin 字段不为空的记录
+----+------+-------+------+
| id | NAME | level | coin |
+----+------+-------+------+

|    2 | ab	|	20 | 100 |
+----+------+-------+------+
1 row in set (0.00 sec)

1.8 正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。
MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式
支持的匹配模式
了解正则表达式的匹配规则之后，就可以将这些规则技巧应用于 SQL 语句中，从而可以更准确、更快速的查出所需的记录
实验案例
1.以特定字符串开头的记录
在player表中查询以us开头的name字段并打印对应的id、name 和 level 记录

mysql> select id,name,level from player where name REGEXP '^us';
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
+-----+---------+-------+
1 row in set (0.00 sec)

2.以特定字符串结尾的记录
在 player 表中查询以 ss 结尾的 name 字段并打印对应的 id、name 和 level 记录

mysql> select id,name,level from player where name REGEXP 'ss$';
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
| 713 | guess   |    25 |
+-----+---------+-------+
2 rows in set (0.00 sec)

3.包含指定字符串的记录##
在player表中查询包含ok字符串的name字段并打印对应的 id、name 和 level
记录

mysql> select id,name,level from player where name REGEXP 'ok';
+-----+--------+-------+
| id  | name   | level |
+-----+--------+-------+
| 795 | senoku |    15 |
+-----+--------+-------+
1 row in set (0.00 sec)

4.以“.”代替字符串中的任意一个字符的记录
在player表中查询包含字符串shir 与 ey，且两个字符串之间只有一个字符的name 字段并打印对应的 id、name 和 level 记录

mysql> select id,name,level from player where name REGEXP 'shir.ey';
+------+---------+-------+
| id   | name    | level |
+------+---------+-------+
| 2460 | shirley |     1 |
+------+---------+-------+
1 row in set (0.00 sec)

5.匹配包含或者关系的记录
在 player 表中查询包含字符串ok或者ss的name字段并打印对应的 id、name
和 level 记录

mysql> select id,name,level from player where name REGEXP 'ok|ss';
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
| 713 | guess   |    25 |
| 795 | senoku  |    15 |
+-----+---------+-------+
3 rows in set (0.00 sec)

6.“ * ”匹配前面字符的任意多次
在 player 表中查询包含三个或者更多个连续的 o 的 name 字段并打印对应的 id、name 和 level 记录

mysql> select id,name,level from player where name REGEXP 'oooo*';
+------+--------+-------+
| id   | name   | level |
+------+--------+-------+
| 1735 | oooooo |     1 |
| 2718 | ooo    |     1 |
+------+--------+-------+
2 rows in set (0.00 sec)

7.“+”匹配前面字符至少一次
在player表中查询包含四个或者更多个o的 name 字段并打印对应的 id、name和 level 记录

mysql> select id,name,level from player where name REGEXP 'oooo+';
+------+--------+-------+
| id   | name   | level |
+------+--------+-------+
| 1735 | oooooo |     1 |
+------+--------+-------+
1 row in set (0.00 sec)

8.匹配指定字符集中的任意一个
在 player 表中查询包含以 d、e、f 开头的 name 字段并打印对应的 id、name 和level 记录

mysql> select id,name,level from player where name REGEXP '^[d-f]';
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
| 199 | D 丶狙击王           |    46 |
| 272 | D 丶抢人头辅助       |    45 |
+-----+----------------------+-------+
2 rows in set (0.00 sec)

1.9 运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种，分别是：算术运算符、比较运算符、逻辑运算符和位运算符

运算符描述
+加法
-减法
*乘法
/除法
%取余数

实验案例
以 SELECT 命令来实现最基础的加减乘除运算

mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;
+----------+-------------+----------------+----------+-----------+
| addition | subtraction | multiplication | division | remainder |
+----------+-------------+----------------+----------+-----------+
|        3 |           1 |              6 |   2.0000 |         1 |
+----------+-------------+----------------+----------+-----------+
1 row in set (0.00 sec)

注意

  • 在除法运算和求余数运算中，除数不能为 0，若除数是 0，返回的结果则为 NULL。
  • 需要注意的是，如果有多个运算符，按照先乘除后加减的优先级进行运算，相同优先级的运算 符没有先后顺序。
  • 在 MySQL 的字段值内还有一种情况：某些字符串类型的字段存储的数字型字符串，这些字段在进行算术运算时将会被自动转换为数字的值。
  • 如果字符串的开始部分是数字，在转 换时将被转换为这个数字。
  • 如果是既包含字符又包含数字得的混合字符串，无法转换为数字时，将被转换为 0

这些细节在进行算术运算时需多加注意

1.10 比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，如果要区分大小写可以通过 binary 关键字来实现

运算符描述运算符描述
=等于IS NOT NULL判断一个值是否不为 NULL
>大于BETWEEN AND两者之间
<小于IN在集合中
>=大于等于LIKE通配符匹配
<=小于等于GREATEST两个或多个参数时返回最大值
!=或<>不等于LEAST两个或多个参数时返回最小值
IS NULL判断一个值是否为 NULLREGEXP正则表达式

实验案例
（1）等于运算符

  • 等号（=）是用来判断数字、字符串和表达式是否相等的，如果相等则返回1，如果不相等则返回0
  • 如果比较的两者有一个值是 NULL，则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的
  • 如果 ASCII 码相等，则表示两个字符相同；如果ASCII码不相等，则表示两个字符不相同

等于运算符在数字、字符串和表达式上的使用

mysql> select 2=4,2='2','e'='e',(2+2)=(3+1),'r'=NULL;
+-----+-------+---------+-------------+----------+
| 2=4 | 2='2' | 'e'='e' | (2+2)=(3+1) | 'r'=NULL |
+-----+-------+---------+-------------+----------+
|   0 |     1 |       1 |           1 |     NULL |
+-----+-------+---------+-------------+----------+
1 row in set (0.00 sec)

从以上查询可以看出来：

  • 如果两者都是整数，则按照整数值进行比较
  • 如果一个整数一个字符串，则会自动将字符串转换为数字，再进行比较
  • 如果两者都是字符串，则按照字符串进行比较
  • 如果两者中至少有一个值是 NULL，则比较的结果是 NULL

（2）不等于运算符

  • 不等于号有两种写法，分别是<>或者!=，用于针对数字、字符串和表达式不相等的比较
  • 如果不相等则返回 1，如果相等则返回 0，这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL

关于数字、字符串和表达式的不等于运算符的使用

mysql> SELECT 'kgc'<>'bdqn', 1<>2, 3!=3, 2.5!=2, NULL<>NULL ;
+---------------+------+------+--------+------------+
| 'kgc'<>'bdqn' | 1<>2 | 3!=3 | 2.5!=2 | NULL<>NULL |
+---------------+------+------+--------+------------+
|             1 |    1 |    0 |      1 |       NULL |
+---------------+------+------+--------+------------+
1 row in set (0.00 sec)

（3）大于、大于等于、小于、小于等于运算符

  • 大于（>）运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回 1，否则返回 0，同样不能用于判断 NULL
  • 小于（<）运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回 1，否则返回 0，同样不能用于判断 NULL
  • 大于等于（>=）判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回 1，否则返回 0，不能用于判断 NULL
  • 小于等于（<=）判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回 1，否则返回 0，不能用于判断 NULL

关于大于、大于等于、小于、小于等于运算符的使用

mysql> select 5>4,'a'>'b',2>=3,(2+3)>=(1+2),4.4<3,1<2,'x'<='y',5<=5.5,'u'>=NULL;
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
| 5>4 | 'a'>'b' | 2>=3 | (2+3)>=(1+2) | 4.4<3 | 1<2 | 'x'<='y' | 5<=5.5 | 'u'>=NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
|   1 |       0 |    0 |            1 |     0 |   1 |        1 |      1 |      NULL |
+-----+---------+------+--------------+-------+-----+----------+--------+-----------+
1 row in set (0.00 sec)

（4）IS NULL、IS NOT NULL

  • IS NULL 判断一个值是否为 NULL，如果为 NULL 返回 1，否则返回 0
  • IS NOT NULL 判断一个值是否不为 NULL，如果不为 NULL 返回 1，否则返回 0

关于数字、字符和 NULL 值的运用

mysql> select 2 IS NULL,'f' IS NOT NULL,NULL IS NULL;
+-----------+-----------------+--------------+
| 2 IS NULL | 'f' IS NOT NULL | NULL IS NULL |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

IS NULL 和 IS NOT NULL 一个判断为空，另一个判断不为空，只是有无 NOT 这个关键字的区别，同时返回值不同
（5）BETWEEN AND
BETWEEN AND 比较运算通常用于判断一个值是否落在某两个值之间。例如，判断某数字是否在另外两个数字之间，也可以判断某英文字母是否在另外两个字母之间

mysql> select 4 BETWEEN 2 AND 6,5 BETWEEN 6 AND 8,'c' BETWEEN 'a' AND 'f';
+-------------------+-------------------+-------------------------+
| 4 BETWEEN 2 AND 6 | 5 BETWEEN 6 AND 8 | 'c' BETWEEN 'a' AND 'f' |
+-------------------+-------------------+-------------------------+
|                 1 |                 0 |                       1 |
+-------------------+-------------------+-------------------------+
1 row in set (0.00 sec)

（6）LEAST、GREATEST

  • LEAST：当有两个或者多个参数时，返回其中的最小值。如果其中一个值为 NULL，则返回结果就为 NULL
  • GREATEST：当有两个或者多个参数时，返回其中的最大值。如果其中一个值为 NULL， 则返回结果就为 NULL

若要判断一组数字或字母中哪个最小、哪个最大，可以通过使用 LEAST 和GREATEST 来实现

mysql> SELECT least(1,2,3),least('a','b','c'),greatest(1,2,3),greatest('a','b','c');
+--------------+--------------------+-----------------+-----------------------+
| least(1,2,3) | least('a','b','c') | greatest(1,2,3) | greatest('a','b','c') |
+--------------+--------------------+-----------------+-----------------------+
|            1 | a                  |               3 | c                     |
+--------------+--------------------+-----------------+-----------------------+
1 row in set (0.00 sec)

从以上结果可以看出

  • LEAST 比较的参数为数字时，返回的是其中最小的值；当比较的参数为字符串时，返回字母表中顺序最靠前的字符
  • GREATEST 比较的参数为数字时， 返回的是其中最大的值；当比较的参数为字符串时，返回字母表中顺序最靠后的字符

（7）IN、NOT IN

  • IN 判断一个值是否在对应的列表中，如果是返回 1，否则返回 0
  • NOT IN 判断一个值是否不在对应的列表中，如果不是返回 1，否则返回 0

判断某数字是否在一组数字中，也可判断某字符是否在一组字符中

mysql> SELECT 2 in (1,2,3,4,5),'c' not in ('a','b','c');
+------------------+--------------------------+
| 2 in (1,2,3,4,5) | 'c' not in ('a','b','c') |
+------------------+--------------------------+
|                1 |                        0 |
+------------------+--------------------------+
1 row in set (0.00 sec)

从以上结果可以看出，IN 和 NOT IN 的返回值正好相反
（8）LIKE、NOT LIKE

  • LIKE 用来匹配字符串，如果匹配成功则返回 1，反之返回 0。
  • LIKE 支持两种通配符：’%’ 用于匹配任意数目的字符，而’_’只能匹配一个字符。
  • NOT LIKE 正好跟 LIKE 相反，如果没有匹配成功则返回 1，反之返回 0

若要判断某字符串能否匹配成功，分单字符匹配和多字符匹配，也可以判断不匹配

mysql> SELECT 'bdqn' LIKE 'bdq_','kgc' LIKE '%c','etc' NOT LIKE '%th';
+--------------------+-----------------+----------------------+
| 'bdqn' LIKE 'bdq_' | 'kgc' LIKE '%c' | 'etc' NOT LIKE '%th' |
+--------------------+-----------------+----------------------+
|                  1 |               1 |                    1 |
+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)

1.11 逻辑运算符

逻辑运算符又被称为布尔运算符，通常用来判断表达式的真假，如果为真返回 1，否则返回 0，真和假也可以用 TRUE 和 FALSE 表示
MySQL 中支持使用的逻辑运算符有四种

运算符描述
NOT 或 ！逻辑非
AND 或 &&逻辑与
OR 或
XOR逻辑异或

（1）逻辑非

  • 逻辑运算符中最简单的运算符就是逻辑非，逻辑非使用 NOT 或！表示
  • 逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真
  • 如果 NOT 后面的操作数为 0 时，所得值为 1
  • 如果操作数为非 0 时，所得值为 0
  • 如果操作数为 NULL 时，所得值为 NULL

对非 0 值和 0 值分别作逻辑非运算

mysql>SELECT not 2,!3,not 0,!(4-4);
+-------+----+-------+--------+
| not 2 | !3 | not 0 | !(4-4) |
+-------+----+-------+--------+
|	0 |   0 |	1 |	1 |
+-------+----+-------+--------+
1 row in set (0.00 sec)

（2）逻辑与

  • 逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0
  • 逻辑与使用 AND 或者&&表示
    对非 0 值、0 值和 NULL 值分别作逻辑与运算
mysql> SELECT 2 AND 3,4 && 0,0 && NULL,1 AND NULL;
+---------+--------+-----------+------------+
| 2 AND 3 | 4 && 0 | 0 && NULL | 1 AND NULL |
+---------+--------+-----------+------------+
|       1 |      0 |         0 |       NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)

（3）逻辑或
逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0
逻辑或通常使用 OR 或者||来表示,但是建议使用OR不使用||，因为使用||会在某些特定情况下运算错误
对非 0 值、0 值和 NULL 值分别作逻辑或运算

mysql>SELECT 2 OR 3,4 || 0,0 OR NULL,1 || NULL;		###有问题
+--------+--------+-----------+-----------+
| 2 OR 3 | 4 || 0 | 0 OR NULL | 1 || NULL |
+--------+--------+-----------+-----------+
|	1 |	1 |	NULL |	1 |
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

（4）逻辑异或
两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；当任意一个值为 NULL 时，返回值为 NULL
对非0 值、0 值和 NULL 值分别作逻辑异或运算

mysql>SELECT 2 XOR 3,0 XOR 0,0 XOR 5,1 XOR NULL,NULL XOR NULL;
+---------+---------+---------+------------+---------------+
| 2 XOR 3 | 0 XOR 0 | 0 XOR 5 | 1 XOR NULL | NULL XOR NULL |
+---------+---------+---------+------------+---------------+
|	0 |	0 |	1 |	NULL |	NULL |
+---------+---------+---------+------------+---------------+
1 row in set (0.00 sec)

1.12 位运算符

  • 位运算符实际上是对二进制数进行计算的运算符
  • MySQL 内位运算会先将操作数变成二进制格式，然后进行位运算，最后在将计算结果从二进制变回到十进制格式，方便用户查看
  • MySQL 支持 6 种位运算符
运算符描述
&按位与
按位或
~按位取反
^按位异或
<<按位左移
>>按位右移

对数字进行按位与、或和取反运算

mysql> SELECT 10 & 15, 10 | 15, 10 ^ 15, 5 &~1;
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
|      10 |      15 |       5 |     4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)

10 转换为二进制数是 1010, 15 转换为二进制数是 1111
按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0，所以 10 & 15 的结果为 10
按位或运算（|），是对应的二进制位有一个或两个为 1 的，运算结果为 1，否则为 0， 所以 10 | 15 的结果为 15
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0，所以 10 ^ 15的结果为 5
按位取反（~），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001，取反后变为 1110, 数字 5 的二进制是 0101，将 1110 和 0101进行求与操作，其结果是二进制的 0100，转换为十进制就是 4

mysql> SELECT 1<<2, 2<<2,10>>2,15>>2;
+------+------+-------+-------+
| 1<<2 | 2<<2 | 10>>2 | 15>>2 |
+------+------+-------+-------+
|    4 |    8 |     2 |     3 |
+------+------+-------+-------+
1 row in set (0.00 sec)

左移或右移运算符，都是将数转换为二进制后，然后在左移或右移指定的位数，超出的位数将被移除并丢弃，空出来的位置用 0 补齐
“2<<2”将数字 2 的二进制数 0010， 向左移动两位后变成 10，右侧用 00 补齐，最终变为二进制的 1000，转换为十进制是 8
“15>>2”将数字 15 转换为二进制是 1111，向右移动两位，右侧的两位 11 被丢弃，变为 11， 左侧用 00 补齐，最终变为二进制的 0011，转换为十进制就是 3

以上不管哪种运算符，在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算，如果运算符的级别相同， MySQL 会按照顺序从左到右依次进行计算。如果不确定所使用的运算符的优先级，可以使用（）改变优先级
常用的运算符优先级情况
常用的运算符优先级情况

1.13 连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接
首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：

  • 内连接
  • 左连接
  • 右连接

环境设置

CREATE TABLE `a_player` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(32) DEFAULT NULL,
`a_level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `b_player` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(32) DEFAULT NULL,
`b_level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into a_player(a_id, a_name, a_level) values(1, 'aaaa', 10); insert into a_player(a_id, a_name, a_level) values(2, 'bbbb', 20); insert into a_player(a_id, a_name, a_level) values(3, 'cccc', 30); insert into a_player(a_id, a_name, a_level) values(4, 'dddd', 40);

insert into b_player(b_id, b_name, b_level) values(2, 'bbbb', 20); insert into b_player(b_id, b_name, b_level) values(3, 'cccc', 30); insert into b_player(b_id, b_name, b_level) values(5, 'eeee', 50); insert into b_player(b_id, b_name, b_level) values(6, 'ffff', 60);

1.13.1 内连接

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在FROM 子句中使用关键字 INNER JOIN 来连接多张表，
并使用 ON 子句设置连接条件
语法格式

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。例如，在刚才创建的 a_player 和 b_player 表中使用内连接查询出通过判断 a_id 和 b_id 相等，包含在两个表内的部分

mysql>select a_id,a_name,a_level from a_player inner join b_player on a_id=b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|	2 | bbbb	|	20 |
|	3 | cccc	|	30 |
+------+--------+---------+
2rows in set (0.00 sec)

上述操作是将 a_player 表的每一行与 b_player 表的每一行进行比较，以检查它们是否都满足条件。当满足条件时，INNER JOIN 将返回由 a_player 表和 b_player 表中的列组成的新行。如果没有匹配项，查询将返回一个空的结果集。INNER JOIN 结果集中的行必须出现在两个表 a_player 和 b_player 中，是这两个表的交叉部分，具体的 INNER JOIN 工作原理

如果表中至少有一个匹配项，INNER JOIN 关键字将返回一行。如果没有匹配项，则不会有输出结果

1.13.2.左连接

MySQL 除了内连接，还可以使用外连接
区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录
外连接按照连接时表的顺序来分，有左连接和右连接之分
左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示
左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行
实验案例
从a_player 和 b_player 表中，查询出 a_player 表中所有内容，并且查询出通过 a_id 和 b_id 相等判断出的 b_player 中的部分

mysql> select * from a_player a left join b_player b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

从以上左连接查询结果可以看出，除了匹配的行，a_id 和 b_id 是 2 和 3 那两行，还包括了左表中有但右表中没有的行，如果有表没有对应值，则使用 NULL 代替。也就是说在左连接查询中，使用 NULL 值表示右表中没有找到与左表中匹配的记录
从原理图可以看出，左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索 条件的记录，也就是图中交叉的部分。右表记录不足的地方均为 NULL

1.13.3 右连接

右连接也被称为右外连接
在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示
右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录
实验案例
从 a_player 和 b_player 表中，查询出在 b_player 表内的所有记录，并且通过判断 a_id 和 b_id 相等，在 a_player 表内的部分

mysql> select * from a_player a right join b_player b on a.a_id=b.b_id;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
4 rows in set (0.00 sec)

在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹 配的行，这些记录在左表中以 NULL 补足

1.14 数据库函数

MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数

1.14.1 数学函数

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数

常用的数学函数如表

数学函数描述
abs(x)返回 x 的绝对值
rand()返回 0 到 1 的随机数
mod(x,y)返回 x 除以 y 以后的余数
power(x,y)返回 x 的 y 次方
round(x)返回离 x 最近的整数
round(x,y)保留x 的y 位小数四舍五入后的值
sqrt(x)返回 x 的平方根
truncate(x,y)返回数字 x 截断为 y 位小数的值
ceil(x)返回大于或等于 x 的最小整数
floor(x)返回小于或等于 x 的最大整数
greatest(x1,x2…)返回集合中最大的值
least(x1,x2…)返回集合中最小的值

MySQL 数学函数的使用方法

mysql>select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
+---------+---------------------+----------+------------+-------------+
| abs(-1) | rand()	| mod(5,3) | power(2,3) | round(1.89) |
+---------+---------------------+----------+------------+-------------+
|	1 | 0.46176527949214474 |	2 |	8 |	2 |
+---------+---------------------+----------+------------+-------------+
1 row in set (0.00 sec)

mysql>select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);
+-----------------+-------------------+-----------+------------+-----------------------+
| round(1.8937,3) | truncate(1.235,2) | ceil(5.2) | floor(2.1) | least(1.89,3,6.1,2.1) |
+-----------------+-------------------+-----------+------------+-----------------------+
|	1.894 |	1.23 |	6 |	2 |	1.89 |
+-----------------+-------------------+-----------+------------+-----------------------+
1 row in set (0.00 sec)

1.14.2 聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数

常见的聚合函数

聚合函数描述
avg()返回指定列的平均值
count()返回指定列中非 NULL 值的个数
min()返回指定列的最小值
max()返回指定列的最大值
sum(x)返回指定列的所有值之和

MySQL 聚合函数的使用方法

mysql> select sum(level) as sum_level from player;
+-----------+
| sum_level |
+-----------+
|       483 |
+-----------+
1 row in set (0.00 sec)

mysql>select max(level) as max_level from player;
+-----------+
| max_level |
+-----------+
|	47 |
+-----------+
1 row in set (0.00 sec)

mysql>select min(level) as min_level from player;
+-----------+
| min_level |
+-----------+
|	1 |
+-----------+
1 row in set (0.00 sec)

聚合函数中最常用到的是 count()函数，用于统计表中的总记录数

1.14.3 字符串函数

常用函数不仅包括数学函数和聚合函数，还包含字符串函数，MySQL 为字符串的相关操作设计了丰富的字符串函数

常用的字符串函数

字符串函数描述
length(x)返回字符串 x 的长度
trim()返回去除指定格式的值
concat(x,y)将提供的参数 x 和 y 拼接成一个字符串
upper(x)将字符串 x 的所有字母变成大写字母
lower(x)将字符串 x 的所有字母变成小写字母
left(x,y)返回字符串 x 的前 y 个字符
right(x,y)返回字符串 x 的后 y 个字符
repeat(x,y)将字符串 x 重复 y 次
space(x)返回 x 个空格
replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 y
strcmp(x,y)比较 x 和 y，返回的值可以为-1,0,1
substring(x,y,z)获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
reverse(x)将字符串 x 反转

MySQL 字符串函数的使用方法

mysql>select length('bdqn'), trim(' yellow '), concat('bd', 'qn'), upper('abc'), right('hello', 3);
+----------------+------------------+--------------------+--------------+-------------------+
| length('bdqn') | trim(' yellow ') | concat('bd', 'qn') | upper('abc') | right('hello', 3) |
+----------------+------------------+--------------------+--------------+-------------------+
|	4 | yellow	| bdqn	| ABC	| llo
|
+----------------+------------------+--------------------+--------------+-------------------+
1 row in set (0.00 sec)

mysql>select repeat('kgc', 2), replace('hello', 'll', 'kgc'), strcmp(4, 5), substring('bjbdqn', 4, 2), reverse('hello');
+------------------+-------------------------------+--------------+---------------------------+------------------+
| repeat('kgc', 2) | replace('hello', 'll', 'kgc') | strcmp(4, 5) | substring('bjbdqn', 4, 2) | reverse('hello') |
+------------------+-------------------------------+--------------+---------------------------+------------------+
| kgckgc	| hekgco	|	-1 | dq
| olleh	|
+------------------+-------------------------------+--------------+---------------------------+------------------+
1 row in set (0.00 sec)

1.14.4 日期时间函数

MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数
常用的日期时间函数

字符串函数描述
curdate()返回当前时间的年月日
curtime()返回当前时间的时分秒
now()返回当前时间的日期和时间
month(x)返回日期 x 中的月份值
week(x)返回日期 x 是年度第几个星期
hour(x)返回 x 中的小时值
minute(x)返回 x 中的分钟值
second(x)返回 x 中的秒钟值
dayofweek(x)返回 x 是星期几，1 星期日，2 星期一
dayofmonth(x)计算日期 x 是本月的第几天
dayofyear(x)计算日期 x 是本年的第几天

MySQL 日期时间函数的使用方法

mysql>select curdate(),curtime(),now(),month('2020-02-09'),	week('2020-02-09'), hour('21:13:53');
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| curdate()	|  curtime() | now()	| month('2020-02-09') | week('2020-02-09') | hour('21:13:53') |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
| 2020-02-09 | 21:14:34    | 2020-02-09 21:14:34 |	2 |	6 |
21 |
+------------+-----------+---------------------+---------------------+--------------------+------------------+
1 row in set (0.00 sec)

mysql>select minute('21:13:53'),second('21:13:53'),	dayofweek('2020-02-09'), dayofmonth('2020-02-09'), dayofyear('2020-02-09');
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
| minute('21:13:53') | second('21:13:53') | dayofweek('2020-02-09') | dayofmonth('2020-02-09') | dayofyear('2020-02-09') |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
|	13 |	53 |	1 |
9 |	40 |
+--------------------+--------------------+-------------------------+--------------------------+-------------------------+
1 row in set (0.00 sec)

因为 MySQL 函数的数量比较多，存在很多使用频率不是很高的函数，所以本章仅列举了一些具有代表性、比较常用的函数。在实际的工作中，需要什么类型和功能的函数可以通 过手册去查找，了解实际功能后再使用

1.15 存储过程

1.15.1 存储过程简介

  • 是一组为了完成特定功能的SQL语句集合
  • 比传统SQL速度更快、执行效率更高
  • 存储过程的优点
    • 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
    • SQL语句加上控制语句的集合，灵活性高
    • 在服务器端存储，客户端调用时，降低网络负载
    • 可多次重复被调用，可随时修改，不影响客户端调用
    • 可完成所有的数据库操作，也可控制数据库的信息访问权限

1.15.2 创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名><类型>

参数分为:

  • 输入参数:IN(输入参数可以传递给一个存储过程)

  • 输出参数:OUT(输出参数用于存储过程需要返回一个操作结果的情形)

  • 输入/输出参数: INOUT(输入/输出参数既可以充当输入参数也可以充当输出参数)

  • 存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会发生错误

  • 存储过程可以添加参数，具有自己的参数列表。参数包括参数名和其对应的类型

  • 存在多个参数时，参数列表之间用逗号进行分隔。创建存储过程的时候可以不使用参数，但是括号要存在，也可以有一个或多个参数

  • 在存储过程的创建过程中，会用到 DELIMITER 命令。因为在 MySQL 中，服务器处理SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后面都是分号结尾，那么 MySQL 服务器在处理时遇到第一条 SQL 语句就会结束整个过程， 不再去处理后面的 SQL 语句

在创建存储过程时，使用 DELIMITER 命令

mysql> DELIMITER $$
//省略存储过程其他步骤
mysql> DELIMITER ;	//分号前有空格

要创建存储过程，必须要具有 CREATE ROUTINE 权限
使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程
如果要查看某个存储过程的具体信息，则可以使用SHOW CREATE PROCEDURE <存储过程名称>
通过存储过程查询 player 表的三条数据，存储过程是不带参数的

mysql> DELIMITER $$
mysql> 
CREATE PROCEDURE PlayerRole()
BEGIN
SELECT id,name,level from player limit 3;
END $$

mysql> DELIMITER ;
mysql> call PlayerRole();
+----+--------------+-------+
| id | name	| level |
+----+--------------+-------+
|   1 | 修欧拉卡	|	10 |
|   2 | 起 风了	|	10 |
|   3 | 吊打低V	|	15 |
+----+--------------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

通过存储过程查询 player 表中某一条记录，存储过程是带参数的

mysql>DELIMITER $$
CREATE PROCEDURE GetRole(IN inname VARCHAR(16))
BEGIN
SELECT id,name,level from player where name=inname;
END $$

mysql>DELIMITER ;
mysql>call GetRole('shirley');
+------+---------+-------+
| id	| name	| level |
+------+---------+-------+
| 2460 | shirley |	1 |
+------+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

1.15.3 修改存储过程

  • 存储过程在创建之后，随着开发业务的不断推进，业务需求难免有所调整，相应的存储 过程也会发生变动，这个时候就需要修改存储过程
  • 存储过程的修改分为特征的修改和业务内容的修改
  • 特征的修改可以使用 ALTER PROCEDURE 来实现
    语法结构
ALTER PROCEDURE <过程名> [ <特征> … ]

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储 过程

1.15.4 删除存储过程

存储过程创建之时是存储到 MySQL 数据库中的，当程序不在调用这个存储过程时，也就意味这个存储过程被废弃了，废弃的存储过程需要从数据库中将其删除
使用 DROP PROCEDURE 语句即可删除存储过程
语法格式

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

从以上语法结构可以看出，在删除时存储过程的名字是放到最后的，前面可以添加 IF EXISTS 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误

mysql>DROP PROCEDURE PlayerRole; 
Query OK, 0 rows affected (0.00 sec) 

mysql>CALL PlayerRole;
ERROR 1305 (42000): PROCEDURE test.PlayerRole does not exist

需要注意的是：

  • 存储过程名称后面没有参数列表，也没有括号
  • 在删除之前，必须确认 该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行
唐门中单申请出战
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
进销存数据库表设计
06-01
NULL 博文链接：https://zhouhaitao.iteye.com/blog/2113955
SQL语法基础+高级.PDF（纯干货）
07-14
SQL，全称Structured Query ...这只是SQL语法的冰山一角，还有JOIN操作、GROUP BY和HAVING子句、子查询、存储过程、触发器等更高级的概念需要深入学习。理解并熟练运用这些知识，将使你在处理数据库时更加得心应手。
PHP&MySQL基础（三）:处理查询SQL返回的结果集
欢迎来到长风沛雨的博客
05-30 1133
PHP查询SQL的返回结果处理
想把数据库玩的溜，离不开mysql高级语句查询，带你进入不一样的高度！！！
weixin_48191039的博客
10-20 3905
MySQL 进阶查询 在对MySQL数据库的增、删、改、查操作有一定了解之后，就可以学习一些 SQL 语句的高级使用方法。 SQL语句的熟练使用，在平时的运维工作中可以提供不小的帮助，尤其是在一些规模较小的公司，运维身兼数职，可能会有不少数据库的相关工作。本章将从不 同的方面出发介绍 SQL 语句的高级运用方法。 常用查询介绍 对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等，这些内容就是接下来要讲解的知识。 1.按关
mysql resultset用法_结果集(ResultSet)用法
weixin_29123181的博客
01-19 1967
结果集(ResultSet)是数据中查询结果返回的一种对象,可以说结果集是一个存储查询结果的对象,但是结果集并不仅仅具有存储的功能,他同时还具有操纵数据的功能,可能完成对数据的更新等.结果集读取数据的方法主要是getXXX(),他的参数可以使整型表示第几列(是从1开始的),还可以是列名.返回的是对应的XXX类型的值.如果对应那列时空值,XXX是对象的话返回XXX型的空值,如果XXX是数字类型,如F...
MyBatis问答自测
weixin_45329799的博客
07-09 890
简述MyBatis工作原理；简述 MyBatis 和 Spring 的整合过程。除 MyBatis 持久化框架外，您还知道哪些持久化框架？并说明他们的特点。MyBatis 实现查询时，返回的结果集有几种常见的存储方式？请举例说明。 在 MyBatis 中针对不同的数据库软件，元素如何将主键回填？ 在 MyBatis 中，如何给 SQL 语句传递参数？ Maven 和 ANT 的区别？什么是 Spring Boot Stater ？ Springboot 如何集成 MyBatis？...
Mysql面试笔试语句干货.docx
09-22
Mysql面试笔试语句干货 titre: Mysql面试笔试语句干货 这份资源总结了两年的Mysql面试笔试题，涵盖了数据库设计、数据操作、数据查询等多方面的知识点。 1. 数据库设计 在创建表时，需要考虑字段的数据类型、...
mysql数据库生产环境性能优化总结，纯干货！！！（三）
01-19
mysql数据库生产环境性能优化总结，纯干货！！！（三） 从mysql配置的角度优化数据库 1.优化mysql的线程缓存 在生产环境中，会有很多请求连接到数据库。为此系统就需要创建线程处理这些请求。然而如果来一个请求就...
30道MySQL高分面试题纯干货-含答案.docx
03-05
MySQL是世界上最流行的开源关系型数据库管理系统之一，面试中经常会有涉及MySQL的问题，下面是一些重要的MySQL面试知识点： 1. **日期函数的区别**： - `NOW()`返回当前日期和时间，包括小时、分钟和秒。 - `...
干货！MySQL资源大全共40页.pdf.zip
11-20
11. **复制与集群**：MySQL的主从复制可以实现数据冗余和高可用性，集群技术如MySQL Cluster则提供了更高级的高可用性和负载均衡。 12. **最新特性**：了解MySQL的新版本特性，如窗口函数、JSON支持、分区表等。 ...
MySQL查询操作总结归纳
Judy的博客
01-23 918
MySQL查询操作
MYSQL——数据处理之查询
weixin_43520450的博客
07-13 1108
查询一、基本SELECT语句二、过滤和排序数据三、分组函数四、分组查询五、多表查询六、分页查询 一、基本SELECT语句 SELECT *|{[DISTINCT] column|expression} FROM table; #SELECT 标识选择哪些列 #FROM 表示从哪个表中选择 SELECT * #*表示选择全部列 FROM departments; SELECT department_id,location_id #选择特定的列 FROM departments; S
详解MySQL 查询语句的执行过程
菜鸟教程
05-07 921
文章来源： 学习通http://www.bdgxy.com/普学网http://www.boxinghulanban.cn/智学网http://www.jaxp.net/ 目录1.客户端和服务器端之间的通信方式2.查询缓存3.查询优化处理4.查询执行引擎5.返回结果给客户端首先先简单的将一个查询语句背后MySQL做了什么捋一捋： 客户端发送一条查询给服务器。 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。 服务器端进行SQL解析，预处理，再由优化器
MySQL高级语句(一)
ZFX20001123的博客
04-06 1373
目录 一、常用查询（增、删、改、查） 1.1.按关键字排序（asc|desc） 1.2.区间判断（and/or） 1.3.查询不重复记录（distinct） 1.4.对结果进行分组（group by） 1.5.限制结果条目（limit） 1.6.设置别名（alias——>as） 1.7.通配符 1.8.子查询 一、常用查询（增、删、改、查） 对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。 例如取 10 条数据、对查询结果进行排序或分组等等。 1.
利用MYSQL存储过程:循环处理查出来的结果集
CXY_BOY的博客
04-23 1824
【代码】利用MYSQL存储过程:循环处理查出来的结果集。
MySQL数据查询的相关操作
weixin_45726908的博客
03-23 1641
数据查询 文章目录数据查询select语句简单查询模糊查询空值查询查询结果排序limit子句去除重复行聚合函数与group by子句多表连接查询嵌套查询数据查询与数据更新合并查询结果 select语句 数据查询语句中使用频率最高的是select语句. 数据库用户在客户端编写select语句, 然后发送给MySQL服务器, MySQL服务器实例将select语句进行解析、编译、执行, 从数据表中查询满足条件的若干记录, 将查询的结果集(result_set)返回客户端. select [all | dis.
MySQL学习9_查询结果处理
万年青的博客
07-29 397
文章目录排序 order by分页 limit分组 group by分组后筛选 having分组后取值 group_concat去重 distinct 排序 order by select * from 【表名】 order by 【字段1】, 【字段2】 asc|desc; ASC表示按升序排列，DESC表示按降序排列，默认升序； 多字段排序，先按照前面的，前面字段值相同，再比较后面的 分页 limit select * from 【表名】 limit offset, count; 使用LIM
mysql进阶
weixin_41158378的博客
09-25 7543
mysql的进阶之路，包括索引、思路、优化、主从等等核心环节
MySQL进阶篇（一）
真滴不可理喻的博客
04-26 824
MySQL中的存储引擎、索引、SQL优化、视图、存储过程、触发器详解
干货预警——原来基因功能富集分析这么简单
最新发布
01-23
根据引用[1]的描述，"干货预警——原来基因功能富集分析这么简单！"是一篇介绍如何使用DAVID在线分析工具对基因进行GO/KEGG功能富集分析的文章。该文章可能包含了以下内容： - DAVID在线分析工具的介绍和使用方法 - ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值