33. SQL -- 管理数据库和数据库表（2）

filts

　　
　　修改数据库
　　在数据库中添加或删除文件和文件组。也可用于更改文件和文件组的属性，例如更改文件的名称和大小。ALTER DATABASE 提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力。
　　语法：
　　ALTER DATABASE database {
　　ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
　　| ADD LOG FILE < filespec > [ ,...n ]
　　| REMOVE FILE logical_file_name
　　| ADD FILEGROUP filegroup_name
　　| REMOVE FILEGROUP filegroup_name
　　| MODIFY FILE < filespec >
　　| MODIFY NAME = new_dbname
　　| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME =
　　new_filegroup_name }
　　| SET < optionspec > [ ,...n ] [ WITH < termination > ]
　　| COLLATE < collation_name >
　　}
　　其中：
　　< filespec > ::=
　　( NAME = logical_file_name
　　[ , NEWNAME = new_logical_name ]
　　[ , FILENAME = 'os_file_name' ]

　　[ ,>　　[ , MAXSIZE = { max_size | UNLIMITED } ]
　　[ , FILEGROWTH = growth_increment ] )
　　DEMO:
　　alter database db1
　　add file
　　(name = file1,
　　filename = 'c:\Program Files\Microsoft SQL
　　Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file2.ndf')
　　删除数据库
　　从数据库服务器中删除一个或多个数据库。删除数据库将删除数据库所使用的数据库文件和磁盘文件。
　　语法
　　DROP DATABASE database_name [ ,...n ]
　　参数
　　database_name：指定要删除的数据库名称
　　创建及管理表
　　基本概念
　　T-SQL 中创建表的语法定义：
　　l 每个表有一个名称，称为表名或关系名。表名必须以字母开头，最大长度为30 个字符。
　　l 一张表包含若干列，列名惟一，列名也成为属性名。
　　l 同一列的数据必须要有相同的数据类型。
　　l 表中的每一列数值必须为一个不可分割的数据项。
　　l 表中的一行称为一条记录。
　　列属性：
　　表的列名在同一个表中具有惟一性，同一列的数据属于同一种数据类型。
　　NULL 或NOT NULL

　　>　　注意：一个列不能同时具有NULL 属性和IDENTITY 属性，只能二者选其一。
　　SQL 中表查询方式：
　　表扫描：堆表的查询方式，即未创建索引
　　堆表+索引：
　　索引： 聚集索引： 数据按物理存放位置排序,定义主键
　　非聚集索引： 在聚集索引的基础上，建立索引，一般用于why 条件
　　创建表：
　　1)、CREATE TABLE 语法：
　　CREATE TABLE
　　[ database_name.[ owner ] .| owner.] table_name
　　( { < column_definition >| column_name AS computed_column_expression | <
　　table_constraint > } [ ,...n ] )
　　[ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
　　< column_definition > ::= { column_name data_type } [ [ DEFAULT constant_expression ]|

　　[>　　[ < column_constraint > ] [ ...n ]
　　另外，在创建表时，还要注意以下几点：
　　一个表至少有一列，最多不超过1 024 列。
　　每个数据库中最多可以创建200 万个表。
　　表在存储时使用的计量单位是盘区(Extent)。一个盘区分为8 个数据项，每页8KB字节。在创建表时，会分配给它一个初始值为一个盘区的存储空间。当增加表的存储空间时，以盘区为单位增加。
　　2)、使用图形化界面创建表:
　　数据库 → 表 → 新建表 → 定义列名、数据类型、是否允许null → 列属性：定义默认值
　　列属性配置：
　　DEMO：
　　创建表tb3，定义列co11 为int 数据类型，定义其为自增列，起始值为1，增涨幅度为1，定义其为主键，定义列co12 ，数据类型为char(10)
　　在向表中写入数据时，co11 列不需指定值，其会自动增涨
　　use mytest
　　go
　　create table tb3

　　(co11 int>　　co12 char(10))
　　insert into tb3 values ('admin')
　　go 100
　　select * from tb3
　　定义实验用表格：
　　DEMO1：创建一个商标信息表；
　　包含商标ID，商标名称，要求将商标ID 设为主键，所有列not null
　　create table Toybradn
　　(cBrandld char(3) primary key not null, --主键默认是not null
　　cBrandName char(20) not null)
　　go
　　select * from Toybradn
　　查看表列信息：
　　数据库 → 表 → 列 | key 信息等
　　插入数值：
　　insert into Toybradn values ('P01','bus')
　　insert into Toybradn values ('P02','bike')
　　insert into Toybradn
　　values ('A01','desk'),('A02','JF')
　　select * from Toybradn
　　数据插入方式：
　　A、T-SQL：insert into
　　B、图形化界面：数据库 → 表 → 右键 → 编辑前200 行 → 输入内容
　　C、在EXCEL 中，对应表中各列关系，建立好相关数据，点击复制，在SQL 数据库 → 表
　　→ 右键 → 编辑前200 行 → 粘贴
　　DEMO2：创建一个玩具信息表
　　create table toys
　　(cToyid char(6) primary key , --玩具ID
　　vToyName varchar(20) not null, --名称
　　vToyDescription varchar(250), --规格
　　cCategoryld char(3), --种类
　　mToyRate money not null , --价格
　　cBrandld char(3) references Toybradn(cBrandld), --定义外键，引用表Toybradn中的
　　cBrandld列，玩具ID
　　imPhoto image, --玩具图片
　　siLowerAge smallint not null, --年龄下限
　　siUpperAge smallint not null , --年龄上限
　　siToyWeight smallint , --玩具重量
　　vToyimgPath varchar(50)) --图片路径
　　select * from toys
　　当表创建完成后，若需在图形化界面修改表列参数，需修改：
　　工具 → 选项 → designers → 表设计器及数据库设计器 → （取消）阻止保存要求重新创建表的更改写入一条数据：
　　insert into toys values
　　('P0001','bus','A-B1','BIG',150,'P01',NULL,3,4,NULL,NULL)
　　由于cBrandld 列定义了外键，因此如果cBrandld 列中的内容与商标表中不对应，则不能写入数据.
　　DEMO3：创建销售表
　　销售表：使用订单号做为主键，创建一个聚集索引.
　　create table Orders
　　(
　　docno char(20), --订单号
　　docdate datetime , --日期
　　cust Nvarchar(100), --客户
　　carrency char(3), --地点
　　rate numeric(8,4), --订单金额
　　constraint pk_docno primary key clustered
　　(docno asc)
　　)
　　DEMO4：销售明细表：
　　create table OrdersDetail
　　(
　　txcode char(10),
　　docno char(20),
　　docseq smallint,
　　part varchar(30),
　　txqty numeric(12,4),
　　price numeric(12,4),
　　constraint pk_OrdersDetail primary key clustered
　　(
　　[txcode] asc,
　　[docno] asc,
　　[docseq] asc
　　)
　　)
　　3)、临时表：
　　可以用CREATE TABLE 命令创建表局部的或全局作用的临时表。其语法与创建一般表基本相同，只是在局部临时表的表名前要使用符号―#‖，全局临时表的表名前要使用符号―##‖
　　以便与一般的表相区别。
　　--创建临时表并附值：
　　create table #Toybradn
　　(cBrandld char(3) primary key ,
　　cBrandName char(20) not null)
　　insert into #Toybradn
　　select * from Toybradn
　　go
　　select * from #Toybradn
　　/*或者：
　　将一个实际表内容into到一个临时表，自动创建*/
　　select *
　　into #tmpt
　　from Toybradn
　　select * from Toybradn
　　select * from tempdb.dbo.#tmpt
　　--临时表是存储于系统数据库tempdb 中
　　修改表
　　用ALTER TABLE 命令修改表结构通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。
　　ALTER TABLE 语法：
　　ALTER TABLE table

　　{ [>　　{ new_data_type [ ( precision [ , scale ] ) ]
　　[ COLLATE < collation_name > ]
　　[ NULL | NOT NULL ]
　　| {ADD | DROP } ROWGUIDCOL }
　　]
　　| ADD
　　{ [ < column_definition > ]
　　| column_name AS computed_column_expression
　　} [ ,...n ]
　　| [ WITH CHECK | WITH NOCHECK ] ADD
　　{ < table_constraint > } [ ,...n ]
　　| DROP
　　{ [ CONSTRAINT ] constraint_name
　　| COLUMN column } [ ,...n ]
　　| { CHECK | NOCHECK } CONSTRAINT
　　{ ALL | constraint_name [ ,...n ] }
　　| { ENABLE | DISABLE } TRIGGER
　　{ ALL | trigger_name [ ,...n ] }
　　}
　　使用图形化界面修改：
　　数据库 → 表 → 设计
　　DEMO1：在tb3 中新增一列
　　alter table tb3
　　add co13 nvarchar(20)
　　DEMO2：修改tb3 中，列co13 的数据类型为char(30)
　　alter table tb3
　　alter column co13 char(30)
　　sp_help tb3:查看表的详细信息
　　sp_spaceused tb3：查看表的存储过程
　　sp_rename: 修改表名和列名
　　DEMO：
　　/*A. 重命名表
　　以下示例将SalesTerritory 表重命名为SalesTerr。
　　复制代码*/
　　USE AdventureWorks;
　　GO
　　EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';
　　GO
　　/* B. 重命名列
　　以下示例将SalesTerritory 表中的TerritoryID 列重命名为TerrID。
　　复制代码*/
　　USE AdventureWorks;
　　GO
　　EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
　　GO
　　/* C. 重命名索引
　　以下示例将IX_ProductVendor_VendorID 索引重命名为IX_VendorID。
　　复制代码*/
　　USE AdventureWorks;
　　GO
　　EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID',
　　N'IX_VendorID', N'INDEX';
　　GO
　　/* D. 重命名别名数据类型
　　以下示例将Phone 别名数据类型重命名为Telephone。
　　复制代码*/
　　USE AdventureWorks;
　　GO
　　EXEC sp_rename N'Phone', N'Telephone', N'USERDATATYPE';
　　3）、删除表：
　　drop table：删除表
　　elect:删除一条记录
　　delete from Toybradn
　　where cBrandld = 'A12'