MySQL 运维 - 高阶SQL语句

港控/mmm° 2023-01-13 05:58 178阅读 0赞

### MySQL 运维 - 高阶SQL语句 ###

*  一、排序
 *   *  ① 按单字段排序
     *  ② 多字段排序
 *  二、条件判断
 *   *  ① or/and
     *  ② 嵌套/多条件
 *  三、查询
 *   *  ① 查询不重复记录
     *  ② 指定多字段去重
     *  ③ 对结果进行分组
     *  ④ 结合排序语法
     *  ⑤ 限制条目结果
     *  ⑥ 子查询
     *  ⑦ 子查询-exists
     *  ⑧ AS作为连接语句
     *  ⑨ 子查询-别名
     *  ⑩ IN
 *  五、通配符
 *  六、视图\[类似与镜像\]
 *  七、NULL值
 *  八、正则表达式
 *  运算符
 *  九、操作符
 *   *  ① 较运算符
     *   *  等于
         *  不等于
         *  判断一个值为/不为NULL(是否为空或非空)
         *  两者之间(between...and...)
         *  在/不在集合中(in,not in)
     *  ② 逻辑运算符
     *   *  逻辑非(not 或 ！)
         *  逻辑与
         *  逻辑或
         *  逻辑异或
     *  ③ 位运算符
     *   *  位与运算符 &
         *  位或运算符 |
         *  位异或运算 ^
         *  位取反运算&~
 *  十、内连接查询
 *  十一、左连接查询
 *  十二、右连接查询
 *  十三、数据库函数
 *  十四、聚合函数
 *  十五、字符串函数
 *  十六、日期时间函数
 *  十七、存储过程

# 一、排序 #

## ① 按单字段排序 ##

`asc` 升序\[默认\] `desc` 降序  
**语法**

select 显示用字段 from 表名 where 判断用字段>=Number order by 以哪个字段做排序 desc;

**举例：**

select id from user where point>=200 order by point desc;

## ② 多字段排序 ##

`order by` 以后的参数 使用","分割优先是按先后循序而定  
**语法**

select 字段1 from 表名 order by 字段2 desc,字段3 desc;

**举例：**

select id from user order by point desc,id desc;

现根据字段1进行降序排序，在进行字段2的降序排序  
第一个字段只有在出现相同数值的时候第二字段才有意义

# 二、条件判断 #

## ① or/and ##

**语法**

select * from 表名 xx >20 and xx<=10;

**and**表示需要同时满足  
**or**表示只需要满足其中之一  
**举例：**

select id from user where point > 100 and point <= 500;

## ② 嵌套/多条件 ##

**语法**

select * from 表名 where xx > 10 or (xx > 15 and xx < 50);

**举例：**

select id from user where point > 100 or (point > 150 and point < 500);

# 三、查询 #

## ① 查询不重复记录 ##

**语法**

select distinct 字段 from 表名;

**举例：**

select distinct id from user;

![20210416131835392.png][]

![=][20210416131814988.png]

## ② 指定多字段去重 ##

`distinct`必须放在最开头  
`distinct`只能使用需要去重的字段进行操作  
指定多个字段去重的话，必须要满足这几个字段同时重复时才会被过滤  
**语法**

select distinct 字段 from 表名 where 字段 in (select distinct 字段 from 表名);

**举例：**

select id,name from user where point in (select distinct point from user);

## ③ 对结果进行分组 ##

通过 `SQL` 查询出来的结果，还可以对其进行分组，使用`group by`语句来实现，`group by` 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数(count) 求和(sum) 求平均数(avg) 最大值(max) 最小值(min)，`group by` 分组的时候可以按一个或多个字段对结果进行分组处理

**语法**

select count(字段)  from * where 表名 group by 字段;

**举例：**

select count(name),point from user where point>=300 group by point;

对`user`表进行分组  
筛选范围/条件是`point`大于等于`300`的`name`，`point`相同的会默认分在一组

## ④ 结合排序语法 ##

**语法**

