数据库原理及其应用

数据库系统(DBS):是以计算机软硬件为工具,把数据组织成数据库形式并对其储存,管理，处理和维护数据的高效能的信息处理系统

组成：由计算机硬件系统，数据库，软件系统（含操作系统，应用程序开发工具，数据库应用系统），数据库管理系统，数据库用户组成。

数据库的两个特性:
1.集成性
2.共享性

数据库管理系统:是数据库与系统的和心软件
功能：
数据定义功能：
1.提供数据定义语言(DDL)定义数据的模式，外模式和内模式三级模式结构
2.定义模式/内模式和外模式/模式二级映像
3.定义有关的约束条件

数据操纵功能：增删查改

数据库运行管理功能

数据库的建立与维护功能

数据组织，储存和管理

与其他软件系统进行通信功能

数据库系统所处位置

数据库系统内部和外部体系结构：
内部体系结构：
数据库系统通常采用三级模式结构（模式，内模式，外模式）

二级映像：外模式/模式映像 和 模式/内模式映像

外模式/模式映像：保证了数据与程序间的逻辑独立性
模式/内模式映像：保证了数据的物理独立性

模式：模式是数据库的整体逻辑，即概念视图，概念级数据库，一个数据库只有一个模式

外模式：又称子模式或用户模式，是与某一应用有关的数据的逻辑表示，即用户视图，用户数据库，可以有多个外模式，同意外模式可以为多个应用程序使用

内模式：又称为存储模式或

物理模式，是对整个数据库存储结构的描述，是整个数据库实际存储的表示，物理视图

数据库系统的三级模式与二级映像的优点：
1.保证数据的独立性
2.简化了用户接口
3.有利于数据共享
4.有利于数据的安全保密

数据库系统外部体系结构：
从最终用户角度来看，DBS分为：
1.单用户结构：又称桌面型数据库系统，将应用程序
2.主从式结构：大型主机带多终端的多用户结构的系统，又称主机/终端模式
3.分布式结构：是分布式网络技术与数据库技术相结合的产物
优点：堕胎服务器并发的处理数据，提高效率
缺点：数据分布式分布式存储给数据处理任务协调与维护带来困难
4.客户/服务器结构（cs）：把DBMS的功能与应用程序分开，分为：数据库服务器和客户机
优点：网络运行效率大大提高
缺点：维护升级很不方便
5.浏览器/服务器结构（bs）：客户机仅安装通用的浏览器软件，实现输入输出，应用程序安装在应用服务器上，充当中介

数据模型
概念：是描述数据，数据联系，数据的语义和完整性约束的概念集合，由数据结构，数据操作和完整性约束三要素组成

数据结构：用于描述系统的静态特征，描述数据库的组成对象以及对象间的联系即：
1.描述数据对象的类型，内容，性质等
2.描述数据对象间的联系

数据结构有三种：
1.层次结构–层次模型–层次数据库
2.网状结构–网状模型–网状数据库
3.关系结构–关系模型–关系数据库

数据操作：对数据库中的数据允许执行的操作的集合，包括操作及相应的操作规则等，描述了数据库的动态特性（增删改查插）

数据的完整性约束：一组完整性规则的集合，完整性规则是数据模型中数据及其联系所具有的制约和依存规则

数据模型由三种：
层次模型：树状模型
网状模型：
关系模型：是一张规范化的二维表，它有表名，表头，表体三部分构成

关系的形式化定义和概念：
域：域是一组具有相同数据类型的值的集合，又称为值域。用D表示
域中所包含的值的个数称为域的基数用m表示，在关系中用域表示属性的取值范围

//如
D1={莉莉，王平，刘伟}，m=3；
D2={男， 女}，m=2；

关系运算符：

sql：
数据库的结构：
数据文件和事务日志文件(.ldf)
数据文件又分主要数据文件(.mdf)和次要数据文件(.ndf)

