一、 CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

（3）视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

#语法：
   CREATE VIEW "视图表名" AS "SELECT 语句";
#示例1：创建视图表，表名为new_students，数据是跟在as后面的select语句，scores表的别名为a， 
        students表的别名为b；将a表和b表等值连接，连接字段为stuid，将a表的score和stuid列显示到新 
        表中为score和stuid；将b表中的name列显示到新表为name
   create view new_students as select a.score score,a.stuid stuid,b.name name from scores a 
   inner join students b on a.stuid=b.stuid;
#查看视图
  select * from  new_students;
#删除视图
  drop view new_students;

二、联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

2.1UNION

生成结果的资料值将没有重复，且按照字段的顺序进行排序

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];
#示例：搜索两个表中courses列的所有记录，先安装原有顺序列出第一个select语句中的数据，再将第二个表 
       中的数据对照第一个表的数据去重后按源于的顺序列出
select courseid from coc  union select courseid from courses;

2.2UNION ALL

将生成结果的资料值都列出来，无论有无重复

#语法
  [select 语句1] union all [select 语句2];
#按照顺序,不去重
  select courseid from coc union all select courseid from courses;

三、交集值

取两个SQL语句结果的交集

# destination 表别名为a info别名为b  先将a表和b表的place_name等值连接,找出相同字段 再返回a表中的这些字段
select a.place_name from destination a inner join info b on a.place_name = b.place_name;
#使用using(courseid代替 on a.courseid=b.courseid,效果一样)
select a.courseid from courses a inner join coc b using(courseid);

#首先执行中间()内的语句,将food表和info表的数据汇总在一起,输出的内容定义为别名a
 通过group by和having进行汇总,判断a表中是否有重复然后输出重复的字符串
 但这里有这个缺点,在info表中添加的两行相同的数据,但是food表中没有此记录,也被列出,但那个相同的数据不属于交集。
select a.city from( select city from food union all select city from info) a group by a.city having count(*) > 1;

取两个SQL语句结果的交集，且没有重复

#()是将food表和info表等职连接,查询的结果定义为别名a外面select语句是查询a里的city列分类汇总的显示结果
select a.city from (select b.city from food b inner join info c on b.city=c.city) a group by a.city;

#这就是上面取交集的结果,但是加了distinct去重
select distinct a.city from food a inner join info b using(city);

#先查出food表中的city列,再对比info表中city列的字段,列出两个表相同的字段最后去重
select distinct city from food where (city) in (select city from info);

#food别名a,info别名b;根据字段city使两个表左连接where后面是只显示b表中有字符的行 然后disinct去重显示结果
select distinct a.city from food a left join b using(city) where b.city is not null;

四、无交集值

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

#对比两个表中city列,显示出food表中有的，info表中没有的,最后去重
select distinct city from food where (city) not in (select city from  info);

#food别名a，info别名b 将两个表左连接,去重显示a表的city列(where 条件是b表中city为 null)
select distinct a.city from food a left join info b using(city) where b.city is null;

五、CASE

CASE是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字

#语法：
SELECT CASE ("栏位名")
  WHEN "条件1" THEN "结果1"
  WHEN "条件2" THEN "结果2"
  ...
  [ELSE "结果N"]
  END
FROM "表名";
# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

#示例：
select city, case city                #“ city ”对city进行判读  
  when '南京' then price - 50         #当city为南京时,price-50;
  when '北京' then price - 20         #当city为北京时,price-20;
  else price                          #其他的不变,可以不加
  end                                 #结束 
"new price",date                      #跟在city后面显示列名
from info;                            #from 表info
#"new price" 是用于 CASE 那个栏位的栏位名。

六、表格内容排序

#新建一个表格
create database test1;
use  test1;
insert into class values('小明',88);
insert into class values('小红',76);
insert into class values('小斌',90);
insert into class values('小云',64);
insert into class values('小芳',73);
insert into class values('小龙',52);
insert into class values('小伟',99);
select * from class;

6.1 算排名

表格自我连结 (Self Join)，然后将结果依序列出，算出每一行之前 (包含那一行本身) 有多少行数

