数据库相关知识

最新推荐文章于 2023-12-28 19:27:00 发布

工具兵

最新推荐文章于 2023-12-28 19:27:00 发布

阅读量235

点赞数

分类专栏：面试文章标签：数据库 mysql sql oracle java

本文链接：https://blog.csdn.net/qq_43541242/article/details/107960532

版权

面试专栏收录该内容

17 篇文章 0 订阅

订阅专栏

1.事务

1.1什么是事务？

为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。比如“转帐”

1.2事务的特点

a. 原子性：事务所涉及的一系列操作要么全部成功，要么全部失败，不允许有中间状态（部分成功或部分失败）。

b. 一致性：事务结束之后，不允许有非法数据进入数据库。

注：一致性由原子性、隔离性和持久性来保证。

c. 隔离性：多个事务可以同时执行，并且能保证一定程度上互不影响。

d.持久性：事务结束之后，数据必须要保存到数据库（即写入文件）。

注：即使发生数据库崩溃、断电等极端情况，数据库系统仍然保证数据会被写入数据库。

1.3 隔离级别

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

在这里插入图片描述

b.读已提交：一个事务只能读取到另外一个事务已经提交的数据，该隔离级别解决了“脏读”问题，但是仍然有可能产生“不可重复读取”和“幻影读取”问题。

c. 可重复读取：在同一个事务当中，两次读取同一份数据，结果一样。该隔离级别解决了“不可重复读取”问题，但是仍然有可能产生“幻影读取”问题。

d.序列化：多个事务需要一个一个执行，不允许并发执行。该隔离级别解决了“脏读”、“不可重复读取”和“幻影读取”问题。

结论：隔离级别按照从低到高，依次是"读未提交"、“读已提交”、“可重复读取”和“序列化”。隔离级别越高，性能越低，可靠性就越高，所以在实际应用当中，要按照实际业务的需要来选择合适的隔离级别。

注：mysql数据库默认的隔离级别是“可重复读取”，oracle数据库默认的隔离级别是“读已提交”。

mysql数据库事务相关的三个命令：
开始事务：begin
提交事务：commit
回滚事务：rollback

2.视图

2.1什么是视图？

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

注：数据库只保存有视图的定义，不会存放任何表中的数据。

2.2如何创建视图?

create view 视图名 as select 或者

create view 视图名(字段列表) as select

注：对视图进行插入/删除/修改操作，也会影响到原有的表。但是

仅限于单表，对于一些复杂的视图（比如join查询、分组、子查询）则进行这些操作会失败。也就是说，视图一般用于查询。

2.3 视图的优点

a.简化开发：可以将一些复杂的查询定义成视图，这样开发人员只需要查询视图即可。

b.易于维护：当基表（视图基于哪些表来创建的）的结构发生一些变化，不会影响到视图。

c. 安全：只将部分数据暴露给开发人员。

2.4 删除视图

drop view 视图名

参考sql:

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

create view v_employee as select * from t_employee;

create view v_employee2(name,age) as select name,age from t_employee;

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

create table t_staff(
 id int primary key,
 name varchar(50),
 age int,
 dept_id int
);
insert into t_staff values(1,'小白',22,100);
insert into t_staff values(2,'Tom',32,100);
insert into t_staff values(3,'张三',26,200);

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

insert into v_employee2 values('Jerry',33);

3.约束

3.1 什么是约束？

约束是一种限制，它通过对表的行或者列的数据作出限制，来确保数据的完整性和一致性。

3.2有哪些约束?

主键、外键、非空、唯一和检查约束。

a.主键约束：相当于唯一约束 + 非空约束。一张表只能建一个主键约束，数据库会在主键所在的列上面创建主键索引。主键也可以在多个列上来创建（即主键可以包含多个列）。

b.外键约束：用于保证两个表之间的参照完整性，即一张表中外键的值来自于另外一张表的主键。

注：外键所在的表称之为从表，主键所在的表称之为主表。

c.非空约束：用于确保当前列的值不为空值。

d.唯一性约束：列或者列的组合不能重复，保证数据的唯一性。

e.检查约束:

注：mysql8.0.16(mariadb 10.2)之后才能使用

参考sql:

--主表
create table t_class(
 cno int primary key,
 cname varchar(50)
);

--插入记录时，要先插入主表中的记录
insert into t_class values(100,'jsd2003');
insert into t_class values(200,'jsd2004');

delete from t_class where cno=200;

--从表
create table t_student(
 sno int primary key,
 sname varchar(50),
 cno int,
 foreign key(cno) references t_class(cno)
);

insert into t_student values(1,'Sally',100);
insert into t_student values(2,'King',200);

--删除时，要先删除从表中的记录
delete from t_student where sno=2;

--检查约束（对数据库版本有要求）
create table t_demo(
	id int primary key,
	name varchar(50),
	salary int check(salary >0 and salary < 80000)
);

4.存储过程

4.1存储过程是什么?

是一组存储在关系数据库中为了完成特定功能的sql语句。

注：一般用在业务比较复杂，需要进行一些数据密集性操作的场合。

4.2 如何创建存储过程?

create procedure 存储过程名 ([IN/OUT/INOUT] 参数名数据类型)

