建立数据库和表 (2).ppt

Ch2 建立数据库,1,本章内容,1.建立数据库,2,SQL server2008客户端工具,SQL Server Management Studio 是为 SQL Server 数据库管理员和开发人员提供的新工具。它提供了用于数据库管理的图形工具和功能丰富的开发环境。完成数据库管理与维护的大部分工作。如配置系统环境和管理SQL Server服务器，显示、建立、管理所有的SQL Server对象，备份和恢复数据库，复制数据库，管理用户帐户等。输入和执行Transact-SQL语句，并且迅速查看这些语句的结果，以分析和处理数据库中的数据。,3,SQL server2005系统数据库简介,1).master(主要的)是系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统及用户数据库的相关信息。建议不要在 master 数据库中创建任何用户对象。如表、视图、存储过程或触发器等。2).model（模型的）是所有用户数据库和tempdb（临时）数据库的模板数据库，它含有master数据库所有系统表的子集，这些系统表是每个用户定义数据库需要的。,4,3).msdb 是代理服务数据库，用于为警报、任务调度和记录操作员的操作提供存储空间。4).tempdb（临时）用于为所有的临时表、临时存储过程提供存储空间，还用于任何其它的临时存储要求。例如存储SQL Server生成的工作表。tempdb数据库是全局资源，所有连接到系统的用户的临时表和存储过程都存储在该数据库中。在SQL Server 每次启动时都重新创建，因此该数据库在系统启动时总是干净的，临时表和存储过程在连接断开时自动除去。,5,自带的示例数据库,5).AdventureWorks数据库（示例）AdventureWorks数据库:以一家大型跨国生产公司为模型，该公司生产金属和复合材料的自行车，产品远销北美、欧洲和亚洲市场。该数据库及其中的表经常在帮助文档和教程所介绍的示例中使用。默认情况下不安装 AdventureWorks数据库，可以从 Microsoft 下载中心下载并安装。,6,1.建立数据库,SQL Server中每个数据库由一组操作系统文件组成。数据库中的所有数据、对象和数据库操作日志都存储在这些文件中。数据库文件可以分为三类：主数据库文件(.mdf)次数据库文件(.ndf)事务日志文件(.ldf),7,1.1 数据库文件,主数据库文件(Primary Database file)每个数据库有且仅有一个主数据库文件，主数据库文件用来存储数据库的启动信息和部分或全部数据。一个数据库可以有1到多个数据库文件，其中只有一个文件为主数据库文件。主数据库文件的文件扩展名为mdf，如：try1.mdf。次数据库文件(Secondary Database File)用于存储主数据库文件中未存储的剩余数据和数据库对象。一个数据库可以没有次数据库文件，也可以有多个次数据库文件。次数据库文件的文件扩展名为ndf 如：try1_2.ndf。事务日志文件(Transcation Log File)用于存储数据库的更新情况等事务日志信息。当数据库损坏时，可以利用事务日志文件恢复数据库。一个数据库可以有1到多个事务日志文件。事务日志文件的扩展名为ldf 如：try1_log.ldf。,8,1.2 创建数据库,创建数据库的过程实际上是确定数据库的名称、设计数据库所占用的存储空间和文件的存放位置。创建数据库的途径:SQL Server Management Studio,两种方法,使用管理工具,使用Transact-SQL语句,9,使用管理工具创建数据库,创建mydb1数据库，文件存放在C盘db下,10,数据库名称数据库的所有者,默认为登录用户,数据库文件存储位置,添加此数据库文件,11,使用SQL命令CREATE DATABASE mydb2 ON PRIMARY-在主文件组创建,PRIMARY可以省略(NAME=mydb2,FILENAME=c:dbmydb2.mdf,SIZE=3072KB,FILEGROWTH=1024KB)LOG ON-创建日志文件(NAME=mydb2_log,FILENAME=c:dbmydb2_log.ldf,SIZE=1024KB,FILEGROWTH=10%),创建mydb2数据库，文件存放在C盘db下,12,练习：,1、创建一个mydb1数据库，位置“D:mydb1”,初始容量8M，最大容量16M，文件增长量为5%。CREATE DATABASE mydb1 ON(NAME=mydb1,FILENAME=d:mydb1.mdf,SIZE=8MB,MAXSIZE=16MB,FILEGROWTH=5%)2、增加一个“D:mydb_log1”日志文件，要求初始容量为2MB,最大容量50MB，文件增长量10%。CREATE DATABASE mydb_log1 ADD LOG FILE(NAME=mydb_log1,FILENAME=D:mydb_log1,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=10%),13,1.3 数据库包含的数据对象,在一个SQL Server数据库中，除了包含基本表以外，还包含了与这些基本表相关的多种对象，如：视图、索引、存储过程和触发器等等。目的是为执行与数据有关的活动提供支持。,14,1.表在数据库中，所有的数据存放在表中，表由行(记录)和列(字段)组成，一个数据库可以包含多个表。2.视图视图是由查询数据表产生的结果，是一种虚构的表。视图把表中的部分数据映射出来供用户使用，这样可以防止所有的用户直接对表进行操作而导致系统的性能和安全性的下降。3.存储过程一个存储过程实际上是由一组SQL语句组成的完成特定功能的程序。存储过程在服务器端被编译后可以反复执行。4索引索引是对表中的一个或多个列的值进行排序的结构。可以利用索引提高对数据库表中的特定信息的访问速度。,15,5.约束约束是一种定义自动强制数据库完整性的方式。约束定义了关于列中允许值的规则。例如，强制定义某成绩列的值只能在0到100之间。6.默认值如果在插入行时没有指定该行中某列的值，那么使用默认值可以指定该列自动使用的值。例如，定义某“性别”列的默认值为“男”，则插入某学生信息时，如果没有指定其性别，则自动采用定义的默认值“男”。7.触发器触发器由一组SQL语句组成，当对表或视图进行某种操作（添加、删除或修改）时，这组命令会自动执行。,16,8.用户用于定义允许访问当前数据库的用户及其权限。9.角色角色定义了一组具有相同权限的用户。,17,整数型bit 1 或 0 的整数数据。Tinyint（极小整数）从 0 到 255 的整数数据。Smallint（小整数）从-215(-32,768)到 215-1(32,767)的整数数据。int 从-231(-2,147,483,648)到 231-1(2,147,483,647)的整型数据（所有数字）。bigint 从-263(-9223372036854775808)到 263-1(9223372036854775807)的整型数据（所有数字）。,2、数据类型,18,浮点数型：decimal（小数）从-1038+1 到 1038 1 的固定精度和小数位的数字数据。例如：decimal(5,2)总长度5位（不含小数点），2位小数。numeric（数值）功能上等同于 decimal。货币型：Money（货币）货币数据值介于-263(-922,337,203,685,477.5808)与 263-1(+922,337,203,685,477.5807)之间，精确到货币单位的千分之十，小数点后4位。smallmoney 货币数据值介于-214,748.3648 与+214,748.3647 之间，精确到货币单位的千分之十。,19,近似数字real 从-3.40E+38 到 3.40E+38 的浮点精度数字float 从-1.79E+308 到 1.79E+308 的浮点精度数字时间日期型smalldatetime 从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟datetime 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。,20,字符串char 固定长度的ASCII字符数据，最大长度为 8,000 个字符。例如：char（5），占据5个字节。varchar 可变长度的ASCII 数据，最长为 8,000 个字符。例如：varchar（5），占据1-5个字节。text 可变长度的ASCII 数据，最大长度为 231-1(2,147,483,647)个字符。nchar 固定长度的 二字节ASCII码数据，最大长度为 4,000 个字符。nvarchar 可变长度 Unicode 数据，其最大长度为 4,000 字符。ntext 可变长度 Unicode 数据，其最大长度为 230-1(1,073,741,823)个字符。,21,二进制binary 固定长度的二进制数据，其最大长度为 8,000 个字节。varbinary 可变长度的二进制数据，其最大长度为 8,000 个字节。image 可变长度的二进制数据，其最大长度为 231-1(2,147,483,647)个字节。主要用于图片、视频、声音等。,22,其它数据类型uniqueidentifier 全局唯一标识符(GUID)使用newid()自动生成。cursor 游标的引用。sql_variant 一种存储 SQL Server 支持的各种数据类型（text、ntext、timestamp 和 sql_variant 除外）值的数据类型。table 一种特殊的数据类型，存储供以后处理的结果集。timestamp 数据库范围的唯一数字，每次更新行时也进行更新。,23,3.建立数据表使用管理工具SSMS创建表,列的常用属性列名列的数据类型是否允许为空（NULL或NOT NULL）主键（PRIMARY KEY）默认值（DEFAULT）,24,设置自动增加的标识字段,25,使用CREATE TABLE语句在数据库“mydb1”中创建“学生”表。CREATE TABLE 学生(学号 char(10)PRIMARY KEY,姓名 varchar(50)NOT NULL,性别 bit DEFAULT(0),出生日期 smalldatetime,班级 char(8)NOT NULL,入学成绩 float DEFAULT(0),所属院系 tinyint NOT NULL),3.建立数据表使用SQL语句创建表,26,删除表,在查询窗口中执行该语句，建立表结构。使用 drop table 可删除表,练习：创建”学生表”,练习：删除”学生表”,27,4、操作数据插入、删除、修改,插入数据INSERT INTO 表名(列名1,列名2,.,列名n)VALUES(值1,值2,.,值n)例如：INSERT INTO 院系 VALUES(5,水声,0,0,王亮 INSERT INTO 院系(院系编号,院系名称)VALUES(6,计算机学院),所有字段,部分字段,28,向教师表插入数据,向教师表中插入6条记录（4人属于5系，2人是6系的）：insert into 教师 values(t05001,王慧,女,35,讲师,5)insert into 教师 values(t05002,张绍华,男,45,副教授,5)insert into 教师 values(t05003,李树生,男,55,教授,5)insert into 教师 values(t05004,刘华,男,25,助教,5)insert into 教师 values(t06001,赵树龙,男,31,讲师,6)insert into 教师 values(t06002,李建,男,42,副教授,6)若尝试插入下面3条记录：insert into 教师 values(t07001,王建新,男,40,副教授,7)insert into 教师 values(t06002,李佩琦,女,22,助教,6)insert into 教师 values(t06003,焦晓山,女,17,无,6)结果怎样？原因？,被拒绝，违反了完整性约束,29,注意：,在插入数据时，还需要考虑到表的约束和规则等因素，如果插入的数据违反表约束或规则，则无法正常插入数据。例如：不允许设置标识列的值不允许向唯一性约束列中插入相同的数据不能违反检查约束不能与绑定到列的规则冲突不能违反外部键约束“mydb1”数据库中的各个数据表建立和插入数据时，顺序应该是先被参照的表（主键表），然后是参照表（外键表）。,30,向学生表插入记录insert into 学生 values(2009051101,李珊,1,1988-3-25,20090511,598,5),练习6：（1）类似地，依次地向学生表、课程表、选课表插入记录。每个表的记录数要求在5条以上。（2）注意各字段数据要与定义的数据类型一致。,31,修改数据语法：UPDATE 表名 SET 列名1=值1,列名2=值2,.,列名n=值n WHERE 更新条件 例如：update 教师 set 年龄=38 where 教师号=t05001update 教师 set 年龄=年龄+1 where 所在院系=6请尝试：update 教师 set 所在院系=8 where 教师号=t05001update 教师 set 性别=难 where 教师号=t05001update 教师 set 年龄=68 where 教师号=t05001结果如何？原因？,4 操作数据插入、删除、修改,修改一条记录,一次修改多条记录,32,删除记录DELETE FROM 表名 WHERE 删除条件例如：delete from 教师 where 职称=助教 delete from 教师 where 年龄50尝试删除被参照数据表（主键表）的数据记录，如删除院系表的一条记录，观察结果，分析原因。delete from 院系 where 院系编号=6当6系还有教师、学生时，根据系统的设置，会有不同的处理方式：正常删除，但可能造成数据不一致（没有设置外键约束时）；拒绝删除（有外键约束时）；级联删除（需要设置。删除6系的同时，删除所有6系教师和学生）。删除表中全部记录 delete from 表名,或者使用SQL命令进行设置,再或者修改外键的insert和update规范,4 操作数据插入、删除、修改,33,create table 教师(教师号 char(6)primary key,姓名 char(10)not null,性别 char(2)check(性别 in(男,女),年龄 tinyint check(年龄 between 18 and 65),职称 char(6)check(职称 in(教授,副教授,讲师,助教,工人,其他),所在院系 tinyint foreign key references 院系(院系编号)ON DELETE CASCADE/*当删除院系表中的元组造成了与教师表不一致时拒绝删除*/ON UPDATE NO ACTION/*当更新院系表中的院系编号时，级联更新教师表中相应的元组*/),4 操作数据插入、删除、修改,34,CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table|view(column ASC|DESC,.n)WITH,.n ON filegroup:=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB,CREATE INDEX：创建索引,35,参数说明：UNIQUE:为表或视图创建唯一索引,不允许存在索引值相同的两行。CLUSTERED:为表或视图创建一个聚集索引。NONCLUSTERED:创建一个非聚集索引。index_name:索引名。table:包含要创建索引的列的表。可以选择指定数据库和表所有者。View:要建立索引的视图的名称。Column:应用索引的列。指定两个或多个列名，可为指定列的组合值创建组合索引。ASC|DESC:确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。N:表示可以为特定索引指定多个 columns 的占位符。PAD_INDEX:指定索引中间级中每个页（节点）上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。FILLFACTOR=fillfactor:指定在 SQL Server 创建索引的过程中，各索引页叶级的填满程度。如果某个索引页填满，SQL Server 就必须花时间拆分该索引页，以便为新行腾出空间，这需要很大的开销。IGNORE_DUP_KEY:控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。DROP_EXISTING:指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。STATISTICS_NORECOMPUTE:指定过期的索引统计不会自动重新计算。SORT_IN_TEMPDB:指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。ON filegroup:在给定的 filegroup 上创建指定的索引。,36,课堂作业：,1、简述int、char、varchar三种类型的用法。2、如果一个表中存放备注信息，应该选择什么类型，为什么？3、手动创建stuinfo数据库，用命令创建“学生”表，学生（学号，姓名，性别，年龄）。4、在“学生”表中用命令插入十行信息，然后创建索引。,Thank You!,