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

一 数据库和表的建立与增删数据,移动应用教研室 殷晓春,数据库设计与实现,考核要求： 平时成绩（课堂提问/学生作业/平时测验、） 实验成绩 考试成绩 构成比例： 平时成绩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.5 创建数据类型（掌握） 1.6 创建表（掌握） 1.7 插入、删除和更新数据（掌握）,1 数据库和表的建立与增删数据,数据库的文件组成 数据存储 创建数据库的参数 创建数据库的示例 配置事务日志 数据库选项 数据库元数据,1.1 创建数据库,数据库的文件组成 主数据文件 辅助数据文件 事务日志文件,数据存储,日志文件: .ldf,数据文件: .mdf or .ndf,区: 8 个连续的 8 KB 页,页: 8 KB,数据库大小的选项 数据文件初始大小 日志文件初始大小 文件增长,model 数据库,主文件,辅助文件,事务日志,创建数据库的参数,方法1：可以使用SQL Server Management Studio中的可视化工具来创建数据库,创建数据库的示例,CREATE DATABASE TestDB ON -允许显式定义用于存储数据的文件 - 定义数据库主文件 ( 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 ) - 未启用自动增长,方法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文件夹下。,配置事务日志,提示：为了提高性能，应将日志文件放在单独的驱动器上,磁盘,磁盘,缓冲区缓存,通过使用下面的工具设置数据库选项： SQL Server Management Studio ALTER DATABASE 语句,数据库选项,使用ALTER DATABASE语句修改数据库 ALTER DATABASE database ADD FILE ,.,n TO FILEGROUP filegroup_name ADD LOG FILE ,.,n ,数据库元数据,注意： 为了避免数据读写时对磁盘的争抢，请不要将数据文件置于包含了操作系统文件的磁盘中。 将数据文件和事务日志文件分开放置。由于减少了数据文件和事务日志文件对磁盘的争抢，这种分隔将给数据库带来最佳的性能。 如果可能，应将tempdb数据库放置在一个独立的磁盘驱动器上，例如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,在单个文件组中使用多个文件 使用多个文件组控制物理数据布局,创建文件组的场合,创建用户定义文件组 改善性能 易管理 指定默认文件组 指定只读文件组,默认文件组和只读文件组,练习 分别用SQL Server Management Studio中的可视化工具和Transact-SQL重建MyPubs数据库，该数据库的主数据文件逻辑名称为MyPubs_Data，物理文件名为MyPubs_Data.mdf，初始大小为10MB，不限大小，增长幅度为10%，辅助数据文件逻辑名称为MyPubs_DataAdd，物理文件名为MyPubs_DataAdd.ndf，初始大小为20MB，不限大小，增长幅度为20%，数据库的日志文件逻辑名称为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 Server 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 schema = guest),SELECT * FROM authors,对象名称解析的工作方式,练习 分别用SQL Server Management Studio中的可视化工具和Transact-SQL为Pubs数据库创建架构，名为Publisher。,CREATE SCHEMA Publisher,讨论 规划数据库的注意事项有哪些?,规划数据库时的注意事项 文件类型和文件位置 容量规划注意事项,1.4 数据库规划,数据存储的用途 事务吞吐量 物理数据存储可能的增长 文件位置,规划数据库时的注意事项,文件类型 主文件 辅助文件 事务日志文件 文件位置 管理磁盘上的数据文件和事务日志文件的位置可改善性能并实现容错功能,文件类型和文件位置,预估数据库的大小 model 数据库的大小 计划增长值 索引 事务日志文件的大小,容量规划注意事项,SQL Server数据类型 用户定义数据类型 使用Transact-SQL创建用户定义数据类型,1.5 实现数据类型,系统提供的数据类型,用户定义数据类型 基于系统提供的数据类型 允许进一步细化数据类型 在特定数据库中定义 在数据库内必须有唯一的名称,使用Transact-SQL创建用户定义数据类型 - 创建名为au_type的用户定义数据类型 - 指定其数据类型为tinyint型，可空,CREATE TYPE dbo.au_type FROM tinyint NULL,练习 分别用SQL 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,10111001,列数,指示每列的可变字节（第一个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的表 - 创建列，并定义其数据类型和可空性 - 指定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改成标识列，标识种子为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，数据类型为前面练习中所建立的用户定义数据类型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子句插入一行数据 为authors表添加一条记录， 内容是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, 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_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-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 where 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 Join dbo.titleauthor On titles.title_id=titleauthor.title_id Inner Join dbo.authors On titleauthor.au_id=authors.au_id And authors.au_fname=Johnand authors.au_lname=White,删除基于其他表的行 从销售书目中删除John White的所有书名,练习 使用DELETE语句将Pubs数据库中名为Bookbeat的书店的销售记录删除。,Delete From Sales Inner Join Stores On Stores.stor_id=Sales.stor_id where stor_name=Bookbeat,根据表中数据更新行 根据其他表更新行,更新数据,Update dbo.authors set phone=408 496-3837 Where 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,Amanda 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=White Update dbo.titles set price=price*1.1 Where title_id In (Select title_id From dbo.titleauthors where au_id In (Select au_id From dbo.authors where au_fname