#表class别名为a1,a2将自己看成两张表  查询a1的name,a1的scores,统计(a2的scores中的非空值) 别名为rank当a1.scores小于a2.scores或者(a1,a2的scores和name字符都相当)计入统计对a1.name,a1.scores进行汇总分组,然后对a1.scores进行降序排序
select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name = a2.name) group by a1.name,a1.scores order by a1.scores desc;

6.2 算中位数

#给算好排名表定义别名a3表
select scores middle from ( select a1.name,a1.scores,count(a2.scores) rank from class a1,class a2a1.scores < a2.scores or (a1.scores = a2.scores and a1.name <= a2.name) group by a1.name ,a1.scores order by a1.scores desc) a3 where a3.rank = (select (count(*)+1) div 2 from class);

6.3算累积总计

#在算排名的基础上改一下
select a1.*,sum(a2.scores) sum_scores from class a1,class a2 where a1.scores < a2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

6.4.算总合百分比

#SELECT SUM(Sales) FROM Total_Sales 这一段子查询是用来算出总合
#总合算出后，我们就能够将每一行一一除以总合来求出每一行的总合百分比
select a1.*,a1.scores/(select sum(scores) from class) per_sum from class a1,class a2 where a1.sco2.scores or(a1.scores = a2.scores and a1.name = a2.name) group by a1.name order by a1.scores desc;

取小数点后几位数

#使用truncate(sum(),2) 来取小数点后两位, ||为连接符来%
select a1.name,a1.scores,sum(a2.scores),truncate(sum(a2.scores)/(select sum(scores) from class),2) ||'%' per_sum from class a1,class a2 where a1.scores < a2.scores or (a1.scores = a2.scores and a1.name=a2.name) group by a1.name order by a1.scores desc;

七、空值(NULL) 和无值(’ ') 的区别

（1）无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。

（2）IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。

（3）无值的判断使用=’‘或者<>’'来处理。<> 代表不等于。

（4）在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。

#建立一个表
create table info(name char(100));
insert into info values('小明');
insert into info values('小红');
insert into info values(null);
insert into info values('小云');
insert into info values('');
insert into info values(null);
insert into info values('小伟');

#示例1：查看‘NULL’，‘’，‘123’所占用的字符
select length (NULL),length(''),length('123');
#示例2：查看空值和非空值
select * from info where name = '';
select * from info where name <> '';
#示例3：统计行数
select COUNT(*) from info;
#指定字段时,null会自动忽视,但空值不会忽略,会加入到记录中
select COUNT(name) from info;

八、正则表达式

#语法:
SELECT "栏位”FROM "表名" WHERE "栏位”REGEXP {模式};
#示例1：查看数据表中的name字段
select name from students;
#示例2：返回表中name中字符带'ua'的数据的全部字段
select * from students where name regexp 'ua';
#示例3：返回表中字段中以'A-G'开头的数据，不区分大小写
select * from students where name regexp '^[A-G]';
#示例4：返回表中字段中以'du'和'hu'开头的全部信息
select * from students where name regexp '^(du|hu)';

#示例1：显示当前日期
select curdate();
#示例2：显示当前的 时分秒
select curtime();
#示例3：返回月份
select month('2021-12-8');
#示例4：返回一年中的第几周
select week('2021-12-8');
#示例5：返回小时值
select hour('2021-12-8 13:42:21');
#示例6：返回星期几
select dayofweek('2021-12-8 13:42:21');
#示例7：返回在这个月的第几天
select dayofmonth('2021-12-8 13:42:21');
#示例8：返回在这一年的第几天
select dayofyear('2021-12-8 13:42:21');

十、存储过程

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

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

10.1 存储过程的优点

1.封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

2.可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3.可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4.提高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

5.提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

6.使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

10.2 创建、调用和查看存储的过程

1.创建存储过程

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

1) 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2) 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

3) 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $

语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符
成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格

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

2.调用存储过程

#语法
call 过程名
#示例：
call proc5

3.查看存储过程

#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;
##示例1：
show create procedure hellodb.proc5\G;
##示例2：再查看就没有了
show create procedure proc5\G;

4.删除存储过程

#语法
DROP PROCEDURE IF EXISTS 过程名;
#示例：删除过程
drop procedure if exists proc5;

