外键

外键：foreign key，(键不在自己表中)：如果一张表中有一个字段（非主键）指向另外一张表的主键，那么将该字段称之为外键。

增加外键

外键可以在创建表时候，或者创建表之后增加。(但是要考虑数据的问题)，一张表可以有多个外键。

创建表的时候增加外键

-- 在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)

下面为之前的my_class表(表结构如下图)增加一个外键


create table my_foreignone(    id int primary key auto_increment,    name varchar(20) not null comment '学生姓名',    c_id int comment '班级id',    -- 增加外键    foreign key(c_id) references my_class(c_id))charset utf8;

在新增表之后增加外键

alter table 表名 add[constraint 外键名称] foreign key(外键字段) references 父表(主键字段)-- 创建表create table my_foreigntwo(    id int primary key auto_increment,    name varchar(20) not null comment '学生姓名',    c_id int comment '班级id')charset utf8;-- 增加外键alter table my_foreigntwo add -- 指定外键名constraint student_class_1-- 指定外键字段foreign key(c_id)references my_class(c_id);

删除外键

-- 删除外键语法：alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键，但是外键的名称不能相同-- 删除外键alter table my_foreigntwo drop foreign key student_class_1;

外键的作用

外键默认的作用有两点：一个是对父表，一个对子表(外键字段所在的表)

-- 插入数据：外键字段对应的数据在父表中不存在insert into my_foreignone values(null,'王二小',5);

对父表约束
父表数据进行写操作(删和改：涉及到主键本身)，如果对应的主键，在子表中已经被数据引用，那么就不允许操作。

-- 更新父表记录update my_class set c_id=5 where c_id=2;  -- 失败，c_id=2的记录已经被学生表引用

外键约束

外键有三种约束模式：

set null ： 置空模式，父表被操作之后，子表对应的外键字段被置空。

通常情况下，合理的模式应该是这样的：删除父表中的数据，子表置空，更新父表的时候，子表做级联操作。

-- 指定模式的语法：foreign key(外键字段) references 父表(外键字段) on delete 模式  on update 模式foreign key(外键字段) references 父表(外键字段) on delete set null on update cascade-- 创建外键，指定模式：删除置空，更新级联create table my_foreignthree(    id int primary key auto_increment,    name varchar(20) not null,    c_id int,    -- 增加外键    foreign key(c_id)    -- 引用表    references my_class(c_id)    -- 指定删除模式    on delete set null    -- 指定更新模式    on update cascade)charset utf8;

为了演示上面设定的约束模式，这里我先往my_foreignthree表中插入一些数据：

-- 插入数据insert into my_foreignthree values(null,'王五',1),(null,'王红',1),(null,'夏明',2),(null,'二楼',3),(null,'哈哈',2),(null,'好人',3);

-- 更新父表主键update my_class set c_id = 5 where c_id=1;

-- 删除父表主键delete from my_class where c_id = 2;

联合查询

联合查询：将多次查询(多条select语句)，在记录上进行拼接。

-- 基本语法多条select构成，每一条select语句获取的字段数必须严格一致(与字段类型无关)。select 语句1union [union 选项]select 语句2...union选项：      all： 保留所有     distinct： 去重：默认的-- 联合查询select c_id,c_name,c_room from my_classunion -- 去重select studentid,number,name from my_student;-- 联合查询select c_id,c_name,c_room from my_classunion all -- 不去重select studentid,number,name from my_student;

联合查询的意义

联合查询意义：

联合查询中：order by不能直接使用，需要对查询语句使用括号才行。

-- 需求：男生升序，女生降序(年龄)(select * from my_student where gender='boy' order by age asc) union(select * from my_student where gender='girl' order by age desc); 

可以发现，虽然使用了order by关键字，但是并没有达到预期效果，此时需要注意的是，若要order by生效，必须搭配limit，limit使用限定的最大数。

-- 需求：男生升序，女生降序(年龄)(select * from my_student where gender='boy' order by age asc limit 500) union(select * from my_student where gender='girl' order by age desc limit 500); 

子查询

子查询：查询是在某一个查询结果集中查询

子查询分类

