---
title: "数据库查询测试(一)"
author: "java1573"
date: "2007-07-30T16:39:32Z"
tags: []
slug: "untitled"
draft: false
lastmod: "2007-07-30T16:39:32Z"
id: 107023
url: "https://java1573.iteye.com/blog/107023"
---

--查询所有的字段怎么办

--查询所有学生的学号，并在前面加上“lovo”

--查询所有学生的学号、姓名( 显示中文字段:别名)

--查询各个考试日期(不显示时分秒)

--如何按姓名排序(升序，倒序)

--语文分从低到高排序(空值转换函数)

--如何查询没有输入年龄的学生信息(空值判断，不能…不能…)

--如何让年龄按从小到大排序

--如何求每个人的总分(语文+数学)

--查询年龄在20以上,语文在85以上的学生记录(复合条件:and,or)

--所有姓王的学生信息(模糊查询)

--姓名中包含“%%”的学生信息？(了解)  
select t.\*, t.rowid from t\_stu t  
where t.stu\_name like '%/%%' escape '/'

--已经有考试了哪些科目(重复值的过滤，d后面可以跟多个字段)

--想同时查看两个表所有学生的考试记录  
\--(表间连接：纵;去除重复数据;字段要对准)

--想知道两个表中一共考试了哪些科目

--查询成绩50-90范围内

--查询成绩不在50-90范围内

--查询1,3,5,9组同学的考试记录

--把表t\_exam中成绩大于60分的学生添加到t\_exam2

 

相关的两个表：

prompt PL/SQL Developer import file  
prompt Created on 2007年6月20日 by Administrator  
set feedback off  
set define off  
prompt Dropping T\_STU...  
drop table T\_STU cascade constraints;  
prompt Creating T\_STU...  
create table T\_STU  
(  
  STU\_ID   NUMBER not null,  
  STU\_NAME VARCHAR2(10),  
  STU\_SFZ  VARCHAR2(20),  
  AGE      NUMBER,  
  DATE\_REG DATE,  
  FEN\_YW   NUMBER(5,2),  
  FEN\_SX   NUMBER(5,2)  
)  
;  
comment on column T\_STU.STU\_ID  
  is '学号';  
comment on column T\_STU.STU\_NAME  
  is '姓名';  
comment on column T\_STU.STU\_SFZ  
  is '身份证';  
comment on column T\_STU.AGE  
  is '年龄';  
comment on column T\_STU.DATE\_REG  
  is '注册日期';  
comment on column T\_STU.FEN\_YW  
  is '语文';  
comment on column T\_STU.FEN\_SX  
  is '数学';  
alter table T\_STU  
  add constraint PK\_STU primary key (STU\_ID);  
alter table T\_STU  
  add constraint CHK\_1111  
  check (age&gt;16);

prompt Disabling triggers for T\_STU...  
alter table T\_STU disable all triggers;  
prompt Loading T\_STU...  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (107, '王1', '100004', 29, null, 90, null);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (108, '朱', '100005', 39, null, 80, 60);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (109, '王2', '100006', 49, null, 70.5, 80);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (110, '李', '100007', 99, null, 80, 50);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (104, '赵', '100001', null, to\_date('14-01-1990 11:12:13', 'dd-mm-yyyy hh24:mi:ss'), 58, null);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (105, '王3', '100002', null, to\_date('14-02-1985', 'dd-mm-yyyy'), null, 60);  
insert into T\_STU (STU\_ID, STU\_NAME, STU\_SFZ, AGE, DATE\_REG, FEN\_YW, FEN\_SX)  
values (106, '%孙', '100003', 19, to\_date('14-03-1980', 'dd-mm-yyyy'), null, 70);  
commit;  
prompt 7 records loaded  
prompt Enabling triggers for T\_STU...  
alter table T\_STU enable all triggers;  
set feedback on  
set define on  
prompt Done.

