数据库总结

基本概念

DATA: 数据

数据长期存储，有序的共享集合
DB： 数据库

长期存储在计算机内有组织的，共享的数据集合，即在计算机系统中按一定的数据模型组织存储和使用的相关连的数据集合

DBMS: 数据库管理系统

数据系统的核心，是为数据库的建立，使用和维护而配置的软件（略）
DBS:数据库系统
DBA:数据库管理员

三级模式结构

外模式：

外模式也称子模式或用户模式，他是对数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述。对应着视图
模式:

模式也称概念模式或逻辑模式，是对数据库中全部数据的逻辑结构和特征的描述，是所有用户的公共数据视图。对应着基本表
内模式:

内模式也称存储模式或无厘模式，是对数据物理结构和存储方式的描述，是数据在数据库内部的表示方式，一个数据库只有一个内模式。对应着索引

两级映像

外模式/模式映像

描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构，对应于同一个模式可以有任意多个外模式。
保证数据的逻辑独立性
模式/内模式映像

在数据库种只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的，他定义了数据库全局了逻辑结构与存储直接的对应关系。
保证数据的物理独立性

E-R图

矩形：实体

椭圆：属性

菱形：联系 联系可以有属性

实体关系图

联系

一对一联系(1:1)
一对多联系(1:M)

N 方的添加 联系的属性和1方的主键
多对多联系(M:N)

一定要单独创表，表内包含两个表的主键和联系属性

关系的完整性

实体完整性规则

实体完整性要求关系的主码具有唯一性且不能取空值。

主码有唯一性且不能取空值
参照完整性规则

外键的取值只能等于空值或所参照的主键的取值。

（外键）
用户定义的的完整性规则
是由用户根据实际情况对数据库中数据的内容进行的规定，也称为域完整性规则。
- 默认值：default
- 唯一值：unque
- 取值范围：check
- 空值：

关系运算

是以关系为运算对象的一组高级运算的集合。关系代数是一种抽象的查询语句，是关系数据操纵语言的一种传统表达方式，即代数方式的数据查询过程。关系代数的运算对象是关系，运算结果也是关系。

笛卡尔积： R x S = {t|t=<t^r,t^s> 并 t^r 属于R 并 t^s 属于 S}
- A×B={(x,y)|x∈A∧y∈B}
- 例如，A={a,b}, B={0,1,2}，则
- A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
- B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}
  
  属性，元组
选择运算
- 选择：行
- 投影：列

关系的规范化

关系模式中的异常：

删除异常：数据在数据库中的重复存放称之为数据冗余

插入异常：数据冗余，更新数据时维护数据完整性的代价太大

更新异常：无法插入某部分信息称为插入异常

数据冗余：不该删除的数据不得不删除

范式的转化

1NF: 每个属性不可再分。

2NF：不存在非主属性对码的部分函数依赖

3NF：不存在非主属性对码的部分函数依赖和传递函数依赖

数据库设计

逻辑设计：主要为E-R图的转换和关系模式的主码判定

数据库的基本概念

数据库的文件类型
- 主数据文件：必有
- - 数据 + 系统信息 = .mdf
- - 有且只有一个
- 辅助数据文件：可有可无
- - 除主数据以外的 = .ndf
- - 任意个
- 事务日志文件
- - 所有数据库的更新操作 = .ldf
- - 至少有一个 不属于任何文件组
数据文件组：为了便于管理和分配数据而将文件组织在一起，通常可以为一个数据库创建多个文件组(File Group)，将多个数据库文件分配在不同的文件组内分组管理。
逻辑数据库
- master 数据库
- - 系统级信息（不要破坏）
- model 数据库
- - 模型（之后创建的数据库一定含有）
- msdb 数据库
- - 作业调度
- tempdb 数据库
- - 临时数据库（仅在一个回话期间有效）

数据库的操作

创建数据库

1	create datebase <datebase_name>

表的创建、修改、删除

-- 创建
create table student
(
  sno char(4) PRIMARY KEY,  --主键
  sname nvarchar(20) NOT NULL,  --不能为空
  sex nvarchar(4),
  birthday date
)
-- 删除
drop table table_name
-- 修改
alter table table_name
-- 修改sname属性为nvarchar(20) 可以为空
alter column sname nvarchar(20) null
-- 添加字段
add teacher nvarchar(10)
-- 删除字段
drop column sex

约束：约束每列的属性