文件组：是将多个数据文件集合起来形成的一个整体，分为主要文件组和次要文件组
一个数据文件只能存放在一个文件组中，一个文件组也只能被一个数据库使用

日志文件不分组，它不属于任何文件组

用sql命令创建数据库：

//[]内容可填可不填
CREATE  DATABASE
[ON
[FILEGROUP 文件组名称]
( NAME = 数据文件逻辑名称,
  FILENAME = '路径+数据文件名',
  SIZE = 数据文件初始大小,
  MAXSIZE = '数据文件最大容量',
  FILEGROWTH = 数据文件自动增长容量,)]
[LOG ON
( NAME  = 日志文件逻辑名称,
  FILENAME = '路径+日志文件名',
  SIZE = 日志文件初始大小,
  MAXSIZE = 日志文件最大容量,
  FILEGROWTH = 日志文件自动增长容量,)]
[COLlATE 数据库校验方式名称]
[FOR ATTACH]

//实例:用SQL命令创建一个teach数据库
CREATE DATABASE Teach
ON
(NAME = Teach_Data,
 FILENAME = 'E:\TeachData.mdf',

修改数据库:
1.使用management studio修改
2.使用sql命令修改

ALTER DATABASE 数据库名称
ADD FILE(具体文件格式)
[,...n]
[TO FILEGROUP 文件组名称]
[ADD LOG FILE(具体文件格式)
[,...n]
|REMOVE FILE 文件逻辑名称
|MODIFY FILE(具体文件格式)
|ADD FILEGROUP 文件组名称
|REMOVE FILEGROUP 文件组名
|MODIFY FILEGROUP 文件组名
{READ_ONLY|READ_WRITE,
	|DEFAULT,
	|NAME = 新文件组名
}
其中具体文件格式为:
( NAME=文件逻辑名称
  [,NAENAME=新文件逻辑名称]
  [,SIZE = 初始文件大小]
  [,MAXSIZE=文件最大容量]
  [,FILEGROWTH=文件自动增长容量]
  )

//实例：修改teach数据库中的teach_data文件增容方式为一次增加20MB
ALTER DATABASE Teach
MODIFY FILE
(NAME = Teach_Data,
 FILEGROWTH = 20)

//实例：用SQL命令修改数据库teach，添加一个次要数据文件，逻辑名称为teach_datanew
ALTER DATABASE Teach
ADD FILE(
	NAME=Teach_Datanew,
	FILENAME = 'E:\Teach_Datanew.ndf',
	SIZE = 100,
	MAXSIZE = 200,
	FILEGROWTH = 10)

//实例：用SQL命令从teach数据库中删除次要文件
ALTER DATABASE Teach
REMOVE FILE Teach_Datanew

用系统存储过程显示数据库结构:

Sp_helpdb[[@dbname=]'name]
//实例
Sp_helpdb AdventureWorks2012

用系统存储过程显示文件信息

Sp_helpdb[[@filename=]'name']
//实例
Sp_hellpfile Address

用系统存储过程显示文件组信息

Sp_helpfilegroup[[@filegroupname=]'name']
//实例

迁移数据库:
1.分离数据库
2.生成脚本

数据类型:
1.整数型:
从大到小:
bigint int smallint tinyint bit

2.数值型：
精确数值型:numeric decimal
近似型:float real

numeric(p,s),其中p表示数据长度,s表示小数位数

3.字符型
char varchar nchar nvarchar text ntext
char varchar 存放非Unicode字符即(ASCII字符),一个字符占一个字节,char是定长的,varchar是非定长的，nchar,nvarchar存放Unicode字符,一个字符占2个字节，当字符较多时可使用你text或者ntext
最大可存储2GB

char(6)表示可以包含6个非Unicode字符,即使小于6个,在内存中也要分配6字节空间大小
varchar(6)表示实际包含多少个非Unicode字符在内存中就分配多少字节

二进制数据型：
binary varbinary image
binary是定长的二进制数据型,varbinary是非定长的二进制数据类型,两者最多克表示8000个字节,任何类型的数据都可以存放在这种类型的字段中，不需要类型转换
image可以存放图片本身,但事先需要将图片转换成二进制流形式,也可以存储图片路径

特殊类型:
timestamp uniquedentifier sql_variant table
sql_variant数据类型可以存放除文本，图片数据和timestamp类型数据外的任何合法SQL server数据
table数据类型用于存储对表或视图处理后的结果集,这一类型使得变量可以存储一个表,从而使函数或过程返回查询结果更加方便，便捷

创建数据表:
1.通过management studio
2.使用sql命令

CREATE TABLE <表名>
(<列定义>[{,列定义>|<表约束>}]）

//示例：
CREATE TABLES
	(SNo CHAR(6) CONSTRAINT S_CONS NOT NULL,//可以将CONSTRAINTS S_CONS省去
	 SN VARCHAR(10),
	 Sex NCHAR(1) DEFAULT '男',
	 Age INT,
	 Dept NVARCHAR(20))

表约束：
NULL， NOT NULL，UNIQUE, PRIMARY KEY, FOREIGN KEY,CHECK
UNIQUE约束:
使用UNIQUE约束,指明基本表在某一列或多个列的组合上的取值必须唯一,在建立UNIQUE约束时,需要考虑以下爱几个因素:
1.使用unique约束的字段允许为NULL
2.一个表中可以允许有多个UNIQUE约束
3.UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引,缺省为非聚集索引
4.可用于列约束,也可用于表约束

//示例：建立一个s表，定义sn为唯一键（该列中不允许出现重复项）
CREATE TABLE S
	(SNo CHAR(6),
	 SN VARCHAR(10) CONSTRAINT SN_UNIQ UNIQUE,//CONSTRAINT SN_UNNIQ可以省略
	 Sex NCHAR(1) DEFAULT '男',
	 Age INT,
	 Dept NVARCHAR(20))

//示例:建立一个S表，定义SN+Sex为唯一键,次约束为表约束
CREATE TABLE S
	(SNo CHAR(6),
	 SN VARCHAR(10),
	 Sex NCHAR(1) DEFAULT '男',
	 Age INT,
	 Dept NVARCHAR(20),
	 CONSTRAINT S_UNIQ UNIQUE(SN, Sex))

PRIMARY KEY约束:(主键约束)
用于定义基本表的主键,起唯一表示作用,不能重复,不能为NULL
1.在一个表中只能定义一个PRIMARY KEY约束
2.不能为同一列或一组列既定义UNIQUE约束,又帝国一PRIMARY约束
3.也分列约束和表约束

//示例:建立一个s表,定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键
CREATE TABLE S
(SNo VARCHAR(6) CONSTRAINT S_Prim PRIMARY KEY,
SN NVARCHAR(10) UNIQUE,
Sex NCHAR(1),
Age INT,
Dept NVARCHAR(20))

CREATE TABLE C
(CNo VARCHAR(6) CONSTRAINT C_PRIM PRIMARY KEY,
CN NVARCHAR(10),
Sex NCHAR(1),
Age INT,
Dept NVARCHAR(20))

//示例：建立一个SC表，定义SNo+CNo为SC的主键
CREATE TABLE SC
(SNo VARCHAR(6) NOT NULL,
CNo VARCHAR(6) NOT NULL,
Score NUMERIC(4,1),
CONSTRAINT SC_PRIM PRIMARY KEY(SNo, CNo))

FOREIGN KEY约束(外键约束):

//示例：建立一个SC表，定义SNo，CNo为SC的外部建
CREATE TABLE SC
(SNo VARCHAR(6) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo),
CNo VARCHAR(6) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo),
Score NUMERIC(4,1),
CONSTRAINT S_Prim PRIMARY KEY(SNo, CNo))

CHECK约束:
CHECK约束用来检查字段值所允许的范围,在建立CHECK约束时,需要考虑一下几个因素:
1.一个表中可以定义多个CHECK约束,
2.每个字段中只能定义一个CHECK约束
3.在多个字段上定义的CHECK约束必须为表约束
4.当执行INSERT,UNDATE语句时，CHECK约束将验证数据

//示例:建立一个SC表，定义Score的取值范围为0-100之间
CREATE TABLE SC
(SNo VARCHAR(6),
CNo VARCHAR(6),
Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score<=100))

修改数据表：

//示例：在s表中添加一个班号列和住址列，使用此方式添加的新列自动填充NULL，因此不能设为NOT NULL
ALTER TABLES S
ADD
Class_No VARCHAR(9),
Adress NVARCHAR(20),

//示例：在SC表中添加完整性约束定义，使Score在0~100之间
ALTER TABLE SC
ADD
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

//示例：把s表中的SN列加宽到12个字符,若列中已有数据,则不能减少该列的宽度，也不能改变其数据类型
ALTER TABLE S
ALTER COLUMN
SN NVARCHAR(12)

//示例：删除S表中的主键
ALTER TABLE S
DROP CONSTRAINT S_PRIM

删除数据库:

DROP DATABASE 数据库名称
//示例
DROP DATABASE Teach

单关系表的数据查询

SELECT [ALL|DISTINCT][TOP N
[PERCENT][WITH TIES]]
<列名> [AS别名1] [{,<列名> [AS别名2]}]
FROM <表名> [[AS]表别名]
[WHERE <检索条件> ]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|desc]]

