ORACLE_笔记_练习题目

浅浅的花香味﹌ 2022-12-08 06:33 129阅读 0赞

一.plsql用法网址及时复习

extract()函数----用于截取年、月、日、时、分、秒

https://www.cnblogs.com/xqzt/p/4477239.html

case when 不同位置用法不同，可用于求显示等级、及格率等

https://www.cnblogs.com/soundcode/p/5549901.html

merge into

https://www.cnblogs.com/kongxc/p/9237941.html

where 和having 的区别

https://www.cnblogs.com/jameshappy/archive/2012/05/23/2515034.html

constraint约束

https://blog.csdn.net/qqww120102/article/details/79791396

trigger触发器

producers存储过程

consequences序列

index索引

cursor游标

二.写SQL前注意点：

0.查询字段必要的加注释，取名字

1.单表查询不要取别名

2.别名不要用关键字

3.表不多时候用 A B C D E 取别名而不用缩写

4.聚集函数多和分组函数group by 联用求均值

5.分类后求第几个一般用row\_num()rank函数 而不用 group by

\*6.exists 查询效率一般比in 高 因为in不走索引，in适合外表大内表小  exists 适合外表小内表大。(只要内表和外表有关联ID 就可以使用)

用exists 替换步骤 优化sql如下例：

假如有一个表user，它有两个字段id和name，我们要查询名字中带a的用户信息：

a.　最简单的SQL：select \* from user where name like '%a%';

b.　使用IN的SQL：select u.\* from user u where u.id in (select uu.id from user uu where uu.name like '%a%');

我们现在将使用IN的SQL修改为使用EXISTS的SQL该怎么写呢？

c.　一开始我直接将u.id in 替换为EXISTS，获得如下语句，发现把结果全部查询出来了 ：

select u.\* from user u where exists(select uu.id from user uu where uu.name like '%a%');

d.最终末尾加上关联

select u.\* from user u where exists (select uu.id from user uu where uu.name like '%a%' and uu.id=u.id);

原因解释：

总结：EXISTS子查询可以看成是一个独立的查询系统，只为了获取真假逻辑值，EXISTS子查询与外查询查询的表是两个完全独立的毫无关系的表(当第二个表中的name中有包含a的姓名存在，那么就执行在第一个表中查询所有用户的操作)，当我们在子查询中添加了id关联之后，EXISTS子查询与外查询查询的表就统一了，是二者组合组建的虚表，是同一个表(这样当子查询查询到虚表中当前行的uu.name中包含a时，则将虚表当前行中对应的u.id与u.name查询到了)

所以一切的重点就在这个ID关联之上，添加ID关联，数据库会先将两张表通过ID关联组合成一张虚表，所有的查询操作都在这张虚表上完成，操作的是同一张表，当然就不会出现之前的那种情况了！

例子使用exists语句显示BLAKE所在部门的其他所有雇员，但是不要显示BLAKE

WHERE EXISTS(子查询) ,子查询返回的的是TRUE OR FALSE 即使是

SELECT \* FROM DEPR WHERE EXISTS (SELECT NULL)

\--正确写法 关键在于内表外表相同组成的同一虚表

SELECT \*

FROM EMP B  关键点

WHERE EXISTS

(SELECT \*

FROM EMP C     --关键点

WHERE C.EMPNO = B.EMPNO

AND C.ENAME <> 'BLAKE'

AND C.DEPTNO =

(SELECT A.DEPTNO FROM EMP A WHERE A.ENAME = 'BLAKE'))

7.lpad( string, padded\_length, \[ pad\_string \] ) 填充在左侧使其左对齐  rpad 填充在右测  一般price习惯lpad对齐，pad\_string不指定默认是空格

8. NUMBER 四则运算 要注意 用NVL(EX1,EX2) 因为number+null=null，如果ex1为空返回ex2，如果ex1 不为空返回ex1，作用就是不返回空值

