SQLServer

创建数据库

use master
go
create database student
on primary(
name='student_data',
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\student_data.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%
),
FILEGROUP STUDENTGROUP(
name='Mystudent_data',
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\student_data.ndf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)

log on(
name='Mystudent_data_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\student_data_log.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)
go

查询数据库创建

use master
go
exec sp_helpdb student
go 

修改数据库名

use master
go
alter database student modify name = MyStudent
go

删除数据库

use master 
go
drop database MyStudent
go

给数据库添加组

use master 
go
alter database student add filegroup scoregroup

alter database student add file
(
name=Score_Data,
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Score_Data.ndf',
size=10MB,
maxsize=50MB,
FILEGROWTH=10%
)to filegroup scoregroup
go

创建自定义数据类型

use library
go
create type bookidtype from char(17)
go

创建表

 use library
go
create table reader
(
readerid char(13)  not null ,
readername varchar(20) not null,
typeid bit null,
birthday date null,
ex char(1) not null,
address varchar(40) null,
postalcode int null,
tel varchar(15),
enrolldate date null,
state varchar(10) null,
memo varchar(200) null
)
go

修改字段名：

use Student
go 
exec sp_rename 'student.Sno','sno'
go

修改表名：

use Student
go
exec sp_rename 'dbo.student','STU'

增删改列名：

alter table book add add_col int not null;

alter table book ALTER column add_col VARCHAR(200);

alter table book DROP column add_col;

建表时添加主外键

create table Course
(
cno CHAR(4) PRIMARY KEY,
cname VARCHAR(20) null,
cpno int not null,

FOREIGN key (cpno) REFERENCES Student(cpno)
)

修改表添加聚集索引

alter table mytable add CONSTRAINT PK_mytable PRIMARY KEY CLUSTERED(ID ASC);

##作业

use master
go
create database library
on primary(
name='library_data',
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\library_data.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)
log on(
name='library_log',
filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\library_log.ldf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)
go




use library
go
create table reader(
readerid char(13) not null,
readername nvarchar(20) not null,
typeid int null,
birthday char null,
ex char(1) not null,
address nvarchar(40) null,
postalcode char(6) null,
tel char(15) null,
enrolldate char not null,
state char(10) null,
memo char(200) null
)
go



use library
go 
create table readertype(
typeid int not null,
typename char(20) not null,
booksum int not null,
bookday int not null
)


use library
go 
create type bookIdType from char(17)


use library
go
create table book(
bookid bookIdType not null,
booktitle nvarchar(40) not null,
isbn char(21) null,
typeid numeric(10,2)  null,
author char(30) null,
press char(30) null,
pudate char null,
price char null,
regdate char null,
state char(10) null,
memo char(30) null
)
go



use library
go
create table booktype(
typeid int not null,
typename char(20) not null
)
go


use library
go
create table record
(
recordid int not null,
readerid char(13) not null,
bookid char not null,
outdate char not null,
indate char null,
state char(10) not null,
)
go