create table student
(
    sno char(4) primary key,    --主键
    sex nvarchar(4) default '男' /*默认男*/ check(sex in ('男','女')),
    idnumber char(18) unique,   --unique 唯一的
    teacerid char(4)  foreign key peferences teacher(teacherid),  --外键 teacher表中的teacherid
    -- 略
)

数据库查询

很多内容来源于JasonHe

简单查询

DISTINCT 关键字过滤重复内容

-- 单表查询
SELECT * FROM 表名
-- 多表查询
SELECT rdID, rdName FROM Reader;
-- DISTINCT 关键字 过滤重复内容
SELECT DISTINCT specialty FROM student; /*查询学生表中的专业名称，过滤重复行*/
-- AS 关键字
SELECT DISTINCT specialty AS 专业名称 FROM student;/*将查询结果中 specialty 取一个列别名*/

选择查询

-- 比较运算符 =、>、<、>=、<=、!=、<>、!>、!<
SELECT * FROM sc WHERE score >= 60
-- 逻辑表达式 AND、OR、NOT
SELECT * FROM student WHERE specialty = '计算机' AND ssex = '男'
-- 确定范围的关键字 BETWEEN AND、NOT BETWEEN AND
SELECT * FROM sc WHERE score BETWEEN 80 AND 90
-- 确定集合的关键字 IN、NOT IN
SELECT * FROM student WHERE specialty IN ('计算机','通信')
-- 字符匹配关键字 LIKE、NOT LIKE
SELECT * FROM student WHERE sname LIKE '张%' /*查找姓张的学生*/
SELECT * FROM student WHERE sname LIKE '张_' /*查找姓张且姓字为两个字的学生*/
-- 空值判断关键字 IS NULL、IS NOT NULL
SELECT * FROM sc WHERE score IS NULL
-- 复合条件查询
SELECT * FROM student WHERE sex = '女' AND (specialty = '计算机' OR specialty = '通信')

分组查询

-- 简单分组
SELECT * FROM student GROUP BY ssex
-- HAVING 关键字：选修了两门及以上课程的学生
SELECT * FROM student GROUP BY ssex HAVING COUNT(cno) >= 2
-- 聚合函数
count()、sum()、avg()、min()、max()

连接查询

-------------------------------------------------
a表     id   name     b表     id   job   parent_id
        1    张三             1    23    1
        2    李四             2    34    2
        3    王武             3    34    4
a.id 同 parent_id 存在关系
--------------------------------------------------
-- 内连接
select a.*,b.* from a inner join b on a.id = b.parent_id
结果是：
1   张三       1     23     1
2   李四       2     34     2
-- 左外连接
select a.*,b.* from a left join b on a.id = b.parent_id
结果是：
1   张三       1     23     1
2   李四       2     34     2
3   王武       null
-- 右外连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是：
1   张三       1     23     1
2   李四       2     34     2
null           3     34     4

子查询

1
2
3

-- 查询与连晓燕在同一个单位的读者；
SELECT rdName FROM Reader WHERE rdDept = (SELECT rdDept FROM Reader 
  WHERE rdName = '连小燕');

集合运算查询

-- UNION（并集）：查询选修了课程 C001 和 课程 C004 的学生的姓名
SELECT sname FROM sc,student
WHERE cno = 'C001' AND sc.sno = student.sno
UNION
SELECT sname FROM sc,student
WHERE cno = 'C004' AND sc.sno = student.sno
-- EXCEPT（差集）：查询没有选课的学生的学号
SELECT sno FROM student
EXCEPT
SELECT sno FROM sc
-- INTERSECT（交集）：查询即选修了课程 C001 又选修了课程 C004 的学生的姓名
SELECT sname FROM sc,student
WHERE cno = 'C001' AND sc.sno = student.sno
INTERSECT
SELECT sname FROM sc,student
WHERE cno = 'C004' AND sc.sno = student.sno

对查询结果进行排序

-- 默认升序 ASC
SELECT * FROM SC ORDER BY sname ASC
-- 降序排序 DESC（按成绩降序，成绩相同的话按学号升序排）
SELECT sno,score FROM SC ORDER BY score DESC,sno ASC

视图和索引

视图并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，不存储视图对应的数据，这些数据仍存储在导出视图的基本表中，视图实际上是一个查询结果。
1
2
3
4
5
6
7
8
9
-- 创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
-- 查看视图
SELECT * FROM view_name
-- 删除视图
DROP VIEW view_name
索引概述：索引(Index)是对数据库表中一个或多个列的值进行排序的结构，其主要目的是提高SQL Server系统的性能，加快数据的查询速度和减少系统的响应时间。
索引的类型

