数据库杂记

发表于 | 分类于

最近面试面到数据结构的题有点爆炸。基本都忘光了，记录一下基础的东西吧。

SQL语言的基本概念

数据(Data)是数据库中存储的基本对象。

数据库(DataBase，简称DB)是长期储存在计算机内、有组织的、可共享的大量数据的集合。

数据库管理系统(DBMS，Database Management System)
位于用户与操作系统之间的一层数据管理软件。
是基础软件，是一个大型复杂的软件系统。

数据库系统(DBS，DataBase System)在计算机系统中引入数据库后的系统构成，包括硬件、软件、数据和人员。

SQL语言的主要特点

1.SQL语言是一种非过程语言，即用户只要提出“干什么”即可，不必管具体操作过程，也不必了解数据的存取路径，只要指明所需的数据即可。

2.SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。

3.SQL语言既是自含式语言，又是嵌入式语言。可独立使用交互命令，适用于终端用户、应用程序员和DBA；也可嵌入在高级语言中使用，供应用程序员开发应用程序。

SQL语言具有:
数据定义（DEFINITION）(CREATE,DROP,ALTER)，
数据查询（QUERY）(SELECT)，
数据操纵（MANIPULATION）(INSERT,UPDATE,DELETE)，
数据控制（CONTROL）(GRANT,REVOKE)。

基本表（BASE TABLE）：是独立存在的表，不是由其它的表导出的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。

视图（VIEW）：
是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。
当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。

在用户看来，视图是通过不同路径去看一个实际表，就象一个窗口一样，我们通过窗户去看外面的高楼，可以看到高楼的不同部分，而透过视图可以看到数据库中自己感兴趣的内容。

索引（INDEX）：是一种和表格或视图有关的结构，可以加快从表格或视图中获得数据行。表格索引可以是群集的，也可以是非群集的。群集指数据在物理上是基于索引关键字的顺序存放的。

关键字：是众多列中一列或组，它唯一识别一行（主关键字），定义两个表格之间的关系（外关键字），或者用于构建一个索引

存储过程：是一种Transact-SQL语句，它们被编译成一个执行计划。该过程用于性能优化和控制访问。

约束：定义了有关列中允许的值的规则，是加强数据完整性的标准机制。

触发器：存储过程的一个特殊类，用于当对一个表格或视图发出update、insert 或delete语句时，能自动执行。

三级模式结构

SQL语言支持数据库的三级模式结构，如图所示。其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件。

三级模式结构

基本表的操作

创建基本表

创建一个数据表时主要包括以下几个组成部分：

（1）字段名（列名）：字段名可长达128个字符。字段名可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号(@)。同一表中不许有重名列；

（2）字段数据类型；

（3）字段的长度、精度和小数位数；

（4）NULL值与DEFAULT值：DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。

在SQL语言中，使用语句CREATE TABLE创建数据表，其基本语法格式为：

1
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
CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] <表名>
  (     <列定义>[{，<列定义>|<表约束>}][,
        [CONSTRAINT 主键约束] PRIMARY KY （属性名）][,
        [CONSTRAINT 检查约束] CHECK（逻辑表达式）][,
        [CONSTRAINT 外键约束] REFRENCES 参照表（参照属性）[,…n]]
  )
  [ON {文件组|默认文件组}]
