数据库.docx
- 文档编号:25947060
- 上传时间:2023-06-16
- 格式:DOCX
- 页数:19
- 大小:18.73KB
数据库.docx
《数据库.docx》由会员分享,可在线阅读,更多相关《数据库.docx(19页珍藏版)》请在冰豆网上搜索。
数据库
--实验一
createdatabaseYGGL
maxinstances1
maxloghistory1
maxlogfiles5
maxlogmembers5
maxdatafiles100
datafile'D:
\app\Lenovo\oradata\system01.dbf'
size325Mreusereuseautoextenonnext10240maxsizeunlimited
charactersetzhs16GBK
nationalcharactersetAL16UTF16
logfilegroup1('D:
\app\Lenovo\oradata\redo01.log')size100M,
group2('D:
\app\Lenovo\oradata\redo02.log')size100M,
group3('D:
\app\Lenovo\oradata\redo03.log')size100M
defaulttemporarytablespacetemp
tempfile'D:
\app\Lenovo\oradata\system01.dpf'
extentmanagementlocaluniformsize10M
undotablespaceundo_tsdatafile'D:
\app\Lenovo\oradata\system01.dbf'
size150Mreuseautoextendonnext10240Kmaxsizeunlimited;
--实验二
createtableDepartments(
DepartmentIDchar(3)notnullprimarykey,
DepartmentNamechar(20)notnull,
Notevarchar2(100)null
);
insertintoDepartmentsvalues('1','财务部',null);
insertintoDepartmentsvalues('2','人力资源部',null);
insertintoDepartmentsvalues('3','经理办公室',null);
insertintoDepartmentsvalues('4','研发部',null);
insertintoDepartmentsvalues('5','市场部',null);
select*fromdepartments;
createtableEmployees(
EmployeeIDchar(6)notnullprimarykey,
Namechar(10)notnull,
Birthdaydatenotnull,
Sexnumber
(1)notnull,
Addresschar(20)null,
Zipchar(6)null,
PhoneNumberchar(12)null,
DepartmentIDchar(3)notnull
);
altertableEmployeesaddforeignkey(DepartmentID)referencesDepartments(DepartmentID);
insertintoEmployeesvalues('000001','王林',to_date('1966-01-23','YYYY-MM-DD'),1,'中山路32-1-508','210003','83355668','2');
insertintoEmployeesvalues('010008','伍容华',to_date('1976-03-28','YYYY-MM-DD'),1,'北京东路100-2','210001','83321321','1');
insertintoEmployeesvalues('020010','王向荣',to_date('1982-12-09','YYYY-MM-DD'),1,'四牌楼10-0-108','210006','83792361','1');
insertintoEmployeesvalues('020018','李丽',to_date('1960-07-30','YYYY-MM-DD'),0,'中山东路102-2','210002','83413301','1');
insertintoEmployeesvalues('102201','刘明',to_date('1972-10-18','YYYY-MM-DD'),1,'虎距路100-2','210013','83606608','5');
insertintoEmployeesvalues('102208','朱俊',to_date('1965-09-28','YYYY-MM-DD'),1,'牌楼巷5-3-106','210004','84708817','5');
insertintoEmployeesvalues('108991','钟敏',to_date('1979-08-10','YYYY-MM-DD'),0,'中山路10-3-105','210003','83346722','3');
insertintoEmployeesvalues('111006','张石兵',to_date('1974-10-01','YYYY-MM-DD'),1,'解放路34-1-203','210010','84563418','5');
insertintoEmployeesvalues('210678','林涛',to_date('1977-04-02','YYYY-MM-DD'),1,'中山北路24-35','210008','83467336','3');
insertintoEmployeesvalues('302566','李玉珉',to_date('1968-09-20','YYYY-MM-DD'),1,'热和路209-3','210001','58765991','4');
insertintoEmployeesvalues('308759','叶凡',to_date('1978-11-18','YYYY-MM-DD'),1,'北京西路3-7-52','210002','83308901','4');
insertintoEmployeesvalues('504209','陈林琳',to_date('1969-09-03','YYYY-MM-DD'),0,'汉中路120-4-12','210018','84468158','4');
select*fromemployees;
createtableSalary(
EmployeeIDchar(6)notnullprimarykey,
inComenumber(8,2)notnull,
outComenumber(8,2)notnull
);
insertintoSalaryvalues('000001',2100.8,123.09);
insertintoSalaryvalues('010008',1582.62,88.03);
insertintoSalaryvalues('102201',2569.88,185.65);
insertintoSalaryvalues('111006',1987.01,79.58);
insertintoSalaryvalues('504209',2066.15,108.0);
insertintoSalaryvalues('302566',2980.15,210.2);
insertintoSalaryvalues('108991',3259.98,281.52);
insertintoSalaryvalues('020010',2860.0,198.0);
insertintoSalaryvalues('020018',2347.68,180.0);
insertintoSalaryvalues('308759',2531.98,199.08);
insertintoSalaryvalues('210678',2240.0,121.0);
insertintoSalaryvalues('102208',1980.0,100.0);
select*fromsalary;
updatesalarysetinCome=2890whereemployeeID='011112';
updatesalarysetinCome=inCome+100;
deletefromsalarywhereemployeeID='011112';
trancatetablesalary;
--实验三
select*fromemployees;
selectaddress,phonenumberfromemployees;
selectaddress,phonenumberfromemployeeswhereemployeeid='000001';
selectaddressas地址,phonenumberas电话fromemployeeswheresex=0;
selectemployeeid,income-outcomeas实际收入fromsalary;
selectdepartmentidfromemployeeswherenamelike'王%';
selectemployeeidfromsalarywhereincomebetween2000and3000;
select*fromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务部');
selectnamefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')and
birthday<=all(selectbirthdayfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='研发部'));
selectnamefromemployeeswhereemployeeidin
(selectemployeeidfromsalarywhereincome>all
(selectincomefromsalarywhereemployeeidin
(selectemployeeidfromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务部'))));
selectemployees.*,salary.*fromemployees,salarywhereemployees.employeeid=salary.employeeid;
selectname,income,outcomefromemployees,salary,departmentswhereemployees.employeeid=salary.employeeidand
employees.departmentid=departments.departmentidanddepartmentname='财务部'andincome>2000;
selectavg(income)as财务部平均收入fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentid=
(selectdepartmentidfromdepartmentswheredepartmentname='财务部'));
selectavg(income-outcome)as财务部平均实际收入fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentid=
(selectdepartmentidfromdepartmentswheredepartmentname='财务部'));
selectcount(employeeid)fromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentName='财务部');
selectcount(employeeid)fromemployeesgroupbydepartmentid;
selectemployees.*,salary.*fromemployees,salarywhereemployees.employeeid=salary.employeeidorderbyincome;
createorreplaceviewcx_employeesasselectemployeeid,name,birthday,sex,departmentidfromemployees;
createorreplaceviewcx_salaryasselectname,income,outcomefromemployees,salary,departmentswhereemployees.employeeid=salary.employeeidand
employees.departmentid=departments.departmentidanddepartmentname='财务部';
select*fromcx_employees;
select*fromcx_salary;
insertintocx_employeesvalues('510888','张无忌',to_date('19780823','YYYYMMDD'),1,'3');
updatecx_employeessetdepartmentid='5'wherename='张无忌';
deletefromcx_employeeswherename='张无忌';
--实验四
createindexpk_xs_bakonemployees(departmentid)tablespaceuserspctfree48initrans10maxtrans100storage
(initial64knext64kminextents5maxextents20pctincrease10freelists1freelistgroups1)parallel(degreedefault);
altertableemployeesadd(constraintch_phonecheck(phonenumberbetween'0'and'9'));
altertabledepartmentsadd(constraintun_departments(departmentname));
altertableemployeesadd(constraintfk_employeesforeignkey(departmentid)referencesdepartments(departmentid));
--实验五
declare
v_numnumber(3);
begin
selectcount(*)intov_num
fromxsb
wherezxf>50;
ifv_num<>0then
dbms_output.put_line('总分数>50的人数为:
'||to_char(v_num));
endif;
end;
declare
v_numnumber(3);
begin
selectcount(*)intov_num
fromxsb
wherezxf>40andzy='计算机';
ifv_num<>then
ifv_num>5then
dbms_output.put_line('计算机系总学分>40的人数超过10人');
endif;
endif;
end;
declare
v_avgnumber(4,2);
begin
selectavg(cj)intov_avg
fromxsb,cjb,kcb
wherexsb.xh=cjb.xhandcjb.kch=kcb.kchandkcb.kcm='数据库原理';
ifv_avg>75then
dbms_output.put_line('平均成绩大于75');
else
dbms_output.put_line('平均成绩小于75');
endif;
end;
declare
anumber;
bnumber;
cnumber;
x1number;
x2number;
dnumber;
begin
a:
=1;
b:
=4;
c:
=3;
d:
=b*b-4*a*c;
ifa=0then
x1:
=-c/b;
dbms_output.put_line('只有一个平方根'||to_char(x1));
elsifd<0then
dbms_output.put_line('没有算术平方根');
else
x1:
=(-b+sqrt(d))/(2*a);
x2:
=(-b-sqrt(d))/(2*a);
dbms_output.put_line('第一个平方根'||to_char(x1));
dbms_output.put_line('第二个平方根'||to_char(x2));
endif;
end;
declare
nnumber:
=1;
count1number:
=2;
begin
loop
n:
=n*count1;
count1:
=count1+1;
ifcount1>10then
exit;
endif;
endloop;
dbms_output.put_line(to_char(n));
end;
declare
nnumber:
=1;
count1number:
=2;
begin
loop
n:
=n*count1;
count1:
count1+1;
exitwhencount1=11;
endloop;
dbms_output.put_line(to_char(n));
end;
declare
nnumber:
=1;
count1number:
=2;
begin
whilecount1<=10
loop
n:
=n*count1;
count1=count1+1;
endloop;
dbms_output.put_line(to_char(n));
end;
declare
nnumber:
=1;
count1number;
begin
forcount1in2..10
loop
n:
=n*count1;
endloop;
dbms_output.put_line(to_char(n));
end;
declare
v_kchchar(3);
v_resultvarchar2(16);
begin
selectkch
intov_kch
fromkcb
wherekkxq=1;
casev_kch
when'101'thenv_result:
='计算机基础';
when'102'thenv_result:
='程序设计语言';
when'206'thenv_result:
='离散数学';
when'208'thenv_result:
='数据结构';
else
v_result:
='nothing';
endcase;
dbms_output.put_line(v_result);
end;
declare
v_counterbinary_integer:
=1;
v_xhnumber(6);
begin
v_xh:
=100001;
loop
insertintotemp(xh,xb)
values(to_char(v_ch),'男');
v_counter:
=v_counter+1;
v_xh:
=v_xh+1;
ifv_counter=10then
gotoloop_end;
endif;
endloop;
<
dbms_output.put_line('initok');
end;
createorreplacefunctioncheck_id(departmentidinchar)
returnnumber
as
num1number;
xnumber;
begin
selectcount(*)intox
fromdepartments
wheredepartmentid=departmentid;
if(x>0)then
num1:
=0;
else
num1:
=-1;
endif;
return(num1);
end;
declare
num1number;
begin
num1:
=check_id(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库