\*9.重点掌握 row\_number()rank 自主平时多找练习做

10. case when 两种表现形式，3位置不同的用法：10.1 SELECT CASE WHEN 用法 10.2 WHERE CASE WHEN...  10.3 GROUP BY

CASE WHEN salary <= 500 THEN '1' WHEN ....

11.left join 易错点 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了，条件不为真的就全部过滤掉。

总结：如果想要查询某列所在行所有信息，不要在where 后加过分的限制条件，

12.对时间操作多用round(to\_number(to\_char(sysdate,'yyyy')),2)  ;插入时间字符串 转换成 date类型到数据库中:to\_date('2019/1/1','yyyy-mm-dd')

\*13.merge into TABLE\_A USING TABLE\_B  用B表更新A表 一般用法： 有多种模式  1.正常模式先插入再修改  2.只插入 3.只修改

MERGE INTO A\_MERGE A USING (select B.AID,B.NAME,B.YEAR from B\_MERGE B) C ON (A.id=C.AID)

WHEN MATCHED THEN

UPDATE SET A.YEAR=C.YEAR

WHEN NOT MATCHED THEN

INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);

commit;

14.where 和 having的区别

它们的相似之处就是定义搜索条件，不同之处是where子句为单个筛选而having子句与组有关，而不是与单个的行有关。

最后:理解having子句和where子句最好的方法就是基础select语句中的那些句子的处理次序：where子句只能接收from子句输出的数据，而having子句则可以接受来自group by，where或者from子句的输入。

15.TABLE\_A LEFT JOIN TABLE\_B ON A.AID=B.AID 查询数量为A表中每个AID×B表对应的AID的数量和+A表中有而B表中没有的数量=总查询数量，如果加上了where 则会更少

SELECT  DEPT.DEPTNO FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO 查询到15条信息

SELECT  DEPT.DEPTNO FROM DEPT RIGHT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO 查询到14条信息因为是以EMP为基础

SELECT  DEPT.DEPTNO FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO WHERE EMP.DEPTNO=DEPT.DEPTNO 查询到14条信息 本来左连接有15条数据，但是where 对结果进行筛选去掉了单独的deptno=40这个数据，因为它没有和EMP表关联

SELECT \* FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO  14条数据，因为以EMP为基础

SELECT \* FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 15条数据，因为以DEPT为基础 但是结果左边会出现空值，所以把两个表交换一下位置再重新用左连接

/\*1.  没有使用子查询的理由；

查询老师：谌燕 ，所教授的课程；

\*/

SELECT hc.COURSE\_NO, hc.COURSE\_NAME

FROM HAND\_COURSE hc

WHERE TEACHER\_NO =

(SELECT TEACHER\_NO FROM HAND\_TEACHER WHERE TEACHER\_NAME = '谌燕');

select h.\*,rowid from hzd\_table h where h.age between 18 and 28

select HT.\*  from HZD\_TABLE HT where HT.NAME like '%a\_%' escape 'a';

select distinct  sysdate  "aa" ,rowid from dual

select sysdate||sysdate  "aa" ,rowid from dual