//示例:无条件查询,只包含SELECT --FROM
SELECT SNo,SN,Age
FROM S

//示例：查询学生的全部信息
SELECT *
FROM S

//示例：查询选修了课程的学生的学号,DISTINCT 取消重复项
SELECT DISTINCT SNo
FROM SC

//查询全体学生的姓名,学号和年龄
SELECT SN Name,SNo,Age//SELECT SN AS Name,SNo,Age,表头不在是原字段而是别名
FROM S

//满足条件查询
//示例:查询选修课程号为'C1'的学生的成绩和学号
SELECT SNo,Score
FROM SC
WHERE CNo='C1'

//示例：查询成绩高于85分的学生的成绩和学号
SELECT SNo,Score
FROM SC
WHERE Score>85

//多重条件查询AND OR  NOT
//示例：查询选修C1或C2且分数大于等于85分学生的学号，课程号和成绩
SELECT SNo,CNo,Score
FROM SC
WHERE (CNo = 'C1' OR CNo ='C2') AND (Score >= 85)//CNo IN ('C1','C2') AND (Score>=85)

//示例：查询工资在1000元-1500元之间的交实的教师号，姓名及职称
SELECT TNo,TN,Prof
FROM T
WHERE Sal BETWEEN 1000 AND 1500//Sal>=1000 AND Sal<=1500