```    
### 定义完整性约束
在SQL SERVER中可以定义五种类型的完整性约束。
#### NULL/NOT NULL
是否允许该字段的值为NULL。表示“不知道”、“不确定”或“没有数据”的意思。
如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用
#### UNIQUE约束
用于指明基本表在某一列或多个列的组合上的取值必须唯一。

定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。
#### PRIMARY KEY约束
PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。
PRIMARY KEY与UNIQUE的区别：
1.在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；
2.对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。
#### FOREIGN KEY约束
FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表，包含外部键所引用的主键或唯一键的表称主表。
#### CHECK约束
CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。



### 修改基本表
由于应用环境和应用需求的变化，经常需要修改基本表的结构，比如，增加新列和完整性约束、修改原有的列定义和完整性约束等。
#### ADD方式
用于增加新列和完整性约束。

定义方式同`CREATE TABLE`语句中的定义方式相同，其语法格式为：

//ALTER TABLE <表名> ADD <列定义> | <完整性约束定义>
ALTER TABLE Student ADD RegisterDate DATETIME,

1
2
3
#### ALTER方式

用于修改某些列，其语法格式为：

//ALTER TABLE<表名> ALTER COLUMN <列名><数据类型>[NULL|NOT NULL]
ALTER TABLE Student ALTER COLUMN Sage SMALEINT NULL

1
2
3
4
5
6
7
8
要求：
1.不能改变列名；
2.不能将含有空值的列的定义修改为NOT NULL约束；
3.若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；
4.只能修改NULL|NOT NULL约束，其它类型的约束在修改之前必须先删除，然后再重新添加修改过的约束定义。

#### DROP方式
删除完整性约束定义，其语法格式为：

//ALTER TABLE<表名> DROP CONSTRAINT <约束名>
ALTER TABLE Student DROP UNIQUE(SNAME)

1
2

### 删除基本表

DROP TABLE <表名>

1
2
3
4
5
6
## 数据库表的基本操作
SQL语言的数据更新语句DML主要包括插入数据、修改数据和删除数据三种语句。

### 插入数据记录
有两种方式，一种是向具体记录插入常量数据；另一种是把子查询的结果输入到另一个表中去。前者智能插入一条数据，后者一次可以插入多条。
#### 插入一行新记录

//INSERT INTO <表名>[(<列名1>[,<列名2>…])] VALUES(<值>)

INSERT INTO S VALUES (‘s7’,’小刚’,’男’,21,’计算机’)

1
2
3
4
5
6
7
注意：

必须用逗号将各个数据分开，字符型数据要用单引号括起来。

INTO子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。
#### 插入一行部分数据
在SC表中插入一条选课记录（’S7’,’C1’）。

INSERT INTO SC (SNO,CNO)
VALUES (‘s7’,‘c1’)

1
2
3
4
5
6
7
8
注意：

将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。

对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值，如上例的SCORE即赋空值。
但在表定义时有NOT NULL约束的属性列不能取空值。      
#### 插入多行记录
1.

INSERT INTO userTable (user_id, user_name) VALUES
(1, ‘dsf’),
(2, ‘fgy’),
(3, ‘faad’);

1
这样只需执行一次SQL查询，即可插入多行记录，大大提高了效率，使用php编程的时候，可以使用字符串连接的方式连接sql语句即可:

$a = 1;
while (5 == $a)
{
if (1 == $a)
$sql = “INSERT INTO tbl_name (col1,col2) VALUES ($a,$b)”;
else
$sql .= “,($a,$b)”;

$a++;
$b++;

}

1
2
mysql_query($sql);
用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。

///INSERT INTO <表名> [(<列名1>[,<列名2>…])] 子查询
INSERT INTO MySalesReason
SELECT SalesReasonID, Name, ModifiedDate
FROM AdventureWorks2008R2.Sales.SalesReason
WHERE ReasonType = N’Marketing’;

1
### 修改数据记录

//UPDATE <表名> SET <列名>=<表达式> [,<列名>=<表达式>]…[WHERE <条件>]
//修改一行

UPDATE T
SET DEPT=’信息’
WHERE TN=’刘伟’

//用子查询选择要修改的行
UPDATE T
SET COMN=COMN+100
WHERE TNO IN
(SELECT T.TNO
FROM T,TC
WHERE T.TNO=TC.TNO
AND TC.CNO=’C5’)
//用子查询提供要修改的值

UPDATE T
SET SAL =
(SELECT 1.2*AVG(SAL)
FROM T)

1
### 删除数据记录

//DELETE FROM <表名> [WHERE <条件>]
//删除一行
DELETE
FROM T
WHERE TN=’刘伟’
//删除多行
DELETE
FROM TC
//利用子查询选择要删除的行
DELETE
FROM TC
WHERE TNO=
(SELECT TNO
FROM T
WHERE TN=’ 刘伟’)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22


## 索引
索引的作用：
1. 加快查询速度；
2. 保证行的唯一性。
### 索引的分类
按照索引记录的存放位置分类：

聚集索引：按照索引的字段排列记录，并且依照排好的顺序将记录存储在表中。

非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。

### 唯一索引
唯一索引表示表中每一个索引值只对应唯一的数据记录，
这与表的PRIMARY KEY的特性类似，因此唯一性索引常用于PRIMARY KEY的字段上，以区别每一笔记录。

当表中有被设置为UNIQUE的字段时，SQL SERVER会自动建立一个非聚集的唯一性索引。

而当表中有PRIMARY KEY的字段时，SQL SERVER会在PRIMARY KEY字段建立一个非聚集索引。

### 建立索引

//CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX <索引名> ON <表名> (<列名> [次序] [{,<列名>}] [次序]…)
//在agent表中建立一个索引aidx，保证每一行都有唯一的aid值。
create unique index aidx on agents(aid);

1
次序用来指定索引值的排列顺序，可为ASC（升序）或DESC（降序），缺省值为ASC。

USE STUDENT
CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)

1
2
3
4
执行此命令后，为SC表建立一个索引名为SCI的唯一索引
此索引为SNO和CNO两列的复合索引，即对SC表中的行先按SNO的递增顺序索引，对于相同的SNO，又按CNO的递增顺序索引。
### 删除索引
建立索引是为了提高查询速度，但随着索引的增多，数据更新时，系统会花费许多时间来维护索引。这时，应删除不必要的索引。

//DROP INDEX 数据表名.索引名
//删除表SC的索引SCI。
DROP INDEX SC.SCI

1
2
3
### 查看索引
#### 查看索引信息
报告有关表或视图上索引的信息，当前数据库中表或视图的名称的数据类型为nvarchar(776)。

//sp_helpindex [ @objname = ] ‘表或视图的名称’
Use eduDB
GO
sp_helpindex

1
2
#### 查看索引的空间信息
显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间

sp_spaceused [‘表的名称’]
`

事务

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

1.原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2.一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

3.隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。

4.持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始。delete删除表中数据，可以加where字句。

ER图

在ER图中有如下四个成分：
矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

冗余字段

冗余字段，是设计数据的时候常用的一种手段。优点是，可以在很大程度上避免数据库的关联查询，不使用join提升查询速度。缺点是，数据一致性维护成本高，需要维护冗余字段的一致性。

存储过程与触发器的区别

触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。

两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。