数据库

数据库作用

• 将数据持久化存储到本地
• 结构化查询数据

数据库相关概念

数据

数据库存储的基本单位，数据与其语义不可分

DB：Database

长期储存在计算机内，有组织、可共享的大量数据集合

特点

• 数据按一定数据模型组织、描述、储存
• 可以为各种用户共享
• 冗余度较小
• 数据独立性高
• 易扩展

DBMS：Database Management System

用户与操作系统之间的数据库管理软件

用途

• 科学地组织、存储数据，高效地获取、维护数据
• SQL：Structured Query Language

功能

• 数据定义功能
  • 提供数据定义语言(DDL，Data Definition Language)
  • 定义数据库中的数据对象

数据操纵功能

• 提高存储空间利用率和方便存取，提供多种方法提高存储效率

数据库的事务管理和运行管理功能

• 提供数据操纵语言(DML,Data Manipulation Language)
• 操纵数据实现对数据库的基本操作(查询、插入、删除和修改)

数据库的事务管理和运行管理功能

• 保证数据的安全性、完整性
• 多用户对数据的并发使用
• 发生故障后的系统恢复

数据库的建立和维护功能

• 数据库数据批量装载
• 数据库转储
• 介质故障恢复
• 数据库的重组织
• 性能监视等

数据的高共享性高，冗余度低，易扩充

• 降低数据的冗余度，节省存储空间
• 避免数据间的不一致性
• 使系统易于扩充

并发控制

对多用户的并发操作加以控制和协调，防止相互干扰而得到错误的结果。

数据库恢复

将数据库从错误状态恢复到某一已知的正确状态。

信息世界的概念

实体（Entity）

客观存在并可相互区别的事物称为实体。
可以是具体的人、事、物或抽象的概念。

属性（Attribute）

实体所具有的某一特性称为属性。
一个实体可以由若干个属性来刻画。

码（Key）

唯一标识实体的属性集称为码。

域（Domain）

属性的取值范围称为该属性的域。

实体型（Entity Type）

用实体名及其属性名集合来抽象和刻画同类实体称为实体型。

实体集（Entity Set）

同型实体的集合称为实体集。

联系（Relationship）

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。

数据模型

层次模型

层次模型是数据库系统中最早出现的数据模型

• 根节点、双亲节点、兄弟节点、叶节点

网状模型

以基本层次联系为基本单位，两个记录以及它们之间的一对一、一对多的联系

关系模型

• 关系（Relation）：一个关系对应通常说的一张表。
• 元组（Tuple）：表中的一行即为一个元组。
• 属性（Attribute）：表中的一列即为一个属性，给每一个属性起一个名称，即属性名。
• 主码（Key）：表中的某个属性组，它可以唯一确定一个元组。
• 域（Domain）：属性的取值范围。
• 分量：元组中的一个属性值。
• 关系模式：对关系的描述，关系名（属性1，属性2，…，属性n）

优点

• 建立在严格的数学概念的基础上
• 概念单一、数据结构简单、清晰、用户易懂易用
• 关系模型的存取路径对用户透明

缺点

• 存取路径对用户透明导致查询效率往往不如非关系数据模型
• 为提高性能，必须对用户的查询请求进行优化
• 增加了开发数据库管理系统的难度

实体间的联系

• 一对一联系
• 一对多联系
• 多对多联系

概念模型的表示方法 实体–联系（E-R）方法

• 实体型：矩形表示
• 属性：椭圆表示，用无向边将其与相应的实体连接
• 联系：菱形表示，用无向边分别与有关实体连接

关系完整性

• 实体完整性规则：属性A是基本关系R的主属性，则属性A不能取空值

• 参照完整性

  • 关系间的引用：实体及实体间的联系都是用关系来描述的，因此可能存在着关系与关系间的引用
  • 外码：F是基本关系R的一个或一组属性，它又是另一个基本关系S的主码，F是R的外码
  • 参照完整性规则：对于R中每个元组在F上的值，取空值或者等于S中某个元组的主码值

• 用户定义的完整性：针对某一具体关系数据库的约束条件，数据必须满足的语义要求（如某些属性不能取空值）

关系运算

• 集合运算符：交、并、差
  • 广义笛卡尔乘积：R为m元关系，S为n元关系，R×S是一个(m+n)元关系
• 比较运算符
• 专门运算符：选择、连接
  • 投影：获取列（属性）
  • 除
    
    
  • 连接
    • 等值连接：从关系R与S的广义笛卡尔积中选取A、B属性值相等的那些元组（进行了筛选）
    • 自然连接：种特殊的等值连接，把重复的属性列去掉
    • 外连接：舍弃的元组也保存在结果关系中，而在其他属性中填空值
• 逻辑运算符

常用完整性约束

• 主码约束：PRIMARY KEY
• 唯一性约束：UNIQUE
• 非空值约束：NOT NULL
• 参照完整性约束

定义基本表

