数据库原理及应用整理

1、数据库（DataBase，简记为DB）就是一个有结构的、集成的、可共享的统一管理的数据集合。所谓有结构的，指的是数据是按一定的模型组织起来的。数据模型可用数据结构来描述。数据模型决定数据的组织方式、操作方法。理解数据库的前提是理解数据模型。现在的数据库多数是以关系模型来组织数据的。可以简单地把关系模型的数据结构关系理解成为一张二维表。所谓集成的，是指数据库中集中存放着企业各种各样的数据。集中存放的好处是：一个数据只需一个备份，重复存储少，即消除了数据的冗余。没有数据冗余，也就能保证数据的一致。所谓共享，指的是数据库中的数据可以被不同的用户使用。也就是说，每一个用户可以按自己的要求访问相同的数据

2、库。所谓统一管理的，指的是数据库由DBMS统一管理，任何数据访问都是通过DBMS来完成的。数据库管理系统 （DataBase Management System，DBMS） 是用来管理数据库的一种商品化软件。数据定义的功能。DBMS提供数据定义语言（Data Definition Language，DDL）。数据操纵的功能。DBMS提供数据操纵语言（Data Manipulation Language，DML）。数据库的运行管理（管理数据库）功能。提供数据控制语言DCL（data controllanguage），负责数据库在建立、运行和维护时由DBMS统一管理和统一控制。数据库系统DBS （

3、 DataBase System）数据库系统（Database Systems，DBS）是一个带有数据库的计算机系统，它能按照数据库的方式存储和维护数据，并且能够向应用程序提供数据。一个完整的数据库系统由数据库（DB）、数据库管理系统（DBMS）、应用程序、用户和硬件组成。数据库系统的产生和发展（1） 人工管理阶段（50年代初期）（2） 文件系统阶段（50年代后期）（3） 数据库系统阶段（60年代后期开始）数据库系统的特点（1）数据结构化（2）数据的共享性高，冗余度低，易扩充（3）数据独立性高（4）数据由DBMS统一管理和控制数据库的三级模式：外模式、概念模式、内模式。数据独立性是指应用程序不

4、因物理存储的改变而改变。数据独立性包含逻辑独立性和物理独立性。逻辑独立性是指概念模式改变时，外模式保持不变，从而使应用程序保持不变。当数据库的概念模式改变时，DBMS只要改变外模式到模式的映射，就可保持外模式不变。从而使应用程序保持不变。物理独立性是指内模式改变时，概念模式保持不变。因为当内模式改变（如数据库的存储设备或存储结构改变）时，DBMS只要通过改变概念模式到内模式映射，从而使概念模式保持不变，实现了数据的物理独立性。decimal(2,1)，有效长度为2，小数位占1位。 提供了CHAR、VARCHAR、TEXT、NCHAR、NVARCHAR和NTEXT 6种数据类型。前3种数据类型是

5、非Unicode字符数据，后3种是Unicode字符数据。Datetime: 用于存储日期和时间的结合体。定义可变长度字符型变量name ,长度为8 可变长度的字符型变量sex,长度为2，DECLARE name varchar(8),sex varchar 小整型变量age age smallint利用CAST函数可以将某一种数据类型强制转换为另一种数据类型，其语法格式如下： CAST（expression AS data_type）CONVERT函数允许用户把表达式从一种数据类型转换为另一种数据类型，并且还在日期的不同显示格式之间进行转换，其语法格式如下： CONVERT（data_typ

6、e(length),expression,style）可使用CREATE FUNCTION语句创建函数，使用ALTER FUNCTION语句修改函数，使用DROP FUNCTION语句删除函数。函数用户自定义函数Schema_name:用户自定义函数所属的模式名称Function_name:用户自定义函数的名称parameter_name:用户自定义函数的参数名称Parameter_data_type:参数的数据类型Default:参数的默认值Return_data_type:用户自定义函数返回值的数据类型Function_body:用户自定义函数体，是一系列实现函数功能的TransactSQ

7、L语句的集合。Expression:用户自定义函数的返回值。数据文件;主数据文件名称的默认后缀是mdf，次数据文件名称的默认后缀是ndf系统数据库 系统数据库由master、model、msdb、tempdb和隐藏的Resource数据库组成（1） master数据库：master数据库用于记录SQL Server 2008 R2中所有服务器级别的对象。（2） model数据库：model数据库是一个模板数据库。每当创建新的数据库时(包括系统数据库tempdb)，就会创建model数据库的一个副本，并以新创建数据库的名称重命名该副本。（3） msdb数据库;可以把msdb数据库看作是SQL S

