MYSQL——MYSQL的基本使用（存储过程、触发器、视图、函数）

2021-10-06 17:14:31

1.存储过程

概念：

存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似于我们Java中的方法

语法

DELIMITER $$
CREATE
    PROCEDURE `performance_schema`.`myTestPro`()
  
    BEGIN
  
    END$$

DELIMITER ;

注意：创建存储过程需要管理员分配权限 

补充：delimiter是mysql定义结束标记的，在mysql客户端中结束标记默认是分号（；）。
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的结束符。
delimiter $$ 表示mysql用$$表示mysql语句结束，过程结束后肯定会有一句delimiter ；
表示恢复成默认的。

参数

in:输入参数
out：输出参数
inout：输入输出参数


例如：
DELIMITER $$

CREATE
    PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
    BEGIN
	DELETE FROM emp WHERE empno=num;
	SELECT COUNT(*) FROM emp INTO r;
    END$$

DELIMITER ;

调用存储过程 call

语法：call myTestPro(9527,@rr)
查询结果： select @rr

删除存储过程 drop

语法： drop procedure myTestPro

查看存储过程 show

show procedure status\G; – 查看所有的存储过程状态
show create procedure 存储过程名字\G; – 查看创建存储过程的语句

带有IF逻辑的存储过程 if then elseif else

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='星期一';
	ELSEIF num=2 THEN              --注意elseif  连在一块
		SET str='星期二';
	ELSEIF num=3 THEN
		SET str='星期三';             -- 注意要用分号结束
	ELSE
		SET str='输入错误';
	END IF;                        -- 注意要结束if  后面有分号
END $$

带有循环的存储过程 while do

DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;  -- 要记得结束循环
	SET result=vsum;
END $

其他循环的写法

例如：  REPEAT 循环 
DELIMITER $
 CREATE PROCEDURE proRepeat(OUT outnum INT)
     BEGIN
     DECLARE i INT DEFAULT 0;
     REPEAT
         SET i=i+1;
         UNTIL i>=5  -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
     END REPEAT; -- 记着结束
     SET outnum=i;
  END $
  
  
  
  CALL proRepeat(@num);
  SELECT @num;
  
  
 例如：loop 循环 
 
 DELIMITER $$

CREATE
    PROCEDURE `mydemo3`.`proLoop`(OUT outnum INT)
   
    BEGIN
	DECLARE i INT DEFAULT 0;
      myloop:LOOP    -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
         SET i=i+1;
        IF i>=5 THEN
            LEAVE myloop;  -- 根据我的循环标号中断循环 leave 是中断循环的意思
         END IF;
    END LOOP;
     SET outnum=i;
    END$$

DELIMITER ;
  
  CALL proLoop(@num);
  SELECT @num;

控制循环的两个关键字

leave 相当于java中的 break
iterate相当于java中的continue

变量

全局变量（内置变量）：可以在多个会话中去访问他
 -- 查看所有全局变量： 
         show variables
        -- 查看某个全局变量： 
          select @@变量名
        -- 修改全局变量： 
          set 变量名=新值
        -- character_set_client: mysql服务器的接收数据的编码
        -- character_set_results：mysql服务器输出数据的编码
        SET @@character_set_client=gbk   –设置数据库编码
        set @@character_set_results=gbk
        
        SET @@character_set_client=utf8;   –设置数据库编码
        set @@character_set_results=utf8;
         SELECT @@character_set_client  --查看数据库编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
        -- 定义会话变量: 
        set @变量=值
        -- 查看会话变量：
        select @变量
        
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
		定义局部变量的语法：
		DECLARE i INT DEFAULT 1;  
		给变量设置值 set i=10;

2.触发器 Trigger

概念

触发器：数据库中的一个对象，相当于JS中的监听器，触发器可以监听增删改三个动作
比如说我想监听一张表，只要我增删改了这张表中的数据，我就可以触发这个触发器，去往另外一张表中记录一下日志

语法

DELIMITER $$

CREATE
    TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `mytestdb`.`<Table Name>`
    FOR EACH ROW 
    BEGIN
     
    END$$

DELIMITER ;
BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
 FOR EACH ROW 行级触发，每操作一行就触发

简单演示

我往一张表test中插入了数据，在日志表logger中添加一条记录
DELIMITER $$

CREATE TRIGGER `mytestdb`.`MyTri3` AFTER DELETE ON test FOR EACH ROW 
    BEGIN
	INSERT INTO logger VALUES(NULL,"你删除了一条数据",NOW());
    END$$

DELIMITER ;

