数据库高级知识点汇总

标签: 数据库  mysql  java  索引  mysql jdbc dirver

数据库

1.事务

​ (1)什么是事务?

​ 为了完成某个业务而对数据库进行一系列操作,这些操作要么全部成功,要么全部失败。

​ (2)事务的四个特性

  • 原子性:事务包含的这一系列操作,要么全部成功,要么全部失败。
  • 一致性:事务完成之后,不会将非法的数据写入数据库。
  • 隔离性:多个事务可以在一定程度上并发执行。
  • 持久性:事务完成之后,数据要永久保存(一般会保存在硬盘上)。

​ (3)隔离级别

  • 读未提交:一个事务可以读取到另外一个事务尚未提交的数据。该隔离级别可能会产生“脏读”、“不可重复读取”和“幻影读取”问题。

  • 读已提交:一个事务只能读取到另外一个事务已经提交的数据。该隔离级别解决了“脏读”问题,但是仍然可能会发生“不可重复读取”和“幻影读取”问题。
    在这里插入图片描述

  • 可重复读取:在同一个事务当中,多次读取同一份数据,结果一样。该隔离级别解决了“脏读”和“不可重复读取”问题,但是仍然有可能会产生“幻影读取问题”(虚读)。

  • 序列化:多个同务只能排队执行,即只有一个事务结束之后,另外一个事务才能开始执行。该隔离级别解决了“脏读”,“不可重复读取”和“幻影读取”问题,但是程序性能会下降。所以只有必要的时候(比如在银行系统里面)才会使用。

    总结:

    ​ 隔离级别从低到高依次是"读未提交"、“读已提交”、“可重复读取”和“序列化”,隔离级别越高,性能越低。mysql数据库默认隔离级别是“可重复读取”,oracle是“读已提交”。数据库底层使用的“加锁”的机制来实现不同的隔离级别,包括对整个表加锁,对表中的行加锁。

    ​ mysql数据库开始事务、提交事务、回滚事务

    begin;
    commit;
    rollback;
    

    ​ mysql数据库必须将数据库引擎设置为"innodb"才能支持事务。

2.视图

​ (1)什么是视图?

​ 在已有的表或者视图上创建的虚拟表。

​ (2)创建视图

​ create view 视图名 as select

注:可以对单表或者多表进行查询,数据库会将视图的定义保存下来。

可以对(单表)视图进行一些增删改查操作,这些操作会影响到原始的表。

(3)删除视图

​ drop view 视图名

参考sql:

create  table t_emp(
	id int primary key auto_increment,
	name varchar(50),
	salary int,
	age int
);

create view v_emp as select * from t_emp;
create view v_emp2(name,salary) as select name,salary from t_emp;

insert into v_emp2 values('Jhon',3000);

create table t_dept(
	id int primary key,
	name varchar(50),
	addr varchar(100)
);
insert into t_dept values(100,'财务部','北京');
insert into t_dept values(200,'开发部','上海');

create table t_staff(
	id int primary key auto_increment,
	name varchar(30),
	age int,
	dept_id int
);
insert into t_staff values(null,'张三',33,100);
insert into t_staff values(null,'李四',23,100);
insert into t_staff values(null,'王五',43,200);

create view v_staff_dept(sname,dname,addr) 
as 
select s.name sname,d.name dname,d.addr from t_staff s 
join t_dept d on s.dept_id = d.id;

drop view v_emp;

3.索引

(1)什么是索引?

​ 为了提高查询的速度而在数据库端创建的一种排序的数据结构。

​ 注:索引类似于一本书的目录

(2)如何创建索引?

​ create index 索引名 on 表名(字段列表)

(3)如何查看当前查询是否用到的索引?

(4) 在哪些字段上加索引?

​ 应该将经常作为查询条件的字段加索引,除此以外,还要在分组、过滤、排序及联合查询的字段上加索引。

(5)如何删除索引?

​ drop index 索引名 on 表名

(6)联合索引

​ 所谓联合索引(复合索引),指的是索引字段是多个,比如:

​

​ 使用联合索引时,要注意“最左匹配原则”。即在使用联合索引的某个字段作为查询条件时,该字段左边的所有字段也要同时作为查询条件,比如:

