MySQL-存储过程及函数

一、存储过程及函数语法简介

文档：https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

1）create procedure用来创建存储过程，create function用来创建函数

2）函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可

3）Definer和sql security子句指定安全环境

Definder是MySQL的特殊的访问控制手段，当数据库当前没有这个用户权限时，执行存储过程可能会报错

sql secuirty的值决定了调用存储过程的方式，取值：definer（默认）或者invoker

definer:在执行存储过程前验证definer对应的用户如：cdq@127.0.0.1是否存在，以及是否具有执行存储过程的权限，若没有则报错
invoker:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限，若没有则报错（当用户删掉不存在只要其他用户有权限执行也能执行成功）

#以root用户创建cdq用户
mysql> grant all on *.* to cdq@localhost identified by 'mysql';

#以cdq用户登录mysql，创建存储过程
[root@db01 ~]# mysql -ucdq -pmysql
mysql> use course;

delimiter //
CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT)
BEGIN
	SELECT COUNT(*) INTO param2 FROM Students where sid>param1;
END
//
delimiter ;

mysql> call simpleproc(1,@a);  #以cdq用户调用
mysql> select @a;
+------+
| @a   |
+------+
|    7 |
+------+

#root用户登录
mysql> call simpleproc(1,@a);  #此时是可以调用的

#删除用户cdq，重新以root用户连接
mysql> drop user cdq@localhost;
mysql> call simpleproc(1,@a);
ERROR 1449 (HY000): The user specified as a definer ('cdq'@'localhost') does not exist  #报错

mysql> show create procedure simpleproc\G  #DEFINER=`cdq`@`localhost` ==>该存储过程是由cdq用户创建的，当删除用户后会报错
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`cdq`@`localhost` PROCEDURE `simpleproc`(IN param1 int,OUT param2 INT)
BEGIN
SELECT COUNT(*) INTO param2 FROM Students where sid>param1;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

#解决方法：
mysql> alter procedure simpleproc sql security invoker;
mysql> show create procedure simpleproc\G
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`cdq`@`localhost` PROCEDURE `simpleproc`(IN param1 int,OUT param2 INT)
    SQL SECURITY INVOKER
BEGIN
SELECT COUNT(*) INTO param2 FROM Students where sid>param1;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

4）IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数

IN输入参数用于把数值传入到存储过程中；
OUT输出参数将数值传递到调用者，初始值是NULL；
INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者

5）Rontine_body子句可以包含一个简单的SQL语句，也可以包含多个SQL语句，通过begin…end将这多个SQL语句包含在一起，MySQL存储过程和函数中也可以包含类似create和drop等DDL语句

6）Comment子句用来写入对存储过程和函数的注释

7）Language子句用来表示此存储过程和函数的创建语言

8）存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic

9）相关属性短语只有咨询含义，并不是强制性的约束

Contains sql表明此存储过程或函数不包含读或者写数据的语句，这是默认属性
NO SQL表示此存储过程或函数不包含SQL语句
Reads sql data表示此存储过程包含诸如select的查询数据的语句，但不包含插入或删除数据的语句
Modifies sql data表示此存储过程包含插入或删除数据的语句

1.1、创建简单的存储过程

#创建简单的存储过程：
#Delimiter命令是改变语句的结束符，MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结
delimiter //   
create procedure simpleproc(in param1 int,out param2 int)
begin
    select count(*) into param2 from Students where sid>param1;
end
//
delimiter ;

#调用存储过程：
call simpleproc(1,@a);
select @a;

1.2、创建简单的函数

#创建简单的函数过程：
delimiter //
create function hello(s char(20))
returns char(50)
return concat('hello  ',s);
//
delimiter ;

#调用
mysql> select hello('zhangsan');
+-------------------+
| hello('zhangsan') |
+-------------------+
| hellozhangsan     |
+-------------------+

#----------------------------------------

delimiter //
create function simplefunc(param1 int)
returns int
begin
    update Students set gender=1 where sid=param1;
    select count(*) into @a from Students where sid>param1;
    return @a;
end;
//
delimiter ;

1.3、删除存储过程及函数

1）Drop procedure/function语句用来删除指定名称的存储过程或函数
2）If exists关键词用来避免在删除一个本身不存在的存储过程或函数时，MySQL返回错误

mysql> drop procedure simpleproc;
mysql> drop function if exists simplefunc;

1.4、begin…end复合语句

Begin…end语句通常出现在存储过程、函数和触发器中，其中可以包含一个或多个语句，每个语句用;号隔开

1.5、label标签语句（不常用）

1）标签label可以加在begin…end语句以及loop, repeat和while语句前

2）语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签

示例：

delimiter //
CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
BEGIN
	label1: LOOP
		SET p1 = p1 + 1;
		IF p1 < 10 THEN ITERATE label1; END IF;
		LEAVE label1;
	END LOOP label1;
	set p2=p1;
END;
//
delimiter ;

mysql> call doiterate(1,@a);
mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+

mysql> call doiterate(5,@a);
mysql> select @a;
+------+
| @a   |
+------+
|   10 |
+------+

1.6、declare语句

1）Declare语句通常用来声明本地变量、游标、条件或者handler

2）Declare语句只允许出现在begin … end语句中而且必须出现在第一行

3）Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

4）本地变量可以通过declare语句进行声明，声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值

5）通过declare声明变量方法：

使用default指定变量的默认值，如果没有指定默认值则初始值为NULL
type指明该变量的数据类型
声明的变量作用范围为被声明的begin … end语句块之间
声明的变量和被引用的数据表中的字段名要区分开来

示例：

delimiter //
CREATE PROCEDURE sp1 (v_sid int)
BEGIN
	DECLARE xname VARCHAR(64) DEFAULT 'bob';
	DECLARE xsex INT;
	SELECT sname, gender INTO xname, xsex
	FROM Students WHERE sid= v_sid;
	SELECT xname,xsex;
END;
//
delimiter ;

#注意定义的类型范围需要大于等于表中的范围VARCHAR(5) 
#Data truncated for column 'xname' at row 1

mysql> call sp1(1);
+--------+------+
| xname  | xsex |
+--------+------+
| Andrew |    0 |
+--------+------+

1.7、流程控制case语句

Case语句在存储过程或函数中表明了复杂的条件选择语句

