数据库基础
视图（零库存经销商）
视图（view），是一种有结构（有行有列）但是没有结果（结构中不真实存放数据）的虚拟表，虚拟表的结构不是自定义的，而是从对应基表中产生（视图的数据来源）
1、创建视图
基本语法：
Create view 试图名字 as select 语句；
（select 语句可以是普通查询；可以使链接查询；也可以是联合查询，可以是子查询）
（1）创建单表视图：基表只有一个
create view my_v1 as select * from universitytable
（2）创建多表视图：基表至少有两个（查找结果中字段重复要用别名命名，如下表的UnID）
create view my_v2 as select a.*,b.CeName from universitytable as a left join collegetable b on b.UnID=a.UnID;
2、查看视图
（1）查看视图结构（表的所有方式都使用于视图）：
show tables：查看所有表
desc 视图名字：查看表的结构
show create table 视图名字：查看表创建语句
show create view视图名字：查看视图创建语句
3、使用视图
（1）查看：
select * from my_v2：实际就是执行视图封装的select语句
（2）修改视图：只能修改视图封装的select语句
alter view my_v1 as select UnName,UnID from universitytable;
（3）删除视图：
drop view my_v3;
4、视图意义
（1）视图可以接收SQL语句，将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作
（2）数据安全：视图操作是主要针对查询的，如果对视图结构进行（删除），不会影响表数据（相对安全）
（3）视图是大项目中使用的，而且是多系统使用：可以对外提供有用的数据，但是隐藏关键（无用）的数据，数据安全
（4）视图可以对外提供友好型，不同视图提供不同的数据，对外好像专门设计
（5）视图可以更好的进行权限控制
5、视图操作
（1）视图可以对数据写操作，但有很多限制，将数据直接在视图上进行操作
新增数据（多表视图不能添加数据），单表视图中必须包含基表中所有不为空且没有默认值的字段- - - - 数据会插入到基表中
（2）删除数据（多表视图不能删除）
（3）更新数据（单表视图和多表视图都可以改）
更新限制： with check option,如果对视图新增时对字段做了限制，那么多视图进行更新操作时，系统会进行验证（要保证视图更新之后，数据依然可以被实体查询出来）
create view my_v4 as select from universitytable where age>30 with check option;
不允许对此视图的age（原来大于30）更改为小于30的数据，如果原来小于30的数据被改为大于30也是无效的
6、算法
变量（修改变量都要使用set）
变量分为两种：系统变量和自定义变量
系统变量：
系统定义好的变量，大部分用户根本不需要使用系统变量，系统变量用来控制服务器的表现，如：autocommit,auto_increment_increment等
查看所有系统变量：show variables
查看系统变量值：select @@ autocommit;
修改系统变量：
a、回话级别（只对当前回话有效）
set autocommit=0;/ set @@ autocommit=0;
b、全局级别，一次修改，永久生效
set global autocommit=0;
自定义变量：（都是回话级别的，用户级别，不区分数据库）
区别于系统变量，用户自定义变量用一个@变量名=值;
set @name=”ltt”;
查看自定义变量：
select @name;
赋值：
set @name:=”hhh”;
Mysql允许从数据表中获取数据然后赋值给变量，两种方式：
a、select @变量名：=字段名 from;- - - 从字段中赋值给变量
b、多个变量：select 字段列表 from 表名 into 变量列表
触发器
1、了解触发器
触发器：trigger，事先为某张表绑定好一段代码，当表中某些内容发生改变的时候（增删改）系统会自动触发代码，执行
触发器：事件类型，触发时间，触发对象
事件类型：增、删、改
触发时间：before和after
触发对象：表中的每一条记录（行）
一张表中只能拥有一种触发时间的一种类型的触发器：最多一张表有6个触发器
2、创建触发器
在MySQL中高级结构：没有大括号，都是对应的字符符号的代替
基本语法：
临时修改语句结束符：
delimiter 自定义符号- - -后续代码中只有碰到自定义符号才算结束
create trigger 触发器名字 出发时间 触发事件 on 表名 for each row
begin - - - 代表大括号开始
里面就是触发器的内容，每行内容都必须使用语句结束符；分好
end- - - 代表大括号结束
语句结束符（自定义符号）
将临时修改修正过来
delimiter
a、创建数据库
create table my_goods(id int primary key auto_increment,name varchar(20) not null,price decimal(10,2) default 1,inv int comment '库存数量')charset utf8;
insert into my_goods values(null,'s5',5288,100),(null,'s6',6238,100);
create table my_order(id int primary key auto_increment,g_id int not null comment '商品id',g_number int comment '商品数量') charset utf8;
b、创建触发器：订单生成，库存减少
delimiter $$
create trigger after_order after insert on my_order for each row
begin
update my_goods set inv=inv-1 where id=2;
end
$$
delimiter ;
3、查看触发器（所有的触发器都会保存在Information_schema.triggers）
查看所有 ：show triggers
查看触发器创建语句：show create trigger after_order（触发器名）;
4、使用触发器
不需要手动调用，而是当某种情况发生时会自动触发（订单里面插入记录之后）
上述例子触发器不合理，商品减少数与订单生成数不对应
5、触发器的删除&修改（触发器不能修改只能先删除后新增）
删除触发器：drop trigger after_order;
6、触发器记录
不管触发器是否触发了，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后新的状态给分别保留下来，供触发器使用。其中要操作的当前状态保存在old中，操作之后的可能形态保存给new。
删除没有new，插入没有old，使用方式：old.字段名或new.字段名
修改后的订单触发器：
delimiter $$
create trigger after_order after insert on my_order for each row
begin
update my_goods set inv=inv-new.g_number where id=new.g_id;
end
$$
delimiter ;
如果库存不足，应在订单生成之前检查
代码执行结构
代码执行结构有三种：顺序结构、分支结构和循环结构
1、分支结构：
实现准备多个代码块，按照条件选择性执行某段代码
在MySQL中只有if分支
基本语法：
if 条件判断 then
- - - 满足条件执行代码
else
end if;
触发器结合if分支：判断商品库存是否足够，不够不能生成订单
delimiter %%
create trigger before_order before insert on my_order for each row
begin
select inv from my_goods where id = new.g_id into @inv;
比较库存
if @inv < new.g_number then
insert into XXX values (xxx); 库存不够(暴力报错)
end if ;
end
%%
delimiter ;
2、循环结构：（不适合在触发器使用）
某段代码在指定条件下重复执行
while 循环（没有for循环）
基本语法：
while 条件判断 do
满足条件要执行的代码
变更循环条件
end while;
循环控制：在循环内部进行循环判断和控制，MySQL没有continue和break，但相应有iterate和leave
使用方式：iterate/leave 循环名字
定义循环名字：
循环名字：while 条件判断 do
循环体
循环控制
iterate/leave 循环名字
end while;
函数
将一段代码封装到一个结构中，在需要执行代码块的时候，调用结构执行即可（代码复用），任何函数都有返回值，因此函数的调用都是通过select调用。MySQL中，字符串的基本操作单位（最常见是字符）
1、系统函数
直接调用
结果：MySQL中字符串的下标是从一开始，一个单位截取出中文说明单位是字符
instr:判断字符串是否存在某个字符串中，存在返回位置，不存在则返回0
lpad:左填充，将字符串按照某个指定方式填充到指定长度（字符）
insert:替换，找到目标位置，指定长度的字符串，替换成目标字符串
strcmp:compare,字符串比较（不区分大小写）
2、自定义函数（调用方式与系统函数一样）
a、函数要素：函数名，参数列表（形参和实参），返回值，函数体（作用域）
b、创建函数
创建语法
create function 函数名（形参列表）returns 数据类型- - - 规定要返回的数据类型
begin
函数体
返回值（指定的数据类型）
end
创建：
create function display1() returns int
return 100;
c、调用：
select display1();
d、查看所有函数：show function status;
查看函数的创建语句：show create function display1;
e、删除函数：（函数不能修改）- - - drop function display1;
f、函数参数（形参、实参）
有@的变量是全局变量，没有的是局部变量,在函数内部定义的全局变量，函数外也可以访问
例子：计算1到指定数之间的和
delimiter %%
create function display2(int_1 int) returns int
begin
set @i=1;
set @res=0;
while @i<=int_1 do
set @res = @res + @i;//MySQL中饭没有++和+=
set @i = @i + 1;
end while;
return @res;
end
%%
delimiter ;
e、函数作用域
MySQL中的作用域与js中的作用域一样
全局变量：使用set关键字定义，使用@符合标志
局部变量：使用declare关键字声明，没有@符号，所有局部变量的声明都必须在函数体开始之前
例子：计算1到指定数之间的和，5的倍数不加
delimiter %%
create function display1(int_1 int) returns int
begin
--声明变量：循环变量，结果变量
declare i int default 1;
declare res int default 0;--定义局部变量可以有属性default
mywhile:while i<=int_1 do
if i % 5 = 0 then
set i = i + 1;
iterate mywhile;
end if;
set res = res + i;--MySQL中饭没有++和+=
set i = i + 1;
end while;
return res;
end
%%
delimiter ;
存储过程
存储过程（procedure），是一种用力啊处理数据的方式，存储过程是一种没有返回值的函数
1、创建过程
create procedure 过程名字[参数列表]
begin
过程体
end
创建：
create procedure prol()
select * from my_goods;
2、函数的查看方式适用于过程，关键字变为procedure
show procedure status like 'pro%';
3、调用过程（关键字call）：call prol;
4、删除过程（过程不能修改）：drop procedure prol;
5、过程参数
过程还有自己的类型限定：三种类型
in:数据只是从外部传入给内部使用（值传递），可以是数值也可以是变量
out:只允许过程内部使用（不用外部数据），给外部使用的（引用传递，外部的数据会先清空才会进入到内部），只能是变量
inout:外部的可以在内部使用，内部修改也可以给外部使用，典型的引用传递，只能是变量
6、基本使用
create procedure 过程名（in 形参名字 数据类型，out 形参名字 数据类型，inout 形参名字 数据类型）
例子：
delimiter $$
create procedure prol(in int_1 int, out int_2 int ,inout int_3 int)
begin
select int_1, int_2, int_3;
end
$$
delimeiter ;
存储过程对于变量的操作（返回）是滞后的，是自爱存储过程调用结束的时候，才会重新将内容修改的值赋值给外部传入的全局变量
set @int_1=1;set @int_2=2;set @int_3=3;
delimiter $$
create procedure pro2(in int_1 int, out int_2 int ,inout int_3 int)
begin
select int_1, int_2, int_3;
set int_1=10;
set int_2=100;
set int_3=1000;
select int_1, int_2, int_3;
select @int_1, @int_2, @int_3;
set @int_1 = 5;
set @int_2 = 6;
set @int_3 = 7;
select @int_1, @int_2, @int_3;
end
$$
delimeiter ;
调用：call pro2(@int_1,@int_2,@int_3);
存储过程调用结束之后，系统会把局部变量返回给全局变量（只有out和inout类型）
