create database SS on
(
name='SS',
filename='C:\SS.mdf',
size=3mb,
filegrowth=5%
)
log on
(
name='SS_log',
filename='C:\SS_log.ldf',
size=3mb,
maxsize=20mb,
filegrowth=5%
)
use SS --打开数据库SS
create table stu
(
sid int primary key identity(1,1) not null,
name varchar(10) not null unique,
sex char(2) check(sex='男' or sex='女') not null,
age as datediff(yy,birth,getdate()),
birth datetime,
email varchar(20) check(email like '%@%.com'),
stuage int check(stuage between 0 and 100),
addr varchar(20) default '中国',
marrige bit,
more text
)
--create table stu 创建表stu
--primary key 主键
--identity(1,1) 标识列
--unique 唯一约束
--check 检查约束
---default 默认值
create table course
(
cid int primary key identity(1,1),
cname varchar(10)
)
create table score
(
sid int,
cid int,
sc int,
primary key(sid,cid),
foreign key(sid) references stu(sid),
foreign key(cid) references course(cid)
)
insert into stu(name,sex,birth,email,stuage,addr,marrige,more) values('张三丰','女','1911-1-1','zsf@163.com',20,'北京',1,'OKOKOKOK')
insert into stu(birth,email,stuage,addr,marrige,more,name,sex) values('1911-1-1','zsf@163.com',20,'北京',1,'OKOKOKOK','张屋脊','男')
insert into stu values('张翠山','男','1922-2-2','zcs@126.com',5,default,0,NULL)
insert into stu values('赵翠花','女','1962-6-6','zch@126.com',16,'上海',0,NULL)
insert into stu values('赵敏','女','1982-6-6','zm@163.com',26,'天津',1,NULL)
insert into stu values('李市民','男','1955-5-6','lsm@163.com',5,'天津',1,NULL)
insert into stu values('成要紧','男','1955-5-6','cyj@163.com',5,'天津',1,NULL)
insert into stu values('方食欲','男','1955-5-6','fsy@163.com',5,'天津',1,NULL)
insert into course(cname) values('数学')
insert into course(cname)
select '语文' union
select '英语'
insert into score(sid,cid,sc)
select 1,1,100 union
select 1,2,90 union
select 1,3,88 union
select 2,1,80 union
select 2,2,75 union
select 2,3,73 union
select 3,1,70 union
select 3,2,50 union
select 3,3,40
select * from course
select * from score
select * from stu