做外贸如何建立网站平台,服装网站设计,医院网站建设模板,基本公共服务标准化一、视图
1、介绍
视图#xff08;view#xff09;是一种虚拟存在的表。视图中的数据并不在数据库中实际存在#xff0c;行和列数据来定义视图的查询中使用的表#xff08;基表#xff09;#xff0c;并且是在使用视图时动态生成的。
通俗的讲#xff0c;视图只保存了…一、视图
1、介绍
视图view是一种虚拟存在的表。视图中的数据并不在数据库中实际存在行和列数据来定义视图的查询中使用的表基表并且是在使用视图时动态生成的。
通俗的讲视图只保存了查询的SQL逻辑不保存查询结果。所以我们在创建视图的时候主要的工作就落在创建这条SQL查询语句上。
2、操作语法
2.1、创建 CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION] 2.2、查询 查看创建视图语句SHOW CREATE VIEW 视图名称; 查看视图数据SELECT * FROM 视图名称.....; 2.3、修改 方式一CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION] 方式二ALTER VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION] 2.4、删除 DROP VIEW [IF EXISTS] 视图名称; #创建视图
create or replace view stu_v as select id,name from student where id10;
#查询视图
show create view stu_v;
select * from stu_v;
select * from stu_v where id3;
#修改视图
create or replace view stu_v as select id,name,no from student where id10;
alter view stu_v as select id,name from student where id10;
#删除视图
drop view if exists stu_v; 3、检查选项
create or replace view stu_v as select id,name from student where id10;
select * from stu_v;
insert into stu_v values(5,tom);
#视图并不存储数据数据在基表当中存储也就意味着我们插入的数据实际上是插入到student表当中了
insert into stu_v values(15,tom);
#基表当中会包含这条数据但是在查询视图时并不包含这条数据这是因为在创建视图时指定了id10为了避免这种情况的发生可以加上检查选项
1.1、试图当中的检查选项
当使用 WITH CHECK OPTION 子句创建视图时MySQL会通过视图检查正在更改的每个行例如 插入更新删除以使其符合视图的定义 。MySQL允许基于另一个视图创建视图它还会检查以来视图中的规则一保持一致性。为了确定检查的范围MySQL提供了两个选项CASCADED 和 LOCAL,默认值为CASCADED。
CASCADED:
#CASCADE :
create or replace view stu_v1 as select id,name from student where id20;
create or replace view stu_v2 as select id,name from stu_v1 where id10 with cascaded check option ;
insert into stu_v2 values(5,tom);
insert into stu_v2 values(25,tom);
#都不能插入成功会检查stu_v2所依赖的底层所有的视图
create or replace view stu_v3 as select id,name from stu_v2 where id15;
insert into stu_v3 values(11,tom);
insert into stu_v3 values(17,tom);#能执行成功会在基表中出现该数据stu_v3视图的条件不需要检查只需要检查另外两个视图
insert into stu_v3 values(28,tom);
LOCAL:
#LOCAL :
create or replace view stu_v_1 as select id,name from student where id20;
create or replace view stu_v_2 as select id,name from stu_v_1 where id10 with local check option ;
insert into stu_v_2 values(5,tom);
#需要检查上一个视图的条件但上一个视图没有with check option选项并且也不满足当前视图的条件
insert into stu_v_2 values(25,tom);
#满足当前视图的条件虽然不满足上一个视图的条件但上一个视图没有with check option选项因此可以插入
create or replace view stu_v_3 as select id,name from stu_v_2 where id15;
insert into stu_v_3 values(11,tom);
insert into stu_v_3 values(17,tom);
insert into stu_v_3 values(28,tom);
#都可以插入进来 4、更新及作用
4.1、视图的更新
要使视图更新视图中的行为与基础表中的行之间必须存在一对一的关系。如果试图中包含一下任何一项则该视图不可更新
1、聚合函数或窗口函数SUM()、MAX()、MIN()、COUNT()等
2、DISTINCT
3、GROUP BY
4、HAVING
5、UNION 与 UNION ALL
4.2、视图的作用
1、简单试图不仅可以简化用户对数据的理解也可以简化他们的操作。那些被经常使用的查询可以被定义为视图从而使得用户不必为以后的操作每次指定全部的条件。
2、安全数据库可以授权但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3、数据独立视图可以帮助用户屏蔽真实表结构带来的影响。
二、存储过程
1、介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合调用存储过程可以简化应用人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。
存储过程思想上很简单就是数据库SQL语言层面的代码的封装与重用。
2、特点
封装、复用可以接收参数也可以返回数据减少网络交互效率提升。
3、操作语法
3.1、创建 CREATE PROCEDURE 存储过程名称[(参数列表)] BEGIN --SQL语句 END; 3.2、调用 CALL 名称 [(参数)]; 3.3、查看 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUINE_SCHEMAxxx;#查询指定数据库的存储过程及状态信息 SHOW CREATE PROCEDURE 存储过程名称;#查询某个存储过程的定义 3.4、删除 DROP PROCEDURE [IF EXISTS] 存储过程名称; #创建
create procedure p1()
beginselect count(*) from student;
end;
#调用
call p1();
#查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMAtest;
show create procedure p1;
#删除
drop procedure if exists p1; 注意在命令行中执行创建存储过程的SQL时需要通过关键字delimiter 指定SQL的结束符 delimiter $$
mysql create procedure p1()- begin- select count(*) from student;- end$$
Query OK, 0 rows affected (0.01 sec)
4、变量 4.1、系统变量
系统变量是MySQL服务器提供不是用户定义的属于服务器层面分为全局变量GLOBAL、会话变量SESSION。
4.1.1、查看系统变量 SHOW [SESSION | GLOBAL] VARIABLES;#查看所有系统变量 SHOW [SESSION | GLOBAL] VARIABLES LIKE....;#可以通过LIKE模糊匹配的方式查找变量 SELECT [SESSION | GLOBAL] 系统变量名;#查看指定变量的值 4.1.2、设置系统变量 SET [SESSION | GLOBAL] 系统变量名值; SET [SESSION |GLOBAL] 系统变量名值; #查询系统变量
show session variables;
show session variables like auto%;#查看与事务自动提交相关的变量
select session.autocommit;
#设置系统变量
set session autocommit0;
set session.autocommit1;
注意
1、如果没有指定SESSION/GLOBAL默认是SESSION会话变量
2、MySQL服务器重启之后所设置的全局参数会失效。
4.2、自定义变量
用户自定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用“变量名”使用就可以。其作用域为当前连接。
4.2.1、用户变量赋值 SET var_nameexpr [,var_nameexpr]...; SET var_name:expr [,var_name:expr]...; 推荐使用:因为在MySQL中判断相等与赋值都是用的为了避免混淆用:。 SELECT var_name:expr [,var_name:expr]...; SELECT 字段名 INTO var_name FROM 表名; 4.2.2、用户变量使用 SELECT var_name; #用户变量赋值
set mynametest;
set myage:10,mygender:男;
select mycolor:black;
select count(*) into mycount from tb_user
#使用自定义变量
select myname,myage,mygender; 注意用户定义的变量无需对其进行声明或优化只不过获取到的值为NULL。
4.3、局部变量
局部变量是根据需要定义在局部生效的变量访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的BIGINT...END块。
4.3.1、局部变量的声明 DECLARE 变量名 变量类型 [FEFAULT ...]; 变量类型就是数据库字段类型INT、BIGINT、VARCHAR、DATE、TIME等。
4.3.2、局部变量赋值 SET 变量名值; SET 变量名值; SELECT 字段名 INTO 变量名 FROM 表名...; #局部变量的声明和赋值
create procedure p2()
begindeclare stu_count int default 0;set stu_count:100;#select count(*) into stu_count from student;select stu_count;
end;
call p2(); 5、if判断
5.1、语法结构 IF 条件1 THEN ... ELSE 条件2 THEN ... ELSE ... END IF; #需求更具定义的分数score变量判断对应等级
#1、score85等级为优秀
#2、score60并且score85等级为及格
#3、score60等级不及格
create procedure p1()
begindeclare score int default 58;declare result varchar(10);if score85 thenset result:优秀;elseif score60 and score85 thenset result:及格;elseset result:不及格;end if;select result;
end;
call p1(); 6、存储过程的参数
类型含义备注IN该类参数作为输入也就是调用时需要传入值默认OUT该类参数作为输出也就是该参数可以作为返回值INOUT既可以作为输入参数也可以作为输出参数
6.1、语法 CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数值 参数类型]) BEGIN --SQL语句 END; #存储过程中的参数
#1、需求根据传入参数的分数score判断对应等级并返回
#score85等级为优秀
#score60并且score85等级为及格
#score60等级不及格
create procedure p2(in score int,out result varchar(10))
beginif score85 thenset result:优秀;elseif score60 and score85 thenset result:及格;elseset result:不及格;end if;
end;
call p2(68,result);
select result;
#2、将传入的200分制的分数进行换算换算成百分制然后返回
create procedure p3(inout score double)
beginset score :score*0.5;
end;
set score:78;
call p3(score);
select score; 7、case
7.1、语法
语法一 CASE case_value WHEN then_value1 THEN statement_list1 [WHEN then_value2 THEN statement_list2] [ELSE statement_list] END CASE; 语法二 CASE WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN statement_list2] #需求根据传入的月份判断月份所属的季度采用case结构
#1、1-3月份第一季度
#2、4-6月份第二季度
#3、7-9月份第三季度
#4、10-12月份第四季度
create procedure p4(in month int)
begindeclare result varchar(10);casewhen month1 and month3 thenset result:第一季度;when month4 and month6 thenset result:第二季度;when month7 and month9 thenset result:第三季度;when month10 and month12 thenset result:第四季度;elseset result:非法参数;end case;select concat(您输入的月份为,month,所属季度,result);
end;
call p4(16); 8、while循环
while循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。
语法结构 #先判定条件如果条件为true则执行逻辑否则不执行 WHILE 条件 DO SQL语句... END WHILE; #需求计算从1累加到n的值n为传入的参数值
#A.定义局部变量用来记录累加之后的值
#B.每循环一次n-1知道满足n0,则不再循环
create procedure p5(in n int)
begindeclare sum int default 0;while n0 doset sum:sumn;set n:n-1;end while;select sum;
end;
call p5(10); 9、repeat循环
repeat是有条件的控制语句当满足条件的时候退出循环。
语法结构 #先执行一次逻辑然后判断逻辑是否满足如果满足则退出反之则进行下一次循环 REPEAT SQL逻辑... UNTIL 条件 END REPEAT; create procedure p6(in n int)
begindeclare sum int default 0;repeatset sum :sumn;set n :n-1;until n0end repeat;select sum;
end;
call p6(10); 10、loop循环
loop实现简单的循环如果不在SQL逻辑中增加退出循环的条件可以用其实现简单的死循环。loop可以配合以下两个语句使用
· LEAVE:退出循环。
· ITERATE:必循用在循环中作用是跳过当前语句剩下的语句直接进入下一次循环。 [begin_label:] LOOP SQL 逻辑... END LOOP [end_lable]; LEAVE lable;#跳出循环 ITERATE lable;#进入下一次循环 #需求计算从1累加到n的值n为传入的参数值
#A.定义局部变量用来记录累加之后的值
#B.每循环一次n-1知道满足n0,则不再循环
create procedure p7(in n int)
begindeclare sum int default 0;total:loopif n0 thenleave total;end if;set sum :sumn;set n :n-1;end loop total;select sum;
end;
call p7(10);
#需求计算从1到n之间偶数的累加值n为传入的参数值
create procedure p8(in n int)
begindeclare sum int default 0;total:loopif n0 thenleave total;elseif n%21 thenset n:n-1;iterate total;end if;set sum :sumn;set n :n-2;end loop total;select sum;
end;
call p8(10);
11、游标(cursor)
create procedure p9()
begindeclare stu_count int default 0;select * into stu_count from student;select stu_count;
end;
call p9();
#会报这样一条错误The used SELECT statements have a different number of columns
这个时候就需要游标了。
游标是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理游标的使用包括游标的声明、OPEN、FETCH和CLOSE。
11.1操作语法
声明游标 DECLARE 游标名称 CURSOR FOR 查询语句; 打开游标 OPEN 游标名称; 获取游标记录 FETCH 游标名称 INTO 变量[,变量]; 关闭游标 CLOSE 游标名称; 11.2、条件处理程序
条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法 DECLARE handler_action HANDLER condition_value [,condition_vakue]...statement; handler_action CONTINUE:继续执行当前程序 EXIT:终止执行当前程序 condition_value SQLSTATE sqlstate_value:状态码如02000 SQLWARNING:所有以01开头的SQLSTATE代码的简写 NOT FOUND: 所有以01开头的SQLSTATE代码的简写 SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写 #需求根据传入的参数uage用来查询用户表tb_user中所有用户年龄小于等于uage的用户姓名name和专业profession
#并将用户的姓名和专业插入到所创建的一张新表idnameprofession中
#A.声明游标存储查询结果集
#B.创建表结构
#C.开启游标
#D.获取游标中的记录
#E.插入数据到新表中
#F.关闭游标
create procedure p9(in uage int)
begindeclare u_name varchar(10);declare u_profession varchar(20);declare u_cursor cursor for select name,profession from tb_user where ageuage;declare exit handler for SQLSTATE 02000 close u_cursor;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(10) ,profession varchar(20));open u_cursor;while true dofetch u_cursor into u_name,u_profession;insert into tb_user_pro values(null,u_name,u_profession);end while;close u_cursor;
end;
三、存储函数
存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。具体语法 CREATE FUNCTION 存储过程名称[(参数列表)] RETURNS type [characteristic ...] BEGIN --SQL语句 RETURN...; END; characterstic说明 DETERMINISTIC相同的输入参数总是产生相同的结果 NO SQL不包含SQL语句 READS SQL DATA包含读取数据的语句但不包含写入数据的语句 #从1到n的累加
create function fun(n int)
returns int deterministic
begindeclare sum int default 0;while n0 doset sum:sumn;set n:n-1;end while;return sum;
end;
select fun(100);
四、触发器
触发器是与表有关的数据库对象指在insert/update/delete之前或之后触发并执行触发器中定义的SQL语句集合。触发器这种特性可以协作应用在数据库端确保数据的完整性日志记录数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容者与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发。
触发器类型NEW和OLDINSERT型触发器NEW表示将要或已经新增的数据UPDATE型触发器OLD表示修改之前的数据NEW表示将要修改或修改后的数据DELETE型触发器OLD表示将要货已经删除的数据 创建 CREATE TRIGGER trigger_name BEDORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN trigger_stmt; END; 查看 SHOW TRIGGERS; 删除 DROP TRIGGER [schema_name.] trigger_name;#如果没有指定schema_name.默认当前数据库 #需求通过触发器记录user表的数据变更日志user_logs包含增加修改、删除
create table user_log(id int(11) not null auto_increment,operation varchar(20) not null comment 操作类型insert/update/delete,operate_time datetime not null comment 操作时间,operate_id int(11) not null comment 操作的ID,operate_params varchar(500) comment 操作参数,primary key (id)
)engineinnodb default charset utf8;
#插入数据的触发器
create trigger insert_triggerafter insert on tb_user for each row
begininsert into user_log(id, operation, operate_time, operate_id, operate_params) values(null,insert,now(),new.id,concat(插入的数据内容为:id,new.id,name,new.name,phone,new.phone,profession,new.profession));
end;
#插入数据到tb_user表验证
insert into tb_user values(5,二秃子,66,1,12345678910,通信工程);
#修改数据的触发器
create trigger update_triggerafter update on tb_user for each row
begininsert into user_log(id, operation, operate_time, operate_id, operate_params) values(null,update,now(),new.id,concat(更新之前的数据:id,old.id,name,old.name,phone,old.phone,profession,old.profession,更新之后的数据:id,new.id,name,new.name,phone,new.phone,profession,new.profession));
end;
update tb_user set age 56 where age55;
#删除数据的触发器
create trigger delete_triggerafter delete on tb_user for each row
begininsert into user_log(id, operation, operate_time, operate_id, operate_params) values(null,delete,now(),old.id,concat(删除之前的数据:id,old.id,name,old.name,phone,old.phone,profession,old.profession));
end;
delete from tb_user where id5;
show triggers;
