（简单易懂！一看就会！）MySQL高级语句的详细用法

最新推荐文章于 2024-09-30 15:39:10 发布

Jessica丶唯

最新推荐文章于 2024-09-30 15:39:10 发布

阅读量5.8k

点赞数 2

分类专栏： MySQL

本文链接：https://blog.csdn.net/ZG_66/article/details/109427041

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

一、按关键字排序
- 1.1 语法结构
二、对结果进行分组
- 2.1 语法结构
三、限制结果条目
- 3.1 语法结构
- 3.2 环境创造
四、AS的用法
- 4.1 设置别名
- 4.2 作为连接语句
五、常用通配符的用法
- 5.1 %的用法
- 5.2 _的用法
- 5.3 两者结合使用
六、子查询
七、查看表的状态
八、创建视图（虚拟表)
九、结果集
十、exists的用法
十一、NULL值
- 11.1 给表添加属性
- 11.2 在统计时null值不会进入统计
- 11.3 在统计时空值会进入统计
十二、正则表达式
十三、运算符
- 13.1 算术运算符
- 13.2 比较运算符
十四、比较运算符
- 14.1 least 求最小值 Greatest求最大值
- 14.2 IN NOT IN
- 14.3 LIKE NOT LIKE
十五、逻辑运算符
- 15.1 逻辑非
- 15.2 逻辑与
- 16.3 逻辑或
- 15.4 逻辑异或
十六、位运算符
- 16.1 向左位移运算
- 16.2 向右位移运算
十七、运算符的优先级
十八、连接查询
- 18.1 内连接查询
- 18.2 左连接查询
- 18.3 右连接查询
十九、数学函数
- 19.1 abs(x) 返回x的绝对值
- 19.2 rand() 返回0-1的随机数
- 19.3 mod(x,y）返回x除以y以后的余数
- 19.4 power(x,y) 返回x的y次方
- 19.5 round(x) 返回离x最近的整数
- 19.6 sqrt(x) 返回x的平方根
- 19.7 truncate(x,y) 返回数字x截断为y位小数的值
- 19.8 ceil(x)、floor(x)返回大于或等于x的最小整数
二十、聚合函数
- 20.1 avg()返回指定列的平均值
- 20.2 count()返回指定列中非NULL值的个数
- 20.3 min()返回指定列的最小值
- 20.4 max()返回指定列的最大值
- 20.5 sum()返回指定列的所有值之和
二十一、字符串函数
- 21.1 length(x) 返回字符串 x 的长度
- 21.2 trim() 返回去除指定格式的值
- 21.3 concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
- 21.4 upper(x)、lower(x) 将字符串 x 的所有字母变成大写字母
- 21.5 left(x,y) 、right(x,y)返回字符串 x 的前 y 个字符
- 21.6 repeat(x,y) 将字符串 x 重复 y 次
- 21.7 space(x) 返回 x 个空格
- 21.8 replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
- 21.9 strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
- - 21.9.1 substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
  - 21.9.2 reverse(x）将字符串 x 反转

一、按关键字排序

使用SELECT语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，该如何去实现呢？
可以使用 ORDER BY 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，
也可以针对多个字段。以下就是MySQL中ORDER BY语句的语法结构。

1.1 语法结构

排序的字段可以根据具体需求进行选择，没有限制。排序的关键字可以使用 ASC 或者DESC。ASC 是按照升序进行排序的，
是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。
DESC 是按降序方式进行排列。

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

当然 ORDER BY 前面也可以使用 WHERE 子句对查询结果进一步过滤。
例如，执行以下操作可查询等级大于等于 45 级的用户，并按降序进行排序。

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


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用降序；##

二、对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现。
GROUP BY 从字面上看，是以 BY 后面的内容对查询出的数据进行分组，就是将一个“数据集”
划分成若干个“小区域”，然后针对这些个“小区域”进行数据处理。
GROUP BY通常都是结合聚合函数一起使用的，常用的聚合函数包括：
计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），
这些聚合函数的用法在后面函数小节会有更详细的讲解。GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

2.1 语法结构

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)

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

三、限制结果条目

在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第
一行或者前几行，这时候就需要用到 LIMIT 子句。

3.1 语法结构

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number

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

3.2 环境创造

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 * from kgc limit 3;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  2 | lisi      |    77 |     2 |
|  3 | xiaoming  |    66 |     1 |
+----+-----------+-------+-------+
3 rows in set (0.00 sec)

查询第三行后面的两行

mysql> select * from kgc limit 3,2;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  4 | zhaoliu   |    66 |     3 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

四、AS的用法

4.1 设置别名

在MySQL查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者多次使用相同的表，
可以给字段列或表设置别名。使用的时候直接使用别名，简洁明了，增强可读性。设置别名使用AS语句，
列的别名语法结构：

mysql> select i.user_name as 姓名,i.level as 成绩 from kgc as i;
+-----------+--------+
| 姓名      | 成绩   |
+-----------+--------+
| zhangshan |     88 |
| lisi      |     77 |
| xiaoming  |     66 |
| zhaoliu   |     66 |
| tianqi    |     90 |
| wangba    |     60 |
+-----------+--------+
6 rows in set (0.00 sec)

4.2 作为连接语句

as可作为连接语句，可克隆别的表结构，克隆后的表和原先的表数据类型是一样的，但是约束条件不能克隆。

mysql> create table tmp as select * from kgc where  level>=80;
mysql> select * from tmp;
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

五、常用通配符的用法

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。
通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。
常用的通配符有两个，分别是：

%：百分号表示零个、一个或多个字符
_：下划线表示单个字符
like的作用是模糊查询

5.1 %的用法

mysql> select * from kgc where user_name like 'z%';
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  4 | zhaoliu   |    66 |     3 |
+----+-----------+-------+-------+
2 rows in set (0.00 sec)

5.2 _的用法

mysql> select * from kgc where user_name like 'l_s_';
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  2 | lisi      |    77 |     2 |
+----+-----------+-------+-------+
1 row in set (0.00 sec)

5.3 两者结合使用

mysql> select id,user_name,level from kgc where user_name like '_es%';
+----+-----------+-------+
| id | user_name | level |
+----+-----------+-------+
|  7 | leslieF   |    70 |
+----+-----------+-------+
1 row in set (0.00 sec)

六、子查询

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

<表达式> [NOT] IN <子查询>

```bash
mysql> select * from kgc where id in (1,3,5,7)
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  3 | xiaoming  |    66 |     1 |
|  5 | tianqi    |    90 |     2 |
|  7 | leslieF   |    70 |     2 |
+----+-----------+-------+-------+
4 rows in set (0.00 sec)

挨个遍历数据，但是没有的数据不能显示，内部语句执行的结果作为外部语句执行的条件

mysql> create table num (id int(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into num values (1),(3),(5),(7);
Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from num;
+------+
| id   |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
+------+
4 rows in set (0.00 sec)

内外层数据类型必须统一
否则会报错在这里插入图片描述
嵌套举例

mysql> select * from kgc where id in (select id from num);
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  3 | xiaoming  |    66 |     1 |
|  5 | tianqi    |    90 |     2 |
|  7 | leslieF   |    70 |     2 |
+----+-----------+-------+-------+
4 rows in set (0.00 sec)

七、查看表的状态

可查询表中几乎一切信息，包括存储引擎

show table status;

八、创建视图（虚拟表)

视图是真实表的一个映射，只具有读的功能，而且只能读往视图中添加的数据

mysql> create  view v_score as select * from kgc  where level >= 80;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from   v_score；
+----+-----------+-------+-------+
| id | user_name | level | hobby |
+----+-----------+-------+-------+
|  1 | zhangshan |    88 |     1 |
|  5 | tianqi    |    90 |     2 |
+----+-----------+-------+-------+
2 rows in set (0.01 sec)

九、结果集

这就是个结果集
在这里插入图片描述

想要读取结果集当中的某个属性，得给结果集起个别名，将其视为一个整体
from 后面跟结果集就要加别名

错误示范：

mysql> select id from (select id,user_name from kgc);
ERROR 1248 (42000): Every derived table must have its own alias

mysql> select a.id from (select id,user_name from kgc)a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

十、exists的用法

exists 是测试子语句中的数值存不存在的意思，下例语句的意思是，如果表中存在zhangshan，就统计表中的记录条数

mysql> select count(*) from kgc where exists (select * from kgc where user_name='zhangshan');
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)

输入一个不存在的用户名，测试能否统计记录

mysql> select count(*) from kgc where exists (select * from kgc where user_name='xxxoo');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

十一、NULL值

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

11.1 给表添加属性

语句结构

alter table 表名 add column 属性名 数据类型（int)等；

11.2 在统计时null值不会进入统计

创建测试环境
mysql> alter table kgc add column addr varchar(50);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | NULL |
|  2 | lisi      |    77 |     2 | NULL |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | NULL |
|  6 | wangba    |    60 |     2 | NULL |
|  7 | leslieF   |    70 |     2 | NULL |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)
成功添加进了一个属性，设置可以为空便于测试

将所有level属性值大于70的 addr属性值变为nj
mysql> update kgc set addr='nj' where level >= 70;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 | NULL |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)

测试nullz值是否计入统计
mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)
不计入

11.3 在统计时空值会进入统计

将一行null值的记录修改为空值，测试是否会计入统计
mysql> update kgc set addr='' where user_name='wangba';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

测试
mysql> select count(addr) from kgc;
+-------------+
| count(addr) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

十二、正则表达式

MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。
MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式，REGEXP 操作符所支持的匹配模式

正则表达式过滤开头为z的记录

mysql> select * from kgc where user_name regexp '^z';
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  4 | zhaoliu   |    66 |     3 | NULL |
+----+-----------+-------+-------+------+
2 rows in set (0.00 sec)

过滤不含有xyz字符的记录
这里是指连续匹配

+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 |      |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.01 sec)

十三、运算符

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

13.1 算术运算符

运算符描述

加法

减法

乘法
/ 除法
% 取余数
例如，以 SELECT 命令来实现最基础的加减乘除运算，具体操作如下所示。

mysql> select 1+2 as addition, 2-1 as subtraction, 2*3 as multiplication, 4/2 as division, 7%2 as remainder;

13.2 比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出
表中有哪些记录是符合条件的，如果比较的结果为真则返回 1，如果为假则返回 0，
比较的结果如果不确定则返回 NULL。其中字符串在进行比较的时候默认是不区分大小写的，
如果要区分大小写可以通过 binary 关键字来实现。常用的比较运算符
运算符描述运算符描述
= 等于 IS NOT NULL 判断一个值是否不为 NULL
大于 BETWEEN AND 两者之间
< 小于 IN 在集合中
= 大于等于 LIKE 通配符匹配
<= 小于等于 GREATEST 两个或多个参数时返回最大值
!=或<> 不等于 LEAST 两个或多个参数时返回最小值
IS NULL 判断一个值是否为 NULL REGEXP 正则表达式

MySQL当中成立的返回值不为0，不成立为0
在这里插入图片描述

俩个字母比的是底层的ASCll码

mysql> select 'a'>'b';
+---------+
| 'a'>'b' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

等于运算符在字符串上的用法
比的是字符顺序和字符个数

mysql> select 'abc'='abc';
+-------------+
| 'abc'='abc' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

或的关系，当b比a大时，数据库就默认baa大于abc，其余的不会在比较了

mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

between的用法
下面语句是意思是：4是不是在2和6 的中间？

mysql> select 4 between 2 and 6;
+-------------------+
| 4 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

between是包含头和尾的

mysql> select 2 between 2 and 6;
+-------------------+
| 2 between 2 and 6 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

十四、比较运算符

14.1 least 求最小值 Greatest求最大值

mysql> select least(10,20,30),greatest(10,20,30);  
+-----------------+--------------------+
| least(10,20,30) | greatest(10,20,30) |
+-----------------+--------------------+
|              10 |                 30 |
+-----------------+--------------------+
1 row in set (0.00 sec)

支持小数

mysql> select least(10.1,20,30),greatest(10,20,30.1);
+-------------------+----------------------+
| least(10.1,20,30) | greatest(10,20,30.1) |
+-------------------+----------------------+
|              10.1 |                 30.1 |
+-------------------+----------------------+
1 row in set (0.00 sec)

least如果放的是字符和数值的混合数据，是不能执行成功的
greatest:自动屏蔽不同类型的数据

mysql> select least(10.1,20,30,'a'),greatest(10,20,30.1,'a');
+-----------------------+--------------------------+
| least(10.1,20,30,'a') | greatest(10,20,30.1,'a') |
+-----------------------+--------------------------+
| 0                     | 30.1                     |
+-----------------------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

14.2 IN NOT IN

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

mysql> select 97 in ('a','b','c'),'a' not in ('a','b','c');
+---------------------+--------------------------+
| 97 in ('a','b','c') | 'a' not in ('a','b','c') |
+---------------------+--------------------------+
|                   0 |                        0 |
+---------------------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

14.3 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，否则返回 0，
真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种，
运算符描述
NOT 或！逻辑非
AND 或 && 逻辑与
OR 或 || 逻辑或
XOR 逻辑异或

15.1 逻辑非

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

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

15.2 逻辑与

逻辑与通常用于判断两个值或多个值的有效性，如果所有值都是真返回 1，否则返回 0
逻辑与使用 AND 或者&&表示。

0与NULL时结果为0
1与NULL时结果为NULL

mysql> select 2&&3,0&&1,0&&NULL,1 and NULL;
+------+------+---------+------------+
| 2&&3 | 0&&1 | 0&&NULL | 1 and NULL |
+------+------+---------+------------+
|    1 |    0 |       0 |       NULL |
+------+------+---------+------------+
1 row in set (0.00 sec)

16.3 逻辑或

逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。
逻辑或通常使用 OR 或者||来表示。

注意：最好用OR，ll有时不能准确表达或的关系，有时候会合并

mysql> select 2 or 3, 2||0;
+--------+------+
| 2 or 3 | 2||0 |
+--------+------+
|      1 | 20   |
+--------+------+
1 row in set (0.05 sec)

mysql> select 2 or 3, 2 or 0;
+--------+--------+
| 2 or 3 | 2 or 0 |
+--------+--------+
|      1 |      1 |
+--------+--------+
1 row in set (0.00 sec)

字符串无法识别

mysql> select 2 or 3,'a' or 'c';
+--------+------------+
| 2 or 3 | 'a' or 'c' |
+--------+------------+
|      1 |          0 |
+--------+------------+
1 row in set, 2 warnings (0.00 sec)

逻辑或 1和NULL在一起时，输出结果还是 1

mysql> select 0 or NULL,1 or NULL;
+-----------+-----------+
| 0 or NULL | 1 or NULL |
+-----------+-----------+
|      NULL |         1 |
+-----------+-----------+
1 row in set (0.00 sec)

15.4 逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0，另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL。

mysql> select 0 xor 3,0 xor 0,0 xor NULL,1 xor NULL;
+---------+---------+------------+------------+
| 0 xor 3 | 0 xor 0 | 0 xor NULL | 1 xor NULL |
+---------+---------+------------+------------+
|       1 |       0 |       NULL |       NULL |
+---------+---------+------------+------------+
1 row in set (0.00 sec)

十六、位运算符

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

例子

取反1的二进制，取5的二进制，二者进行且的计算
1的二进制：0001
1的取反：1110
5的二进制：0101
1110
0101
将上面二者用且关系计算得出
0100就是4

mysql> select 5&~1;
+------+
| 5&~1 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

16.1 向左位移运算

mysql> select 1<<2;
0001
0100    4
+------+
| 1<<2 |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

16.2 向右位移运算

向右移动位数，多余的位数直接删掉

15>>2
1111
0011 3
mysql> select 15>>2;
+-------+
| 15>>2 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

十七、运算符的优先级

优先级    运算符                   优先级      运算符
1          ！                      8          |
2          ~                       9          =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
3          ^                      10          BETWEEN,CASE,WHEN,THEN,ELSE
4          * , /(DIV), %(MOD)     11          NOT
5          +,-                    12          &&,AND
6          >>,<<                  13          ||,OR,XOR
7          &                      14          :=

十八、连接查询

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

18.1 内连接查询

将两个表的id 连接起来显示id和user_name 属性

mysql> select kgc.id,kgc.user_name from kgc inner join num on kgc.id=num.id;
+----+-----------+
| id | user_name |
+----+-----------+
|  1 | zhangshan |
|  3 | xiaoming  |
|  5 | tianqi    |
|  7 | leslieF   |
+----+-----------+
4 rows in set (0.00 sec)

用内连接的方法将kgc表中的id 和 uer_name属性显示出来，并且显示出hob表中的hob_name属性，将kgc表中的hobby属性和hob表中的id连接一起.

mysql> create table hob (id int(3) not null primary key auto_increment,hob_name varchar(10) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into hob(hob_name) values('云计算'),('大数据'),('人工智能');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select kgc.id,kgc.user_name,hob.hob_name from kgc inner join hob on kgc.hobby=hob.id;
+----+-----------+--------------+
| id | user_name | hob_name     |
+----+-----------+--------------+
|  1 | zhangshan | 云计算       |
|  2 | lisi      | 大数据       |
|  3 | xiaoming  | 云计算       |
|  4 | zhaoliu   | 人工智能     |
|  5 | tianqi    | 大数据       |
|  6 | wangba    | 大数据       |
|  7 | leslieF   | 大数据       |
+----+-----------+--------------+
7 rows in set (0.00 sec)

mysql> select * from kgc;
+----+-----------+-------+-------+------+
| id | user_name | level | hobby | addr |
+----+-----------+-------+-------+------+
|  1 | zhangshan |    88 |     1 | nj   |
|  2 | lisi      |    77 |     2 | nj   |
|  3 | xiaoming  |    66 |     1 | NULL |
|  4 | zhaoliu   |    66 |     3 | NULL |
|  5 | tianqi    |    90 |     2 | nj   |
|  6 | wangba    |    60 |     2 |      |
|  7 | leslieF   |    70 |     2 | nj   |
+----+-----------+-------+-------+------+
7 rows in set (0.00 sec)

18.2 左连接查询

MySQL 除了内连接，还可以使用外连接。区别于 MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，
有左连接和右连接之分。
左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，
并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

左连接是左表的优先级大，所以会根据左表的hobby进行排序，匹配右表的值

mysql> insert into hob(hob_name) values('云开发'),('云交付');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select kgc.id,kgc.user_name,hob.hob_name from kgc left join hob on kgc.hobby=hob.id;
+----+-----------+--------------+
| id | user_name | hob_name     |
+----+-----------+--------------+
|  1 | zhangshan | 云计算       |
|  3 | xiaoming  | 云计算       |
|  2 | lisi      | 大数据       |
|  5 | tianqi    | 大数据       |
|  6 | wangba    | 大数据       |
|  7 | leslieF   | 大数据       |
|  4 | zhaoliu   | 人工智能     |
+----+-----------+--------------+
7 rows in set (0.00 sec)

18.3 右连接查询

右连接也被称为右外连接，在 FROM 子句中使用 RIGHT JOIN 或者 RIGHT OUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录。

以右表为主，显示右表的全部信息，左表信息有则匹配
给右表添加一段属性 addr

mysql> alter table hob add column addr varchar(50);
mysql> select kgc.id,kgc.user_name,hob.hob_name,hob.addr from kgc right join hob on kgc.hobby=hob.id;
+------+-----------+--------------+------+
| id   | user_name | hob_name     | addr |
+------+-----------+--------------+------+
|    1 | zhangshan | 云计算       | NULL |
|    2 | lisi      | 大数据       | NULL |
|    3 | xiaoming  | 云计算       | NULL |
|    4 | zhaoliu   | 人工智能     | NULL |
|    5 | tianqi    | 大数据       | NULL |
|    6 | wangba    | 大数据       | NULL |
|    7 | leslieF   | 大数据       | NULL |
| NULL | NULL      | 云开发       | NULL |
| NULL | NULL      | 云交付       | NULL |
+------+-----------+--------------+------+
9 rows in set (0.00 sec)

十九、数学函数

MySQL提供了实现各种功能的函数
常用的函数分类
1.数学函数
2.聚合函数
3.字符串函数
4.日期时间函数
##常用的数学函数##
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的最大整数

19.1 abs(x) 返回x的绝对值

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

19.2 rand() 返回0-1的随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.0633721929633731 |
+--------------------+
1 row in set (0.00 sec)

如何利用rand控制取值范围？
以下取0-100的随机数

mysql> select rand()*100;
+-------------------+
| rand()*100        |
+-------------------+
| 89.42384467406556 |
+-------------------+
1 row in set (0.00 sec)

19.3 mod(x,y）返回x除以y以后的余数

mysql> select mod(7,2);
+----------+
| mod(7,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

19.4 power(x,y) 返回x的y次方

mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

19.5 round(x) 返回离x最近的整数

mysql> select round(1.4);
+------------+
| round(1.4) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

round(x,y) 保留x的y位小数四舍五入后的值
保留小数点后1位并且四舍五入

mysql> select round(1.49,1);
+---------------+
| round(1.49,1) |
+---------------+
|           1.5 |
+---------------+
1 row in set (0.00 sec)

19.6 sqrt(x) 返回x的平方根

mysql> select sqrt (8);
+--------------------+
| sqrt (8)           |
+--------------------+
| 2.8284271247461903 |
+--------------------+
1 row in set (0.00 sec)

19.7 truncate(x,y) 返回数字x截断为y位小数的值

mysql> select truncate(1.449,2);
+-------------------+
| truncate(1.449,2) |
+-------------------+
|              1.44 |
+-------------------+
1 row in set (0.00 sec)

19.8 ceil(x)、floor(x)返回大于或等于x的最小整数

mysql> select ceil(1.1),floor(1.1);
+-----------+------------+
| ceil(1.1) | floor(1.1) |
+-----------+------------+
|         2 |          1 |
+-----------+------------+
1 row in set (0.00 sec)

取余限制范围 0 ，1 ，2
限制思路：0-100以内的数除以3，余数都是0 1 2 所以输出结果也都是0-2这个范围，ceil是大于x的最小整数

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select mod(ceil(rand()*100),3);
+-------------------------+
| mod(ceil(rand()*100),3) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

二十、聚合函数

MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。
常用的聚合函数
avg()返回指定列的平均值
count()返回指定列中非NULL值的个数
min()返回指定列的最小值
max()返回指定列的最大值
sum()返回指定列的所有值之和

20.1 avg()返回指定列的平均值

求kgc表中level属性的平均值

mysql> select avg(level) from kgc;
+------------+
| avg(level) |
+------------+
|    73.8571 |
+------------+
1 row in set (0.00 sec)

20.2 count()返回指定列中非NULL值的个数

这里的3，是作为一个值统计的

mysql> select count(3);
+----------+
| count(3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

统计表中 name属性值的个数

mysql> select count(user_name) from kgc;
+------------------+
| count(user_name) |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

20.3 min()返回指定列的最小值

mysql> select min(level) from kgc;
+------------+
| min(level) |
+------------+
|         60 |
+------------+
1 row in set (0.00 sec)

20.4 max()返回指定列的最大值

mysql> select max(level) from kgc;
+------------+
| max(level) |
+------------+
|         90 |
+------------+
1 row in set (0.00 sec)

20.5 sum()返回指定列的所有值之和

mysql> select sum(level) from kgc;
+------------+
| sum(level) |
+------------+
|        517 |
+------------+
1 row in set (0.00 sec)

二十一、字符串函数

常用函数不仅包括数学函数和聚合函数，还包含字符串函数，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 反转

21.1 length(x) 返回字符串 x 的长度

mysql> select length('abdc');
+----------------+
| length('abdc') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

空格也会占位置

mysql> select length(' bdc');
+----------------+
| length(' bdc') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

空值不占空间

mysql> select length('');
+------------+
| length('') |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

NULL占空间，数据显示就叫NULL

mysql> select length(NULL);
+--------------+
| length(NULL) |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

21.2 trim() 返回去除指定格式的值

trim 能够去除头尾空格

mysql> select length(trim(' abc'));
+----------------------+
| length(trim(' abc')) |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.06 sec)

中间的格式是去除不掉的

mysql> select length(trim(' a bc'));
+-----------------------+
| length(trim(' a bc')) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

21.3 concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串

mysql> select length(concat('abc','def'));
+-----------------------------+
| length(concat('abc','def')) |
+-----------------------------+
|                           6 |
+-----------------------------+
1 row in set (0.00 sec)

21.4 upper(x)、lower(x) 将字符串 x 的所有字母变成大写字母

mysql> select upper('abc'),lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)

21.5 left(x,y) 、right(x,y)返回字符串 x 的前 y 个字符

mysql> select left('abcdefg',3),right('abcdefg',2);
+-------------------+--------------------+
| left('abcdefg',3) | right('abcdefg',2) |
+-------------------+--------------------+
| abc               | fg                 |
+-------------------+--------------------+
1 row in set (0.00 sec)

21.6 repeat(x,y) 将字符串 x 重复 y 次

mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc       |
+-----------------+
1 row in set (0.00 sec)

21.7 space(x) 返回 x 个空格

由于单单输入这条命令难以看到效果，所以有以下两种方法测试
（1）使用length便于观看

mysql> select length(concat('abc',space(3),'abc'));
+--------------------------------------+
| length(concat('abc',space(3),'abc')) |
+--------------------------------------+
|                                    9 |
+--------------------------------------+
1 row in set (0.00 sec)

（2）用concat也能进行验证

mysql> select concat('abc',space(3),'abc');
+------------------------------+
| concat('abc',space(3),'abc') |
+------------------------------+
| abc   abc                    |
+------------------------------+
1 row in set (0.00 sec)

21.8 replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y

mysql> select replace('abcdefg','bc','12');
+------------------------------+
| replace('abcdefg','bc','12') |
+------------------------------+
| a12defg                      |
+------------------------------+
1 row in set (0.00 sec)

删除字符

mysql> select replace('abcdefg','bc','');
+----------------------------+
| replace('abcdefg','bc','') |
+----------------------------+
| adefg                      |
+----------------------------+
1 row in set (0.00 sec)

21.9 strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1

mysql> select strcmp(4,5);
+-------------+
| strcmp(4,5) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> select strcmp(5,5);
+-------------+
| strcmp(5,5) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select strcmp(6,5);
+-------------+
| strcmp(6,5) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

21.9.1 substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串

mysql> select substring('abcdefg',3,3);
+--------------------------+
| substring('abcdefg',3,3) |
+--------------------------+
| cde                      |
+--------------------------+
1 row in set (0.01 sec)

21.9.2 reverse(x）将字符串 x 反转

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)