0%

学生表.sql

发表于 更新于 分类于 阅读次数：
本文字数： 2.7k 阅读时长 ≈ 2 分钟

2017年10月8日 上午11:24

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
create database  xuexiao

go

create table student --先建第一个表
(
sno char(3) primary key,--学号，主键
sname char(8) not null,--学生姓名
ssex char(2)  not null,--学生性别
sbirthday datetime ,--学生出生年月
class char(5) --学生所在班级
)

create table student 
(
sno char(3) primary key,
sname char(8) not null,
ssex char(2)  not null,
sbirthday datetime ,
class char(5) 
)

insert into student values (108,'曾华','男','1977-9-1',95033);
insert into student values (105,'匡明','男','1975-10-2',95031);
insert into student values (107,'王丽','女','1976-1-23',95033);
insert into student values (101,'李军','男','1976-2-20',95033);
insert into student values (109,'王芳','女','1975-2-10',95031);
insert into student values (103,'陆君','男','1974-6-3',95031);

select * from student

create table teacher --创建第二个
(
tno char(3) primary key,--教工编号主键
tname char(4) not null ,--教工姓名
tsex char(2) not null ,--教工性别
tbirthday datetime, --教工的出生年月
prof char(6) ,--职称
depart varchar(10) not null--教工所在部门
)


create table teacher 
(
tno char(3) primary key,
tname char(4) not null ,
tsex char(2) not null ,
tbirthday datetime,
prof char(6) ,
depart varchar(10) not null
)

insert into teacher values (804,'李成','男','1958-12-2','副教授','计算机系');
insert into teacher values (856,'张旭','男','1969-3-12','讲师','电子工程系');
insert into teacher values  (825,'王萍','女','1972-5-5','助教','计算机系');
insert into teacher values (831,'刘冰','女','1977-8-14','助教','电子工程系');


select * from teacher

create table course--第三个表
(
cno char(5)  primary key,--课程号，主键
cname varchar(10) not null,--课程名称
tno char(3)  foreign key references teacher(tno)--教工编号（外键）
)

create table course
(
cno char(5)  primary key,
cname varchar(10) not null,
tno char(3)  foreign key references teacher(tno)
)

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','高等数学','831');

select * from course

create table score
(
sno char(3)  foreign key references student(sno) ,--学号（外键）
cno char(5) foreign key references course(cno) not null,--课程号（外键）
degree decimal(4,1) --成绩
)

create table score
(
sno char(3)  foreign key references student(sno) ,
cno char(5) foreign key references course(cno) not null,
degree decimal(4,1) 
)

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',81);
 

select * from score

select * from course

select * from student

select * from teacher