8、erver代理的数据库，这是因为SQL Server 2008 R2代理广泛地使用msdb数据库存储自动化作业定义、作业计划、操作员定义以及警报定义。（4） tempdb数据库 SQL Server 2008 R2使用tempdb数据库临时性地存储数据。在SQL Server 2008 R2操作中，tempdb数据库有广泛的运用，因此要确保SQL Server 2008 R2数据库有效的操作，就需要仔细计划和评估tempdb数据库的规模和位置。（5）Resource数据库它是一个只读数据库，包含SQL Server 2008 R2实例使用的所有系统对象。 T-SQL语言创建数据库Create

9、database语句中参数说明：database_name：新数据库的名称。ON ：指定用来存储数据库数据部分的磁盘文件（数据文件）。LOG ON ：指定显式定义用来存储数据库日志的磁盘文件（日志文件）。NAME logical_file_name： 指定文件的逻辑名称。FILENAME ：指定操作系统（物理）文件名称。SIZE ：指定文件的大小。MAXSIZE ：指定文件可增大到的最大大小。 FILEGROWTH ：指定文件的自动增量。也可以指定百分比。exet1. sys.databases：可以查看该服务器上所有数据库的基本信息。2. sp_helpdb：可以查看该服务器上所有数据库或指

10、定单个数据库的基本信息。如图4-9所示存储过程查看所有数据库。 使用sp_helpdb JdglSys命令可以查看单个数据库的详细信息。使用sp_helpdb JdglSys命令可以查看单个数据库的详细信息。3. sp_helpfile：显示当前数据库关联的文件的名称及属性。如图4-11所示。其中，use语句用来改变当前数据库，把JdglSys数据库作为当前查询的数据库。4. sp_helpfilegroup系统存储过程查看JdglSys数据库中的所有文件组或某一个文件组的信息。5. sp_spaceused系统存储过程 显示由整个数据库保留和使用的磁盘空间。也可以使用exet sp_deta

11、ch_db系统存储过程来分离数据库例如：将JdglSys数据库的空间缩减至可用剩余空间为60%。语句为：DBCC SHRINKDATABASE(JdglSys,60);使用T-SQL语言删除数据库 删除数据库也可以是用DROP DATABASE语句来删除。 DROP DATABASE语句的语法是： DROP DATABASE database_name,n; 例如：使用DROP DATABASE语句删除数据库Studentdb。 语句为： DROP DATABASE Studentdb;完整备份数据库JdglSys到指定的备份设备JdglSys_backup上。 语句为： backup dat

12、abase JdglSys to JdglSys_backup;使用Transact-SQL语言创建数据表时，主要是用到CREATE TABLE语句。CREATE TABLE 数据表名字（字段名称1 (对字段的定义部分)字段名称2 (对字段的定义部分) .字段名称n (对字段的定义部分)）；对数据表进行字段添加的语法如下：ALTER TABLE 数据表名 ADD 字段名 字段类型 是否为空向客房类型表中添加一个字段【备注】，类型为nchar，长度为10。USE hotelGOALTER TABLE 客房类型 ADD 备注 nchar(10) NULLGO对数据表进行字段类型修改的语法如下：AL

13、TER TABLE table_name ALTER COLUMN column_name new_type_name客房类型的【备注】属性列的字段类型为nchar10，现在把它修改为ntext类型。具体的Transact-SQL语句如下所示：USE hotelGOALTER TABLE 客房类型 ALTER COLUMN 备注 ntext GO对数据表进行字段删除的语法如下：ALTER TABLE 数据表名 DROP COLUMN 字段名对客房类型表删除刚才添加的字段【备注】。具体的Transact-SQL语句如下所示：USE hotelGOALTER TABLE 客房类型 DROP COL