select to\_char(sysdate),to\_char(sysdate,'yyyy-MM-dd HH24:mm:ss'), to\_date(sysdate),to\_date(to\_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')from dual

where to\_char(sysdate,'yyyy-MM-dd')='2019-11-02'

三.触发器

\------------------------------------------触发器trigger 测试----------------------------------------------------

create or replace trigger update\_hzd\_table\_trigger

after

update on hzd\_table

for each row

begin dbms\_output.put\_line('触发器测试')  ;

end;

update hzd\_table ht set ht.age=19 where ht.age<55

select h.\*,length(h.id),length(h.name),length('hzd1') aa,length('Oracle'),rowid from hzd\_table h

\-------------------------服务器端使用哪种编码   GBK 一个中文16位占用2字节，UTF-8 32位 一个中文占用三个字节-----------

select userenv('language') from dual;

select h.\*,length('GBK一个汉字占用两个字节') ,rowid from hzd\_table h

select h.\*,lengthb('GBK一个汉字占用两个字节') ,rowid from hzd\_table h

四.建表 建立CONSTRAINT约束，建立序列SEQUENCE 插入数据自增

DROP TABLE HZD\_STUDENT;

CREATE TABLE HZD\_STUDENT (

SID NUMBER(10)   CONSTRAINT CON\_SID\_PK PRIMARY KEY ,

SNAME VARCHAR(255),

SDESCRIPTION VARCHAR(255)

)

\--ALTER 重新、定义、改变命名表字段等

ALTER table hzd\_table modify sid not null

CREATE SEQUENCE HZD\_STUDENT\_SID\_SEQ

INCREMENT BY 1

START WITH 1;

insert into HZD\_STUDENT(SID,SNAME,SDESCRIPTION)values(HZD\_STUDENT\_SID\_SEQ.Nextval,'张三','测试序列和主键约束');

SELECT ROWID, HZD\_TABLE.\* FROM HZD\_TABLE

SELECT \* FROM HZD\_STUDENT

--根据 B表信息修改A表信息 这是错误的写法会把所有的数据都修改

UPDATE  HZD\_TABLE  A

SET  NAME ='测试修改数据'

WHERE    EXISTS

(SELECT \*

FROM  HZD\_STUDENT  B

WHERE  A.SID = B.SID)

SELECT \* FROM HZD\_TABLE A WHERE EXISTS (SELECT \* FROM HZD\_STUDENT B WHERE B.SID=A.SID AND AGE=19)

五.序列例子

\-----------------------------------------------序列sequence实现自增长，在插入的时候用-----------------------------------------------

/\*步骤:

1.建表某表Student

2.建立序列，名字最后有格式

3.插入数据 用Student\_stuId\_Seq.Nextval插入 如： insert into Student(stuId,Stuname) values(Student\_stuId\_Seq.Nextval,'张三');

\*/

完整子如下例:

\--例创建示例表 --

create table Student(

stuId number(9) not null,

stuName varchar2(20) not null,

stuMsg varchar2(50) null

);

-- 创建序列  Student\_stuId\_Seq --

create sequence Student\_stuId\_Seq

increment by 1

start with 1

minvalue 1

maxvalue 999999999;

-- 更改序列  Student\_stuId\_Seq--

/\* alter sequence Student\_stuId\_Seq

increment by 2

minvalue 1

maxvalue 999999999;\*/

--获取序列自增ID --

select Student\_stuId\_Seq.Nextval 自增序列ID from dual;

-- 删除序列 --

/\* drop sequence Student\_stuId\_Seq;\*/

--调用序列，插入Student数据 --

insert into Student(stuId,Stuname) values(Student\_stuId\_Seq.Nextval,'张三');

insert into Student(stuId,Stuname) values(Student\_stuId\_Seq.Nextval,'李四');

--查询插入的数据 --

select student.\* from Student

六.约束CONSTRAINT

\-----------------------------------------------约束constraint-----------------------------------------------

https://blog.csdn.net/qqww120102/article/details/79791396

约束按照创建方式分为：表级约束和列级约束

表级约束：指创建表的时候，定义完全部列后。在最后指定约束对应的列

列级约束：指创建表的时候，定义为列立即定义该列对应的约束

数据库约束有五种：

1.主键约束(PRIMARY KEY)   :相当于UNIQUE+NOT NULL   语法:alter table table\_name add constraint \[constraint\_type\_name \] \[constraint\_type\](column)

2.唯一性约束(UNIQUE)       :alter table table\_name add constraint constraint\_name unique(column\_name);

3.非空约束(NOT NULL)       :alter table table\_name modify column\_name constraint constraint\_name not null ;

4.外键约束(FOREIGN KEY)    :

5.检查约束(CHECK)          :

\-- https://blog.csdn.net/qqww120102/article/details/79791396

/\*主键约束(primary key)

外键约束(foreign key)

唯一性约束(unique)

非空约束(not null)

检查约束(check)\*/

1.主键约束==UNIQUE+NOT NULL

\--增加主键约束

alter table table\_name add constraint constraint\_name primary key(column\_name);

\--删除主键约束

SQL> alter table table\_name drop constraint constraint\_name;

或

alter table table\_name drop primary key;

2.唯一性约束(UNIQUE)

\--增加唯一约束

alter table table\_name add constraint constraint\_name unique(column\_name);

\--删除唯一约束

alter table table\_name drop constraint constraint\_name;

3.非空约束(NOT NULL)

\--增加非空约束

alter table table\_name modify column\_name constraint constraint\_name not null ;

\--删除非空约束

alter table table\_name modify column\_name constraint constraint\_name null;

4.外键约束(FOREIGN KEY)

第一种方式简单粗暴，删除的时候，级联删除掉子表中的所有匹配行，在创建外键时，通过 on delete cascade 子句指定该外键列可级联删除：

SQL> alter table 外键表名 add constraint constraint\_name foreign key(外键表列) references 参照表 (参照表列) on delete cascade;

第二种方式，删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL，通过 on delete set null 子句实施：

SQL> alter table 外键表名 add constraint constraint\_name foreign key(外键表列) references 参照表 (参照表列) on delete set null;

删除外键约束

SQL> alter table table\_name drop constraint constraint\_name;

————————————————

原文链接：https://blog.csdn.net/qqww120102/article/details/79791396

5.检查约束(CHECK)

添加约束

SQL> alter table table\_name add constraint constraint\_name check(具体约束内容);

删除约束

SQL> alter table table\_name drop constraint constraint\_name;

添加、修改默认值

alter table table\_name modify column\_name default 具体内容;

删除默认值

alter table table\_name modify column\_name default null;

\--主键约束 alter table table\_name add constraint \[constraint\_type\_name \] \[constraint\_type\](column)

alter table books add constraint books\_pk primary key (id);

\--唯一约束

alter table table\_name add \[constraint constraint\_name\] unique(column\_name);

删除约束

删除unique约束

删除列上的unique约束，可以使用alter table...drop语句，形式如下：

alter table table\_name drop unique(column\_name)

如果约束有名称，也可以使用指定名称的方式删除该约束，语句形式如下：

alter table table drop constraint constraint\_name;

SELECT HT.\* ,ROWID FROM HZD\_TABLE HT

alter table HZD\_TABLE add constraint con primary key (id);

七时间转换

\-----------------------------------------oracle timestamp和日期 timestamp和字符串互转 ---------------------

Oracle 日期类型timestamp(时间戳)和date类型使用

1、获取系统时间的语句(ssxff6获取小数点后面六位)

select sysdate,systimestamp,to\_char(systimestamp, 'yyyymmdd hh24:mi:ssxff6'),

to\_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff6') from dual;

2、字符型转成timestamp

select to\_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') from dual;

3、timestamp转成date型

select cast(to\_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp\_to\_date from dual;

4、date型转成timestamp

select cast(sysdate as timestamp) date\_to\_timestamp from dual;

5、两date的日期相减得出的是天数，而两timestamp的日期相减得出的是完整的年月日时分秒小数秒

select sysdate-sysdate,systimestamp-systimestamp from dual;

select extract(day from inter) \* 24 \* 60 \* 60 +

extract(hour from inter) \* 60 \* 60 + extract(minute from inter) \* 60 +

extract(second from inter) "seconds" from

(

select to\_timestamp('2011-09-14 12:34:23.281000000', 'yyyy-mm-dd hh24:mi:ss.ff') -

to\_timestamp('2011-09-14 12:34:22.984000000', 'yyyy-mm-dd hh24:mi:ss.ff') inter from dual

);

select extract(second from to\_timestamp('2011-09-14 12:34:23.281000000', 'yyyy-mm-dd hh24:mi:ss.ff'))-

extract(second from to\_timestamp('2011-09-14 12:34:22.984000000', 'yyyy-mm-dd hh24:mi:ss.ff')) from dual;

注：所以，timestamp要算出两日期间隔了多少秒，要用函数转换一下。

to\_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。

select systimestamp-systimestamp from dual

select sysdate,systimestamp,to\_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6'),

to\_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff6') from dual;

\-----------------------------extract (year from sysdate)用于截取时分秒 获取日期间隔---------------------------------------------------------

select extract(second from to\_timestamp('2011-09-14 12:34:23.281000000', 'yyyy-mm-dd hh24:mi:ss.ff'))，

extract(second from to\_timestamp('2011-09-14 12:34:22.984000000', 'yyyy-mm-dd hh24:mi:ss.ff')) from dual;

只可以从一个date类型中截取年月日

select  extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from  dual;

从timestamp中获取年月日时分秒

select

extract(year from systimestamp) year

,extract(month from systimestamp) month

,extract(day from systimestamp) day

,extract(minute from systimestamp) minute

,extract(second from systimestamp) second

,extract(timezone\_hour from systimestamp) th

,extract(timezone\_minute from systimestamp) tm

,extract(timezone\_region from systimestamp) tr

,extract(timezone\_abbr from systimestamp) ta

from dual

获取两个日期之间的具体时间间隔

获取两个日期之间的具体时间间隔，extract函数是最好的选择

select

extract (day from dt2 - dt1) day,

extract (hour from dt2 - dt1) hour,

extract (minute from dt2 - dt1) minute,

extract (second from dt2 - dt1) second

from

(

select

to\_timestamp ('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,

to\_timestamp ('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2

from

dual

)

例题：

Create Table  HAND\_STUDENT

(

STUDENT\_NO Varchar2(10) Not Null,

STUDENT\_NAME Varchar2(20),

STUDENT\_AGE Number,

STUDENT\_GENDER Varchar2(5),

OBJECT\_VERSION\_NUMBER Number Default 1 Not Null,

CREATION\_DATE DATE Default Sysdate Not Null,

CREATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATE\_DATE DATE Default Sysdate Not Null,

LAST\_UPDATE\_LOGIN NUMBER

);

\-- Add comments to the table

comment on table  HAND\_STUDENT is '学生信息表';

\-- Add comments to the columns

comment on column  HAND\_STUDENT.STUDENT\_NO is '学号';

comment on column  HAND\_STUDENT.STUDENT\_NAME is '姓名';

comment on column  HAND\_STUDENT.STUDENT\_AGE is '年龄';

comment on column  HAND\_STUDENT.STUDENT\_GENDER is '性别';

comment on column  HAND\_STUDENT.OBJECT\_VERSION\_NUMBER is '行版本号，用来处理锁';

\-- Create/Recreate indexes

Create Unique Index  HAND\_STUDENT\_U1 On  HAND\_STUDENT(STUDENT\_NO);

Create Table  HAND\_TEACHER

(

TEACHER\_NO Varchar2(10) Not Null,

TEACHER\_NAME Varchar2(20),

MANAGER\_NO Varchar2(10),

OBJECT\_VERSION\_NUMBER Number Default 1 Not Null,

CREATION\_DATE DATE Default Sysdate Not Null,

CREATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATE\_DATE DATE Default Sysdate Not Null,

LAST\_UPDATE\_LOGIN NUMBER

);

\-- Add comments to the table

comment on table  HAND\_TEACHER is '教师信息表';

\-- Add comments to the columns

comment on column  HAND\_TEACHER.TEACHER\_NO is '教师编号';

comment on column  HAND\_TEACHER.TEACHER\_NAME is '教师名称';

comment on column  HAND\_TEACHER.MANAGER\_NO is '上级编号';

comment on column  HAND\_TEACHER.OBJECT\_VERSION\_NUMBER is '行版本号，用来处理锁';

\-- Create/Recreate indexes

Create Unique Index  HAND\_TEACHER\_U1 On  HAND\_TEACHER(TEACHER\_NO) ;

Create Table  HAND\_COURSE

(

COURSE\_NO Varchar2(10) Not Null,

COURSE\_NAME Varchar2(20),

TEACHER\_NO Varchar2(10) Not Null,

OBJECT\_VERSION\_NUMBER Number Default 1 Not Null,

CREATION\_DATE DATE Default Sysdate Not Null,

CREATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATE\_DATE DATE Default Sysdate Not Null,

LAST\_UPDATE\_LOGIN NUMBER

);

\-- Add comments to the table

comment on table  HAND\_COURSE is '课程信息表';

\-- Add comments to the columns

comment on column  HAND\_COURSE.COURSE\_NO is '课程号';

comment on column  HAND\_COURSE.COURSE\_NAME is '课程名称';

comment on column  HAND\_COURSE.TEACHER\_NO is '教师编号';

comment on column  HAND\_COURSE.OBJECT\_VERSION\_NUMBER is '行版本号，用来处理锁';

\-- Create/Recreate indexes

Create Unique Index  HAND\_COURSE\_U1 On  HAND\_COURSE(COURSE\_NO);

Create Table  HAND\_STUDENT\_CORE

(

STUDENT\_NO Varchar2(10) Not Null,

COURSE\_NO Varchar2(10) Not Null,

CORE Number,

OBJECT\_VERSION\_NUMBER Number Default 1 Not Null,

CREATION\_DATE DATE Default Sysdate Not Null,

CREATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATED\_BY NUMBER Default -1 Not Null,

LAST\_UPDATE\_DATE DATE Default Sysdate Not Null,

LAST\_UPDATE\_LOGIN NUMBER

);

\-- Add comments to the table

comment on table  HAND\_STUDENT\_CORE is '学生成绩表';

\-- Add comments to the columns

comment on column  HAND\_STUDENT\_CORE.STUDENT\_NO is '学号';

comment on column  HAND\_STUDENT\_CORE.COURSE\_NO is '课程号';

comment on column  HAND\_STUDENT\_CORE.CORE is '分数';

comment on column  HAND\_STUDENT\_CORE.OBJECT\_VERSION\_NUMBER is '行版本号，用来处理锁';

\-- Create/Recreate indexes

Create Unique Index  HAND\_STUDENT\_CORE\_U1 On  HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO);

/\*\*\*\*\*\*\*初始化学生表的数据\*\*\*\*\*\*/

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s001','张三',23,'男');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s002','李四',23,'男');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s003','吴鹏',25,'男');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s004','琴沁',20,'女');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s005','王丽',20,'女');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s006','李波',21,'男');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s007','刘玉',21,'男');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s008','萧蓉',21,'女');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s009','陈萧晓',23,'女');