1）第一个语句中case_value与后面各句的when_value依次做相等的对比，如果碰到相等的，则执行对应的后面的statement_list，否则接着对比，如果都没有匹配，则执行else后面的statement_list
2）第二个语句中当search_condition满足true/1的结果时，则执行对应的statement_list，否则执行else对应的statement_list
3）Statement_list可以包含一个或多个SQL语句

示例：

#性别转换
delimiter //
CREATE PROCEDURE exp_case(v_sid int)
BEGIN
	DECLARE v INT DEFAULT 1;
	select gender into v from Students where sid=v_sid;
	CASE v
		WHEN 0 THEN update Students set gender=1 where sid=v_sid;
		WHEN 1 THEN update Students set gender=0 where sid=v_sid;
		ELSE update Students set gender=-1 where sid=v_sid;
	END CASE;
END;
//
delimiter ;
call exp_case(1);

delimiter //
CREATE PROCEDURE exp_case2(v_sid int)
BEGIN
	DECLARE v INT DEFAULT 1;
	select gender into v from Students where sid=v_sid;
	CASE
		WHEN v=0 THEN update Students set gender=1 where sid=v_sid;
		WHEN v=1 THEN update Students set gender=0 where sid=v_sid;
		ELSE update Students set sex=-1 where sid=v_sid;
	END CASE;
END;
//
delimiter ;
call exp_case2(1);

1.8、流程控制语句if语句

1)IF语句在存储过程或函数中表明了基础的条件选择语句
2)IF语句中如果search_condition满足true/1的条件，则执行对应的statement_list，否则再判断elseif中的search_condition是否满足true/1的条件，如果都不满足则执行else中的statement_list语句
3)Statement_list中可以包含一个或多个SQL语句

DELIMITER //
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
BEGIN
	DECLARE s VARCHAR(20);
	IF n > m THEN SET s = '>';
		ELSEIF n = m THEN SET s = '=';
		ELSE SET s = '<';
	END IF;
	SET s = CONCAT(n, ' ', s, ' ', m);
	RETURN s;
END 
//
DELIMITER ;

mysql> select simplecompare(1,2);
+--------------------+
| simplecompare(1,2) |
+--------------------+
| 1 < 2              |
+--------------------+

##嵌套if语句
DELIMITER //
CREATE FUNCTION VerboseCompare (n INT, m INT)
RETURNS VARCHAR(50)
BEGIN
	DECLARE s VARCHAR(50);
	IF n = m THEN SET s = 'equals';
		ELSE
			IF n > m THEN SET s = 'greater';
				ELSE SET s = 'less';
			END IF;
			SET s = CONCAT('is ', s, ' than');
	END IF;
	SET s = CONCAT(n, ' ', s, ' ', m, '.');
RETURN s;
END 
//
DELIMITER ;

mysql> select verbosecompare(1,2);
+---------------------+
| verbosecompare(1,2) |
+---------------------+
| 1 is less than 2.   |
+---------------------+

1.9、流程控制iterate语句

1）Iterate语句仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环
2）Label表示自定义的标签名

1.10、流程控制leave语句

1）Leave语句表明退出指定标签的流程控制语句块
2）通常会用在begin…end，以及loop,repeat,while的循环语句中
3）Label表明要退出的标签名

示例：

delimiter //
CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
BEGIN
	label1: LOOP
		SET p1 = p1 + 1;
		IF p1 < 10 THEN ITERATE label1; END IF;
		LEAVE label1;
	END LOOP label1;
	set p2=p1;
END;
//
delimiter ;

1.11、流程控制loop语句

1）Loop语句是存储过程或函数中表达循环执行的一种方式
2）其中的statement_list可以包含一个或多个SQL语句

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
	label1: LOOP
		SET p1 = p1 + 1;
		IF p1 < 10 THEN
			ITERATE label1;
		END IF;
		LEAVE label1;
	END LOOP label1;
	SET @x = p1;
END;

1.12、流程控制repeat语句

1）repeat语句是存储过程或函数中表达循环执行的一种方式
2）Repeat语句中statement_list一直重复执行直到search_condition条件满足，Statement_list可以包含一个或多个SQL语句

delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
	SET @x = 0;
	REPEAT
		SET @x = @x + 1;
	UNTIL @x > p1 END REPEAT;
END
//
delimiter ;

mysql> CALL dorepeat(1000);

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+

1.13、流程控制while语句

 

DELIMITER //
CREATE PROCEDURE dowhile()
BEGIN
	DECLARE v1 INT DEFAULT 5;
	WHILE v1 > 0 DO
		update Students set gender=-1 where sid=v1;
		SET v1 = v1 - 1;
	END WHILE;
END;
//
DELIMITER ;

call dowhile();

1.14、流程控制return语句

1）Return语句用在函数中，用来终结函数的执行并将指定值返回给调用者
2）在函数中必须要有至少一个return语句，当有多个return语句时则表明函数有多种退出的方式

delimiter //
create function doreturn()
returns int
begin
	select gender into @a from Students where sid=1;
	if @a=1 then return 1;
		elseif @a=0 then return 0;
		else return 999;
	end if;
end;
//
delimiter ;

select doreturn();

1.15、强化练习一

#创建一个存储过程proc1，将10万行如下格式的测试数据插入到students表中，数据中只有sid是递增的，其余字段值都是固定的
（1，’mike’,1,1),
 (2,’mike’,1,1),
 (3,’mike’,1,1),
 …….
 (100000,’mike’,1,1)

#方式一
Delimiter //
Create procedure proc1()
Begin
	Declare n int default 1;
	while n<=100000 do
		Insert into Students values(n, 'mike' , 1,1);
		Set n=n+1;
	End while;
End;
//
Delimiter ;
call proc1();

#方式二
delimiter //
Create procedure proc1_3()
begin
	Declare n int default 1;
	start_label: loop
		if n>100000 then
			leave start_label;
		End if;
		insert into Students values(n, 'mike' , 1,1);
		set n=n+1;
	end loop;
End;
//
Delimiter ;
call proc1_3(); 
--------------------------------------------------------------------------- 

#在第1题的基础上，创建另一个存储过程proc2，插入10万行数据到students表中，但要求gender字段在0和1之间随机，dept_id在1~3这三个整数之间取随机，sname字段固定是’mike’

Delimiter //
Create procedure proc2()
Begin
	Declare n int default 1;
	Declare v_gender_id int;
	Declare v_dept_id int;
	while n<=100000 do
		Set v_gender_id=round(rand());
		Set v_dept_id=floor(rand()*3+1);
		Insert into Students values(n, 'mike' , v_gender_id, v_dept_id);
		Set n=n+1;
	End while;
