数据库原理及应用整理.docx
《数据库原理及应用整理.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用整理.docx（22页珍藏版）》请在冰点文库上搜索。
数据库原理及应用整理
数据库（DataBase，简记为DB）就是一个有结构的、集成的、可共享的统一管理的数据集合。
所谓有结构的，指的是数据是按一定的模型组织起来的。
数据模型可用数据结构来描述。
数据模型决定数据的组织方式、操作方法。
理解数据库的前提是理解数据模型。
现在的数据库多数是以关系模型来组织数据的。
可以简单地把关系模型的数据结构－关系理解成为一张二维表。
所谓集成的，是指数据库中集中存放着企业各种各样的数据。
集中存放的好处是：
一个数据只需一个备份，重复存储少，即消除了数据的冗余。
没有数据冗余，也就能保证数据的一致。
所谓共享，指的是数据库中的数据可以被不同的用户使用。
也就是说，每一个用户可以按自己的要求访问相同的数据库。
所谓统一管理的，指的是数据库由DBMS统一管理，任何数据访问都是通过DBMS来完成的。
数据库管理系统
（DataBaseManagementSystem，DBMS）
是用来管理数据库的一种商品化软件。
数据定义的功能。
DBMS提供数据定义语言（DataDefinitionLanguage，DDL）。
数据操纵的功能。
DBMS提供数据操纵语言（DataManipulationLanguage，DML）。
数据库的运行管理（管理数据库）功能。
提供数据控制语言DCL（datacontrollanguage），负责数据库在建立、运行和维护时由DBMS统一管理和统一控制。
数据库系统DBS（DataBaseSystem）
数据库系统（DatabaseSystems，DBS）是一个带有数据库的计算机系统，它能按照数据库的方式存储和维护数据，并且能够向应用程序提供数据。
一个完整的数据库系统由数据库（DB）、数据库管理系统（DBMS）、应用程序、用户和硬件组成。
数据库系统的产生和发展
（1）人工管理阶段（50年代初期）
（2）文件系统阶段（50年代后期）
（3）数据库系统阶段（60年代后期开始）
数据库系统的特点
（1）数据结构化
（2）数据的共享性高，冗余度低，易扩充
（3）数据独立性高
（4）数据由DBMS统一管理和控制
数据库的三级模式：
外模式、概念模式、内模式。
数据独立性是指应用程序不因物理存储的改变而改变。
数据独立性包含逻辑独立性和物理独立性。
逻辑独立性是指概念模式改变时，外模式保持不变，从而使应用程序保持不变。
当数据库的概念模式改变时，DBMS只要改变外模式到模式的映射，就可保持外模式不变。
从而使应用程序保持不变。
物理独立性是指内模式改变时，概念模式保持不变。
因为当内模式改变（如数据库的存储设备或存储结构改变）时，DBMS只要通过改变概念模式到内模式映射，从而使概念模式保持不变，实现了数据的物理独立性。
decimal（2,1），有效长度为2，小数位占1位。
提供了CHAR、VARCHAR、TEXT、NCHAR、NVARCHAR和NTEXT6种数据类型。
前3种数据类型是非Unicode字符数据，后3种是Unicode字符数据。
Datetime:
用于存储日期和时间的结合体。
定义可变长度字符型变量@name,长度为8
可变长度的字符型变量@sex,长度为2，DECLARE@namevarchar（8）,@sexvarchar
小整型变量@age@agesmallint
利用CAST函数可以将某一种数据类型强制转换为另一种数据类型，其语法格式如下：
CAST（expressionASdata_type）
CONVERT函数允许用户把表达式从一种数据类型转换为另一种数据类型，并且还在日期的不同显示格式之间进行转换，其语法格式如下：
CONVERT（data_type[（length）],expression[,style]）
可使用CREATEFUNCTION语句创建函数，使用ALTERFUNCTION语句修改函数，使用DROPFUNCTION语句删除函数。
函数—用户自定义函数
Schema_name:
用户自定义函数所属的模式名称
Function_name:
用户自定义函数的名称
@parameter_name:
用户自定义函数的参数名称
Parameter_data_type:
参数的数据类型
Default:
参数的默认值
Return_data_type:
用户自定义函数返回值的数据类型
Function_body:
用户自定义函数体，是一系列实现函数功能的Transact—SQL语句的集合。
Expression:
用户自定义函数的返回值。
数据文件;主数据文件名称的默认后缀是mdf，次数据文件名称的默认后缀是ndf
系统数据库
系统数据库由master、model、msdb、tempdb和隐藏的Resource数据库组成
（1）master数据库：
master数据库用于记录SQLServer2008R2中所有服务器级别的对象。
（2）model数据库：
model数据库是一个模板数据库。
每当创建新的数据库时（包括系统数据库tempdb），就会创建model数据库的一个副本，并以新创建数据库的名称重命名该副本。
（3）msdb数据库;可以把msdb数据库看作是SQLServer代理的数据库，这是因为SQLServer2008R2代理广泛地使用msdb数据库存储自动化作业定义、作业计划、操作员定义以及警报定义。
（4）tempdb数据库SQLServer2008R2使用tempdb数据库临时性地存储数据。
在SQLServer2008R2操作中，tempdb数据库有广泛的运用，因此要确保SQLServer2008R2数据库有效的操作，就需要仔细计划和评估tempdb数据库的规模和位置。
（5）Resource数据库它是一个只读数据库，包含SQLServer2008R2实例使用的所有系统对象。
T-SQL语言创建数据库
Createdatabase语句中参数说明：
database_name：
新数据库的名称。
ON：
指定用来存储数据库数据部分的磁盘文件（数据文件）。
LOGON：
指定显式定义用来存储数据库日志的磁盘文件（日志文件）。
NAMElogical_file_name：
指定文件的逻辑名称。
FILENAME：
指定操作系统（物理）文件名称。
SIZE：
指定文件的大小。
MAXSIZE：
指定文件可增大到的最大大小。
FILEGROWTH：
指定文件的自动增量。
也可以指定百分比。
exet
1.sys.databases：
可以查看该服务器上所有数据库的基本信息。
2.sp_helpdb：
可以查看该服务器上所有数据库或指定单个数据库的基本信息。
如图4-9所示存储过程查看所有数据库。
使用sp_helpdbJdglSys命令可以查看单个数据库的详细信息。
使用sp_helpdbJdglSys命令可以查看单个数据库的详细信息。
3.sp_helpfile：
显示当前数据库关联的文件的名称及属性。
如图4-11所示。
其中，use语句用来改变当前数据库，把JdglSys数据库作为当前查询的数据库。
4.sp_helpfilegroup系统存储过程查看JdglSys数据库中的所有文件组或某一个文件组的信息。
5.sp_spaceused系统存储过程显示由整个数据库保留和使用的磁盘空间。
也可以使用exetsp_detach_db系统存储过程来分离数据库
例如：
将JdglSys数据库的空间缩减至可用剩余空间为60%。
语句为：
DBCCSHRINKDATABASE（'JdglSys',60）;
使用T-SQL语言删除数据库
删除数据库也可以是用DROPDATABASE语句来删除。
DROPDATABASE语句的语法是：
DROPDATABASEdatabase_name[,…n];
例如：
使用DROPDATABASE语句删除数据库Studentdb。
语句为：
DROPDATABASEStudentdb;
完整备份数据库JdglSys到指定的备份设备JdglSys_backup上。
语句为：
backupdatabaseJdglSystoJdglSys_backup;
使用Transact-SQL语言创建数据表时，主要是用到CREATETABLE语句。
CREATETABLE数据表名字（
字段名称1（对字段的定义部分）
字段名称2（对字段的定义部分）
......
字段名称n（对字段的定义部分）
）；
对数据表进行字段添加的语法如下：
ALTERTABLE数据表名ADD字段名字段类型是否为空
向客房类型表中添加一个字段【备注】，类型为nchar，长度为10。
USEhotel
GO
ALTERTABLE客房类型ADD[备注]nchar（10）NULL
GO
对数据表进行字段类型修改的语法如下：
ALTERTABLEtable_nameALTERCOLUMNcolumn_namenew_type_name
客房类型的【备注】属性列的字段类型为nchar[10]，现在把它修改为ntext类型。
具体的Transact-SQL语句如下所示：
USEhotel
GO
ALTERTABLE客房类型ALTERCOLUMN[备注]ntext
GO
对数据表进行字段删除的语法如下：
ALTERTABLE数据表名DROPCOLUMN字段名
对客房类型表删除刚才添加的字段【备注】。
具体的Transact-SQL语句如下所示：
USEhotel
GO
ALTERTABLE客房类型DROPCOLUMN[备注]
GO
插入数据
一般地，使用INSERT语句一次只能插入一行数据。
INSERT语句的基本语法形式如下：
INSERTINTOtable_or_view_name（column_list）VALUES（expression）
在客房类型表中插入一行数据，类型编号为“P004”,名称为“普通三人间”，面积为“45”，床位为“3”，价格为“550”，空调为“1”，电视为“1”，卫生间为“0”。
具体的Transact-SQL语句如下所示：
INSERTINTO[hotel].[dbo].[客房类型]
（[类型编号],[名称],[面积],[床位],[价格],[空调],[电视],[卫生间]）
VALUES
（'P004','普通三人间',45,3,550,1,1,0）
GO
更新数据
可以使用UPDATE语句更新表中已经存在的数据。
UPDATE语句既可以一次更新一行数据，也可以一次更新许多行，甚至可以一次更新表中的全部数据行。
UPDATE语句的基本语法形式如下：
UPDATEtable_or_view_name
SETcolumn_name=expression,…
WHEREsearch_condition
由于物价上涨，现在对酒店所有类型房间的价格统一上调50元。
具体的Transact-SQL语句如下所示：
USEhotel
GO
UPDATE[客房类型]SET[价格]=[价格]+50
GO
由于物价上涨，现在对酒店的豪华商务房的价格统一上调50元。
具体的Transact-SQL语句如下所示：
USEhotel
GO
UPDATE[客房类型]SET[价格]=[价格]+50
WHERE[名称]='豪华商务房'
GO
删除数据
当表中的数据不再需要时，可以将其删除。
一般情况下，使用DELETE语句删除数据。
DELETE语句可以从一个表中删除一行或多行数据。
删除数据的DELEE语句的基本语法形式如下：
DELETE
FROMtable_or_name
WHEREsearch_condition
删除客房类型数据表中【面积】=30平方米的房间类型数据。
具体的Transact-SQL语句如下所示：
USEhotel
GO
DELETEFROM[客房类型]WHERE[面积]=30
GO
约束
ALTERTABLE[客房类型]
ADDCONSTRAINTcol_b_def
DEFAULT'1'FOR[电视];
CHECK约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。
CHECK约束的两种基本语法形式如下：
CONSTRAINTconstraint_nameCHECK（logical_expression）
CHECK（logical_expression）
UNIQUE约束指定表中某一个列或多个列不能有相同的两行或两行以上的数据存在。
这种约束通过实现唯一性索引来强制实体完整性。
UNIQUE约束的4种基本语法形式如下所示：
CONSTRAINTconstraint_nameUNIQUE
UNIQUE
CONSTRAINTconstraint_nameUNIQUE（column_list）
UNIQUE（column_list）
虽然SELECT语句的完整语法比较复杂，但其主要子句可归纳如下：
SELECT<目标列名表>
FROM<数据源表>
[WHERE<查询条件>]
[GROUPBY<分组列>]
[HAVING<组选择条件>]
[ORDERBY<排序列>[ASC|DESC]]
select
（1）SELECT<目标列名表>，称为SELECT子句。
用于指定整个查询结果表中包含的列。
假定已经执行完FROM、WHERE、GROUPBY、HAVING子句，从概念上来说得到了一个表，若将该表称为T，从T表中选择SELECT子句指定的目标列即为整个查询的结果表。
（2）FROM<数据源表>，称为FROM子句。
用于指定整个查询语句用到的一个或多个基本表或视图，是整个查询语句的数据来源，通常称为数据源表。
（3）WHERE<查询条件>，称为WHERE子句。
用于指定多个数据源表的连接条件和单个源表中行的筛选或选择条件。
如果只有一个源表，则没有表间的连接条件，只有行的筛选条件。
（4）GROUPBY<分组列>，称为GROUPBY子句。
假定已经执行完FROM、WHERE子句，则从概念上来说得到了一个表，若将该表称为T1表，则GROUPBY用于指定T1表按哪些列（称为分组列）进行分组，对每一个分组进行运算，产生一行。
所有这些行组成一个表，不妨把它称为T2表，T2表实际上是一个组表。
（5）HAVING<组选择条件>，称为HAVING子句。
与GROUPBY子句一起使用。
用于指定组表T2表的选择条件，即选择T2表中满足<组选择条件>的行，组成一个表。
（6）ORDERBY<排序列>，称为ORDERBY子句。
若有ORDERBY子句，则用于指定查询结果表T中按指定列进行升序或降序排序（默认情况下按升序排列），得到整个查询的结果表。
【例6-1】查询全体客户的身份证号和姓名。
SELECT
身份证号,姓名
FROM客户信息;
【例6-2】查询全体客户的身份证号，姓名和电话。
SELECT身份证号,姓名,电话
FROM客户信息;
或 SELECT*
FROM客户信息；
【例6-3】查询所有客户的姓名，并去掉重复行。
SELECTDISTINCT姓名
FROM客户信息;
【例6-3】查询所有客户的姓名，并去掉重复行。
SELECTDISTINCT姓名
FROM客户信息;
【例6-4】查询所有客房类型的类型编号和名称，并将价格上涨5%。
SELECT
类型编号,名称,价格*1.05
FROM客房类型;
【例6-5】查询所有客房类型的类型编号和名称，将价格上涨5%并显示为“新价格”。
SELECT类型编号,名称,价格*1.05AS'新价格'
FROM客房类型;
或SELECT 类型编号,名称,价格*1.05'新价格'
FROM客房类型;
或SELECT 类型编号,名称,'新价格'=价格*1.05
FROM客房类型;
【例6-6】查询所有价格大于300的客房类型编号，名称，面积，床位和价格信息。
SELECT类型编号,名称,面积,床位,价格
FROM 客房类型
WHERE 价格>300;
between
【例6-7】查询价格在400～500之间的客房类型编号，名称，面积和床位信息。
SELECT类型编号,名称,面积,床位
FROM 客房类型
WHERE 价格BETWEEN400AND500;
等价于：
SELECT类型编号,名称,面积,床位
FROM 客房类型
WHERE 价格>=400AND价格<=500;
【例6-8】查询价格不在400～500之间的客房类型编号，名称，面积和床位信息。
SELECT类型编号,名称,面积,床位
FROM客房类型
WHERE价格NOTBETWEEN400AND500;
等价于：
SELECT类型编号,名称,面积,床位
FROM客房类型
WHERE价格<400OR价格>500;
in
【例6-9】 查询价格为160，260，560的客房类型编号和名称。
SELECT类型编号,名称
FROM 客房类型
WHERE价格IN （160,260,560）;
【例6-10】查询价格不是160，260，560的客房类型编号和名称。
SELECT类型编号,名称
FROM 客房类型
WHERE价格NOTIN （160,260,560）;
like
【例6-11】查找名字包含３个字符，且中间为‘毅’的客户身份证号和姓名。
SELECT身份证号,姓名
FROM客户信息
WHERE姓名 LIKE‘_毅_’;
【例6-12】 查找姓’王’的客户身份证号和姓名。
SELECT身份证号,姓名
FROM客户信息
WHERE姓名 LIKE‘王%’;
【例6-13】查找姓’王’,’张’或’李’的客户身份证号和姓名。
SELECT身份证号,姓名
FROM客户信息
WHERE姓名 LIKE'[王张李]%';
【例6-14】查找不姓’王’或’张的客户身份证号和姓名。
SELECT身份证号,姓名
FROM客户信息
WHERE姓名LIKE'[^王张李]%';
NULL
【例6-15】查询没有登记面积的客房类型编号和名称。
SELECT类型编号,名称
FROM客房类型
WHERE面积ISNULL;
【例6-15】查询没有登记面积的客房类型编号和名称。
SELECT类型编号,名称
FROM客房类型
WHERE面积ISNULL;
带条件查询where（andnotor）
NOT运算符表示逻辑“非”关系。
用于对搜索条件的逻辑值求反。
AND运算符表示逻辑“与”关系。
当使用AND运算符组合两个逻辑表达式时，只有当两个表达式均为TRUE时返回TRUE。
OR运算符表示逻辑“或”关系。
当使用OR运算符组合两个逻表达式时，只要其中一个表达式的条件为TRUE，结果便返回TRUE。
【例6-17】查询价格为160，260，560的客房类型编号和名称。
SELECT类型编号,名称
FROM客房类型
WHERE价格=160OR价格=260OR价格=560;
【例6-18】查询价格既不是260也不是560的客房类型编号和名称。
SELECT类型编号,名称
FROM客房类型
WHERE价格<>260AND价格<>560;
基于函数
COUNT（*）求表中或组中记录的个数
COUNT（<列名>）求不是NULL的列值个数
SUM（<列名>）求该列所有值的总和（必须是数值型列）
AVG（<列名>）求该列所有值的平均值（必须是数值型列）
MAX（<列名>）求该列所有值的最大值（必须是数值型列）
MIN（<列名>）求该列所有值的最小值（必须是数值型列）
【例6-19】 求客户的总人数。
SELECTCOUNT（*）'总人数'
FROM客户信息;
【例6-20】求客房的最高价格、最低价格和平均价格。
SELECTMAX（价格）'最高价格',MIN（价格）'最低价格',AVG（价格）'平均价格'
FROM客房类型;
查询结果处理
【例6-21】查询不同种类的客房类型编号，名称和价格，结果按价格降序排列。
SELECT类型编号,名称,价格
FROM客房类型
ORDERBY价格DESC;
【例6-22】查询不同种类的客房类型编号和名称，结果按价格降序排列，价格相同的情况下按面积升序排列。
SELECT类型编号,名称,价格,面积
FROM客房类型
ORDERBY价格DESC,面积ASC;
分组查询
GROUPBY子句在WHERE子句后边。
一般形式为：
GROUPBY<分组列>[，…n]
其中<分组列>是分组的依据。
分组原则是<分组列>的列值相同，就为同一组。
当有多个<分组列>时，则先按第一个列值分组，然后对每一组再按第二个列值进行分组，依此类推。
【例6-23】查询同一客户入住本酒店的次数。
SELECT身份证号,COUNT（*）'次数'
FROM业务记录
GROUPBY身份证号;
HAVING
【例6-24】查询入住本酒店次数大于等于两次的客户身份证号和入住次数。
SELECT身份证号,COUNT（*）'入住次数'
FROM业务记录
GROUPBY身份证号
HAVINGCOUNT（*）>=2;
内连接是比较常用的一种数据连接查询方式。
它使用比较运算符进行多个基表间的数据的比较操作，并列出这些基表中与连接条件相匹配的所有的数据行。
一般用INNERJOIN或JOIN关键字来指定内连接，又可分为等值连接、非等值连接和自然连接３种。
内连接的语法格式为：
SELECT<目标列名表>
FROM表1INNERJOIN表2[ON连接条件]
[WHERE查询条件]
[ORDERBY排序条件]
【例6-25】查询当前入住本酒店的客户信息及入住情况（用WHERE子句实现）。
SELECT客房业务.*,客户信息.*
FROM客房业务,客户信息
WHERE客房业务.身份证号=客户信息.身份证号;
【例6-26】查询当前入住本酒店的客户信息和入住情况（用INNERJOIN的方式实现）。
SELECT客房业务.*,客户信息.*
FROM客户信息INNERJOIN客房业务
ON客户信息.身份证号=客房业务.身份证号;
内连接
非等值连接
非等值连接查询就是在连接条件中使用除了等号之外的比较运算符，来比较连接列的列值。
在非等值连接中，可以使用的比较运算符有：
>，<，>=，<=，<>，也可以使用范围运算符BETWEEN。
【例6-27】查询入住过本酒店且单次消费金额大于200的客户身份证号和姓名。
SELECT业务记录.*,客户信息.*
FROM客户信息INNERJOIN业务记录
ON客户信
升级会员 