select count(字段)  from * where 表名 group by 字段 order by count(字段) desc;

**举例：**

select count(id),name from user group by point;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70][]

select count(id),name from user group by point order by count(id) asc;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 1][]

## ⑤ 限制条目结果 ##

结果集返回的是所有匹配的记录  
使用`limit`语句限制条目  
位置偏移量第一条是`0`第二条是`1`以此类推  
**语法**

select * from 表名 limit number;

**举例：**

select * from user limit 2,3;

**结合order by排序**

select * from 表名 order by 字段 desc limit number;

select * from user order by id desc limit 2,3;

## ⑥ 子查询 ##

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句  
子查询语句是先于著查询语句被执行，其结果作为外层的条件返回给主查询进行下一步的查询过滤

> 子语句可以与主语句所查询的表相同，也可以是不同的表

**不同表举例：**

select name,score from info where id in (select id from info where score>80);

**主语句举例：**

select name,score from info where id;

**子语句举例：**

select id from info where score > 80;

子查询不仅可以在`select`语句中使用，在`insert`、`update`、`delete`中也同样使用  
支持多次嵌套  
可以使用`not in`表示对结果集取反

## ⑦ 子查询-exists ##

select count(*) as number from 表名 where exists(
    select id from 表名 where 字段='值'
    )

`exists` 布尔值判断，后面的内查询语句，是否成立`where` 之后跟条件判断加`exists`之后的条件是否成立，如果成立，则正常执行`count`为计数 `sum`为求和 使用`sum`求和结合`exists`，如子查询结果集不成立输出`null`

> 设定模式(指定字段) 选择表 判断 (选中查询的字段);

## ⑧ AS作为连接语句 ##

**语法**

create table 表名 as select * from 表名

**举例：**

create table user_new as select * from user;

1.创建一个新的表 定义了表结构 插入表数据  
2.但是约束没有复制过来，但是如果原表设置了主键那么`Defaut`默认为`0`

## ⑨ 子查询-别名 ##

先查询表字段\[看作一个结果集\]  
将结果集作为一张表将那些查询的时候  
**语法**

select 别名.字段 from (select 字段 from 表) 别名;

**举例：**

select a.id from (select id,name from info)a;

`select * from` 表名，此为标准格式，而以上的查询语句，"表名"的位置其实是一个结果集，`mysql`并不能识别  
此时给与结果集设置一个别名，并且以`select a.id,name from a;`的方式查询，将此结果集视为一张表就可以正常查询出数据了

相当于select info.id,name from info;
         select 表.字段,字段 from 表;

## ⑩ IN ##

显示已知的资料  
**语法**

select 字段 from 表名 where 字段 in ('值1','值2'...);

**举例：**

