【数据库】MySQL 高级(进阶) SQL 语句

客官°小女子只卖身不卖艺 2024-04-06 14:46 163阅读 0赞

#### 文章目录 ####

*  前提条件
 *  一、常用查询
 *   *  1. SELECT（显示查询）
     *  2. DISTINCT（不重复查询）
     *  3. WHERE（有条件查询）
     *  4. AND/OR（且/或）
     *  5. IN （显示已知值的字段）
     *  6. BETWEEN（显示两个值范围内的字段）
     *  7. 通配符的使用
     *   *  LIKE （匹配关键字查询）
     *  8. ORDER BY (按关键字排序)
 *  二、常用函数
 *   *  1. 数学函数
     *  2. 聚合函数
     *  3. 字符串函数
 *  三、查询与函数
 *   *  1. GROUP BY（汇总分组）
     *  2. HAVING （过滤返回值）
     *  3. 别名（字段别名 表格别名）
     *  4. 子查询
     *  5. EXISTS（类查询）
 *  四、连接查询
 *   *  1. 表连接
     *  2. CREATE VIEW（视图）
     *  3. UNION（联集）
     *  4. 交集
     *  5. 无交集
 *  五、其他用法
 *   *  1. CASE
     *  2. NULL 值
     *   *  2.1 空值（NULL）和无值（“ ”）的区别
         *  2.2 查询空值与非空值
     *  3. 正则表达式
 *  六、存储过程
 *   *  1. 存储过程概念
     *  2. 存储过程的优点
     *  3. 存储过程相关操作
     *   *  3.1 创建存储过程
         *  3.2 调用存储过程
         *  3.3 查看存储过程
         *  3.4 存储过程的参数
         *  3.5 删除存储过程
         *  3.6 控制语句
         *   *  条件语句
             *  循环语句
 *  总结
 *   *  1. sql 语句中 HAVING 的用法
     *  2. mysql数据库有几种表连接
     *  3. view视图表是否能更改数据
     *  4. 创建存储过程的步骤

--------------------

## 前提条件 ##

> location表格创建

use class;
    create table location (Region char(20),Store_Name char(20));
    insert into location values('East','Boston');
    insert into location values('East','New York');
    insert into location values('West','Los Angeles');
    insert into location values('West','Houston');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M8A30HhZ-1687255797624)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620140331268.png)\]][img-M8A30HhZ-1687255797624_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140331268.png]

> store\_info表格创建

create table store_info (Store_Name char(20),Sales int(10),Date char(10));
    insert into store_info values('Los Angeles','1500','2020-12-05');
    insert into store_info values('Houston','250','2020-12-07');
    insert into store_info values('Los Angeles','300','2020-12-08');
    insert into store_info values('Boston','700','2020-12-08');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-76Uc2W4D-1687255797625)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620140459620.png)\]][img-76Uc2W4D-1687255797625_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140459620.png]

--------------------

## 一、常用查询 ##

### 1. SELECT（显示查询） ###

> 显示表格中一个或数个字段的所有数据记录

SELECT "字段" FROM "表名";
    
    例如
    select Store_name from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-swNPEhYH-1687255797625)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620140533955.png)\]][img-swNPEhYH-1687255797625_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140533955.png]

### 2. DISTINCT（不重复查询） ###

> 不显示重复的数据记录

SELECT DISTINCT "字段" FROM "表名";
    
    例如
    select distinct Store_Name from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TB8ttezc-1687255797626)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620140550840.png)\]][img-TB8ttezc-1687255797626_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140550840.png]

### 3. WHERE（有条件查询） ###

> 按照条件进行查询

SELECT "字段" FROM "表名" WHERE "条件";
    
    例如
    select distinct Store_Name from store_info where Sales > 1000;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MDvqQTZW-1687255797626)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141321876.png)\]][img-MDvqQTZW-1687255797626_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141321876.png]

### 4. AND/OR（且/或） ###

SELECT "字段" FROM "表名" WHERE "条件1" {
       [AND|OR] "条件2"} + ;
    
    例如
    select Store_Name from store_info where Sales > 1000 OR ( Sales < 500 and Sales > 200 );

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-61Kt25MA-1687255797627)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141404552.png)\]][img-61Kt25MA-1687255797627_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141404552.png]

### 5. IN （显示已知值的字段） ###

> 在已知的字段数据取值范围内取值

SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);
    
    例如
    select * from store_info where Store_Name in ('Los Angeles', 'Houston');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2JnfgOtj-1687255797627)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141429181.png)\]][img-2JnfgOtj-1687255797627_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141429181.png]  
另外还有not in命令，用法一致，表示显示不在指定范围内的字段的值。

### 6. BETWEEN（显示两个值范围内的字段） ###

> 在两个字段数据值之间取值，包含两边字段的数据

