数据库和表的建立与增删数据.ppt

《数据库和表的建立与增删数据.ppt》由会员分享，可在线阅读，更多相关《数据库和表的建立与增删数据.ppt（70页珍藏版）》请在三一办公上搜索。

1、一 数据库和表的建立与增删数据,移动应用教研室 殷晓春,数据库设计与实现,考核要求：平时成绩（课堂提问/学生作业/平时测验、）实验成绩 考试成绩构成比例：平时成绩25%，实验成绩25%，期末成绩50%考试形式：闭卷考试,1数据库和表的建立与增删数据2数据库表的管理3T-SQL语言4查询5视图和索引6优化查询性能7事务8用户自定义函数,9存储过程10触发器11安全管理12数据库备份和还原13传输数据和复制14性能监视15自动执行管理任务16安装配置和高可用性,演示：Pubs数据库的业务和结构,1.1 创建数据库（掌握）1.2 创建文件组（掌握）1.3 创建架构（掌握）1.4 数据库规划（理解）1

2、.5 创建数据类型（掌握）1.6 创建表（掌握）1.7 插入、删除和更新数据（掌握）,1 数据库和表的建立与增删数据,数据库的文件组成数据存储创建数据库的参数创建数据库的示例配置事务日志数据库选项数据库元数据,1.1 创建数据库,数据库的文件组成主数据文件辅助数据文件事务日志文件,数据存储,日志文件:.ldf,数据文件:.mdf or.ndf,区:8 个连续的 8 KB 页,页:8 KB,数据库大小的选项数据文件初始大小日志文件初始大小文件增长,model 数据库,主文件,辅助文件,事务日志,创建数据库的参数,方法1：可以使用SQL Server Management Studio中的可视化工

3、具来创建数据库,创建数据库的示例,CREATE DATABASE TestDBON-允许显式定义用于存储数据的文件-定义数据库主文件(NAME=TestDB_Data,-主文件逻辑名称 FILENAME=D:DATATestDB.mdf,-包含路径的操作系统文件名称 SIZE=20 MB,-主文件初始大小 FILEGROWTH=0)-未启用自动增长-定义数据库日志文件LOG ON(NAME=TestDB_Log,-日志文件逻辑名称 FILENAME=D:DATATestDB_Log.ldf,-包含路径的操作系统文件名称 SIZE=5 MB,-日志文件初始大小 FILEGROWTH=0)-未启用

4、自动增长,方法2：可以使用CREATE DATABASE语句创建数据库,练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL创建MyPubs数据库，该数据库的主数据文件逻辑名称为Pubs_Data，物理文件名为Pubs_Data.mdf，初始大小为10MB，不限大小，增长幅度为10%，数据库的日志文件逻辑名称为Pubs_Log，物理文件名为Pubs_Log.ldf，初始大小为5MB，最大尺寸为5MB，增长幅度为1MB，主数据文件和日志文件都存在C盘的Pubs文件夹下。,配置事务日志,提示：为了提高性能，应将日志文件放在单独的驱动器上,磁盘,

5、磁盘,缓冲区缓存,通过使用下面的工具设置数据库选项：SQL Server Management StudioALTER DATABASE 语句,数据库选项,使用ALTER DATABASE语句修改数据库ALTER DATABASE database ADD FILE,.,n TO FILEGROUP filegroup_name ADD LOG FILE,.,n,数据库元数据,注意：为了避免数据读写时对磁盘的争抢，请不要将数据文件置于包含了操作系统文件的磁盘中。将数据文件和事务日志文件分开放置。由于减少了数据文件和事务日志文件对磁盘的争抢，这种分隔将给数据库带来最佳的性能。如果可能，应将tem

6、pdb数据库放置在一个独立的磁盘驱动器上，例如RAID 10或者RAID 5系统中。这样，在tempdb数据库被大量使用的时候，通过独立的磁盘驱动器，数据库可以并行地处理tempdb的操作，因此能获得最佳性能。,文件组的概念创建文件组的场合默认文件组和只读文件组,1.2 创建文件组,MyPubs数据库,默认文件组,MyPubsGroup,E:,C:,D:,MyPubs_Log.Idf,MyPubs.mdf,MyPubs2.ndf,MyPubs1.ndf,sys.,sys.,sysusers,sysobjects,.,Table3,Table2,Table1,Table12,Table11,在单

7、个文件组中使用多个文件使用多个文件组控制物理数据布局,创建文件组的场合,创建用户定义文件组 改善性能 易管理 指定默认文件组指定只读文件组,默认文件组和只读文件组,练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL重建MyPubs数据库，该数据库的主数据文件逻辑名称为MyPubs_Data，物理文件名为MyPubs_Data.mdf，初始大小为10MB，不限大小，增长幅度为10%，辅助数据文件逻辑名称为MyPubs_DataAdd，物理文件名为MyPubs_DataAdd.ndf，初始大小为20MB，不限大小，增长幅度为20%，数据库的日

