T-SQL 数据库的创建
create database databaseName
on primary
(
Name =dbName1,
FileName='C:\mydatabase1\dbName1.mdf',
Size =10MB,
MaxSize=20MB,
FileGrowth=10mb),
(
Name =dbName2,
FileName='C:\mydatabase2\dbName2.mdf',
Size =10MB,
MaxSize=20MB,
FileGrowth=10mb)
Log on
(
Name =dbLogName1,
Filename= 'C:\mydatabaseLog\dbLogName1.ldf',
Size=10mb,
MaxSize=20mb,
FileGrowth=10mb)
/*
Name sql server使用的逻辑名称
Filename 完全限定的NT Server 文件名
Size 文件的初始大小 ，默认值是model数据库主文件[model.mdf]的大小
MaxSize 最大的文件尺寸，默认值是占满整个空间
FileGrowth 当需要时，SQL Server拓展文件的量 。默认值10%
*/
------------
---修改数据库----
--添加文件--
alter database databaseName
add File
(
Name= dbName3,
size=10MB,
MaxSize=20mb,
FileName='C:\dbName3.mdf',
FileGrowth=10mb
)
---移除文件-----
Alter Database databaseName
remove File dbName3
------修改文件---------
alter database databaseName
Modify File
(
Name=dbName2,
Size=15mb
)
数据库之study
create database study /*数据文件 前三行必写*/ on
(
name = study_data,
filename = 'f:\20160303\study.mdf',
size=20,
maxsize=unlimited,
filegrowth=20%
)
log on (
name = study_log,
filename = 'f:\20160303\study.log',
size=5,
maxsize=20,
filegrowth=2
)
/*GO的作用是先执行上面的语句 数据库建好之后再往下运行*/
use study
create table class (
class_no char(6) primary key,
class_name char(20) not null,
class_special varchar(20),
class_dept char(20)
)
GO
create table student (
s_no char(6) primary key,
class_no char(6) not null references class(class_no),
s_name varchar(10) not null,
s_sex char(2) check(s_sex ='男' or s_sex='女'),
s_birthday datetime
)
GO
create table course (
course_no char(5) primary key,
course_name char(20) not null, course_score numeric(6,2)
)
GO create table choice (
s_no char(6) references student(s_no),
course_no char(5) references course(course_no),
score numeric(6,1)
)
GO
create table teacher (
t_no char(6) primary key,
t_name varchar(10) not null,
t_sex char(2) check(t_sex ='男' or t_sex='女'),
t_birthday datetime, t_title char(10)
)
GO
create table teaching (
course_no char(5) references course(course_no),
t_no char(6) references teacher(t_no)
)
select * from choice
insert into class values('js9901','计算机99-1','计算机','计算机系')
insert into class values('js9902','计算机99-2','计算机','计算机系')
insert into class values('js0001','计算机00-1','计算机','计算机系')
insert into class values('js0002','计算机00-2','计算机','计算机系')
insert into class values('xx0001','信息00-1','信息','信息系') insert into class values('xx0002','信息00-1','信息','信息系')
insert into student values('991101','js9901','张斌','男','1981-10-1')
insert into student values('991102','js9901','王蕾','女','1980-8-8')
insert into student values('991103','js9901','李建国','男','1981-4-5')
insert into student values('991104','js9901','李平方','男','1981-5-12')
insert into student values('991201','js9902','陈东辉','男','1980-2-8')
insert into student values('991202','js9902','葛鹏','男','1979-12-23')
insert into student values('991203','js9902','潘耀志','女','1980-2-6')
insert into course values('01001','计算机基础','3')
insert into course values('01002','程序设计语言','5')
insert into course values('01003','数据结构','6')
insert into course values('02001','数据库原理与应用','6')
insert into course values('02002','计算机网络','6')
insert into choice values('991101','01001','88.0')
insert into choice(s_no,course_no) values('991102','01001')
insert into choice values('991103','01001','91.0')
insert into choice values('991104','01001','78.0')
insert into teacher values('000001','李英','女','1964-11-3','讲师')
insert into teacher values('000002','王大山','男','1955-3-3','副教授')
insert into teacher values('000003','张朋','男','1974-10-3','讲师')
insert into teacher values('000004','陈伟军','男','1963-11-1','助教')
insert into teacher values('000005','宋浩然','男','1966-12-4','讲师')
insert into teacher values('000006','徐红霞','女','1964-5-8','副教授')
insert into teaching values('01001','000001')
insert into teaching values('01002','000002')
insert into teaching values('01003','000003')
insert into teaching values('02001','000004')
select * from choice; select * from class select * from course select * from student select * from teacher select * from teaching
