数据库实验二.docx
- 文档编号:11003748
- 上传时间:2023-02-24
- 格式:DOCX
- 页数:18
- 大小:16.68KB
数据库实验二.docx
《数据库实验二.docx》由会员分享,可在线阅读,更多相关《数据库实验二.docx(18页珍藏版)》请在冰豆网上搜索。
数据库实验二
select*fromsc;
insert
intosc(sno,cno,grade)
selectsno,'7',60
fromstudent
wheresdept='IS';
select*fromsc;
snocnograde
98001187
98001267
98001390
98002295
98002388
98010187
980102NULL
98010380
98010487
98010685
98011152
98011247
98011353
98011545
98012184
980123NULL
98012467
98012581
select*intoTs
fromstudent;
select*fromTs;
delete
fromTs;
select*fromTs;
snosnamesagessexsdept
98001钱横18男CS
98002王林19女CS
98003李明20男IS
98004赵三16女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
snosnamesagessexsdept
insert
intoyj
select*fromstudent
wheresage<=16andssex='女';
select*fromyj;
snosnamesagessexsdept
98004赵三16女MA
98013陈婷婷16女PH
98014李军16女EH
select*fromhc;
insertintohc
select*fromstudent
wheresnoin(
selectsnofromsc)
andnotexists
(
select*fromsc
wherestudent.sno=sc.snoand(grade>=60orgradeisNULL)
)
select*fromhc;
snosnamesagessexsdept
98004赵三16女MA
98013陈婷婷16女PH
98014李军16女EH
snosnamesagessexsdept
98004赵三16女MA
98013陈婷婷16女PH
98014李军16女EH
98011张丽萍19女CH
select*fromstudent;
updatestudent
setsname='刘华',sage=sage+1
wheresno='98001';
select*fromstudent;
snosnamesagessexsdept
98001钱横18男CS
98002王林19女CS
98003李明20男IS
98004赵三16女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
snosnamesagessexsdept
98001刘华19男CS
98002王林19女CS
98003李明20男IS
98004赵三16女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
select*fromsc;
updatesc
setgrade=NULL
where'数据库系统'=
(
selectcname
fromcouse
whereo=o
)
andgrade<60;
select*fromsc;
snocnograde
98001187
98001267
98001390
98002295
98002388
98003760
98010187
980102NULL
98010380
98010487
98010685
98011152
98011247
98011353
98011545
98012184
980123NULL
98012467
98012581
98012760
snocnograde
98001187
98001267
98001390
98002295
98002388
98003760
98010187
980102NULL
98010380
98010487
98010685
980111NULL
98011247
98011353
98011545
98012184
980123NULL
98012467
98012581
98012760
updatestudent
setstudent.sage=student.sage+1
from(
selecttop4*fromstudent
orderbysno
)stu
wherestudent.sno=stu.sno;
select*fromstudent;
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
updatesc
setgrade=NULL
wherecno='3'and'王林'=
(
selectsname
fromstudent
wherestudent.sno=sc.sno
);
select*fromsc;
snocnograde
98001187
98001267
98001390
98002295
980023NULL
98003760
98010187
980102NULL
98010380
98010487
98010685
980111NULL
98011247
98011353
98011545
98012184
980123NULL
98012467
98012581
98012760
updatesc
setgrade=grade*1.05
where'女'=
(selectssex
fromstudent
wherestudent.sno=sc.sno
)
and
grade<(
selectavg(grade)
fromsc);
select*fromsc;
snocnograde
98001187
98001267
98001390
98002295
980023NULL
98003760
98010187
980102NULL
98010380
98010487
98010685
980111NULL
98011249
98011355
98011547
98012184
980123NULL
98012467
98012581
98012760
updatesc
setgrade=grade*0.98
wherecno='2'andgrade<=80;
select*fromsc;
updatesc
setgrade=grade*0.99
wherecno='2'andgrade>80;
select*fromsc;
snocnograde
98001187
98001265
98001390
98002295
980023NULL
98003760
98010187
980102NULL
98010380
98010487
98010685
980111NULL
98011248
98011355
98011547
98012184
980123NULL
98012467
98012581
98012760
snocnograde
98001187
98001265
98001390
98002294
980023NULL
98003760
98010187
980102NULL
98010380
98010487
98010685
980111NULL
98011248
98011355
98011547
98012184
980123NULL
98012467
98012581
98012760
delete
fromsc
wheregradeisNULL;
select*fromsc;
snocnograde
98001187
98001265
98001390
98002294
98003760
98010187
98010380
98010487
98010685
98011248
98011355
98011547
98012184
98012467
98012581
98012760
select*fromstudent;
delete
fromsc
where'钱横'=(
selectsname
fromstudent
wherestudent.sno=sc.sno
)
select*fromsc;
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
snocnograde
98001187
98001265
98001390
98002294
98003760
98010187
98010380
98010487
98010685
98011248
98011355
98011547
98012184
98012467
98012581
98012760
select*fromsc;
delete
fromsc
wheresno='98010';
select*fromsc;
select*fromstudent;
delete
fromstudent
wheresno='98010';
select*fromstudent;
snocnograde
98001187
98001265
98001390
98002294
98003760
98010187
98010380
98010487
98010685
98011248
98011355
98011547
98012184
98012467
98012581
98012760
snocnograde
98001187
98001265
98001390
98002294
98003760
98011248
98011355
98011547
98012184
98012467
98012581
98012760
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98010赵青江18男CS
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
select*fromsc;
deletefromsc
wheresnoin(
selectsno
fromstudent
wheresnamelike('张%')
)
select*fromsc;
select*fromstudent;
delete
fromstudent
wheresnamelike'张%';
select*fromstudent;
snocnograde
98001187
98001265
98001390
98002294
98003760
98011248
98011355
98011547
98012184
98012467
98012581
98012760
snocnograde
98001187
98001265
98001390
98002294
98003760
98012184
98012467
98012581
98012760
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98011张丽萍19女CH
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
snosnamesagessexsdept
98001刘华20男CS
98002王林20女CS
98003李明21男IS
98004赵三17女MA
98012陈景欢20男IS
98013陈婷婷16女PH
98014李军16女EH
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验