insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0078','王振','男', 21,'JSJ')
insert into Student (Sno,2','赵三','男', 22,'SX')
Sage smallint check(sage>16) ,
Sdept char(10) not null default 'JSJ' ,
primary key (sno)
)
create index ix_student_sname ON student(sname)
create table course(
insert into student (sno,sname,ssex,sage,sdept) values ('8003','钱凯','男',22,'JSJ')
insert into student (sno,sname,ssex,sage,sdept) values ('8004','王华','男',21,'SX')
create table SC(
Sno ud_sno ,
Cno char(4) ,
Grade int check(grade<=100) ,
constraint pk_sc primary key (sno,cno),
foreign key (sno) references student(sno) ,
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0004','杨秀红','女',21,'JSJ')
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0001','周志林','男',20,'SX')
foreign key (cno) references course(cno) ,
)
create index ix_sc_cno ON sc(cno)
--添加数据
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0003','陈小明','男',20,'SX')
Cno char(4) ,
Cname char(16) ,
Cpno char(4) ,
Ccredit int check (Ccredit >=0 and Ccredit<=5),
check( cno<>cpno) , --约束
primary key (cno)
)
create index ix_course_cpno ON course(cpno)
insert into student (sno,sname,ssex,sage) values ('8005','张英','女',21)
insert into student (sno,sname,ssex,sage) values ('8006','赵章','女',22)
insert into student (sno,sname,ssex,sage) values ('8007','钱利','男',23)
70B0A14155718F1746C9E61DECDDFCCA9FE601F7FF5D30B6389406271102E38ED
--drop database school
create database school --创建数据库
go
/*CREATE DATABASE school
ON
( NAME = school_dat,
insert into student (sno,sname,ssex,sage) values ('8008','王铁','男',21)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1001','高等数学','',5)
insert into course(Cno,Cname,Cpno,Ccredit) values ('1002','离散数学','1001',3)
insert into student (sno,sname,ssex,sage,sdept) values ('8001','张华','男',23,'SX')
insert into student (sno,sname,ssex,sage,sdept) values ('8002','赵颖','女',21,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0081','刘亭','女', 22,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0091','贺秋雪','女',20,'SX')
FILENAME = 'd:\school1.mdf',
SIZE = 2,
MAXSIZE = 4,
FILEGROWTH = 1 )
LOG ON
( NAME =school_log,
FILENAME = 'd:\school1.ldf',
SIZE = 2MB,
MAXSIZE = 3MB,
FILEGROWTH = 1MB ) */
go
use school
go
EXEC sp_addtype ud_sno,'char(6)'
go
--创建表
create table Student (
Sno ud_sno ,
Sname char(10) not null unique ,
Ssex char(2) check (ssex='男' or ssex='女') ,
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0009','钱明明','男',20,'SX')
insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ('0002','李文庆','男',23,'JSJ')