数据库实验记录

Author Avatar
tianrking Dec 18, 2018
  • Read this article on other devices

实验一

建表与简单操纵

1
😃😃😃
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
create database EDUC
on
(
name = 'student_data',
filename = '/var/opt/mssql/tudent_data.mdf',
size = 10,
filegrowth = 5%,
maxsize = 50
)
log on
(
name = 'student_log',
filename = '/var/opt/mssql/student_log.ldf',
size = 2,
maxsize = 5,
filegrowth = 1
)
create table student
(
sno char(8) not null constraint PK_student primary key ,
sname char(8) not null ,
sex char(2),
native char (20),
birthday smalldatetime ,
dno char(6) ,
entime smalldatetime,
home varchar(40),
tel varchar(40),
)
create table course
(
cno char(10) not null primary key , --class id
spno char(8) , -- major
cname char(20) not null , -- class name
experiment tinyint, --ex time sy
lecture tinyint --le time
)
create table student_course
(
sno char(8) not null primary key,
cno char(10) not null ,
score tinyint
)
create table teacher
(
tno char(8) not null primary key,
tname char(8) not null ,
sex char(2) ,
birthday smalldatetime ,
dno char(6) ,
pno tinyint ,
home varchar(40),
tel varchar(40),
)
create table teacher_course
(
tno char(8) not null constraint T_Force foreign key references teacher(tno),
classno char(4),
cno char(10) not null constraint C_Force foreign key references course(cno),
classtime varchar(40),
classroom varchar(40),
weektime tinyint,
)

alter table course add year datetime

alter table course alter column year smalldatetime

alter table course add constraint year check(year between 2004 and 2012)

alter table course drop year

alter table course drop column year

insert into student (sno,sname,sex,birthday,dno,entime,home,tel)
values ('800130','w0x7c3','m','1998-11-02 ','idiot','2017-09-01','Jin','+17654000663')

update student set native='Jining Shandong' where sname='w0x7c3'

实验二

建表

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
create database BR
on
(
name = 'book_reader_data',
filename = '/var/opt/mssql/book_reader.mdf',
size = 10 ,
filegrowth = 5%,
maxsize = 50
)
log on
(
name = 'book_reader_log',
filename = '/var/opt/mssql/book_reader.ldf',
size = 2,
maxsize = 5,
filegrowth = 1
)
create table book
(
bno char(10) constraint bkey primary key not null,
bsort char(12) ,
bpublish char(50) not null,
author char (20) ,
bname char(50) not null,
bprice money(8) ,
)
create table reader
(
rnu char(10) not null constraint rkey primary key ,
rname char(8) not null,
rwork char(50) ,
rsex char(2) constraint csex check(rsex=='男' or rsex == '女') ,
rtel char(15),
)
create table borrow
(
number char(10) primary key ,
bnu char(10),
rnu char(10),
bdata datetime ,
)
~~~

##插入数据

insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1001’,’计算机’,’机械工业出版社’,’王民’,’数据结构’,’80’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1002’,’计算机’,’机械工业出版社’,’张建平’,’计算机应用’,’20’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1003’,’计算机’,’电子工业出版社’,’王敏’,’数据库技术’,’15’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1004’,’计算机’,’电子工业出版社’,’谭浩强’,’c语言’,’25’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1005’,’英语’,’中国人民大学出版社’,’张锦芯’,’应用作文写作’,’25’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1006’,’管理’,’高等教育出版社’,’Robison’,’管理学’,’15’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1007’,’管理’,’机械工业出版社’,’Fayol’,’工业管理’,’70’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1008’,’数学’,’机械工业出版社’,’李平’,’线性代数’,’50’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1009’,’管理’,’机械工业出版社’,’Durark’,’公司的概念’,’14’)
insert into book (bno,bsort,bpublish,author,bname,bprice)
values (‘1010’,’数学’,’机械工业出版社’,’徐新国’,’统计学’,’15’)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
~~~	
insert into borrow (number,bnu,rnu)
values ('1','1001','1003')
insert into borrow (number,bnu,rnu)
values ('2','1002','1004')
insert into borrow (number,bnu,rnu)
values ('3','1003','1005')
insert into borrow (number,bnu,rnu)
values ('4','1004','1006')
insert into borrow (number,bnu,rnu)
values ('5','1005','1007')
insert into borrow (number,bnu,rnu)
values ('6','1006','1008')
insert into borrow (number,bnu,rnu)
values ('7','1007','1009')
insert into borrow (number,bnu,rnu)
values ('8','1008','1010')
insert into borrow (number,bnu,rnu)
values ('9','1009','1011')
insert into borrow (number,bnu,rnu)
values ('10','1010','1012')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1001','丁一','数学院','男','81234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1002','赵二','经济学院','男','82234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1003','张三','管理学院','女','83234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1004','李四','文学院','男','84234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1005','王五','历史文化学院','女','85234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1006','孙六','物理学院','男','86234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1007','周七','生命科学院','女','87234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1008','徐八','化学院','男','88234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1009','宋九','信息学院','女','89234567')
insert into reader (rnu,rname,rwork,rsex,rtel)
values ('1010','刘十','计算机学院','女','90234567')