SELECT "字段" FROM "表名" WHERE "字段"  BETWEEN '值1' AND '值2';
    
    例如
    select * from store_info where Date between '2020-12-06' and '2020-12-10';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iOdqgdiw-1687255797628)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141538394.png)\]][img-iOdqgdiw-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141538394.png]

### 7. 通配符的使用 ###

> 通常通配符都是与like配合使用的
> 
> % ：百分号表示零个、一个或多个字符
> 
> \_ ：下划线表示单个字符

'A_Z'：所有以 'A' 起头，另一个任何值的字符，且以 'Z' 为结尾的字符串。例如，'ABZ' 和 'A2Z' 都符合这一个模式，而 'AKKZ' 并不符合 (因为在 A 和 Z 之间有两个字符，而不是一个字符)。
    'ABC%': 所有以 'ABC' 起头的字符串。例如，'ABCD' 和 'ABCABC' 都符合这个模式。
    '%XYZ': 所有以 'XYZ' 结尾的字符串。例如，'WXYZ' 和 'ZZXYZ' 都符合这个模式。
    '%AN%': 所有含有 'AN'这个模式的字符串。例如，'LOS ANGELES' 和 'SAN FRANCISCO' 都符合这个模式。
    '_AN%'：所有第二个字母为 'A' 和第三个字母为 'N' 的字符串。例如，'SAN FRANCISCO' 符合这个模式，而 'LOS ANGELES' 则不符合这个模式。

#### LIKE （匹配关键字查询） ####

SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
    
    例如
    select * from store_info where Store_Name like '%os%';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nqItwmTs-1687255797628)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141620857.png)\]][img-nqItwmTs-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141620857.png]

### 8. ORDER BY (按关键字排序) ###

SELECT "字段" FROM "表名" [WHERE “条件”] ORDER BY "字段" [ASC, DESC];
    #ASC 	是按照升序进行排序的，是默认的排序方式。
    #DESC 	是按降序方式进行排序。
    
    例如
    select Store_Name,Sales,Date from store_info order by Sales DESC;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AnAOrwut-1687255797628)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141645511.png)\]][img-AnAOrwut-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141645511.png]

## 二、常用函数 ##

### 1. 数学函数 ###

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

select abs(-1), rand(), mod(5,3), power(2,3), round(1.89);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6cesclfZ-1687255797629)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141747900.png)\]][img-6cesclfZ-1687255797629_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141747900.png]

select round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V04tfNVS-1687255797629)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620141808995.png)\]][img-V04tfNVS-1687255797629_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141808995.png]

#求一组数据最大值
    create table t1 (id1 int, id2 int, id3 int, id4 int);
    insert into t1 values (10, 50, 30, 20);
    select * from t1;
    select greatest(id1,id2,id3,id4) from t1;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9m8oGZmf-1687255797630)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620142853498.png)\]][img-9m8oGZmf-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142853498.png]

#求1000以内的随机整数
    select round(rand() * 1000);
    select truncate(rand() * 1000,0);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dcxEJv40-1687255797630)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620142922318.png)\]][img-dcxEJv40-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142922318.png]

### 2. 聚合函数 ###

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>avg()</code></td> 
   <td>返回指定列的平均值</td> 
  </tr> 
  <tr> 
   <td><code>count()</code></td> 
   <td>返回指定列中非 NULL 值的个数</td> 
  </tr> 
  <tr> 
   <td><code>min()</code></td> 
   <td>返回指定列的最小值</td> 
  </tr> 
  <tr> 
   <td><code>max()</code></td> 
   <td>返回指定列的最大值</td> 
  </tr> 
  <tr> 
   <td><code>sum(x)</code></td> 
   <td>返回指定列的所有值之和</td> 
  </tr> 
 </tbody> 
</table>

#求平均值
    select avg(Sales) from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aft7VI8S-1687255797630)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620142952378.png)\]][img-aft7VI8S-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142952378.png]

#求表中城市数量
    select count(Store_Name) from store_info where Store_Name is NOT NULL;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uV07qn1a-1687255797631)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143001063.png)\]][img-uV07qn1a-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143001063.png]

#统计数量，进行去重
    select count(DISTINCT Store_Name) from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uQFEyunh-1687255797631)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143007782.png)\]][img-uQFEyunh-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143007782.png]

#统计字段中最大值
    select max(Sales) from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ooZPNcCv-1687255797631)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143050512.png)\]][img-ooZPNcCv-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143050512.png]

#统计字段中最小值
    select min(Sales) from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-enURmoPY-1687255797631)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143058997.png)\]][img-enURmoPY-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143058997.png]

#统计字段中的总和
    select sum(Sales) from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q5rRmw9P-1687255797632)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143104492.png)\]][img-Q5rRmw9P-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143104492.png]

### 3. 字符串函数 ###

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

#输出指定一个字段中某一部分
    #命令行
    i='123456789'
    echo ${i:3:4}

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jLr7i60P-1687255797632)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620145401175.png)\]][img-jLr7i60P-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145401175.png]