//示例：查询所有姓张的教师的教师号和姓名
SELECT TNo,TN
FROM T
WHERE TN LIKE '张%'

//示例：查询姓名中第二个汉字是“力”的教师号和姓名
SELECT TNo,TN
FROM T
WHERE TN LIKE ‘_力%’//下划线代表一个字符，%代表0个或者多个字符

//查询没有考试成绩的学生的学号和相应的课程号：
SELECT SNo,CNO
FROM SC
WHERE Score IS NULL


//库函数
AVG//按列计算平均值
SUM//按列计算值的总和
MIN//求一列中最小值
MAX//求一列中最大值
COUNT //按列计算个数

//示例：求学号为s1的学生的总分和平均分
SELECT SUM(Score) AS TotalScore,AVG(Score) AS AvgScore
FROM SC
WHERE (SNO = 'S1')

//示例：求选秀C1号课程的最高分，最低分之间相差的分数
SELECT MAX(Score) AS MaxScore,MIN(Score) AS MinScore，MAX(Score) - MIN(Score) AS Diff
FROM SC
WHERE (CNO = 'C1')

//求学校有多少个系
SELECT COUNT(DISTINCT Dept) AS DeptNum//可以消去重复
FROM S

//统计有成绩同学的人数
SELECT COUNT(Score)
FROM SC