old和new

old.字段 可以获取到被监听的表中的字段的旧值
new.字段 可以获取到被监听表中更新后的字段的新值 比如插入新值或者修改旧值
    
例如：我往一张表t1中添加一条数据，另一张表t2也要添加一条同样的数据
  DELIMITER $$
CREATE
    TRIGGER `mytestdb`.`myTri6` AFTER INSERT
    ON `mytestdb`.`t1`
    FOR EACH ROW BEGIN
	INSERT INTO t2 VALUES(new.id,new.username,new.age);
    END$$

DELIMITER ;
例如：我修改一张表t1中的数据，另一张表t2中的数据也要修改
  DELIMITER $$
CREATE
    TRIGGER `mytestdb`.`MyTri7` AFTER UPDATE
    ON `mytestdb`.`t1`
    FOR EACH ROW 
    BEGIN
	UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id;
    END$$

DELIMITER ;

3.视图 View

概念

视图:有结构(有行有列),但没有结果(结构中不真实存储数据)的虚拟的表,
虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图数据的来源)

语法

create view 视图名称 as select语句(这个语句可以是一张或多张表的的普通查询,或多表查询)
      例如:创建单表视图 
  	create view my_v1 as select * from student;
  
      例如:创建多表视图 注意:不要查询两张表中的同名字段 不然会报错
  	create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;
  	注意：低版本MySQL中视图不支持封装子查询查出来的数据，我现在用的是MySQL5.7 是支持 子查询查出来的数据

查看视图

其实视图是一张虚拟表那关于查询表的语句对视图都是可以用的
比如:show tables; desc my_v1
只是在查看视图创建语句的的时候把table 改成view
如:show create view my_v1;

视图一旦创建,系统会在视图对应的数据库文件夹下,创建一个对应的结构文件:frm文件.

视图的使用:

视图的使用,只是为了简化查询,你可以把视图当作表一样去使用例如:select * from my_v1;

视图的执行:其实本质就是执行封装的select语句

删除视图:

drop view 视图名称
例如:drop view my_v1

修改视图:

视图本身不可以修改,但是视图的来源是可以修改的(其实就是修改select 语句)
语法: alter view 视图名字 as 新的select语句

视图的意义:

(1)视图可以节省SQL语句,将一条复杂的查询语句,使用视图进行保存,以后可以直接对视图进行操作.
(2)数据安全,视图操作注意是针对查询语句的,如果对视图结构进行处理(比如删除),不会影响基表的数据.
所以相对来说数据比较安全
(3)视图往往是在大项目中去使用,而且是多系统中去使用.我可以对外提供一些有用的数据,隐藏一些关键的数据.
(4)视图对外可以提供友好的数据:不同的视图提供不同的数据,对外提供的数据好像是经过专门设计的一样.
(5)视图可以更好的进行权限控制比如对外隐藏我的一些基表的名称

视图数据的操作:

