数据库基本表

最新推荐文章于 2022-04-10 16:43:38 发布

coolshark

最新推荐文章于 2022-04-10 16:43:38 发布

阅读量1.7k

点赞数

分类专栏：数据库文章标签：数据库 insert null table 数据结构 database

本文链接：https://blog.csdn.net/coolshark/article/details/3016804

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

<< SQL_SERVER实用教程>>

============================================================

Create DataBase XSCJ
on
(
Name='XSCJ_Data',
FileName='e:/data/XSCJ.mdf',
Size=5mb,
MaxSize=50mb,
FileGrowth=10%
)
Log on
(
Name='XSCJ_Log',
FileName='e:/data/XSCJ_Log.mdf',
Size=2mb,
MaxSize=5mb,
FileGrowth=1mb
)
go

Use XSCJ
Create Table XS
( 学号 char(6) Not Null,
姓名 char(8) Not Null,
专业名 char(10) Null,
性别 bit   Not Null,
出生时间 datetime Not Null,
总学分 tinyint  Null,
备注 text  Null
)
go

Use XSCJ
Create Table KC
( 课程号 char(3) Not Null,
课程名 char(16) Not Null,
开课学期 tinyint Not Null,
学时 tinyint Not Null,
学分 tinyint Null,
)
go

Use XSCJ
Create Table XS_KC
( 学号 char(6) Not Null,
课程号 char(3) Not Null,
成绩 tinyint Null,
学分 tinyint Null,
)
go

================================
USE XSCJ
Insert Into XS
Values('001101','王林',  '计算机',1,'02/10/1980 0:0:0',50,Null)
Insert Into XS
Values('001102','程明',  '计算机',1,'01/01/1981 0:0:0',50,Null)
Insert Into XS
Values('001103','王燕',  '计算机',0,'10/06/1979 0:0:0',50,Null)
Insert Into XS
Values('001104','韦严平', '计算机',1,'08/26/1980 0:0:0',50,Null)
Insert Into XS
Values('001105','李方方', '计算机',1,'01/30/1980 0:0:0',50,Null)
Insert Into XS
Values('001106','李明',  '计算机',1,'11/20/1980 0:0:0',54,'提前修完《数据结构》，并获学分')
Insert Into XS
Values('001107','林一帆', '计算机',1,'08/05/1979 0:0:0',52,'已经提前修完一门课')
Insert Into XS
Values('001108','张强民', '计算机',1,'08/11/1978 0:0:0',50,Null)
Insert Into XS
Values('001109','张蔚',  '计算机',1,'07/22/1981 0:0:0',50,'三好学生')
Insert Into XS
Values('001110','赵琳',  '计算机',0,'03/18/1980 0:0:0',48,'Null')
Insert Into XS
Values('001111','严红',  '计算机',0,'08/11/1979 0:0:0',44,Null)
Insert Into XS
Values('001112','罗林琳', '计算机',0,'06/05/1980 0:0:0',49,Null)
Insert Into XS
Values('001113','李方',  '计算机',0,'11/28/1981 0:0:0',50,'有一门功课不及格等待补考')
Insert Into XS
Values('001201','王敏',  '通信工程',1,'06/10/1978 0:0:0',42,Null)
Insert Into XS
Values('001202','王琳',  '通信工程',1,'01/29/1979 0:0:0',40,'有一门功课不及格等待补考')
Insert Into XS
Values('001203','王玉民', '通信工程',1,'03/26/1980 0:0:0',42,Null)
Insert Into XS
Values('001204','马林琳', '通信工程',0,'02/10/1978 0:0:0',42,Null)
Insert Into XS
Values('001205','李计',  '通信工程',1,'09/20/1979 0:0:0',42,Null)
Insert Into XS
Values('001206','李红庆', '通信工程',1,'05/01/1979 0:0:0',40,'已经提前修完一门课')
Insert Into XS
Values('001207','孙祥欣', '通信工程',1,'03/09/1979 0:0:0',44,Null)
Insert Into XS
Values('001208','孙研',  '通信工程',1,'09/10/1980 0:0:0',42,Null)
Insert Into XS
Values('001209','吴薇华', '通信工程',0,'03/18/1980 0:0:0',42,Null)
Insert Into XS
Values('001210','刘燕敏', '通信工程',0,'11/12/1979 0:0:0',42,Null)
Insert Into XS
Values('001211','罗林琳', '通信工程',0,'01/30/1980 0:0:0',50,'转换专业学习')
go

