MYSQL多表查询、视图、函数、存储过程、流程控制、触发器

最新推荐文章于 2022-02-20 15:51:12 发布

zerocdn

最新推荐文章于 2022-02-20 15:51:12 发布

阅读量461

点赞数

分类专栏： MYSQL 文章标签： mysql linux

本文链接：https://blog.csdn.net/zerocdn/article/details/103800046

版权

MYSQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

多表查询

多表纵向合并

让多张表可以纵向合并，并且去重

union示例：

select * from test union select * from test2;

如果不想去重加上all
select * from test union all select * from test2;

交叉连接

交叉连接也叫笛卡尔乘积，一般很少用

cross join示例：

test表
id		name		age		city		typeid
1		zhangsan	20		beijing		1
2		xiaoming	20		shanghai	2
3		lisi		21		beijing		1
4		wangwu		22		shanghai	2

test2表
id	type		code
1	a1			10
2	a2			11

select * from test cross join test2;
查询结果：
id		name		age		city		typeid	id	type		code
1		zhangsan	20		beijing		1		1	a1			10
1		zhangsan	20		beijing		1		2	a2			11
2		xiaoming	20		shanghai	2		1	a1			10
2		xiaoming	20		shanghai	2		2	a2			11
3		lisi		21		beijing		1		1	a1			10
3		lisi		21		beijing		1		2	a2			11
4		wangwu		22		shanghai	2		1	a1			10
4		wangwu		22		shanghai	2		2	a2			11

内连接

内连接：
等值连接：让表之间的字段以“等值”建立连接关系；
不等值连接
自然连接:去掉重复列的等值连接
自连接

内连接inner join示例：

test表
id		name		age		city		typeid
1		zhangsan	20		beijing		1
2		xiaoming	20		shanghai	2
3		lisi		21		beijing		2
4		wangwu		22		shanghai	3

test2表
id	type		code
1	a1			10
2	a2			11

select * from test inner join test2 on test.typeid=test2.id;
相等的旧写法
select * from test,test2 on test.typeid=test2.id;
查询结果：
id		name		age		city		typeid	id	type		code
1		zhangsan	20		beijing		1		1	a1			10
2		xiaoming	20		shanghai	2		2	a2			11
3		lisi		21		beijing		2		2	a2			11

使用别名写法
select t1.id,t1.name,t1.age,t1.city,t1.typeid,t2.type,t2.code from test as t1 inner join test2 as t2 on t1.typeid=t2.id;
查询结果：
id		name		age		city		typeid	type		code
1		zhangsan	20		beijing		1		a1			10
2		xiaoming	20		shanghai	2		a2			11
3		lisi		21		beijing		2		a2			11

自连接示例：

test表
id	name	lid
1	a		null
2	b		1
3	c		2

用内连接的方式自连接
select t1.name,t2.name from test t1,test t2 on t1.lid=t2.id;
查询结果：
name	name
b		a
c		b

除此之外也可以用左外连接的方式来自连接

左右外连接

左外连接：左边的表全要，右边的表只取交集
右外连接则反过来
示例：

test表
id		name		age		city		typeid
1		zhangsan	20		beijing		1
2		xiaoming	20		shanghai	2
3		lisi		21		beijing		2
4		wangwu		22		shanghai	3

test2表
id	type		code
1	a1			10
2	a2			11
4	x			15

左外连接
select t1.id,t1.name,t1.age,t1.city,t1.typeid,t2.type,t2.code from test as t1 left outer join test2 as t2 on t1.typeid=t2.id;
查询结果：
id		name		age		city		typeid	type		code
1		zhangsan	20		beijing		1		a1			10
2		xiaoming	20		shanghai	2		a2			11
3		lisi		21		beijing		2		a2			11
4		wangwu		22		shanghai	3		null		null