视图是可以进行数据操作的(比如增,删,改,视图中的数据),但是有很多限制

	视图插入数据:
	(1)多表视图不能插入数据
	(2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
	(3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)

	视图删除数据
	(1):多表视图不能删除数据
	(2):单表视图可以删除数据,也会影响到基表
	 
	 视图更新数据
	 (1):单表视图,多表视图都可以更新数据
	     更新限制:with check option
	     例如:create view my_v1 as select * from student where age>30 with check option;
	     表示视图数据的来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到
	     数据age>30的学生 改成age<30 的.

	     那么:update  my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制

4.函数（方法）

函数：包括内置函数，和自定义函数
自定义函数语法


  DELIMITER $$
  
  CREATE
     
      FUNCTION `mytestdb`.`myFun`(num INT)
      RETURNS INT

      BEGIN
  	DECLARE i INT DEFAULT 100;
  	SET i=i+num;
      RETURN i;
      END$$
  
  DELIMITER ;
 
  函数的调用 select 函数名();

函数和存储过程的区别

1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
2.存储过程有in out inout 这几个参数类型函数的参数全是用来收实参

5.数据库的权限（DCL）

 -- mysql数据库权限问题：
    root ：拥有所有权限（可以干任何事情）
   -- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表
   -- 如何修改mysql的用户密码？
   -- password: 
    password('123456')
    md5加密函数(单向加密)
   SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
   
   -- MySQL5.7 查询root用的密码
   SELECT authentication_string FROM USER WHERE USER='root';
   
  --  mysql数据库，用户配置 : user表
  USE mysql; -- 使用数据库
  Select password(‘root’ )   查询用户密码
  SELECT * FROM USER;  -查询数据库用户
  
  -- MySQL5.5修改密码
  UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
  -- MySQL5.7修改密码 
  UPDATE USER SET authentication_string=PASSWORD('123456') WHERE USER='root';
  
  -- 分配权限账户
  权限： select insert delete update drop create/  或,all
  @ 后面可以是localhost 也可以是ip  也可以给% 那%代表任意一台计算机都可以连接上来
  语法:
  GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
  
  GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
  
  注意分配多个权限用逗号隔开
  GRANT DELETE,SELECT,UPDATE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
  GRANT all ON *.* TO 'eric'@'localhost' IDENTIFIED BY '123456';
  
  --方式2：分配账户和权限
   INSERT INTO USER 
          (HOST, USER, PASSWORD, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');

  删除用户
  Delete FROM user Where User='eric' and Host='localhost';
  
  --- 数据库备份和还原 注意备份还原不需要登陆数据库
  备份:mysqldump -uroot -p day02 > d:/back.sql
  C:\Documents and Settings\Administrator>mysqldump -uroot -p day02 > d:/back.sql
         Enter password: ****	
       恢复：mysql -uroot -p day02 < d:/back.sql
        注意恢复之前 先创建跟你原来一样的名称的数据库 相当于一个空的数据库,然后再还原
       C:\Documents and Settings\Administrator>mysql -uroot -p day02 < d:/back.sql
       Enter password: ****

6.远程登录数据库

1.切换库
use mysql; 

 2.授权远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 

3.刷新
flush privileges; 

4.然后退出mysql
  exit;
  
5.重启mysql服务
net stop mysql
net start mysql


6.远程登录：mysql -h 192.168.17.123 -P 3306 -u root -p123456

7.数据库表设计

数据库设计
	引入
	 需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型
	 
				图书模型：图书名称，版本号，作者，出版社
				学生模型： 学号，学生姓名 手机号码
				......
				角色：学生 老师，图书管理员
			《需求说明书》
	 需求设计 -  
				概要设计：		
					 抽取实体：业务模型 -> 实体模型（java 类 c++类）内存
							class Book{ name, bookNo,author }
					 数据库设计:
							业务模型/实体模型 - > 数据模型 (硬盘)
				
							数据库表设计
							问题： 如何设计？
				详细设计
					类详细，属性和方法
					
 三大范式
		设计原则： 建议设计的表尽量遵守三大范式。

第一范式： 要求表的每个字段必须是不可分割的独立单元
		student     :   name              -- 违反第一范式  
										  张小名|狗娃					
		sutdent    ： name    old_name    --符合第一范式
					  张小名    狗娃

第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
					
		employee（员工）: 员工编号  员工姓名 部门名称   订单名称  --违反第二范式

					员工表：员工编号  员工姓名 部门名称   

					订单表：  订单编号  订单名称            -- 符合第二范式
					      
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

	员工表： 员工编号（主键） 员工姓名  部门编号  部门名 --符合第二范式，违反第三范式																	（数据冗余高）

	员工表：员工编号（主键） 员工姓名  部门编号    --符合第三范式（降低数据冗余）
   
   
     部门表：部门编号  部门名

补充内容

-- 条件选则 比如给不同职位的人加不同的工资  用 case  when  then end  语句来完成

-- 例如:
SELECT ename,job,sal AS 涨前, 

CASE job 

WHEN 'PRESIDENT' THEN sal+1000

WHEN 'MANAGER' THEN sal+800

ELSE sal+300

END AS 加后 


FROM emp;

定义一个变量，作为一个临时展示的字段

SELECT (@i:=@i+1) AS id,ename,job,sal FROM emp,(SELECT @i:=0) AS init;


说明：mysql 这句话什么意思，SELECT @i:= 0
它的意思是为变量@i赋值（如：set @i=0;）。
在mysql中用户变量赋值有两种方式，一种是=另一种是:= 

其中区别在于使用set赋值时两种方式都可以使用，使用select赋值时只能使用:=。

码农公寓

MYSQL——MYSQL的基本使用（存储过程、触发器、视图、函数）

目录

1.存储过程

变量

2.触发器 Trigger

3.视图 View

4.函数（方法）

5.数据库的权限（DCL）

6.远程登录数据库

7.数据库表设计

补充内容

码农公寓

目录

1.存储过程

变量

2.触发器 Trigger

3.视图 View

4.函数（方法）

5.数据库的权限（DCL）

6.远程登录数据库

7.数据库表设计

补充内容

相关文章