第六周总结
day1 - Mysql数据库入门
数据
数据即用于描述一些客观事物的符号；比如说人具备：年龄，身高，体重，姓名，性别等信息，这些信息都称之为数据（在java中称之为属性）；数据的分类:
- 文本
- 图形（图片）
- 图像（视频）
- 声音
- 文件
主流数据库产品
- Sybase SqlServer
- MSSqlServer（SqlServer）
- Oracle(先知/甲骨文)
- DB2/Informix (IBM)
- Mysql(瑞典Mysql AB)
- MariaDB
- PostgreSQL
- Access(微软)
- Sqlite3（应用于移动设备:手机，平板，pad）
- 达梦数据库(国产:政府，消防，军工以事业单位为主的客户)
- OceanBase（阿里巴巴+蚂蚁金服）
SQL语句入门
SQL是Structured Query Language(结构化查询语言)的缩写;sql语句是专门为数据库管理提供的通用操作语言；语法类似于英语口语，在使用它时，只需要发出“做什么”的命令“怎么做”是不用使用者考虑的。sql语句分为以下几个大类：
-
DDL语句（数据定义语句）
数据定义语句主要应用于对数据表的结构操作：比如建表，删除表，修改表的结构等；DDL语句包含以下命令：
- create （新建）
- drop（删除，主要删除数据库中的结构，比如表，库等）
- alter（主要用已有表删除，新建，修改行）
- add(添加列)
- modify（修改列）
-
DML语句（数据操作语句）
数据操作语句一般用于对的语句数据库表中数据进行更新操作，比如添加，删除，修改数据等，DML语句包含以下命令:
- insert （对表新增加数据）
- update（更新表字段）
- delete（删除信息）
-
DQL语句（数据查询语句）
数据查询语句一般用于对数据库表中数据进行查询操作，命令主要包含：select
-
DCL语句（数据控制语句）
数据控制语句一般用于对于数据库用户的权限管理，事务管理，DCL包含以下命令:
- grant （权限设置）
- revoke（回收权限）
- commit（事务提交）
- rollback（事务回滚）
关于mysql中字符长度问题：**
如果是utf8编码下，默认的中文字符占3个字节；如果是gbk编码，默认的中文占2个字节
关于mysql中字符长度问题：
如果是utf8编码下，默认的中文字符占3个字节；如果是gbk编码，默认的中文占2个字节
关于char类型和varchar类型：
- char类型是定长字符串，类似于java中String；长度一旦定义则无法改变，并且无论数据是否够指定长度，都会占满，不够的用空格填充；char类型一般应用于长度确定的字符串类型，比如：性别，手机号，身份证号等；
- varchar类型是可变长度字符串，类似java中StringBuffer；长度定义之后会根据实际填充的内容，选择占用多大空间，比较节省空间；varchar类型一般使用在长度不确定的字符串类型，比如:姓名，邮箱地址，评论信息等。
修改表结构
语法：
alter table 表名 [add/drop/modify/change/rename] 列名称
DML语句
- 显示表中所有的数据
select * from tbemp;
insert语句（插入）
语法：
insert into 表名(列名1，列名2...) values(列值1,列值2...)
插入数据出现中文乱码时解决方案：**
由于安装mysql使用的是UTF8编码，但是cmd命令行中默认为GBK编码，因此在命令行中
使用中文数据是会出现乱码；解决方式只需要在打开cmd命令行时输入以下命令：
- set names gbk；
然后再进行插入操作即可解决（但是以上修改只对一次会话生效；如果只会再次使用cmd需要重新设置）
select语句
select * from student;-- 查询所有
select id,name from student; -- 查询指定的字段
select * from student where id='1';-- 条件查询
select * from student where id='1' and sex ='男';-- 多条件查询
select * from student where id='1' or sex ='男';
update语句（更新）
语法：
update 表名 set 列名1=值1,列名2=值2... where 条件
注意事项：
数据库中是否为空（null）的判断不能使用“=”或“!=”应该使用 is null或者 is not null
delete语句（删除）
语法：
delete from 表名 where 条件
注意事项:
实际开发中，数据积累不容易，一般不会轻易使用物理删除；大多数时候会选择使用逻辑删除；所谓逻辑删除实际就是在表中增加一个标识列，通过该列的取值标记该列是否应该被查询到
因此针对删除需求，可以在表中新增一列用于标记该列是否被删除
alter table tbemp add isdel tinyint;
物理删除：
使用delect 语句直接讲数据库中的数据删除掉
逻辑删除：
实际上执行的是update语句，更新了该条数据的状态字段，一般状态字段的值：0表示删除，1表示有效
如果要做逻辑删除，就是将stats字段的值更新为0
MySql数据类型
mysql数据库中支持的数据类型分为以下几个大类：
- 数值类型
- 整型
- 浮点型
- 字符类型
- 日期时间类型
数值类型
mysql中数值类型主要包含以下两大类
- 整型
- 浮点型
常用数值类型主要包含以下几个:
- int
- double
- decimal
字符串类型
注意事项：**
在使用中文字符串时，如果数据库编码是gbk,则每个中文字符占2个字节；如果是utf8编码，则每个中文字符占3个字节
关于取值返回:
char(m)
varchar(m)
其中m表示的字符个数
常见的字符串类型：
- char
- varchar
- text
blog和clob
blob（Binary Large object）二进制大对象（视频，图片，音频）
clob（Character Large Object）字符大对象（大文本）
enum类型
枚举类型，用于限定该列只能选择枚举中其中一个值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gjBCFA9z-1607243872370)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201130/%E7%AC%94%E8%AE%B025/assets/1595925731146.png)]
日期时间类型
获取当前的系统时间
select now()
常用日期类型：
date 用于表示日期 如：2020-01-01
datetime 用于表示日期时间 如：2020-01-01 10:11:12
timestamp 用于表示时间戳，格式等同datetime，支持默认值 CURRENT_TIMESTMP
另外该类型也支持自动更新（on update CURRENT_TIMESTAMP）当前行 数据更新时，该列也会自动更新为最新的时间
注意事项：
- 一张表中只能有一个字段的timestamp可以设置默认值
day2 - Mysql之SQL查询
运算符
关系运算
| 运算符 | 操作方式 | 说明 |
|---|---|---|
| = | a=b | 如果两操作数相等则为true |
| !=,<> | a != b,a<>b | 如果两操作数不相等则为true |
| > | a > b | 如果a大于b,为true |
| >= | a >= b | 如果a大于等于b,为true |
| < | a < b | 如果a小于b,为true |
| <= | a <= b | 如果a小于等于b,为true |
| in | a in(b1,b2,b3…) | 判断a是否是b1,b2,b3…中的一个 |
| between…and | a between b1 and b2 | 判断a是否在b1,b2之间 |
| like | a like b | 如果a与b匹配，则为true |
| not like | a not like b | 如果a与b不匹配，则为true |
| is null | a is null | 如果操作数为null，则为true |
| is not null | a is not null | 如果操作数不为null，则为true |
SQL查询
SQL查询在数据库中是非常重要的组成，因为未来开发场景下，大多数的功能都集中在查询上，而且查询可以简单，也可以复杂，复杂到很多表之间的联合查询。
select
distinct
查询列
from
表名称
连接表（inner join/left join/right join）
where
查询条件
group by
分组依据
having
分组的查询条件
order by
排序字段
limit
结果限制
多表联合查询
笛卡尔乘积：
- 先确定数据要用到哪些表。
- 将多个表先通过笛卡尔积变成一个表。
- 然后去除不符合逻辑的数据（根据两个表的关系去掉）。
- 最后当做是一个虚拟表一样来加上条件即可。
/*
多表联合查询
1.等值连接 （查询条件数至少等于 表数-1）
2.内连接
3.左外连接
4.右外连接
5.自连接
*/
-- 等值连接
-- 为避免笛卡尔积出现，应该在查询时加入等值连接条件
/*
子查询：将一个查询的查询结果当做另一个查询的条件使用;或者将一个查询的结果当做一张临时表使用
单行子查询
多行子查询
多列子查询（临时表）
*/
查询注意事项:
- 对于任何查询，明确几个目标：
- 查询列
- 查询目标表
- 查询条件
- 查询方式：
多表查询: 等值连接 内连接 自连接 外连接(左外连接，右外连接) 子查询 单行子查询 多行子查询 多列子查询（虚拟表）
对于同一个查询需求可以使用多种手段实现，但是需要考虑效率
查询语句优化：
尽量避免子查询
避免使用“*”
对查询结果尽量使用limit显示
单条sql尽量少用或者不用like
在sql语句中尽量使用等值作为条件
使用函数的时候，尽量使用系统函数，少使用自定义函数
对某些经常使用或者经常被查询的字段添加索引
维护数据完整性之约束
在数据库中维护数据完整性的解决方案有两种：
- 约束（constraint）
- 触发器（trigger）
约束（Constraint）
约束是通过对数据表中的字段使用以一些特殊用途的关键字进行限定，从而使得该列的数据不能随意填写，以此来保障数据的完整性；数据库中一共包含以下5种约束：
- 主键约束（primary key）
- 外键约束（foreign key）
- 唯一约束（unique）
- 检查约束（check） Mysql暂时不生效
- 不为空约束（not null）
主键约束（primary key）
主键约束一般用于一张表中的标识列（该列数据唯一且不为空）；每一张表都应该存在一个主键，主键可以用于一个列，也可以应用于多个列
- 如果主键类型为int的时候：
建议主键设置成自增长，如果主键设置成自增长的时候，在添加数据时，可以不用给主键赋值
INSERT into emp (eno,ename) VALUES (null,'沙和尚2')
- 如果主键类型为字符串：
可以使用类似于UUID这样的一个32位或者18位的一个字符串：比如eb077943-0b49-43ba-a7be-22c443e1b7b8
作为varchar 类型的id的值
- 注意事项：
- 主键列一般用于标识列（不能重复，且不为空）
- 尽量避免使用联合主键（设置多个列同时为主键）
- 任何表都应该存在主键列
外键约束（foreign key）
外键约束一般用于对一个表与另一个表进行关联时的依据，通常会在表中使用foreign key建立外键；外键必然是另一张表的主键，而另一张就称之为主表，添加外键的表称之为从表。
唯一约束（unique）
唯一约束用于设置表中指定列是唯一的（不可重复）；常见于用于表中的用户名列，分类表中类别名列等，使用方式：
username varchar(30) unique not null,
如果报错值，后面偷偷跟了一个空格这样会导致数据重复
解决这种问题：
先后台对这个数据去空格在数据库中查询一边，再做保存
不为空约束（not null）
设置表中指定列必须给定值，不允许为null
检查约束（check）
检查约束在mysql中还未生效，如果需要对字段进行检查约束，可以考虑使用enum类型。
触发器：
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
day3 - Mysql数据库 (三)
内置函数
聚合函数
| 函数名 | 说明 |
|---|---|
| count() | 统计数据行 |
| sum() | 对指定列求和 |
| avg() | 对指定列求平均值 |
| max() | 获取指定列最大值 |
| min() | 获取指定列最小值 |
--查询表中一共有多少员工
select count(*) from emp;
--查询所有员工的总薪资
select sum(sal) from emp;
--查询所有员工的月薪平均值
select avg(sal) from emp;
--查询工资最低的员工薪资
select min(sal) from emp;
--查询工资最高的员工薪资
select max(sal) from emp;
字符函数
| 函数名 | 说明 |
|---|---|
| concat (st2 [,… ]) | 连接多个字符串为一个字符串 |
| length(s) | 获取字符串的字节长度 |
| lcase/ucase | 字符串转小写/字符串转大写 |
| ltrim(s)/rtrim(s) | 去除字符串左边空格/右边空格 |
| substr(s,pos,len) | 将字符串s从pos开始截取len长 |
| lpad/rpad | 左填充/右填充 |
-- 获取字符串使用的字符集
select charset('helloworld');
-- 将多个字符串连接为一个字符串
select concat('hello','world',',','softeem')
-- 应用
select * from emp where concat(ename,job,sex) like '%阿%';
-- 返回字符占据的字节数（中文字符占3字节(utf-8),英文字母占1个字节）
select length('hello');
select length('软帝softeem');
-- 将字母转小写（toLowerCase）
select LCASE('HELLO');
-- 转大写
select upper ('dsasa');
-- 去除字符串空格
select TRIM(' world ');
-- 左填充(对指定的字符串检测，长度如果未到达目标长度，则使用指定占位符从左边开始填充)
select LPAD('softeem',10,'*');
select LPAD(ename,10,"#") from emp;
select RPAD(ename,10,"#") from emp;
use mydb;
select * from tbemp;
-- 字符串截取
select substr('abdcsa',1,3);
数值函数
| 函数名 | 说明 |
|---|---|
| abs(n) | 取数值绝对值 |
| round | 四舍五入 |
| ceiling(n) | 向上取整 |
| floor(n) | 向下取整 |
| format(n,len) | 截取len位小数（四舍五入） |
| rand() | 获取随机数(0-1)，取0-1之间随机的小数，无限接近于0且不会等于0，无限接近于1，且不会等于1 |
/*
数值函数
*/
-- 绝对值
select abs(-100);
-- 向上、向下取整
select CEILING(3.14);
select FLOOR(3.54);
-- 保留指定位小数点
select FORMAT(3.1415926,2);
---随机数
SELECT ROUND(rand()*10)
日期函数
| 函数名 | 说明 |
|---|---|
| now() | 获取当前时间 |
| current_date() | 获取当前日期 |
| current_time() | 获取当前时间（时分秒） |
| current_timestamp() | 获取当前时间戳 |
| date() | 获取时间的日期部分 |
| day() | 获取日期中的天数部分 |
| datediff(t1,t2) | 获取两个日期之差（天数） |
/*
时间日期函数
*/
-- 获取当前的系统时间
select now();
select CURRENT_DATE(); -- 获取当前日期
select CURRENT_TIME(); -- 获取当前时间
select CURRENT_TIMESTAMP(); -- 获取当前时间戳
select date(now());
-- 统计从出生到现在一共安全生活了多少天
select DATEDIFF(now(),'1998-07-29')
-- 获取日期中的day部分(天数)
select day(now());
create table temp(today date,msg varchar(30));
insert into temp values(CURRENT_DATE(),'nothing');
select * from temp;
-- 日报表中获取当天提交的日报信息
create table log(
id int primary key auto_increment,
content varchar(20000),
time timestamp default CURRENT_TIMESTAMP);
insert into log(content,time) values('n天前收获很多！！！','2020-03-29 10:11:11');
select * from log where date(time)=date(now());
-- 查询所有的在29号发布的日志
select * from log where day(time)=29;
加密函
| 函数名 | 说明 |
|---|---|
| md5(s) | 对字符串使用md5算法加密 |
| sha(s) | 对字符串使用sha加密 |
| password(s) | 使用sha1对字符串加密 |
-- 加密函数（非对称加密）
select password('123456');
select MD5('123456');
select SHA('123456');
这三种加密方式都是不可逆，不能反推破解
复制表与数据复制
-- 复制表（同时复制数据） 对关键表的数据进行备份
create table tbemp select * from emp;
-- 复制表结构（只需要结构不要数据）
create table tbemp select * from emp where 1=0;
-- 复制数据(蠕虫复制:一般用于表机构稳定性的测试)
insert into tbemp(ename,job,hiredate,age,sex,sal,dno)
select ename,job,hiredate,age,sex,sal,dno from tbemp;
索引
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
索引类似字典的目录，可通过索引快速查询到目标数据。
索引分类
1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点
2、唯一性索引：索引列中的值必须是唯一的，但是允许为空值
3、主键索引(聚簇索引)：即表中主键列
4、全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。（mysql数据库在5.5版本以前，使用的引擎是MylSAM;在5.5以及以上的版本，mysql的引擎使用的是InnoDB）
5、组合索引：
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
如果某个字段不是经常被作为查询条件进行使用时，那么该字段尽量就不要作为索引
因为添加字段索引，就是将该字段的索引添加在服务器内存当中，如果每张表的索引过多，会对整个服务器的运行产生巨大的影响
索引创建语法
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W8HANEeO-1607243872384)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596078377803.png)]
创建普通索引
create index index_ename on tbemp(ename);
创建唯一索引
create unique index index_username on tbuser(username);
唯一索引要求被添加索引的列值必须唯一
创建组合索引
create index index_emp on tbemp(ename,sal,age);
组合索引的使用一般在进行多条件查询时提升查询效率
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dEQUsvaj-1607243872386)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596079997391.png)]
注意事项：
任何一张数据库表都应该有一个主键列，默认数据系统对主键列增加了聚簇索引，因此针对主键列的查询速度比较快。
Mysql中的索引实现基于B+树（二叉树） 最左匹配原则
视图
视图实际上就是一张虚拟的表，视图是针对基表的一部分字段的缩影，一般用于对一些字段比较多的表，通过视图可以简化表结构；另外针对一些涉及到多表的查询操作时，建立视图可以简化查询语句；
创建语法
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zmz9VLyI-1607243872391)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596089489736.png)]
-- 创建视图
create view v$emp as select ename,job,hiredate,sal from emp;
select * from v$emp;
update v$emp set hiredate=now() where ename='孙悟空';
-- 复杂视图创建
create view v$details
as
select
e.eno,e.ename,e.job,e.hiredate,e.age,e.sal,
d.dno,d.dname,d.tel,
s.level
from emp e,dept d,sallevel s where e.dno=d.dno and
e.sal between s.lowsal and s.hisal;
select level from v$details where eno=6;
-- 查询研发部所有人的薪资等级和部门信息
select level,ename from v$details where dname='研发部'
注意事项：
视图不占据存储空间，只是一种逻辑存在（非物理存储）；只有在使用视图时才通过视图的定义，加载对应的数据
对视图的操作会影响基表(物理表，实际开发中一般针对视图做查询，避免基于视图做修改
索引提高查询速度，视图简化了查询的方式
事务（Transaction）
概述
事务是实际开发中，比较常见业务逻辑操作中都会存在问题，比如转账的业务，需要将一个账户的钱转向到另一个账号，此时会涉及到两个修改操作：A账户钱减少，B账户钱增加；这两个操作要保证能同时成功或者同时失败，那么这一个业务需求(逻辑单元)就称之为一个事务。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vuewHOzX-1607243872395)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596090904115.png)]
事务特性
数据库事务包含四大特征(ACID)：
- 原子性：对于事务中的多次更新操作要么同时成功，要么同时失败
- 一致性：保证事务操作完成之后，所有的结果一致
- 隔离性：事物之间各自独立存在相互不影响
- 持久性：事务完成之后，确保所有的数据长期持久的存在
Mysql事务的使用
mysql中只有使用InnoDB引擎才支持事务；MyISAM引擎不支持事务
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IvLlgih0-1607243872398)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596093789302.png)]
参考代码
-- 查看表的状态
show table status where name = 'emp'
-- 开启事务(事务一旦开，后续的所有更新操作都在这个事务中，直到提交后才会对物理表产生影响)
start transaction;
-- 设置保存点(在当前位置设置保存点，通过rollback to 保存点，可以回滚到该位置)
savepoint p1;
-- 回滚保存点（回滚到指定的保存点：一旦回滚，则当前保存点会撤销）
rollback to p1;
-- 回滚到事务开始的位置
rollback;
-- 提交事务
commit;
事务隔离级别
由于实际的业务操作可能会涉及到很多事务的并发操作，因此在事务并发时可能会遇到以下问题：
-
**丢失更新：**撤消一个事务时，把其它事务已提交的更新的数据覆盖了。
-
脏读：事务A读取了事务B更新的数据，而此时事务B并未提交，那么A读取到的数据是脏数据
脏数据
-
**不可重复读：**事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新
并提交，导致事务A多次读取同一数据时，结果 不一致。 -
**幻读：**系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B
就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有
改过来，就好像发生了幻觉一样，这就叫幻读。
对于以上可能出现的问题，数据库中引入事务隔离级别的解决方案：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k7QHMxNm-1607243872400)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596094489993.png)]
以上是解决事务并发问题的方案，其中隔离级别从低到高，对数据操作的效率影响从低到高；Mysql中默认的隔离级别是：repeatable-read
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Tqqboc3s-1607243872404)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596094769618.png)]
数据库可编程性介绍
概述
在之前学习到所有有关数据库操作几乎都是一行命令解决问题，这些命令大多都是sql标准语法；但是不同的数据库管理系统对sql语句都添加了扩展支持，允许通过一些常见数据类型，运算符，分支语句，循环语句等实现一些类似常规编程语言的操作，这个概念称之为数据库的可编程性；对于不同的数据库产品对于可编程的命名也存在差异：
- MSSQLServer 的可编程性称之为T-SQL
- Oracle中的可编程性称之为PL/SQL
数据库可编程性分类
- 存储过程(procedure)
- 触发器(trigger)
存储过程（Procedure）
存储过程(Procedure),是数据库操作语言SQL的可编程性实现，传统的SQL语句通常是在数据库服务器中执行一条命令，命令在数据库引擎内部需要经过创建与分析的过程，因此在运行时间上开销较大；而存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，后期的使用只需通过命令调用即可，因此，在执行过程时便可节省此开销。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Di0wfksI-1607243872406)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596104686639.png)]
--最基础简单存储过程
create procedure testP1 (a varchar(10),b varchar(30))
BEGIN
if a is null then
set a = "男";
end if;
SELECT * from emp where sex = a;
end
call testP1(null,"1");
-- 使用临时变量的存储过程
create procedure emp1 ($ename varchar(30))
BEGIN
DECLARE $job varchar(30);
SELECT job into $job from emp where ename = $ename;
if $job is null then
set $job = '普通员工';
update emp set job = $job where ename = $ename;
SELECT * from emp where ename = $ename;
end if;
end
call emp1('沙和尚');
DROP procedure emp1;
-- 查询指定部门名的员工信息？如何使用存储过程实现
create procedure sp_emp3($dname varchar(30))
begin
-- 声明临时变量
declare $dno int;
-- 根据指定的部门名称查询到部门号并赋值到临时变量中
select * from emp where dno in (select dno from dept where dname=$dname);
end
call sp_emp3('研发部');
-- 如何利用存储过程实现一个分页操作：输入一个每页数据行数和页码数，显示该页数据
create procedure sp_page(pagesize int,pagenum int)
begin
-- 声明临时变量
declare startNum int;
-- 对变量赋值操作
set startNum = (pagenum - 1) * pagesize;
select * from emp limit startNum,pagesize;
end
call sp_page(3,3)
-- 删除存储过程
drop procedure sp_page;
存储过程优劣势：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m44SO9AJ-1607243872408)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201202/assets/1596104342361.png)]
day4 - 数据库设计
数据库可编程性-触发器
触发器（TRIGGER）是MySQL的数据库对象之一，对表中数据进行删除，更新和插入时做的一个监听，触发器就自动启动，并做一些后续处理
列子：
对用户信息的更新删除插入等一些操作的时候，对该动作捕获，将一些关键性信息保持到一个指定的位置，并记录下来
从5.0.2版本开始支持。该对象与编程语言中的存储过程以及函数非常类似，都是SQL语句可编程性的实现，并且都需要编写、编译以及调用。但是触发器的执行不是由程序调用，也不是由手动启动，而是由事件来触发、激活以及执行。
那么为什么要使用数据库对象触发器呢？在具体开发项目时，经常会遇到如下实例：
- 在删除部门表中记录时，员工表因为设置外键约束关联部门表，导致部门表中记录无法删除，如何在执行部门删除时同时将关联的员工表中部门号列设置为NULL。
- 在对员工表中员工数据删除时如何自动将被删除的员工信息记录到备份表中。
上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作，对SELECT语句则不会激活触发器。
语法
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8lib082V-1607243872411)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596414885952.png)]
案例
-- 触发器创建
-- 当对emp表做更新操作时，将操作记录保存在日志表中
--当触发器启动时，将部分信息保存到日志表中
create TRIGGER emp_trigger BEFORE insert ON emp for each row
BEGIN
insert into logs (id,createTime,exquet) values (null,now(),'inset');
end;
-- 操作emp表，对emp表中使用更新
update emp set ename = '天蓬元帅' where eno=11;
-- 将数据插入到复制表中
create trigger emp_trigger2 after update on emp for each row
BEGIN
INSERT into empcopy (eno,ename,job,hiredate,age,sex,sal,dno) values (old.eno,old.ename,old.job,old.hiredate,old.age,old.sex,old.sal,old.dno);
end;
--当员工表的dno和部门表dno是主外建关系，那么当删除部门表信息时，就需要先将员工表中该部门的dno更新为null，才可以删除部门信息
create trigger emp_trigger2 BEFORE delete on dept for each row
BEGIN
UPDATE emp set dno = null where dno = old.dno;
end;
触发器的应用：
触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中
| 触发器类型 | new和old的使用 |
|---|---|
| INSERT型触发器 | 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据 |
| UPDATE型触发器 | 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据 |
| DELETE型触发器 | 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据 |
–BEFORE 和old old使用的没更新之前的数据源
–BEFORE 和new new使用的是更新之后的数据源
–after 和old old使用的没更新之前的数据源
–after 和new new使用的是更新之后的数据源
数据库优化
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SXj0gLOf-1607243872414)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596370525218.png)]
数据库的分库分表
数据库设计
目前所有的主流DBMS都是关系型数据库。通过二维表表示数据本身，另外表中存在一些关联列实现表和表之间的关系。
表之间关系
- 一对一（人对应一个身份证）
- 一对多/多对一（一个部门包含多个员工）
- 多对多（学生选课，用户和商品）
一对一
一张表中的一条数据，对应另外一张表中的一条数据，而且这些数据在表里都是唯一存在的
实际开发中一对一的关系并不常见，大多数时候一对一的关系其实可以建立成为一张表；如果需要建立一对一的关系，实现方式有两种：
-
唯一外键关联(在其中一张表建立外键，同时设置外键列唯一unique)
-
主键关联（两个表的主键列一致）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fQzLZspb-1607243872417)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596164167153.png)]
一对多/多对一
一对多：一张表的一条数据，可以对应到另外一张表的很多条数据
一对多/多对一关系在开发中十分常见，大多数时候表之间的关系都是一对多/多对一的，比如：员工和部门，学生和班级，老师和学生，商品类别和商品，实现方式：
- 在多的一方表中新增外键列，关联一的一方的主键列
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oJsvgiN3-1607243872418)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596164769379.png)]
多对多
多对多的关系在开发中也是很常见的，比如说：学生和课程关系，用户和商品的，实现关联的方式如下：
- 通过第三张表维护两个表的关系
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RtXPzqDr-1607243872421)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596165443776.png)]
数据库设计范式
在进行数据库设计的时候需要满足的一些规范形式，称之为数据库范式，满足范式的数据库设计是合理的，数据冗余小的，并且不会引起数据更新的异常。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jex6lsmA-1607243872426)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1607003203211.png)]
第一范式
要求建立的数据库表中所有的列是原子的，每一列不可再拆分；目前的关系型数据库默认都是满足第一范式（不可能创建出不满足第一范式的数据表）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-avUrcCXs-1607243872431)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596166392271.png)]
第一范式：列不可再分
保证每列的原子性，原子性的意思就是每列不可以再次被拆分，并保持独立且唯一
第二范式： 表中的每一列都必须完全依赖主键
可以去除部分的数据冗余
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eb75iIhE-1607243872433)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596167334552.png)]
第三范式: 需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j0kLwVT1-1607243872438)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596167846781.png)]
范式总结
在实际开发中，一般情况只要满足三大范式即可；另外，由于程序对查询的需求(处于便捷性考虑)可能会出现违背三大范式的情况；因此三大范式只是设计数据时候的一种参考，并不是定律。
范式的存在主要解决了：
- 数据冗余
- 更新(insert,delete)操作异常
数据库设计案例分析
概念模型设计（ER图）
E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3R8vKztZ-1607243872440)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596370648058.png)]
实体(表)，关系理解为表之间的联系；在数据库设计阶段，实体关系图的建立位于概念模型设计阶段，这一阶段主要用于进行实体之间的关系建立
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lHgki9aM-1607243872444)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596177151438.png)]
物理模型设计（建表)
数据库设计一般使用一些专业设计工具，其中最常见以sybase(SAP)的数据库建模工具 PowerDesigner最为常见，还有一些其他数据库图形工具，比如navcat的模型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1vCytLjE-1607243872446)(E:/Java%E5%AD%A6%E4%B9%A0/Java%E5%AD%A6%E4%B9%A0/20201204/%E7%AC%94%E8%AE%B0/assets/1596183813747.png)]
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