左外连接后过滤结果
select t1.id,t1.name,t1.age,t1.city,t1.typeid,t2.type,t2.code from test as t1 left outer join test2 as t2 on t1.typeid=t2.id where t2.type is null;
查询结果：
id		name		age		city		typeid	type		code
4		wangwu		22		shanghai	3		null		null

右外连接
select t1.id,t1.name,t1.age,t1.city,t1.typeid,t2.type,t2.code from test as t1 right outer join test2 as t2 on t1.typeid=t2.id;
查询结果：
id		name		age		city		typeid	type		code
1		zhangsan	20		beijing		1		a1			10
2		xiaoming	20		shanghai	2		a2			11
3		lisi		21		beijing		2		a2			11
null	null		null	null		null	x			15

完全外连接

完全外连接会把有连接的地方和没有连接的都显示出来
示例：

test表
id		name		typeid
1		zhangsan	1
2		xiaoming	2
3		lisi		2
4		wangwu		3

test2表
id	type		code
1	a1			10
2	a2			11
4	x			15

select t1.id,t1.name,t1.typeid,t2.type,t2.code from test1 t1 left outer join test2 on t1.typeid=t2.id
union
select t1.id,t1.name,t1.typeid,t2.type,t2.code from test1 t1 right outer join test2 on t1.typeid=t2.id;
查询结果：
id		name		typeid	type		code
1		zhangsan	1		a1			10
2		xiaoming	2		a2			11
3		lisi		2		a2			11
4		wangwu		3		null		null
null	null		null	x			15

三表连接

用内连接来连接3张表
示例：

test表，存放学生信息
id	name
1	张三
2	李四
3	小明
4	小红

test2表，存放课程信息
id	class
1	linux
2	python
3	java

test3表，存放成绩信息
id	sid	cid	num
1	1	1	80
2	1	2	80
3	1	3	80
4	2	1	95
5	2	2	90
6	3	3	75
7	4	1	85

三表连接写法
select t1.name,t2.class,t3.num from test as t1 inner join test3 as t3 on t1.id=t3.sid inner join test2 as t2 on t3.cid=t2.id;
查询结果：
name	class	num
张三	linux	80
张三	python	80
张三	java	80
李四	linux	95
李四	python	90
小明	java	75
小红	linux	85

子查询

子查询是在查询语句嵌套着查询语句，性能较差
写法示例：
select name,age from test where age >(sekect avg(age) from test);

视图

视图：VIEW,虚表，保存有实表的查询结果

创建方法：
CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

示例：

create view test_view as select t1.name,t2.class,t3.num from test as t1 inner join test3 as t3 on t1.id=t3.sid inner join test2 as t2 on t3.cid=t2.id;
创建之后查询结果跟三表连接的结果一样
select * from test_view;
查询结果：
name	class	num
张三	linux	80
张三	python	80
张三	java	80
李四	linux	95
李四	python	90
小明	java	75
小红	linux	85

判断一个表是否是视图可以查看表状态：

show table status like 'test_view';
让输出结果更直观加\G
show table status like 'test_view'\G
在输出结果中有一项comment会显示VIEW

查看视图定义：SHOW CREATE VIEW view_name

删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] …
[RESTRICT | CASCADE]

视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；
其修改操作受基表限制

函数

函数：系统函数和自定义函数
自
定义函数 (user-defined function UDF)保存在mysql.proc表中

创建UDF
CREATE [AGGREGATE] FUNCTION function_name(parameter_name
type,[parameter_name type,…])
RETURNS {STRING|INTEGER|REAL}
runtime_body

说明：
参数可以有多个,也可以没有参数
必须有且只有一个返回值

创建函数
示例：无参UDF
CREATE FUNCTION hello() RETURNS VARCHAR(20) RETURN "Hello
World!”;
函数储存在mysql数据库里的proc表中

查看函数列表：
SHOW FUNCTION STATUS;

查看函数定义
SHOW CREATE FUNCTION function_name

删除UDF:
DROP FUNCTION function_name

调用自定义函数语法:
SELECT function_name(parameter_value,…)