===========================
USE XSCJ
Insert Into KC
Values('101','计算机基础', 1,80,5)
Insert Into KC
Values('102','程序设计与语言', 2,68,4)
Insert Into KC
Values('206','离散数学',  4,68,4)
Insert Into KC
Values('208','数据结构',  5,68,4)
Insert Into KC
Values('209','操作系统',  6,68,4)
Insert Into KC
Values('210','计算机原理', 7,85,5)
Insert Into KC
Values('212','数据库原理', 7,68,4)
Insert Into KC
Values('301','计算机网络', 7,51,3)
Insert Into KC
Values('302','软件工程',  7,51,3)
go

================================================
USE XSCJ
Insert Into XS_KC
Values('001101','101',80,Null)
Insert Into XS_KC
Values('001101','102',78,Null)
Insert Into XS_KC
Values('001101','206',76,Null)
Insert Into XS_KC
Values('001103','101',62,Null)
Insert Into XS_KC
Values('001103','102',70,Null)
Insert Into XS_KC
Values('001103','206',81,Null)
Insert Into XS_KC
Values('001104','101',90,Null)
Insert Into XS_KC
Values('001104','102',84,Null)
Insert Into XS_KC
Values('001104','206',65,Null)
Insert Into XS_KC
Values('001102','102',78,Null)
Insert Into XS_KC
Values('001102','206',78,Null)
Insert Into XS_KC
Values('001106','101',65,Null)
Insert Into XS_KC
Values('001106','102',71,Null)
Insert Into XS_KC
Values('001106','206',80,Null)
Insert Into XS_KC
Values('001107','101',78,Null)
Insert Into XS_KC
Values('001107','102',80,Null)
Insert Into XS_KC
Values('001107','206',68,Null)
Insert Into XS_KC
Values('001108','101',85,Null)
Insert Into XS_KC
Values('001108','102',64,Null)
Insert Into XS_KC
Values('001108','206',87,Null)
Insert Into XS_KC
Values('001109','101',66,Null)
Insert Into XS_KC
Values('001109','102',83,Null)
Insert Into XS_KC
Values('001109','206',70,Null)
Insert Into XS_KC
Values('001110','101',95,Null)
Insert Into XS_KC
Values('001110','102',90,Null)
Insert Into XS_KC
Values('001110','206',89,Null)
Insert Into XS_KC
Values('001111','101',91,Null)
Insert Into XS_KC
Values('001111','102',70,Null)
Insert Into XS_KC
Values('001111','206',76,Null)
Insert Into XS_KC
Values('001113','101',63,Null)
Insert Into XS_KC
Values('001113','102',79,Null)
Insert Into XS_KC
Values('001113','206',60,Null)
Insert Into XS_KC
Values('001201','101',80,Null)
Insert Into XS_KC
Values('001202','101',65,Null)
Insert Into XS_KC
Values('001203','101',87,Null)
Insert Into XS_KC
Values('001204','101',91,Null)
Insert Into XS_KC
Values('001210','101',76,Null)
Insert Into XS_KC
Values('001216','101',81,Null)
Insert Into XS_KC
Values('001218','101',70,Null)
Insert Into XS_KC
Values('001220','101',82,Null)
Insert Into XS_KC
Values('001221','101',76,Null)
Insert Into XS_KC
Values('001241','101',90,Null)
go

<< 数据库原理习题与解析>>

============================================================

Create Table student      -- 学生表
(
sno  char(5) not null unique,   -- 学号
sname  char (8),     -- 学生姓名
ssex  char(2),     -- 性别
sbirthday DATETIME,     -- 出生日期
class char(5)       -- 班号
)

-- 插入学生表数据
insert into student values('108','曾华','男','1977-09-01','9533')
insert into student values('105','匡明','男','1975-10-02','9531')
insert into student values('107','王丽','女','1976-01-23','9533')
insert into student values('101','李军','男','1976-02-20','9533')
insert into student values('109','王芳','女','1975-02-10','9531')
insert into student values('103','陆君','男','1974-06-03','9531')

Create Table teacher      -- 教师表
(
tno  char(5) not null unique,   -- 教师学号
tname  char (8),     -- 教师姓名
tsex  char(2),     -- 教师性别
tbirthday DATETIME,     -- 教师出生日期
prof  char(6),     -- 职称
depart  char(10)     -- 所在系
)

-- 插入教师表数据
insert into teacher values('804','李诚','男','1958-12-02 00:00:00:000','副教授','计算机系')
insert into teacher values('856','张旭','男','1969-03-12 00:00:00:000','讲师','电子工程系')
insert into teacher values('825','王萍','女','1976-05-05 00:00:00:000','助教','计算机系')
insert into teacher values('831','刘冰','女','1977-08-14 00:00:00:000','助教','电子工程系')