子查询有两种分类方式：按位置，按结果

-- 获取java003班的所有学生select * from my_student where c_id=?select c_id from my_class where c_name='java003'-- 标量子查询select * from my_student where c_id= (select c_id from my_class where c_name='java003')

-- 查询有效班级里的学生信息-- 确定数据源select * from my_student where c_id in(?)-- 确定有效班级select c_id from my_class-- 列子查询select * from my_student where c_id in (select c_id from my_class);

-- 查询学生中年龄最大且身高最高的学生-- 行子查询select * from my_student where (age,height) = (select max(age),max(height) from my_student);

-- 找出每个班中最高的学生-- 表子查询select * from (select * from my_student order by height desc) as student group by c_id;

exists子查询：用来判断某些条件是否满足(跨表)，exists是接在where之后，exists返回的结果：0或者1

-- 查询有所属班级的所有学生信息select * from my_student where exists(select * from my_class)

视图

视图：view，是一种有结构(有行有列)，但是没有结果(结果中不真实存放数据)的虚拟表，虚拟表的结构不是自己定义，而是从对应的基表中产生。

创建视图

-- 基本语法create view 视图名称 as select语句

 -- 创建单表视图 : 基表只有一个create view viewone as select * from my_student;create view viewtwo as select * from my_class;--  创建单表视图 : 基表有多个create view viewthree as select s.*,c.c_name,c.c_room from my_student as s left join my_class c on s.c_id = c.c_id;

查看视图

视图是一张虚拟表，所以表的所有查看方式都适用于视图

desc viewone;show create table viewone;show create view viewone;

视图的使用

使用视图主要是为了查询：将视图当做表一样查询即可。

select * from viewone;select * from viewtwo;

视图的执行本质都是执行封装的select语句

修改视图

修改视图：修改视图本身的来源语句，即select语句

alter view 视图名称 as 新的select语句alter view viewone as select name,age,height from my_student;

删除视图

drop view 视图名称

视图的意义

视图数据操作

新增数据：直接对视图上的数据进行新增。

-- 单表视图插入数据insert into viewone values('小王',23,176);

删除视图数据：

-- 删除单表视图数据delete from viewtwo where c_id = 5;

事务

事物：一系列要发生的连续的操作。
事物安全：是一种保证连贯操作同时实现的机制。

这里以转账为例，演示事物的操作和特性

-- 创建一个账户表create table countone(    id int primary key auto_increment,    number varchar(20) not null unique comment '账户',    name varchar(20) not null,    money decimal(10,2) default 0.0 comment '账户余额')charset utf8;-- 插入数据insert into countone values(null,'0000000000001','张三',1000.0),(null,'0000000000002','李四',2000.0),(null,'0000000000003','王五',1500.0);

事务操作

事物操作分为两种，自动事物(默认的)，手动事务。

手动事务：

-- 开启事务start transaction;


2. 进行事务操作，一系列操作

-- 张三账户减少update countone set money = money - 500 where id = 1;


此时，在新打开一个终端，来查看当前张三的余额,可以看到由于当前的事务还没有执行完成，所以此时查询到的张三的余额还是没有变化，上面的查询之所以会有变化，并不是真正的数据查询，而是从事务日志中进行查询所得。


-- 李四账户增加update countone set money = money + 500 where id = 2;

-- 关闭事务：选择性的将日志文件中操作的结果保存到数据表，或者直接清空事务日志，即某一个步骤操作失败，此时真正的表数据是不会发生改变的。-- (a):提交事务：同步数据表(操作成功)：commit   (b):回滚事务：直接清空日志(操作失败)：rollback-- 提交事务commit;


可以看到，当该条事务commit之后，在通过从表中查询数据，此时数据发生改变。

事务原理

事务操作原理：事务开启之后，所有的操作，都会保存到事物日志中，事务日志只有在得到commit之后，才会同步到数据表。其他任何情况都会清空。

回滚点

回滚点：在某个成功的操作完成之后，后续的操作有可能成功，也有可能失败，不管成功还是失败，前面的操作都已经成功，可以在当前成功的位置设置一个点，当后续操作失败的时候，返回到该位置，而不是返回所有操作，该点，称之为回滚点。