​ 联合索引包含了三个字段(c1,c2,c3),则:

​ 将c1,c2,c3同时作为查询条件时,会用到索引;

​ 将c1,c2同时作为查询条件时,会用到索引;

​ 将c1作为查询条件时,会用到索引;

​ 将c2作为查询条件时,不会用到索引;

​ 将c3作为查询条件时,不会用到索引;

​ 将c1和c3作为查询条件时,不会用到索引。

​
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

(7) 索引原理

 假设t_user表有15条记录,每个磁盘块只能放4条记录,则需要4个磁盘块,对应的"B+"

树结构如下:

在这里插入图片描述
在这里插入图片描述

(8)主键索引和非主键索引。

​ 数据库自动会为主键字段添加上相应的索引,该索引称之为主键索引。

比如,有一张表(id是主键,除此之外还有name,email,age等字段),则数据库会为id字段加上主键索引,如果以id作为条件,显然会用到主键索引,但是如果是以name等非主键字段作为查询条件,则不会用到主键索引,所以为了提高查找的速度,可以在非主键字段上加上相应的索引。

​ 主键索引和非主键索引有什么区别?

​ 非主键索引叶子节点存放的是索引字段及主键值,而主键索引叶子节点存放的是完整的记录。使用主键索引查找要比使用非主键索引一般要快一些,因为使用非主键索引,很多时候需要“回表”。

​  什么是索引覆盖?

​  查找的字段都包含在了索引字段里面,此时,不需要进行“回表”操作,查找的速度会非常快。

面试相关:

​ 为什么数据库不使用B树而是使用B+树?

​ 因为B树索引块除了可以存放索引字段及指针以外,还可以存放记录,也就是说,B树的索引的扇出变少(扇出指的是索引块当中指针的个数),相应的由索引块构成的树的高度增加,也就是说,需要进行更多的“i/o”操作。
在这里插入图片描述
 为什么数据库很少使用hash作为索引的数据结构?

​ hash索引虽然依据索引字段定位记录很快(比B+树还要快),但是不能进行范围查询。

(9)使用索引需要注意的问题:

​ a. 使用索引之后,虽然加快了查询的速度,但是在进行添加、删除、修改操作时会变慢,因为需要重建索引。在批量添加记录时,建议先临时删除索引,在批量添加成功之后再加上相应的索引。
在这里插入图片描述

​ b.不要建过多的索引。

​ 注:索引会占用硬盘空间,一般建议不要超过6个。

​ c.小表不要建索引。

​ 注:小表使用全表扫描更快。

​ d.索引字段不要参与计算。

​ 比如 “select * from t_user where id-1 = 100” 不会用到索引。

​ 注:包括使用函数都不行。
在这里插入图片描述

​ e. 尽量在同值少的字段上建索引。

​ 注: 比如性别就不适合作索引。

4.存储过程(了解)

​ (1)什么是存储过程?

​ 存储在数据库端的一组为了完成特定功能的sql语句。
在这里插入图片描述
在这里插入图片描述

​ (2)如何创建存储过程?

​ create procedure 存储过程名([参数])

​ 参数格式 (参数类型 参数名 数据类型)

​ 参数类型有三种:

​ IN: 输入参数,该参数的值必须在调用该存储过程时指定,在存储过程内部使用, 不能返回。

​ 缺省值是IN。

​ OUT:输出参数,该参数值的值可以在存储过程内部修改,并可返回。

​ INOUT:输入输出参数,该参数需要在调用时指定,并且可以返回。

delimiter //
create procedure proc_find1()
begin
	select * from t_emp;
end
//
delimiter ;

注:
	delimiter // 这句的作用是将结束符号设置为"//"

call proc_find1;

delimiter //
create procedure proc_find2(eid int)
begin
	select * from t_emp where id = eid;
end
//
delimiter ;

call proc_find2(1);

delimiter //
create procedure proc_find3(out max_sal int)
begin
	select max(salary) into max_sal from t_emp;
end
//
delimiter ;

call proc_find3(@sal);
select @sal

​注意:小海豚SQLyog中对delimiter的识别略有不同,修改结尾符时两个都要写。
在这里插入图片描述
在这里插入图片描述