Create Table course      -- 课程表
(
cno  char(5) not null unique,   -- 课程编号
cname  char(10),     -- 课程名
degree  int      -- 任课教师编号
)

--插入课程信息
insert into course values('3-105','计算机导论',825)
insert into course values('3-245','操作系统',804)
insert into course values('6-166','数字电路',856)
insert into course values('9-888','高等数学',100)

Create Table score      -- 成绩表
(
sno  char(5),     -- 学号
cno  char(5),     -- 课程编号
degree  int      -- 分数
)

-- 插入成绩信息
insert into score values('103','3-245',86)
insert into score values('105','3-245',75)
insert into score values('109','3-245',68)
insert into score values('103','3-105',92)
insert into score values('105','3-105',88)
insert into score values('109','3-105',76)
insert into score values('101','3-105',64)
insert into score values('107','3-105',91)
insert into score values('108','3-105',78)
insert into score values('101','6-166',85)
insert into score values('107','6-166',79)
insert into score values('108','6-166',87)

<< 数据库实用教程>>

清华大学出版社董健全等编著

============================================================

供应商关系：S(SNO,SNAME,SADDR)
零件关系：P(PNO,PNAME,COLOR,WEIGHT)
工程项目关系：J(JNO,JNAME,JCITY,BALANCE)
供应关系：SPJ(SNO,PNO,JNO,PRICE,QTY)

Create DataBase Supplys
on
(
Name='Supplys_Data',
FileName='e:/data/Supplys.mdf',
Size=5mb,
MaxSize=50mb,
FileGrowth=10%
)
Log on
(
Name='Supplys_Log',
FileName='e:/data/Supplys_Log.mdf',
Size=2mb,
MaxSize=5mb,
FileGrowth=1mb
)
go

Use Supplys
Create Table S
(
SNO char(4) Not Null,
SNAME char(20) Not Null,
SADDR char(20),
PRIMARY KEY(SNO)
)
go

insert into S values('S1','原料公司','南京北门23号')
insert into S values('S2','红星钢管厂','上海浦东100号')
insert into S values('S3','零件制造公司','南京东晋路23号')
insert into S values('S4','配件公司','江西上饶58号')
insert into S values('S5','原料厂','北京红星路88号')
insert into S values('S8','东方配件厂','天津叶西路100号')

Create Table P
(
PNO char(4) Not Null,
PNAME char(20),
COLOR char(8),
WEIGHT SMALLINT,
PRIMARY KEY(PNO)
)
go

insert into P values('P1','钢筋','黑',25)
insert into P values('P2','钢管','白',26)
insert into P values('P3','螺母','红',11)
insert into P values('P4','螺丝','黄',12)
insert into P values('P5','齿轮','红',18)

Create Table J
(
JNO char(4) Not Null,
JNAME char(20),
JCITY char(20),
BALANCE NUMERIC(7,2),
PRIMARY KEY(JNO)
)
go

insert into J values('J1','东方明珠','上海',0.00)
insert into J values('J2','炼油厂','长春',-11.20)
insert into J values('J3','地铁三号','北京',678.00)
insert into J values('J4','明珠线','上海',456.00)
insert into J values('J5','炼钢工地','天津',123.00)
insert into J values('J6','南浦大桥','上海',234.00)
insert into J values('J7','红星水泥厂','江西',343.00)

Create Table SPJ
(
SNO char(4) Not Null,
PNO char(4) Not Null,
JNO char(4) Not Null,
PRICE NUMERIC(7,2),
QTY SMALLINT,

PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO),

CHECK(QTY BETWEEN 0 AND 10000)
)
go

insert into SPJ values('S1','P1','J1',22.60,80)
insert into SPJ values('S1','P1','J4',22.60,60)
insert into SPJ values('S1','P3','J1',22.80,100)
insert into SPJ values('S1','P3','J4',22.80,60)
insert into SPJ values('S3','P3','J5',22.10,100)
insert into SPJ values('S3','P4','J1',11.90,30)
insert into SPJ values('S3','P4','J4',11.90,60)
insert into SPJ values('S4','P2','J4',33.80,60)
insert into SPJ values('S5','P5','J1',22.80,20)
insert into SPJ values('S5','P5','J4',22.80,60)
insert into SPJ values('S8','P3','J1',13.00,20)
insert into SPJ values('S1','P3','J6',22.80,6)
insert into SPJ values('S3','P4','J6',11.90,6)
insert into SPJ values('S4','P2','J6',33.80,8)
insert into SPJ values('S5','P5','J6',22.80,8)