prompt PL/SQL Developer import file  
prompt Created on 2007年6月20日 by Administrator  
set feedback off  
set define off  
prompt Dropping T\_EXAM...  
drop table T\_EXAM cascade constraints;  
prompt Dropping T\_EXAM2...  
drop table T\_EXAM2 cascade constraints;  
prompt Creating T\_EXAM...  
create table T\_EXAM  
(  
  KS\_ID      NUMBER not null,  
  KS\_NAME    VARCHAR2(10),  
  KS\_DATE    DATE,  
  KS\_CJ      NUMBER(3),  
  STU\_ID     NUMBER,  
  GROUP\_NAME CHAR(4)  
)  
;  
alter table T\_EXAM  
  add constraint PK\_T\_KS primary key (KS\_ID);  
alter table T\_EXAM  
  add constraint PPP foreign key (STU\_ID)  
  references T\_STU (STU\_ID) on delete cascade;

prompt Creating T\_EXAM2...  
create table T\_EXAM2  
(  
  KS\_ID   NUMBER,  
  KS\_NAME VARCHAR2(10),  
  KS\_DATE DATE,  
  KS\_CJ   NUMBER(3),  
  STU\_ID  NUMBER  
)  
;

prompt Disabling triggers for T\_EXAM...  
alter table T\_EXAM disable all triggers;  
prompt Disabling triggers for T\_EXAM2...  
alter table T\_EXAM2 disable all triggers;  
prompt Disabling foreign key constraints for T\_EXAM...  
alter table T\_EXAM disable constraint PPP;  
prompt Loading T\_EXAM...  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (1, '语文', to\_date('19-06-2007', 'dd-mm-yyyy'), 20, 105, '1组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (2, '数学', to\_date('19-06-2007', 'dd-mm-yyyy'), 30, 105, '3组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (3, '语文', to\_date('19-06-2007', 'dd-mm-yyyy'), 40, 106, '4组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (4, '化学', to\_date('19-06-2007', 'dd-mm-yyyy'), 50, 106, '2组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (5, '物理', to\_date('19-06-2007', 'dd-mm-yyyy'), 60, 107, '3组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (6, '化学', to\_date('19-06-2007', 'dd-mm-yyyy'), 70, 107, '2组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (7, '语文', to\_date('19-06-2007', 'dd-mm-yyyy'), 80, 108, '1组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (8, '化学', to\_date('19-06-2007', 'dd-mm-yyyy'), null, 109, '2组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (9, '化学', to\_date('19-06-2007', 'dd-mm-yyyy'), 100, 110, '3组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (10, '语文', null, 60, 110, '8组 ');  
insert into T\_EXAM (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID, GROUP\_NAME)  
values (11, '英语', null, 70, 110, '9组 ');  
commit;  
prompt 11 records loaded  
prompt Loading T\_EXAM2...  
insert into T\_EXAM2 (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID)  
values (1, '德语', to\_date('19-06-2006', 'dd-mm-yyyy'), 20, 105);  
insert into T\_EXAM2 (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID)  
values (2, '法语', to\_date('19-06-2006', 'dd-mm-yyyy'), 30, 105);  
insert into T\_EXAM2 (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID)  
values (3, '语文', to\_date('19-06-2006', 'dd-mm-yyyy'), 40, 106);  
insert into T\_EXAM2 (KS\_ID, KS\_NAME, KS\_DATE, KS\_CJ, STU\_ID)  
values (4, '英文', to\_date('19-06-2006', 'dd-mm-yyyy'), 50, 106);  
commit;  
prompt 4 records loaded  
prompt Enabling foreign key constraints for T\_EXAM...  
alter table T\_EXAM enable constraint PPP;  
prompt Enabling triggers for T\_EXAM...  
alter table T\_EXAM enable all triggers;  
prompt Enabling triggers for T\_EXAM2...  
alter table T\_EXAM2 enable all triggers;  
set feedback on  
set define on  
prompt Done.