-- 设置回滚点：savepoint 回滚点名称;-- 回到回滚点：rollback 回滚点名称;-- 开启事务start transaction;-- 事务处理1:张三增加1000元update countone set money = money + 1000 where id = 1;-- 设置回滚点savepoint save1;-- 扣税update countone set money = money - 200 where id = 2;-- 扣税出错rollback to save1;-- 继续操作update countone set money = money - 200 where id = 1;-- 查看结果select * from countone where id = 1;-- 提交事务commit;

事务特性

事务有四大特性：ACID

D：持久性，数据一旦提交，不可改变，永久的改变数据表中的数据。

锁机制：默认使用的是行级锁，但是如果在事务的操作过程中，没有使用到索引，那么系统会自动使用全表检索数据，自动升级为表锁。


mysql变量

系统变量

系统变量：系统定义好的变量，大部分时候，用户根本不需要使用系统变量，是用来控制服务器的表现的。如autocommit
,auto_increment等。

-- 查看系统变量：show variables; -- 查看所有系统变量-- 查看具体变量值： 任何一个有数据返回的内容，都是由select查看select @@ 变量名;

自定义变量

定义变量：系统为了区分系统变量，规定用户自定义变量必须使用一个@符号

set @变量名 = 值set @name = '张三';-- 查看自定义变量：select @变量名;-- 查看变量select @name;

注意：在mysql中，‘=’会默认的被当做比较符号处理，mysql为了区分比较和赋值的概念：重新定义了一个新的赋值符号： ‘:=’

触发器

触发器：trigger，事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候，系统会自动触发代码然后执行。

一个触发器主要有下面三个部分组成
触发器：事件类型，触发时间，触发对象。
事件类型：增删改，三种类型。insert，delete，update
触发时间：前后：before，after
触发对象：表中的每一条记录（行）
一张表中只能有一种触发时间的一种类型的触发器，最多一张表能有6个触发器

创建触发器

在mysql高级结构中没有小括号，都是用对应的字母符号代替。

触发器基本语法：

-- 临时修改语句结束符delimiter 自定义符号： 后续代码中只有碰到自定义符号才算结束create trigger 触发器名称 触发时间 事件类型 on 表名 for each rowbegin  -- 表示左大括号：开始    -- 里面就是触发器的内容：每行内容都必须使用语句结束符：分号 end  -- 代表右大括号： 结束自定义符号-- 将临时修改修正过来delimiter ;

触发器记录

触发器记录：不管触发器是否触发，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后的新的状态给分别保留下来供触发器使用。其中，要操作的当前状态保存到old中，操作之后的可能的形态保存给new。

使用触发器

下面为了更深刻的理解触发器的作用和用法，以一个实例来演示，现在有下面这样一个需求：
需求：有两张表，一张订单表，一张商品表，每生成一个订单，意味着商品的库存要减少。

-- 创建表create table 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 goods values(null,'荣耀3c',999,100),(null,'魅族3c',1299,50),(null,'iphone6s',5999,200),(null,'荣耀6',1999,250),(null,'iphonese',2999,300);create table orders(    id int primary key auto_increment,    o_id int not null comment '商品id',    o_number int comment '商品数量') charset utf8;

创建触发器

-- 临时修改语句结束符delimiter $$create trigger after_order after insert on orders for each rowbegin     -- 触发器内容开始    update goods set inv = inv - new.o_number where id = new.id;end-- 结束触发器$$-- 修改临时语句结束符delimiter  ;

下面，根据生成一个指定商品的订单号，看下goods表中的对应商品的数量是否会减少，即触发器是否真正工作

 -- 生成订单 insert into orders values(null,1,3);

查看触发器

-- 查看所有触发器或者模糊匹配show triggers [like pattern]-- 查看所有触发器show triggers\G

-- 所有的触发器都会保存在一张表中，information_schema.triggersselect * from information_schema.triggers\G

删除触发器

drop trigger 触发器名称-- 删除触发器drop trigger after_order;

mysql代码分支

if分支

在mysql中只有if分支

