数据库基础
本文参考——廖雪峰的网站
1 数据库结构
- 层次模型
- 网状模型
- 关系模型,将数据看作一个二维表格,理解使用简单
2 SQL数据库语言
SQL(Structured Query Language)是结构化查询语言的缩写,用来访问和操作数据库系统.
通过学习SQL这一种语言,就可以操作各种不同的数据库。
SQL语言定义了这么几种操作数据库的能力:
- DDL:Data Definition LanguageDDL允许用户定义数据,也就是创建表、删除表、修改表结构这些操作。通常,DDL由数据库管理员执行。
- DML:Data Manipulation LanguageDML为用户提供添加、删除、更新数据的能力,这些是应用程序对数据库的日常操作。
- DQL:Data Query LanguageDQL允许用户查询数据,这也是通常最频繁的数据库日常操作。
语法特点:
SQL语言关键字不区分大小写!!!为避免不同数据库以及操作系统的区别,约定:SQL关键字总是大写,以示突出,表名和列名均使用小写。
3 MySQL数据库
目前应用最广泛的开源关系型数据库。MySQL本身只是一个SQL接口,其内部包含多种数据库引擎。
四种最常用的:
- InonoDB,支持事务特性
- MyISAM,不支持事务、也不支持外键,优势是访问速度快
- MEMORY,出发点是速度
- ARCHIVE,归档,很多高级功能不再支持
4 事务
事物的四大特性
- A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
- C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
- I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
- D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。
对于单条SQL语句,数据库系统自动将其作为一个事务执行,这种事务被称为隐式事务。
要手动把多条SQL语句作为一个事务执行,使用BEGIN开启一个事务,使用COMMIT提交一个事务,这种事务被称为显式事务,例如,把上述的转账操作作为一个显式事务:
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK回滚事务,整个事务会失败:
数据库事务是由数据库系统保证的,我们只需要根据业务逻辑使用它就可以。
- 数据库事务具有ACID特性,用来保证多条SQL的全部执行。
隔离级别
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oZfEZIXz-1601952918118)(en-resource://database/2266:1)]
如果没有指定隔离级别,数据库就会使用默认的隔离级别。在MySQL中,如果使用InnoDB,默认的隔离级别是Repeatable Read。
5 主键和外键
在关系数据库中,关系是通过主键和外键来维护的。
在关系数据库中,一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如,students表的两行记录:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2VlaC9Lh-1601952918120)(en-resource://database/2262:1)]
主键
同一个表的所有记录都有相同的字段定义。对于关系表,有个很重要的约束,就是任意两条记录不能重复。不能重复不是指两条记录不完全相同,而是指能够通过某个字段唯一区分出不同的记录,这个字段被称为主键。
- 作为主键最好是完全业务无关的字段,我们一般把这个字段命名为id。
非业务字段id为BIGINT NOT NULL AUTO_INCREMENT类型。
CREATE TABLE statistics (
id BIGINT NOT NULL AUTO_INCREMENT,
class_id BIGINT NOT NULL,
average DOUBLE NOT NULL,
PRIMARY KEY (id)
);
联合主键:关系数据库实际上还允许通过多个字段唯一标识记录,但尽量不使用联合主键,因为它给关系表带来了复杂度的上升。
外键
在关系数据库中,表与表之间可能存在一对一,一对多,多对一的关系。
一对多
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CtfdRmeG-1601952918122)(en-resource://database/2264:1)]
由于一个班级可以有多个学生,在关系模型中,这两个表的关系可以称为“一对多”,即一个classes的记录可以对应多个students表的记录。
为了表达这种一对多的关系,我们需要在students表中加入一列class_id,让它的值与classes表的某条记录相对应,这种列称为外键。
- 外键的实现
通过定义外键约束实现的:
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
由于外键约束会降低数据库的性能,大部分互联网应用程序为了追求速度,并不设置外键约束,而是仅靠应用程序自身来保证逻辑的正确性。这种情况下,class_id仅仅是一个普通的列,只是它起到了外键的作用而已。
多对多
多对多关系实际上是通过两个一对多关系实现的,即通过一个中间表,关联两个一对多关系,就形成了多对多关系
一对一
如果业务允许,完全可以把两个表合为一个表。
还有一些应用会把一个大表拆成两个一对一的表,目的是把经常读取和不经常读取的字段分开,以获得更高的性能。
小结
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束,也可以不设置约束,仅依靠应用程序的逻辑来保证。
6 索引
场景:
索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引,可以让数据库系统不必扫描整个表,而是直接定位到符合条件的记录。
优点:
索引主要目的是提高SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间
建索引:
ALTER TABLE students
ADD INDEX idx_score (score);
-
索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。
-
可以对一张表创建多个索引。索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。
索引的结构
- 聚集索引
- 非聚集索引
聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。
-
聚集索引一个表只能有一个(一个表只能有一个物理存储排序),而非聚集索引一个表可以存在多个。
-
索引建立时如果不指明采用的索引结构,则SQL Server系统默认为采用非聚集索引结构。
索引建立的原则
必须索引:
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
建议索引:
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。
不要索引:
7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引
8) 对于定义为text、image和bit的数据类型的列不要建立索引。
9) 对于经常存储的列避免建立索引
其他:
10) 限制表上的索引数目。
对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。
11) 对复合索引,按照字段在查询条件中出现的频度建立索引。
在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。
唯一索引
在设计关系数据表的时候,看上去唯一的列,例如身份证号、邮箱地址等,因为他们具有业务含义,因此不宜作为主键。
这些列根据业务要求具有唯一性约束时,可以给该列添加一个唯一索引。
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
小结
- 通过对数据库表创建索引,可以提高查询速度,但也增加了维护成本。
- 通过创建唯一索引,可以保证某一列的值具有唯一性。
- 数据库索引对于用户和应用程序来说都是透明的。