End;
//
delimiter ;
call proc2();
---------------------------------------------------------------------------

#创建一个函数，输入参数为学生学号sid，函数返回对应学生的平均成绩

Delimiter //
Create function func1(v_sid int)
Returns int
Begin
	Select avg(score) into @x from score where sid=v_sid;
	Return @x;
End;
//
Delimiter ;

select func1(1);
---------------------------------------------------------------------------

#创建一个函数，输入参数是老师的id，函数返回该老师所教授的课程数量，并将这些学习这些课程的每个学生如果成绩不及格，把学生的sid和对应课程名字、成绩insert到表A中，如果成绩及格，把学生的sid和对应的课程名字、成绩insert到表B中

Delimiter //
Create function func2(v_teacher_id int)
Returns int
Begin
	Declare n_course int;
	Select count(*) into n_course from Course where teacher_id=v_teacher_id;  #老师所教授的课程数量
	Insert into A select a.sid,b.course_name,a.score From score a inner join Course b on a.course_id=b.id Where b.teacher_id=v_teacher_id and a.score<60;
	Insert into B select a.sid,b.course_name,a.score From score a inner join Course b on a.course_id=b.id Where b.teacher_id=v_teacher_id and a.score>=60;
	Return n_course;
End;
//
Delimiter ;

mysql> create table A (sid int, course_name varchar(64),score int);
mysql> create table B (sid int, course_name varchar(64),score int);

mysql> select func2(3);
+----------+
| func2(3) |
+----------+
|        1 |
+----------+

mysql> select * from A;
+------+-------------+-------+
| sid  | course_name | score |
+------+-------------+-------+
|    3 | math        |    48 |
|    7 | math        |    55 |
+------+-------------+-------+

mysql> select * from B;
+------+-------------+-------+
| sid  | course_name | score |
+------+-------------+-------+
|    1 | math        |    76 |
|    5 | math        |    75 |
+------+-------------+-------+

1.16、cursor游标

1）Cursor游标用来声明一个数据集
2）游标的声明必须在变量和条件声明之后，在handler声明之前

delimiter //
CREATE PROCEDURE curdemo()
BEGIN
	DECLARE done INT DEFAULT FALSE;
	DECLARE a int;
	DECLARE b varchar(64);
	DECLARE cur1 CURSOR FOR SELECT sid,sname FROM Students;  ##声明游标，数据集
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  #当找不到时设置done为true
	OPEN cur1;	#打开游标
	read_loop: LOOP
		FETCH cur1 INTO a, b;	#获取游标的每一行
		IF done THEN	#当done为true时，退出循环
			LEAVE read_loop;
		END IF;
		IF a < 100 THEN
			INSERT INTO t3 VALUES (a,b);  
		ELSE
			INSERT INTO t4 VALUES (a,b);
		END IF;
	END LOOP;
	CLOSE cur1; #关闭游标
END;
//
delimiter ;

create table t3(sid int,sname varchar(64));
create table t4(sid int,sname varchar(64));
call curdemo();
---------------------------------------------------------------------------------------------
#Cursor close语句用来关闭之前打开的游标
如果关闭一个未打开的游标，则MySQL会报错
如果在存储过程和函数中未使用此语句关闭已经打开的游标，则游标会在声明的begin…end语句块执行完之后自动关闭

#Cursor declare语句用来声明一个游标和指定游标对应的数据集合，通常数据集合是一个select语句

#Cursor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予后面的变量
数据集中的字段需要和INTO语句中定义的变量一一对应
数据集中的数据都fetch完之后，则返回NOT FOUND

#Open cursor语句用来打开一个之前已经声明好的游标

1.17、Declare condition语句

1）Declare condition语句命名特定的错误条件，而该特定错误可以在declare…handler中指定处理方法

2）Condition_value指定特定的错误条件，可以有以下两种形式

Mysql_err_code表示MySQL error code的整数
SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态

3）比如在MySQL中1051 error code表示的是unknown table的错误，如果要对这个错误做特殊处理，可以用三种种方法：

DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END;

DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;
 
DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
 BEGIN
 -- body of handler
 END;

1.18、Declare handler语句

1）Declare handler语句用来声明一个handler来处理一个或多个特殊条件，当其中的某个条件满足时则触发其中的statement语句执行

2）Handler_action子句声明当执行完statement语句之后应该怎么办

Continue代表继续执行该存储过程或函数
Exit代表退出声明此handler的begin…end语句块
Undo参数已经不支持

3）Condition_value的值有以下几种：

Mysql_err_code表示MySQL error code的整数
SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态
Condition_name表示之前在declare…condition语句中声明的名字
SQLWARNING表示所有的警告信息，即SQLSTATE中01打头的所有错误
NOT FOUND表示查完或者查不到数据，即SQLSTATE中02打头的所有错误
SQLEXCEPTION表示所有的错误信息

DECLARE CONTINUE HANDLER FOR 1051
 BEGIN
 -- body of handler
 END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
 BEGIN
 -- body of handler
 END;

DECLARE CONTINUE HANDLER FOR SQLWARNING
 BEGIN
 -- body of handler
 END;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
 -- body of handler
 END;

4）当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理

发生SQLEXCEPTION错误，则执行exit退出
发生SQLWARNING警告，则执行contine继续执行
发生NOT FOUND情况，则执行continue继续执行

#比如SQLSTATE '23000表示主键冲突错误
CREATE TABLE t (s1 INT, PRIMARY KEY (s1));

delimiter //
CREATE PROCEDURE handlerdemo ()
BEGIN
	DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
	SET @x = 1;
	INSERT INTO t VALUES (1);
	SET @x = 2;
	INSERT INTO t VALUES (1);  #此时会有主键冲突
	SET @x = 3;
END;
//
delimiter ;

CALL handlerdemo();
SELECT @x;  #3   ==>当发生主键冲突时，继续向下执行

1.19、强化练习二

#创建一个过程，将男生和女生的学生数据分别存储到男生表和女生表中。
delimiter //
Create procedure proc1()
Begin
	Insert into students_male select * from students where gender=0;
	Insert into students_female select * from students where gender=1;
End
//
delimiter ;
---------------------------------------------------------------------------------------

