【Mysql】Mysql常用（2）

1 表索引

索引类似图书的目录，方便快速定位，查找指定内容，提高查询速度。
索引是数据库的一个对象，数据库中的一列或者多列组成，它不能独立存在，必须对某个表对象进行依赖。
索引保存在information_schema数据库里的STATISTICS表中。

1.1 索引分类

查看数据库 db_book 内所有索引：

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'db_book';

查看当前table的索引：

show index from tableName;

1.2 创建索引

创建的关键字 INDEX
一般主键在创建时默认就是唯一性索引。
方法1：最开始创建表的时候添加索引：

create table t_u1(id int ,
                     uName varchar(20),
                     password varchar(20),
                     INDEX (uName)
	             );		#普通 单列 索引
    	             
create table t_u2(id int ,
                     uName varchar(20),
                     password varchar(20),
                     UNIQUE INDEX index_uName(uName)
	             );		#带别名的唯一性索引  注意对应位置含义

create table t_u3(id int ,
                     uName varchar(20),
                     password varchar(20),
                     INDEX index_uName_password(uName,password)
	             );	
#多列索引，uName,password两列属性指向一个索引

方法2：为已经存在的表添加索引：

create 	INDEX index_uName ON t_u4(uName);		#普通索引
create 	UNIQUE INDEX index_uName ON t_u4(uName);	#唯一性
create  INDEX index_uName_password ON t_u4(uName,password);		#多列

方法3：使用alter方法创建索引：

alter TABLE t_u5 ADD INDEX index_uName(uName);
alter TABLE t_u5 ADD UNIQUE INDEX index_uName(uName);
alter TABLE t_u5 ADD INDEX index_uName_password(uName,password);

1.3 删除索引

DROP INDEX 索引名 ON 表名;

DROP INDEX index_userName ON t_u5;
DROP INDEX index_userName_password ON t_u5;	#删除多列索引

2 数据库视图（View）

• 视图是从一个或几个基本表（或视图）中导出的虚拟的表。
• 视图是从一个或多个实际表中获得的，那些用于产生视图的表叫做该视图的基表。在系统的数据字典中仅存放了视图的定义，不存放对应的数据，通过视图看到的数据存放在基表中。
• 通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。
• 由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）。

2.1 视图的好处

• 提高重用性，针对重复使用的属性字段；
• 增强安全性，不同权限用户使用部分数据创建的对应视图；
• 表的逻辑独立性。

可参考：博客

2.2 创建视图

ALGORITHM表示视图选择的算法（可选参数）

 - UNDEFINED：MySQL将自动选择所要使用的算法
 - MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
 - TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

WITH CHECK OPTION表示更新视图时要保证在该试图的权限范围之内（可选参数）

2.2.1 单表视图

CREATE VIEW v1 AS SELECT * FROM t_book;
SELECT * FROM v1;		#所有的字段属性作为视图1
CREATE VIEW v2 AS SELECT bookName,price FROM t_book;
SELECT * FROM v2;		#仅取name和price作为创建视图

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;
SELECT * FROM v3;		#将name和price字段重命名为 b 和 p

2.2.2 多表视图

同一个数据库下不同表：

create view v as (select * from table1) union all (select * from table2);	#合并查询
或：
CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;
SELECT * FROM v5;		#从两张取了别名的表 里按指定条件创建视图

不同数据库下的不同表：

#在数据库1 目录下创建
create view 数据库1.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

#在数据库2 目录下创建
create view 数据库2.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

2.3 查看视图

通过指令或数据库图形管理工具查看。

DESC view1;
#基本的视图信息：字段名、类型等

SHOW TABLE STATUS LIKE 'view1';
SHOW TABLE STATUS LIKE 't_book';
# 查看状态信息：包含创建时间，一些虚拟状态值

SHOW CREATE VIEW view1;
#详细信息，包括编码，建表语句等

2.4 修改视图

方法1：

CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

方法2：

ALTER VIEW v1 AS SELECT * FROM t_book;

2.5 更新视图

• 通过视图来插入、更新、删除表中的数据。
• 视图只是一个虚表，没有数据，操作的是识图来源的基表
• 更新视图要在全县范围内。

