主题：关于数据库控制语句学习的资料

1、数据库定义 /*一个数据文件和一个日志文件*/

CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON( NAME = 'Sales_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO

/*多个数据文件和多个日志文件*/

CREATE DATABASE Archive ON PRIMARY( NAME = Arch1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\archdat1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\archdat2.ndf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch3, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\archdat3.ndf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON( NAME = Archlog1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\archlog1.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Archlog2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\archlog2.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) GO

2、创建外键、主键 create table stu1( number char(4) not null primary key, name char(10) )

create table grade1( number char(4) not null foreign key references stu1, average int )

亦可分开写： create table stu1( number char(4) not null , name char(10), primary key(number) )

create table grade1( number char(4) not null , average int foreign key(number) references stu1)

还可以起名字： create table stu1( number char(4) not null , name char(10), constraint pri1 primary key (number) )

create table grade1( number char(4) not null , average int constraint for1 foreign key (number) references stu1)

删除为：（注意次序） alter table grade1 drop constraint for1 alter table stu1 drop constraint pri1

3、视图 3.1 SQL语法 create view stu_name as select name from student 重命名列： create view stu_name1(stuname) as select name from student

3.2 企业管理器和向导

3.3 视图修改 sp_helptext stu_name显示内容 sp_rename stu_name,old重命名 alter view stu_name as select * from student修改视图 drop view stu_name,stu_name1删除视图

视图和基本表的修改关系，一般为自由操作，可以通过with check option 来检查 create view stu_name2 as select * from student where height>1.76

此句可以：insert into stu_name2(number,height) values('207',1.74)

create view stu_name2 as select * from student where height>1.76 with check option

此句不可以：insert into stu_name2(number,height) values('207',1.74)

4、索引 丛集索引：数据因索引而重新排序，查询速度快，一个表只能有一个，但制作开销大，硬盘占用多120% 非丛集索引：默认为可重复索引，但最好不同，而前者相反

4.1创建 create index in1 on student(name)

4.2查看索引 set showplan_text on 或 dbcc show_statistics(student,in1)

4.3索引列的选择 主键列、外键列、group by列、 order by列和 where常查询的列 但不要text,image,bit类型列，重复值多的列