insert into HAND\_STUDENT(STUDENT\_NO,STUDENT\_NAME,STUDENT\_AGE,STUDENT\_GENDER) values ('s010','陈美',22,'女');

commit;

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*初始化教师表\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

insert into HAND\_TEACHER(TEACHER\_NO,TEACHER\_NAME,MANAGER\_NO) values ('t001', '刘阳','');

insert into HAND\_TEACHER(TEACHER\_NO,TEACHER\_NAME,MANAGER\_NO)  values ('t002', '谌燕','t001');

insert into HAND\_TEACHER(TEACHER\_NO,TEACHER\_NAME,MANAGER\_NO)  values ('t003', '胡明星','t002');

commit;

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*初始化课程表\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c001','J2SE','t002');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c002','Java Web','t002');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c003','SSH','t001');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c004','Oracle','t001');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c005','SQL SERVER 2005','t003');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c006','C\#','t003');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c007','JavaScript','t002');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c008','DIV+CSS','t001');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c009','PHP','t003');

insert into HAND\_COURSE(COURSE\_NO,COURSE\_NAME,TEACHER\_NO) values ('c010','EJB3.0','t002');

commit;

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*初始化成绩表\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s001','c001',58.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s002','c001',80.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s003','c001',81.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s004','c001',60.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s001','c002',82.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s002','c002',72.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s003','c002',81.9);

