SQL Server 语句练习准备

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