SPJ 与 Student 数据库的创建于数据插入

create database SPJ
on primary
(
   name='SPJ',
   filename='D:\test\SPJ.mdf',
   size=10mb,
   maxsize=50mb,
   filegrowth=5%
   )
log on
(
   name='SPJ_log',
   filename='D:\test\SPJ_log.ldf',
   size=2mb,
   maxsize=5mb,
   filegrowth=1mb
   )
  go

create table SPJ.dbo.S
(
 SNO nchar(10),
 SNAME	nchar(10),
 STATUS	int,
 CITY	nchar(10),
)
  create table SPJ.dbo.P
(
   PNO nchar(10),
   PNAME nchar(10),
   COLOR nchar(10),
   WEIGHT int,
  )
 create table SPJ.dbo.J
(
   JNO nchar(10),
   JNAME nchar(10),
   CITY nchar(10),
   )
 create table SPJ.dbo.SPJ
(
   SNO nchar(10),
   PNO nchar(10),
   JNO nchar(10),
   QTY int,
   )
  go
insert into SPJ.dbo.S
values('S1','精益',20,'天津')
insert into SPJ.dbo.S
values('S2','盛锡',10,'北京')
insert into SPJ.dbo.S
values('S3','东方红',30,'北京')
insert into SPJ.dbo.S
values('S4','丰泰盛',20,'天津')
insert into SPJ.dbo.S

values('S5','为民',30,'上海')

insert into SPJ.dbo.P
values('P1','螺母','红',12)
insert into SPJ.dbo.P
values('P2','螺栓','绿',17)
insert into SPJ.dbo.P
values('P3','螺丝刀','蓝',14)
insert into SPJ.dbo.P
values('P4','螺丝刀','红',14)
insert into SPJ.dbo.P
values('P5','凸轮','蓝',40)
insert into SPJ.dbo.P
values('P6','齿轮','红',30)

insert into SPJ.dbo.J
values('J1','三建','北京')
insert into SPJ.dbo.J
values('J2','一汽','长春')
insert into SPJ.dbo.J
values('J3','弹簧厂','天津')
insert into SPJ.dbo.J
values('J4','造船厂','天津')
insert into SPJ.dbo.J
values('J5','机车厂','唐山')
insert into SPJ.dbo.J
values('J6','无线电厂','常州')
insert into SPJ.dbo.J
values('J7','半导体厂','南京')

insert into SPJ.dbo.SPJ
values('S1','P1','J1',200)
insert into SPJ.dbo.SPJ
values('S1','P1','J3',100)
insert into SPJ.dbo.SPJ
values('S1','P1','J4',700)
insert into SPJ.dbo.SPJ
values('S1','P2','J2',100)
insert into SPJ.dbo.SPJ
values('S2','P3','J1',400)
insert into SPJ.dbo.SPJ
values('S2','P3','J2',200)
insert into SPJ.dbo.SPJ
values('S2','P3','J4',500)
insert into SPJ.dbo.SPJ
values('S2','P3','J5',400)
insert into SPJ.dbo.SPJ
values('S2','P5','J1',400)
insert into SPJ.dbo.SPJ
values('S2','P5','J2',100)
insert into SPJ.dbo.SPJ
values('S3','P1','J1',200)
insert into SPJ.dbo.SPJ
values('S3','P3','J1',200)
insert into SPJ.dbo.SPJ
values('S4','P5','J1',100)
insert into SPJ.dbo.SPJ
values('S4','P6','J3',300)
insert into SPJ.dbo.SPJ
values('S4','P6','J4',200)
insert into SPJ.dbo.SPJ
values('S5','P2','J4',100)
insert into SPJ.dbo.SPJ
values('S5','P3','J1',200)
insert into SPJ.dbo.SPJ
values('S5','P6','J2',200)
insert into SPJ.dbo.SPJ
values('S5','P6','J4',500)

create database Student
on primary
(
   name='Student',
   filename='D:\test\Student.mdf',
   size=20mb,
   maxsize=100mb,
   filegrowth=10mb
   )
log on
(
   name='Student-log',
   filename='D:\test\Student_log.ldf',
   size=2mb,
   maxsize=5mb,
   filegrowth=1mb
   )
  go

create table Student.dbo.S
(
 sno nchar(10) primary key,
 sname	nchar(10),
 ssex	nchar(10),
 sbirth	datetime,
 Sdept  nchar(10)
 )
  create table Student.dbo.C
(
   cno nchar(10) primary key,
   cname nchar(10),
   cpno nchar(10),
   Ccredit nchar(10),
  )
 create table Student.dbo.SC
(
   sno nchar(10),
   cno nchar(10),
   grade int,
   )
  go
insert into Student.dbo.S
values('95001','李勇','男',1986/1/1,'CS')
insert into Student.dbo.S 
values('95002','刘晨','女',1985/2/1,'IS')
insert into Student.dbo.S
values('95003','王敏','女',1986/10/4,'MA')
insert into Student.dbo.S
values('95004','张立','男',1985/6/8,'IS')

insert into Student.dbo.C
values('1','数据库','2','4')
insert into Student.dbo.C(cno,cname,Ccredit)
values('2','数学','3')
insert into Student.dbo.C
values('3','信息系统','1','4')
insert into Student.dbo.C
values('4','操作系统','5','3')
insert into Student.dbo.C
values('5','数据结构','6','3')
insert into Student.dbo.C(cno,cname,Ccredit)
values('6','C语言','2')

insert into Student.dbo.SC
values('95001','1',92)
insert into Student.dbo.SC
values('95001','2',85)
insert into Student.dbo.SC
values('95001','3',87)
insert into Student.dbo.SC
values('95002','2',78)
insert into Student.dbo.SC
values('95002','3',84)

转载于:https://www.cnblogs.com/codeyuan/p/4254413.html