insert into HAND\_STUDENT\_CORE(STUDENT\_NO,COURSE\_NO,CORE) values ('s001','c003','59');

commit;

/\*\*

alter table HAND\_STUDENT\_CORE add constraint FK\_STUDENT\_NO foreign key(STUDENT\_NO) REFERENCES HAND\_STUDENT(STUDENT\_NO);

alter table HAND\_STUDENT\_CORE add constraint FK\_COURSE\_NO foreign key(COURSE\_NO) REFERENCES HAND\_COURSE(COURSE\_NO);

alter table HAND\_COURSE add constraint FK\_TEACHER\_NO foreign key(TEACHER\_NO) REFERENCES HAND\_TEACHER(TEACHER\_NO);\*\*/

改写：

1.  没有使用子查询的理由；

查询老师：谌燕 ，所教授的课程；

SELECT hc.COURSE\_NO, hc.COURSE\_NAME

FROM HAND\_COURSE hc

WHERE TEACHER\_NO =

(SELECT TEACHER\_NO FROM HAND\_TEACHER WHERE TEACHER\_NAME = '谌燕');

2.  自定义更新语句，没有判断版本号，没有更新版本号+who字段

更新学生s001对应的课程 c002 成绩为 82.8

UPDATE HAND\_STUDENT\_CORE