-- 基本语法：if  条件判断  then    -- 满足条件要执行的代码else     -- 不满足条件要执行的代码end if;

下面，我们将当前的if分支以及上面的触发器结合使用，当用户下单的时候：判断年商品库存是否足够，不够不能生成订单。

-- 临时修改语句结束符delimiter %%create trigger before_order before insert on orders for each rowbegin     -- 判断商品库存是否足够    -- 获取商品库存    select inv from goods where id = new.o_id into @inv;    -- 比较库存    if @inv < new.o_number then        -- 库存不够:触发器没有提供一个能够阻止事件发生的能力(暴力报错)        insert into xxx values(xxx);    end if;end-- 结束触发器%%-- 修改临时语句结束符delimiter  ;


可以看到，上面的’before_order’触发器，是在生成订单之前即insert，就会做判断，如果当前物品剩余个数不满足条件，则暴力出错，后面的after_order触发器，同样也是不会执行的。

下面做测试

-- 生成订单，正常情况下，订单商品数量 < 商品剩余总数insert into orders values(null,2,5);-- 订单商品数量 > 商品剩余总数insert into orders values(null,2,5000);

while循环

while 条件判断  do    -- 满足条件要执行的循环代码    -- 变更循环条件end while;循环控制：在循环内部进行循环判断和控制iterate：迭代，类似于continueleave： 离开，类似于break使用方式：iterate/leave 循环名称-- 定义循环名称循环名称: while 条件 do    -- 循环体    -- 循环控制    leave/iterate 循环名称;end while;

函数

系统函数：

任何函数都有返回值，因此函数的调用是通过selet调用

-- MD5()select MD5('TEST');-- PASSWORD()select PASSWORD('TEST');

-- count()-- max()-- min()-- sum()-- AVG()select AVG(3,6,3);

-- connection_id()-- database()-- last_insert_id()-- user()-- version()

-- date() 当前的日期和时间-- curdate()-- curtime()

自定义函数：

-- 创建函数：create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型begin    -- 函数体    -- 返回值： return 类型(指定数据类型);end-- 创建函数create function display() returns intreturn 100; -- 调用函数select display();

查看函数

-- 查看所有函数：show function status[like pattern];-- 查看所有函数show function status;-- 查看函数的创建语句：show create function 函数名;-- 查看函数show create function display;

删除函数

-- 删除函数drop function display;

存储过程

存储过程：procedure，存储过程是一种没有返回值的函数

创建存储过程

-- 创建存储过程：create procedure 工程名称([参数列表])begin    -- 过程体end-- 创建存储过程create procedure pro1() select * from my_student;

查看存储过程

show procedure status[like pattern];-- 查看具体的过程：show procedure status like 'pro%';-- 查看创建语句：show create procedure pro1;

存储过程调用

存储过程有一个专门的调用关键字：call-- 调用过程call pro1();

删除存储过程

drop procedure 过程名称;-- 删除过程drop procedure pro1;

存储过程参数

函数的参数需要数据类型指定，过程还有自己的类型限定：

-- 基本使用：create procedure 过程名(in 形参名称 数据类型， out 形参名称 数据类型, inout 形参名称 数据类型)-- 过程参数delimiter $$create procedure pro1(in int_1 int, out int_2 int ,inout int_3 int)begin    -- 查看三个变量    select int_1,int_2,int_3;end$$delimiter ;


调用： out和inout类型的参数必须传入变量，而不能是数值

-- 设置变量set @int_1 = 1;set @int_2 = 2;set @int_3 = 3;select @int_1,@int_2,@int_3;call pro1(@int_1,@int_2,@int_3);select @int_1,@int_2,@int_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 = 33;set int_2 = 22;set int_3 = 44;-- 查看局部变量select int_1,int_2,int_3;-- 查看全局变量select @int_1,@int_2,@int_3;-- 修改全局变量set @int_1 = 'a';set @int_2 = 'b';set @int_3 = 'c';-- 查看全局变量select @int_1,@int_2,@int_3;end$$delimiter ;

-- 设置变量：set @int_1 = 1;set @int_2 = 2;set @int_3 = 3;call pro2(@int_1,@int_2,@int_3);