##操纵

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use br
---查找这样的图书类别:要求类别中最高的图书定价不低于按类别分组的图书的平均定价的2 倍
--select bsort from book group by bsort having max(bprice)>=all(select 2*avg(bprice) from book group by bsort)
---验证
--select 2*avg(bprice),bsort,max(bprice) from book group by bsort
---求机械工业出版社出版的各类图书的平均定价,分别用GROUP BY
--select avg(bprice),bsort from book where (bpublish='机械工业出版社') group by bsort
---验证
---select bprice,bsort from book where (bpublish='机械工业出版社')
---列出计算机类图书的书号、名称及价格,最后求出册数和总价格。
--select bname,bprice from book where bsort like '计算机'
--select count(*) from book where bsort='计算机'
--select sum(bprice) from book where bsort like '计算机'
--查询计算机类和机械工业出版社出版的图书。
--select distinct bpublish from book where( bsort like '%计算机%' or bsort like '%机械工业出版%' )

#实验三

建表

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
create database en_tools
on
(
name='en-tools_data',
filename='/var/opt/mssql/en-tools.ldf',
size=10,
filegrowth=5%,
maxsize=50
)
log on
(
name='en-tools_log',
filename='/var/opt/mssql/en-tools.mdf',
size=2,
maxsize=5,
filegrowth=1
)
create table S
(
sno char(5) constraint S_pk primary key,
sname char(20) ,
scity char(20) ,
stel char(20) ,
)
create table J
(
jno char(5) constraint J_pk primary key,
jname char(50) ,
jmaster char(10) ,
jplan char(8),
)
create table P
(
pno char(5) constraint P_pk primary key,
pname char(50),
ptype char(10),
plocal char(20),
pcolor char(10),
)
create table SPJ
(
spj_sno char(5),
spj_jno char(5),
spj_pno char(5),
spj_count int(4),
constraint SPJ_pk primary key(spj_jno,spj_count,spj_sno)
)

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
insert into S(sno,sname,scity,stel)
values('S1','北京供应商','北京','0108888888')
insert into S(sno,sname,scity,stel)
values('S2','天津供应商','天津','022888888')
insert into S(sno,sname,scity,stel)
values('S3','重庆供应商','重庆','023888888')
insert into S(sno,sname,scity,stel)
values('S4','上海供应商1','上海','021888888')
insert into S(sno,sname,scity,stel)
values('S5','广州供应商','广州','020888888')
insert into S(sno,sname,scity,stel)
values('S6','上海供应商2','上海','0216666666')
1
2
3
4
5
6
7
8
9
10
insert into J (jno,jname,jmaster,jplan)
values('J1','工程1','丁一','200000')
insert into J (jno,jname,jmaster,jplan)
values('J2','工程2','赵二','60000')
insert into J (jno,jname,jmaster,jplan)
values('J3','工程3','张三','70000')
insert into J (jno,jname,jmaster,jplan)
values('J4','工程4','李四','80000')
insert into J (jno,jname,jmaster,jplan)
values('J5','工程5','王五','150000')
1
2
3
4
5
6
7
8
9
10
11
12
insert into P(pno,pname,ptype,plocal,pcolor)
values('P1','螺丝','中','济南','红色')
insert into P(pno,pname,ptype,plocal,pcolor)
values('P2','主板','集成','深圳','绿色')
insert into P(pno,pname,ptype,plocal,pcolor)
values('P3','显卡','独立','香港','蓝色')
insert into P(pno,pname,ptype,plocal,pcolor)
values('P4','声卡','集成','天津','红色')
insert into P(pno,pname,ptype,plocal,pcolor)
values('P5','网卡','100M','上海','黑色')
insert into P(pno,pname,ptype,plocal,pcolor)
values('P6','鼠标','无线','上海','黑色')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S1','J2','P4','50')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S2','J3','P5','100')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S3','J2','P6','500')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S4','J1','P3','150')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S4','J5','P1','200')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S5','J4','P6','100')
insert into SPJ(spj_sno,spj_jno,spj_pno,spj_count)
values('S6','J','P2','90')

操纵

1
2
3
4
5
6
7
8
9
10
11
select sno as 供应商代码 , sname as 姓名 from s 
where sno in (select spj_sno from SPJ
where spj_sno = 'J4' and spj_pno in (
select pno from p
where pcolor = '红色')
)
select jno as 工程代码 from S,SPJ
where not exist(select * from SPJ
where S.sno=SPJ.spj_sno and scity='上海')
select jno as 工程代码 from J,
select distinct spj_jno from spj where spj_sno like 's5'

#实验四

操纵

1
2
3
4
5
6
7
8
9
10
11
12
13
14

显示所有专业号为‘001’,并且在服务器显示之前,暂停 1 分钟
timefor delay '00:01'
select * from course where spno='001'

把所有计算机转学学生的联系电话删掉,所有专业号为‘001’的学生
的入学时间删掉,所有专业专业号为‘003.’的学生的班级号改为‘001’,
其他的学生删掉其籍贯。(case)
update student
set entime = (case spno
when '001' then null else entime end)
classno = (case spn
when '003' then '001' else classno end)
native = (case spno when '001' native case class when '003' then native else null end )

        

This blog is under a CC BY-NC-SA 4.0 Unported License
Link to this article: https://zyx.8a8.xyz/2018/12/18/数据库实验记录/