14、UMN 备注 GO插入数据一般地，使用INSERT语句一次只能插入一行数据。INSERT语句的基本语法形式如下：INSERT INTO table_or_view_name (column_list) VALUES (expression)在客房类型表中插入一行数据，类型编号为“P004”,名称为“普通三人间”，面积为“45”，床位为“3”，价格为“550”，空调为“1”，电视为“1”，卫生间为“0”。具体的Transact-SQL语句如下所示：INSERT INTO hotel.dbo.客房类型 (类型编号,名称 ,面积 ,床位 ,价格 ,空调 ,电视 ,卫生间) VALUES (P004

15、,普通三人间 ,45 ,3 ,550 ,1 ,1 ,0)GO更新数据可以使用UPDATE语句更新表中已经存在的数据。UPDATE语句既可以一次更新一行数据，也可以一次更新许多行，甚至可以一次更新表中的全部数据行。UPDATE语句的基本语法形式如下：UPDATE table_or_view_name SET column_name = expression, WHERE search_condition 由于物价上涨，现在对酒店所有类型房间的价格统一上调50元。具体的Transact-SQL语句如下所示：USE hotelGOUPDATE 客房类型 SET 价格 = 价格+50GO由于物价上涨，

16、现在对酒店的豪华商务房的价格统一上调50元。具体的Transact-SQL语句如下所示：USE hotelGOUPDATE 客房类型 SET 价格 = 价格+50 WHERE 名称=豪华商务房GO删除数据当表中的数据不再需要时，可以将其删除。一般情况下，使用DELETE语句删除数据。DELETE语句可以从一个表中删除一行或多行数据。删除数据的DELEE语句的基本语法形式如下：DELETE FROM table_or_name WHERE search_condition 删除客房类型数据表中【面积】=30平方米的房间类型数据。具体的Transact-SQL语句如下所示：USE hotelGOD

17、ELETE FROM 客房类型 WHERE 面积=30GO约束ALTER TABLE 客房类型ADD CONSTRAINT col_b_defDEFAULT 1 FOR 电视 ;CHECK约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。CHECK约束的两种基本语法形式如下：CONSTRAINT constraint_name CHECK (logical_expression)CHECK (logical_expression)UNIQUE约束指定表中某一个列或多个列不能有相同的两行或两行以上的数据存在。这种约束通过实现唯一性索引来强制实体完整性。UNIQUE约束的4种基

18、本语法形式如下所示：CONSTRAINT constraint_name UNIQUEUNIQUECONSTRAINT constraint_name UNIQUE (column_list)UNIQUE (column_list)虽然SELECT语句的完整语法比较复杂，但其主要子句可归纳如下：SELECT FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC select（1）SELECT ，称为SELECT子句。用于指定整个查询结果表中包含的列。假定已经执行完FROM、WHERE、GROUP BY、HAVING子句，从概念上来说得到了一个表，若将该表称为

19、T，从T表中选择SELECT子句指定的目标列即为整个查询的结果表。（2）FROM ，称为FROM子句。用于指定整个查询语句用到的一个或多个基本表或视图，是整个查询语句的数据来源，通常称为数据源表。（3）WHERE ，称为WHERE子句。用于指定多个数据源表的连接条件和单个源表中行的筛选或选择条件。如果只有一个源表，则没有表间的连接条件，只有行的筛选条件。（4）GROUP BY ，称为GROUP BY子句。假定已经执行完FROM、WHERE子句，则从概念上来说得到了一个表，若将该表称为T1表，则GROUP BY用于指定T1表按哪些列（称为分组列）进行分组，对每一个分组进行运算，产生一行。所有这些

20、行组成一个表，不妨把它称为T2表，T2表实际上是一个组表。（5）HAVING ，称为HAVING子句。与GROUP BY子句一起使用。用于指定组表T2表的选择条件，即选择T2表中满足的行，组成一个表。（6）ORDER BY ，称为ORDER BY子句。若有ORDER BY子句，则用于指定查询结果表T中按指定列进行升序或降序排序（默认情况下按升序排列），得到整个查询的结果表。【例6-1】 查询全体客户的身份证号和姓名。SELECT 身份证号,姓名 FROM 客户信息;【例6-2】查询全体客户的身份证号，姓名和电话。SELECT 身份证号,姓名,电话 FROM 客户信息;或SELECT * FRO

21、M 客户信息；【例6-3】查询所有客户的姓名，并去掉重复行。SELECT DISTINCT姓名FROM 客户信息;【例6-3】查询所有客户的姓名，并去掉重复行。SELECT DISTINCT姓名FROM 客户信息;【例6-4】 查询所有客房类型的类型编号和名称，并将价格上涨5%。SELECT类型编号,名称,价格*1.05FROM 客房类型;【例6-5】查询所有客房类型的类型编号和名称，将价格上涨5%并显示为“新价格”。 SELECT 类型编号,名称,价格*1.05 AS 新价格FROM 客房类型;或 SELECT类型编号,名称,价格*1.05 新价格 FROM 客房类型;或 SELECT类型编