SET CORE = 82.8

WHERE STUDENT\_NO = 'S001'

AND COURSE\_NO = 'c002';

\--PDATE HAND\_STUDENT\_CORE

SET CORE = 82.8,

OBJECT\_VERSION\_NUMBER =OBJECT\_VERSION\_NUMBER+1,

LAST\_UPDATED\_BY = &userid,

LAST\_UPDATE\_DATE = sysdate,

WHERE STUDENT\_NO = 'S001'

AND COURSE\_NO = 'c002'

and  OBJECT\_VERSION\_NUMBER=1

3.  子查询滥用，重复的子查询请简化；

查询学生s001 对应的成绩+教师编号+课程名称+课程编号

SELECT hsc.COURSE\_NO,

(select COURSE\_NAME

from HAND\_COURSE i

where i.COURSE\_NO = hsc.COURSE\_NO) COURSE\_NAME,

(select TEACHER\_NO

from HAND\_COURSE j

where j.COURSE\_NO = hsc.COURSE\_NO) TEACHER\_NO,

hsc.CORE

FROM HAND\_STUDENT\_CORE hsc

where hsc.STUDENT\_NO = 's001';

SELECT hsc.COURSE\_NO,

(select COURSE\_NAME

from HAND\_COURSE i

where i.COURSE\_NO = hsc.COURSE\_NO) COURSE\_NAME,