注：

IN: 输入参数（默认），表示该参数的值必须在调用存储过程时指定。

OUT:输出参数，表示该参数的值可以在存储过程内部被改变，并且可以返回。

INOUT:输入输出参数，相当于IN和OUT的结合。

参考sql

-- 结束符号由";"改成了"//"，因为在控制台，";"意味着语句结束，这样
-- 存储过程就不完整了。

--不带参的存储过程
delimiter //  
create procedure proc_find() 
begin
  select * from t_employee;	
end
//
delimiter ;

--带有IN参数的存储过程 
delimiter //
create procedure proc_find2(in eid int)
begin
   select * from t_employee where id = eid;
end
//
delimiter ;

call proc_find2(1);

--带有OUT参数的存储过程
delimiter //
create procedure proc_find3(out max_sal int)
begin
  select max(salary) into max_sal from t_employee;
end
//
delimiter ;

call proc_find3(@sal)
select @sal;
注：@sal是会话变量（只在当前终端中有效，如果打开另外一个终端，该变量就会失效）。

4.3 使用 jdbc调用存储过程

/**
 * 演示如何调用存储过程
 */
public class CallProcDemo {
    /**
     * 调用不带参的存储过程
     */
    public static void demo1(){
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            //调用存储过程必须用CallableStatement
            CallableStatement cs =
                    conn.prepareCall("{call proc_find}");
            ResultSet rs = cs.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("name"));
                System.out.println(rs.getInt("salary"));
                System.out.println(rs.getInt("age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }

    }

    public static void main(String[] args) {
        demo1();
    }
}

练习：写一个存储过程，将薪水前2的员工找出来。

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

call proc_find4;

5.索引

1.1什么是索引？

索引是对数据库中的一列或者多列的值进行排序的一种数据结构，使用索引可以快速地访问数据库中特定信息。

索引类似于一本书的目录。

1.2如何创建索引？

create index 索引名 on 表名(列名)；

1.3如何创建联合索引？

create index 索引名 on 表名(列名1，列名2…)；

注：使用联合索引要注意“最左匹配原则”。

最左匹配原则指的是联合索引中的字段，只有某个字段左边的所有字段都被使用了，才能使用该字段上的索引。比如：

有一个联合索引包含了三个字段(a,b,c),如果查询条件是:

where a=… and b=… and c=… 会用到索引

where a= … and b=… 会用到索引

where a= …会用到索引

where a=… and c=… 不会用到索引

where b=… and c=… 不会用到索引

where c=… 不会用到索引

1.4 索引的原理

1.4.1 不加索引为什么查询速度慢？

1.4.2 B + 树 B树

mysql数据库innodb引擎默认会使用B + 树作为索引的数据结构，其它大部分关系数据库也是使用该数据结构。

在这里插入图片描述

B树和B+树的最大的区别是：B树的非叶子节点（也就是索引）除了存放索引字段的指针以外，还会存放完整的记录。使用B树不如使用B+树，因为B树的索引扇出比B+树的少，也就是：对于相同数量的记录，B树更高，需要进行更多次数的I/O操作。

1.5 索引的使用原则

a.要将经常作为查询条件、分组、过滤、联合查询的字段加上相应的索引。

b.要在连接字段上加索引。

c.索引字段不要参与计算(包括调用一些函数)，否则索引不会用到。

d.使用is null,is not null(只要索引中出现一个null,则索引失效)，所以在建索引的进修，一定要将准备建立索引的字段上加上not null约束。

e.如果一个字段的值特别少，比如性别，不要建索引。

f.要尽量选择具有唯一性特点的字段加索引。

g.使用不等于“<>,!="时一定会进行全表扫描（不会用到索引）。

h. like语句如果使用”%“开头，则不会使用到索引。

i.索引不是越多越好（索引会占用硬盘空间），一张表一般建议不要超过6个。

j.数据量小的表不要使用索引（使用全表扫描更快）。

k.在大批量插入记录时，可以先删除索引，完成插入之后再重建索引。

1.6 索引的优缺点

优点：极大的提高查询速度。
缺点：索引要占用硬盘空间；会影响插入、删除、修改记录的速度（需要重建索引）；

参考sql:

-- 创建索引
create index idx_id on t_user(id);

-- 查看表中有哪些索引
show index from t_user;

-- 查看当前查询有没有用到索引
explain select * from t_user where name='user999';

create index idx_name on t_user(name);

-- 删除索引
drop index idx_id on t_user;
drop index idx_name on t_user;

--创建复合索引
create index idx_id_name on t_user(id,name);

工具兵

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库相关知识

数据库相关知识1.事务2.视图3.约束4.存储过程5.索引1.事务1.1什么是事务？为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。比如“转帐”1.2事务的特点 a. 原子性：事务所涉及的一系列操作要么全部成功，要么全部失败，不允许有中间状态（部分成功或部分失败）。 b. 一致性：事务结束之后，不允许有非法数据进入数据库。注：一致性由原子性、隔离性和持久性来保证。 c. 隔离性：多个事务可以同时执行，并且能保证一定程度上互不影响。 d
复制链接

扫一扫