oracle 数据库增删改查练习50例-答案
一、建表--学生表drop table student;create table student (sno varchar2(10,sname varchar2(10,sage date,ssex varchar2(10;insert into student values('01','赵雷
',to_date('1990/01/01','yyyy/mm/dd','男';insert into student values('02','钱电
',to_date('1990/12/21','yyyy/mm/dd','男';insert into student values('03','孙风
',to_date('1990/05/20','yyyy/mm/dd','男';insert into student values('04','李云
',to_date('1990/08/06','yyyy/mm/dd','男';insert into student values('05','周梅
',to_date('1991/12/01','yyyy/mm/dd','女';insert into student values('06','吴兰
',to_date('1992/03/01','yyyy/mm/dd','女';insert into student values('07','郑竹
',to_date('1989/07/01','yyyy/mm/dd','女';insert into student values('08','王菊
',to_date('1990/01/20','yyyy/mm/dd','女';--课程表drop table course;create table course (cno varchar2(10,cname varchar2(10,tno varchar2(10;insert into course values ('01','语文','02';insert into course values ('02','数学','01';insert into course values ('03','英语','03';
--教师表drop table teacher;create table teacher (tno varchar2(10,tname
varchar2(10;insert into teacher values('01','张三';insert into teacher values('02','李四';insert into teacher values('03','王五';
--成绩表drop table sc;create table sc (sno varchar2(10,cno varchar2(10,score number(18,1;insert into sc values('01','01',80.0;insert into sc values('01','02',90.0;insert into sc values('01','03',99.0;insert into sc values('02','01',70.0;insert into sc
values('02','02',60.0;insert into sc values('02','03',80.0;insert into sc
values('03','01',80.0;insert into sc values('03','02',80.0;insert into sc
values('03','03',80.0;insert into sc values('04','01',50.0;insert into sc
values('04','02',30.0;insert into sc values('04','03',20.0;insert into sc
values('05','01',76.0;insert into sc values('05','02',87.0;insert into sc
values('06','01',31.0;insert into sc values('06','03',34.0;insert into sc
values('07','02',89.0;insert into sc values('07','03',98.0;
commit;
二、查询1.1、查询同时存在"01"课程和"02"课程的情况select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and https://www.doczj.com/doc/999645844.html,o = '01' and https://www.doczj.com/doc/999645844.html,o = '02';
1.2、查询必须存在"01"课程，"02"课程可以没有的情况
select t.*, s.score_01, s.score_02 from student t inner join (select a.sno, a.score score_01, b.score score_02 from sc a left join (select * from sc where cno = '02' b on (a.sno = b.sno where https://www.doczj.com/doc/999645844.html,o = '01' s on (t.sno = s.sno;
2.1、查询同时'01'课程比'02'课程分数低的数据
select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score from student s, sc sc1, sc sc2 where s.sno = sc1.sno and s.sno = sc2.sno and https://www.doczj.com/doc/999645844.html,o = '01' and https://www.doczj.com/doc/999645844.html,o = '02' and sc1.score < sc2.score;
2.2、查询同时'01'课程比'02'课程分数低或'01'缺考的数据select s.sno, s.sname, s.sage, s.ssex, t.score_01, t.score_02 from student s, (select b.sno, a.score score_01,
b.score score_02 from (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where a.sno(+ = b.sno t where s.sno = t.sno and (t.score_01 < t.score_02 or
t.score_01 is null;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩select s.sno, s.sname, t.avg_score avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno having avg(score >= 60 order by sno t where s.sno = t.sno;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩4.1、有考试成绩，且小于60分select s.sno, s.sname, t.avg_score avg_score from student s,
(select sno, round(avg(score, 2 avg_score from sc group by sno having avg(score < 60 order by sno t where s.sno = t.sno;
4.2、包括没有考试成绩的数据select g.* from (select s.sno, s.sname,
nvl(t.avg_score, 0 avg_score from student s, (select sno, round(avg(score, 2 avg_score from sc group by sno order by sno t where s.sno = t.sno(+ g where g.avg_score < 60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩5.1、查询所有成绩的（不含缺考的）。select s.sno,s.sname,c.count_cno,c.sum_scorefrom student s,(select sno,count(cno count_cno,sum(score sum_score from sc group by sno order by sno c
where s.sno = c.sno
5.2、查询所有成绩的(包括缺考的。select a.s_sno, a.s_sname, nvl(a.c_cno, 0,
a.s_score from (select s.sno s_sno, s.sname s_sname, c.count_cno c_cno, c.sum_score
s_score from student s, (select sno, count(cno count_cno, sum(score sum_score from sc group by sno order by sno c where s.sno = c.sno(+ a
6、查询"李"姓老师的数量（有几个老师姓李）select count(tn from (select tno, substr(tname, 0, 1 tn from teacher where substr(tname, 0, 1 = '李' a;
7、哪些学生上过张三（老师）的课select st.* from student st, course co, teacher te, sc where te.tno = co.tno and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o and sc.sno = st.sno and te.tname = '张三'
8、哪些学生没上过张三（老师）的课select * from studentminus select st.* from student st, course co, teacher te, sc where te.tno = co.tno and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o and sc.sno = st.sno and te.tname = '张三'
9、查询 '01' '02'都学过的同学的信息select st.* from student st, (select * from sc where cno = '01' a, (select * from sc where cno = '02' b where st.sno = a.sno and st.sno = b.sno
10、查询学过编号为'01'但是没有学过编号为'02'的课程的同学的信息select st.* from student st, ((select sno from sc where cno = '01' minus (select sno from sc where cno = '02' a where st.sno = a.sno;
11、查询没有学全所有课程的同学的信息11.1 学完所有课程的select st.* from student st, (select sno, count(cno from sc group by sno having count(cno = 3 a where st.sno = a.sno;
11.2 没有学完所有课程的select st.* from student st,
(select sno from student minus select sno from sc group by sno having count(cno = 3 a where st.sno = a.sno;
12、查询至少有一门课与学号为'01'的同学所学相同的同学的信息select st.* from student st, (select distinct sno from sc where cno in (select cno from sc where sno = '01' and sno != 1 a where st.sno = a.sno;
13、查询和'01'号的同学学习的课程完全相同的其他同学的信息select st.* from student st, (select sno from (select sno, count(cno CNT1 from sc group by sno a, (select count(cno CNT2 from sc where sno = '01' b where https://www.doczj.com/doc/999645844.html,T1 = https://www.doczj.com/doc/999645844.html,T2 and a.sno != '01' c where st.sno = c.sno;
14、查询没学过"张三"老师讲授的任一门课程的学生姓名select st.* from student st, (select sno from student minus select sno from sc where cno = (select https://www.doczj.com/doc/999645844.html,o from teacher t, course c where t.tno = c.tno and tname = '张三' a where st.sno = a.sno;
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select
a.sno, st.sname, a.avg_score from student st, (select sno, count(cno, round(avg(score, 2 avg_score from (select * from sc where score < 60 group by sno a where st.sno = a.sno;
16、检索'01'课程分数小于60，按分数降序排列的学生信息select st.*, https://www.doczj.com/doc/999645844.html,o,
a.score from student st, (select sno, cno, score from sc where cno = '01' and score < 60 order by score desc a where st.sno = a.sno;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select st.sno, st.sname, c.score, d.score, e.score, a.avg_score from student st, (select sno,
round(avg(score, 2 avg_score from sc group by sno
order by avg_score desc a, (select * from sc where cno = '01' c, (select * from sc where cno = '02' d, (select * from sc where cno = '03' e where st.sno = a.sno(+ and st.sno = c.sno(+ and st.sno = d.sno(+ and st.sno = e.sno(+
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课
程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率select
https://www.doczj.com/doc/999645844.html,o "课程编号", https://www.doczj.com/doc/999645844.html,ame "课程名称",
b.max_score "最高分",
c.min_score "最低分",
d.avg_socre "平均分",
e."及格率", e."中等率", e."优良率", e."优秀率" from course a, (select cno, max(score max_score from sc group by cno b, (select cno, min(score
min_score from sc group by cno c, (select cno, round(avg(score, 2 avg_socre from sc group by cno d, (select '01' cno, round((select count(1 from sc where cno = '01' and score >= 60 / (select count(1 from sc where cno = '01' * 100, 2 "及格率", round((select count(1 from sc where cno = '01' and score >= 70 and score < 80 / (select count(1 from sc where cno = '01' * 100, 2 "中等率", round((select count(1 from sc where cno = '01' and score >= 80 and score < 90 / (select count(1 from sc where cno = '01' * 100, 2 "优良率", round((select count(1 from sc where cno = '01' and score >= 90 / (select count(1 from sc where cno = '01' * 100, 2 "优秀率" from dual union select '02' cno, round((select count(1 from sc where cno = '02' and score >= 60 / (select count(1 from sc where cno = '02' * 100, 2 "及格率", round((select count(1 from sc where cno = '02' and score >= 70 and score < 80 / (select count(1 from sc where cno = '02' * 100, 2 "中等率",
round((select count(1 from sc where cno = '02' and score >= 80 and score < 90 / (select count(1 from sc where cno = '02' * 100, 2 "优良率", round((select count(1 from sc where cno = '02' and score >= 90 / (select count(1 from sc where cno = '02' * 100, 2 "优
秀率" from dual union select '03' cno, round((select count(1 from sc where cno = '03' and score >= 60 / (select count(1 from sc where cno = '03' * 100, 2 "及格率", round((select count(1 from sc where cno = '03' and score >= 70 and score < 80 / (select count(1 from sc where cno = '03' * 100, 2 "中等率", round((select count(1 from sc where cno = '03' and score >= 80 and score < 90 / (select count(1 from sc where cno = '03' * 100, 2 "优良率", round((select count(1 from sc where cno = '03' and score > 90 / (select count(1 from sc where cno = '03' * 100, 2 "优秀率" from dual e where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o;
19、按各科成绩进行排序，并显示排名select sno, cno, score,
rank( over(partition by cno order by score desc "名次" from sc;
select sno, cno, score, dense_rank( over(partition by cno order by score desc "名次" from sc;
20、查询学生的总成绩并进行排名20.1 查询学生的总成绩select a.sno, a.sname, nvl(b.sum_score, 0 "总成绩" from student a, (select sno, sum(score sum_score from sc group by sno order by sno b where a.sno = b.sno(+;
20.2 查询学生的总成绩并进行排名。select c."学生编号",c."学生姓名",c."总成绩",rank( over (order by c."总成绩" desc "排名"from (select a.sno "学生编号", a.sname "学生姓名", nvl(b.sum_score, 0 "总成绩" from student a, (select sno, sum(score
sum_score from sc group by sno order by sno b where a.sno = b.sno(+ c
21、查询不同老师所教不同课程平均分从高到低显示select a.tno, a.tname,
c.avg_score "平均分" from teacher a, course b, (select cno, round(avg(score, 2 avg_score from sc group by cno c where a.tno = b.tno and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o order by "平均分" desc;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩Score 重复时保留名次空缺select * from (select sno, cno, score, rank( over(partition by cno order by score desc order_sc from sc a where a.order_sc in (2, 3;
Score 重复时合并名次 select * from (select sno, cno, score,
dense_rank( over(partition by cno order by score desc order_sc from sc a where
a.order_sc in (2, 3;
23 、统计各科成绩各分数段人数：课程编号 , 课程名称,"100-85","85-70","70-60","0-60"及所占百分比23.1 统计各科成绩各分数段人数：课程编号 , 课程名称,"100-85","85-70","70-60","0-60"select https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, b."85-100", b."70-85", b."60-70", b."0-60" from course a, (select '01' cno, (select count(1 from sc where cno = '01' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '01' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '01' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '01' and score < 60 "0-60" from dual union select '02' cno, (select count(1 from sc
where cno = '02' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '02' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '02' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '02' and score < 60 "0-60" from dual union select '03' cno, (select count(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", (select count(1 from sc where cno = '03' and score >= 70 and score < 85 "70-85", (select count(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", (select count(1 from sc where cno = '03' and score < 60 "0-60" from dual b where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o;
纵向显示 1(显示存在的分数段;
select https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, b.ff, b.c1 from course a, (select cno, c1, '0-60' ff from (select cno, count(1 c1 from sc where score < 60 group by cno union select cno, c1, '60-70' ff from (select cno, count(1 c1 from sc where score >= 60 and score < 70 group by cno
union select cno, c1, '70-85' ff from (select cno, count(1 c1 from sc where score >= 70 and score < 85 group by cno union select cno, c1, '85-100' ff from (select cno, count(1 c1 from sc where score >= 85 and score <= 100
group by cno b where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
23.2 统计各科成绩各分数段人数：课程编号 , 课程名称,"100-85","85-70","70-60","<60"及所占百分比横向显示select https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, bb."0-60", bb."1百分比(%", bb."60-70", bb."2百分比(%", bb."70-85", bb."3百分比(%", bb."85-100", bb."4百分比(%" from course aa, ((select '01' cno, (select count(1 from sc where cno = '01' and score < 60 "0-60", round(((select count(1 from sc where cno = '01' and score < 60 * 100 / (select count(1 from sc where cno = '01', 2 "1百分比(%", (select count(1 from sc where cno = '01' and score >= 60 and score < 70 "60-70", round(((select count(1 from sc where cno = '01' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '01', 2 "2百分比(%", (select count(1 from sc where cno = '01' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '01' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '01', 2 "3百分比(%", (select count(1 from sc where cno = '01' and score >= 85 and score <= 100 "85-100", round(((select count(1 from sc where cno = '01' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '01', 2 "4百分比(%" from dual union (select '02' cno, (select count(1 from sc
where cno = '02' and score < 60 "0-60", round(((select count(1 from sc where cno = '02' and score < 60 * 100 / (select count(1 from sc where cno = '02', 2 "1百分比(%", (select count(1 from sc where cno = '02' and score >= 60 and score < 70 "60-70",
round(((select count(1 from sc where cno = '02' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '02', 2 "2百分比(%", (select count(1 from sc where cno = '02' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '02' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '02', 2 "3百分比(%", (select count(1 from sc where cno = '02' and score >= 85 and score <=
100 "85-100", round(((select count(1 from sc where cno = '02' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '02', 2 "4百分比(%" from dual union (select '03' cno, (select count(1 from sc where cno = '03' and score < 60 "0-60",
round(((select count(1 from sc where cno = '03' and score < 60 * 100 / (select count(1 from sc where cno = '03', 2 "1百分比(%", (select count(1 from sc where cno = '03' and score >= 60 and score < 70 "60-70", round(((select count(1 from sc where cno = '03' and score >= 60 and score < 70 * 100 / (select count(1 from sc where cno = '03', 2 "2百分比(%",
(select count(1 from sc where cno = '03' and score >= 70 and score < 85 "70-85", round(((select count(1 from sc where cno = '03' and score >= 70 and score < 85 * 100 / (select count(1 from sc where cno = '03', 2 "3百分比(%", (select count(1 from sc where cno = '03' and score >= 85 and score <= 100 "85-100", round(((select count(1 from sc where cno = '03' and score >= 85 and score <= 100 * 100 / (select count(1 from sc where cno = '03', 2 "4百分比(%" from dual bb where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o;
纵向显示
select t1.*, round(t1.num / t2.all_num * 100, 2 || '%' 百分比 from (select https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end as px, count(1 num from Course m, sc n where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o group by https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, (case when n.score >= 85 then '85-100' when n.score >= 70 and n.score < 85 then '70-85' when n.score >= 60 and n.score < 70 then '60-70' else '0-60' end order by https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, px t1, (select https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame, count(1 all_num from course m, sc n where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o group by https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame order by https://www.doczj.com/doc/999645844.html,o, https://www.doczj.com/doc/999645844.html,ame t2 where https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
24、查询学生的平均成绩并进行排名select c.sno, st.sname, c.avg_score, c.paim
from student st, (select b.*, rownum paim from (select a.sno, round(avg(score, 2 avg_score from (select s.sno sno, nvl(sc.score, 0 score from student s, sc where s.sno = sc.sno(+ a group by a.sno order by avg_score desc b c where st.sno = c.sno;
25、查询各科成绩前三名的记录25.1 分数重复时保留名次空缺select st.*,
https://www.doczj.com/doc/999645844.html,o, d.score from student st, (select a.* from (select * from sc where cno = '01' order by score desc a where rownum <= 3 union all select b.* from (select * from sc where cno = '02' order by score desc b where rownum <= 3 union all select c.* from (select * from sc where cno = '03' order by score desc c where rownum <= 3 d where st.sno = d.sno
25.2 分数重复时不保留名次空缺，合并名次select
st.sno,st.sname,st.sage,st.ssex,https://www.doczj.com/doc/999645844.html,o,b.score,b.paimfrom student st,(select
sno,cno,score,paim from (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc awhere a.paim <= 3 bwhere st.sno = b.sno
26、查询每门课程被选修的学生数select cno,count(1 from sc group by cno;
27、查询出只有两门课程的全部学生的学号和姓名 select st.sno, st.sname from student st, (select a.sno from (select sno, count(cno cn from sc group by sno a where https://www.doczj.com/doc/999645844.html, = 2 b where st.sno = b.sno;
28、查询男生、女生人数select '男生人数' "男女情况", cn from (select
count(ssex cn from student where ssex = '男'union allselect '女生人数' "男女情况", cn from (select count(ssex cn from student where ssex = '女'
29、查询名字中含有"风"字的学生信息select * from student where sname like '%风%'
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是dateselect * from student where to_char(sage,'yyyy' = '1990'
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列select https://www.doczj.com/doc/999645844.html,o ,https://www.doczj.com/doc/999645844.html,ame,a.avg_score
from course c,(select cno,round(avg(score,2 avg_score from sc group by cno awhere https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,oorder by a.avg_score desc ,https://www.doczj.com/doc/999645844.html,o;
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩select st.sno, st.sname, a.avg_score from student st, (select sno, round(avg(score, 2 avg_score from sc group by sno having round(avg(score, 2 >= 85 a where st.sno = a.sno;
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数select
st.sname,a.scorefrom student st,(select sno, score from sc where cno = (select cno from course where cname = '数学' and score < 60 awhere st.sno = a.sno
35、查询所有学生的课程及分数情况select st.*, https://www.doczj.com/doc/999645844.html,ame, https://www.doczj.com/doc/999645844.html,o, sc.score from student st, course c, sc where st.sno = sc.sno and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；select st.*,
https://www.doczj.com/doc/999645844.html,ame, https://www.doczj.com/doc/999645844.html,o, sc.score from student st, course c, sc, (select sno, min(score from sc group by sno having min(score >= 70 a where st.sno = a.sno and a.sno = sc.sno and
https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
37、查询不及格的课程 select st.*,https://www.doczj.com/doc/999645844.html,ame,https://www.doczj.com/doc/999645844.html,o,a.scorefrom student st,course c,(select * from sc where score < 60 awhere st.sno = a.snoand https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名select st.*,https://www.doczj.com/doc/999645844.html,ame,https://www.doczj.com/doc/999645844.html,o,a.scorefrom student st,course c,(select sno,cno,scorefrom scwhere cno = '01' and score >= 80 awhere st.sno = a.snoand https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
39、求每门课程的学生人数select https://www.doczj.com/doc/999645844.html,o,https://www.doczj.com/doc/999645844.html,ame,https://www.doczj.com/doc/999645844.html,from course c,(select
cno,count(sno cn from sc group by cno awhere https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩select st.*, https://www.doczj.com/doc/999645844.html,ame, https://www.doczj.com/doc/999645844.html,o, b.score
from student st, course c, (select * from sc where score = (select max(score from (select * from sc where cno = (select cno from course where tno = (select tno from teacher where tname = '张三' a and cno = (select cno from course where tno = (select tno from teacher where tname = '张三' b where st.sno = b.sno and https://www.doczj.com/doc/999645844.html,o = https://www.doczj.com/doc/999645844.html,o
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select distinct sc1.sno, https://www.doczj.com/doc/999645844.html,o, sc1.scorefrom sc sc1,sc sc2where sc1.score = sc2.scoreand
https://www.doczj.com/doc/999645844.html,o != https://www.doczj.com/doc/999645844.html,o
42、查询每门功成绩最好的前两名
select st.sno, st.sname, st.sage, st.ssex, https://www.doczj.com/doc/999645844.html,o, a.paim from student st, (select sno, cno, score, dense_rank( over(partition by cno order by score desc paim from sc a where st.sno = a.sno and a.paim <= 2
select st.sno, st.sname, st.sage, st.ssex, https://www.doczj.com/doc/999645844.html,o, a.paim from student st, (select sno, cno, score, rank( over(partition by cno order by score desc paim from sc a where st.sno =
a.sno and a.paim <= 2
44、检索至少选修两门课程的学生学号select st.sno,st.sname,https://www.doczj.com/doc/999645844.html,from student st,(select sno,count(cno cn from sc group by sno having count(cno >= 2 aawhere st.sno = aa.sno
46、查询各学生的年龄select st.*, (to_char(sysdate, 'yyyy' - to_char(st.sage, 'yyyy' "年龄" from student st
47、查询本周过生日的学生select st.* from student st where to_char(st.sage,
'mmdd' between to_char(trunc(sysdate, 'iw', 'mmdd' and to_char(trunc(sysdate, 'iw' + 6,
'mmdd'
48、查询下周过生日的学生select st.* from student st where to_char(st.sage,
'mmdd' between to_char(trunc(sysdate, 'iw'+7, 'mmdd' and to_char(trunc(sysdate, 'iw' + 13, 'mmdd'
select to_char(trunc(sysdate, 'iw'+7, 'mmdd' from dual; select to_char(trunc(sysdate, 'iw'+ 13, 'mmdd' from dual;
49、查询本月过生日的学生select st.* from student st where to_char(st.sage, 'mm' = to_char(sysdate,'mm'
50、查询下月过生日的学生select st.*from student st where to_char(st.sage, 'mm' = to_char(add_months(trunc(sysdate,1,'mm'
学习收藏数据库增删改查 --查询信息系和计算机系的学生，并按学生所在系和学号排序。select sno,sname,Sdept from Student where Sdept='CS'OR Sdept='IS' order by Sdept,sno ASC --查询学生表中最小的年龄。 select MIN(sage from student --查询课程名中包含“数据”的课程名。 select cno,cname from course where Cname like'%数据%' --查询先行课程为空值的课程号、课程名及学分 select cno,cname,ccredit from Course where Cpno is null --查询李勇选修的数据库课程的成绩 select grade from SC where Sno=(select Sno from Student where Sname='李勇'and Cno=(select Cno from Course where cname='数据库' --查询平均成绩分以上的学生的学号 select distinct sno from SC scx where (select AVG(Grade from SC scy
where scy.sno=scx.Sno>85 --求计算机系没有选修数据库课程的学生姓名 select sname from Student where Sno not in(select Sno from SC where Cno in(select Cno from Course where Sname='数据库'and Sdept='IS' --求至少选修了学号为S1所选修的全部课程的学生学号 select distinct sno from SC scx where not exists(select*from SC scy where scy.Sno='20021522'and not exists(select* from sc scz where scz.sno=scx.sno and https://www.doczj.com/doc/999645844.html,o=https://www.doczj.com/doc/999645844.html,o --求各系的系的学生人数的，并将结果按学生人数的降序排序 select Sdept,COUNT(sno from Student group by Sdept order by Sdept ASC --查询选修了数学课程并且成绩高于该门课程平均分的学生学号和成绩 select sno,grade from SC scx where Grade>=(select AVG(Grade from SC scy where Cno=(select Cno from Course where Cname='数学'and Cno=(select Cno from Course
A1、以下（）内存区不属于SGA。 A．PGA B．日志缓冲区C．数据缓冲区D．共享池 D2、（）模式存储数据库中数据字典的表和视图。 A．DBA B．SCOTT C．SYSTEM D．SYS C3、在Oracle中创建用户时，若未提及DEFAULT TABLESPACE 关键字，则Oracle就将（）表空间分配给用户作为默认表空间。 A．HR B．SCOTT C．SYSTEM D．SYS A4、（）服务监听并按受来自客户端应用程序的连接请求。 A．OracleHOME_NAMETNSListener B．OracleServiceSID C．OracleHOME_NAMEAgent D．OracleHOME_NAMEHTTPServer B5、（）函数通常用来计算累计排名、移动平均数和报表聚合等。 A．汇总B．分析C．分组D．单行 B6、（）SQL语句将为计算列SAL*12生成别名Annual Salary A．SELECT ename,sal*12 …Annual Salary? FROM emp; B．SELECT ename,sal*12 “Annual Salary” FROM emp; C．SELECT ename,sal*12 AS Annual Salary FROM emp; D．SELECT ename,sal*12 AS INITCAP(“Annual Salary”) FROM emp; B7、锁用于提供( )。 A．改进的性能
B．数据的完整性和一致性 C．可用性和易于维护 D．用户安全 C8、( )锁用于锁定表，允许其他用户查询表中的行和锁定表，但不允许插入、更新和删除行。 A．行共享B．行排他C．共享D．排他 B9、带有( )子句的SELECT语句可以在表的一行或多行上放置排他锁。 A．FOR INSERT B．FOR UPDATE C．FOR DELETE D．FOR REFRESH C10、使用( )命令可以在已分区表的第一个分区之前添加新分区。 A．添加分区B．截断分区 C．拆分分区D．不能在第一个分区前添加分区 C11、( )分区允许用户明确地控制无序行到分区的映射。 A．散列B．范围C．列表D．复合 C12、可以使用（）伪列来访问序列。 A．CURRVAL和NEXTVAL B．NEXTVAL和PREVAL C．CACHE和NOCACHE D．MAXVALUE和MINVALUE A13、带有错误的视图可使用（）选项来创建。 A．FORCE B．WITH CHECK OPTION C．CREATE VIEW WITH ERROR
数据库增删改查基本语句 adoquery1.Fielddefs[1].Name; 字段名 dbgrid1.columns[0].width:=10; dbgrid的字段宽度 adoquery1.Fields[i].DataType=ftString 字段类型 update jb_spzl set kp_item_name=upper(kp_item_name) 修改数据库表中某一列为大写select * from master.dbo.sysobjects ,jm_https://www.doczj.com/doc/999645844.html,ers 多库查询 adotable1.sort:='字段名称ASC' adotable排序 SQL常用语句一览 sp_password null,'新密码','sa' 修改数据库密码 (1)数据记录筛选： sql="select * from 数据表where 字段名=字段值orderby 字段名[desc] " sql="select * from 数据表where 字段名like '%字段值%' orderby 字段名[desc]" sql="select top10 * from 数据表where 字段名orderby 字段名[desc]" sql="select * from 数据表where 字段名in('值1','值2','值3')" sql="select * from 数据表where 字段名between 值1 and 值2" (2)更新数据记录： sql="update 数据表set 字段名=字段值where 条件表达式" sql="update 数据表set 字段1=值1,字段2=值2……字段n=值n where 条件表达式" (3)删除数据记录： sql="delete from 数据表where 条件表达式" sql="delete from 数据表"(将数据表所有记录删除) (4)添加数据记录： sql="insert into 数据表(字段1,字段2,字段3…) values(值1,值2,值3…)" sql="insert into 目标数据表select * from 源数据表"(把源数据表的记录添加到目标数据表)
ORACLE数据库试题 74 姓名：戴希引 一、选择题(总分50分，每题1分，*为多选)42 1．下列不属于ORACLE的逻辑结构的是（C） A 区 B 段 C 数据文件 D表空间 2. 下面哪个用户不是ORACLE缺省安装后就存在的用户( A ) A . SYSDBA B. SYSTEM C. SCOTT D. SYS 3 下面哪个操作会导致用户连接到ORACLE数据库，但不能创建表(A ) A 授予了CONNECT的角色，但没有授予RESOURCE的角色 B没有授予用户系统管理员的角色 C数据库实例没有启动 D数据库监听没有启动 4. 在Oracle中，当FETCH语句从游标获得数据时，下面叙述正确的是（ C ） A 游标打开 B游标关闭 C当前记录的数据加载到变量中 D创建变量保存当前记录的数据 5. 在Oracle中，下面关于函数描述正确的是（ A D）* A SYSDATE函数返回Oracle服务器的日期和时间 B ROUND数字函数按四舍五入原则返回指定十进制数最靠近的整数 C ADD_MONTHS日期函数返回指定两个月份天数的和 D SUBSTR函数从字符串指定的位置返回指定长度的子串 6. 阅读下面的PL/SQL程序块： BEGIN INSERT INTO employee(salary,last_name,first_name)VALUES(35000,’Wang’,'Fre d ’); SAVEPOINT save_a; INSERT INTO employee(salary,last_name,first_name) VALUES(40000,’Woo’,'David’); SAVEPOINT save_b; DELETE FROM employee WHERE dept_no=10; SAVEPOINT save_c; INSERT INTO employee(salary,last_name,first_name) VALUES(25000,’Lee’,'Bert’); ROLLBACK TO SAVEPOINT save_c;
一、增：有2种方法 1.使用insert插入单行数据： 语法：insert [into]<表名> [列名] values <列值> 例：insert into Strdents (姓名,性别,出生日期) values ('邢金聪','男','1990/6/15') 注意：如果省略表名，将依次插入所有列 2.使用insert,select语句将现有表中的数据添加到已有的新表中 语法：insert into <已有的新表> <列名> select <原表列名> from <原表名> 例：insert into addressList ('姓名','地址','电子邮件')selectname,address,email from Strdents 注意：查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致 二、删：有２中方法 1.使用delete删除数据某些数据 语法：delete from <表名> [where <删除条件>] 例：delete from awhere name='邢金聪'（删除表a中列值为邢金聪的行） 注意：删除整行不是删除单个字段，所以在delete后面不能出现字段名 2.使用truncate table 删除整个表的数据
语法：truncate table<表名> 例：truncate table addressList 注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能 用于有外建约束引用的表 三、改 使用update更新修改数据 语法：update <表名> set <列名=更新值> [where <更新条件>] 例：truncate table addressList 注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除；不能 用于有外建约束引用的表 四、查 1.普通查询 语法：select <列名> from <表名> [where <查询条件表达试>] [order by<排序的列 名>[asc或desc]] 1).查询所有数据行和列 例：select * from a 说明：查询a表中所有行和 2).查询部分行列--条件查询 例：select i,j,kfrom a where f=5 说明：查询表a中f=5的所有行，并显示i,j,k３列
第1章Oracle 11g简介 软件122班王平安 一、选择题 1．D 2．D 3．C 4．A 5．D 6．D 7．B 二、填空题 1．视图2．索引3．簇4．DB_BLOCK_SIZE 5．SYSTEM 6．数据文件7．日志文件8．控制文件 9．系统全局区（System Global Area，SGA） 10．程序全局区（Program Global Area，PGA） 11．数据库管理员12．安全官员 13．网络管理员14．应用程序开发员 15．应用程序管理员16．数据库用户。 三、简答题 1．答： 数据库由若干个表空间组成，表空间由表、索引、视图等逻辑对象组成，表由段组成，段由区组成，区则由数据块组成。 2．答： 物理结构由构成数据库的操作系统文件所决定。每个Oracle数据库都由3种类型的文件组成：数据文件、日志文件和控制文件。 Oracle数据库有一个或多个物理的数据文件。数据库的数据文件包含全部数据库数据。逻辑数据物理地存储在数据文件中。 每个数据库有两个或多个日志文件组，日志文件组用于收集数据库日志。日志的主要功能是记录对数据所作的修改，所以对数据库作的全部修改记录在日志中。在出现故障时，如果不能将修改数据永久地写入数据文件，则可利用日志得到修改记录，从而保证已经发生的操作成果不会丢失。 每个Oracle数据库有一个控制文件，记录数据库的物理结构。控制文件包含数据库名、数据库数据文件和日志文件的名字、位置和数据库建立日期等信息。 3．答： SGA是一组共享内存结构，其中包含一个Oracle数据库例程数据及控制信息。如果有多个用户同时连接到同一个例程，则此例程的SGA数据由这些用户共享。因此，SGA也称为共享全局区（Shared Global Area）。 SGA包含以下数据结构。
O r a c l e数据库试题及 答案
Oracle数据库试题 一、选择题 1、段是表空间中一种逻辑存储结构，以下（ D ）不是ORACLE数据库使用的段类型。 （A）索引段（B）临时段（C）回滚段（D）代码段 2、 ORACLE数据库物理结构包括以下三种文件，以下不属于的是（ A ）。（A）系统文件（B）日志文件（C）数据文件（D）控制文件 3、数据库中有两个用户scott和muuser，物资表wz是属于myuser用户的，但当前用户是scott，要求查询物资表wz（wno，wname，wtype，wunit）物资单位wunit列为null的记录，取结果的前5条记录显示，以下正确的SQL语句是（ C ） （A）select*from scott.wz where wunit is null and rownum<5； （B）select*from myuser.wz where wunit = null and rownum<5； (C) select*from myuser.wz where wunit is null and rownum<6； （D）select*form scott.wz where wunit is null and rownum<6； 4、若当前日期为’25-5月-06’，以下（ D ）表达式能计算出5个月后那一天所在月份的最后一天的日期。 （A）NEXT_DAY(ADD_MONTHS(28-5月-06,5)) （B）NEXT_DAY(MONTHS_BETWEEN(28-5月-06,5)) （C）NEXT_DAY(MONTHS_BETWEEN(’28-5月-06’,5)) （D）NEXT_DAY(ADD_MONTHS(’28-5月-06’,5))
请将下列试题的正确答案写在答题栏里。（每小题2分） 1.当Oracle服务器启动时，下列哪种文件不是必须的（）。 A．数据文件 B．控制文件 C．日志文件 D．归档日志文件 2.在Oracle中，当用户要执行SELECT语句时，下列哪个进程从磁盘获得用户需要的数据（）。 A．用户进程 B．服务器进程 C．日志写入进程（LGWR D．检查点进程（CKPT） 3.在Oracle中，一个用户拥有的所有数据库对象统称为（）。 A．数据库 B．模式 C．表空间 D．实例 4.在Oracle中，有一个教师表teacher的结构如下： ID NUMBER(5) NAME V ARCHAR2(25) EMAIL VARCHAR2(50) 下面哪个语句显示没有Email地址的教师姓名（）。 A．SELECT name FROM teacher WHERE email = NULL; B．SELECT name FROM teacher WHERE email <> NULL; C．SELECT name FROM teacher WHERE email IS NULL; D．SELECT name FROM teacher WHERE email IS NOT NULL; 5.在Oracle数据库的逻辑结构中有以下组件： A 表空间 B 数据块 C 区 D 段 这些组件从大到小依次是（）。 A．A→B→C→D B．A→D→C→B C．A→C→B→D D．D→A→C→B 6.在Windows操作系统中，Oracle的（）服务监听并接受来自客户端应用程序的连接请求。 A．OracleHOME_NAMETNSListener
JAVA 数据库基本操作, 增删改查 package mypack; JAVA 数据库基本操作, 增删改查 import java.sql.Connection; import java.sql.ResultSet; import java.util.ArrayList; public class DbOper {//查询多行记录public ArrayList select({Connection conn =null; ResultSet rs =null; try {import java.sql.PreparedStatement; import java.sql.SQLException; PreparedStatement pstmt =null; ArrayList al =new ArrayList(; conn =DbConn.getConn(;pstmt =conn.prepareStatement(“select *from titles ”; rs =pstmt.executeQuery(;while (rs.next({Titles t =new Titles(;t.setTitleid(rs.getString(1;t.setTitle(rs.getString(2;al.add(t;}}catch (SQLExceptione { e.printStackTrace(;}finally {try {//TODO 自动生成catch 块if (rs!=null rs.close(;if (pstmt!=nullpstmt.close(;if (conn!=nullconn.close(;}catch (SQLExceptione { e.printStackTrace(;}}//TODO 自动生成catch 块 return al; }//查询单个对象public Titles selectOne(Stringtitleid{Connection conn =null; ResultSet rs =null; try {PreparedStatement pstmt =null; Titles t =new Titles(;
oracle数据库期末考试试题及答案
A13、带有错误的视图可使用（）选项来创建。 A．FORCE B．WITH CHECK OPTION C．CREATE VIEW WITH ERROR D．CREATE ERROR VIEW B14、在联接视图中，当（）时，该基表被称为键保留表。 A．基表的主键不是结果集的主键 B．基表的主键是结果集的主键 C．基表的主键是结果集的外键 D．基表的主键不是结果集的外键 D15、在列的取值重复率比较高的列上，适合创建（）索引。 A．标准B．唯一C．分区D．位图 C16、要以自身的模式创建私有同义词，用户必须拥有（）系统权限 A．CREATE PRIVATE SYNONYM B．CREATE PUBLIC SYNONYM C．CREATE SYNONYM D．CREATE ANY SYNONYM D17、PL/SQL块中不能直接使用的SQL命令是（）。 A．SELECT B．INSERT C．UPDATE D．DROP B18、以零作除数时会引发（）异常。
A．VALUE_ERROR B．ZERO_DIVIDE C．STORAGE_ERROR D．SELF_IS_NULL A19、要更新游标结果集中的当前行，应使用（）子句。 A．WHERE CURRENT OF B．FOR UPDATE C．FOR DELETE D．FOR MODIFY B20、用于处理得到单行查询结果的游标为（）。 A．循环游标B．隐式游标 C．REF游标D．显式游标 C21、公用的子程序和常量在（）中声明。 A．过程B．游标 C．包规范D．包主体 C22、数据字典视图（）包含存储过程的代码文本A．USER_OBJECTS B．USER_TEXT C．USER_SOURCE D．USER_DESC C23、以下不属于命名的PL/SQL块的是（）。 A．程序包B．过程 C．游标D．函数 A24、（）包用于显示PL/SQL块和存储过程中的调试信息。 A．DBMS_OUTPUT B．DBMS_STANDARD C．DBMS_INPUT D．DBMS_SESSION
mysql 增、删、改、查基本语句 数据库的链接和选择及编码 $link=mysql_connect("localhost","root","123456") or die("数据库连接失败".mysql_error()); $sel=mysql_select_db("login",$link) or die("数据库选择失败".mysql_error()); mysql_query("set names 'utf8'"); 添加数据 $link=mysql_connect("localhost","root","123456") or die("数据库连接失败".mysql_error()); $sel=mysql_select_db("login",$link) or die("数据库选择失败".mysql_error()); mysql_query("set names 'utf8'",$sel); $add="insert into title(title,content,username,time) values('$title','$content','$username',$time)"; $query=mysql_query($add); if($query){ echo "add sucess"; echo ""; } else echo "add false"; 删除数据 $link=mysql_connect("localhost","root","123456") or die("数据库连接失败".mysql_error()); $sel=mysql_select_db("login",$link) or die("数据库选择失败".mysql_error()); mysql_query("set names 'utf8'"); $id=$_GET['id']; $delete="delete from title where id='$id'"; $query=mysql_query($delete); if($query){ echo "delete sucess!"; echo ""; } else echo "delete false"; 改数据 $link=mysql_connect("localhost","root","123456") or die("数据库连接失败".mysql_error()); $sel=mysql_select_db("login",$link) or die("数据库选择失败".mysql_error()); mysql_query("set names 'utf8'",$sel);
02.连接命令：mysql -h[主机地址] -u[用户名] -p[用户密码] 03.创建数据库：create database [库名] 04.显示所有数据库: show databases; 05.打开数据库:use [库名] 06.当前选择的库状态:SELECT DATABASE(); 07.创建数据表:CREATE TABLE [表名]([字段名] [字段类型]([字段要求]) [字段参数], ......); 08.显示数据表字段:describe 表名; 09.当前库数据表结构:show tables; 10.更改表格 11. ALTER TABLE [表名] ADD COLUMN [字段名] DATATYPE 12. 说明：增加一个栏位（没有删除某个栏位的语法。 13. ALTER TABLE [表名] ADD PRIMARY KEY ([字段名]) 14. 说明：更改表得的定义把某个栏位设为主键。 15. ALTER TABLE [表名] DROP PRIMARY KEY ([字段名]) 16. 说明：把主键的定义删除。 17.显示当前表字段:show columns from tablename; 18.删库:drop database [库名]; 19.删表:drop table [表名]； 20.数据操作 21.添加：INSERT INTO [表名] VALUES('','',......顺序排列的数据); 22.查询: SELECT * FROM [表名] WHERE ([条件]); 23.建立索引:CREATE INDEX [索引文件名] ON [表名] ([字段名]); 24.删除：DELETE FROM [表名] WHERE ([条件]); 25.修改：UPDATE [表名] SET [修改内容如name = 'Mary'] WHERE [条件]； 26. 27.导入外部数据文本: 28.1.执行外部的sql脚本 29.当前数据库上执行:mysql < input.sql 30.指定数据库上执行:mysql [表名] < input.sql 31.2.数据传入命令load data local infile "[文件名]" into table [表名]; 32.备份数据库：(dos下) 33.mysqldump --opt school>school.bbb 34. 35. 36. 37.提示：常用MySQL命令以";"结束,有少量特殊命令不能加";"结束，如备份数据库 38.一. 增删改查操作 39. 40.============================================================================ ===== 41.1. 增: 42.insert into 表名values(0,'测试'); 43.注：如上语句，表结构中有自动增长的列，也必须为其指定一个值，通常为0 44.insert into 表名(id,name) values(0,'尹当')--同上
Oracle数据库试题 一、选择题 1、段就是表空间中一种逻辑存储结构,以下( D )不就是ORACLE数据库使用的段类型。 (A) 索引段 (B)临时段 (C)回滚段(D)代码段 2、 ORACLE数据库物理结构包括以下三种文件,以下不属于的就是( A )。 (A) 系统文件 (B)日志文件(C)数据文件 (D)控制文件 3、数据库中有两个用户scott与muuser,物资表wz就是属于myuser用户的,但当前用户就是scott,要求查询物资表wz(wno,wname,wtype,wunit)物资单位wunit列为null的记录,取结果的前5条记录显示,以下正确的SQL语句就是( C ) (A)select*from scott、wz where wunit is null and rownum<5; (B)select*from myuser、wz where wunit = null and rownum<5; (C) select*from myuser、wz where wunit is null and rownum<6; (D)select*form scott、wz where wunit is null and rownum<6; 4、若当前日期为’25-5月-06’,以下( D )表达式能计算出5个月后那一天所在月份的最后一天的日期。 (A)NEXT_DAY(ADD_MONTHS(28-5月-06,5)) (B)NEXT_DAY(MONTHS_BETWEEN(28-5月-06,5)) (C)NEXT_DAY(MONTHS_BETWEEN(’28-5月-06’,5)) (D)NEXT_DAY(ADD_MONTHS(’28-5月-06’,5)) 5、下列叙述中正确的就是_C_。 A、数据库就是一个独立的系统,不需要操作系统的支持 B、数据库设计就是指设计数据库管理系统 C、数据库技术的根本目标就是要解决数据共享的问题 D、数据库系统中,数据的物理结构必须与逻辑结构一致 6、SQL语句中修改表结构的命令就是 C 。 A、MODIFY TABLE B、MODIFY STRUCTURE C、ALTER TABLE D、ALTER STRUCTURE 7、数据库DB、数据库系统DBS、数据库管理系统DBMS三者之间的关系就是__ A___。 A、DBS包括DB与DBMS B、DBMS包括DB与DBS C、DB包括DBS与DBMS D、DBS就就是DB,也就就是DBMS 8、要控制两个表中数据的完整性与一致性可以设置"参照完整性",要求这两个表_A___。 A、就是同一个数据库中的两个表 B、不同数据库中的两个表 C、两个自由表 D、一个就是数据库表另一个就是自由表 9、在关系模型中,实现"关系中不允许出现相同的元组"的约束就是通过__B_ __。 A、候选键 B、主键 C、外键 D、超键 10、索引字段值不唯一,应该选择的索引类型为___ B__。 A、主索引 B、普通索引 C、候选索引 D、唯一索引 11、从数据库中删除表的命令就是___C __。 A、DROP TABLE B、ALTER TABLE C、DELETE TABLE D、USE 12、DELETE FROM S WHERE 年龄>60语句的功能就是___B___。 A、从S表中彻底删除年龄大于60岁的记录
SQLSQL常用增删改查语句 作者：hiker 一．Insert 插入语句 1.Insert into 表名（列名）values (对应列名值) //插入一行. 2.Insert into 新表名（列名） Select (列名) 旧表名 3.Select 旧表名.字段… Into 新表名from 旧表名 4.Select identity ( 数据类型，标识种子，标识增长量) as 列名 Into新表名 From 旧表名 5.Insert 表名（列名） Select (对应列名值) union Select (对应列名值) union Select (对应列名值) 二．Update 更新语句 1.Update 表名set 列名=’更新值’ where 更新条件 三．delete 删除语句 1.delete from 表名where 删除条件 2.truncate table 表名//删除表中所有行 四．select 基本查询语句 1.select 列名from 表名where 查询条件 order by 排序的列名asc或desc升/降 2.select 列名as 别名from 表名where 查询条件 3.select 列名from 表名where 列名is null //查询空值 4.select 列名, ‘常量值’ as 别名from 表名//查询时定义输出一列常量值 5.select top 5 列名from 表名//查询前5行 6.select top 5 percent 列名from 表名//查询前百分之5的数据行 五．select 函数查询语句 1.select LEN(Class_Name)from Class //查询class_Name字符串长度 2.select upper(Class_Name)from Class //查询class_Name并转换为大写 3.ltrim和rtrim //清除字符串左右空格 4.select REPLACE(card_No,'0','9')from CardRecord//修改列中字符串中的字符 列名字符串中0修改为9 5.select STUFF(Card_No,2,3,'8888')from CardRecord 列名字符串中第2个开始删除3个字符，再从第二个开始插入8888字符串 6.select GETDATE()//显示系统日期
数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复 操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘） 2. 你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题呢 解答：重建控制文件，用带backup control file 子句的recover 命令恢复数据库。 3. 如何转换到spfile 解答：使用create spfile from pfile 命令 . 4. 解释data block , extent 和segment的区别（这里建议用英文术语） 解答：data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent . 一 个数据库对象拥有的所有extents被称为该对象的segment. 5. 给出两个检查表结构的方法 解答：1、DESCRIBE命令 2、包 6. 怎样查看数据库引擎的报错 解答：alert log. 7. 比较truncate和delete 命令 解答：两者都可以用来删除表中所有的记录。区别在于：truncate是DDL操作，它移动HWK，不需要rollback segment .而Delete是DML操作 , 需要rollback segment 且花费较长时间. 8. 使用索引的理由 解答：快速访问表中的data block 9. 给出在STAR SCHEMA中的两种表及它们分别含有的数据 解答：Fact tables 和dimension tables. fact table 包含大量的主要的信息而dimension tables 存放对fact table 某些属性描述的
数据库增删改查操作（2） 一、案例描述 程序运行的时候加载的界面如图1，包括添加、删除、修改和查询所有数据按钮，并实现相应功能。 图1 图2 单击“添加”按钮，插入一条记录，效果如图2；单击“查询全部”按钮，查询得到全部记录，并将结果打印出来，如图3；单击“删除”按钮，删除指定记录，如图4；单击“修改”按钮，修改指定记录，如图5。 图3
图4 图5 二、知识链接 （4）ContentValues类 ● ContentValues类和Hashtable类较为相似，它用于存储一些键值对，可以被ContentResolver类处理，但是它存储的键值对当中的键是一个String类型，而值都是基本类型。ContentValues类作为一个重要的参数在SQLiteDatabase中的insert，update等方法中使用。 ● ContentValues类有3种构造方法，格式分别如下： √ ContentValues(：使用默认的初始大小来创建一个空集。 √ ContentValues（int size）：使用指定的初始大小size值来创建一个空集。 √ ContentValues（ContentValues from）：复制给定的集合from，用于创建一组集合数值。 ● ContentValues类的常用方法介绍如下：
√ void clear(：清空集合中的所有数值。 √ boolean containsKey（String key）：如果集合中包含了指定的关键字key，则返回true，否则返回false。 √ Object get（String key）：返回关键字key对应的数值，返回数值类型为Object，通常还需要进行强制类型转换。 √ void put（String key，Integer value）：将一个值加入到集合中，其中参数key表示集合中的关键字；参数value表示要添加的数据。ContentValues类还有很多put方法，主要的区别是第二个参数为其他数据类型，例如：put（String key，Byte value）、put（String key，Float value）、put（String key，Short value）、put（String key，byte[] value） √ void remove（String key）：将某个关键字key的数值从集合中删除。 √ int size (：返回集合中数值的个数。 三、案例实现 （1）MainActivity.java中添加数据的方法 /** * 添加一条记录 */ public void add(View view { Random random=new Random(; SQLiteDatabase db=personHelper.getWritableDatabase(; //db.execSQL("insert into info(name,phone values(?,?",new Object[]{"张三"+random.nextInt(100,"186********"}; ContentValues values=new ContentValues(; values.put("name", "张三"+random.nextInt(100; values.put("phone", "186775411"+random.nextInt(100;
ＯRAＣLＥ数据库基础测试题 提示:本题为ORACLE数据库基础测试题，适合初学者对基础知识进行测试，以便查漏补缺。 1、 DDＬ指得就是：（)。 A、数据定义语言 B、数据操作语言 C、数据查询语言 D、数据控制语言 正确答案:A解析: 2、 下列创建表语句正确得就是:（）. A、crｅate table eｍp（id ｎuｍber（４)，）; B、cｒeａｔeｔabｌe emp（id numｂer(4）)； C、aｌｔer tａbｌe ｅｍp（id numbeｒ（4）) ; D、alｔer ｔabｌｅｅｍp(id nｕｍｂeｒ（4），) ；
正确答案：B解析： 3、 下列Oraｃle函数中能够返回两个字符串连接后得结果得就是：()。 A、iniｔｃaｐ B、instr Ｃ、ｔｒim D、conｃａt 正确答案:D解析： 4、 下列ＳＱL语句得查询结果就是:（）。ｓeleｃｔround（45、９25，0)，trunc(45、92５)frｏｍduaｌ； Ａ、４54５ Ｂ、46４５ Ｃ、4５4６ D、46 46 正确答案:Ｂ解析:
5、 关于函数ｎvl（ｄ1，ｄ2)得用法说法正确得就是:（）。 A、表示如果d1为null则ｄ2必须为nuｌl B、表示如果ｄ1为null则忽略d２ C、表示如果d１不为nuｌl则用d２替代 D、表示如果ｄ1为nｕll则用d2替代 正确答案:Ｄ解析: 6、 显示emp表得所有行，所有列，下列SQL语句正确得就是：(）。 A、selecｔ＊froｍemｐ; B、seｌeｃt ａlｌ、＊from ｅmp; C、seｌecｔａllｆroｍemp； D、sｅleｃt/* from ｅmp； 正确答案:A解析： 7、 查询职员得姓名及其直接领导,如果没有直接领导得职员，则显示为“NoＭanager",下列SＱL语句正确得就是：（）。
本文由我司收集整编，推荐下载，如有疑问，请与我司联系mysql数据库的连接以及增删改查Java代码实现（PreparedStatement 版） 2015/05/27 0 数据库： ?create table t1(id int primary key not null auto_increment,name varchar(32),password varchar(32));insert into t1(name,password) values( admin , 123 insert into t1(name,password) values( zhangsan , 123 insert into t1(name,password) values( lisi , 123 Java代码： ?mySqlConnection.java代码： ?package com.dbdao.mysql;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;public class mySqlConnection {//创建数据库驱动名称private static String Driver_ >//数据库链接地址private String url= jdbc:mysql://localhost:3306/test //数据库用户名private String user= root //数据库密码private String password= 11 //数据库链接private Connection con=null;//准备声明sql语句private PreparedStatement pstmt=null;//结果集private ResultSet rs=null;//影响行数private int i;/* * 创建驱动* */static{try {Class.forName(Driver_class);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/* * 加载驱动* */@Testpublic void getConnect() {// TODO Auto-generated method stubtry {con=DriverManager.getConnection(url, user, password);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}//判断数据库是否加载成功if(con!=null){System.out.println( 数据库加载成功！}else{System.out.println( 数据库加载失败！}}/* * 执行sql语句* */public void doSql(String sql,Object[] object) {// TODO Auto-generated method stub//判断sql语句是否存在if(sql!=null){//加载驱动getConnect();//判断object数组是否存在if(object==null){//如果不存在，创建一个，