2.5.1 插入视图（数据）

INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

2.5.2 更新视图（数据）

UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

2.5.3 删除视图（数据）

DELETE FROM v1 WHERE id=5;

2.6 删除视图

删除数据库中已经存在的视图，删除视图并不会删除数据，只是删除视图定义。

DROP VIEW IF EXISTS view1;

3 触发器（TRIGGER）

执行某项操作（INSERT、UPDATE、DELETE）时候自动触发执行预设好的相对应操作。
如在表a添加数据时表b里的对应属性也进行一个变化。

相同的表相同的操作只能创建一个对应的触发器。

3.1 创建使用触发器

单个执行语句的触发器：

注意执行语句where 后面的部分old和new

• old表示插入之前的值，old用在删除和修改

• new表示新插入的值，new用在添加、更新和修改

   #在insert插入新数据操作  之后  更新书的数量
   CREATE TRIGGER trig_book AFTER INSERT 
        ON t_book FOR EACH ROW
           UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
       
   INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);
  

多条执行语句：

DELIMITER | 是告诉mysql解释器不要将多条程序体里面单条语句的分号 ；直接执行，是作为一个程序体的。也可以使用 $$ // 等表示
默认mysql是遇到一个分号 ； 执行一次的。
可参考：厚积_薄发博客

DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE 
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
       #mysql里NOW（）表示当前时间
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END 
|
DELIMITER ;

DELETE FROM t_book WHERE id=5;

上面函数的作用是：当删除 t_book 里面 id=5的数据时，触发在 t_bookType 表中 bookNum数量-1，在日志表 t_log 中加入一条记录，同时在测试表 t_test 里面删除了和之前记录id相等的数据 （old.bookTypeId=t_test.id）

3.2 查看触发器状态

查看触发器的状态：

SHOW TRIGGERS;
#列出来所有的

3.3 删除触发器

注意结束符号 ；之前是否有空格或者全半角区别，可能引起错误。

DROP TRIGGER trig_book  ;

触发器补充介绍：硕果累累的博客

4 函数（functions）

4.1 常用函数

4.1.1 日期和时间函数

获取当前时间的语句：

select now();
select sysdate();

获取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒

set @test='2019-03-26 23:08:12.123456';

select date(@test); 		#示例
+-------------+
| date(@test) |
+-------------+
| 2019-03-26  |
+-------------+

其他类似：time、year 、quarter、month、week、day、hour、minute、second

与时间相关函数：

名称	作用
CURDATE()	返回当前日期
CURTIME()	返回当前时间
MONTH(d)	返回当前日期d中的月份值

SELECT CURDATE(),CURTIME(),MONTH(birthday) AS m FROM table;
select curdate(),curtime() from gongkuang limit 5;

4.1.2 字符串函数

名称	作用
CHAR_LENGTH(s)	计算字符串字符数
UPPER(s)	转为大写
LOWER(s)	转为小写
CONCAT(s1，s2…)	拼接多个字符串
LEFT(str,len) RIGHT(str,len)：	返回字符串str 从左、右起len长的子串。
REVERSE(str)	把str倒序

SELECT uName,CHAR_LENGTH(uName),UPPER(uName) up,LOWER(uName) low,concat(uName,upper(uName)) FROM t_u1;

输出：

select uName,left(uName,2),right(uName,2) from t_u1;

其他嗨哟很多字符串函数可以需要的时候再查阅使用：

ASCII(str)	BIN(N)	CONV(str,from_base,to_base)	ELT(N,str1,str2,str3,…)
FIELD(str,str1,str2,str3,…)	FIND_IN_SET(str,strlist)	FORMAT(X,D)	INSERT(str,pos,len,newstr)
LOCATE(substr,str,pos)	REPLACE(str,from_str,to_str)	POSITION(substr IN str)	INSTR(str,substr)

4.1.3 数学函数

名称	作用
ABS(x)	绝对值
SQRT(x)	平方根
MOD(x，y)	求余
ROUND(X,Y)	X的Y位四舍五入小数
CEIL(x)、CEILING(x)	向上取整
FLOOR(x)	向下取整
POW(x,y)、POWER(x,y)	幂运算，求x的y次方幂

