外键
什么是外键
在两张表中公用字段,在一个关系中是主键,在另外一个关系中就是外键
公共字段是主键的表,是主表;是外键的表就是从表
增加外键
建表的时候加外键(明确指定从表中的公用字段是外键—有一定的约束)
create table class
(
id int primary key auto_increment,
class_name char(20)
)charset=utf8;
create table student
(
id int primary key auto_increment,
name char(30),
class_id int,
foreign key(class_id) references class(id)
)charset=utf8;
指定外键的时候,系统会自动的把外键字段加一个索引
创建外键并指定名字
修改表加外键
alter table 表名add foreign key(外键字段) references 主表(主键);
删除外键
Alter table 表名 drop foreign key 外键名字;
外键的基本要求
- 首先数据表字段是外键,表必须是innodb的数据库引擎
- 主表和从表的公用字段,数据类型和必要的属性必须一致
- 外键创建的时候需要注意数据的影响—(修改表后期加外键的时候)公用字段中从表的数据必须能在主表相应字段中找到
外键约束
什么是外键约束
就是对主表数据,和从表数据的一些要求
从表中有的相关数据,主表中不能随意删除
想操作主表中的内容怎么办/
把相关信息从从表中全部删除,再修改相应主表中的内容
从表中用的相关数据,必须是主表中有的
外键的意义
保证了数据的一致性(外键约束的体现)
和完整性(班级和人进行了关联)
工作中一般很少使用外键,因为操作表数据的时候太麻烦,限制的条件太死
视图
什么是视图
视图其实就是一张虚构的表,这个表只有表结构,没有表数据,视图的数据是从真正的数据表中引用的,那么我们修改视图的数据相关数据表的数据也会被修改;说白了视图就是把select查询结果当做了一个表
视图的操作
创建视图
Create view view1 as select 语句
查看视图
使用视图
和使用表的用法一样,注意这里视图主要是用于查询和修改的,一版不用于添加也不删除
查询视图
视图中的数据,都是从真实的表中来的
create view view1 as
select class_name,name from class join student on class.id=student.class_id;
删除视图
视图数据的操作
增加视图数据
一般不是对视图的数据进行添加
这个视图的数据是来源于多表的
肯定不能插入成功;
这个视图数据是来源于一张表的
当视图的数据是这个表中所有必须要写的数据的时候能插入
否则不能插入
测试1
测试2
删除视图数据
第一种:视图的数据来源于多个表
第二种:视图的数据来源于一个表
修改视图数据
视图 数据的修改和表修改一样的用法都是使用update,视图数据的修改成功后,相应数据表中的数据也会发生改变
查询视图
和普通的表查询一样
视图的作用和好处
视图的数据都是存在真实的表中的,但是视图的结构可以自己定义(select查询结果的结构)
当一些敏感数据不想被用户看到,但是该表中还有些数据用户还必须使用
我们就可以用视图来解决这个问题
比如下边的一些敏感数据 年龄,性别我们都可以用视图来把这些信息不显示(只需要用select语句不选这两个字段),用户只需要有权限查询我们创建的视图就可以了
Id |
Name |
Age |
sex |
1 |
林志玲 |
67 |
男 |
2 |
赵薇 |
58 |
男 |
我们可以把一些复杂的sql语句的结果做成视图
四表联查
班级名,学生名,课程名, 成绩
我们可以把四表联查的sql结果做成视图,每次我们需要查看这种数据的时候直接
Select * from 视图;就可以了,而不用每次都写一个四表连接的sql
如果一个表数据量太大有200万条,查询的速度慢
我们可以把200条分别做成视图
1-10万条是一个视图 (id从1—10万)
10-20万条是一个视图 (id从10万到20万)
……………
我们查询前先判断id是哪个范围,再根据范围查找相应视图
查询的效率就大大的提高了 这种做法就是把表横向切割—-分表
事务
什么是事务
你向银行存钱 ,钱已经存入银行了,这是该把你的存钱记录存入电脑了,但是停电了
你的钱少了,但是钱没在银行提现
你去银行取钱,钱取出来了,该从你的账户扣钱,这是又停电了,
你拿到钱了,银行账户余额没少
在多条sql的时候,如果有一条不成功,那么所有的sql执行失效—事务
事务的执行流程
开启事务 begin /start transcation
执行sql
提交事务(所有sql执行成功) commit
回滚 (有一条sql执行失败就回滚) rollback
注意;再开启事务后,没有提交事务,或是回滚都是没有效果的
测试2
事务执行没有完成的时候,数据表的数据没有任何变化
提交事务后,数据表中的数据会永久性的改变
事务基本原理图
事务的特点(acid)
- 原子性(Atomicity)
事务里边的所有sql语句是一个整体,不可分割,要么全部执行成功,要么全部执行失败
- 一致性(consistency)
事务执行过后,没有用户访问到的数据都是一样的
- 隔离性(Isolation)
事务与事务之间没有影响各自执行自己的命令
当前事务一开启执行后,开启事务2,操的数据如果一样比如是同一行数据,那么事务2会等待事务1结束以后再执行,开启事务1以后,操作的这行数据就会被‘锁’起来,其他的命令不能操作
- 永久性(Durability)
事务结束以后,对表的影响是永久有效的
Mysql编程
系统函数(了解)
- 字符串函数,比如:char_length(), concat(), instr(), lcase(), left(), ltrim(), mid()
- 时间函数,比如:now(), cordate(), curtime(), datediff(), date_add()
- 数学函数,比如:abs(), ceiling(), floor(), pow(), rand(), round()
- 其他函数,比如:md5(), version(), datebase(), UUID()
系统函数就是mysql系统自己定义的函数,我们只需要调用就可以了
存储函数
就是自己定义函数,必须返回一个单独的值,存储函数里边不能有结果集显示不能有select show 等命令
创建存储函数
创建语法:
create function 函数名(参数1 类型1,参数2 类型2…..) returns 返回值类型
begin
…..
end;
其中begin和end 就是开始和结束的标识 可以理解成PHP的 { }
解决:修改sql代码执行结束符
命令:delimiter 新的结束符
声明存储函数
查看存储函数状态;
Show function status;
存储函数是属于某一个数据库的只能在当前这个数据库中使用
调用存储函数
一般存储函数的调用和select 连用
其实和系统函数的使用没有差别,都是函数的调用
扩展,如果传入的参数类型不是int会怎么样?
删除存储函数
drop function 函数名;
总结:存储函数就是一个普通的有返回值的函数,这里返回值必须是一个单独的数据
存储函数里边不能使用select show等命令因为,select show 返回的是一个结果集
变量
系统变量
查看系统变量
Show variables;
Autocommit 自动事务的提交,默认自动事务的提交会开启的
自动事务的开启 set atuocommit=1;
关闭 set autocommit=0;
局部变量
用declare声明的变量
声明格式是如下
Declare 变量名 类型 default 默认值;
局部变量一般使用在函数的begin end中间,应该先声明再使用;
会话变量
就是在一次数据库连接中都生效的变量
声明方式是:set @变量名=值;
流程控制
If分支
if 条件 then
//代码
elseif 条件 then
//代码
else
//代码
end if;
做一个判断学生成绩的函数,如果分数超过80为优秀,超过60为及格 ,低于60不及格
While循环
while 条件 do
//代码
end while;
案例,求一个数的阶乘
Mysql注释
第一种
— 注释 注–后有一个空格
第二种
# 注释 注#后有一个空格
第三种
/*
多行注释
*/
存储过程
什么是存储过程:说白了,存储过程也是一个mysql自定义函数,要求没有return返回值
也就没有返回值的MySQL自定义函数,一般用于处理大型的业务逻辑,存储过程的代码只解析一次,
存储过程也是只属于某一个数据库的
创建存储过程
语法:
Create procedure 名字 ([形参修饰符] 参数1 参数类型,[形参修饰符] 参数2,参数类型….)
Begin
End;
调用存储过程
存储过程的调用和存储函数的调用不同,存储函数不是单独调用的,可以和其他存储函数一块调用
存储过程调用
存储过程是单独调用的用到了call关键字
用法:call 存储过程名字(参数列表);
查看存储过程
Show procedure status;
删除存储过程
Drop procedure 存储过程的名字
存储过程的形参修饰符in,out,inout
形参修饰符放在参数前边,类型放在参数后边
In
参数的值可以传入,但是存储过程不能修改这个值
Out
不能传入值,能在内部修改变量的值,外部也生效
Inout
能传入值,并且可以修改标量的值,并且外部也生效
思考:存储函数和存储过程的区别
触发器
触发器概念
触发器的基本概念
类似js中的点击事件,当我们点击了一个连接会自动的跳出一个窗口
触发器是在操作数据的时候,比如删除(增加,更新)了某个表的数据的时候,自动调用的自定义函数
触发器的作用好处
首先可以安全验证,—增加数据的时候,进行数据的安全验证
不用手动调用,—如果一件商品下架了,那么订单中就不要出现这个商品了,触发器可以自动的删除订单中的商品
创建触发器
创建触发器基本语法
create trigger 名称 after|before 事件 on 表名 for each row
begin
#SQL语句
end //
定义一个触发器,当删除商品的时候,自动的把订单中用相关商品的记录删除(自动)
触发事件 触发 触发器自动调用
删除商品信息后,再次查看订单表信息
触发时机关键字:before, after
触发事件关键字:insert, delete, update
触发器一共有
6中before insert,before delete,before update,after insert ,after delete,after update;
注意触发器不能有冲突
触发器的应用
触发器中的关键字:new
New新添加的记录信息
Before Delete
After delete
两个都没有new关键字
触发器中的关键字:old
Old代表老的信息记录
Before insert 这个触发器有old吗? 没有