22、号,名称, 新价格=价格*1.05 FROM 客房类型;【例6-6】查询所有价格大于300的客房类型编号，名称，面积，床位和价格信息。SELECT 类型编号,名称,面积,床位,价格FROM客房类型 WHERE价格300;between【例6-7】查询价格在400500之间的客房类型编号，名称，面积和床位信息。SELECT 类型编号,名称,面积,床位FROM客房类型 WHERE价格 BETWEEN 400 AND 500;等价于：SELECT 类型编号,名称,面积,床位FROM客房类型 WHERE价格= 400 AND 价格=500;【例6-8】查询价格不在400500之间的客房类型编号，名称，

23、面积和床位信息。SELECT 类型编号,名称,面积,床位FROM 客房类型 WHERE 价格 NOT BETWEEN 400 AND 500;等价于：SELECT 类型编号,名称,面积,床位FROM 客房类型 WHERE 价格500;in【例6-9】查询价格为160，260，560的客房类型编号和名称。SELECT 类型编号,名称FROM客房类型 WHERE 价格IN(160,260,560); 【例6-10】查询价格不是160，260，560的客房类型编号和名称。SELECT 类型编号,名称FROM客房类型 WHERE 价格 NOT IN(160,260,560);like【例6-11】查找

24、名字包含个字符，且中间为毅的客户身份证号和姓名。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】查询没有登记面积的客房类型编号和名称。SELEC

25、T 类型编号,名称FROM 客房类型WHERE 面积 IS NULL; 【例6-15】查询没有登记面积的客房类型编号和名称。SELECT 类型编号,名称FROM 客房类型WHERE 面积 IS NULL; 带条件查询where（and not or）NOT 运算符表示逻辑“非”关系。用于对搜索条件的逻辑值求反。AND运算符表示逻辑“与”关系。当使用AND运算符组合两个逻辑表达式时，只有当两个表达式均为TRUE时返回TRUE。OR运算符表示逻辑“或”关系。当使用OR运算符组合两个逻表达式时，只要其中一个表达式的条件为TRUE，结果便返回TRUE。【例6-17】查询价格为160，260，560的客

26、房类型编号和名称。SELECT 类型编号,名称FROM 客房类型 WHERE 价格=160 OR 价格=260 OR 价格=560;【例6-18】查询价格既不是260也不是560的客房类型编号和名称。SELECT 类型编号,名称FROM 客房类型WHERE 价格260 AND 价格560; 基于函数COUNT(*)求表中或组中记录的个数COUNT()求不是NULL的列值个数SUM()求该列所有值的总和（必须是数值型列）AVG()求该列所有值的平均值（必须是数值型列）MAX()求该列所有值的最大值（必须是数值型列）MIN()求该列所有值的最小值（必须是数值型列）【例6-19】求客户的总人数。SE

27、LECT COUNT(*) 总人数FROM 客户信息; 【例6-20】求客房的最高价格、最低价格和平均价格。SELECT MAX(价格) 最高价格,MIN(价格) 最低价格,AVG(价格)平均价格FROM 客房类型;查询结果处理【例 6-21】查询不同种类的客房类型编号，名称和价格，结果按价格降序排列。SELECT 类型编号,名称,价格 FROM 客房类型 ORDER BY 价格 DESC;【例 6-22】查询不同种类的客房类型编号和名称，结果按价格降序排列，价格相同的情况下按面积升序排列。SELECT 类型编号,名称,价格,面积 FROM 客房类型 ORDER BY 价格 DESC,面积 A

28、SC; 分组查询GROUP BY子句在WHERE子句后边。一般形式为：GROUP BY ， n 其中是分组的依据。分组原则是的列值相同，就为同一组。当有多个时，则先按第一个列值分组，然后对每一组再按第二个列值进行分组，依此类推。 【例6-23】查询同一客户入住本酒店的次数。SELECT 身份证号,COUNT(*) 次数FROM 业务记录 GROUP BY 身份证号; HAVING【例6-24】查询入住本酒店次数大于等于两次的客户身份证号和入住次数。SELECT 身份证号,COUNT(*) 入住次数 FROM 业务记录GROUP BY 身份证号 HAVING COUNT(*)=2; 内连接是比较

