SQL实训示例.docx
《SQL实训示例.docx》由会员分享，可在线阅读，更多相关《SQL实训示例.docx（28页珍藏版）》请在冰点文库上搜索。
SQL实训示例
10月24日
查询当前系统时间
selectsysdatefromdual;
PL/SQL序模板
setserveroutputon
begin
dbms_output.put_line（'这是第一个PL/SQL程序'）;
end;
/
创建课本中的范例数据库，students表,teachers表,departments表,courses表,students_grade表,grades表；
新建的用户并赋予其权限
Connsystem/oracle;
createuserwanghuiidentifiedbywanghui;
grantcreatesession,createuser,createview,createtabletowanghui;
为用户指定表空间
alteruserwanghuidefaulttablespaceusersquota20monusers;
connwanghui/wanghui;
创建students
createtablestudents（student_idnumber（5）
constraintstudent_pkprimarykey,
monitor_idnumber（5）,
namevarchar2（10）notnull,
sexvarchar2（6）
constraintsex_chkcheck（sexin（'男','女'））,
dobdate,
specialtyvarchar2（10）
）;
创建departments
createtabledepartments（
department_idnumber（3）
constraintdepartment_pkprimarykey,
department_namevarchar2（8）notnull,
addressvarchar2（40）
）;
创建teachers
createtableteachers（
teacher_idnumber（5）
constraintteacher_pkprimarykey,
namevarchar2（8）notnull,
titlevarchar2（6）,
hire_datedatedefaultsysdate,
bonusnumber（7,2）,
wagenumber（7,2）,
department_idnumber（3）
constraintteachers_fk_departments
referencesdepartments（department_id）
）;
创建courses
createtablecourses（
course_idnumber（5）
constraintcourse_pkprimarykey,
course_namevarchar2（30）notnull,
credit_hournumber
（2）
）;
创建students_grade
createtablestudents_grade（
student_idnumber（5）
constraintstudents_grade_fk_students
referencesstudents（student_id）,
course_idnumber（5）
constraintstudents_grade_fk_courses
referencescourses（course_id）,
scorenumber（4,1）
）;
创建grades
createtablegrades（
grade_idnumber
（1）
constraintgrade_pkprimarykey,
low_scorenumber（4,1）,
high_scorenumber（4,1）,
gradevarchar2（6）
）;
查看表结构
Descstudents;
添加students表数据
insertintostudents
values（10101,null,'王晓芳','女','07-5月-1988','计算机'）;
insertintostudents
values（10205,null,'李秋枫','男','25-11月-1990','自动化'）;
insertintostudents
values（10102,10101,'刘春苹','女','12-8月-1991','计算机'）;
insertintostudents
values（10301,null,'高山','男','08-10月-1990','机电工程'）;
insertintostudents
values（10207,10205,'王刚','男','03-4月-1987','自动化'）;
insertintostudents
values（10112,10101,'张纯玉','男','21-7月-1989','计算机'）;
insertintostudents
values（10318,10301,'张冬云','女','26-12月-1989','机电工程'）;
insertintostudents
values（10103,10101,'王天仪','男','26-12月-1989','计算机'）;
insertintostudents
values（10201,10205,'赵风雨','男','25-10月-1990','自动化'）;
insertintostudents
values（10105,10101,'韩刘','男','3-8月-1991','计算机'）;
insertintostudents
values（10311,10301,'张扬','男','08-5月-1990','机电工程'）;
insertintostudents
values（10213,10205,'高淼','男','11-3月-1987','自动化'）;
insertintostudents
values（10212,10205,'欧阳春岚','女','12-3月-1989','自动化'）;
insertintostudents
values（10314,10301,'赵迪帆','男','22-9月-1989','机电工程'）;
insertintostudents
values（10312,10301,'白菲菲','女','07-5月-1988','机电工程'）;
insertintostudents
values（10328,10301,'曾程程','男',null,'机电工程'）;
insertintostudents
values（10128,10101,'白昕','男',null,'计算机'）;
insertintostudents
values（10228,10205,'林紫寒','女',null,'自动化'）;
添加departments表数据
insertintodepartments
values（101,'信息工程','1号教学楼'）;
insertintodepartments
values（102,'电气工程','2号教学楼'）;
insertintodepartments
values（103,'机电工程','3号教学楼'）;
添加teachers表数据
insertintoteachers
values（10101,'王彤','教授','01-9月-1990',1000,3000,101）;
insertintoteachers
values（10104,'孔世杰','副教授','06-7月-1994',800,2700,101）;
insertintoteachers
values（10103,'邹人文','讲师','21-1月-1996',600,2400,101）;
insertintoteachers
values（10106,'韩冬梅','助教','01-8月-2002',500,1800,101）;
insertintoteachers
values（10210,'杨文化','教授','03-10月-1989',1000,3100,102）;
insertintoteachers
values（10206,'崔天','助教','05-9月-2000',500,1900,102）;
insertintoteachers
values（10209,'孙晴碧','讲师','11-5月-1998',600,2500,102）;
insertintoteachers
values（10207,'张珂','讲师','16-8月-1997',700,2700,102）;
insertintoteachers
values（10308,'齐沈阳','高工','03-10月-1989',1000,3100,103）;
insertintoteachers
values（10306,'车东日','助教','05-9月-2001',500,1900,103）;
insertintoteachers
values（10309,'臧海涛','工程师','29-6月-1999',600,2400,103）;
insertintoteachers
values（10307,'赵昆','讲师','18-2月-1996',800,2700,103）;
insertintoteachers
values（10128,'王晓',null,'05-9月-2007',null,1000,101）;
insertintoteachers
values（10328,'张笑',null,'29-9月-2007',null,1000,103）;
insertintoteachers
values（10228,'赵天宇',null,'18-9月-2007',null,1000,102）;
添加courses表数据
insertintocourses
values（10101,'计算机组成原理',4）;
insertintocourses
values（10201,'自动控制原理',4）;
insertintocourses
values（10301,'工程制图',3）;
insertintocourses
values（10102,'C++语言程序设计',3）;
insertintocourses
values（10202,'模拟电子技术',4）;
insertintocourses
values（10302,'理论力学',3）;
insertintocourses
values（10103,'离散数学',3）;
insertintocourses
values（10203,'数字电子技术',4）;
insertintocourses
values（10303,'材料力学',3）;
添加students_grade表数据
insertintostudents_grade
values（10101,10101,87）;
insertintostudents_grade
values（10101,10201,100）;
insertintostudents_grade
values（10101,10301,79）;
添加grades表数据
insertintogrades
values（1,0,59,'不及格'）;
insertintogrades
values（2,60,69,'及格'）;
insertintogrades
values（3,70,79,'中等'）;
insertintogrades
values（4,80,89,'良好'）;
insertintogrades
values（5,90,100,'优秀'）;
查看数据
Select*fromstudents;
Select*fromteachers;
Select*fromdepartments;
Select*fromcourses;
Select*fromstudents_grade;
Select*fromgrades;
单表查询
selectname,title,wage,hire_datefromteachers;
selectname,title,wage,to_char（hire_date,'YYYY-MM-DD'）FROMteachers;
select*fromstudents;
selectstudent_id,name,sex,specialty,dobfromstudents;
selectspecialtyfromstudents;
selectdistinctspecialtyfromstudents;
改变输出
selectnameas"姓名",dobas"生日"fromstudents;
selectname||'生日是'||dobas"学生生日清单"fromstudents;
selectnameas"姓名",bonus+wageas"月总收入"fromteachers;
空值处理
selectnameas"姓名",nvl（bonus,0）+wageas"月总收入"fromteachers;
selectnameas"姓名",nvl2（bonus,bonus+wage,wage）as"月总收入"fromteachers;
selectnameas"姓名",coalesce（bonus+wage,wage）as"月总收入"fromteachers;
条件查询
Selectname,hire_date,title,wage
Fromteacherswherewage>=2000;
Selectstudent_id,name,specialty,dob
Fromstudentswherespecialty='计算机';
Selectstudent_id,name,specialty,dob
Fromstudentswheredob<'1-1月-1990';
selectname,hire_date,title,bonus
fromteacherswherebonusin（500,600）;
selectstudent_id,name,specialty,dob
fromstudentswheredobin（'08-10月-1990','26-12月-1989'）;
selectname,hire_date,title,bonus
fromteacherswherebonusbetween500and600;
selectstudent_id,name,specialty,dob
fromstudentswherenamelike'王%';
selectname,hire_date,title,bonus
fromteacherswherebonusisnull;
10月25日
selectname,hire_date,title,bonus
fromteacherswheretitleisnull;
selectstudent_id,name,specialty,dob
fromstudentswheredobisnull;
selectstudent_id,name,sex,specialty
fromstudentswheredobbetween'1-1月-1989'and'1-1月-1990';
selectstudent_id,name,specialty,dob
fromstudents
wherespecialty='计算机'andsex='男';
组合使用逻辑条件
selectstudent_id,name,sex,specialty
fromstudentswherespecialty='计算机'andsex='女'
orspecialty='机电工程'andsex='男';
记录排序
Selectname,hire_date,title,bonus,wage
Fromteachersorderbywageasc;
selectstudent_id,name,specialty,dob
fromstudentsorderby2desc;
selectstudent_id,name,specialty,dob
fromstudentsorderbyspecialty,name;
分组查询
Selectavg（wage）fromteachers;
Selectdepartment_idfromteachersgroupbydepartment_id;
Selectdepartment_id,titlefromteachersgroupbydepartment_id,title;
Selectdepartment_id,avg（wage）fromteachers
Groupbydepartment_idhavingavg（wage）>2200;
子查询
Select*fromteachers
Wherewage<
（selectavg（wage）fromteachers）;
Selectcourse_id,course_namefromcourses
Wherecourse_idnotin
（selectcourse_id
Fromstudents_grade）;
Select*fromteachers
Wherewage（selectavg（wage）fromteachersgroupbydepartment_id）;
Select*fromteachers
Wherewage>all
（selectavg（wage）fromteachersgroupbydepartment_id）;
Select*fromteachers
Where（department_id,wage）in
（selectdepartment_id,min（wage）
fromteachersgroupbydepartment_id）;
Select*fromteacherst1
wherewage>
（selectavg（wage）fromteacherst2
wheret2.department_id=t1.department_id）;
selectcourse_id,course_namefromcoursesc
whereexists
（select2fromstudents_gradesg
wheresg.course_id=c.course_id）;
selectcourse_id,course_namefromcoursesc
wherenotexists
（select2fromstudents_gradesg
wheresg.course_id=c.course_id）;
selectdepartment_id,department_namefromdepartments
wheredepartment_idin
（selectdepartment_idfromteachers）;
建立辅修课程表MINORS
createtableminors（
minor_idnumber（5）
constraintminor_pkprimarykey,
minor_namevarchar2（30）notnull,
credit_hournumber
（2）
）;
为辅修课程表插入数据
Insertintominorsvalues（10101,'计算机组成原理',4）;
Insertintominorsvalues（10201,'自动控制原理',4）;
Insertintominorsvalues（10301,'工程制图',3）;
建立课程courses2表
createtablecourses2（
course_idnumber（5）
constraintcourse2_pkprimarykey,
course_namevarchar2（30）notnull,
credit_hournumber
（2）
）;
为课程courses2表插入数据
Insertintocourses2values（10201,'自动控制原理',4）;
Insertintocourses2values（10301,'工程制图',3）;
使用集合操作符
Selectcourse_id,course_name,credit_hour
Fromcourses
Unionall
Selectminor_id,minor_name,credit_hour
Fromminors;
Selectcourse_id,course_name,credit_hour
Fromcourses
Union
Selectminor_id,minor_name,credit_hour
Fromminors;
Selectcourse_id,course_name,credit_hour
Fromcourses
intersect
Selectminor_id,minor_name,credit_hour
Fromminors;
Selectcourse_id,course_name,credit_hour
Fromcourses
minus
Selectminor_id,minor_name,credit_hour
Fromminors;
Selectcourse_id,course_name,credit_hour
Fromcourses
union
Selectminor_id,minor_name,credit_hour
Fromminorsorderbycourse_name;
Selectcourse_id,course_nam
升级会员 