4.1.4 加密函数

名称	作用
PASSWORD(str)	对密码加密，不可逆
MD5(str)、SHA5()	MD5校验、SHA5校验
ENCODE(str，pswd_str)	加密字符串，结果必须用BLOB类型保存
DECODE(crypt_str,pswd_str)	解密字符串

在插入数据的时候设置加密的格式、加密数据和加密解密的钥匙：

INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,MD5('123456'),ENCODE('abcd','aa'));
SELECT DECODE(passwords,'aa') FROM t_t WHERE id=1;

4.2 其他函数可以查阅mysql官方手册

5 存储过程

5.1 存储过程和函数定义

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输；

区别：

• 存储函数必须有返回值，而存储过程没有，
• 存储过程的参数可以使用 in，out，inout 类型，而存储函数的参数只能是 in 类型的。
• 如果有函数从其他类型的数据库迁移到 MySQL，那么就可能因此需要将函数改造成存储过程

5.1.1 创建存储过程：

创建存储过程或函数：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type

// 参数取值
    proc_parameter:[ IN | OUT | INOUT ] param_name type
    func_parameter:param_name type
    type:Any valid MySQL data type
    characteristic:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'
    
    routine_body:Valid SQL procedure statement or statements 

参数说明

• sp_name 参数是存储过程/或存储函数的名称

• proc_parameter 表示存储过程的参数列表

  • - [ IN | OUT | INOUT ] param_name type
    - IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
      存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型
    

• characteristic 参数指定存储过程的特性

  • 参数很多：LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'
    
    	LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成
    	
    	[NOT] DETERMINISTIC  存储过程输出结果是否是确定的，即每次输入一样输出也一样的程序
    	
    	{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } 指明子程序使用 SQL 语句的限制
    			- CONTAINS SQL子程序包含 SQL 语句，但不包含读或写数据的语句。默认格式
    			- NO SQL 表示子程序中不包含 SQL语句
    			- READS SQL DATA 子程序中包含读数据的语句
    			- MODIFIES SQL DATA 表示子程序中包含写数据的语句
    			
    	SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。
    			- DEFINER 表示只有定义者自己才能够执行；
    			- INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。
    		
    	COMMENT ‘string’ ：注释信息；
    

• routine_body 参数是 SQL 代码的内容，可以用 BEGIN…END 来标志 SQL 代码的开始和结束

创建存储过程/函数示例sql语句

DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
   READS SQL DATA
   BEGIN 
     SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
   END 
   &&
DELIMITER ;

CALL pro_book(1,@total);		//调用存储过程


DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
 RETURNS VARCHAR(20)
 BEGIN 
  RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
 END 
    &&
DELIMITER ;

SELECT func_book(2);			//调用存储函数

5.1.2 修改存储过程或函数：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:参数含义类似

5.1.3 查看现有存储过程/函数

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']		//查看状态

SHOW PROCEDURE STATUS LIKE 'pro_book';

SHOW CREATE {PROCEDURE | FUNCTION} sp_name				//查看定义

5.1.4 删除存储函数/过程

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

DROP PROCEDURE pro_user3;

5.2 存储函数变量

5.2.1 存储函数/过程中定义变量

DECLARE var_name[,...] type [DEFAULT value]

定义的变量不赋值时默认插入为NULL

DELIMITER &&
CREATE PROCEDURE pro_user()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;

5.2.2 存储函数过程变量赋值

//方式1 直接赋值
SET var_name = expr [, var_name = expr] ...
//方式2 从其他表查询结果赋给当前表
SELECT col_name[,...] INTO var_name[,...] table_expr

分别表示直接赋值和查询结果赋值

DELIMITER &&
CREATE PROCEDURE pro_user2()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 SET a='java1234',b='123456';
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;
---------------------------------
DELIMITER &&
CREATE PROCEDURE pro_user3()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
	 INSERT INTO t_user VALUES(NULL,a,b);
	END 
&&
DELIMITER ;