有参数的UDF
DELIMITER //
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNSVARCHAR(20)
BEGIN
DELETE FROM test WHERE sid = uid;
RETURN (SELECT COUNT(sid) FROM test);
END//
DELIMITER ;
解释：

DELIMITER //
原先语句结束是用";"，这条命令改成"//"，如果不这样改在函数中碰到语句结束的分号就会执行了。

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNSVARCHAR(20)
创建deleteById函数，参数是一个正的数字

DELETE FROM test WHERE sid = uid;
删除sid等于参数uid的

RETURN (SELECT COUNT(sid) FROM test);
返回数据条目数

DELIMITER ;
结束符号改回;

自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]

局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义

示例:

DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT
UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;

函数中的变量赋值语法
SET parameter_name = value[,parameter_name = value…]
SELECT INTO parameter_name

示例：

DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;

存储过程

存储过程优势
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
提高了运行速度
同时降低网络数据传输量

存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强
存储过程可以有多个返回值,而自定义函数只有一个返回值
存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用

创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])
routime_body
proc_parameter : [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；
param_name表示参数名称；type表示参数的类型

示例：

无参数存储过程
delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;

调用存储过程
CALL showTime;


只有一个参数的存储过程
delimiter //
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)
BEGIN
SELECT * FROM test WHERE sid = id;
END//
delimiter ;
调用存储过程
call selectById(2);


delimiter //
CREATE PROCEDURE dorepeat(n INT)
BEGIN
SET @i = 0;
SET @num = 0;
REPEAT SET @num = @num+@i; SET @i = @i + 1;
UNTIL @i > n END REPEAT;
END//
delimiter ;
带"@"的是全局变量，存储过程结束后变量还是可以用

CALL dorepeat(100);
SELECT @num;

包含IN参数和OUT参数的存储过程
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num
SMALLINT UNSIGNED)
BEGIN
DELETE FROM test WHERE sid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(2,@i);
SELECT @i;
创建存储过程deleteById,包含一个IN参数和一个OUT参数
调用时,传入删除的ID和保存被修改的行数值的用户变量@i,select @i;输出被影响行数

存储过程保存在mysql.proc表中

查看存储过程列表
SHOW PROCEDURE STATUS;

查看存储过程定义
SHOW CREATE PROCEDURE sp_name

调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter …]])
CALL sp_name
当无参时,可以省略"()",当有参数时,不可省略"()”

存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行
IF：用来进行条件判断。根据是否满足条件，执行不同语句
CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
LOOP：重复执行特定的语句，实现一个简单的循环
LEAVE：用于跳出循环控制
ITERATE：跳出本次循环，然后直接进入下一次循环
REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
WHILE：有条件控制的循环语句

触发器

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行

创建触发器
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDAT

示例：

先创建数据表
CREATE TABLE test (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE test_count (
test_count INT(11) DEFAULT 0
);
INSERT INTO test_count VALUES(0);

创建触发器
test表每次增加记录就让test_count+1
CREATE TRIGGER trigger_test_count_insert
AFTER INSERT
ON test FOR EACH ROW
UPDATE test_count SET test_count=test_count+1;

test表每次增加记录就让test_count-1
CREATE TRIGGER trigger_test_count_delete
AFTER DELETE
ON test FOR EACH ROW
UPDATE test_count SET test_count=test_count-1;

查看触发器
SHOW TRIGGERS

查询系统表information_schema.triggers的方式指定查询条件，查看指定的
触发器信息。
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE
trigger_name=‘trigger_student_count_insert’;

删除触发器
DROP TRIGGER trigger_name;

zerocdn

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL多表查询、视图、函数、存储过程、流程控制、触发器

多表查询多表纵向合并让多张表可以纵向合并，并且去重union示例：select * from test union select * from test2;如果不想去重加上allselect * from test union all select * from test2;交叉连接交叉连接也叫笛卡尔乘积，一般很少用cross join示例：test表id name ...
复制链接

扫一扫