今天面试,数据库好多忘了,面试官还逮着问,尴尬得不行,准备好好整理一下相关基础的知识
一、表的基本构成
主键(primary key):表中某一个属性组合能唯一标识一条记录,该属性组就是一个主键
外键(foreign key):在一个表中存在的另一个表的主键为此表的外键
超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键
候选键(candidate key):不含有多余属性的超键称为候选键
增删改查就完事
类型格式
char:长度固定, 即每条数据占用等长字节空间;适合用在身份证号码、手机号码等定。
varchar:可变长度,可以设置最大长度;适合用在长度可变的属性。
text:不设置长度, 当不知道属性的最大长度时,适合用text。
查询速度: char最快, varchar次之,text最慢
选择
选择运算的记号为σ,从表中给出某些数据来过滤数据选中指定的数据;例如找出体重大于120的猫科动物
投影
投影运算的记号为Π,选中指定的数据显示出来;例如将之前选择数据中的体重、肩高数据显示出来
连接
左连接:两张表进行连接时,以左边表为基准匹配右边的表,如果左表一条数据在右表中能找到对应的数据,显示在结果表中,如果没有找到,那么会以null来代替右表中的数据去匹配左表。
select a.*,b.*
from a left join b
on a.id=b.id
右连接:左连接的过程反过来 right join
内连接:类似交集,表中一条数据在另一张表中找得到才会显示在结果表中 inner join
外连接:类似交集,与内连接是相反 hull join
自然连接:可以想象成两张一样的表进行左右连接 natural inner join
查询语句
1.按员工号查询逆序
SELECT e.emp_no, s.salary
FROM employees AS e, salaries AS s
WHERE e.emp_no = s.emp_no
ORDER BY e.emp_no DESC
2.查找出现次数
select emp_no,count(emp_no) as t
from salaries
Group by emp_no having t>15;
3.只出现一次,使用 group by或者distinct(可放在聚合函数中)去除重复值
delete
from titles_test
where id not in
(select min(id) from titles_test group by emp_no)
4.查询一个表中不包含另一个表的数据
select emp_no
from employees
where emp_no not in
(select emp_no from dept_manager )
5.更新两行语句
update titles_test
set to_date=null, from_date='2001-01-01'
where to_date='9999-01-01'
6.建立强制索引
SELECT *
FROM salaries
force index idx_emp_no
where emp_no=10005
8.SQL表行转列、列转行
select 姓名,
max(case 课程 when'语文'then分数 else 0 end)语文,
max(case 课程 when'数学'then分数 else 0 end)数学,
max(case 课程 when'物理'then分数 else 0 end)物理,
......
from tb
group by姓名
行转列
select*from tb
pivot(max(分数)for课程in(语文,数学,物理))a
列转行
select姓名,课程,分数from tb
unpivot(分数for课程in([语文],[数学],[物理]))t
一、数据库的一些功能
1.触发器
触发器是和表关联的特殊的存储过程,可以在插入,删除或修改表中的数据时触发执行
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tb_name
FOR EACH ROW trigger_stmt
trigger_name: 触发器的名称。
tirgger_time: 触发时机,为BEFORE或者AFTER。
trigger_event:触发事件,为INSERT、DELETE或者UPDATE。
tb_name: 表示建立触发器的表名,在哪张表上建立触发器。
trigger_stmt: 触发器的程序体,可以是一条SQL语句或者是用BEGIN和END包含的多条语句。
INSERT型触发器:插入某一行时激活触发器,通过 INSERT、LOAD DATA、REPLACE语句触发;
create trigger audit_log after insert on employees_test
begin
insert into audit values(new.id,new.name);
end;
UPDATE型触发器:更改某一行时激活触发器,通过UPDATE语句触发;
DELETE型触发器:删除某一行时激活触发器,通过DELETE、REPLACE语句触发。
2.存储过程
3.索引
索引的本质是把字段按照一定的方式排序,建立的索引只对该字段有用。 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。数据库索引可以提高数据库表数据访问速度,但需要占用物理空间和维护。
索引的实现通常使用B树及其变种B+树。每个节点包含了键值和键值对于的数据对象存放地址指针。
唯一索引:不允许其中任何两行具有相同索引值的索引
主键索引:该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问
聚集索引:表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引速度更快。
CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC])
);
UNIQUE: 可选。表示索引为唯一性索引。
FULLTEXT;可选。表示索引为全文索引。
SPATIAL: 可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引。
索引名: 可选。给创建的索引取一个新名称。
字段名1: 指定索引对应的字段的名称,该字段必须是前面定义好的字段。
CREATE TABLE index2(id INT UNIQUE,
name VARCHAR(20),
UNIQUE INDEX index2_id(id ASC)
);
CREATE INDEX index_name ON table_name(column_name,column_name) include(score)
三、数据库模式
数据库共有三级模式
模式:也称逻辑模式或概念模式,描述全体数据的全局逻辑结构和特征
外模式:也称用户模式,
内模式:也称存储模式,描述数据物理结构与储存方式,一个数据库中仅能有一个
四、数据库事务
数据库事务,即数据库执行的功能的过程,等同于操作系统当中的进程,为了让事务能够顺利准确的完成,需要实现四个特性
1.原子性
原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,不可能停滞在中间环节。
2.一致性
一致性指一个事务执行之前和执行之后都必须处于一致性状态,比如A向B转账,不可能A扣了钱,B却没收到。
3.隔离性
同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。
4.持久性
持久性是指一个事务对数据库中的数据的改变是永久性的,不能回滚。
事务存在的问题
1.脏读:一个事务处理过程里读取了另一个未提交的事务中的数据。
2.不可重复读:事务 A 读取一数据,事务 B 在事务A读取的过程中对数据作了更新,导致事务A多次读取数据时结果不一致。
3.虚读:事务 A 修改表数据,事务 B 在事务A修改的过程中插入一条新数据,导致事务A像是没有修改完一样。
解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
MySQL事务隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交(read-uncommitted) | 是 | 是 | 是 |
读提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
serializable:通过给每个数据行与表上锁,强行让事务的处理序列化,可以解决三大问题但也会导致大量超时与锁竞争现象,故而不常用。
repeatable-read:开始读取数据(事务开启)时,不再允许修改操作,可以解决不可重复读问题但还存在幻读现象。
read-committed:就是一个事务要等另一个事务提交后才能读取数据,只能解决脏读问题。
read-uncommitted:没用,啥也干不了。
MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。
在MySQL数据库中查看当前和设置事务的隔离级别:
select @@tx_isolation;
set [glogal | session] transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;’