8、志文件逻辑名称为MyPubs_Log，物理文件名为MyPubs_Log.ldf，初始大小为5MB，最大尺寸为5MB，增长幅度为1MB，主数据文件和日志文件都存在C盘的MyPubs文件夹下。主数据文件在主文件组，辅助数据文件在MyPubsGroup文件组。,架构创建架构对象名称解析的工作方式,1.3 创建架构,sys,Table1,(Server1.Pubs.sys.Table1),guest,Table2,(Server1.Pubs.guest.Table2),Pubs,dbo,authors,(Server1.Pubs.dbo.authors),数据库对象的命名空间,架构,使用SQL Ser

9、ver Management Studio创建架构,创建架构,CREATE SCHEMA schema_name|AUTHORIZATION owner_name|schema_name AUTHORIZATION owner_name table_definition|view_definition|grant_statement|revoke_statement|deny_statement,2.使用CREATE SCHEMA语句创建架构,authors,dbo,SELECT*FROM dbo.authors,Lance(Default schema=dbo),Anders(Default

10、 schema=guest),SELECT*FROM authors,对象名称解析的工作方式,练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL为Pubs数据库创建架构，名为Publisher。,CREATE SCHEMA Publisher,讨论 规划数据库的注意事项有哪些?,规划数据库时的注意事项文件类型和文件位置容量规划注意事项,1.4 数据库规划,数据存储的用途事务吞吐量物理数据存储可能的增长文件位置,规划数据库时的注意事项,文件类型 主文件 辅助文件 事务日志文件 文件位置 管理磁盘上的数据文件和事务日志文件的位置可改善性能并实

11、现容错功能,文件类型和文件位置,预估数据库的大小model 数据库的大小计划增长值 索引 事务日志文件的大小,容量规划注意事项,SQL Server数据类型用户定义数据类型使用Transact-SQL创建用户定义数据类型,1.5 实现数据类型,系统提供的数据类型,用户定义数据类型基于系统提供的数据类型允许进一步细化数据类型 在特定数据库中定义 在数据库内必须有唯一的名称,使用Transact-SQL创建用户定义数据类型-创建名为au_type的用户定义数据类型-指定其数据类型为tinyint型，可空,CREATE TYPE dbo.au_typeFROM tinyintNULL,练习分别用SQ

12、L Server Management Studio中的可视化工具和Transact-SQL在数据库Pubs中创建dbo架构中的用户定义数据类型phone_type，其数据类型为char型，长度为12，允许为空。,CREATE TYPE dbo.phone_type FROM char(12)NULL,SQL Server在行中存储数据的方式SQL Server组织大型数据值的方式创建表的注意事项使用Transact-SQL语句创建、修改和删除表,1.6 创建表,SQL Server 在行中组织数据的方式,标头,固定数据,NB,VB,可变数据,空块,可变块,4 B,数据,2 B,1011100

13、1,列数,指示每列的可变字节（第一个8列）,2 B,2 B,可变长度列数,指向第一个可变列数据末尾的指针,行外的大型数据值选项OFF,行外的大型数据值选项ON,nvarchar(max)8 KB,数据行,nvarchar(max)8 KB,nvarchar(max)8 KB,数据行,nvarchar(max)8 KB,SQL Server 组织大型数据值的方式,创建表的注意事项列排序规则 可空性 特殊列类型 计算列 标识列时间戳列uniqueidentifier 列使用可视化数据库工具创建表,使用Transact-SQL语句创建、修改和删除表-在名为dbo的架构中创建名为Customers的表

14、-创建列，并定义其数据类型和可空性-指定CustomerID列为标志列,CREATE TABLE dbo.Customers(CustomerID int identity NOT NULL,CustomerName varchar(100)NOT NULL,CustomerDate datetime NOT NULL,Notes nvarchar(200)NULL),练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL在dbo架构中创建MyStores表，用来存储书店信息。该表结构同stores表。注意：stor_id改成标识列，标识种子

15、为1，标识增量为1。CREATE TABLE dbo.Mystores(stor_id int identity(1,1)NOT NULL,stor_name varchar(40)NULL,stor_address varchar(40)NULL,city varchar(20)NULL,state char(2)NULL,zip char(5)NULL),练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL为dbo架构中的MyStores表添加字段，用来存储书店电话号吗。该字段名称为stor_phone，数据类型为前面练习中所建立的用户

16、定义数据类型phone_type。,Alter TABLE dbo.MyStores Add Column stor_type dbo.phone_type NULL,插入数据删除数据更新数据,1.7 插入、删除和更新数据,用VALUES子句插入一行数据使用INSERT SELECT语句使用SELECT INTO语句创建表插入部分数据,插入数据,Insert Into dbo.authors Values(302-32-495,James,Albert,415 472-9749,Maine Street,Oakland,CA,94609,False),用VALUES子句插入一行数据为autho