CREATE TABLE <表名>(
<列名> <数据类型>[<列级完整性约束条件> ]
[,<列名> <数据类型>[<列级完整性约束条件> ] ] 
[,<表级完整性约束条件> ]);

CREATE TABLE Student
 (Sno varchar(11),
 Sname varchar (20),
 Sage smallint,
 PRIMARY KEY (Sno));#约束

CREATE TABLE employees2 SELECT * FROM myemployees.`employees`;

CREATE TABLE SC
 (Sno varchar(11),
 Cno smallint,
 Grade decimal(4,1),
 PRIMARY KEY (Sno, Cno)); #两个属性为主键，只能在表级定义

CREATE TABLE SC
 (Sno varchar(11) not null,
 Cno smallint not null,
 primary key (Sno,Cno),#表级定义实体完整性
 foreign key (Sno) references Student(Sno) on delete cascade)#表级定义参照完整性

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),#表级定义
FOREIGN KEY (Sno) REFERENCES Student(Sno)
 ON DELETE CASCADE#级联操作
 ON UPDATE CASCADE,
FOREIGN KEY(Cno) REFERENCES Course(Cno)
 ON DELETE NO ACTION#拒绝操作
 ON UPDATE CASCADE#
);

CREATE TABLE Student
(Sno NUMBER(6) constraint C1 check(Sno BETWEEN 90000 AND
99999),
Sname VARCHAR(20) constraint C2 not null,
Age smallint constraint C3 check (Age < 30),
Sex varchar(2) constraint C4 check (Sex IN ('男', '女')#CHECK短语定义元组上的约束条件
constraint StudentKey PRIMARY KEY（Sno）);

修改基本表

修改基本表的结构

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ];

ALTER TABLE employees2 RENAME emp5;
ALTER TABLE emp5 MODIFY Last_name VARCHAR(50);
ALTER TABLE dept1 ADD test_column VARCHAR(10);
ALTER TABLE emp5 DROP test_column;

ALTER TABLE emp5 ADD dept_id INT;
ALTER TABLE emp5 ADD FOREIGN KEY(dept_id) REFERENCES dept1(id);
ALTER TABLE emp5 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(employee_id);#添加约束名

删除基本表

DROP TABLE <表名>[RESTRICT|CASCADE];

restrict: 确保只有不存在相关视图和完整性约束的表才能删除
cascade: 任何相关视图和完整性约束一并被删除

复制基本表

create table <表名> like <源表>;

create table <表名> 
select 查询列表 
from 源表[where <筛选条件>];

CREATE TABLE employees2 SELECT * FROM myemployees.`employees`;

约束相关操作

ALTER TABLE emp5 ADD FOREIGN KEY(dept_id) REFERENCES dept1(id);
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

alter table 表名 modify column 字段名 字段类型 约束;

ALTER TABLE emp5 MODIFY salary FLOAT NOT NULL;
ALTER TABLE emp5 MODIFY salary FLOAT#删除了约束

查询操作

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];

SELECT
  department_id,
  MAX(salary) max_salary
FROM
  employees
GROUP BY department_id
HAVING max_salary > 12000;

字符串查询

• 通配符 %、_，若查询的字符串有通配符，需要在where后加上ESCAPE ‘<通配符>’

HAVING

HAVING短语作用于组，从中选择满足条件的组。

外连接与普通连接的区别：

FROM <表名> LEFT JOIN <表名> ON <以某属性连接>;

SELECT
  g.id,
  g.name,
  b.id
FROM
  beauty g
  LEFT JOIN boys b
    ON g.boyfriend_id = b.id
WHERE g.id > 3;

• 普通连接操作只输出满足连接条件的元组
• 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

嵌套查询

将SELECT-FROM-WHERE查询块放在另一个查询块条件中

• 不相关子查询：子查询的查询条件不依赖于父查询
• 相关子查询：子查询的查询条件依赖于父查询

SELECT
  last_name,
  salary
FROM
  employees
WHERE
  department_id=(SELECT
    department_id
  FROM
    employees
  WHERE last_name = 'zlotkey');

分类

带有比较运算符、IN、ANY、ALL、EXISTS谓词的子查询

SELECT employee_id,last_name FROM employees e
WHERE department_id IN  (SELECT department_id FROM employees WHERE last_name LIKE'%e%');

集合查询

• 并操作(UNION)：查询的列最要也相同，将会去重（mysql只支持）
• 交操作(INTERSECT)
• 差操作(MINUS)

SELECT last_name,salary FROM employees WHERE employee_id=100
UNION
SELECT last_name,salary FROM employees WHERE salary=24000;

要求

参加操作的各结果表的列数必须相同
对应项的数据类型也必须相同

插入数据

方式1

INSERT
INTO <表名> [ (<属性列1>[，<属性列2 >…) ]
VALUES ( <常量1> [，<常量2>] … )

INSERT INTO employees(employee_id,last_name,first_name,email)
VALUES(0001,'011eH',' '