(select TEACHER\_NO

from HAND\_COURSE j

where j.COURSE\_NO = hsc.COURSE\_NO) TEACHER\_NO,

hsc.CORE

FROM HAND\_STUDENT\_CORE hsc

where hsc.STUDENT\_NO = 's001';

4.  条件语句中直接使用 case when，性能差，建议子查询封装

core 分数等级在 70分以下，差； 70-80分，良； 80分以上，优；

查询出所有成绩等级为"差"的学生及对应课程分数；

SELECT hs.STUDENT\_NAME,

hsc.COURSE\_NO,

(CASE

WHEN hsc.CORE > 80 THEN

'优'

WHEN hsc.CORE > 70 THEN

'良'

ELSE

'差'

END) type,

hsc.CORE

FROM HAND\_STUDENT\_CORE hsc, hand\_student hs

where hsc.STUDENT\_NO = hs.STUDENT\_NO

and (CASE

WHEN hsc.CORE > 80 THEN

'优'

WHEN hsc.CORE > 70 THEN

'良'

ELSE

'差'

END) = '差' ;

5.  列名的函数运算，将不走索引

查询出所有加10分后，分数还低于80分的学生成绩；

SELECT hs.STUDENT\_NAME, hsc.STUDENT\_NO, hsc.COURSE\_NO, hsc.CORE