#创建一个过程，将每个学生的课程数，平均成绩，及格课程数，非及格课程数都存放在单独的表中
delimiter //
Create procedure proc2()
Begin
	Insert into temp1 select sid,count(*),avg(score),sum(case when score>=60 then 1 else 0 end),sum(case when score<60 then 1 else 0 end)From score Group by sid;
End
//
delimiter ;

call proc2();
---------------------------------------------------------------------------------------

#创建一个过程，将学生sid作为输入参数，结果展示出该学生的课程数和平均成绩
delimiter //
Create procedure proc3(IN st_id)
Begin
Select sid,count(*),avg(score) from score where sid=st_id group by Sid;
End
//
delimiter ;
---------------------------------------------------------------------------------------

#创建一个函数，以学生sid作为输入参数，将该学生的课程数和平均成绩存放在单独的表中，并返回平均成绩
delimiter //
Create function func1(st_id int)
Return int 
Begin
	Declare avg_score int;
	Insert into temp2 select Sid,count(*),avg(score) from score where sid=st_id group by Sid;
	Select avg(score) into avg_score from score where sid=st_id group by Sid;Return avg_score; 
End
//
delimiter ;

---------------------------------------------------------------------------------------
#用游标的方法实现创建一个函数，输入参数是老师的id，函数返回该老师所教授的课程数量，并将这些学习这些课程的每个学生如果成绩不及格，把学生的sid和对应课程名字、成绩insert到表A中，如果成绩及格，把学生的sid和对应的课程名字、成绩insert到表B中

delimiter //
Create function func3(v_teacher_id int)
Returns int  #返回类型
Begin
	Declare n_course int;
	Declare v_sid int default null;
	Declare v_course_name varchar(60);
	Declare v_score int;
	#定义游标
	Declare cur1 cursor for select a.sid,b.course_name,a.score From score a inner join Course b on a.course_id=b.id Where b.teacher_id=v_teacher_id;
	declare continue handler for not found set v_sid=null;  #定义handler
	Select count(*) into n_course from Course where teacher_id=v_teacher_id;
	Open cur1;  #打开游标
	Fetch cur1 into v_sid,v_course_name,v_score;  #获取游标第一行，否则下面不运行
	While v_sid is not null do
		If v_score<60 then
			insert into A select v_sid,v_course_name,v_score;
		Else
			insert into B select v_sid,v_course_name,v_score;
		End if;
		Fetch cur1 into v_sid,v_course_name,v_score;  #再次回去游标下一行
	End while;
	Close cur1;
	Return n_course;
End;
//
Delimiter ;

mysql> select func3(3);
+----------+
| func3(3) |
+----------+
|        1 |
+----------+

mysql> select * from B;
+------+-------------+-------+
| sid  | course_name | score |
+------+-------------+-------+
|    1 | math        |    76 |
|    5 | math        |    75 |
+------+-------------+-------+

mysql> select * from A;
+------+-------------+-------+
| sid  | course_name | score |
+------+-------------+-------+
|    3 | math        |    48 |
|    7 | math        |    55 |
+------+-------------+-------+

1.20、create trigger语句

1）create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行，触发器创建时需要指定对应的表名tbl_name

2）Definer关键词用来指定trigger的安全环境

3）Trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行

4）Trigger_event指定触发该触发器的具体事件

INSERT当新的一行数据插入表中时触发，比如通过执行insert,load data,replace语句插入新数据
UPDATE当表的一行数据被修改时触发，比如执行update语句时
DELETE当表的一行数据被删除时触发，比如执行delete,replace语句时

5）当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器

6）从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下按照创建的时间依次执行，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器后执行，PRECEDES则表示新触发器先执行

7）Trigger_body表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

mysql> desc Students;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| sid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(64) | YES  |     | NULL    |                |
| gender  | varchar(12) | YES  |     | NULL    |                |
| dept_id | int(11)     | NO   | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

delimiter //
create trigger simple_trigger
after update on Students for each row
begin
	insert into Students_bak values(old.sid,new.sid,old.sname,new.sname,old.gender,new.gender,old.dept_id,new.dept_id,now());
end;
//
delimiter ;

create table Students_bak(old_sid int,new_sid int,old_sname varchar(64),new_sname varchar(64),old_gender varchar(12),new_gender varchar(12),old_dept_id  int,new_dept_id int,tstamp datetime);

mysql> update Students set sname='AAA',gender=1 where sid < 5;
mysql> select * from Students_bak;
+---------+---------+-----------+-----------+------------+------------+-------------+-------------+---------------------+
| old_sid | new_sid | old_sname | new_sname | old_gender | new_gender | old_dept_id | new_dept_id | tstamp              |
+---------+---------+-----------+-----------+------------+------------+-------------+-------------+---------------------+
|       1 |       1 | abc       | AAA       | 1          | 1          |           1 |           1 | 2019-10-07 21:07:19 |
|       2 |       2 | abc       | AAA       | 1          | 1          |           3 |           3 | 2019-10-07 21:07:19 |
|       3 |       3 | abc       | AAA       | 1          | 1          |           1 |           1 | 2019-10-07 21:07:19 |
|       4 |       4 | abc       | AAA       | 1          | 1          |           3 |           3 | 2019-10-07 21:07:19 |
+---------+---------+-----------+-----------+------------+------------+-------------+-------------+---------------------+

8）Drop trigger语句用来删除一个触发器,If exists短语用来避免删除不存在的触发器时引发报错.当你执行drop table时，表上的触发器也被drop掉了

trigger练习：

#在score表上创建一个触发器，当有新的数据插入时，在score_bak表里记录新插入的数据的所有字段信息，并用tstamp字段标注数据的插入时间
Delimiter //
Create trigger trig1
after insert on score For each row
Begin
	Insert into score_bak(sid,course_id,score,tstamp) values(new.sid,new.course_id,new.score,now());
End;
//
Delimiter ;

mysql> create  table score_bak(sid int, course_id int, score int, tstamp datetime);
mysql> insert  into score values(12,3,89);
mysql> select * from score_bak;
+------+-----------+-------+---------------------+
| sid  | course_id | score | tstamp              |
+------+-----------+-------+---------------------+
|   12 |         3 |    89 | 2019-10-07 21:19:22 |
+------+-----------+-------+---------------------+


#在score表上创建一个触发器，当有新的数据插入时，在score_avg表里记录对应学生的所有课程的平均成绩（注意，如果在score_avg表里已经有了学生的记录，需要update）

