gitee
1 | 1.git add . [file] add all file under '.' |
cmd修改
The commonly used cmd command
1 | 1.cd go to the directory |
configration cmd
1.Establish a configration file and name it cmd.cmd,enter the following.
move the file to a directory (c:\windows)
1 | @echo off |
step 2 modify the registeration information table
1 | 1.Locate the path |
编程题
- 创建回复表,并且模拟合适的数据
- 给定一个评论id,删除该评论下的所有的回复.但是要保留该评论下面的评论.
- 给定一个视频id,将这个视频下的所有的评论以及回复信息全部加载出来.
1发布了视频
2(评论）哈哈 ,挺搞笑
3-2 (评论）你笑什么
4-2 (评论）你傻笑？有病？
5-3（回复） 别人不能笑？
2-3（回复） 笑一笑十年少
5-2（回复） 他有病
2-5（回复） 是的
6-2（回复） 嗯嗯，笑别人，确实有毛病
解答题
- Mybatis有哪些Executor执行器？
2
3
4
5
6
ReuseExecutor：执行update或select，以sql作为key查找Statement对象，存在就使用，不存在就创建，用完后，不关闭Statement对象，而是放置于Map内，供下一次使用。简言之，就是重复使用Statement对象。
BatchExecutor：执行update（没有select，JDBC批处理不支持select），将所有sql都添加到批处理中（addBatch()），等待统一执行（executeBatch()），它缓存了多个Statement对象，每个Statement对象都是addBatch()完毕后，等待逐一执行executeBatch()批处理。与JDBC批处理相同。
CachingExecutor：CachingExecutor是一个Executor接口的装饰器，它为Executor对象增加了二级缓存的相关功能，委托的执行器对象可以是SimpleExecutor、ReuseExecutor、BatchExecutor中任一一个。执行 update 方法前判断是否清空二级缓存；执行 query 方法前先在二级缓存中查询，命中失败再通过被代理类查询。
- Mybatis是如何进行分页的？分页插件的原理是什么？
- mybatis优缺点
2
3
4
5
6
7
8
2.JDBC相比减少50%以上的代码量，不需要手动开启连接。
3.与各种数据库兼容。（因为他与JDBC连接数据库）。
4.提供了许多第三方插件。（分页插件，逆向工程）。
5.能够与spring很好的集成。
6.mybatis相当灵活，不会对现有程序影响，sql写在XML中，从程序代码中彻底分离出来，解除了sql与程序耦合，重用
7.提供XML标签，支持编写sql语句。
8.提供映射标签，支持对象与数据库的orm字段关系映射
$和#区别
2
3
4
5
都能取到变量的值。
不同点：
#可以实现预编译，会先把#{变量}编译成?，在执行时再取值，可以防止sql注入。
$是直接进行字符串替换。Mybatis动态sql有什么用？执行原理？有哪些动态sql？
2
3
4
5
2、Mybatis 提 供 了 9 种 动 态 sql 标 签 ： trim|where|set|foreach|if|choose|when|otherwise|bind。
3、其执行原理为，使用 OGNL 从 sql 参数对象中计算表达式的值，根据表达式的值动态拼接 sql，以此来完成动态 sql 的功能。
- resultType和resultMap区别
2
3
4
5
6
7
8
9
10
11
12
一、对象不同
1、resultmap：resultMap如果查bai询出来的列名和pojo的属性名不一致，通过定义一个resultMap对列名和pojo属性名之间作一个映射关系。
2、resulttype：resultType使用resultType进行输出映射，只有查询出来的列名和pojo中的属性名一致，该列才可以映射成功。
二、描述不同
1、resultmap：resultMap对于一对一表连接的处理方式通常为在主表的pojo中添加嵌套另一个表的pojo，然后在mapper.xml中采用association节点元素进行对另一个表的连接处理。
2、resulttype：resultType无法查询结果映射到pojo对象的pojo属性中，根据对结构集查询遍历的需要选择使用resultType还是resultMap。
三、类型适用不同
1、resultmap：mybatis中在查询进行select映射的时候，返回类型可以用resultType，也可以用resultMap。
2、resulttype：resultType是直接表示返回类型的,而resultMap则是对外部ResultMap的引用，但是resultType跟resultMap不能同时存在。
如果你要用resulttype返回一个复杂对象的话，就必须返回这个对象的所有属性
- Mybatis是否支持延迟加载？ 如果支持它的原理是什么？
2
3
当然了，不光是 Mybatis，几乎所有的包括 Hibernate，支持延迟加载的原理都
是一样的。
什么是ORM
2
3
，关系数据库是企业级应用环境中永久存放数据的主流数据存储系统。对象和关系数据是业务实体的两种表现形式，业务实体在内存中表现为对象，在数据库中表现为关系数据。内存中的对象之间存在关联和继承关系，而在数据库中，关系数据无法直接表达多对多关联和继承关系。因此，对象-关系映射(ORM)系统一般以中间件的形式存在，主要实现程序对象到关系数据库数据的映射。当实体类中的属性名和表中的字段名不一样 ，怎么办 ？
2
第 2 种： 通过来映射字段名和实体类属性名的一一对应的关系。
如何获取自动生成的(主)键值?
2
3
4
5
6
示例:
<insert id=”insertname” usegeneratedkeys=”true” keyproperty=”id”>
insert into names (name) values (#{name})
</insert>
1 | drop table comment; |
JDBC
jdbc - java database connectivity - java数据库连接
用java编写的程序来连接的数据库的技术.jdbc是sun公司制定的一套”规范”,里面提供了大量的接口.由不同的db厂商进行实现.
而这些实现类就是驱动.
jdbc是最原始的持久层[和db交互层]的技术,属于JavaEE十三种核心技术中的一种.后期学习的持久层框架都是对jdbc的封装.
为什么要有jdbc
如果没有jdbc
2
3
4
5
6
7
8
9
10
11
12
MysqlDriver driver = new MysqlDriver();//mysql
//java切换db比较麻烦 - 换一套连接db的代码.
//sun制定jdbc规范 - 连接db的规范
//java.sql.Driver[I],不同的db厂商都是要去实现这个接口的
//伪代码
Class.forName("驱动类的全限定名"); // 可以配置到文件中的.
优势
为java程序访问不同的db提供统一的方式.在切换db的时候,能够做到最少改动.
相关api
java.sql.Driver[I] - 每个驱动程序类必须实现的接口. jdbc编程第一步就是需要加载驱动[jdbc规范4.x开始可以省略不写]
java.sql.DriverManager[C] - 驱动管理类. 获取连接
static Connection getConnection(String url,String user,String password)
2
3
password: db密码
url: 主协议:次协议://ip地址:端口号/db名称?key1=value1&key2=value2java.sql.Connection[I] - 一次会话/连接
- Statement createStatement();
- PreparedStatement prepareStatement(String sql);//预编译语句对象
- CallableStatement prepareCall();// 调用存储过程.
java.sql.Statement[I] - 负责将sql语句发送到db-server端
- int executeUpdate(String sql);// 用于执行DML操作,insert,update,delete
- ResultSet executeQuery(String sql);//用于执行DQL语句
java.sql.ResultSet[I] - 结果集对象
本质上仅仅是一个游标.默认指向第一行的上方.
- boolean next();//1. 游标向下移动一行;2. 如果下一行没有行记录,则返回false
- 取列值. String getString(int colINdex);//根据列的序号取值,第一列就是1
- 取列值 - String getString(String colName);//根据列的名称(支持使用别名)
体验
查询 - 六大编程步骤
脚本
2
3
4
5
6
7
8
9
10
11
create table jdbc_user(
id int(7) primary key auto_increment,
username varchar(20) not null unique,
password varchar(20),
birthday date,
power int(1) comment '0-管理员,1-普通用户'
);
insert into jdbc_user values(1,'admin','123','2021-01-01',0);
insert into jdbc_user values(2,'tom','123','2021-01-01',1);
insert into jdbc_user values(3,'蔡根花','123','2021-01-02',1);实体类
2
3
4
5
6
7
private Integer id;
private String username;
private String password;
private Date birthday;
private Integer power;
}制定持久层的接口 - IUserDao.java
制定持久层的实现类 - UserDaoImpl.java
单元测试
Statement弊端
参数硬拼接到了sql语句中,比较麻烦的
容易造成SQL注入 - 非法的参数/sql硬拼接到了sql语句中.
缺点 - 通过语句对象每次发送sql到db-server端,都是需要对sql语句进行编译和解析的.但是业务中可能遇到同构的sql.
同构的sql还是会多次编译和解析的,比较影响性能.
优点 - 一个statement对象可以用来多次发送不同的sql语句.
PreparedStatement[I]
预编译语句对象
提前将带有占位符号的sql发送到db-server进行解析,后面只要发送参数即可.适合同构的sql
缺点: 一个pst对象,只能编译一条sql语句.
批处理效率问题
addBatch(String sql);//向当前批处理中添加一条sql语句。
executeBatch();//执行批处理
clearBatch();//清空批处理需要在url中添加rewriteBatchedStatements=true
JDBC事务
jdbc事务 - 由连接的db决定
jdbc事务 - 编程性事务 - 事务代码和应用程序代码耦合在一块儿的.
spring事务 - 声明式事务.事务代码(与业务无关的代码)和应用程序进行分离.
jdbc事务 - dml操作之后默认都是自动commit - executeUpdate
代码示例的结构
2
3
4
5
6
7
conn.setAutoCommit(false);
conn.commit();
}catch(Exception e){
conn.rollback();
}
模板设计模式
针对DML
1 | Connection conn = null; |
ResultSetMetaData
结果集元数据
sql优化
sql优化
1 | -- sql优化遵守原则 |
delete和truncate的区别
1、在功能上，truncate是清空一个表的内容，它相当于delete from table_name。
2、delete是dml操作，truncate是ddl操作；因此，用delete删除整个表的数据时，会产生大量的roolback，占用很多的rollback segments， 而truncate不会。
3、在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，便于以后的使用，另外它是“假相”的删除，相当于windows中用delete删除数据是把数据放到回收站中，还可以恢复，当然如果这个时候重新启动系统（OS或者RDBMS），它也就不能恢复了！
而用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放，相当于windows中用shift+delete删除数据，不能够恢复！
4、truncate 调整high water mark 而delete不；truncate之后，TABLE的HWM退回到 INITIAL和NEXT的位置（默认）delete 则不可以。
5、truncate 只能对TABLE，delete 可以是table,view,synonym。
6、TRUNCATE TABLE 的对象必须是本模式下的，或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的，或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。
7、在外层中，truncate或者delete后，其占用的空间都将释放。
8、truncate和delete只删除数据，而drop则删除整个表（结构和数据）。
区别
1.TRUNCATE TABLE是非常快的
2.TRUNCATE之后的自增字段从头开始计数了，而DELETE的仍保留原来的最大数值
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
注意:这里说的delete是指不带where子句的delete语句
相同点
truncate和不带where子句的delete, 以及drop都会删除表内的数据
不同点:
\1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动
显然drop语句将表所占用的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).
4.速度,一般来说: drop>; truncate >; delete
5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/
innoDB_and_myisam
innoDB与Myisam的区别
InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3**.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大**，其他索引也都会-很大。
3.MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
- InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
- Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了**
- MyISAM表格可以被压缩后进行查询操作
7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件 Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
如何选择：
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差
InnoDB为什么推荐使用自增ID作为主键？
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。
储存函数触发器
#函数function
mysql中内置了很多函数
mysql8.0不支持创建函数的语法的
解决方案:
在my.ini或者my.cnf文件下添加：
[mysqld]
log-bin-trust-function-creators=1
最后mysql服务器重启
创建语法
函数体中一定有return语句
只要遇到varchar类型,必须要指定参数的长度
语法部分
2
3
4
5
6
7
8
9
10
delimiter $$
create function 函数名([变量名 数据类型(长度)]) returns 返回类型
begin
-- -- 函数体
return 结果;
end $$
delimiter ;
练习
传入俩个整数,返回俩个整数的相加的结果
2
3
4
5
6
7
8
9
create function dy_add(a int,b int) returns int
begin
return a + b;
end //
delimiter ;
-- 调用函数
select dy_add(50,10);
练习
写一个函数可以实现日期转成指定格式的字符串xxxx年xx月xx日
2
3
4
5
6
7
8
9
10
11
12
drop function dy_format;
delimiter //
create function dy_format(dt date) returns varchar(20)
begin
return date_format(dt,'%Y年%m月%d日');
end //
delimiter ;
select dy_format(now());
语句语法
while..do..end while
求出1~x之间的总和
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
delimiter //
create function x_add(x int) returns int
begin
-- 循环变量因子,局部变量
declare i int default 1;
-- 定义一个变量,用来保存总的和
declare sums int default 0;
while i<=x do
-- 对已经声明过的变量进行赋值操作
set sums = sums + i;
set i = i + 1;
end while;
return sums;
end //
delimiter ;
select x_add(100);分支语句if … then…end if
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
drop function ji_add;
delimiter //
create function ji_add(x int) returns int
begin
declare i int default 1;
declare sums int default 0;
while i<=x do
if i%2!=0 then
set sums = sums+i;
end if;
set i = i + 1;
end while;
return sums;
end //
delimiter ;
全局变量
求出1~x之间的数字之和,但是不包括5的倍数
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
delimiter //
create function my_add(x int) returns int
begin
-- 定义一个局部变量
declare i int default 1;
-- 定义一个全局变量,用来存储总和
-- 全局变量 @变量名
-- 系统的全局变量 @@tx_isolation
set @sums = 0;
-- 对语句片段进行一个命名 - 类似于java中的continue语句
-- 命名是任意的
success:while i<=x do
-- mysql中判断是否相等,=
if i%5=0 then
set i = i + 1;
-- continue - 跳过本轮循环,继续下一轮循环
iterate success;
end if;
set @sums = @sums + i;
set i = i + 1;
end while;
return @sums;
end //
delimiter ;
2
3
4
-- 全局变量,在函数体外调用
select @sums;
存储过程
定义:为了完成一些特定的工程,提前将sql语句预编译好,存储在在mysql-server端.并且只会编译一次.后面直接调用
存储过程的时候,是不需要再次对sql语句进行编译了,提高性能.
存储过程可以做到标准的组件编程[把sql封装好,形成一个组件]
sql执行的过程
- mysql-client 客户端编写sql语句 mysql> select * from s_emp;
- 将这个客户端的sql发送到mysql-server端,需要对这条sql进行编译[检测语法]以及解析.
- mysql-server将解析之后的结果返回给mysql-client;
在没有使用存储过程之前,只要将sql语句发送到mysql-server端,每次都是需要对sql进行编译的.比较浪费时间.
语法
1 | -- 删除存储过程 |
体验
1 | -- 把s_emp表中的员工的平均薪资预编译好在mysql-server端存储 |
in - 接受参数
1 | drop procedure in_pro; |
1 | -- 直接接受一个字面量 |
out - 返回结果
1 | drop procedure out_pro; |
1 | -- 注意点:如果参数使用out,那么此处是不允许直接使用字面量进行传参 |
练习 - 封装单个结果集
方式一
根据员工的id来返回员工的名称,薪资
和表结合一起使用的话,数据类型和表统一
2
3
4
5
6
7
8
9
10
11
12
delimiter //
create procedure find_pro(in eid int(7),out fname varchar(20),out sal float(11,2))
begin
select first_name into fname from s_emp where id=eid;
select salary into sal from s_emp where id=eid;
end //
delimiter ;
call find_pro(1,@fname,@sal);
select @fname;
select @sal;
方式二
统一赋值
2
3
4
5
6
7
8
9
10
11
delimiter //
create procedure find_pro(in eid int(7),out fname varchar(20),out sal float(11,2))
begin
select first_name,salary into fname,sal from s_emp where id=eid;
end //
delimiter ;
call find_pro(1,@fname,@sal);
select @fname;
select @sal;
练习 - 封装多个结果集
传统的做法 - 已经被弃用了 - 性能比较低 - 游标
定义第三张表来存储多个结果集
2
3
4
5
6
7
8
9
10
11
12
13
create table emp_copy as select first_name,salary from s_emp where 1=2;
drop procedure find_pro;
delimiter //
create procedure find_pro(in eid int(7))
begin
insert into emp_copy(first_name,salary) select first_name,salary from s_emp where id>eid;
end //
delimiter ;
call find_pro(20);
select * from emp_copy;
练习 - 带事务
转账功能 - 要么同时成功,要么同时失败
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
delimiter //
create procedure transfer_pro(in sid int(7),in tid int(7),in money double(10,2),in st int(7))
begin
-- 定义一个局部变量,用来展示是否转账成功
declare msg varchar(20) default '';
-- 手动开启失败
start transaction;
-- 第一条sql
update t_acc set balance = balance - money where id = sid;
-- 故意搞事情
if st=1 then
set msg = 'sorry,转账失败!';
-- 事务回滚
rollback;
else
-- 第二条sql
update t_acc set balance = balance + money where id = tid;
set msg = 'good,转账成功!';
-- 手动提交事务
commit;
end if;
select msg;
end //
delimiter ;
2
3
4
5
call transfer_pro(1,2,1000,2);
-- 转账失败
call transfer_pro(1,2,1000,1);
语句使用
条件分支语句
if 条件表达式 then elseif … then … else…end if;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
delimiter //
create procedure if_pro(in a int)
begin
declare msg varchar(20) default '';
if a>=90 then
set msg = '优秀';
elseif a>=80 then
set msg = '良好';
elseif a>=60 then
set msg = '中等';
else
set msg='不及格';
end if;
select msg;
end //
delimiter ;
case .. when 固定的值 then .. else .. end case
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
delimiter //
create procedure case_pro(in a int)
begin
declare msg varchar(20) default '';
case a
when 1 then
set msg = '1';
when 2 then
set msg = '2';
else
set msg = '3';
end case;
select msg;
end //
delimiter ;
循环语句
while … do .. end while
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
delimiter //
create procedure while_pro(in x int,out result int)
begin
-- 定义一个局部变量 int i = 1
declare i int default 1;
-- 保存最终的总和
declare sums int default 0;
-- 循环
while i<=x do
-- sums自增1
set sums = sums + i;
-- i应该要自增1
set i = i + 1;
end while;
-- select sums;
set result = sums;
end //
delimiter ;
2
select @result;loop .. end loop - 类似于java中的while(true)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop procedure loop_pro;
delimiter //
create procedure loop_pro(in x int)
begin
declare i int default 1;
declare sums int default 0;
success:loop
if i>x then
-- 打破循环的语句
-- iterate success; 类似于continue
-- 类似于break
leave success;
end if;
set sums = sums + i;
set i = i + 1;
end loop;
select sums;
end //
delimiter ;
repeat … until … end repeat
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
create procedure repeat_pro(in x int)
begin
-- 但是先进入到这个循环体中先执行一遍,然后再进行判断
repeat
set x = x + 1;
select x;
-- 如果x>0,循环停止了
until x>0
end repeat;
end //
delimiter ;
存储过程和函数的区别
- 定义函数的时候,在函数的签名上必须要指定returns返回类型,定义存储过程的时候不需要使用returns来指定返回类型
- 函数体中必须要有return语句来返回函数的执行结果,但是存储过程中可以没有return语句
- 调用函数使用select,调用存储过程使用call
- 存储过程更加侧重于sql的封装以及sql的预编译,提高效率和安全和sql的复用性
- 存储过程必须要使用in来接受参数,使用out来返回结果
触发器
在myql中,当我们执行一些操作的时候,比如DML操作(触发器触发的事件),一旦事件被触发,那么
就会执行一段程序.触发器本质上就是一个特殊的存储过程.
分类
- after触发器 - 在触发条件之后执行
- before触发器 - 在触发条件之前执行
语法
1 | -- 删除触发器 |
练习
1 | create table t_acc_copy as select * from t_acc where 1=2; |
删除t_acc表中的任意一条数据之后,会在t_acc_copy表中插入一条.
2
3
4
5
6
7
8
9
delimiter //
create trigger acc_tri
after delete on t_acc
for each row
begin
insert into t_acc_copy values(old.id,old.balance);
end //
delimiter ;
2
3
4
5
6
7
8
9
10
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_acc_copy;
+------+---------+
| id | balance |
+------+---------+
| 1 | 1000.00 |
+------+---------+
1 row in set (0.00 sec)级联删除 - 删除t_customer中的客户信息之前需要级联删除该客户的订单信息
2
3
4
5
6
7
8
9
10
11
delimiter //
create trigger cus_tri
before delete on t_customer
for each row
begin
delete from t_ord where customer_id=old.id;
end //
delimiter ;
delete from t_customer where id=2;
oracle中支持自检约束check
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table cks(
id int(7) primary key auto_increment,
age int(7)
);
insert into cks values(1,30);
insert into cks values(2,30);
auto_increment 主键自增长的策略,默认值是从1开始,步长为1. 主键列不需要设置值
insert into cks(age) values(20);
insert into cks(age) values(30);
delete from cks where id=2;
insert into cks(age) values(40);
select * from cks;
-- auto_increment - 主键列数据类型不能是varchar
-- auto_increment到达最大值
mysql用触发器来实现自检约束
2
3
4
5
6
7
8
9
10
11
12
13
14
drop trigger cks_tri;
delimiter //
create trigger cks_tri
before insert on cks
for each row
begin
if new.age<0 or new.age>18 then
signal sqlstate '42000' set message_text='age必须在0~18区间';
end if;
end //
delimiter ;
insert into cks(age) values(200);
B+树
索引底层原理
解释底层的索引的数据结构 - b+树
B+树
InnoDB 存储引擎中的 B+ 树索引。要介绍 B+ 树索引，就不得不提二叉查找树，
平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们仨演化来的。
索引文件和数据文件 - innodb中 - 合二为一的 - 只有1个文件
索引文件和数据文件 - myisam中 - 分开独立的 - 俩个文件
二叉树
节点(每个圆圈圈)中存储了键（key - 主键索引列）和数据（data - 每一个行记录）。键对应 user 表中的 id，数据对应 user 表中的行数据。
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
如果我们需要查找 id=12 的用户信息，利用我们创建的二叉查找树索引，查找流程如下：
id=12先和根节点[只有一个]key=10,发现id=12>id=10 - 顺利向着根节点的右边去匹配
id=12和非叶节点id=13的进行匹配,顺利执行id=13的左边
id=12和id=12比较 - 两者是相同的.由于每个节点除了保存key还保存了value[行记录 - 行真实的行数据]
直接将这个节点的value直接取出来了.
总结 - 总共匹配了3次就可以顺利找到我们的数据.
如果没有创建二叉树索引.查找id=12,必然会进行全表扫描.从表的第一行向下找.最好的状态也得找6次
平衡二叉树
二叉树带来的弊端
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值
二叉树在极端的场景下有可能成为一个链表的结构[链表的查询效率很低很低的.]
查找id=12,”链表结构”,只能从链表的头节点开始查找,最佳状态也得寻找找了5次.
AVL
为了解决这个问题[防止二叉树变成了链表结构导致查询效率依然低下]，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树.
平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1。
下面是平衡二叉树和非平衡二叉树的对比：
只要找到任何一个节点的左右子树高度差的绝对值大于1 - 非平衡二叉树
1 | 节点45 - 左子树高度 = 左边的子节点的个数 = 2 |
B树
平衡二叉树暴露出来了一些缺点:
每个节点仅仅保存一个key-value键值对[每个节点可保存的键值对数据太少了].每次进行查询的时候,实际上都是需要从磁盘中读取数据的.
那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块
由于每个节点可保存的数据不多,仅仅保存了一个key-value.在查找数据的过程中,它就不断去和磁盘进行IO交互.
导致平衡二叉树的节点比较多.也就导致了平衡二叉树的高度比较高 - 导致比较的次数比较多 - 频繁和IO进行交互 - 查询效率低下.
为了解决平衡二叉树的高度太高问题.B树登场了.
B树特点
根节点[第一页] - 永驻内存.
每个节点可以保存多个key-value - 导致子节点也会增多.B树又矮又胖.
没有子节点的节点 - 叶节点,有子节点的节点 - 非叶节点
B树的m阶 - m值就是看它最大的子节点的个数 - 3 , 下面的图代表的就是3阶b树.
如果有10亿条数据,只需要和磁盘进行交互2次.把磁盘块中的一页数据[16kb]全部加载到内存中.
页page的概念 - 那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块
读取的单位是 - 页 - 1页的磁盘块的数据大小是16kb,每个节点可以更多的key-value
页与也之间是一个链表的结构
查找id=28的数据 - 磁盘交互了3次
①id=28到第一页中进行匹配,发现id=28在17和35之间,获取p2指针.p2指向到页3
②定位到页3,发现id=28在26~30之间,继续拿到p2指针,p2指向的是页8
③定位到页8,顺利匹配查找到id=28这条数据
B+树
是Innodb和myisam存储引擎中索引底层的数据结构 - B+树
B树中每个节点中不仅仅存储key[索引列值,主键列值],还存储了数据.因为数据库中的页的大小是固定的[Innodb默认是16kb],
导致每个节点的存储资源有点浪费了.
B+树和B树的重要区别就是
B+树中非叶节点,仅仅保存了key值[索引列,主键列值],没有保存数据.每个非叶节点可以保存更多的key
B+树中索引的所有的数据都放在了叶子节点中,而且是按照顺序排列的.
- **页与页之间是双向链表结构,**叶节点中的每个数据节点单向链表
- 下面这个图展示的是Innodb中的索引的结构.并不是Myisam中索引的结构
- 以下图示本质上就是聚簇索引[主键列索引]的方式 - key - 主键列
聚簇索引和非聚簇索引
在上节介绍 B+ 树索引的时候，我们提到了图中的索引其实是聚集索引的实现方式。
那什么是聚集索引呢？在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。
这里我们着重介绍 InnoDB 中的聚集索引和非聚集索引：
聚集索引（聚簇索引）：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。
这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。
这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。
非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。
非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
聚簇索引存储和查找
- 先到非叶节点找到索引列所在的页位置
- 根据页位置定位到叶节点的位置
- 叶节点中根据索引列的值找出数据
1 | select * from xx where id>=18 and id<41; |
非聚簇索引存储和查找
B+树的结构
表结构:id age name
id - 主键列 - 默认是聚簇索引列 - 主键列
name - 非聚簇索引列 - 索引列 - 辅助索引
非聚簇索引 - 非主键列索引 - name列创建了索引 - 辅助索引.
结构:
根节点 - 一页数据 - 非聚簇索引列值 - name
非叶节点 - 非聚簇索引列值
页节点存储的东西 - name索引列以及该列对应的主键列值. - 这是和聚簇索引最大的一个不同点
它和聚簇索引最大的区别是页节点中没有存储最终的数据.而是存储的是键值对x-y
x就是非聚簇索引列值,y是对应的主键列值.
非聚簇索引的查找方式:
按照B+树的查找流程 - 确认name=’Bob’的具体位置
由于非聚簇索引的结构中叶节点仅仅保存了name-主键列值
先根据name=’Bob’这个条件找到对应的主键列值id=15
要进行”回表操作”
继续拿着主键列id=15到索引的结构中继续查找一次 - “一次回表查找”.
id也是聚簇索引 - B+树的结构 - 叶子节点中存储的就是数据.
根据聚簇索引列的查找方式 - id=15的叶节点 - 拿到里面的数据
非聚簇索引列查找一定会回表?????
未必 - 因为非叶节点中存储的就是索引列值.
查询
select id from xxx where name=’Bob’;
select name from xxx where name=’Bob’;不需要回表了.这条语句查询的结果name已经在非聚簇索引的非叶节点中保存了.
回表
根据一个非聚簇索引列查找 - 优先先到非聚簇索引的B+树中找到该列对应的主键列值[聚簇索引列值]
再拿着这个聚簇索引列的值再去到聚簇索引列的B+树中再查找一次
myisam中的索引特点
索引的本质就是一个键值对key-value
key - 索引列值,value - 数据行的物理地址.
主键列索引/辅助索引 -> 两颗独立的B+树,都是索引列值对应的行记录的物理地址.
innodb中索引和数据合并到一个文件中
myisam中索引和数据是单独的俩个文件,分别是索引文件和数据文件.
myisam中采用的是”非聚集的方式”
无论是聚簇索引还是非聚簇索引,查找方式是一样.
采用的也是B+树的结构**.只是叶节点中存储的是索引的列值以及该对应的行记录的地址.**
需要再根据行记录地址到表中进行定位[回表]
2
3
4
5
6
非主键列 - key是允许重复的.
select * from xxx where id=5;
1. 先到B+树找到找到id=5对应的节点 - 取出里面的行记录的物理地址0x6a
2. 回表 - 直接根据行记录的物理地址直接定位到具体的一行.
sql习题1
现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：
- 商品product（商品号productid int(7)]，
商品名productname [varchar(128)]，
单价unitprice int(11,2)，
商品类别category[varchar(28)]，
供应商provider [varchar(48)]）；
主键：productid- 顾客customer（顾客号customerid int(7)]，
姓名name[varchar(48)]，
住址location[varchar(128)]）；
主键：customerid- 购买purcase（顾客号customerid，商品号productid，
购买数量quantity[int(7)]）；
外键：customerid 引用 客户表的 customerid
外键：productid 引用 产品表的 productid
主键：(customerid, productid) –> 联合主键
任务一:使用DDL语言创建上面的表,并且设置必要的主键约束和外键约束
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
create table product(
productid int(7) primary key,
productname varchar(128),
unitprice double(11,2),
catagory varchar(28),
provider varchar(48)
);
drop table customer;
create table customer(
customerid int(7) primary key,
name varchar(48),
location varchar(128)
);
drop table purcase;
create table purcase(
customerid int(7),
productid int(7),
quantity int(7),
constraint purcase_id_pk primary key(customerid,productid),
constraint purcase_id1_fk foreign key(productid) references product(productid),
constraint purcase_id2_fk foreign key(customerid) references customer(customerid)
);
insert into product values(1,'佳洁士',8.00,'牙膏','宝洁'),(2,'高露洁',6.50,'牙膏','高露洁'),
(3,'洁诺',5.00,'牙膏','联合利华'),
(4,'舒肤佳',3.00,'香皂','宝洁'),
(5,'夏士莲',5.00,'香皂','联合利华'),
(6,'雕牌',2.50,'洗衣粉','纳爱斯'),
(7,'中华',3.50,'牙膏','联合利华'),
(8,'汰渍',3.00,'洗衣粉','宝洁'),
(9,'碧浪',4.00,'洗衣粉','宝洁');
insert into customer values(1,'Dennis','黄浦区'),
(2,'John','徐家汇'),
(3,'Tom','闸北'),
(4,'Jenny','静安'),
(5,'Rick','浦东');
insert into purcase values(1,1,3),
(1,5,2),
(1,8,2) ,
(2,2,5),
(2,6,4) ,
(3,1,1),
(3,5,1 ),
(3,6,3),
(3,8,1 ),
(4,3,7),
(4,4,3 ),
(5,6,2),
(5,7,8);
任务二: 向对应的表中插入如下数据
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2，高露洁，6.50，牙膏，高露洁；
3，洁诺，5.00，牙膏，联合利华；
4，舒肤佳，3.00，香皂，宝洁；
5，夏士莲，5.00，香皂，联合利华；
6，雕牌，2.50，洗衣粉，纳爱斯
7，中华，3.50，牙膏，联合利华；
8，汰渍，3.00，洗衣粉，宝洁；
9，碧浪，4.00，洗衣粉，宝洁；）
顾客（1，Dennis，黄浦区；
2，John，徐家汇；
3，Tom，闸北；
4，Jenny，静安；
5，Rick，浦东；）
购买(1，1，3；
1，5，2；
1，8，2；
2，2，5；
2，6，4；
3，1，1；
3，5，1；
3，6，3；
3，8，1；
4，3，7；
4，4，3；
5，6，2；
5，7，8；）
任务三: 完成如下语句
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select distinct c.customerid,c.name from customer c join purcase p on p.customerid=c.customerid where p.productid in (select pr.productid from product pr where pr.provider='宝洁');
+------------+--------+
| customerid | name |
+------------+--------+
| 1 | Dennis |
| 3 | Tom |
| 4 | Jenny |
+------------+--------+
（2）求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）
select p2.customerid from purcase p2 where p2.productid in (select p.productid from customer c join purcase p on c.customerid=p.customerid where c.name='Dennis') and customerid<>(select distinct p4.customerid from customer c4 join purcase p4 on p4.customerid=c4.customerid where c4.name="Dennis") group by p2.customerid having count(*)>=(select count(*) from customer c2 join purcase p3 on c2.customerid=p3.customerid where c2.name='Dennis');
+------------+
| customerid |
+------------+
| 3 |
+------------+
（3）求牙膏卖出数量最多的供应商。
select pr.provider,sum(pu1.quantity) from product pr join purcase pu1 on pu1.productid=pr.productid and pr.catagory='牙膏' GROUP BY pr.provider order by sum(pu1.quantity) desc limit 1;
+--------------+-------------------+
| provider | sum(pu1.quantity) |
+--------------+-------------------+
| 联合利华 | 15 |
+--------------+-------------------+
(4)将所有的牙膏商品单价增加10%。
update product set unitprice=unitprice*1.1 where catagory='牙膏';
(5)删除从未被购买的商品记录。
delete from product where productid not in (select pu.productid from purcase pu);