#字段替换
    #命令行操作
    i='123456789'
    echo $i | tr '456' 'abc'

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6lMes6ye-1687255797632)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620145306764.png)\]][img-6lMes6ye-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145306764.png]

#大小写字母替换
    #命令行操作
    echo abc123xyz | tr 'a-z' 'A-Z'

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EfDv9jRu-1687255797633)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620145349286.png)\]][img-EfDv9jRu-1687255797633_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145349286.png]

#字段拼接
    select concat(Region, Store_Name) from location where Store_Name = 'Boston';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n4yj5WC3-1687255797633)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143346203.png)\]][img-n4yj5WC3-1687255797633_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143346203.png]

#如sql_mode开启开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，
    #和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的
    select Region || ' ' || Store_Name from location where Store_Name = 'Boston';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-izpkQijY-1687255797634)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143450953.png)\]][img-izpkQijY-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143450953.png]

select substr(Store_Name,5) from location where Store_Name = 'Los Angeles';
    select substr(Store_Name,3,4) from location where Store_Name = 'New York';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CMzpF4bD-1687255797634)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143659443.png)\]][img-CMzpF4bD-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143659443.png]

SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
    #[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。 
    #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。
    
    例如
    select trim(leading 'New ' from 'New York');
    select trim(leading 'Los' from (select store_name from location where store_name = 'Los Angeles'));

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9xevhfWr-1687255797634)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144436274.png)\]][img-9xevhfWr-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144436274.png]

select replace(Region, 'ast', 'astern')from location;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cHBnmPll-1687255797635)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620143841232.png)\]][img-cHBnmPll-1687255797635_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143841232.png]

## 三、查询与函数 ##

### 1. GROUP BY（汇总分组） ###

> 对GROUP BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的。
> 
> GROUP BY有一个原则，就是 SELECT 后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。

SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1";
    
    例如
    #对分组的总和进行降序排序
    select Store_Name,sum(Sales) from store_info group by Store_Name order by sales desc;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QgzP10x4-1687255797635)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144452549.png)\]][img-QgzP10x4-1687255797635_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144452549.png]

### 2. HAVING （过滤返回值） ###

> 用来过滤由 GROUP BY 语句返回的记录集，通常与 GROUP BY 语句联合使用。
> 
> HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。

SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);
    
    例如
    select Store_Name, SUM(Sales) from store_info group by Store_Name having SUM(Sales) > 1500;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-laBozDPj-1687255797636)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144613694.png)\]][img-laBozDPj-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144613694.png]

### 3. 别名（字段别名 表格别名） ###

SELECT "表格別名"."字段1" [AS] "字段別名" FROM "表格名" [AS] "表格別名";
    
    例如
    select A.Store_Name Store, SUM(A.Sales) "Total Sales" from store_info A group by A.Store_Name;
    select Store_Name,sum(Sales) as "Total Sales" from store_info  group by Store_Name;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yQefuTDH-1687255797636)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144639200.png)\]][img-yQefuTDH-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144639200.png]

### 4. 子查询 ###

> 连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句

SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符] 				#外查询
    (SELECT "字段1" FROM "表格2" WHERE "条件");						   #内查询
    #内查询优先级高于外查询
    
    #可以是符号的运算符，例如 =、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN

例如
    #查询两个表交集的城市
    select * from location where store_name in (select store_name from store_info);
    
    #查询在location表中西部城市的销售总和
    select sum(sales) from store_info where store_name in (select store_name from location where region = 'West');
    
    #通过多表查询，查询两个表中有交集的值
    select sum(A.Sales) from store_info A where A.Store_Name in (select Store_Name from location B where B.Store_Name = A.Store_Name);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Cp4Sh4fA-1687255797636)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144740036.png)\]][img-Cp4Sh4fA-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144740036.png]

### 5. EXISTS（类查询） ###

> 用来测试内查询有没有产生任何结果，类似布尔值是否为真。
> 
> 如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。

SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

例如
    #判断是否有西部城市，有的话就计算销售和
    ，如果没有就输出NULL
    select SUM(Sales) from store_info where exists (select * from location where Region = 'West');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uIGixYUA-1687255797637)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230620144806324.png)\]][img-uIGixYUA-1687255797637_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144806324.png]

## 四、连接查询 ##

### 1. 表连接 ###

<table> 
 <thead> 
  <tr> 
   <th>表连接方式</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>inner join</code>（内连接）</td> 
   <td>只返回两个表中联结字段相等的行</td> 
  </tr> 
  <tr> 
   <td><code>left join</code>（左连接）</td> 
   <td>返回包括左表中的所有记录和右表中联结字段相等的记录，不相等的行则返回null</td> 
  </tr> 
  <tr> 
   <td><code>right join</code>（右连接）</td> 
   <td>返回包括右表中的所有记录和左表中联结字段相等的记录，不相等的行则返回null</td> 
  </tr> 
 </tbody> 