10.3 存储过程的参数

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

#示例：
#创建存储过程proc11,参数为inname,存储体过程为从students表中查看是否有符号name的数据
delimiter $$                                                                        
create procedure proc11(in inname char(40))
-> begin
-> select * from students where name = inname;
-> end $$ 
mysql> delimiter ;
call proc11('Xi Ren');  #调出存储过程，参数为  ‘Xi Ren’

10.4 存储过程的控制语句

10.4.1 条件控制语句 if-then-else … end if

delimiter $$                    #修改默认结束符为$$
create procedure proc12(in pro int)         #创建存储过程proc2，参数为pro，类型为int
-> begin                      #过程体以关键字begin开始
-> declare var int;                 #定义变量var为int类型
-> set var=pro*3;                 #设置变量var等于传入参数的3倍
-> if var>=10 then                  #如果变量var大于10，则执行下面过程体
-> update students set age=age+10;            #设置表students中的age加10
-> else                       #如果变量var不大于10，则执行下面过程体
-> update students set age=age-10;          #设置表students中的age减10
-> end if;                      #结束if语句
-> end $$                       #创建存储过程结束
delimiter ;                     #重新修改默认结束符为原始的;
CALL proc12(4);                   #调用proc2存储过程，并传入参数4

10.4.2 循环语句 while … end while

#创建表
create table nametest(id int auto_increment primary key,name char(40),age int default 20);
DELIMITER $$                      #修改默认结束符为$$
-> create procedure yxp()               #创建存储过程yxp
-> begin                        #过程体以关键字begin开始
-> declare i int;                 #定义变量i为int类型（长度最大为10）
-> set i = 1;                       #设置i = 1;
->  while i <= 100                  #使用while循环，i要小于100
-> do insert into nametest(name,age) values
      #满足条件则进行添加数据，内容为变量i
-> set i=i+1;                   #变量i每次循环后加1               
-> end while;                       #结束while循环
-> end $$                       #创建存储过程结束
delimiter ;                       #重新修改默认结束符为原始的;
CALL proc6;                       #调用yxp存储过程

MySQL数据库系列(八)-------高级语句二

一、 CREATE VIEW（视图）

二、联集

2.1UNION

2.2UNION ALL

三、交集值

取两个SQL语句结果的交集

取两个SQL语句结果的交集，且没有重复

四、无交集值

五、CASE

六、表格内容排序

6.1 算排名

6.2 算中位数

6.3算累积总计

6.4.算总合百分比

七、空值(NULL) 和无值(’ ') 的区别

八、正则表达式

十、存储过程

10.1 存储过程的优点

1.封装性

2.可增强 SQL 语句的功能和灵活性

3.可减少网络流量

4.提高性能

5.提高数据库的安全性和数据的完整性

6.使数据独立

10.2 创建、调用和查看存储的过程

1.创建存储过程

2.调用存储过程

3.查看存储过程

4.删除存储过程

10.3 存储过程的参数

10.4 存储过程的控制语句

10.4.1 条件控制语句 if-then-else … end if

10.4.2 循环语句 while … end while

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

MySQL数据库系列(八)-------高级语句二

一、 CREATE VIEW（视图）

二、联集

2.1UNION

2.2UNION ALL

三、交集值

取两个SQL语句结果的交集

取两个SQL语句结果的交集，且没有重复

四、无交集值

五、CASE

六、表格内容排序

6.1 算排名

6.2 算中位数

6.3算累积总计

6.4.算总合百分比

七、空值(NULL) 和 无值(’ ') 的区别

八、正则表达式

十、存储过程

10.1 存储过程的优点

1.封装性

2.可增强 SQL 语句的功能和灵活性

3.可减少网络流量

4.提高性能

5.提高数据库的安全性和数据的完整性

6.使数据独立

10.2 创建、调用和查看存储的过程

1.创建存储过程

2.调用存储过程

3.查看存储过程

4.删除存储过程

10.3 存储过程的参数

10.4 存储过程的控制语句

10.4.1 条件控制语句 if-then-else … end if

10.4.2 循环语句 while … end while

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

七、空值(NULL) 和无值(’ ') 的区别