Delimiter //
Create trigger trig2
After insert on score For each row
Begin
	Declare n int;
	Select count(*) into n from score_avg where sid=new.sid;  #当为1时表示有数据
	If n=1 then
		update score_avg set avg_score=(select avg(score) from score where sid=new.sid) where sid=new.sid;
	Else
		insert into score_avg select sid,avg(score) from score where sid=new.sid group by sid;
	End if;
End;
//
Delimiter ;

create table score_avg(sid int,score_avg int);

二、MySQL数据类型

2.1、整数类型

1）下表中指明了具体的类型，存储消耗的字节数，最小最大取值范围，unsigned代表不允许负数，则正整数的取值范围扩大一倍

2）MySQL可以为整数类型指定宽度，比如INT(11)，这个限制对大多数应用没有意义，因为这不是限制值的合法范围，对于存储和计算来说，INT(1)和INT(20)是相同的，只是对一些MySQL的交互工具规定了显示字符的个数，比如MySQL命令行客户端。

mysql> create table temp1(id int(1),id2 int(20));
mysql> insert into temp1 values(1000000,1000000);
mysql> select * from temp1;
+---------+---------+
| id      | id2     |
+---------+---------+
| 1000000 | 1000000 |
+---------+---------+

mysql> alter table temp1 modify id int(1) zerofill;
mysql> alter table temp1 modify id2 int(20) zerofill;
mysql> select * from temp1;
+---------+----------------------+
| id      | id2                  |
+---------+----------------------+
| 1000000 | 00000000000001000000 |
+---------+----------------------+

2.2、固定浮点类型

1）Decimal和numeric数据类型用来存储高精度数据，一般只在对小数进行精确计算时才使用，比如涉及财务数据的时候

2）DECIMAL[(M[,D])] [UNSIGNED]

3）在MySQL中，numeric和decimal的含义相同

4）Decimal的使用方法举例为decimal(5,2)

其中的5代表为精度，表示了可以使用多少位数字
其中的2代表小数点后面的小数位数
此例子的取值范围为-999.99到999.99

5）当不需要指定小数时，可以使用decimal(M),decimal(M,0)表示

6）当直接使用decimal时，则默认的M为10

7）M的最大取值为65，D的最大取值为30，当D为0时可以用来存储比BIGINT更大范围的整数值

8）当指定unsigned，表示不允许负数

9）MySQL对decimal字段采用每4个字节存储9个数字的方式，例如decimal(18,9)小数点两边各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

2.3、浮点类型

1）浮点类型中包含float和double两种，与decimal相比是不精确类型

2）FLOAT[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数，D则代表小数点后的小数位数，Unsigned(无)代表不允许使用负数

3）Float的取值范围为-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38

4）DOUBLE[(M,D)] [UNSIGNED]中的M代表可以使用的数字位数，D则代表小数点后的小数位数

5）Double的取值范围对比float要大，-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308

6）在存储同样范围的值时，通常比decimal使用更少的空间，float使用4个字节存储，double使用8个字节。

mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21, 9876543.12, 9876543.12);
mysql> select * from temp2;
+------------+------------+------------+
| id         | id2        | id3        |
+------------+------------+------------+
| 1234567.25 | 1234567.21 | 1234567.21 |
| 9876543.00 | 9876543.12 | 9876543.12 |
+------------+------------+------------+

2.4、bit类型

1）Bit数据类型用来存储bit值

2）BIT(M)代表可以存储M个bit，M的取值范围为1到64

3）如果手工指定bit值，则可以使用b’value’格式，比如b’111’和b‘10000000’分别代表7和128

4）除非特殊情况，否则尽量不要使用这个类型

2.5、日期时间类型

日期时间类型包括date,time,datetime,timestamp和year，用来指定不同范围的日期或时间值

1）Date类型用来表示仅日期，MySQL默认的日期格式为yyyy-mm-dd，取值范围为1000-01-01到9999-12-31

2）Datetime类型用来表示日期和时间，MySQL默认的格式为yyyy-mm-dd hh:mi:ss，取值范围为1000-01-01 00:00:00到9999-12-31 23:59:59

3）Timestamp类型也用来表示日期和时间，其取值范围为1970-01-01 00:00:01到2038-01-19 03:14:07

4）Datetime和timestamp两个类型都可以保存到微妙级别，即6位毫秒微妙精度，即1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999和1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999

5）非法的date,datetime,timestamp值将被转换成0值，0000-00-00或者0000-00-00 00:00:00

6）Time类型用来仅表示时间，MySQL默认格式为HH:MM:SS，其取值范围为-838:59:59到838:59:59，小时字段可以超过24是因为time类型不光代表小时，也可以代表持续时长中的小时。Time类型也可以包含6位的毫秒微秒精度，其取值范围为-838:59:59.000000到838:59:59.000000

7）Year类型用来仅表示年份，MySQL默认格式为YYYY，其取值范围为1901到2155，和0000（针对非法的year数据，则直接转化为0000）

8）Timestamp和datetime日期时间类型可以被自动初始化和更新为当前的日期时间数据，当你默认指定current timestamp为默认值，或者指定此数据列为自动更新时

指定默认值是指当插入新的数据而该列没有显视指定数值时，则插入当前日期时间值
指定自动更新是指当行中的其他列被更新时，则此列被自动更新为当前日期时间值