FROM HAND\_STUDENT\_CORE hsc, hand\_student hs

WHERE hsc.STUDENT\_NO = hs.STUDENT\_NO

and hsc.core + 10 < 80

order by hs.STUDENT\_NAME;

6.  重复的SQL建议使用子查询整个进行封装 or 合适的方式进行改写；

查询老师：刘阳，所教授的课程；

SELECT hc.COURSE\_NO,

hc.COURSE\_NAME,

hc.TEACHER\_NO,

(SELECT TEACHER\_NAME

FROM HAND\_TEACHER ht

where hc.TEACHER\_NO = ht.TEACHER\_NO) TEACHER\_NAME

FROM HAND\_COURSE hc

WHERE 1 = 1

and (SELECT TEACHER\_NAME

FROM HAND\_TEACHER ht

where hc.TEACHER\_NO = ht.TEACHER\_NO) = '刘阳';

7.Group by 与 distinct 混用

SELECT DISTINCT STUDENT\_NO, COURSE\_NO

FROM HAND\_STUDENT\_CORE

GROUP BY STUDENT\_NO, COURSE\_NO;

8.  内层的Group by 没有任何的限制条件，只能走全表扫描，建议改成子查询

计算学生的成绩总分；

大数据量，比如50万左右的数据可以明显看出执行计划上的差别，小数据量比如10几行可能错误的写法还更快，全表扫描比走索引快；

select hs.STUDENT\_NO, hs.STUDENT\_NAME, hc.cores

from hand\_student hs

left join (SELECT STUDENT\_NO, sum(CORE) cores

FROM HAND\_STUDENT\_CORE

GROUP BY STUDENT\_NO) hc

on hs.STUDENT\_NO = hc.STUDENT\_NO;

9.  内层子查询的Group by 没有限制条件走全表扫描，外层where条件可作为限制，建议移到内层，走索引查询；内层多余的查询字段请去除，只使用有用的字段

select COURSE\_NO, sum(core)

from (select STUDENT\_NO, COURSE\_NO, core

from HAND\_STUDENT\_CORE

union ALL

select STUDENT\_NO, COURSE\_NO, core

from HAND\_STUDENT\_CORE) a

where 1 = 1

and a.student\_no = 's001'

group by COURSE\_NO;

10. 存在全表扫描，可以如何提速；

SELECT hsc.COURSE\_NO, hc.COURSE\_NAME, hc.TEACHER\_NO, hsc.CORE

FROM HAND\_STUDENT\_CORE hsc, HAND\_COURSE hc

with (index(hsc.COURSE\_NO))

where hsc.COURSE\_NO = 'c001'

and hc.COURSE\_NO = hsc.COURSE\_NO;

\---------------------------2019/11/4 黄泽东SQL练习 BY PLSQL-----------------------------------------

取合适的别名，字段不要取关键字

表少的时候最好是A B C D E

1.关键字：ROW\_NUMBER

题目：请按渠道的首付酬金进行排序，找出排名为第3的渠道编码

CREATE TABLE DD\_TABLE1

(

GROUP\_ID         VARCHAR (10), --渠道编码

PARAM\_NAME       VARCHAR (10), --酬金类别

VALUE             INTEGER,     --酬金金额

OP\_TIME          VARCHAR(100), --操作时间

OP\_NOTE          VARCHAR(100) --操作说明

);

Insert into DD\_TABLE1