(3)使用jdbc调用存储过程

public class CallProcdure {
	
	/**
	 * 调用不带参的存储过程
	 */
	public static void test1() {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			CallableStatement cs = 
				conn.prepareCall("{call proc_find1}");	
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int salary = rs.getInt("salary");
				int age = rs.getInt("age");
				System.out.println("id:" + id + " name:" 
				+ name + " salary:" + salary + " age:" + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		
		
	}
	
	/**
	 * 调用带输入参数的存储过程
	 */
	public static void test2() {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			CallableStatement cs = 
				conn.prepareCall("{call proc_find2(?)}");
			cs.setInt(1, 1);
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int salary = rs.getInt("salary");
				int age = rs.getInt("age");
				System.out.println("id:" + id + " name:" 
				+ name + " salary:" + salary + " age:" + age);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		
	}
	
	/**
	 * 调用带输出参数的存储过程
	 */
	public static void test3() {
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			CallableStatement cs = 
				conn.prepareCall("{call proc_find3(?)}");
			//输出参数赋值不能用setxxx
			cs.registerOutParameter(1, Types.INTEGER);
			cs.execute();
			int maxSal = cs.getInt(1);
			System.out.println("maxSal:" + maxSal);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		
	}

5.约束

​ (1)什么约束?

​ 是一种限制,通过对表的行或者列的数据做出限制来确保数据的完整性和一致性。

​ (2)有哪些约束?

​ 主键:相当于唯一性约束 + 非空约束的组合。

​ 注:一张表只能一个主键,数据库会为主键添加主键索引

​ 外键:用于确保两个表之间的参照完整性。

create table t_cls(
	cno int primary key,
	cname varchar(50)
);

insert into t_cls values(100,'jsd2002');
insert into t_cls values(200,'jsd2003');

create table t_stu(
	sno int primary key auto_increment,
	sname varchar(50),
	cno int,
	foreign key(cno) references t_cls(cno)
);
insert into t_stu values(null,'Sally',100);

外键所在的表称之为从表,从表参照的表称之为主表,
这儿,参照的含义是指从表中外键的值来自于主表的主键。

插入记录时,要先插入主表中的记录。
删除记录时,要先删除从表中的记录。

​ 非空: not null

​ 唯一性:unique

​ 检查(了解):

​ 注:检查约束跟数据库版本有关系,mysql8.0.16之后才支持。

create table t_check(
	id int primary key,
	name varchar(50),
	salary int check(salary > 0 and salary < 20000)
);

练习:写一个存储过程,将薪水前2的员工信息找出来。

并且使用jdbc来调用该存储过程。

delimiter //
create procedure proc_find4()
begin
	select * from t_emp order by salary desc limit 2;
end
//
delimiter ;

SQL执行过程:

1. FROM :  对FROM的左表和右边计算笛卡尔积,生成虚表v1(临时表)。

2. ON:  对虚表v1进行ON筛选,只有符合join条件的行被保留到虚表v2。 
3. WHERE: 对虚表v2进行where条件的过滤,生成虚表v3。
4. GROUP BY: 依据group by子句中的列,对虚表v3进行分组操作,生成虚表v4。
5. HAVING: 对虚表v4使用having进行过滤,生成虚表v5。
6. SELECT :使用select选择需要的列,生成虚表v6。
7. DISTINCT:对虚表v6中的记录进行去重,生成虚表v7。
8. ORDER BY: 对虚表v7中的记录进行排序,生成虚表v8。
9. LIMIT: 从虚表v8中取出指定条数的记录。

在这里插入图片描述

6. Case表达式(未完待续…)

“新手用where 句子进行条件分支,高手用select 句子进行条件分支"
“新手用having 句子进行过滤,高手用select 句子进行过滤"

Case的最强大功能在于,自定义条件分组显示
三张表:
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

及不及格

在这里插入图片描述
在这里插入图片描述

是男是女

在这里插入图片描述

在这里插入图片描述

分分数段显示

在这里插入图片描述
在这里插入图片描述

版权声明:本文为EdwardWH原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/EdwardWH/article/details/107197228