ORACLE_笔记_练习题目

一.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