5.3 游标

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标必须声明在处理程序之前，并且声明在变量和条件之后。

游标的使用包括

• 声明游标

  • DECLARE cursor_name CURSOR FOR select_statement ;
    

• 打开游标

  • OPEN cursor_name;
    

• 使用游标

  • FETCH cursor_name INTO var_name [,var_name ... ]；
    

• 关闭游标。

  • CLOSE cursor_name；
    

DELIMITER &&
CREATE PROCEDURE pro_user4()
	BEGIN
	 DECLARE a,b VARCHAR(20) ;
	 DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;
	 OPEN cur_t_user2;
	 FETCH cur_t_user2 INTO a,b;	//未使用循环，所以只取出、插入了集合的一条数据
	 INSERT INTO t_user VALUES(NULL,a,b);
	 CLOSE cur_t_user2;
	END 
&&
DELIMITER ;

5.4 流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。

5.4. 1 IF语句

IF search_condition THEN statement_list
    [ ELSEIF search_condition THEN statement_list ]...
    [ ELSE statement_list ]
END IF

举例：根据传入的id查询，结果数量大于0时更新这个id对应的name，否则新插入一条数据

DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
	BEGIN
	 SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
	 IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
	 ELSE
	   INSERT INTO t_user VALUES(NULL,'2312312','2321312');
	 END IF ;
	END 
&&
DELIMITER ;

5.4. 2 CASE 语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list ]
END CASE

举例：传入id，查询的结果数量为1时更新，2时插入，其他情况插入另一条

DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
	BEGIN
	 SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
	 CASE @num
	  WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
	  WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
	  ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
	 END CASE ;
	END 
&&
DELIMITER ;

5.4. 3 LOOP，LEAVE 语句

LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环
的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：

LEAVE 语句主要用于跳出循环控制。语法形式如下：

[begin_label：]LOOP
Statement_list
END LOOP [ end_label ]

LEAVE 语句主要用于跳出循环控制。语法形式如下：

LEAVE label

举例：循环插入指定参数条数据

DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	    ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	    END IF ;
	  END LOOP aaa ;
	END 
&&
DELIMITER ;

5.4. 4 ITERATE 语句

ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次
循环。基本语法：

ITERATE label ;

示例：指定插入次数参数为3的时候不插入，其余在0之前的都插入

DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	    ELSEIF totalNum=3 THEN ITERATE aaa ;
	    END IF ;
	    INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	  END LOOP aaa ;
	END 
&&
DELIMITER ;

5.4. 5 REPEAT 语句

REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本语法形式如下：

[ begin_label : ] REPEAT
    Statement_list
    UNTIL search_condition
END REPEAT [ end_label ]

举例：

DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
	BEGIN
	  REPEAT
	     SET totalNum=totalNum-1;
	     INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	     UNTIL totalNum=1 
	  END REPEAT;
	END 
&&
DELIMITER ;

5.4. 6 WHILE 语句

[ begin_label : ] WHILE search_condition DO
	Statement_list
END WHILE [ end_label ]

举例：

DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
	BEGIN
	 WHILE totalNum>0 DO
	  INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
	  SET totalNum=totalNum-1;
	 END WHILE ;
	END 
&&
DELIMITER ;

6 数据备份与还原

备份数据可以保证数据库中数据的安全，需要定期的进行数据库备份，可以备份数据表和整个数据库。

6.1 使用 mysqldump 备份（导出）

可以使用命令备份，也可以使用图形管理工具直接导出备份。

mysqldump -username -p dbname table1 table2 ... > BackupName.sql

• dbname ：表示数据库的名称；
• table1 和 table2 ：表示数据表的名称，没有指明时将备份整个数据库；
• BackupName.sql ：表示备份文件的名称，文件名前面可以加绝对路径，一般以sql做后缀

6.2 还原（导入sql数据）

使用命令或者图形界面导入还原数据。

mysql -u root -p [dbname] < backup.sql

• dbname ：表示导入后的数据库名称，参数可指定或不指定
• 指定数据库名时，表示还原文件到这个表下
• 不指定数据库名时，表示还原备份sql文件中默认的数据库和表属性。