</table>

#内连接
    select * from location A inner join store_info B on A.Store_Name = B.Store_Name ;
    select * from location A inner join store_info B using(Store_Name);
    #其中using需要在两张表有同名情况下使用

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zKKJnPxs-1687331282224)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621133924390.png)\]][img-zKKJnPxs-1687331282224_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621133924390.png]

#右连接
    select * from location A RIGHT JOIN store_info B on A.Store_Name = B.Store_Name ;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LYfo78zw-1687331282225)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621134002192.png)\]][img-LYfo78zw-1687331282225_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134002192.png]

#多表查询
    select * from location A, store_info B where A.store_name = B.store_name;
    #效率略低于inner用法

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cn4Os6N0-1687331282226)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621134040344.png)\]][img-cn4Os6N0-1687331282226_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134040344.png]

#对分表查询进行分组、排序
    select region,sum(B.sales) from location A, store_info B where A.store_name = B.store_name group by region order by sum(B.sales) desc;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V4hXgEOJ-1687331282227)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621134133908.png)\]][img-V4hXgEOJ-1687331282227_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134133908.png]

### 2. CREATE VIEW（视图） ###

> 视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。
> 
> 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还耍进行统计排疗等操作，写sql语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

CREATE VIEW "视图表名" AS "SELECT 语句";
    
    例如
    CREATE VIEW V_REGION_SALES AS SELECT A.Region REGION,SUM(B.Sales) SALES FROM location A INNER JOIN store_info B ON A.Store_Name = B.Store_Name GROUP BY REGION;
    
    SELECT * FROM V_REGION_SALES;
    DROP VIEW V_REGION_SALES;

![在这里插入图片描述][596fee0ad8e94cb9a25dacdb1c12db68.png_pic_center]

### 3. UNION（联集） ###

> 将两个sql语句的结果合并起来，两个sql语句所产生的字段需要是同样的数据记录种类。

#union生成结果的数据记录值将没有重复，且按照字段的顺序进行排序
    [SELECT 语句 1] UNION [SELECT 语句 2];
    
    例如
    select Store_Name from location union select Store_Name from store_info;

![在这里插入图片描述][fcb16a6d1d02456c9eb583771900f99f.png_pic_center]

#union all:将生成结果的数据记录值都列出来，无论有无重复
    语法：[SELECT 语句 1] UNION ALL [SELECT 语句 2];
    
    例如
    select Store_Name from location union all select Store_Name from store_info;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XVWBVThb-1687331282229)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621134536306.png)\]][img-XVWBVThb-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134536306.png]

### 4. 交集 ###

> 取两个SQL语句结果的交集

#内连接
    select [distinct] A.字段 from 左表 A inner join 右表 B on A.字段 = B.字段;
    select A.字段 from 左表 A inner join 右表 B using(同名字段);
    
    例如
    SELECT A.Store_Name FROM location A INNER JOIN store_info B ON A.Store_Name = B.Store_Name;
    SELECT A.Store_Name FROM location A INNER JOIN store_info B USING(Store_Name);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lW4Q4EbQ-1687331282229)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621134736502.png)\]][img-lW4Q4EbQ-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134736502.png]

#左连接
    select B.字段 from 左表 A left join 右表 B on A字段 = B.字段 where B.字段 is not null;
    #右连接
    select A.字段 from 左表 A right join 右表 B on A.字段 = B.字段 where A字段 is not null;
    
    例如
    select B.store_name from location A left join store_info B on A.store_name = B.store_name where B.store_name is not null;
    select A.store_name from location A right join store_info B on A.store_name = B.store_name where A.store_name is not null;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wGvoDKuP-1687331282229)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621140733195.png)\]][img-wGvoDKuP-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621140733195.png]

#查询两表相同字段
    select A.字段 from 左表 A，右表 B where A.字段 = B.字段;
    
    例如
    select B.store_name from location A, store_info B where A.store_name = B.store_name;
    select A.store_name from location A, store_info B where A.store_name = B.store_name;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cfYqqLRh-1687331282230)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621140913526.png)\]][img-cfYqqLRh-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621140913526.png]

#子查询
    select 字段 from 左表 where 字段 in (select 字段 from 右表);
    
    例如
    select store_name from location where store_name in (select store_name from store_info);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8KSlyqNp-1687331282230)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621141056847.png)\]][img-8KSlyqNp-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141056847.png]

#去重查询
    select distinct B.store_name from location A inner join store_info B using(store_name);
    select B.store_name from location A inner join store_info B using(store_name) group by store_name;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1AkybI2B-1687331282230)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621141402455.png)\]][img-1AkybI2B-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141402455.png]

### 5. 无交集 ###

> 显示第一个SQL语句的结果，且与第二个SQL语句没有交集的结果，且没有重复