聚集索引：
- 在表和视图中只能有一个聚集索引。
- 当建立主键约束时，若表中没有聚集索引，会用主键列作为聚集索引键。
- 实际应用中一般为定义成主键约束的列建立聚集索引。
- 聚集索引会对表和视图进行物理排序，对查询非常有效。
非聚集索引:
- 非聚集索引不对表和视图进行物理排序。
- 若表中不存在聚集索引，则表是未排序的。
- 在表或视图中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。
聚集索引和非聚集索引都可以是唯一的索引。因此，只要列中数据是唯一的，就可在同一个表上创建一个唯一的聚集索引。如果必须实施唯一性以确保数据的完整性，则应在列上创建UNIQUE或PRIMARY KEY约束，而不要创建唯一索引。

创建索引：CREATE INDEX index_name ON table_name(col_name)

T-SQL编程

变量的声明：DECLARE @var_name VARCHAR(20)、DECLARE @MyCounter INT
变量的分类：“@”开头为局部变量，“@@”开头为全局变量。
变量的赋值：SET @var_name = GETDATA()、SELECT COUNT(sno)=@MyCounter from student
显示变量的值：PRINT @var_name,@MyCounter

批出理语句使用

/* 多行注释 */
-- 单行注释
SELECT sno,sname FROM student
GO -- 此处 GO 为批处理语句

流程控制语句

-- SET 语句
DECLARE @myvar char(20);
SET @myvar = 'This is a test';
SELECT @myvar;
GO

-- BEGIN END 语句（用于将多个 SQL 语句合并为一个语句块）
BEGIN
  { sql_statement|statement_block }
END

-- IF……ELSE 语句：
-- 如果 C001 号课的平均成绩高于 80 分，则显示“平均成绩还不错”，否则显示“平均成绩一般”
IF ( SELECT AVG(score) FROM sc WHERE cno = 'coo1') > 80
  PRINT 'C001 号课的平均成绩还不错'
ELSE
  PRINT 'C001 号课的平均成绩一般'

-- CASE 语句：以搜索 CASE 格式查询所有学生的考试等级，包括学号、课程号和成绩级别
SELECT sno,cno,
  CASE
    WHEN score >= 90 then 'a'
    WHEN score >= 80 then 'b'
    WHEN score >= 70 then 'c'
    WHEN score >= 60 then 'd'
    WHEN score < 60 then 'e'
  END AS score_level
FROM sc

-- WHILE 语句：求 1 到 100 的累加和，当和超过 1000 时停止累加，显示累加和以及累加到的位置。
-- 结果：a=1035,i=45
DECLARE @i int,@a int
SET @i = 1, @a = 0
WHILE @i <= 100
  BEGIN
    SET @a = @a + @i
    IF @a >= 1000 BREAK
    SET @i = @i + 1
  END
SELECT @a AS 'a',@i AS 'i'

函数

-- 聚合函数
ADD(),AVG(),SUM()……
-- 日期函数
GETDATE()：返回系统当前的日期和时间
DAY()：返回日期表达式中的日
MOUTH()：返回日期表达式中的月
YEAR()：返回日期表达式中的年
-- 自定义函数：求选课表中某门课的平均成绩
CREATE FUNCTION average(@cn char(4)) RETURNS float
AS
  BEGIN
    DECLARE @aver float
    SELECT @aver = (SELECT avg(score) FROM sc WHERE cno = @cn)
    RETURN @aver
  END

游标（定义、打开、读取、关闭、释放）

-- 利用游标将sc表中选课成绩小于50分的成绩提高30％，将大于等于50分的成绩提高50％
-- 定义游标
DECLARE SC_Cursor CURSOR
DYNAMIC FOR SELECT Grade FROM SC FOR UPDATE OF Grade
-- 打开游标
OPEN SC_Cursor
-- 读取游标
DECLARE @Grade INT
FETCH NEXT FROM SC_Cursor INTO @Grade
--假如检索到了数据，才处理
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 填充数据
    IF @Grade < 50
        UPDATE SC SET Grade = @Grade * 1.3 WHERE CURRENT OF SC_Cursor
    ELSE
        UPDATE SC SET Grade = @Grade * 1.5 WHERE CURRENT OF SC_Cursor
    -- 填充下一条数据
    FETCH NEXT FROM SC_Cursor INTO @Grade
END
-- 关闭游标
CLOSE SC_Cursor
-- 释放游标
DEALLOCATE SC_Cursor
GO