MySQL(三)

enginelong

已于 2022-03-22 00:25:46 修改

阅读量234

点赞数

分类专栏：数据库文章标签： mysql sql

于 2021-03-20 00:30:33 首次发布

本文链接：https://blog.youkuaiyun.com/m0_46278903/article/details/115017102

版权

数据库专栏收录该内容

5 篇文章

订阅专栏

文章目录

表连接
视图
函数与存储过程

关于MySQL更为基础的部分请见 enginelong博客

表连接

如果多个表存在关联联系，可以将多表以某种连接方式组合起来进行查询。实际上，表的关联与外键约束之间并没有必然联系，但是基于外键约束的具有关联性的表往往可以更多的使用关联查询。

内连接

select字段列表 from 表1  inner join 表2 ON 表1.字段 = 表2.字段;

笛卡尔积
笛卡尔积是将表1的每一条记录与表2的每一条记录强行拼接在一起，因此，假设表1有n条记录，表2有m条记录，笛卡尔积产生的结果将会有n*m条记录。实际上，笛卡尔积用的并不多。
左连接
左表全部显示，右表只显示与左表相匹配的记录

select 字段列表 from 表1 left join 表2 on 表1.字段 = 表2.字段;

右连接
与左连接相反，右表全部显示，左表只显示与右表相匹配的记录

select 字段列表 from 表1 right join 表2 on 表1.字段 = 表2.字段;

【注意】
尽量使用数据大的表作为基准表，即将数据量较大的表当作左表，以提高效率

视图

视图是存储的查询语句，当调用时，产生结果集，视图充当的是虚拟表的角色。实际上，视图可以理解为从一个表或者多个表中导出来的表，作用和真实表一致，用户同样可以使用SELECT、INSERT、UPDATE、DELETE等方法。视图可以使得用户操作更方便，并保障数据库系统的安全，因为拥有不同权限的用户有各自不同的数据视图。如果原表改名或者删除则视图也自动失效。

视图作用
- 优点
1. 视图是数据的一种重构，不影响原数据表的使用；
2. 视图是简化复杂操作的过程，就像一种对复杂操作的封装；
3. 通过提供给不同用户的视图提高了数据的安全性；
4. 视图使得数据管理更加清晰
- 缺点
  视图的性能相对较差，从数据库视图查询数据速度可能较慢
创建视图

语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [查询语句];

释义：

CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

e.g.
create view  c1 as select name,age from class;

视图表的增删改查操作
同普通视图一致。

【注意】
原数据表的约束条件仍然对视图产生作用

查看现有视图

show full tables in 数据库名 where table_type like 'VIEW';

删除视图

drop view [if exists] 视图名;

【注意】
IF EXISTS表示如果存在，这样即使数据库中没有指定删除的视图也不会报错

修改视图

alter view 视图名 as select 字段1、字段2... from 表名;

函数与存储过程

存储过程和函数是事先经过编译并存储在数据库种的一段SQl语句集合，调用存储过程和函数可以简化应用开发工作，以此提高数据处理的效率。

函数

函数创建

delimiter 自定义符合(比如：$)

【注意】
重新定义语句结束符号，是因为函数体内部一般具有多条语句并且均以分号结束，因此重新定义SQL语句结束符号是为了避免在写函数体内部语句时直接执行语句，写完函数体之后可以将符号改回来。

delimiter 自定义符合; 
create function 函数名(形参列表) returns 返回类型
begin
	函数体   -- 函数语句集,set @a 定义变量
	return 返回内容
delimiter ;

【注意】

如果函数体只有一条语句, begin、end、delimiter可以省略；
形参列表：(形参名类型)，类型为MySQL支持类型；
返回类型：函数返回的数据类型，为MySQL支持类型；
函数体：若干SQL语句组成；
return：返回指定类型返回值

e.g. 无参数的函数调用
delimiter $
create function st() returns int 
begin 
	return (select 字段 from 数据表名 order by 排序字段 desc limit 1); 
end $
delimiter ;

select st(); # 使用函数

e.g. 含有参数的函数调用
delimiter $$
create function queryNameById(uid int(10)) 
returns varchar(20)
begin
	return  (select name from class_1 where id=uid);
end $$
delimiter ;

select queryNameById(1);

设置变量
- 用户变量方法：set@[变量名] = 值; 使用时用 @[变量名];
- 局部变量：在函数内部设置declare [变量名] [变量类型]; 局部变量可是使用set 赋值或者使用 into关键字

存储过程

创建存储过程语法与创建函数基本相同，但是存储过程没有返回值

存储过程创建

delimiter 自定义符号　

　　create procedure 存储过程名(形参列表)

　　begin

　　　　存储过程　　　　-- 存储过程语句集,set @a 定义变量

　　end  自定义符号

delimiter ;

【注意】

形参列表：[ IN | OUT | INOUT ] 形参名类型， in 输入，out 输出，inout 可以输入也可以输出

e.g. 存储过程创建和调用
delimiter $$
create procedure st() 
begin 
    select 字段名 from 表名; 
	...
end $$
delimiter ;

call st(); # 调用存储过程

存储过程三个参数的区别
- IN：表示可以接收变量亦可以接收常量，传入参数在存储过程内部使用即可，但是在存储过程内部的修改无法传递到存储过程外部；
- OUT：表示只能接收一个变量，接收的变量不能够在存储过程内部使用(内部默认为NULL)，但是可以在存储过程内部对此变量进行修改，因为定义的变量是全局的，因此外部可以使用修改后的变量；
- INOUT：表示只能接收一个变量，但是此变量亦可以在存储过程内部使用。在外部同样可以获取修改后的变量的值。

delimiter $$
create procedure p_out ( OUT num int )
begin
    select num;
    set num=100;
    select num;
end $$
delimiter ;

set @num=10; # 存储过程之外创建变量
call p_out(@num)

存储过程和存储函数操作

调用存储过程

call 存储过程名([存储过程的参数,...]);

调用函数

select 函数名([函数参数,...]);

查看存储过程和函数的信息

show {procedure|function} status [like '存储过程名|函数名'];
# 显示内容：数据库名、名字、类型、创建者、创建日期和修改日期

查看存储过程和函数的定义

show create {procedure|function} 存储过程名|函数名;

查看所有函数或者存储过程

select name from mysql.proc where db='数据库名' and type='[procedure|function]';

删除存储过程或函数

drop {procedure|function} [if exists] 存储过程名|函数名;

存储过程和函数区别
- 函数有且只有一个返回值，而存储过程不能有返回值；
- 函数只能有输入参数，而存储过程可以有IN、OUT、INOUT多个类型参数；
- 存储过程的语句功能更加丰富，可以实现更加复杂的业务逻辑，可以理解为一个按照预定步骤执行的过程，而函数只是在完成查询工作后返回一个结果，功能针对性更强。
- 存储过程一般是作为一个独立的部分进行执行(call调用)，而函数可以作为查询语句的一个部分使用。

进一步的操作请见enginelong的博客
OK…