CREATE TABLE t1 (
 ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

对比：

mysql> create table temp3(id int, tstamp datetime, tstamp2 datetime, tstamp3 timestamp, tstamp4 timestamp); #报错
mysql> create table temp3(id int, tstamp datetime, tstamp2 datetime, tstamp3 timestamp);
mysql> insert into temp3(id) values(1);
mysql> insert into temp3(id) values(2);
mysql> select * from temp3;
+------+--------+---------+---------------------+
| id   | tstamp | tstamp2 | tstamp3             |
+------+--------+---------+---------------------+
|    1 | NULL   | NULL    | 2019-10-07 22:17:18 |
|    2 | NULL   | NULL    | 2019-10-07 22:17:18 |
+------+--------+---------+---------------------+

mysql> create table temp4(id int,tstamp datetime default current_timestamp,tstamp2 datetime default current_timestamp,tstamp3 timestamp,tstamp4 timestamp default current_timestamp);
mysql> insert into temp4(id) values(1);
mysql> insert into temp4(id) values(2);
mysql> select * from temp4;
+------+---------------------+---------------------+---------------------+---------------------+
| id   | tstamp              | tstamp2             | tstamp3             | tstamp4             |
+------+---------------------+---------------------+---------------------+---------------------+
|    1 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 |
|    2 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 | 2019-10-07 22:20:01 |
+------+---------------------+---------------------+---------------------+---------------------+

mysql> create table temp5(id int,tstamp datetime default current_timestamp on update current_timestamp,tstamp2 datetime default current_timestamp ,tstamp3 timestamp,tstamp4 timestamp default current_timestamp on update current_timestamp);
mysql> desc temp5;
+---------+-----------+------+-----+-------------------+-----------------------------+
| Field   | Type      | Null | Key | Default           | Extra                       |
+---------+-----------+------+-----+-------------------+-----------------------------+
| id      | int(11)   | YES  |     | NULL              |                             |
| tstamp  | datetime  | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| tstamp2 | datetime  | YES  |     | CURRENT_TIMESTAMP |                             |
| tstamp3 | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| tstamp4 | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------+-----------+------+-----+-------------------+-----------------------------+

mysql> insert into temp5(id) values(1);
mysql> select * from temp5;
+------+---------------------+---------------------+---------------------+---------------------+
| id   | tstamp              | tstamp2             | tstamp3             | tstamp4             |
+------+---------------------+---------------------+---------------------+---------------------+
|    1 | 2019-10-07 22:23:08 | 2019-10-07 22:23:08 | 2019-10-07 22:23:08 | 2019-10-07 22:23:08 |
+------+---------------------+---------------------+---------------------+---------------------+

mysql> update temp5 set id=2;
mysql> select * from temp5;
+------+---------------------+---------------------+---------------------+---------------------+
| id   | tstamp              | tstamp2             | tstamp3             | tstamp4             |
+------+---------------------+---------------------+---------------------+---------------------+
|    2 | 2019-10-07 22:24:07 | 2019-10-07 22:23:08 | 2019-10-07 22:24:07 | 2019-10-07 22:24:07 |
+------+---------------------+---------------------+---------------------+---------------------+

#当在time,timestamp,datetime中指定含有毫秒微秒数值时，则用type_name(fsp)来表达，其中fsp可以取0到6之间的数值
mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
mysql> INSERT INTO fractest VALUES('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
mysql> SELECT * FROM fractest;
+-------------+------------------------+------------------------+
| c1          | c2                     | c3                     |
+-------------+------------------------+------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
+-------------+------------------------+------------------------+

2.6、字符类型

1）字符类型包含char, varchar, binary, varbinary, blob, text, enum和set

2）Char和varchar可以通过char(M)和varchar(M)指定可以存储的最大字符数，比如char(30)表示可以存储最长30个字符Char类型的长度一旦指定就固定了，其范围可以是0到255，当被存储时，未达到指定长度的则在值右边填充空格，而获取数据时则会把右侧的空格去掉

3）Varchar类型是变长的类型，其范围可以是0到65535，当存储是未达到指定长度则不填充空格。Varchar类型用来存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间。另外varchar需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节时，需要1个字节，否则需要2个字节。比如采用Latin1字符集，varchar(10)的列需要11个字节的存储空间，而varchar(1000)列需要1002个字节的存储空间。varchar节省了存储空间，所以对性能也有帮助。但由于行是变长的，在update时可能使行变得比原来更长，这就导致需要做额外的工作。如果一行占用的空间增长，并且物理数据页内没有更多空间存储时，MyISAM会将行拆成不同的片段存储，InnoDB需要分列页来讲行放到数据页里。

4）char类型是定长，MySQL总是根据定义的字符串长度分配足够的空间。当查询char值时，MySQL会删除所有的末尾空格。char适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。而且对非常短的字符串，char不需要一个额外的字节记录长度

#Char类型值右边的空格会被自动剔除，而varchar类型则不会
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab )               | (ab)                |
+---------------------+---------------------+

2.7、二进制类型

Binary和varbinary类型和char/varchar类似，只不过是存储二进制字符
mysql> CREATE TABLE t (c BINARY(3));
mysql> INSERT INTO t SET c = 'a';
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+

2.8、大数据类型

1）Blob和text类型被用来存储大量的数据
2）Blob是用来存储二进制的大量数据，其有四种类型，tinyblob、blob、mediumblob、longblob。四种的区别是能存储的数据长度有所不同
3）Text是用来存储字符型的大量数据，其有四种类型， tinytext、text、mediumtext、longtext。四种的区别是能存储的数据长度有所不同
4）Blob和text的列字段不能含有默认值

2.9、枚举类型

1）Enum枚举类型是字符串类型，其值是从事先指定的一系列值中选出，适用在某列的取值范围已经固定
2）主要好处为MySQL在存储此类数据时，直接转化成数字存储而不是字符串，可以节省空间，并且在表的.frm文件中存储“数字-字符串”之间的对应关系

CREATE TABLE shirts (
 name VARCHAR(40),
 size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+

UPDATE shirts SET size = 'small' WHERE size = 'large';
commit;
mysql> select size+0 from shirts;
+--------+
| size+0 |
+--------+
|      2 |
|      3 |
|      2 |
+--------+

另外枚举类型的排序规则是按照存储顺序进行而不是按照值本身排序的
mysql> select size from shirts order by size;
+--------+
| size   |
+--------+
| small  |
| medium |
| large  |
+--------+

mysql> select * from shirts order by size;
+-------------+--------+
| name        | size   |
+-------------+--------+
| polo shirt  | small  |
| t-shirt     | medium |
| dress shirt | large  |
+-------------+--------+


如果想要按照一般的排序规则进行排序，需要使用field()函数显示指定排序规则
mysql> select size from shirts order by field(size,'large','medium','small');
+--------+
| size   |
+--------+
| large  |
| medium |
| small  |
+--------+

枚举类型字段的取值的增加必须通过alter table命令
mysql> alter table shirts modify size ENUM('x-small', 'small', 'medium', 'large', 'x-large','xx-large');


Enum枚举类型最多可以有65535个值
当插入数字到枚举类型字段时，数字会被当做枚举值的第几个值而插入  ?????
mysql> create table t (numbers enum('0','1','2'));
mysql> desc t;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| numbers | enum('0','1','2') | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> select * from t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+


枚举类型中的空串和NULL值
当插入一个非法的值到枚举字段时，则会报错
如果枚举字段允许NULL，则NULL值为此枚举类型的默认值
mysql> insert into shirts values('abc','smal');
ERROR 1265 (01000): Data truncated for column 'size' at row 1

2.10、集合类型

1、Set集合类型是字符类型，可以含有0个或多个值，其中的每个值都需要是在创建字段时指定的集合中

2、比如一个字段被指定为SET('one', 'two') not null可以含有以下四种值: '','one','two','one,two'

3、Set集合最大可以有255个值

4、MySQL在存储set集合时，同样也是存储为数字类型

5、Set集合列中各值的顺序无关紧要，且一个值如果出现多次也会被忽略

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
mysql> INSERT INTO myset (col) VALUES('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+

2.11、数据类型选择

1）尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU时间也更少。比如如果知道某个数字列的存储值在0~200之间，就应该选取tinyint类型

2）简单的数据类型操作通常需要更少的CPU周期。例如整型比字符操作代价更低，因为字符集和排序规则使得字符比较比整型比较更复杂

3）通常情况下最好指定列为NOT NULL。因为如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更为复杂。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，所以会使用更多的存储空间

2.12、设置默认值

1）Default默认值用来指定一个列的默认值，但不能指定函数或表达式作为默认值，比如now()和current_date，但唯一的例外是可以指定current_timestamp作为timestamp和datetime列的默认值
2）Blob,text列不能指定默认值
3）如果一个列没有显视指定default默认值，如果该列允许null值，则默认值为null

2.13、自增长类型字段

整型和浮点型字段可以被指定为自增长类型字段，意味着当插入行数据时这列为NULL时，则按照此列最大值+1的方式插入数据
获取插入后的自增长列的值，可以用LAST_INSERT_ID()函数获取
一个表中只能有一个自增长字段，且不能含有默认值
自增长字段的数值从1开始递增，且不能插入负值

CREATE TABLE animals (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');

SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

当你显视的插入一个数值到自增长字段时，则下一个是表中所有值的最大值+1
设置字段的auto_increment属性，可以有两种方法
1)Create table的时候指定
2)Alter table的时候指定： 
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

针对Myisam存储引擎，auto_increment属性可以添加到多列键值的第二列上，则自增列的值计算是根据第一个列分组计算得出
CREATE TABLE animals (
 grp ENUM('fish','mammal','bird') NOT NULL,
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
 ('mammal','dog'),('mammal','cat'),
 ('bird','penguin'),('fish','lax'),('mammal','whale'),
 ('bird','ostrich');
 
SELECT * FROM animals ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

三、MySQL存储引擎

3.1、mysql支持的存储引擎

#通过执行show engines命令查看MySQL中支持哪些存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

3.2、设置存储引擎的方法

1）在my.cnf配置文件中设置default-storage-engine参数表示设置默认存储引擎

2）在MySQL的连接上设置当前连接的默认存储引擎

SET default_storage_engine=NDBCLUSTER;

3）在创建表的时候通过engine=语句指定该表的存储引擎

CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

4）在表创建之后通过alter语句修改表的存储引擎

ALTER TABLE t ENGINE = InnoDB;

3.3、InnoDB

1）存储引擎InnoDB是目前MySQL版本默认的存储引擎，也是MySQL推荐使用的存储引擎，是集高可靠性和高性能于一身的存储引擎。

2）在MySQL5.7版本中，除非在配置文件中显视指定default storage engine或者创建表时显视使用engine=语句指定其它的存储引擎，否则默认都是InnoDB

3）使用InnoDB存储引擎的优势在于

DML语句支持事务功能，保证ACID特性
行级锁的使用保证了高并发的属性
InnoDB对有主键的表会依据主键优化查询性能，也称聚簇索引，将所有数据存储在聚簇索引上以减少对主键查询的IO消耗
为保证数据的一致性，InnoDB还支持外键属性，确保有外键约束的表之间不会有不一致的数据
当服务器硬件或者软件故障导致MySQL重启后，InnoDB会自动识别已经在故障之前提交的数据，并回退所有故障时未提交的数据，最大限度的保护数据不会丢失(crash recovery)

4）InnoDB存储引擎的属性

3.4、MyISAM

1）MyISAM存储引擎是MySQL老版本的默认存储引擎，由于其表级锁的特性，所以限制了其在读写操作时的性能，常用在只读表上或者读操作占绝大多数的表上，比如一些web应用和数据仓库相关表
2）每个MyISAM表都会在磁盘上生成三个文件，表名和文件名相同但后缀不同，.frm文件存储表的结构信息，.MYD文件存储表的数据信息，.MYI文件存储表的索引信息

3.5、Memory

1）Memory存储引擎将所有数据存储在内存中以便加快对某些不重要数据的访问速度
2）此存储引擎的使用范围已经变小，因为InnoDB已经提供了数据缓存区以便对将经常访问的数据缓存在内存中
3）当MySQL重启时，Memory表中的数据会丢失，但表结构还在
4）Memory只适用在只读表或者读操作占绝大多数的情况，因为对表的写操作也会导致表锁，大大限制了并发性
5）Memory表创建之后，在磁盘文件会生成一个相同表名的文件，后缀为.frm，仅存储表结构而不存储表数据

mysql> CREATE TABLE test ENGINE=MEMORY SELECT ip,SUM(downloads) AS down FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

3.6、CSV

1）Csv存储引擎下的表对应了文本文件，其中的数据用逗号隔开，csv表可用来以csv格式导入和导出表
2）当创建一个csv表时，磁盘会生成三个以表名为名字的文件，.frm存储表的结构信息，而.CSV文件用来存储以逗号隔开的数据信息，.CSM文件用来存储表的元数据，包括表的状态和有多少行数据信息

mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
mysql> SELECT * FROM test;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
[root@db01 abc]# ls
test.CSM  test.CSV  test.frm
[root@db01 abc]# cat test.CSV 
1,"record one"
2,"record two"

3.7、Archive

1）Archive存储引擎表用来存储大量未加索引的历史归档数据
2）archive表会在磁盘创建两个文件，.frm文件用来存储表结构信息，.ARZ文件用来存储历史归档数据
3）Archive表支持insert, replace和select语句，但不支持delete和update语句
4）Archive表支持行级锁
5）Archive支持auto_incrment列，且其列上可以包含一个索引，但在其他字段上不能创建索引
6）Archive不支持对auto_incrment列插入一个小于当前最大值的数据
7）Archive存储引擎会用zlib来压缩数据

3.8、Blackhole

1）Blackhole存储引擎用来接收表插入请求，但不存储数据，所以查询表数据总是返回空，通常用在主从复制的情况下当主库不想保留数据而从库通过复制语句执行而保留数据的情况
2）Blackhole表在磁盘会创建一个文件，.frm文件用来存储表结构

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
mysql> SELECT * FROM test;  #空

3.9、Merge

1）Merge存储引擎可以将一批字段相同，索引相同且顺序相同的MyISAM表在逻辑上看做是同一个
2）Merge表在磁盘上创建两个文件，.frm文件保存表的结构信息，.MRG文件包含所有被视作同一个表的MyISAM表
3）Merge表支持select,delete,update,insert语句执行
4）创建merge表时需要执行union子句，用来将指定的MyISAM结合起来，insert_method选项用来指定插入语句是将数据插入到第一个表FIRST还是最后一个表LAST中，或者不指定或NO代表不允许插入

mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT,message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

3.10、Federated

Federated存储引擎提供了从一个MySQL实例连接其它实例上数据的能力
Federated存储引擎默认是disable状态，如果要开启，则需要在启动MySQL时使用—federated选项

CREATE TABLE federated_table (
 id INT(20) NOT NULL AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL DEFAULT '',
 other INT(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (id),
 INDEX name (name),
 INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';


CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');
CREATE TABLE test_table (
 id INT(20) NOT NULL AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL DEFAULT '',
 other INT(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (id),
 INDEX name (name),
 INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';

3.11、Example

Example存储引擎只存在于MySQL源码中，只针对开发者，对实际的数据库使用者没有太大的意义
Example表只保留表结构，本身不保存数据

3.12、NDB

NDB存储引擎专用在MySQL Cluster软件中，是MySQL自己推出的提高可用性和可靠性的集群软件

四、数据库设计

4.1、E-R模型

1）E-R模型在数据库概念设计阶段广泛采用

2）E-R模型的构成成分是实体集，属性和联系集

实体是把具有属性、性质和特征相同的实体，用所有实体名和他的属性名称的来用抽象的形式描述同种类的实体;一般实体用矩形符号表示，矩形框内标注实体的名称
属性是实体特有的一些特性，一个实体有包含有许多个属性来进行描述。用椭圆形的符号来表示，用无向直线将属性的椭圆和其相对应的实体图形串联起来
联系是ER模型中的联系是用来反映实体内部和实体之间的属性关系。用菱形符号表示，在菱形框内标注联系的名称，然后使用无向直线将有关系的实体属性串联起来，还需要在无向直线上标出实体和联系的类型（1 : 1，1 : n或m : n）

3）例如系、学生和课程的联系的E-R模型
系、学生和课程作为实体集；一个系有多个学生，而一个学生仅属于一个系，所以系和学生之间是一对多的联系；一个学生可以选修多门课程，而一门课程有多个学生选修，所以学生和课程之间是多对多的联系

4）E-R模型的设计也要遵循三范式的要求

4.2、范式

1）第一范式(1NF)：表示每个属性都不可分，是数据库设计的基本要求。仅符合第一范式存在数据冗余过大，插入删除和修改异常的问题

假如学校新建了一个系，但是暂时还没有招收任何学生（比如3月份就新建了，但要等到8月份才招生），那么是无法将系名与系主任的数据单独地添加到数据表中去的（注１）——插入异常
假如将某个系中所有学生相关的记录都删除，那么所有系与系主任的数据也就随之消失了（一个系所有学生都没有了，并不表示这个系就没有了）。——删除异常
假如李小明转系到法律系，那么为了保证数据库中数据的一致性，需要修改三条记录中系与系主任的数据。——修改异常

2）第二范式(2NF)：在第一范式的基础上限制一个表只能表达一个实体

3）第三范式(3NF)：在第二范式的基础上，表中的每列都和主键有直接关系，而不存在传递性依赖

4.3、常用设计工具

1）powerdesigner

2）MySQL Workbench

下载地址https://dev.mysql.com/downloads/workbench/

4.4、字段属性

1）NOT NULL代表表中此列的数据必须存在，默认是NULL容许为空

2）主键(primary key)代表此表的所有数据都可以被主键里的字段区分，创建完主键则默认会在对应字段上创建唯一性索引，且每个主键字段都需要是NOT NULL，一个表上只允许有一个主键

3）外键(foreign key)可以将两个表的数据建立映射关系，并定义不同的外键约束条件以保证数据的一致性，通常为一个父表一个子表，子表中的数据映射到父表对应的列

①Index_name代表外键ID，默认情况下MySQL会在子表上创建一个外键索引

②MySQL推荐在父表和子表的相关字段上都创建索引，以避免全表扫描

③当创建好外键后，任何对子表的插入和修改操作如果对应的值没有在父表中有对应，都会被MySQL拒绝。当在父表上update和delete操作时，对子表中对应数据的操作依赖设置

Cascade代表子表中的数据也自动update和delete（级联）
Set null代表子表中的数据自动修改成null
Restrict(默认)代表如果子表中有对应的数据，则拒绝父表上的update和delete操作
No action在MySQL中的含义和restrict一样
Set default代表将子表中的数据自动修改成default值

CREATE TABLE product (
 category INT NOT NULL, id INT NOT NULL,
 price DECIMAL,
 PRIMARY KEY(category, id)
) ENGINE=INNODB;

CREATE TABLE customer (
 id INT NOT NULL,
 PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE product_order (
 no INT NOT NULL AUTO_INCREMENT,
 product_category INT NOT NULL,
 product_id INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 INDEX (product_category, product_id),  #联合索引
 INDEX (customer_id),
 FOREIGN KEY (product_category, product_id) #外键
 REFERENCES product(category, id)
 ON UPDATE CASCADE ON DELETE RESTRICT,
 FOREIGN KEY (customer_id)
 REFERENCES customer(id)
) ENGINE=INNODB;

4）备注comment可以用在表或者字段上，表达表和字段的含义，最常可以包含1024个字符

posted @ 2019-10-07 11:12 运维人在路上阅读(1130) 评论(0) 编辑收藏举报

刷新页面返回顶部

运维人在路上

当你发现自己的才华撑不起野心时，就请安静下来学习吧