MySQL高级语句（二）

MySQL高级语句（二）

1.视图

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

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;

#删除视图
drop view new_students;

视图能显示出数据。但是视图中不存储数据,只能存储它的定义

视图表需要删除才会消失，退出或者断开数据库，视图是不会消失的

2. 联集

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

UNION

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

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];

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

UNION ALL

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

#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];

#示例：搜索两个表中courses列的所有记录，不按照顺序
select courseid from coc union all select courseid from courses;

3.case

#语法：

SELECT CASE ("栏位名")

        WHEN "条件1" THEN "结果1"

        WHEN "条件2" THEN "结果2"

        ...

        [ELSE "结果N"]

        END

FROM "表名";

# "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。

4.日期时间函数

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

select curdate();
#当前日期
select curtime();
#当前时间的 时分秒
select now();
#当前时间 年月日时分秒
select month('2021-08-11');
#返回月份
select week('2021-12-02');
#返回一年中的第几天
select hour('2021-12-02 14:18');
#返回小时值
select minute('2021-12-05 14:30:9');
#返回分钟的值
select second()
#返回秒值
select dayofweek('2021-12-05');
#返回星期几
select dayofmonth('2021-12-05');
#返回月中某天的值
select dayofyear('2021-12-05');
#返回一年中的 第几天

5.regexp正则表达式

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

6.存储过程

使用方法 是shell脚本

格式和函数很像

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

存储过程优势：

1. 封装性

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

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

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

3. 可减少网络流量

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

4. 提高性能

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

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

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

6. 使数据独立

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