select name from user where money in (100,200);
    # 四、设置别名
    当表中有命名过长的字段时，可以通过
    设置别名来缩短操作
    
    **语法**
    
    ```sql
    select 字段 AS 别名 from 表名;

**举例：**

select name AS '名字' from user;

> AS可以省略

使用`AS`后可以用别名代替字段名，其中`AS`语句时可选的  
之后的别名。主要为表内列或者表提供临时的名称  
在查询过程中使用，库内实际的表名或字段不会改变的

表数据别名设置示例

select a.name as 姓名,a.point as 点数 from user as a;

**使用场景**  
1.对复杂的表进行查询的时候，别名可以缩短查询语句书写  
2.多表相连查询的时候

# 五、通配符 #

通配符主要用于替换字符串的部分字符，通过部分字符的匹配将相关结果查询出来  
`%`百分号表示零个或以后多个字符  
`_`下划线表示单个字符

**查询名字是a开头的记录**  
**模糊查询举例：**  
搜索任意包含M字符的行

select * from info where name like '%M%';

![20210416141548123.png][]  
搜索F后任意单个字符i后零个或多个任意字符k

select * from info where name like 'F_i%k';

![20210416141825365.png][]

# 六、视图\[类似与镜像\] #

用于动态保存结果集 `as` 可以作为把上一个表的内容复制过来  
**语法**

create view v_xxx as select * from 表名 where 字段>=Number;

**举例：**

create view v_user as select * from info where score>60;

**查看表的状态**

show table status\G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 2][]

> Comment:VIEW

**查看视图**

select * from v_xxx;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 3][]

**修改原表数据\[该原数据后就自动映射到视图中\]**

update 表名 set 字段='xx' where 字段='xx';

update info set score=1 where name='Sans';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 4][]

# 七、NULL值 #

`NULL`通过用来表示缺失的值，也就是在表中该字段没有值  
如果在创建表时，限制某些字段不为空，则可以使用`NOT NULL`关键字不适用则默认可以为空  
在向表内插入记录或者更新记录时，如果该字段没有`NOT NULL`并且没有值这时候新纪录的该字段将被保存为`NULL`，需要注意的是，`NULL`值与数字`0`或者空白的字段是不同的，值为`NULL`的字段没有值，在`SQL`语句中，使用`IS NULL`可以判断表内的某个字段是不是`NULL`值，相反用`IS NOT NULL`可以判断不是`NULL`值

**NULL值和空值的区别**

<table> 
 <thead> 
  <tr> 
   <th>NULL值</th> 
   <th>空值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>空值长度为0</td> 
   <td>不占用空间</td> 
  </tr> 
  <tr> 
   <td>NULL值的长度为null</td> 
   <td>占用空间</td> 
  </tr> 
 </tbody> 
</table>

`IS NULL`无法判断空值  
空值使用`=`或者`<>`来处理`(!=)`  
`count()`计算时，`NULL`会忽略，空值会加入计算

**举例：**  
![20210419142315236.png][]

检测NULL值是否会计入统计

select count(id) from 表名;

![20210419142429624.png][]  
将其中一条值改为空值

update 表名 set 字段='' from 字段=值;

![20210419143612254.png][]  
检测空值是否会被统计

select count(字段) from 表名;

![20210419143646668.png][]  
查询为null的值

select * from 表名 where 字段 is null;

查看不能null的值

select * from 表名 where 字段 is not null;

# 八、正则表达式 #

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

**REGEXP操作符所支持的匹配模式如下**

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>^</td> 
   <td>匹配文本的开始字符</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>匹配文本的结束字符</td> 
  </tr> 
  <tr> 
   <td>.</td> 
   <td>匹配任何单个字符</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>匹配零个或多个在它前面的字符</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>匹配前面的字符一次或多次</td> 
  </tr> 
  <tr> 
   <td>字符串</td> 
   <td>匹配包含指定的字符串</td> 
  </tr> 
  <tr> 
   <td>^a</td> 
   <td>匹配所有以a开头的字符</td> 
  </tr> 
  <tr> 
   <td>a1 | a2</td> 
   <td>匹配a1或a2</td> 
  </tr> 
  <tr> 
   <td>[a-z]</td> 
   <td>匹配集合中任意一个字符</td> 
  </tr> 
  <tr> 
   <td>[^a-z]</td> 
   <td>匹配除集合中任意一个字符</td> 
  </tr> 
  <tr> 
   <td>{n}</td> 
   <td>匹配前面的字符串n次</td> 
  </tr> 
  <tr> 
   <td>{n,m}</td> 
   <td>匹配前面的字符串至少n次，至多m次</td> 
  </tr> 
 </tbody> 
</table>

**举例：**  
匹配C开头的内容

select id,name from info where name regexp '^C';

![20210419145205802.png][]  
匹配所有n结尾的内容

select id,name,address from info where address regexp 'n$';

![20210419145412918.png][]  
匹配任何单个或多个字符

select id,name from info where name regexp 'S..s';

![20210419145636304.png][]

匹配零个或多个在它前面的字符

select address from info where address regexp 'p*';

![20210419145854837.png][]  
匹配town和Home

select address from info where address regexp 'town|Home';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 5][]  
匹配集合中的任意一个字符

select address from info where address regexp '[a,b]';

![20210419150554518.png][]  
匹配集合以外的字符

select address from info where address regexp '[^lazy]';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 6][]  
匹配前面的字符串1次

select name from info where name regexp 's{1}';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 7][]  
匹配前面的字符1次最多1次

select name from info where name regexp 's{1,1}';

# 运算符 #

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

**算法运算**

在触发运算和取余数运算中，除数不能为`0`，若除数是`0`，返回的结果则为`NULL`  
如果有多个运算发，按照先乘除后加减的优先级进行运算，相同优先级的运算符没有先后顺序

**举例：**

select 4+5,3*3,12/2,20-20,9%9,1+8-2;

![20210419153535132.png][]

create table awsl (select 4+5,3*3,12/2,20-20,9%9,1+8-2);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 8][]

# 九、操作符 #

## ① 较运算符 ##

字符串的比较默认不区分大小写，可以用`binary`来区分  
常用比较运算符(比较对象：数字、字符)

<table> 
 <thead> 
  <tr> 
   <th>=</th> 
   <th>等于</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>！=或&lt;&gt;</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td>通配符匹配</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>判断一个值是否为NULL</td> 
  </tr> 
  <tr> 
   <td>IS NOT NULL</td> 
   <td>判断一个值是否不为NULL</td> 
  </tr> 
  <tr> 
   <td>BETWEEN AND</td> 
   <td>两者之间</td> 
  </tr> 
  <tr> 
   <td>GREATEST</td> 
   <td>两个或多个参数时返回最大值</td> 
  </tr> 
  <tr> 
   <td>LEAST</td> 
   <td>两个或多个参数时返回最小值</td> 
  </tr> 
  <tr> 
   <td>IN</td> 
   <td>在集合中</td> 
  </tr> 
 </tbody> 
</table>

### 等于 ###

等号用来判断数字、字符串和表达式是否相等，如果相等则返回`1`，如果不相等则返回`0`，如果比较的两者有一个值时`NULL`，则比较的结果就是`NULL`

> 其中字符的比较时根据ASCII码来判断的，如果ASCII码相等，则表示两个字符相同

如果`ASCII`码不相等，则表示两个字符不相同，例如字符串比较  
起始比较的时`ASCII`码

**举例：**

select 'A'+'SOH'='B';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 9][]  
为什么`A+SOH`会等于`B`？？  
那是因为他们比较之前会先转为`ASCII`码中的十进制  
转换后：**65(A)+1(SOH)=66(B)**

### 不等于 ###

用于针对数字、字符串和表达式不相等的比较，如果不相等则返回`1`如果相等则返回`0`  
与等于的返回值相反，同时不等于无法用于判断是否为`null`

select 'Cat'<>'Dog',1<>2,3!=3,6.9!=6, NULL<>NULL;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 10][]

> 大于运算符用来判断左侧的操作数是否大于右侧的操作数，若大于返回1否则返回0，他也同样不能用于判断NULL

> 小于运算符用来判断左侧的操作数是否小于右侧的操作数，若小于返回1否则返回0，同样他也不能用于判断NULL

> 大于等于判断左侧的操作数是否大于等于右侧的操作数，若大于等于返回1，否则返回0，同样他也不能用于判断NULL

> 小于等于判断左侧的操作数是否小于等于右侧的操作数，若小于等于返回1，否则返回0，同样他也不能用于判断NULL

**举例：**

select 10>9,'a'>'b',2>=3,(2+3)>=(9+6),7.74<7,'x'<='y',5<=5.5,'u'>=NULL;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 11][]

### 判断一个值为/不为NULL(是否为空或非空) ###

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

select '404 ERROR Page' is null,'=)' is not null,null is null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 12][]

### 两者之间(between…and…) ###

比较运算通常用于判断一个值是否落在某两个值之间  
例如：判断某数字是否在另外两个数字之间，也可以判断某英文字母是否  
在另外两个字母之间，具体操作，条件符合返回`1`，否则返回`0`  
实例

select 4 between 2 and 6,5 between 6 and 8,'c' between 'a' and 'f'

数字比较，按大小排列  
字母比较，从`a`到`b`排序，字母越前越小

### 在/不在集合中(in,not in) ###

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

select 3 in (1,2,3,4,5),'c' not in ('a','b','c');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 13][]

## ② 逻辑运算符 ##

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

### 逻辑非(not 或 ！) ###

逻辑非将跟在它后面的值取反，如果`not`后面的操作数为`0`时所得值为`1`  
如果操作数非`0`时，所得值为`0`，如果操作数为`NULL`时，所得值为`NULL`

举例：

select not 2,13,not 0,!(4-4);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 14][]

### 逻辑与 ###

当所有操作数都为非零值并且不为`NULL`时，返回值为`1`  
当一个或多个操作数为`0`时，返回值为`0`  
操作数中有任何一个为`NULL`时，返回值为`NULL`  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 15][]

**举例：**

select 2 and 3,4 && 0,0 && null,1 and null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 16][]

### 逻辑或 ###

当两个操作数都为非`NULL`值时，如果又任意一个操作数为非零值，则返回值为`1`，否则结果为`0`  
当又一个操作数为`NULL`时，如果另一个操作数为非零值，则返回值为`1`，否则结果为`NULL`  
加入两个操作数均为`NULL`时，则返回值为`NULL`

**举例：**

select 2 OR 3,0 OR null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 17][]

### 逻辑异或 ###

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

select 2 xor 3,0 xor 1,1 xor 0,1 xor null,null xor 3;

![20210419170022439.png][]

## ③ 位运算符 ##

用于对二进制字节中的位进行测试或者位于等测试处理

### 位与运算符 & ###

两个数进行二进制逐位运算，如都为1则为1，如其中一个为0则为0

select 6 & 2;

6的二进制0110  
2的二进制0010  
与运算后的结果为0010=2  
![20210422165402564.png][]

### 位或运算符 | ###

两个数对应的二进制有一个或两个为1则为1，否则为0

select 5 | 1;

5的二进制0101  
1的二进制0001  
或运算后的结果为0101=5  
![20210422165729697.png][]

### 位异或运算 ^ ###

应的二进制数不同时，对应的结果才为1；如果两个对应位数都为0或者都为1，则为0

select 5 ^ 1;

5的二进制0101  
1的二进制0001  
或运算后的结果为0100=4  
![20210422165949798.png][]

### 位取反运算&~ ###

根据优先级先执行~在执行&

select 6 &~ 1;

6的二进制0110  
1的二进制0001  
因先执行~，所以1全部取反=1110  
最后执行&与运算  
得到结果：0110=6  
![20210422170155517.png][]

# 十、内连接查询 #

MySQL中的内连接就是两张或多张表中同时相同的部分

select xx.字段 from 表名 xx  inner join 表名2 b on xx.字段=xx.字段;

# 十一、左连接查询 #

以左侧表为基础表，接收左表的所有行，并用这些行与右侧关键字来表示

select * from 表名 left join 表名2 on 表名.字段= 表名2.字段;

# 十二、右连接查询 #

以右侧表为基础表，接收右表的所有行，并用这些行与左侧关键字来表示

select * from 表名 right join 表名2 on 表名.字段=表名2.字段;

# 十三、数据库函数 #

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

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

**举例：**  
\-2的绝对值

select abs(-2);

![20210420160312668.png][]  
获取随机数\[范围0-1\]

select rand();

![在这里插入图片描述][2021042016041314.png]  
求9除2的余数

select mod(9,2);

![20210420160545335.png][]  
求2的10次方

select power(2,10);

![20210420160722469.png][]  
从超多小数中拯救整数

select round(3.14159265358979323846);

![20210420161027627.png][]  
保留指定数量的小数并四舍五入

select round(3.14159265358979323846,2);

![20210420161151778.png][]  
求6的平方根

select sqrt(6);

![20210420161357789.png][]  
指定保留几位小数

select truncate(1.1111,2);

![20210420161502416.png][]  
返回大于等于3.14的最小整数

select ceil(3.14);

![20210420161729413.png][]  
返回小于等于3.14的最大整数

select floor(3.14);

![20210420161836813.png][]  
返回集合中最大的值

select greatest(1,2,3,4,5);

![2021042016195421.png][]  
返回集合中最小值

select least(1,2,3,4,5);

![20210420162036178.png][]

# 十四、聚合函数 #

<table> 
 <thead> 
  <tr> 
   <th>聚合函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>avg()</td> 
   <td>平均值</td> 
  </tr> 
  <tr> 
   <td>count()</td> 
   <td>计数</td> 
  </tr> 
  <tr> 
   <td>min()</td> 
   <td>最小值</td> 
  </tr> 
  <tr> 
   <td>max()</td> 
   <td>最大值</td> 
  </tr> 
  <tr> 
   <td>sum()</td> 
   <td>所有值之和</td> 
  </tr> 
 </tbody> 
</table>

# 十五、字符串函数 #

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

> 测试中发现一些有趣的bug  
> select upper(‘a’)=‘a’;会成立

> 它会优先去找可以转化成数字的字符，如果单引号里是1abc就不会成立，它会把字母抛弃变为0，所以结果会成立

# 十六、日期时间函数 #

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

# 十七、存储过程 #

> 存储过程是一组为了完成特定功能的SQL语句集合

存储过程在使用过程中是将常用或者复杂的工作预先使用`SQL`语句写好并用一个指定的名称来进行储存，这个过程经编译和优化后存储在数据库服务器中，当需要使用该存储过程时，只需要调用它即可，存储过程在执行上比传统`SQL`速度更快，执行效率更高。

**存储过程的优点**

1.  执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
2.  SQL语句加上控制语句的集合，灵活性高
3.  在服务器端存储，客户端调用时，降低网络负载
4.  可多次重复被调用，可随时修改，不影响客户端调用
5.  可完成所有的数据库操作，也可控制数据库的信息访问权

**语法：**

DELIMITER !!    				#将语句的结束符号从分号；临时修改，以防出问题，可以自定义
    CREATE PROCEDURE XXX()  	#创建存储过程，过程名自定义,（）可带参数
     	BEGIN   		    	#过程体以关键字BEGIN开始
     	select * from xxx;  	#过程体语句
     	END!!    				#过程体以关键字END结尾
    DELIMITER ; 			    	#将语句的结束符号恢复为分号
    
    call XXX;	  				#调用存储过程
    
    
    show create procedure [数据库.]储存过程名; 	  #查看某个储存过程的具体信息
    show create procedure XXX;
    show procedure status [like '%XXX%'] \G

**例：**

delimiter $$
    create procedure test()
    begin
    create table user(id int(10));
    insert into user values (1);
    end $$
    
    delimiter ;
    call test;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 18][]  
![20210420170457734.png][]  
查看存储过程

show procedure status;

修改存储功能

alter procedure 函数名 modifies sql data sql security invoker;

删除存储过程

drop procedure if exists 函数名

[20210416131835392.png]: /images/20221022/2755ce5a36f34a7898c28a2aa621338f.png
[20210416131814988.png]: /images/20221022/22dd469180f147d0ab4181b45cc1fe88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70]: /images/20221022/5c9e55f713204c4fb79622523b8d608f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 1]: /images/20221022/d26a4a0bf4804b8d8d8fff6a10fc5ba2.png
[20210416141548123.png]: /images/20221022/d012f68329c04dcabc9e393123db759f.png
[20210416141825365.png]: /images/20221022/ea360542fb4049c891e19df6824a2507.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 2]: /images/20221022/a395ef22f2714df4bcf339a49c908260.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 3]: /images/20221022/8821f9468c7c45639d7909cdf0345ab4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 4]: /images/20221022/2aaf7bb269514122b42c6b81196b0ce2.png
[20210419142315236.png]: /images/20221022/f6c520d404264db0910483ab4cadd104.png
[20210419142429624.png]: /images/20221022/faaaff9322ce4ddbba46b9d83306d432.png
[20210419143612254.png]: /images/20221022/827f6eb4f2024e60a72f1d57441eeb0c.png
[20210419143646668.png]: /images/20221022/8c8fc2bffb27493fa2aacd59e488f0f3.png
[20210419145205802.png]: /images/20221022/1a4502d8694748a89819e9a6faccfebf.png
[20210419145412918.png]: /images/20221022/1388c1d8e585411488098c1c3e39a0e8.png
[20210419145636304.png]: /images/20221022/d3ab582ba2364398b5e96920680280a9.png
[20210419145854837.png]: /images/20221022/246759fa0dd048a2a31d35381efdcdd3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 5]: /images/20221022/d137d92bb0f34f0a889a16e772ed3194.png
[20210419150554518.png]: /images/20221022/4da24e9aca1c45cd9091d943ce24e18a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 6]: /images/20221022/6651ec91de7c43b0ae73fe65b7d3f026.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 7]: /images/20221022/dfdd16c38370440face066f377983db6.png
[20210419153535132.png]: /images/20221022/79027d30ab894cd58fc73531acc179a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 8]: /images/20221022/6d9ee1119cad4bf5ac2423f96d96be06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 9]: /images/20221022/ebb04170bd36440a915bfc858b8c1261.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 10]: /images/20221022/cc421b360a424cfa82a42e0fef873c9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 11]: /images/20221022/6b749049bdee4763af807b8d40fab81e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 12]: /images/20221022/6620495ba74049e185f0726e021286d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 13]: /images/20221022/ff42839571ed41228e102e95c7d883af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 14]: /images/20221022/5037f8bbee4f4731bdc36d8317a84689.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 15]: /images/20221022/e004ae9d75aa4e8fa1a5206b5efd5ef9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 16]: /images/20221022/9396d369316241f7bebbdd443b4fafdb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 17]: /images/20221022/22a570e283a64d1587443d580c476b09.png
[20210419170022439.png]: /images/20221022/3224a49e42be4e0d82306ccbf4990c43.png
[20210422165402564.png]: /images/20221022/57d955d788f14e5693118669e51c6bc2.png
[20210422165729697.png]: /images/20221022/ec323713733b4ee6bf6d92a86626e41e.png
[20210422165949798.png]: /images/20221022/a503905b562b45e9be9ddc37c8ca49bc.png
[20210422170155517.png]: /images/20221022/97be4a8596b0442180b02c8afaa5db14.png
[20210420160312668.png]: /images/20221022/df17ac754947443996b5bb21bf723114.png
[2021042016041314.png]: /images/20221022/f4683dc8ffed45dda60ce32d8e0ffc20.png
[20210420160545335.png]: /images/20221022/696047918ec243ca99f48fae02333a65.png
[20210420160722469.png]: /images/20221022/89285350a31e4135a394ae08c3e289dc.png
[20210420161027627.png]: /images/20221022/4d48e6ea057147078819b725a9fa2706.png
[20210420161151778.png]: /images/20221022/6d95ce033aaa46e28eccc25d766e0a18.png
[20210420161357789.png]: /images/20221022/d5c6adde17de43c1a3dd5b8d9c1c2138.png
[20210420161502416.png]: /images/20221022/e4fcd86c36c140cab4417d0d255a4fcc.png
[20210420161729413.png]: /images/20221022/95b8c17f002146eaa23b57b56d9d4e74.png
[20210420161836813.png]: /images/20221022/9eb00ad625b04a408f27f31f616c9731.png
[2021042016195421.png]: /images/20221022/b86d0de96538417bb737072143f2cc21.png
[20210420162036178.png]: /images/20221022/e12d37e8b2284623b226570944dfe558.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI3OTcx_size_16_color_FFFFFF_t_70 18]: /images/20221022/5dc7d5ec4f6340aeba3d598b537e2897.png
[20210420170457734.png]: /images/20221022/3655c9c1d38248bcbd284dda7375bb87.png