//分组查询：
//示例：查询各个教师的教师号及其任课的门数
SELECT TNo,COUNT(*) AS C_Num
FROM TC
GROUP BY TNo//GROUP BY按TNo分组，所有具有相同TNo的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数

//示例：查询选修两门及以上学生的学号和选课门数,若要对分组后再次筛选则要用having
SELECT SNo,COUNT(*) AS SC_Num
FROM SC
GROUP BY SNo
HAVING(COUNT(*)>=2)

//对查询结果进行排序
//示例：查询选秀C1学生的学号和成绩，并按成绩降序排序
SELECT SNo,SCORE
FROM SC
WHERE(CNo = 'C1')
ORDER BY Score DESC

//示例：查询选秀C2，C3，C4或C5课程的学号，课号和成绩，学号升序，学号相同在成绩降序
SELECT SNo,CNo,Score
WHERE CNo IN ('C2','C3','C4','C5')
ORDER BY SNo,Score DESSC

多关系表数据查询：
当查询同时设计两个及两个以上的表时，称为连接查询
JOIN分类（使用时与ON搭配）:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN

//内连接查询
//示例：查询刘伟老师所讲授的课程，要求列出教师号，教师姓名和课程号
SELECT T.TNo,TN,CNo
FROM T,TC
WHERE(T.TNo = TC.TNo) AND (TN='刘伟‘)//这里T.TNo = TC.TNo为连接条件

SELECT T.TNo,TN,CNo
FROM T INNER JOIN TC
ON T.TNo = TC.TNo
WHERE(TN = '刘伟')

//示例：查询所有选课学生的学号，姓名，选课名称以及成绩(没有选课的同学的选课信息显示为NULL)
SELECT S.SNo,SN,CN,Score
FROMM S,C,SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

//外连接
//示例：查询所有选课学生的学号，姓名，选课名称以及成绩(没有选课的同学的选课信息显示为NULL)
SELECT S.SNo,SN,CN,Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo = SC.SNo
LEFT OUTER JOIN C
ON C.CNo = SC.CNo

//交叉连接
//示例：对学生表和课程表进行交叉连接
SELECT *
FROM S CROSS JOIN C//查询结果为行数为两表行的乘积，列数为两表列数和

//自动连接
//示例：查询所有比刘伟工资高的教师姓名，工资和刘伟的工资
SELECT X.TN,X.Sal AS Sal_a,Y.Sal AS Sal_b
FROM T AS X,T AS Y
WHERE X.Sal > Y.Sal AND Y.TN = '刘伟'

子查询：
普通子查询

//示例：查询与刘伟老师相同职称的教师号，姓名，
SELECT TNo,TN
FROM T
WHERE Prof=(SELECT Prof
						FROM  T
						WHERE TN = '刘伟')

SELECT X.TNo,X.TN
FROM T AS X,T AS Y
WHERE Y.TN='刘伟' AND Y.Prof = X.Prof

//示例：返回一组值的普通子查询--使用ANY
//示例：查询讲授课程号为C5的教师姓名
SELECT TN
FROM T
WHERE (TNo ANY (SELECT TNo
								 FROM TC
								 WHERE CNo = 'C5'))
SELECT TN
FROM T
WHERE (TNo IN (SELECT TNo
								 FROM TC
								 WHERE CNo = 'C5'))

/示例：查询其他系中比计算机系某一教师工资高的教师的姓名和工资
SELECT TN,Sal
FROM T
WHERE(Sal > ANY(SELECT Sal
								FROM T
								WHERE Dept='计算机' ))
				AND (Dept <>计算机')

//示例：查询其他系中比计算机系所有教师工资高的教师的姓名和工资
SELECT TN,Sal
FROM T
WHERE(Sal > ALL(SELECT Sal
								FROM T
								WHERE Dept='计算机' ))
				AND (Dept !=计算机')

相关子查询

//示例：查询没有讲授课程号为C5的教师姓名
SELECT TN
FROM T
WHERE(NOT EXISTS(SELECT *
									 FROM TC
									WHERE TNo = T.TNo AND CNo='C5'))

合并查询：
将不同结果放在同一个结果里
UNION会自动将重复的数据剔除
参加和平查询的各个子查询使用的表结构应该相同，即各个子查询中的字段数目和对象的数据类型必须相同

//将学生s1和s5的结果放在一起
SELECT SNo AS 学号,SUM(Score) AS 总分
FROM SC
WHERE(SNo = 'S1')
GROUP BY SNo
UNION SELECT SNo AS 学号,SUM(SCORE) AS 总分
FROM SC
WHERE(SNo = 'S5')
GROUP BY SNo

存储查询结果到表中
SELECT INTO可以将查询结果存储到一个新建的数据库表或临时表中

//从sc数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表中
SELECT SNo AS 学号，SUM(Score) AS 总分
INTO Cal_Table
FROM SC
GROUP BY SNo

数据操纵：
1.添加数据:INSERT INTO

//示例：添加一条新纪录
INSERT INTO SC(SNo,CNo)
VALUES('S7', 'C1')

//示例：求出各系教师的平均工资，把结果存放在新的AvgSal中
CREATE TABLE AvgSal
(Department VARCHAR(20),
 Average SMALLINT)
INSERT INTO AveSal
SELECT Dept,AVG(Sal)
FROM T
GROUP BY Dept

2.修改数据

//修改一行数据
UPDATE T
SET Dept='信息'
WHERE TN='刘伟'

//修改多条记录
UPDATE S
SET Age = Age+1

//用子查询选择要修改的行
//把讲授c5课程的老师的工资增加100
UPDATE T
SET Sal = Sal + 100
WHERE (TNo IN (SELECT TNo
							 FROM T,TC
							 WHERE T.TNo = TC.TNo AND TC.CNo='C5))

3.删除数据

DELETE
FROM T
WHERE TN='刘伟'

视图
创建视图

CREATE VIEW vier_name

//示例：创建一个计算机系教师情况的视图Sub_t
CREATE VIEW Sub_t
AS SELECT TNo,TN,Prof
 FROM T
 WHERE Dept='计算机'

//示例：创建一学生情况视图S_SC_C(包括学生，姓名，课程名及成绩)
CREATE VIEW S_SC_C(SNo,SN,CN,Score)
AS SELECT S.SNo,SN,CN,Score
	FROM S,C,SC
	WHERE S,SNo = SC.SNo AND SC.CNo=C.CNo

//修改视图
//示例：修改学生情况视图S_SC_C（包括姓名，课程名以及成绩）
ALTER VIEW S_SC_C(SN, CN, Score)
AS SELECT SN,CN,Score
	FROM S,C,SC
	WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

//查询视图
//示例：查询视图Sub_t中职称为教授的教师号和姓名
SELECT TNo,TN
FROM Sub_T
WHERE Prof = '教授'

//更新视图
//添加，修改，删除操作跟对表的一样

索引
索引类型
1.聚集索引：一个表中只能包含一个聚集索引
2.非聚集索引：
3.唯一索引
4.视图索引
5.全文索引
6.XML索引

//创建索引
//示例：为表sc在sno和cno建立唯一索引
CREATE UNIQUE INDEX CSI ON SC(SNo,CNo)//CSI是为索引取得名字
//示例: 为教师表T在tn上建立聚集索引
CREATE CLUSTER INDEX TI ON T(TN)

//修改索引，索引参数介绍
REBUILD：删除索引并重新生成索引
PARTITION：指定只重新生成或重新组织索引的一个分区
DISABLE：将索引标记为禁用
REORGANIZE:重新组织索引

//删除索引
DROP INDEX <table or view name> <index name>

DROP INDEX <index name> ON <table or view name>

//查看索引
Sp_helpindex SC

//更改索引名字
Sp_rename oldname,newname