17、rs表添加一条记录，内容是ID：302-32-495姓名：Albert James 电话：415 472-9749地址：Maine Street 城市：Oakland 州：CA邮编：94609 签约状态：否,练习分别用SQL Server Management Studio中的可视化工具和Transact-SQL为Pubs数据库中的stores表添加一条记录，内容是ID：5739 店名：Butterfly地址：Foot Street 城市：Oakland 州：CA 邮编：94666,Insert Into dbo.stores Values(5739,Butterfly,Foot Street

18、,Oakland,CA,94666),Insert Into dbo.authors Select emp_id,lname,fname,999 999-9999 From dbo.employee,使用INSERT SELECT语句将employee表中的所有个人记录添加到authors表中,练习在Pubs数据库中创建MyBackupStores表，结构同stores表，用InsertSelect语句将stores表中的记录复制到MyBackupStores表中。,Insert Into dbo.MyBackupStore Select*from dbo.MyStore,Select au_

19、fname,au_lname Into dbo.CAauthors From dbo.authors,使用SELECT INTO语句创建表创建dbo.CAauthors表，其数据来自authors表中的所有在CA州的个人记录，内容为个人姓名。,练习在Pubs数据库中用Select Into语句将stores表中的记录复制到MyBackupStore2表中。,Select*Into dbo.MyBackupStore2 From dbo.stores,Insert Into dbo.authors(au_id,au_lname,au_fname,phone,contract)Values(302

20、-32-293,Kelvin,Albert,415 472-2073,False),插入部分数据为authors表添加一条记录，内容是ID：302-32-293姓名：Albert Kelvin 电话：415 472-2073签约状态：否,练习用Transact-SQL为stores表添加一条记录，内容是ID：2495 姓名：Forest,Insert Into dbo.stores(stor_id,stor_name)Values(2495,Forest),使用DELETE语句使用TRUNCATE TABLE 语句删除基于其他表的行,删除数据,Delete From dbo.authors w

21、here au_lname=James,使用DELETE语句删除authors表中所有姓James的作家的记录,练习使用DELETE语句将stores表中stor_id为2495的记录删除。,Delete From dbo.stores where stor_id=2495,Truncate dbo.MyBackupStores,使用TRUNCATE TABLE 语句删除MyBackupStores表中所有记录,练习使用Truncate语句将数据库dbo架构中的MyStores表中的记录删除。,Truncate dbo.MyStores,Delete From dbo.titles Inner

22、 Join dbo.titleauthor On titles.title_id=titleauthor.title_idInner Join dbo.authorsOn titleauthor.au_id=authors.au_idAnd authors.au_fname=Johnandauthors.au_lname=White,删除基于其他表的行从销售书目中删除John White的所有书名,练习使用DELETE语句将Pubs数据库中名为Bookbeat的书店的销售记录删除。,Delete From Sales Inner Join Stores On Stores.stor_id=Sa

23、les.stor_id where stor_name=Bookbeat,根据表中数据更新行根据其他表更新行,更新数据,Update dbo.authors set phone=408 496-3837Where au_fname=John and au_lname=White,根据表中数据更新行将John White的电话号码改成408 496-3837,练习将Pubs数据库stores表中名为Bookbeat的书店的名称改为Johnson,地址改为5678 Amanda Ave.。,Update dbo.stores set stor_name=Johnson,address=5678,A

24、manda Ave.where stor_name=Bookbeat,Update dbo.titles set price=price*1.1 From dbo.title Inner Join dbo.titleauthor On titles.title_id=titleauthor.title_id Inner Join dbo.authors On titleauthor.au_id=authors.au_id where au_fname=John and au_lname=WhiteUpdate dbo.titles set price=price*1.1 Where title

25、_id In(Select title_id From dbo.titleauthors where au_id In(Select au_id From dbo.authors where au_fname=John and au_lname=White),根据其他表更新行将John White所著的书籍的价格增加10%。,练习将Bookbeat书店所订购书籍的价格增加10%。Update dbo.titles set price=price*1.1 From dbo.title Inner Join dbo.sales On titles.title_id=sales.title_id I

26、nner Join dbo.stores On sales.stor_id=stores.stor_id where stor_name=BookbeatUpdate dbo.titles set price=price*1.1 Where title_id In(Select title_id From dbo.sales where stor_id In(Select stor_id From dbo.stores where stor_name=Bookbeat),Update t set price=price*1.1 From dbo.title As tInner Join dbo

27、.titleauthor As taOn t.title_id=ta.title_id Inner Join dbo.authors As a On ta.au_id=a.au_id where au_fname=John and au_lname=White,通过把列名改为别名或使用文字替换结果集的值可提高计算结果的可读性。这些格式选项只修改了数据的表达形式，而不是数据的本身。将John White所著的书籍的价格增加10%。,SQLServer会删除或更新表中所有的行，除非在DELETE或UPDATE语句中包含WHERE子句。,本章重点掌握创建数据库掌握创建文件组掌握创建架构理解数据库规划掌握创建数据类型掌握创建表掌握插入、删除和更新数据,