#单表无交集查询
    #子查询
    select distinct store_Name from location where (store_name) not in (select Store_Name from store_info);

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-65MtnFor-1687331282231)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621141534303.png)\]][img-65MtnFor-1687331282231_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141534303.png]

#单表无交集查询
    #右连接
    select B.store_name from location A right join store_info B on A.store_name = B.store_name where A.store_name is null;
    
    #左连接
    select A.store_name from location A left join store_info B on A.store_name = B.store_name where ere B.store_name is null;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-efW8HdBY-1687331282231)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621141857586.png)\]][img-efW8HdBY-1687331282231_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141857586.png]

#多表无交集查询
    #视图方式
    create view V XXX as select 字段 from 左表 union all select 字段 from 右表;
    select 字段 from V_XXX qroup by 字段 having count(字段) = 1;
    
    例如
    create view V_union as select  store_name from location union all select store_name from store_info;
    select * from V_union;
    select store_name from  V_union group by store_name having count(store_name) = 1;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7AK5u548-1687331282232)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621142020945.png)\]][img-7AK5u548-1687331282232_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142020945.png]

#多表无交集查询
    #派生表:将select语句作为一张表来处理
    
    
    例如
    select A.store_name from (select distinct store_name from location union all select distinct store_name from store_info) A group by A.store_name having count(A.store_name) = 1;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MKKAlwDw-1687331282232)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621142117213.png)\]][img-MKKAlwDw-1687331282232_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142117213.png]

#多表无交集查询
    #若查询之前其中一个表中有重复值，需要在建视图之前进行查重
    create view V_union as select distinct store_name from location union all select distinct store_name from store_info;
    select * from V_union;
    select store_name from V_union group by store_name having count(store_name) = 1;

![在这里插入图片描述][27f7d20a9be04c0a84ba1d40b2de03c6.png_pic_center]

## 五、其他用法 ##

### 1. CASE ###

> case 是 sql 语句用来 IF-THEN-ELSE 之类逻辑的关键字

SELECT CASE ("字段名")
      WHEN "条件1" THEN "结果1"
      WHEN "条件2" THEN "结果2"
      ...
      [ELSE "结果N"]
      END
    FROM "表名";
    
    # "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。
    
    
    例如
    mysql> select Store_Name, case store_name
        -> when 'Los Angeles' then sales * 2
        -> when 'Boston' then 2000
        -> else sales
        -> end
        -> 'New Sales',Date
        -> from store_info;
    #then可以对查询结果进行指定数值

![在这里插入图片描述][66b3fe0473fa4bd997fdd6f20c8af533.png_pic_center]

### 2. NULL 值 ###

#### 2.1 空值（NULL）和无值（“ ”）的区别 ####

无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
    IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
    无值的判断使用=''或者<>''来处理。<> 代表不等于。 
    在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

#### 2.2 查询空值与非空值 ####

#计算长度
    select length(null), length('1234'), length('1'), length('');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qmQI2rj3-1687331282233)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621142906730.png)\]][img-qmQI2rj3-1687331282233_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142906730.png]

#指定字段去统计
    select count(name) from t2;
    select count(*) from t2;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U20HtWDi-1687331282234)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621150503892.png)\]][img-U20HtWDi-1687331282234_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621150503892.png]

### 3. 正则表达式 ###

<table> 
 <thead> 
  <tr> 
   <th>匹配模式</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>^</code></td> 
   <td>匹配文本的开始字符</td> 
   <td><code>^bd</code> 匹配以 bd 开头的字符串</td> 
  </tr> 
  <tr> 
   <td><code>$</code></td> 
   <td>匹配文本的结束字符</td> 
   <td><code>qn$</code> 匹配以 qn 结尾的字符串</td> 
  </tr> 
  <tr> 
   <td><code>.</code></td> 
   <td>匹配任何单个字符</td> 
   <td><code>s.t</code> 匹配任何 s 和 t 之间有一个字符的字符串</td> 
  </tr> 
  <tr> 
   <td><code>*</code></td> 
   <td>匹配零个或多个在它前面的字符</td> 
   <td><code>fo*t</code> 匹配 t 前面有任意个 o</td> 
  </tr> 
  <tr> 
   <td><code>+</code></td> 
   <td>匹配前面的字符 1 次或多次</td> 
   <td><code>hom+</code>匹配以 ho 开头，后面至少一个m 的字符串</td> 
  </tr> 
  <tr> 
   <td><code>字符串</code></td> 
   <td>匹配包含指定的字符串</td> 
   <td><code>clo</code> 匹配含有 clo 的字符串</td> 
  </tr> 
  <tr> 
   <td>`p1</td> 
   <td>p2`</td> 
   <td>匹配 p1 或 p2</td> 
  </tr> 
  <tr> 
   <td><code>[...]</code></td> 
   <td>匹配字符集合中的任意一个字符</td> 
   <td><code>[abc]</code> 匹配 a 或者 b 或者 c</td> 
  </tr> 
  <tr> 
   <td><code>[^...]</code></td> 
   <td>匹配不在括号中的任何字符</td> 
   <td><code>[^ab]</code> 匹配不包含 a 或者 b 的字符串</td> 
  </tr> 
  <tr> 
   <td><code>{n}</code></td> 
   <td>匹配前面的字符串 n 次</td> 
   <td><code>g{2}</code> 匹配含有 2 个 g 的字符串</td> 
  </tr> 
  <tr> 
   <td><code>{n,m}</code></td> 
   <td>匹配前面的字符串至少 n 次，至多m 次</td> 
   <td><code>f{1,3}</code> 匹配 f 最少 1 次，最多 3 次</td> 
  </tr> 
 </tbody> 
