MySQL数据库——day28复制表,procedure,触发器Trigger,视图

最新推荐文章于 2022-12-28 19:06:29 发布

@李小白

最新推荐文章于 2022-12-28 19:06:29 发布

阅读量327

点赞数

分类专栏： MySQL数据库

本文链接：https://blog.csdn.net/lalaMarry/article/details/100110373

版权

MySQL数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

sql最后的,感觉在数据库这块学的不是很好,很多东西都没有记住,边学边忘…

1. 约束

作用:为了保证数据的有效性和完整性
mysql中常用的约束
主键约束:(primary key)
自增长约束:(auto_incrment)加在整数型的字段和主键约束来使用
唯一约束:(nuique)
非空约束(not null)
外键约束(foreign key)
sex ENUM(‘男’,‘女’)把一个字段的数据类型设置成枚举类型,也可以起到一定的约束效果
unsigned(非负约束)
例如:TINYINT -128—127 带符号的范围是-128—127.无符号的范围是0—255

2.创建一张表,表的字段和数据来一条查询语句

创建一张表,会把另一张表的字段和数据全部都复制过去
语法:create table 表名 as selete * from 另一张表 where 1=1;
如果我们只是想要字段不要数据的话
create table 表名 as selete * from 另一张表 where 1=2;
如果我只是想要个别的字段的话
create table 表名 as selete 表名.字段名表名.字段名 from 另一张表 where 1=2;
我创建的表也可以来自一个子查询
create table 表名 as 子查询

3.存储过程procedure

概念:
类似于java中的方法被封装可以重复调用.意思就是存储过程中数据库的一个对象,存储在服务端,用来封装多条SQL语句且带有逻辑性,由于在创建的时候随sql进行了编译所以执行速度比较快,而且可以重复调用
语法

DELIMITER  $$
CEATE
PROCEAURE ' prefromance-schema'.'myTestpro'()
BEGIN

END$$

DELIMITER ;

//注意,创建过程需要管理员分配权限
//补充:delimiter是mysql定义的结束标志,在mysql客户端中结束标记默认是分号(;).
如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的结束符。
delimiter $$ 表示mysql用$$表示mysql语句结束，过程结束后肯定会有一句delimiter ；
表示恢复成默认的。

例如：
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 ;

参数
in:输入参数
out：输出参数
inout：输入输出参数
调用存储过程 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 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 $
  
  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   –设置数据库编码
         SELECT @@character_set_client  --查看数据库编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
        -- 定义会话变量: set @变量=值
        -- 查看会话变量： select @变量
        
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
		定义局部变量的语法：DECLARE i INT DEFAULT 1;  
		给变量设置值 set i=10；

触发器 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中插入了数据,在日志表中loggger 中添加一条记录

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 'mytestdp'.'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 'mytest'.t1
FRO  EACH  ROW
BEGIN
UPDATE  t2  SET  id=new.id,username=new.username,age=new.age  WHERE  id=old.id;
END$$
DELIMITER

视图

概念
有结构(有行有列),但是没有结果(不存在真实的数据)的虚拟的表
虚拟表的结构来源不是自己定义的,而是从对应的基表中得来的
创建视图的语法
create view 视图名 seletct (这里面可以是一张表或者多张表的普通查询,也可以是多表查询)
例如:单表查询
create view 视图名 seletct * from student;
例如:多表查询
查看视图视图==一张虚拟的表,查表的语法完全适用
视图一旦被创建,系统就会在对应的数据库文件下创建结构文件:frm文件.
视图的使用:视图的存在只是为了查询,可以用表的语句进行查询
删除视图: drop view 视图名
修改视图:视图本身不可以修改但是,视图的来源是可以修改的
视图的意义