29、常用的一种数据连接查询方式。它使用比较运算符进行多个基表间的数据的比较操作，并列出这些基表中与连接条件相匹配的所有的数据行。一般用INNER JOIN或JOIN关键字来指定内连接，又可分为等值连接、非等值连接和自然连接种。内连接的语法格式为：SELECT FROM 表1 INNER JOIN 表2 ON 连接条件WHERE 查询条件ORDER BY 排序条件【例6-25】查询当前入住本酒店的客户信息及入住情况（用WHERE子句实现）。SELECT 客房业务.*,客户信息.*FROM 客房业务,客户信息WHERE 客房业务.身份证号=客户信息.身份证号;【例6-26】 查询当前入住本酒店的客户信

30、息和入住情况（用INNER JOIN的方式实现）。SELECT 客房业务.*,客户信息.*FROM 客户信息 INNER JOIN 客房业务ON 客户信息.身份证号=客房业务.身份证号;内连接非等值连接非等值连接查询就是在连接条件中使用除了等号之外的比较运算符，来比较连接列的列值。在非等值连接中，可以使用的比较运算符有：，=，=，也可以使用范围运算符BETWEEN。【例6-27】 查询入住过本酒店且单次消费金额大于200的客户身份证号和姓名。SELECT 业务记录.*,客户信息.*FROM 客户信息 INNER JOIN 业务记录ON 客户信息.身份证号=业务记录.身份证号 AND 金额200

31、;其结果如图6-34所示。自然连接自然连接查询是在连接条件中使用比较运算符比较连接列的列值，但它使用选择列表指出查询结果集中所包含的列，并删除连接表中的重复列。在使用自然连接查询时，它为具有相同名称的列自动进行记录匹配。【例6-28】 查询当前入住本酒店的客户信息和入住情况。 SELECT 客户信息.身份证号,客户信息.姓名,客房业务.客房号,客房业务.入住时间 FROM 客户信息 INNER JOIN 客房业务 ON 客户信息.身份证号=客房业务.身份证号;右外连接=匹配行+左边表中不匹配的行其中，缺少的左边表中的列值用NULL表示。【例6-30】查询所有客房的基本信息，如果有客户入住时显示

32、其身份证号。SELECT 客房信息.*, 客房业务.身份证号FROM 客房业务 RIGHT JOIN 客房信息ON 客房业务.客房号= 客房信息.客房号;完全连接的SELECT语句使用FULL OUTER JOIN关键字对两个表进行连接。这种连接方式返回左表和右表中的所有行。当某行在一个表中没有匹配的行时，则另一个表与之相对应列的值为NULL。如果表之间有匹配的行，则整个结果集包含表的数据值。【例6-31】使用完全连接查询客房信息和客房业务两个表。SELECT 客房信息.*, 客房业务.*FROM 客房信息 FULL OUTER JOIN 客房业务ON 客房业务.客房号=客房信息.客房号;子查

33、询指的是一个SELECT查询语句，但它嵌套在SELECT、INSERT、UPDATE、DELETE 语句或其他子查询语句中。通常把外层的SELECT语句叫外查询，内层的SELECT语句叫内查询(或子查询)。子查询要用圆括号括起来，它可以出现在允许使用表达式的任何地方。子查询可分为：非相关子查询和相关子查询。【例6-35】在业务记录表中查询和身份证号为“100100125615896458”的客户同一天入住的客户的身份证号。SELECT 身份证号 FROM 业务记录 WHERE 入住时间=(SELECT 入住时间 FROM 业务记录 WHERE 身份证号=100100125615896458);

34、 例6-36】在业务记录表中查询和身份证号为“100100125615896458”的客户不在同一天入住的客户的身份证号。SELECT 身份证号 FROM 业务记录 WHERE 入住时间=(SELECT 入住时间 FROM 业务记录 WHERE 身份证号=100100125615896458);【例6-39】查询当前住在本酒店，电话号码不是以“135”开头的客户信息。SELECT 身份证号,姓名,电话FROM 客户信息WHERE EXISTS (SELECT * FROM 客房业务WHERE 身份证号=客户信息.身份证号 AND 客户信息.电话 NOT LIKE 135%);【例6-40】查询当前住在本酒店，电话号码是以“135”开头的客户信息。SELECT 身份证号,姓名,电话FROM 客户信息WHERE EXISTS (SELECT * FROM 客房业务WHERE 身份证号=客户信息.身份证号 AND 客户信息.电话 LIKE 135%);聚集索引是一种数据表的物理顺序与索引顺序相同的索引。建立索引时，系统将对表的物理数据页中的数据按列进行排列，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的。聚集索引的叶级和非叶级构成了一个特殊类型的B+树结构.非聚集索引是