3.MySql入门-MySql其他知识

3.1级联更新/级联删除

当我们使用外键约束时，希望删除主表数据的时候，在副表中与它有关的数据也会被删除，这时候可以使用级联更新/级联删除，语法如下:

CREATE TEMPORARY TABLE database1 ( id1 INT， CONSTRAINT database1_database2_fk FOREIGN KEY(id1) REFERENCES database2(id2) ON UPDATE CASCADE ON DELETE CASCADE ); CREATE TEMPORARY TABLE database2 ( id2 INT );

ON UPDATE CASCADE:主表修改某个数据，副表中与之有关的数据也会随之修改。 ON DELETE CASCADE:主表删除某个数据，副表中与之有关的数据也会随之删除。

3.2多表查询

CREATE TEMPORARY TABLE database1 ( id1 INT, name VARCHAR(20) ); CREATE TEMPORARY TABLE database2 ( id2 INT, name VARCHAR(20) );

3.2.1交叉连接查询(笛卡尔积)

当我们需要将表1每一条数据和表2每一条数据组合起来的时候，可以用到这种查询方式。 若database1下id1有2条数据(1,2),database2下id2有3条数据(a,b,c),产生结果： (1,a)(1,b)(1,c)(2,a)(2,b)(2,c) SELECT database1.id1,database2.id1 FROM database1,database2;

3.2.2内连接

当我们需要查询的结果为：遍历表一某些数据和表二某些数据，找出他们其中之中满足某些条件的数据时，可以使用内连接。

select d1.*, d2.* from database1 d1 inner join database2 d2 on d1.id1 = d2.id2 //另外一种写法 SELECT d1.*,d2.* FROM database1 d1,database2 d2 WHERE d1.id1 = d2.id2;

结果为所有满足 database1.id1 = database1.id2 的组合。

3.2.3左外连接查询/右外连接查询

当我们需要查询的结果为：先找出表一中的所有数据,再遍历表二的数据，再找出表二之中满足某些条件(与表一有关)的数据时，左外连接查询/右外连接查询，若表一中某些数据在表二中找不到满足其条件的，表二会显示null，也就是一个表会显示所有内容。

SELECT d1.name,d2.name FROM database1 d1 LEFT OUTER JOIN database2 d2 ON d1.id1=d2.id2；

上面代码显示的结果：database1全部显示，database2满足条件的组合才会显示。 右外连接与这没什么差别，只不过左右互换。

SELECT d1.name,d2.name FROM database2 d2 LEFT OUTER JOIN database1 d1 ON d1.id1=d2.id2；

上面代码显示的结果：database2，database1满足条件的组合才会显示。

3.2.4自连接查询

自连接查询本质上也是左外连接查询/右外连接查询，只不过两个表都是写同一个表而已。

CREATE TEMPORARY TABLE worker( id INT, bossId INT ); SELECT w.name AS '员工',b.name AS '上司' FROM database1 w LEFT OUTER JOIN database1 b ON w.bossId=b.id;

上面代码显示的结果：同样的，员工ID会全部显示出来，如果该员工没有上司，那他只会有一条数据，并且上司列会显示null，如果有多个上司，会显示多列。

3.3 存储过程

3.3.1什么是存储过程

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。可以简单认为是一个方法，可以实现一系列功能，可以接受参数返回参数，声明局部变量等。 优点：提高通信速率，提高安全性，提高效率。

3.3.2怎么创建并调用一个存储过程

DELIMITER $ CREATE PROCEDURE pro_name() BEGIN //功能语句 END $//存储过程结束

可以发现我们需要先定义一个结束符号，也就是DELIMITER $，然后再BEGIN END中写入我们需要的功能语句，最后以结束符号结束。 如何调用？ CALL pro_test();

3.3.3带输入输出参数的存储过程

参数格式：参数类型 参数名称 数据类型 其中参数类型有:IN OUT INOUT

DELIMITER $ CREATE PROCEDURE pro_test(IN n1 VARCHAR(20),OUT n2 VARCHAR(20) ,INOUT n3 VARCHAR(20)) BEGIN SELECT n1; SET n2 = '输出参数'; SELECT n3; SET n3 = '输入输出参数-输出'; END $ SET @n1='输入参数'; SET @n3='输入输出参数-输入'; CALL pro_test(@n1,@n2,@n3);

使用存储过程查询数据库数据:

CREATE TEMPORARY TABLE worker( id INT, name VARCHAR(20) ); DELIMITER $ CREATE PROCEDURE pro_test(IN w_id INT,OUT w_Name VARCHAR(20)) BEGIN SELECT NAME INTO w_Name FROM worker WHERE id=w_id; END $ CALL pro_test(1,@w_Name);

3.3.3在存储过程中使用判断条件、循环条件

条件语句:

IF 条件 THEN 功能语句; ELSEIF THEN 功能语句; END 功能语句; END IF;

循环语句：

DECLARE i INT DEFAULT 1; DECLARE num INT DEFAULT 10; WHILE i<=num DO 功能语句; END WHILE;

判断成绩：

ELIMITER $ CREATE PROCEDURE pro_grade(IN num INT,OUT result VARCHAR(20)) BEGIN IF num>=80 THEN SET str = '优秀'; ELSEIF num>=60 THEN SET str ='良好'; ELSE SET str = '不及格'; END IF; END $

计算从1到num的值:

DELIMITER $ CREATE PROCEDURE pro_testByWhile(IN num INT,OUT sum INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE result INT DEFAULT 0; WHILE i<=num DO SET result = result + i; SET i = i+1; END WHILE; SET sum = result; END $