</table>

SELECT "字段" FROM "表名" WHERE "字段" REGEXP {模式};
    	
    例如
    SELECT * FROM store_info WHERE Store_Name REGEXP 'os';
    SELECT * FROM store_info WHERE Store_Name REGEXP '^[A-G]';
    SELECT * FROM store_info WHERE Store_Name REGEXP 'Ho|Bo';

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Su3E3sYP-1687331282234)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230621143911248.png)\]][img-Su3E3sYP-1687331282234_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621143911248.png]

## 六、存储过程 ##

### 1. 存储过程概念 ###

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

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

### 2. 存储过程的优点 ###

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

简单来说：存储过程就是sql语句的脚本。

### 3. 存储过程相关操作 ###

#### 3.1 创建存储过程 ####

delimiter $$							#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
    create procedure proc()					#创建存储过程，过程名为Proc，不带参数
    -> begin								#过程体以关键字 BEGIN 开始
    -> select * from store_info;			#过程体语句
    -> end $$								#过程体以关键字 END 结束
    delimiter ;								#将语句的结束符号恢复为分号

![在这里插入图片描述][c99d8f0452e6405ab75e107aaa122ea0.png_pic_center]

创建过程需要数据库的支持，可以进入某个数据库创建或者`create procdure 库名.proc（）`。

#### 3.2 调用存储过程 ####

call proc;

![在这里插入图片描述][281949cb45c64e28b296b9719464f111.png_pic_center]

#### 3.3 查看存储过程 ####

show create procedure [数据库.]存储过程名;		#查看某个存储过程的具体信息
    
    例如
    show create procedure proc;					#查询存储过程内的具体信息
    show procedure status like '%proc%' \G	#模糊查询某个存储过程

![在这里插入图片描述][3481f140992c41a6b62973107c9590d6.png_pic_center]

#### 3.4 存储过程的参数 ####

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>IN （输入参数）</td> 
   <td>表示调用者向过程传入值（传入值可以是字面量或变量）</td> 
  </tr> 
  <tr> 
   <td>OUT （输出参数）</td> 
   <td>表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）</td> 
  </tr> 
  <tr> 
   <td>INOUT （输入输出参数）</td> 
   <td>既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）</td> 
  </tr> 
 </tbody> 
</table>

delimiter $$
    create procedure proc1(in input_name char(10))		
    -> begin					
    -> select * from store_info where store_name = input_name;
    -> end $$					
    delimiter ;					
    
    call proc1('Boston');

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PdjMlNQR-1687501808468)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230623141017704.png)\]][img-PdjMlNQR-1687501808468_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230623141017704.png]

delimiter $$
    create procedure proc2(in input_name char(10), out outsales int)
    -> begin
    -> select sales into outsales from store_info where store_name = input_name;
    -> end $$
    delimiter ;
    call proc2('Boston', @out_sales);					#输出的值只能赋给变量
    select @out_sales;

![在这里插入图片描述][0f16c21e6eae424c83cd5fdab8626a71.png_pic_center]

delimiter $$
    create procedure proc3(inout insales char(10))
    -> begin
    -> select sales into insales from store_info where store_name = insales;
    -> end $$
    delimiter ;
    set @inout_sales='Boston';
    call proc3(@inout_sales);
    select @inout_sales;

![在这里插入图片描述][575b7954f1a3453090a1318437f587f5.png_pic_center]

#### 3.5 删除存储过程 ####

> 存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

drop procedure if exists proc;		
    #仅当存在时删除，不添加 IF EXISTS 时，如果指定的过程不存在，则产生一个错误

![在这里插入图片描述][e4de2aeca66344cb88f9b77e1ca94943.png_pic_center]

#### 3.6 控制语句 ####

##### 条件语句 #####

if-then-else ···· end if 
    
    例如
    create table game (name varchar(10), sex char(2), team char(3));
    desc game;
    insert into game values ('小红','女','女子组');
    insert into game values ('小白','男','男子组');
    select * from  game;
    
    delimiter $$
    create procedure proc4(in input_name varchar(10), in in_sex char(2))
     -> begin
     -> if in_sex = '男' then
     -> insert into game value (input_name, in_sex, '男子组');
     -> else
     -> insert into game value (input_name, in_sex, '女子组');
     -> end if;
     -> end$$
    delimiter ;
    
    call proc4('小绿','男');
    call proc4('小黄','女');
    select * from  game;

