关系型数据库mysql（8）sql高级语句②

一.子查询——Subquery

语法

环境准备

In——查询已知的值的数据记录

子查询——Insert

子查询——Update

子查询——Delete

Not In——表示否定，不在子查询的结果集里

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

一.子查询——Subquery

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

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

子语句中的SQL语句是为了最后过滤出一个结果集，用于主语句的判断条件；

in是作为主表和另一个表的连接的语法

语法

IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用
 
语法：
<表达式> [NOT] IN <子查询>

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE。若启用了 NOT 关键字，则返回值相反。需要注意的是，子查询只能返回一列数据，如果需求比较复杂，一列解决不了问题，可以使用多层嵌套的方式来应对。多数情况下，子查询都是与 SELECT 语句一起使用的
子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。

环境准备

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school
Database changed
mysql> create table class1(id int, name varchar(16),score char(10),address varchar(20),cardid int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into class1 values(1,'zhangsan',80,'nanjing',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class1 values(2,'lisi',80,'beijing',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class1 values(3,'wanghan',95,'chengdu',2);
Query OK, 1 row affected (0.01 sec)


mysql> insert into class1 values(4,'wanghan',95,'chengdu',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into class1 values(5,'tianqi',90,'shandong',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class1 values(6,'wangba',90,'beijing',3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from class1;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 80    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    3 | wanghan  | 95    | chengdu  |      2 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
6 rows in set (0.00 sec)

mysql> create table class2(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into class2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from class2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from class3
    -> ;
+------+--------+-------+----------+--------+
| id   | name   | score | address  | cardid |
+------+--------+-------+----------+--------+
|    6 | wangba | 90    | beijing  |      3 |
|    3 | wangwu | 95    | chengdu  |      2 |
|    5 | tianqi | 90    | shandong |      3 |
+------+--------+-------+----------+--------+
3 rows in set (0.00 sec)

In——查询已知的值的数据记录

IN 用来判断某个值是否在给定的结果集中，通常结合子查询来使用

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

mysql> select id,name,score from class where id in(select id from class2);
#先查询class2数据表中的id字段列  将查询到的结果id字段列作为一个已知的值的数据记录；再根据已知的值的数据记录查询class1数据表中id,name,score字段列

select id,name,score from class1 where id in(select id from class3 where score e >= 90);

#先查询class3数据表判断条件为 score分数大于等于90分的数据作为id列  以此作为查询条件从class1数据表中获取数据

select id,name,score from class1 where name in(select name from class3 where score >= 90);

子查询的判断条件主查询语句必须与子查询语句中的字段一致

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

子查询——Insert

子查询还可以用在 INSERT 语句中。子查询的结果集可以通过 INSERT 语句插入到其他的表中

将class3变成空表

mysql> select * from class3;
+------+--------+-------+----------+--------+
| id   | name   | score | address  | cardid |
+------+--------+-------+----------+--------+
|    6 | wangba | 90    | beijing  |      3 |
|    3 | wangwu | 95    | chengdu  |      2 |
|    5 | tianqi | 90    | shandong |      3 |
+------+--------+-------+----------+--------+
3 rows in set (0.00 sec)

mysql> delete from class3;
Query OK, 3 rows affected (0.00 sec)

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

mysql> insert into class3 select * from class1 where id in(select id from class1);
Query OK, 6 rows affected (0.00 sec)
#先遍历class1数据表中id的字段列 然后根据查询到的字段列插入到class3数据表中
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from class3;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 80    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    3 | wanghan  | 95    | chengdu  |      2 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
6 rows in set (0.00 sec)

子查询——Update

UPDATE 语句也可以使用子查询。UPDATE 内的子查询，在 set 更新内容时，可以是单独的一列，也可以是多列。

更新class3数据表中根据查询关联表class1数据表id=1的数据内容修改id=1的字段的分数为85分

mysql> select * from class3;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 80    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    3 | wanghan  | 95    | chengdu  |      2 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
6 rows in set (0.00 sec)

mysql> update class3 set score=85 where id in(select id from class1 where id=1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class3;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 85    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    3 | wanghan  | 95    | chengdu  |      2 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
6 rows in set (0.00 sec)

子查询——Delete

先查询class1数据表中得分大于90分的删除class3数据表中大于90分的数据

mysql> select * from class3 where score>90;
+------+---------+-------+---------+--------+
| id   | name    | score | address | cardid |
+------+---------+-------+---------+--------+
|    3 | wanghan | 95    | chengdu |      2 |
+------+---------+-------+---------+--------+
1 row in set (0.00 sec)

mysql>  delete from class3 where id in (select id from class1 where score > 90);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class3;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 85    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
5 rows in set (0.00 sec)

Not In——表示否定，不在子查询的结果集里

在 IN 前面还可以添加 NOT，其作用与IN相反，表示否定（即不在子查询的结果集里面）

删除class数据表条件判断取反遍历score分数小于85分的也就是删除class3数据表中分数大于85分的所有数据

mysql> select * from class3;
+------+----------+-------+----------+--------+
| id   | name     | score | address  | cardid |
+------+----------+-------+----------+--------+
|    1 | zhangsan | 85    | nanjing  |      1 |
|    2 | lisi     | 80    | beijing  |      1 |
|    4 | zhaoliu  | 88    | shanghai |      2 |
|    5 | tianqi   | 90    | shandong |      3 |
|    6 | wangba   | 90    | beijing  |      3 |
+------+----------+-------+----------+--------+
5 rows in set (0.00 sec)

mysql> delete from class3 where id not in(select id where score <85);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from class3;
+------+------+-------+---------+--------+
| id   | name | score | address | cardid |
+------+------+-------+---------+--------+
|    2 | lisi | 80    | beijing |      1 |
+------+------+-------+---------+--------+
1 row in set (0.00 sec)

Exists——判断查询结果集是否为空

先查询class数据表中id字段列判断score分数是否大于80分 exists判断子查询结果是否为空，如果为空返回false 子查询结果为空那么不执行class3数据表的求和

mysql> select sum(score) from class3 where exists(select id from class3 where score > 80);
+------------+
| sum(score) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

先查询class3数据表中id字段列判断条件score分数是否大于80 exists判断子查询结果为空依旧不执行class1数据表的sum求和

mysql> select sum(score) from class1 where exists(select id from class3 where score > 80);
+------------+
| sum(score) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

先查询class3数据表判断条件为小于等于80 exists子查询结果为TRUE 为真那么执行前面的class1查询语句查询所有字段并且Sum求和

mysql> select sum(score) from class1 where exists(select id from class3 where score <= 80);
+------------+
| sum(score) |
+------------+
|        523 |
+------------+
1 row in set (0.00 sec)

子查询——别名

报错的原因是子查询语句中select查询的表名是一个完整的结果集，主查询语句不能识别子查询的结果（不能识别子查询语句输出的整个数据表的数据内容）

给class1做一个s的别名

select * from 表名此为标准格式，而以上的查询语句，"表名"的位置其实是一个完整结果集，mysql并不能直接识别，而此时给与结果集设置一个别名 s 以”select s.id from s“的方式查询将此结果集视为一张"表"，就可以正常查询数据了.

二.视图

理论：

视图：优化操作+安全方案
数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射

作用场景[图]:
针对不同的人（权限身份），提供不同结果集的“表”（以表格的形式展示）
作用范围：

select * from class1;			#展示的部分是class1表
 
select * from view_name;	  #展示的一张或多张表

功能：
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
本质而言视图是一种 select (结果集的呈现)

注意：

视图适合于多表连接浏览时使用，不适合增、删、改
而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率!

视图和表的区别和联系

区别

视图是已经编译好的sql语句。而表不是
视图没有实际的物理记录。而表有
表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构
表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表
视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

联系

视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

操作

创建视图（单表）

create view v_score as select * from class1;

#创建视图  视图表v_score 基于class1数据表而存在

查看视图

mysql> select * from v_score;

查看表状态

show table status\G

创建的视图看不到任何的状态信息

查看视图与源表结构

mysql> desc v_score;
 
mysql> desc class1;

修改原表数据

更改原表数据

可以看到视图跟着原表的数据改动一起改动了

修改视图数据

可以看出修改视图数据，表的数据也会改变

总结

修改表不能修改以函数、符合函数方式计算出来的字段
视图的存在是为了查询更加方便，为了增强安全性

三.Null值——缺失

在SQL语句使用过程中，通常使用NULL表示缺失的值，也就是在表中该字段是没有值的。如果在创建表时，指定某字段不能为空（Not Null），不指定则默认可以为空。在向表中插入记录或者更新记录的时候，如果该字段没有Not Null值，且没有任何值，这时新记录的字段数据为Null。

需要注意的是，NULL 值与数字 0 或者空白（spaces）的字段是不同的，值为 NULL 的字段是没有值的。在 SQL 语句中，使用 IS NULL 可以判断表内的某个字段是不是 NULL 值，相反的用 IS NOT NULL 可以判断不是 NULL 值。

null值与空值的区别

空值长度为0，不占空间，NULL值的长度为null，占用空间
is null 无法判断空值
空值使用 "=“ 或者 ”<>" 来处理（！=）
count（）计算时，NULL会忽略，空值会加入计算

添加新的字段

count计数统计数量的时候 null值不会计数总数

四.连接查询

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

内连接——Inner Join

MySQL 中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表

语法

#语法：
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

左连接——Left Join

左连接也可以被称为左外连接，在 FROM 子句中使用 LEFT JOIN 或者 LEFT OUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行。

语法

mysql> select a.a_id,a.a_name from test1 a left join test2 b on a.a_name=b.b_name;

左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为 NULL。

右连接——Right Join

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

语法

mysql> select a.a_id,a.a_name from test1 a right join test2 b on a.a_name=b.b_name;

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

五.存储过程

1.概述

存储过程是一组为了完成特定功能的SQL语句集合。两个点第一触发器（定时任务）第二个判断
存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

存储过程在数据库中L 创建并保存，它不仅仅是 SQ语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、在不同的应用程序或平台上执行相同的函数等等。

2.存储过程的优点

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

3.语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

mysql> delimiter $$
#将语句的结束符号从分号;临时改为两个$$(可以自定义)
mysql> create procedure kgc()
#创建存储过程，过程名为kgc，不带参数
    -> begin
#过程体以关键字 BEGIN 开始
    -> create table class4(id int,name varchar(8),score decimal(5,2));
    -> insert into class4 values(1,'zhangsan',80);
    -> insert into class4 values(2,'lisi',70);
    -> select * from class4;
#过程体语句
    -> END $$
#过程体以关键字 END 结束
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;
#将语句的结束符号恢复为分号

mysql> delimiter $$
mysql> create procedure kgc()
    -> begin
    -> create table class4(id int,name varchar(8),score decimal(5,2));              
    -> insert into class4 values(1,'zhangsan',80);                                  
    -> insert into class4 values(2,'lisi',70);
    -> select * from class4;                                                        
    -> end $$                                                                       
Query OK, 0 rows affected (0.01 sec)


mysql> delimiter ;

调用存储过程

call Procedure_name();

查看存储过程

show create procedure Procedure_name

查看指定存储过程

删除存储过程：

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

DROP PROCEDURE IF EXISTS Proc;

参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量）

定义方式与作用域不同。形参是在函数定义中声明的，用于在函数体内进行使用，但它们只在函数执行期间存在，函数调用结束后，形参的作用域结束。实参则是在主调函数中定义的，用于传递值给形参，在函数调用期间，实参的值被传递给形参。

内存占用不同。形参本质上是一个名字，它不占用内存空间，直到函数被调用时，形参才被分配内存空间。实参则是一个已经占用内存空间的变量，它在程序运行期间一直存在。

传递机制不同。在传值调用中，只有实参的值被使用；而在引用调用中，实参的地址被传递给形参，这意味着对形参的任何修改都会影响到实参本身。

六.总结

1.子查询

In：查询已知的值数据记录
Not In：表示否定，不存在子查询结果集里（相当于取反）
Exists：判断查询结果集是否为空（空返回False，非空返回TRUE）

2.视图——View

无论修改视图还是原表数据，都会随之一起更改

3.缺失值——Null

Null：占用空间Null值
空值：占用空间为0

4.连接查询——内左右

内连接：Inner Join
左连接：Left Join
右连接：Right Join

5.存储过程——Procedure

创建

mysql> delimiter $$
#将语句的结束符号从分号;临时改为两个$$(可以自定义)
mysql> create procedure kgc()
#创建存储过程，过程名为kgc，不带参数
    -> begin
#过程体以关键字 BEGIN 开始
    -> create table class4(id int,name varchar(8),score decimal(5,2));
    -> insert into class4 values(1,'zhangsan',80);
    -> insert into class4 values(2,'lisi',70);
    -> select * from class4;
#过程体语句
    -> END $$
#过程体以关键字 END 结束
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;
#将语句的结束符号恢复为分号