![在这里插入图片描述][02fcecef38444ab7a1f58401c98f8ab2.png_pic_center]

##### 循环语句 #####

while ···· end while
    
    例如
    create table t (id int(10));
    insert into t values(10);
    DELIMITER $$  
    CREATE PROCEDURE proc5()
    -> begin 
    -> declare var int(10);  
    -> set var=0;  
    -> while var<6 do  
    -> insert into t values(var);  
    -> set var=var+1;  
    -> end while;  
    -> end $$  
    
    DELIMITER ;
    
    CALL proc5;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BQi8Y2Ck-1687501808469)(C:\\Users\\86138\\AppData\\Roaming\\Typora\\typora-user-images\\image-20230623142858680.png)\]][img-BQi8Y2Ck-1687501808469_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230623142858680.png]

## 总结 ##

### 1. sql 语句中 HAVING 的用法 ###

having 对 group by 分组后的结果根据条件进行过滤筛选。

### 2. mysql数据库有几种表连接 ###

<table> 
 <thead> 
  <tr> 
   <th>表连接方式</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>inner join(内连接)</td> 
   <td>只返回两个表中联结字段相等的行</td> 
  </tr> 
  <tr> 
   <td>left join(左连接)</td> 
   <td>返回包括左表中的所有记录和右表中联结字段相等的记录</td> 
  </tr> 
  <tr> 
   <td>right join(右连接</td> 
   <td>返回包括右表中的所有记录和左表中联结字段相等的记录</td> 
  </tr> 
 </tbody> 
</table>

### 3. view视图表是否能更改数据 ###

如果视图表保存的字段是原表字段，那么数据可以被修改。

如果字段是通过函数（聚合函数、group by）处理过的字段，那么数据不能被修改。

### 4. 创建存储过程的步骤 ###

*  先用`delimiter`临时修改sql语句的结束符；
 *  使用`create procedure [库名.]存储过程名()` 创建存储过程；
 *  再用`delimiter`把sql语句的结束符改回分号；
 *  使用`call [库名.]存储过程名`调用存储过程。

[img-M8A30HhZ-1687255797624_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140331268.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/33c3b095a9a54a84aa22029fb9cf91a3.png
[img-76Uc2W4D-1687255797625_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140459620.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3d7c46e14b464f2fa0b1125d3c8d6e56.png
[img-swNPEhYH-1687255797625_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140533955.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a542bf51989e420ebd3def07653117b0.png
[img-TB8ttezc-1687255797626_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620140550840.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d0a848d4335945e382569d27a445e0d3.png
[img-MDvqQTZW-1687255797626_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141321876.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9d5a280d7d7a46bb863f1a6583c86183.png
[img-61Kt25MA-1687255797627_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141404552.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ffa9b10c1cef49938f067e2e5fe2d892.png
[img-2JnfgOtj-1687255797627_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141429181.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a3b379aef7ee4bd983031c88bc06e439.png
[img-iOdqgdiw-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141538394.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/88d49819e3d44ac8b149e169a2e97efd.png
[img-nqItwmTs-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141620857.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f3b266926c3842dc82bb5a31173f88c2.png
[img-AnAOrwut-1687255797628_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141645511.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f27d7df4ae274eeda8c9dc393f10a930.png
[img-6cesclfZ-1687255797629_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141747900.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/96459fddb0824b55a5b574a7fc1eae0a.png
[img-V04tfNVS-1687255797629_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620141808995.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a1badc9b598d4fd6ab28cbb27f32df9a.png
[img-9m8oGZmf-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142853498.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/35bcf3d711f649e8b33c2534ff4a7b12.png
[img-dcxEJv40-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142922318.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f608c841ee2e4b8b816e63ea2904feb0.png
[img-aft7VI8S-1687255797630_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620142952378.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a5b91c8c23c0451fa6bb814c1b78f187.png
[img-uV07qn1a-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143001063.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3a22f2ee7b1a48edbd6d46a0d928f3bc.png
[img-uQFEyunh-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143007782.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/624b1fa9ec3f4b34af704f9ec1672362.png
[img-ooZPNcCv-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143050512.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/497206dc0f90409e92138a91ec918221.png
[img-enURmoPY-1687255797631_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143058997.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/39256cbd11a24b94ab27c48536461dd9.png
[img-Q5rRmw9P-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143104492.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/190395169aad42d59186f2a87b5e179e.png
[img-jLr7i60P-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145401175.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e549f2df023f4a03ad1a6d4255e61de8.png
[img-6lMes6ye-1687255797632_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145306764.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/52edc4ab6e0648aea8e3af497fbb870e.png
[img-EfDv9jRu-1687255797633_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620145349286.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/686bc2bb20214c45a65be9fe7ba9eabd.png
[img-n4yj5WC3-1687255797633_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143346203.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/10ba6034906c4e6894d2e37a32b7b6ad.png
[img-izpkQijY-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143450953.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/cdf8784732e047a1afa9f126143204b0.png
[img-CMzpF4bD-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143659443.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f4b3dba9113c47b49c7c763a415b08b9.png
[img-9xevhfWr-1687255797634_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144436274.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/11e41111759749a0b9a100635b6f5146.png
[img-cHBnmPll-1687255797635_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620143841232.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/242b260f6dad4b9882f9a7923fc27f1d.png
[img-QgzP10x4-1687255797635_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144452549.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4a1c1fea75714f68ae6c8017bdc97db5.png
[img-laBozDPj-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144613694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ad537e113ec744038641ce47f9b4d57e.png
[img-yQefuTDH-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144639200.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/725a869f5da24d9d95ce1060bdb88d37.png
[img-Cp4Sh4fA-1687255797636_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144740036.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e8fa4c1d61b440988c7e65ceaef560a1.png
[img-uIGixYUA-1687255797637_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230620144806324.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/cd7ab162499d490d8148f4a76953c900.png
[img-zKKJnPxs-1687331282224_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621133924390.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/03a8de68c16a4258acc51d32a732f113.png
[img-LYfo78zw-1687331282225_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134002192.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/31e9f6009f044e1ab52d27473702843d.png
[img-cn4Os6N0-1687331282226_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134040344.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/31954a818aef4df3a902d884e5be762d.png
[img-V4hXgEOJ-1687331282227_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134133908.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/005480a464794346916c635c57b035c2.png
[596fee0ad8e94cb9a25dacdb1c12db68.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b3e557c2f7d44c88bd70ccac9fc38f15.png
[fcb16a6d1d02456c9eb583771900f99f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d13ccc886c0c482c8f1de632551c458c.png
[img-XVWBVThb-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134536306.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7cdbd4801a98487f963b09ced9800413.png
[img-lW4Q4EbQ-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621134736502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/71f9082902d44e3d8342afcb1e770f10.png
[img-wGvoDKuP-1687331282229_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621140733195.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a2f8bc66d111436a9eeda91f174a5f02.png
[img-cfYqqLRh-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621140913526.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/fab612c8d3244a47a8d67697f1733125.png
[img-8KSlyqNp-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141056847.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f0d314471c1e4eedb5ccf867120270f9.png
[img-1AkybI2B-1687331282230_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141402455.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/813be354f0c84302b39f4c7343d5c572.png
[img-65MtnFor-1687331282231_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141534303.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c860654a3f3c4dd1930e0d04ba290fd5.png
[img-efW8HdBY-1687331282231_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621141857586.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ba4d4ee8a8e94294b35d96e71921aee8.png
[img-7AK5u548-1687331282232_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142020945.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3aad7c54d1424c5f90bb700c9188082d.png
[img-MKKAlwDw-1687331282232_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142117213.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/1e372b6785fa454b9e52d35fe9e9ca52.png
[27f7d20a9be04c0a84ba1d40b2de03c6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/441df6cf19fd4c02bfc8570d1a4b73c7.png
[66b3fe0473fa4bd997fdd6f20c8af533.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/0d2f5f27e1d94068ad962d4ef1d6ebbb.png
[img-qmQI2rj3-1687331282233_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621142906730.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/646e36c3ac3b44f4b6b51093b81bb191.png
[img-U20HtWDi-1687331282234_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621150503892.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/248e08270f65465ebf0306c9cced9a1a.png
[img-Su3E3sYP-1687331282234_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230621143911248.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9e6523a4b78049a290fb116c043a0ca0.png
[c99d8f0452e6405ab75e107aaa122ea0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9c9bdcb637f14c959fee9394ee264f95.png
[281949cb45c64e28b296b9719464f111.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4ed86c2f86a64b96906ce5690ce29f06.png
[3481f140992c41a6b62973107c9590d6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/714e3a90fbc14c959a1626e8dd50f307.png
[img-PdjMlNQR-1687501808468_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230623141017704.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/28a45fa4d811428fb9ad8d1f730770b0.png
[0f16c21e6eae424c83cd5fdab8626a71.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/a07037713f0c4ff79609a5f352a3c214.png
[575b7954f1a3453090a1318437f587f5.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f680801e554945478c0904fd7ee520f1.png
[e4de2aeca66344cb88f9b77e1ca94943.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/36732c62e16248beb3bb2326723f80af.png
[02fcecef38444ab7a1f58401c98f8ab2.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b4af62760b91465b8587d67da1864f30.png
[img-BQi8Y2Ck-1687501808469_C_Users_86138_AppData_Roaming_Typora_typora-user-images_image-20230623142858680.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/db11bc1867cd450aa7b2db088718ad6f.png