史上最全关系型数据库（Oracle）语法介绍，传统数仓，银行项目，涵盖极偏知识点

本文链接：https://blog.csdn.net/HelloWowofei/article/details/132617437

数据库

存储数据的仓库

整个仓库就是一个数据库；

仓库中有很f多货柜，相当于数据库的表格；

货柜有很多的格子，对应了表格的行和列；

放在格子里面的货物，就是表格中的数据；

货物有不同的类型和属性，表示数据有不同的数据类型；

货物一定是按照某个顺序存放的，表示数据有排序的方式；

货物我们希望能够用一个目录来展示它们的位置，这个目录就叫索引...

oracle mysql sqlserver db2 postgre mongdb redis...

关系型数据库：RDBMS (relational data base manager system)：

以表格的形式存储和操作数据

oracle 11g：数据库本身的安装文件

PLSQL：连接数据库的工具

username：使用的用户名和账号

sys：超级管理员，可以管理所有的用户和操作，只能用管理员身份登录

system：普通管理员，可以用管理员也可以用普通用户的身份登录，工作中都是用system管理数据库

password：这个账号的密码

database：数据库实例，所有的实例都是以操作系统服务的形式存在的

OracleService 表示它是一个服务 ORCL是服务的名字

connect as：让你的用户，以什么身份来登录数据库

normal 普通用户

sysdba 系统管理员

sysoper 系统检测员：对系统进行关闭、启动、备份、还原等操作

操作数据库的语言，SQL语句：

数据控制语言 DCL

grant 赋予权限

revoke 收回权限

数据定义语言 DDL

create 创建

alter 修改

drop 删除

truncate 清空

数据查询语言 DQL

select

数据操作语言 DML

insert

update

delete

创建一个用户：

create user 用户名 identified by 密码;

create user zz identified by 123456;

给用户赋予基本的操作权限：

grant 权限的类型和名字 to 用户名;

grant connect,resource,dba to zz;

收回权限：

revoke 权限的类型和名字 from 用户名;

revoke resource,dba from zz;

权限的类型和名字:

connect 连接的权限

resource 写代码的权限

dba 管理的权限

调整字体：

数据库中表格的管理：

1. 创建一个新的表格

create table 表名(

列名1 数据类型约束条件,

列名2 数据类型约束条件,

...,

列名n 数据类型

);

create table user_info(

userid number,

username varchar2(12),

usersex char(3),

high number(4,2),

regist_time date

);

数据类型有哪些？

数字的类型

整数 integer -2147483648~2147483647

整数 number

小数 number(总长度, 小数精度) 例如1.75 number(4,2)

字符串的类型

定长字符串 char(最大长度) 最多可以存放2000个字符

一般用在手机号码性别身份证号码状态...

不定长字符串 varchar2(最大长度) 最多可以存放4000个字符

姓名地址爱好邮箱...

oracle里面的中文的编码格式：

GBK（国标码）一个中文占2个长度

UTF8（万国码）一个中文占3个长度

日期的类型

date 包含了年月日时分秒的信息

大文本的类型 4GB

BLOB 存放图片、音乐、视频等文件

CLOB 文字特别多的文本

对于表格数据的输入的不同限制：约束条件

不同的约束条件：

1. 主键：表格中最核心的列 primary key 一个表只能有一个主键

不能重复，也不能为空

2. 非空：not null

填写的内容不能为空

3. 唯一： unique

数据不能重复，例如身份证或者手机号码

4. 检查： check

限制数据在某个范围之内

5. 外键： foreign key 外键只能和另一个表的主键关联，外键可以有多个

我的表的某个内容，是来自于另外一个表格，不能自己随便写

foreign key (自己表的列名) references 另一个表的名字(它的主键名字)

限制购物车的表格中，userid必须在用户表中已经存在，goods必须在商品表中已经存在，不能填写那些没有存在数据

用户表

create table user_info3(

userid number primary key,

username varchar2(200) not null,

mobile char(11) unique,

regist_time date

);

商品表

create table goods_info(

goods varchar2(200) primary key,

g_category varchar2(200),

unit_price number(10,2)

);

购物车表

create table shopping(

shopping_id number primary key,

userid number,

shopping_time date,

goods varchar2(200),

price number(10,2),

foreign key (userid) references user_info3(userid),

foreign key(goods) references goods_info(goods)

);

create table user_info2(

userid number primary key,

username varchar2(12) not null,

mobile char(11) unique,

sex char(3) check(sex='男' or sex='女'),

high number(4,2) check(high>=1.00 and high<=2.00),

regist_time date

);

2. 修改已经存在的表格结构

alter table 表名对应的操作;

2.1 修改列的结构

新增列

alter table 表名 add 列名数据类型约束条件;

alter table user_info add mobile char(11) unique;

删除列

alter table 表名 drop column 列名;

alter table user_info drop column high;

修改列

alter table 表名 modify 列名新的数据类型约束条件;

alter table user_info modify usersex number;

重新命名列

alter table 表名 rename column 旧的列名 to 新的列名;

alter table user_info rename column mobile to phone;

2.2 修改约束条件，在建表的时候添加的约束条件，它的名字是系统自动分配的，但是在alter里面自己手动去操作约束条件，它的名字是要自己给的。

新增一个约束条件

alter table 表名 add constraint 约束条件的名字约束名(列名);

主键

alter table user_info add constraint pri_userid primary key(userid);

唯一

alter table user_info add constraint uni_username unique(username);

非空

alter table user_info add constraint ck_regist check(regist_time is not null);

检查

alter table user_info add constraint ck_sex check(usersex='男' or usersex='女');

外键

alter table user_jifen add constraint fk_user_info_userid foreign key(userid) references user_info(userid);

删除一个约束条件

alter table 表名 drop constraint 约束条件的名字;

alter table user_info drop constraint ck_regist;

删除一个表格：

drop table 表名;

修改表的名字：

alter table 旧表名 rename to 新表名;

alter table shopping rename to shopping_info;

3. DML操作：对表格数据本身的操作：commit（保存提交） rollback（不保存回滚）

新增数据：

往所有的列中添加对应的数据

insert into 表名 values(值1,值2,值3...);

insert into user_info values(1001,'李雷',1,date'2020-11-19','13400009999');

往指定的列中添加对应的数据

insert into 表名(列名1,列名2...) values(值1,值2...);

修改已有数据：where 列名=值根据某个列的值，找到拥有这个值的行，一般都会通过主键查找数据

update 表名 set 列名=新的值 where 列名=原有的值;

update user_info set usersex=0 where username='韩梅梅';

update user_info set username='韩小梅' where userid=1002;

删除数据

delete from 表名 where 列名=原有的值;

delete from user_info where userid=1001;

删除整个表格的数据：

1. delete from 表名;

delete from user_info;

2. truncate table 表名;

truncate table user_info;

delete和truncate的区别是什么？

1. delete是DML操作，truncate是DDL操作

2. delete是以行为单位删除数据，truncate是基于整个表格删除的

3. delete可以加where筛选需要删除的数据，truncate不能使用where筛选

4. delete是有缓存的，truncate删除是没有缓存的

4. DQL操作：对已有的表格进行数据的查询和筛选 select

基本查询语句： * 表示所有的列名

select * from 表名;

select * from scott.emp;

选择部分列进行查看：

select 列名1,列名2... from 表名;

select empno,ename,sal from scott.emp;

选择部分行进行查看：

select 列名1,列名2... from 表名 where 列名=值;

select empno,ename,sal from scott.emp where sal>=3000;

a. 精确查询

where 列名=值

select * from scott.emp where deptno=10;

b. 范围筛选 > < >= <= !=

where 列名>=值

select * from scott.emp where sal<1000;

c. 模糊筛选

where 列名 like 值

select * from scott.emp where ename like 'A%';

A开头，第四个字母是E的名字

select * from scott.emp where ename like 'A__E%';

select * from scott.emp where ename like '__A%';

% 通配符匹配其他的任意长度的任意内容

_ 通配符匹配一个任务的内容

查询名字里面包含了%的姓名

select * from scott.emp where ename like '%\%%' escape '\';

\ 是转义符的意思，转义符可以将特殊的符号，变成普通的字符；

但是要通过escape关键字告诉sql语句，这个时候\是当成转义符来使用的。

d. 逻辑筛选 and（两个条件要同时满足） or（两个条件满足一个就行） not

先运行and ，再运行 or

查询10号部门和20号部门的员工信息

select * from scott.emp where deptno=10 or deptno=20;

查询10号部门中工资超过2000元的员工信息

select * from scott.emp where deptno=10 and sal>2000;

查询10号部门工资超过2000的和20号部门工资低于1000的员工信息

select * from scott.emp where deptno=10 and sal>2000

or deptno=20 and sal<1000;

查询10号和20号两个部门中，工资都高于2000的员工信息

select * from scott.emp where (deptno=10 or deptno=20)

and sal>2000;

查询不是20号部门的员工

select * from scott.emp where deptno!=20;

select * from scott.emp where not deptno=20;

查询20号部门工资小于1000的员工

select * from scott.emp where deptno=20 and not sal>=1000;

e. 空值的查询

where 列名 is null

查询所有没有设置奖金的员工

select * from scott.emp where comm is null;

查询所有设置了奖金的员工

select * from scott.emp where comm is not null;

f. 和范围相关的查询关键字 in between...and...

查询7369 7788 7654这三个员工的信息

select * from scott.emp where empno=7369 or empno=7788

or empno=7654;

select * from scott.emp where empno in(7369,7788,7654);

查询工资在1000到3000范围内的所有员工信息

select * from scott.emp where sal>=1000 and sal<=3000;

select * from scott.emp where sal between 1000 and 3000;

练习：scott.emp

1.新增一个用户：

8888,lilei,SALEMAN,7566,1988-10-9,3200,1200,10

2.更新7654这个用户的奖金为1600

3.查询没有上级领导编号（MGR）的员工信息

4.查询所有的SALESMAN和MANAGER岗位的员工信息

5.查询CLERK里面，工资低于1000的员工信息

6.查询10号部门的MANAGER经理的名字

7.查询奖金比工资高的员工信息

8.查询名字里面倒数第三个字母是O的员工信息

答案：

insert into scott.emp values(

8888,'lilei','SALESMAN',7566,date'1988-10-9',3200,1200,10

);

update scott.emp set comm=1600 where empno=7654;

select * from scott.emp where mgr is null;

select * from scott.emp where job in ('SALESMAN','MANAGER');

select * from scott.emp where job='CLERK' and sal<1000;

select ename from scott.emp where deptno=10 and job='MANAGER';

select * from scott.emp where comm>sal;

select * from scott.emp where ename like '%O__';

sql语句里面的集合运算：

union all：将第一个语句的结果和第二个语句的结果，拼接在一起

select job from scott.emp where deptno=10

union all

select job from scott.emp where deptno=20;，

结果相同

select job from scott.emp where deptno=10 or deptno=20;

union：将第一个语句的结果和第二个语句的结果，拼接在一起，并且去除掉重复的数据

select job from scott.emp where deptno=10

union

select job from scott.emp where deptno=20;

结果相同

select distinct job from scott.emp where deptno=10 or deptno=20;

minus：显示第一个语句有的内容，但是第二个句子没有的内容

select job from scott.emp where deptno=20

minus

select job from scott.emp where deptno=10;

intersect：显示两个句子共有的数据部分

select job from scott.emp where deptno=20

intersect

select job from scott.emp where deptno=10;

union all和union有什么区别？

union all是拼接所有的数据，union也是拼接所有的数据，但是会对数据做去重的处理；

union all的效率更高，通常会使用union all替代or的语句。

数据库的概念

如何连接数据库

sql（结构化的查询语句）：

dcl（权限） ddl（数据库对象） dml（表的数据） dql（查询表格的数据）

create user 用户名 identifed by 密码;

grant connect,resource,dba to 用户名;

create table 表名(

列名数据类型约束条件

);

数据类型:

number

number(长度, 小数精度)

char(长度) 2000

varchar2(长度) 4000

date

blob 4GB

clob 4GB

约束条件:

primary key 只有一个，包含了非空和唯一两个条件

not null 非空

unique 唯一

check 检查，限制格子输入的数据范围

foreign key 外键，格子的内容一定来自另一个表

insert into 表 values(值);

insert into 表(列名) values(值);

update 表 set 列=新值 where 列=旧值;

delete from 表 where 列=旧值;

delete from 表;

truncate table 表;

select * from 表;

select 列名 from 表;

select 列名 from 表 where 列=值;

select 列名 from 表名 where 条件筛选；

数据的统计和计算：

group by 和聚合函数的计算

对谁分组，就只能查看谁的信息

聚合函数：

count() 统计行数

sum() 求和

avg() 求平均值

max() 最大值

min() 最小值

select deptno,count(empno),avg(sal),sum(sal),max(sal),min(sal) from scott.emp group by deptno;

查询人数大于5人的部门是哪一个？

select deptno,count(empno) from scott.emp group by deptno having count(empno)>5;

having关键字：

对分组的结果进行筛选，用在group by之后。

查询20号部门的员工数量：

--先筛选再分组

select deptno,count(empno) from scott.emp where deptno=20 group by deptno;

--先分组再筛选

select deptno,count(empno) from scott.emp group by deptno having deptno=20;

where 和 having 他们的区别是什么？

1. 先筛选再分组，用使用where筛选，速度上更快

2. where在group by 之前使用的，having是在group by之后使用的

3. where不能筛选聚合函数的结果，having可以

--查询每个工作岗位，他们的最大的工资和最小的工资，分别是多少

select job,max(sal),min(sal) from scott.emp group by job;

--查询每个部门，平均工资大于2000的部门是哪一个

select deptno,avg(sal) from scott.emp group by deptno

having avg(sal)>2000;

同时对多个列进行分组：

查询每个部门里面，每个工作岗位，平均工资是多少？

select deptno,job,avg(sal) from scott.emp group by deptno,job;

数据的排序：升序降序

order by 列名 asc|desc

order by 列名默认是升序的排序

查询所有人的信息，并且按照工资进行升序排序

select * from scott.emp order by sal;

查询每个部门的平均工资，并且按照平均工资升序排序

select deptno,avg(sal) from scott.emp group by deptno

order by avg(sal) asc;

查询所有员工的信息，按照工资降序排序，如果工资相同，再按照编号升序排序

select * from scott.emp order by sal desc,empno asc;

现在工资是第一顺位，编号是第二顺位，当工资相同的时候，编号的排序才会生效。

sql语句的嵌套查询（子查询）

将一个句子查询的结果，当成另一个句子查询的条件

查询和SMITH工作岗位相同的其他员工的信息

1.查询SMITH工作岗位

2.查询和他相同的员工

select * from scott.emp where job=(

select job from scott.emp where ename='SMITH'

);

查询工资比MILLER工资要高的其他员工信息

select * from scott.emp where sal>(

select sal from scott.emp where ename='MILLER'

);

查询和10号部门员工，工作岗位相同的其他员工

select * from scott.emp where job in(

select job from scott.emp where deptno=10

);

查询工资比20号部门所有人工资都高的其他员工信息

select * from scott.emp where sal>(

select max(sal) from scott.emp where deptno=20

);

查询SMITH在哪个地点上班？

条件和结果分别在两个不同的表格中

1. 条件和结果分别在哪个表中 emp dept

2. 两个表有没有共同点 deptno

3. SMITH的部门编号是多少

4. 根据他的部门编号，去查询上班的地点

select loc from scott.dept where deptno=(

select deptno from scott.emp where ename='SMITH'

);

查询ACCOUNTING部门有哪些员工，显示出员工的名字

select ename from scott.emp where deptno=(

select deptno from scott.dept where dname='ACCOUNTING'

);

查询工作地点在NEW YORK并且工作岗位和SMITH一样的员工信息

select * from scott.emp where deptno=(

select deptno from scott.dept where loc='NEW YORK'

) and job=(

select job from scott.emp where ename='SMITH'

);

表格的联合查询：

拼接多个表格，变成一张大的表格，然后对这个大的表格进行操作。

第一个句型：

select 列名 from a [inner | left | right | full ] join b

on a.xx=b.xx;

第二个句型：

select 列名 from a,b where a.xx=b.xx; --内连接

select 列名 from a,b where a.xx=b.xx(+); --左连接

select 列名 from a,b where a.xx(+)=b.xx; --右连接

内连接 [inner] join：

拼接左右两个表格，显示两个表格连接列共同拥有的数据

select * from scott.emp join scott.dept

on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno=scott.dept.deptno;

左连接 left [outer] join：

首先先显示左右两个表格所有的共同数据，然后再显示左边那边表独有的数据，因为右边没有匹配数据，所以显示为空

select * from scott.dept left join scott.emp

on scott.emp.deptno=scott.dept.deptno;

select * from scott.dept,scott.emp where scott.dept.deptno=scott.emp.deptno(+);

右连接 right [outer] join：刚好和左连接相反，首先先显示左右两个表格所有的共同数据，然后再显示右边那边表独有的数据，因为左边没有匹配数据，所以显示为空

select * from scott.emp right join scott.dept

on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno(+)=scott.dept.deptno;

全连接 full [outer] join：先显示两个表的共同数据，然后再显示左边表的独有数据，右边没有匹配就显示为空，然后再显示右边表的独有数据，左边没有匹配就显示为空。

select * from scott.emp full join scott.dept

on scott.emp.deptno=scott.dept.deptno;

select * from scott.emp,scott.dept where scott.emp.deptno(+)=scott.dept.deptno

union

select * from scott.emp,scott.dept where scott.emp.deptno=scott.dept.deptno(+);

笛卡尔积

表连接的过程：

对a和b的所有数据进行匹配，先找出所有数据的笛卡尔积，然后在所有的集合结果中找出符合的数据

查询出SMITH的上班地点： dept emp

select loc from scott.emp a join scott.dept b

on a.deptno=b.deptno

where ename='SMITH';

查询出在NEW YORK上班的所有人

select ename from scott.emp a join scott.dept b

on a.deptno=b.deptno

where loc='NEW YORK';

查询出每个上班地点的员工数量

select loc,count(empno) from scott.dept a left join scott.emp b on a.deptno=b.deptno

group by loc;

查询出每个部门中，比自己所在的部门平均工资要高的员工的信息

select * from

(select deptno,avg(sal) s from scott.emp group by deptno) a

join scott.emp b

on a.deptno=b.deptno

where sal>s;

找出平均工资小于2000的部门，上班地点在哪里

select loc,avg(sal) from scott.emp a join scott.dept b

on a.deptno=b.deptno

group by loc having avg(sal)<2000;

找出SALESMAN分别在哪些不同的部门上班，查询出部门名称

select distinct dname from scott.emp a join scott.dept b

on a.deptno=b.deptno

where job='SALESMAN';

查询出每个部门的员工数量，要求查询出部门编号、部门名称、部门地点、人数

select a.deptno,dname,loc,c from

(select deptno,count(empno) c from scott.emp group by deptno) a

join scott.dept b

on a.deptno=b.deptno;

select b.deptno,dname,loc,count(empno) from scott.emp a join scott.dept b

on a.deptno=b.deptno

group by b.deptno,dname,loc;

统计行数：

count(列名)：统计的是这一列中不为空的数量

count(1)等同于count(*)：以行为单位，只要整行中有一列有值，那么就统计数量

整个公司，设置了奖金的人有多少个？

select count(empno) from scott.emp where comm is not null;

select count(comm) from scott.emp;

查询每个部门中，设置了奖金的人有多少个？

select deptno,count(empno) from scott.emp where comm is not null group by deptno;

select deptno,count(comm) from scott.emp group by deptno;(select * from sc where c#=001) a

full join

(select * from sc where c#=002) b

on a.s#=b.s#

where a.score>b.score;

select s#,avg(score) from sc group by s# having avg(score)>60;

group by a.s#,sname;

查询出每个员工和他的上级领导的名字

select a.ename yuangong,b.ename lingdao from scott.emp a join scott.emp b

on a.mgr=b.empno;

查询出每个部门中工资最高的员工的信息

select * from

(select deptno,max(sal) m from scott.emp group by deptno) a

join scott.emp b

on a.deptno=b.deptno

where sal=m;

左连接的时候，数据筛选使用and和where的区别？

a表 b表

1 a 1 A

2 b 2 B

3 c 4 D

select * from a left join b on a.id=b.id where a.id=2;

先连接，在连接的基础上进行筛选，数据只剩1行

2 b 2 B

select * from a left join b on a.id=b.id and a.id=2;

先筛选a表，用筛选后的结果和b表进行连接，数据有3行

2 b 2 B

1 a null null

3 c null null

多表连接的句型：

select * from a join b on a.xx=b.xx join c on a.yy=c.yy join d on d.zz=b.zz;

练习：

查询每个员工在salgrade表中的工资等级

select * from scott.emp,scott.salgrade where sal between losal and hisal;

查询工资比自己上级工资高的员工信息

select * from scott.emp a join scott.emp b on a.mgr=b.empno

where a.sal>b.sal;

查询工资相同的员工，显示工资和员工的姓名

select ename,sal from scott.emp where sal in

(select sal from scott.emp group by sal having count(empno)>=2);

查询每个员工的工资，占自己所在部门工资的比例（百分之多少）

select empno,ename,round(sal/s*100,2)||'%' from

(select deptno,sum(sal) s from scott.emp group by deptno) a

join scott.emp b

on a.deptno=b.deptno;

查询每个工作岗位上，最大工资和最小工资的差距小于1000的工作岗位名字，以及最大和最小工资的差额

select job,max(sal)-min(sal) m from scott.emp group by job having max(sal)-min(sal)<1000;

查询没有员工的部门，工作地点在哪里

select loc,count(empno) c from scott.emp a right join scott.dept b

on a.deptno=b.deptno

group by loc having count(empno)=0;

select loc from scott.dept where deptno not in

(select distinct deptno from scott.emp);

什么是函数：

具有固定功能的、功能单一的、可以反复使用的代码块

函数的使用：

函数名字(要被计算的数据)

单行函数：每一行数据单独的进行运算

数字相关

abs(x) 取绝对值

select abs(-99) from dual;

round(x, 小数精度) 四舍五入

select round(1.2345678,4) from dual;

round(x) 四舍五入，默认是只取值到整数

select round(4.3678) from dual;

trunc(x, 小数精度) 截取到小数精度的位置

select trunc(1.2345678,4) from dual;

trunc(x) 截取到整数

select trunc(4.5678) from dual;

☐

字符串相关

substr(x, idx, len) 截取字符串的一部分 substr(字符串, 开始位置, 连续取值的长度)

select substr('abcdefghijklmn',2,4) from dual;

select substr('abcdefghijklmn',-3,2) from dual; 开始位置为负数，表示从后往前数

select substr('abcdefghijklmn',-3) from dual; 连续取值为空，表示一直取到最后

concat(x1, x2) 两个字符串的拼接

select concat('hello','world') from dual;

select concat(concat('hello','-'),'world') from dual;

select concat('''',concat(deptno,'''')) from scott.emp;

字符串的拼接可以使用管道符来实现，两个管道符可以实现字符的拼接

select ename||':'||deptno from scott.emp;

replace(x, old, new) 字符串内容的替换

select ename,replace(ename,'A','.') from scott.emp;

length(x) 进行字符串长度的计算

select length(ename) from scott.emp;

select length('abcdefg') from dual;

日期相关

sysdate 系统关键字，获取当前的时间日期

select sysdate from dual;

last_day(x) 计算日期的最后一天

select last_day(sysdate) from dual;

months_between(x1, x2) 计算两个日期之间的时间间隔

select months_between(date'2020-9-2',date'2019-9-1') from dual;

add_months(x, m) 计算日期的月份偏移

select add_months(sysdate,11) from dual;

日期可以直接加减的，直接加减是天数的偏移

select date'2020-10-1'+10 from dual;

数据类型转换相关

to_number() 只能转换全是数字的字符串，例如 '100'

select to_number('100') from dual;

to_char() 任何东西都可以转成字符串

select to_char(100) n from dual;

日期转换成字符串，是对日期的内容进行提取

select to_char(sysdate,'yyyy') from dual;

select to_char(sysdate,'mm') from dual;

select to_char(sysdate,'dd') from dual;

select to_char(sysdate,'hh') from dual;

select to_char(sysdate,'mi') from dual;

select to_char(sysdate,'ss') from dual;select *from zz.Emp where sal>

(Select max(sal) from emp where deptno =30 )

select to_char(sysdate,'day') from dual; --提取星期几

to_date() 只有时间格式的字符串可以转成日期，例如 '2020-10-1 9:38:12'

select to_date('2020-9-1 10:21:09','yyyy-mm-dd hh24:mi:ss') from dual;

练习：

1. 写出永远是明天早上10点钟的时间

select to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 10:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual;

2. 查询emp表中，所有星期四入职的员工

select * from scott.emp where to_char(hiredate,'day')='星期四';

3. 查询每一年有多少员工入职

select to_char(hiredate,'yyyy'),count(empno) from scott.emp group by to_char(hiredate,'yyyy');

分析函数：

over() 开窗函数

计算函数部分() over(partition by 列名 order by 列名 asc|desc)

查询每个部门中工资最高的员工信息

1. 每个部门的最高工资是多少 max()

2. 将这个查询的分组结果和原来表格进行拼接 over(partition by 列名)

3. 对拼接之后的数据进行对比 where

select * from

(select a.*,

max(sal) over(partition by deptno) m

from scott.emp a)

where sal=m;

over()：对整个表格进行计算

select a.*,

max(sal) over()

from scott.emp a;

over(partition by 列名)：在每个小的分组中，进行数据的计算

select a.*,

max(sal) over(partition by job)

from scott.emp a;

over(order by 列名 asc|desc)：对整个表，对排序的列进行依次的累计运算，并列的名次和数据，会当成一个整体进行计算

select a.*,

sum(sal) over(order by sal)

from scott.emp a;

over(partition by 列名 order by 列名 asc|desc)：在每个分组中，对排序的列进行依次的累计运算，并列的名次和数据，会当成一个整体进行计算

select a.*,

avg(sal) over(partition by deptno order by sal)

from scott.emp a;

max() | min() | count() | sum() | avg() over(partition by 组名 order by 列名 asc|desc)

排名函数+over()：

row_number()：根据某个列，按照顺序进行排序 1 2 3 4

select a.*,

row_number() over(partition by deptno order by sal desc) r

from scott.emp a;

rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，会跳过占用的名次 1 2 2 4

select a.*,

rank() over(order by sal desc) r

from scott.emp a;

dense_rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，不会跳过名次 1 2 2 3

select a.*,

dense_rank() over(order by sal desc) r

from scott.emp a;

找出每个岗位工资第一的员工，如果工资相同，就全部显示出来

select * from

(select a.*,

rank() over(partition by job order by sal desc) r

from scott.emp a)

where r=1;

create table t1(

id number,

name varchar2(10)

);

insert into t1 values(1,'/');

insert into t1 values(2,'A');

insert into t1 values(3,'B');

insert into t1 values(4,'C');

insert into t1 values(5,'/');

insert into t1 values(6,'D');

insert into t1 values(7,'E');

insert into t1 values(8,'/');

insert into t1 values(9,'F');

insert into t1 values(10,'G');

insert into t1 values(11,'H');

commit;

答案：

select id,name,id-r group_id from

(select a.*,

row_number() over(order by id) r

from

(select * from t1 where name!='/') a) b;

平移函数：在工作中一般用来实现数据的同比和环比的计算

同比：今年的4月和去年的4月相比，在两个时间范围内，取相同时间节点的数据进行比较

环比：今年的4月和今年的3月相比，在同一个时间范围内，取相邻的时间节点的数据进行比较

create table sale_info(

y number,

m number,

amount number(7,2)

);

insert into sale_info values(2018,1,2342);

insert into sale_info values(2018,2,1234);

insert into sale_info values(2018,3,3545);

insert into sale_info values(2018,4,3456);

insert into sale_info values(2018,5,2342);

insert into sale_info values(2018,6,4534);

insert into sale_info values(2018,7,3453);

insert into sale_info values(2018,8,2342);

insert into sale_info values(2018,9,4352);

insert into sale_info values(2018,10,1312);

insert into sale_info values(2018,11,3453);

insert into sale_info values(2018,12,1235);

insert into sale_info values(2019,1,3453);

insert into sale_info values(2019,2,1233);

insert into sale_info values(2019,3,3445);

insert into sale_info values(2019,4,1233);

insert into sale_info values(2019,5,1231);

insert into sale_info values(2019,6,4234);

insert into sale_info values(2019,7,1231);

insert into sale_info values(2019,8,2131);

insert into sale_info values(2019,9,1231);

insert into sale_info values(2019,10,3421);

insert into sale_info values(2019,11,1231);

insert into sale_info values(2019,12,1231);

commit;

lag() 将数据往下平移一行

计算2019年每一个月和2018年同月份的数据增长率。

select y,m,round((amount-lo)/lo*100,2)||'%' from

(select a.*,

lag(amount) over(partition by m order by y) lo

from sale_info a) b;

在m这个组，根据y排序，将amount这个列，数据往下平移一行

使用表连接的方法，也可以实现同样的效果

select b.y,b.m,round((b.amount-a.amount)/a.amount*100,2)||'%' 增长率 from

(select * from sale_info where y=2018) a

join

(select * from sale_info where y=2019) b

on a.m=b.m;

查询2019年的每一个月，比上个月增长了百分之多少？

select y,m,round((amount-lo)/lo*100,2)||'%' from

(select a.*,

lag(amount) over(order by m ) lo

from sale_info a where y=2019) b;

select a.y,a.m,round((a.amount-b.amount)/b.amount*100,2)||'%' from

(select * from sale_info where y=2019) a

join

(select * from sale_info where y=2019) b

on a.m=b.m+1;

lead() 将数据往上平移一行

select a.*,

lead(amount) over(order by m ) lo

from sale_info a where y=2019;

CREATE TABLE NBA (TEAM VARCHAR2(20),Y NUMBER(4));

INSERT INTO NBA VALUES('活塞',1990);

INSERT INTO NBA VALUES('公牛',1991);

INSERT INTO NBA VALUES('公牛',1992);

INSERT INTO NBA VALUES('公牛',1993);

INSERT INTO NBA VALUES('火箭',1994);

INSERT INTO NBA VALUES('火箭',1995);

INSERT INTO NBA VALUES('公牛',1996);

INSERT INTO NBA VALUES('公牛',1997);

INSERT INTO NBA VALUES('公牛',1998);

INSERT INTO NBA VALUES('马刺',1999);

INSERT INTO NBA VALUES('湖人',2000);

INSERT INTO NBA VALUES('湖人',2001);

INSERT INTO NBA VALUES('湖人',2002);

INSERT INTO NBA VALUES('马刺',2003);

INSERT INTO NBA VALUES('活塞',2004);

INSERT INTO NBA VALUES('马刺',2005);

INSERT INTO NBA VALUES('热火',2006);

INSERT INTO NBA VALUES('马刺',2007);

INSERT INTO NBA VALUES('凯尔特人',2008);

INSERT INTO NBA VALUES('湖人',2009);

INSERT INTO NBA VALUES('湖人',2010);

COMMIT;

select team,min(y),max(y) from

(select nba.*,

y-(row_number() over(order by team,y)) r

from nba) a

group by team,r having count(1)>1

order by min(y);

可以先完成前面30个题目的练习

select * from student;

select * from teacher;

select * from sc;

select * from course;

1.查询男生、女生人数

select ssex,count(sno) from student group by ssex;

2.查询姓“张”的学生名单

select * from student where sname like '张%';

3.查询姓“刘”的老师的个数

select count(tno) from teacher where tname like '刘%';

4.查询选了课程的学生人数

select count(distinct sno) from sc;

select count(1) from

(select sno from sc group by sno) a;

5.查询有学生不及格的课程，并按课程号从大到小排列

select * from sc where score<60 order by cno desc;

6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno,max(score),min(score) from sc group by cno;

7.查询不同课程平均分从高到低显示，要显示授课老师

select cname,tname,avg(score) s from sc a join course b on a.cno=b.cno

join teacher c on b.tno=c.tno

group by cname,tname

order by s desc;

8.查询所有学生的选课情况

select a.sno,sname,count(cno) from student a left join sc b on a.sno=b.sno

group by a.sno,sname;

9.查询“c001” 课程成绩在80 分以上的学生的学号和姓名

select sno,sname from student where sno in

(select sno from sc where cno='c001' and score>80);

10.查询“c003”课程分数小于60的同学学号和姓名，按分数降序排列

select a.sno,sname,score from

(select * from sc where cno='c003' and score<60) a

join student b

on a.sno=b.sno

order by score desc;

11.查询课程名称为“SSH”，且分数低于60 的学生姓名和分数

select sname,score from

(select * from sc where cno=(

select cno from course where cname='SSH'

) and score<60) a

join student b

on a.sno=b.sno;

12.查询所有同学的学号、姓名、选课数、总成绩

select a.sno,sname,count(cno),sum(score) from student a

left join sc b on a.sno=b.sno

group by a.sno,sname;

13.查询每门课程被选修的学生数

select a.cno,cname,count(sno) from course a left join sc b on a.cno=b.cno

group by a.cno,cname;

14.查询至少选修两门课程的学生学号和姓名

select sno,sname from student where sno in

(select sno from sc group by sno having count(cno)>=2);

15.查询出只选修了一门课程的学生学号和姓名

select sno,sname from student where sno in

(select sno from sc group by sno having count(cno)=1);

16.查询平均成绩大于80 的同学的学号、姓名和平均成绩

select a.*,sname from

(select sno,avg(score) from sc group by sno having avg(score)>80) a

join student b on a.sno=b.sno;

17.查询所有课程成绩小于70 分的同学的学号、姓名

select sno,sname from student where sno in

(select sno from sc group by sno having max(score)<70);

select sno,sname from student where sno in

(select sno from sc where sno not in

(select distinct sno from sc where score>=70));

18.查询任何一门课程成绩在70 分以上的学生的姓名、课程名称和分数

select sname,cname,score from

(select * from sc where score>70) a

join student b on a.sno=b.sno

join course c on a.cno=c.cno;

19.查询两门以上及格课程的同学的学号及其平均成绩

select b.* from

(select sno from sc where score>=60 group by sno

having count(1)>=2) a

join

(select sno,avg(score) s from sc group by sno) b

on a.sno=b.sno;

select sno,avg_s from

(select sc.*,

avg(score) over(partition by sno) avg_s

from sc) a

where score>=60

group by sno,avg_s having count(1)>=2;

20.查询各科成绩前三名的记录:(不考虑成绩并列情况)

select * from

(select sc.*,

row_number() over(partition by cno order by score desc) r

from sc)

where r<=3;

21.查询每门功课成绩最好的前两名(并列 )

select * from

(select sc.*,

dense_rank() over(partition by cno order by score desc) r

from sc)

where r<=2;

22.查询1998年出生的学生名单

select * from student where to_char(sysdate,'yyyy')-sage=1998;

23.查询同名同姓学生名单，并统计同名人数

insert into student values('s011','王丽',24,'女');

select sname,count(1) from student group by sname having count(1)>=2;

select a.sname,count(1) from student a

join student b on a.sname=b.sname and a.sno!=b.sno

group by a.sname;

24.查询不同课程成绩相同的学生的学号、课程号、学生成绩

select a.* from sc a join sc b on a.cno!=b.cno and a.score=b.score;

25.查询没有学全所有课的同学的学号、姓名

--要知道总共有多少门课

select count(1) from course

--找出选修课程数量少于上面那个数量的学生信息

select a.sno,sname,count(cno) from student a left join sc b on a.sno=b.sno

group by a.sno,sname having count(cno)<(

select count(1) from course

);

26.查询全部学生都选修的课程的课程号和课程名

--有多少学生

select count(1) from student

--每个课程号和课程名分别有多少学生

select a.cno,cname,count(sno) from course a left join sc b on a.cno=b.cno

group by a.cno,cname having count(sno)=(

select count(1) from student

);

27.查询选修“谌燕”老师所授课程的学生中，每门课程成绩最高的学生姓名及其成绩

--找出谌燕教过哪些课，找出这些课的编号

select cno from course where tno=

(select tno from teacher where tname='谌燕')

--根据编号，去成绩表里面和学生表连接然后分组，查出最高分

select sname,score from

(select * from

(select a.*,

max(score) over(partition by cno) m

from

(select * from sc where cno in (

select cno from course where tno=

(select tno from teacher where tname='谌燕')

)) a)

where score=m) b

join student a on a.sno=b.sno;

select sname,cno,score from

(select a.*,

max(score) over(partition by cno) m

from

(select sname,b.cno cno,score from student a join sc b on a.sno=b.sno

join course c on b.cno=c.cno

join teacher d on c.tno=d.tno

where tname='谌燕') a)

where score=m;

28.查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select sname from student where sno not in

(select distinct sno from sc where cno in

(select cno from course where tno=

(select tno from teacher where tname='谌燕')));

29.查询学过“谌燕”老师所教的所有课的同学的学号、姓名

--谌燕教过哪些课

select cno from course where tno=

(select tno from teacher where tname='谌燕')

--谌燕的课程数量是多少

select count(cno) from course where tno=

(select tno from teacher where tname='谌燕')

--在成绩表中筛选学号

select sno from sc where cno in (

select cno from course where tno=

(select tno from teacher where tname='谌燕')

) group by sno having count(1)=(

select count(cno) from course where tno=

(select tno from teacher where tname='谌燕')

)

--放入到学生表中查询出姓名

select sno,sname from student where sno in (

select sno from sc where cno in (

select cno from course where tno=

(select tno from teacher where tname='谌燕')

) group by sno having count(1)=(

select count(cno) from course where tno=

(select tno from teacher where tname='谌燕')

));

30.查询学过“c001”并且也学过编号“c002”课程的所有同学的学号、姓名

select sno,sname from student where sno in(

select a.sno from

(select * from sc where cno='c001') a

join

(select * from sc where cno='c002') b

on a.sno=b.sno);

distinct和group by都可以去重，那么场景下用哪一种，效率更高？

1. 数据量非常大的时候，group by的速度更快

2. 数据量不大的时候，两者的速度差不多，如果去重的列，有大量的重复的信息的组成的，那么group by更快，如果列里面大部分的信息都不重复，只有少部分需要去重的数据，那么distinct更快

伪列：在创建表格的时候，没有直接写上去的列名，但是每个表都一定会有的信息，效果等同于row_number()

rownum：分页查询，必须从1开始查询才会有结果

select a.*,rownum r from scott.emp a;

如果需要从中间的序号开始查询，就需要做表格的嵌套

select * from

(select a.*,rownum r from scott.emp a) b

where r>=3 and r<=6;

要先排序，然后再分页

找出公司里面，工资最高的员工信息

select a.*,rownum from

(select * from scott.emp order by sal desc) a

where rownum=1;

select * from

(select a.*,

row_number() over(order by sal desc) r

from scott.emp a)

where r=1;

rowid：数据去重，表里面每一行数据，在数据库中的唯一编号，不会出现重复的数据

--对重复的数据分组

--找到每个组里面最小或者最大的rowid

--删除除了这些rowid之外的其他rowid所在行

delete from student where rowid not in

(select min(rowid) from student

group by sno,sname,sage,ssex);create table table1(

id number,

name varchar2(10)

);

insert into table1 values(1,'a');

insert into table1 values(2,'b');

insert into table1 values(3,'b');

insert into table1 values(6,'b');

insert into table1 values(8,'c');

insert into table1 values(3,'a');

insert into table1 values(3,'c');

insert into table1 values(5,'c');

commit;

答案：

delete from table1 where rowid not in

(select rowid from

(select table1.*,

min(id) over(partition by name) mi

from table1) a

where id=mi);

delete from table1 where id not in

(select id from

(select table1.*,

min(id) over(partition by name) mi

from table1) a

where id=mi);

行列转换：

现在需要以行的方式去查看维度的数据

10 20 30

2916 2177 1566

select * from (select 需要查看的列名 from 表名)

pivot(聚合函数(计算的列名) for 维度的列名 in (维度列的值));

select * from (select deptno,sal from scott.emp)

pivot(avg(sal) for deptno in (10,20,30));

什么是维度什么是指标

维度：计算的范围，指标：计算结果

--根据部门的维度，计算平均工资的指标，维度是分组的列，指标是计算的列

练习：

查看每个工作岗位，工资的总和是多少，以下面的方式来展示

SALESMAN CLERK MANAGER

xxx yyy zzz

select * from (select job,sal from scott.emp)

pivot(sum(sal) for job in('SALESMAN','CLERK','MANAGER'));

查看每个部门中，每个工作岗位，平均工资是多少？

select * from (select 需要查看的列 from 表名)

pivot(聚合函数(计算的列名) for 小维度的那个列名 in (小维度列的值));

select * from (select deptno,job,sal from scott.emp)

pivot(avg(sal) for job in ('SALESMAN','CLERK','MANAGER','PRESIDENT'));

create table table_emp(

id number,

dept varchar2(10),

intype varchar2(10),

income number

);

insert into table_emp values(1,'sale','master',2000);

insert into table_emp values(1,'sale','slave',1000);

insert into table_emp values(2,'dev','master',3000);

insert into table_emp values(2,'dev','slave',2000);

commit;

答案：

select * from (select id,dept,intype,income from table_emp)

pivot(sum(income) for intype in ('master','slave'))

order by id;

只使用后面的case when的语法，不使用pivot()，也可以实现行列的转换create table table_emp2(

id number,

dept varchar2(10),

intype varchar2(10),

income number

);

insert into table_emp2 values(1,'sale','master',2000);

insert into table_emp2 values(2,'sale','slave',1000);

insert into table_emp2 values(3,'sale','master',2000);

insert into table_emp2 values(4,'sale','slave',1000);

insert into table_emp2 values(5,'dev','master',2000);

insert into table_emp2 values(6,'dev','slave',1000);

insert into table_emp2 values(7,'dev','master',2000);

insert into table_emp2 values(8,'dev','slave',1000);

commit;

答案：

delete from table_emp2 where rowid not in(

select min(rowid) from table_emp2 group by dept,intype,income);

delete from table_emp2 where id not in(

select min(id) from table_emp2 group by dept,intype,income);

逻辑函数和逻辑语句：

查询每个员工的工资和奖金一共有多少钱？

select ename,sal,comm,nvl(comm,0)+sal from scott.emp a;

空值运算的结果为空。

空值的默认值设置：

nvl(列名, 如果这列为空时设置的默认值)

nvl(comm,0) 如果comm这列为空，那么默认值是0，如果不为空，显示原来的值

nvl2(列名, 如果不为空显示的内容, 如果为空显示的内容)

select ename,sal,comm,nvl2(comm,sal+comm,sal) from scott.emp;

如果comm这列为空，那么显示sal本身，如果不为空，显示sal+comm

decode(列名, 判断条件1, 条件1为真的时候, 判断条件2, 条件2为真的时候, 判断条件3, 条件3为真的时候, ... , 所有条件都为假的情况)

如果怎么样

那么执行什么语句

否则怎么样

那么执行什么语句

...

剩下的情况

那么执行什么语句

select ename,sal,comm,

decode(comm,null,'没有奖金',0,'没有奖金','有奖金')

from scott.emp;

如果没有奖金，显示为0,显示提示没有，如果是0，也提示没有，否则，提示有奖金。

在decode里面，只能写精确的值的判断，不能写范围的判断，也不能写取反的值的判断等等，如果要实现范围的判断，需要结合sign()函数。

sign()函数：是用来比较数据的大小的

sign(x-y)：如果x比y大，结果就是1，两者相等，结果是0，x比y小，结果就是-1

查询emp表里面的员工工资，小于2000，等级为C，2000-3000等级为B，3000以上等级为A

select ename,sal,

decode(sign(sal-2000)+sign(sal-3000),-2,'C',2,'A','B')

from scott.emp;

判断每个学生每门课是否是及格的

select sno,cno,score,

decode(sign(score-60),-1,'不及格','及格')

from sc;

判断相关的语句：case when 语句

case

when 条件判断1 then 条件判断1为真的时候

when 条件判断2 then 条件判断2为真的时候

when 条件判断3 then 条件判断3为真的时候

else 所有条件都为假的时候

end

select sno,cno,score,

case

when score>=60 then '及格'

else '不及格'

end

from sc;

select ename,sal,

case

when sal<2000 then 'C'

when sal>=2000 and sal<=3000 then 'B'

else 'A'

end

from scott.emp;

使用case when判断emp的员工是普通员工还是管理层（MANAGER PERSIDENT）

select ename,job,

case

when job in('MANAGER','PRESIDENT') then '管理层'

else '普通员工'

end

from scott.emp;

查询出及格和不及格分别有多少人，显示效果如下：

jige bujige

7 1

select * from (

select sno,case when score>=60 then 'jige' else 'bujige' end c

from sc

)

pivot(count(sno) for c in ('jige','bujige'));

select

sum(case when score>=60 then 1 else 0 end) jige,

sum(case when score<60 then 1 else 0 end) bujige

from sc;

使用case when的方法，使用以行进行部门维度的展示，查看每个部门的平均工资

select

sum(nvl(case when deptno=10 then avg_s end,0)) "10",

sum(nvl(case when deptno=20 then avg_s end,0)) "20",

sum(nvl(case when deptno=30 then avg_s end,0)) "30"

from

(select deptno,avg(sal) avg_s from scott.emp group by deptno);

select * from student;

使用case when 写出男生和女生的数量

男女

5 6

select

sum(case when ssex='男' then 1 else 0 end) 男,

sum(case when ssex='女' then 1 else 0 end) 女

from student;

create table a(

t date,

status varchar2(10)

);

insert into a values(date'2020-6-1','Y');

insert into a values(date'2020-6-2','Y');

insert into a values(date'2020-6-3','Y');

insert into a values(date'2020-6-4','Y');

insert into a values(date'2020-6-5','Y');

insert into a values(date'2020-6-6','N');

insert into a values(date'2020-6-7','N');

insert into a values(date'2020-6-8','Y');

insert into a values(date'2020-6-9','Y');

insert into a values(date'2020-6-10','Y');

insert into a values(date'2020-6-11','Y');

insert into a values(date'2020-6-12','Y');

insert into a values(date'2020-6-13','N');

insert into a values(date'2020-6-14','N');

insert into a values(date'2020-6-15','Y');

insert into a values(date'2020-6-16','Y');

insert into a values(date'2020-6-17','Y');

insert into a values(date'2020-6-18','Y');

insert into a values(date'2020-6-19','Y');

insert into a values(date'2020-6-20','N');

insert into a values(date'2020-6-21','N');

insert into a values(date'2020-6-22','Y');

insert into a values(date'2020-6-23','Y');

insert into a values(date'2020-6-24','Y');

insert into a values(date'2020-6-25','N');

insert into a values(date'2020-6-26','N');

insert into a values(date'2020-6-27','N');

insert into a values(date'2020-6-28','Y');

insert into a values(date'2020-6-29','Y');

insert into a values(date'2020-6-30','Y');

commit;

create table b(

id varchar2(10),

sdate date,

ldate date

);

insert into b values('S001',date'2020-6-2',date'2020-6-8');

insert into b values('S002',date'2020-6-3',date'2020-6-26');

insert into b values('S003',date'2020-6-5',date'2020-6-15');

insert into b values('S004',date'2020-6-15',date'2020-6-30');

insert into b values('S005',date'2020-6-18',date'2020-6-27');

commit;

答案：

select id,count(1) from a,b where status='Y' and sdate<=t and ldate>=t

group by id having count(1)>10;

临时表：临时存储数据的表格，数据暂时放在这个表格中，等会会自动的根据什么事件消失掉，不会永久的占用你的物理磁盘的空间。

会话级临时表：

create global temporary table 表名(

列名数据类型

)on commit preserve rows;

create global temporary table dept_tmp(

deptno number,

dname varchar2(20),

loc varchar2(20)

)on commit preserve rows;

insert into dept_tmp select * from scott.dept;

select * from dept_tmp;

重新打开plsql的对话框，dept_tmp的表格内容就清空了

事务级临时表：

create global temporary table 表名(

列名数据类型

)on commit delete rows;

create global temporary table dept_tmp_2(

deptno number,

dname varchar2(20),

loc varchar2(20)

)on commit delete rows;

insert into dept_tmp_2 select * from scott.dept;

select * from dept_tmp_2;

如果进行了commit和rollback的操作，表格的数据都会被清空。

如果是正常的sql语句的操作，那么一般会话级的操作更多；

如果是在代码中写sql语句，那么就是事务级的临时表更多。

insert into 表名 select 语句;

将查询的结果，追加复制到表格中。

数据库的with as语句：

with 别名 as (select 语句),

别名 as (select 语句),

...

select 查询;

--查询SMITH上班的地点

with a as (select deptno from scott.emp where ename='SMITH')

select loc from a join scott.dept b on a.deptno=b.deptno;

--查询同时选修了c001和c002课程的学生学号

with a as (select sno from sc where cno='c001'),

b as (select sno from sc where cno='c002')

select a.* from a,b where a.sno=b.sno;

--查询10号部门工资大于2000的员工

with a as (select * from scott.emp where deptno=10)

select * from a where sal>2000;

完成最后的6个题目

31.查询“c001”课程比“c002”课程成绩高的所有同学的学号、姓名

select sno,sname from student where sno in

(select a.sno from

(select * from sc where cno='c001') a

join

(select * from sc where cno='c002') b

on a.sno=b.sno and a.score>b.score);

32.统计各科成绩,各分数段人数 : 课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select * from

(select sno,a.cno,cname,

case when score between 85 and 100 then '[100-85]'

when score>=70 and score<85 then '[85-70]'

when score>=60 and score<70 then '[70-60]'

else '[ <60]'

end s

from sc a join course b on a.cno=b.cno)

pivot(count(sno) for s in ('[100-85]','[85-70]','[70-60]','[ <60]'));

select a.cno,cname,

sum(case when score>=85 and score<=100 then 1 else 0 end) "[100-85]",

sum(case when score>=70 and score<85 then 1 else 0 end) "[85-70]",

sum(case when score>=60 and score<70 then 1 else 0 end) "[70-60]",

sum(case when score<60 then 1 else 0 end) "[ <60]"

from sc a join course b on a.cno=b.cno

group by a.cno,cname;

33.查询至少有有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

select sno,sname from student where sno in

(select distinct sno from sc where cno in

(select cno from sc where sno='s001') and sno!='s001');

34.查询学过学号为“s001”同学所有门课的其他同学学号和姓名

select sno,sname from student where sno in

(select sno from

(select cno from sc where sno='s001') a,sc b where sno!='s001' and a.cno=b.cno

group by sno having count(1)=(

select count(1) from sc where sno='s001'

));

35.查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名

select sno,sname from student where sno in

(select sno from

(select cno from sc where sno='s002') a,sc b where sno!='s002'

and a.cno(+)=b.cno

group by sno having count(1)=(

select count(1) from sc where sno='s002'

));

select sno,sname from student where sno in

(select sno from

(select cno from sc where sno='s002') a

right join sc b on a.cno=b.cno where sno!='s002'

group by sno having count(1)=(

select count(1) from sc where sno='s002'

));

36.按各科平均成绩从低到高和及格率的百分数从高到低顺序

--科目平均成绩是否及格的信息汇总到一个表中

select a.cno,avg_score,jige/count_cno*100||'%' jigelv from

(select cno,

sum(case when score>=60 then 1 else 0 end) jige

from sc

group by cno) a

join

(select distinct cno,

avg(score) over(partition by cno) avg_score,

count(1) over(partition by cno) count_cno

from sc) b

on a.cno=b.cno

order by avg_score asc,jigelv desc;

select cno,avg(score) avg_score,

sum(case when score>=60 then 1 else 0 end)/count(1)*100||'%' jigelv

from sc

group by cno

order by avg_score asc,jigelv desc;

4. 数据库的概念：

数据库的事务

什么是事务：在数据库里面，处理数据的最小的功能模块

转账的功能：验证账号、验证密码、a转出、b转入等等

begin

事务处理的sql语句和代码;

end;

declare

user1 varchar2(20);

user2 varchar2(20);

money number;

c number;

s1 number;

s2 number;

s1_2 number;

s2_2 number;

begin

user1:='&输入你的名字';

user2:='&输入对方的名字';

money:=&输入转账金额;

--验证a有没有这个用户

begin

select count(1) into c from scott.emp where ename=user1;

if c=0 then

raise_application_error(-20001,'没有这个账号');

end if;

end;

--验证b有没有这个用户

begin

select count(1) into c from scott.emp where ename=user2;

if c=0 then

raise_application_error(-20001,'没有这个账号');

else

select sal into s2 from scott.emp where ename=user2;

end if;

end;

--验证a有没有这么多钱

begin

select sal into s1 from scott.emp where ename=user1;

if s1<money then

raise_application_error(-20002,'余额不足');

else

update scott.emp set sal=sal-money where ename=user1;

update scott.emp set sal=sal+money where ename=user2;

end if;

end;

--验证是否转出成功，验证是否转入成功

begin

select sal into s1_2 from scott.emp where ename=user1;

select sal into s2_2 from scott.emp where ename=user2;

if s1_2=s1-money and s2_2=s2+money then

commit;

dbms_output.put_line('转账成功');

else

rollback;

end if;

end;

事务的几个特征：

原子性：每一个事务都是最小的功能模块

一致性：同时成功同时失败

隔离性：同时操作事务的时候，事务之间不会互相影响

持久性：通过事务操作的数据，是永久保存的

数据库的锁：

为了避免多个用户对同一个数据同时进行操作，操作数据的时候数据保存出错产生脏数据

从锁的范围上看

行锁：

A用户在对数据做dml操作的时候，数据会被上锁，B用户不能对这个数据所在的行同时进行操作

表锁：

A用户在对数据做dml操作的时候，数据所在的表会被上锁，B用户不能对表做ddl（除了新增表格结构之外）的操作

从上锁的主动性和被动性看

悲观锁：通过for update提前占用数据的资源

select 语句 + for update;

select * from scott.emp where ename='SMITH' for update;

乐观锁：数据库默认的就是乐观锁

数据库的死锁：

同时操作的进程里面，每个进程都互相占用了对方的资源，谁都无法释放资源和成功跑完

怎么解决死锁的问题？

--查看所有被上了锁的对象 69539

select * from v$locked_object;

--找到锁的对象，是在哪个表上面根据自己找到的这个表的名字，回去找session_id 131

select * from dba_objects where object_id=69539;

--找锁的id的 serial# 9555

select * from v$session where sid=131;

--根据这两个编号，杀掉这个锁

alter system kill session '131,9555';

分区表和表分区：***

表分区：将一个表里面，所有的拥有相同特征的数据，在硬盘上规划出一块区域，整合在一起

分区表：拥有表分区的划分规律的表格

表分区的类型划分：

1. 散列分区：hash 用来划分那么没有什么规律的列

create table emp_hash(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by hash(ename) partitions 4;

通过数据库的内部的哈希算法，将所有的行，根据名字的内容，一共放到4个不同的分区中进行分开的保存。哈希算法的分区的数量，最好是写2的次方数。

2. 列表分区 list

create table emp_list(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by list(deptno)

(

partition dept10 values(10),

partition dept20 values(20),

partition dept30 values(30)

);

当某个列的值，是大量的重复的相同数据数据，使用列表分区，对这个列进行特征和内容的归纳和划分，将拥有相同数据的行，放在同一个分区中

3. 范围分区 range

create table emp_range(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by range(sal)

(

partition sal_0_1000 values less than (1001),

partition sal_1001_2000 values less than (2001),

partition sal_2001_3000 values less than (3001),

partition sal_3001 values less than (maxvalue)

);

日期分区

partition s20201125 values less than(date'2020-11-26'),

partition s20201126 values less than(date'2020-11-27')

数字类型、日期类型的数据，例如金额、时间、年龄等等，将某个范围内的数据，存放在一起，那么就使用范围分区

给表格添加140W行的数据：

declare

begin

for i in 1..100000 loop

insert into emp_range select * from scott.emp;

commit;

end loop;

end;

如果一个表的数据越来越大，查询很慢的话，重新创建一个分区表，复制原有的数据，删除旧表。

4. 组合分区

create table 表名(

列名数据类型

)partition by 父分区类型(列名)

subpartition by 子分区类型(列名)

(

partition 父分区名字 values 分区规则(

subpartition 子分区名字 values 分区规则，

...

)

);

create table emp_zuhe(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by list(deptno)

subpartition by range(sal)

(

partition d10 values(10)(

subpartition sal1000_10 values less than (1001),

subpartition sal2000_10 values less than (2001),

subpartition salmax_10 values less than (maxvalue)

partition d20 values(20)(

subpartition sal1000_20 values less than (1001),

subpartition sal2000_20 values less than (2001),

subpartition salmax_20 values less than (maxvalue)

partition d30 values(30)(

subpartition sal1000_30 values less than (1001),

subpartition sal2000_30 values less than (2001),

subpartition salmax_30 values less than (maxvalue)

)

);

首先划分父分区，设置父分区的规则，然后在父分区中划分子分区的规则，这样可以在同时对两个列进行查询的时候，有效的减少查询需要消耗的资源和时间。

2000W以上的数据，就需要划分分区表了。

查看分区

select * from user_indexes; --查看所有的索引

select * from user_tables; --查看所有的表

select * from user_tab_partitions; --查看所有的分区表

select * from user_procedures

alter table 表名 drop partition 分区名;

alter table 表名 add partition 分区名 values 设置的规则;

数据库的索引：index

什么是索引：就是每一个表格的目录结构，帮你快速的定位表格中的数据在表格里的位置

添加索引的语法：

create index 索引名 on 表名(列名);

索引有哪些类型：

1. 主键索引：在创建表格的时候，添加了主键约束，那么就会自动生成主键索引

alter table emp_idx add constraint pri_empno primary key(empno);

通过主键约束的创建，来生成这个主键索引

2. 唯一索引：

2.1 在创建表格的时候，会根据唯一约束自动的生成唯一索引

alter table emp_idx add constraint uni_empno unique(empno);

2.2 在没有约束的前提下，手动创建唯一索引，唯一索引不会反过去创建唯一约束

create unique index idx_emp_empno on emp_idx(empno);

3. 普通索引：这个列经常需要被查询，但是这个列又没有什么特点

create index idx_nor_ename on emp_idx(ename);

4. 组合索引：和普通索引相同，有多个列需要同时被查询，但是这些列也没有什么特点，那么就放在一起，创建一个组合索引

create index idx_sal_job on emp_idx(sal,job);

create index 索引名 on 表名(a,b,c);

abc

查询组合索引，一定需要包含组合的第一个列

5. 函数索引：如果你的列，是需要被使用函数计算之后再用来查询，那么计算过程需要被写在索引里面

create index idx_func_hiredate on emp_idx(to_char(hiredate,'yyyy'));

6. 位图索引：列的内容是由大量的重复的内容组成的 bitmap

create bitmap index idx_emp_deptno on emp_idx(deptno);

7. 分区表索引

7.1 本地索引

create index idx_hash_ename on emp_hash(ename) local;

7.2 全局索引

create index idx_hash_job on emp_hash(job) global;

本地和全局的区别？

--本地索引的话，在创建唯一索引的时候，在整个表中，可能会出现重复信息，在一个分区里面，值保证是唯一的，全局索引是整个表中，数据唯一存在

--删除了一个分区或者新增了一个分区，那么全局的索引会失效，需要你删除了索引然后重新建立，本地的索引是不会失效的

删除索引：

drop index 索引名;

drop index idx_hash_job;

什么时候用哪种索引

普通的索引和位图的索引，区别在哪？

主键、唯一、普通、组合、函数（普通的normal类型）树状结构 b树索引，btree索引

位图（bitmap）二维表结构

普通索引是通过根节点、分支节点、叶子节点、行数据四个节点块，一层层进行数据范围的筛选。

位图的索引是在二维表中，通过0和1来存储数据内容。

每一种索引都是通过rowid来搜索数据的。

索引是不是越多越好？

一个表不要建超过5个索引；索引的数量，不要超过列的数量的15%。

索引一定是建立上反复的经常被查询的列上。

索引会加快dql的速度，但是会降低dml的速度。

有一个2000W数据的表格，要接着往里面添加2000W的数据，怎么样操作最快？

1.先删除所有的索引

2.插入2000W数据

3.重建索引

索引在什么时候会失效？

1. 经常需要被修改的列，索引是一直处于失效状态的

2. 数据发生了隐性的转换

select * from emp_idx where to_char(hiredate,'yyyy')=1981;

3. 计算的时候，公式放在了等号的左边

select * from emp_idx where sal+1000=4000 and job='MANAGER'

4. 查询的时候使用不等于

select * from emp_idx where deptno!=20

5. 查询的时候使用了not

select * from emp_idx where not deptno=20

6. 空值查询

select * from emp_idx where ename is null

7. 组合索引没有使用第一列的列查询

8. 查询的时候，使用了非函数索引的函数

什么是索引？

索引有哪些类型？分别在什么时候会使用到不同的索引？

索引是不是越多越好？

大量数据的表格插入，怎么操作会更快？

索引在什么时候会失效create table t2(

from_user varchar2(5),

to_user varchar2(5),

value number

);

insert into t2 values('a','b',1);

insert into t2 values('b','c',2);

insert into t2 values('d','a',2);

insert into t2 values('a','c',2);

insert into t2 values('e','f',5);

commit;

select a.u,sum(value) from

(select replace(u,'a','') u from

(select concat(from_user,to_user) u,value from t2)

where u like '%a%') a

(select concat(from_user,to_user) u,value from t2) b

where a.u=substr(b.u,1,1) or a.u=substr(b.u,2,1)

group by a.u

order by sum(value) desc;

select u,sum(value) from

(select a.u,from_user u2,value from

(select replace(u,'a','') u from

(select concat(from_user,to_user) u,value from t2)

where u like '%a%') a

join t2 on a.u=t2.from_user

union all

select a.u,to_user u2,value from

(select replace(u,'a','') u from

(select concat(from_user,to_user) u,value from t2)

where u like '%a%') a

join t2 on a.u=t2.to_user)

group by u

order by sum(value) desc;

************

使用oracle完成这个题目：

create table u1(

id number,

name varchar2(10),

address varchar2(10)

);

insert into u1 values(1,'张三','北京');

insert into u1 values(2,'李四','上海');

insert into u1 values(3,'王五',NULL);

commit;

create table u2(

id number,

name varchar2(10),

address varchar2(10)

);

insert into u2 values(1,'张三','上海');

insert into u2 values(3,'王五','广州');

insert into u2 values(4,'赵六','深圳');

commit;

select case when b.id is null then a.id else b.id end id,

case when b.name is null then a.name else b.name end name,

case when b.address is null then a.address else b.address end address

from u1 a full join u2 b on a.id=b.id

order by id;

select id,name,address from

(select b.*,

max(r) over(partition by id,name) m

from

(select a.*,

rownum r

from

(select * from u1

union all

select * from u2) a) b)

where r=m;

数据库的执行计划：看sql执行的效率

怎么查看执行计划？

在plsql工具的菜单栏里面，点击explain plan按钮，或者使用f5的快捷键来查看执行计划执行计划你看什么？

1.查看查询这个数据的方式

全表扫描

索引扫描

分区表的扫描

2.查看语句执行的时候，内部逻辑的执行顺序

3.查看每一个步骤，操作的对象的名字

4.每一步的资源的累计消耗

5.每一步操作的行的数量

6.查看CPU的资源消耗

7.硬盘读写的资源消耗

8.查看每个步骤的时间消耗，单位是ms

通过命令行的方式，来查看执行计划的内容：

cmd--sqlplus

输入用户名和密码

打开执行计划开关：

set autotrace on;

输入要查看的sql语句。

表连接的时候，通过执行计划查看到的表连接内部的逻辑：

表连接有三种内部的连接方式（查询内容的三种方法）：

hash join 哈希连接

nested loops nl嵌套连接

merge join 排序归并

什么时候会出现这个hash join？

两个表格列的等值筛选

1. 两个表的列，进行非等值筛选；

2. A表和B表的数据，相差很大，并且表连接的列都是有索引的，这个时候的等值连接就不会变成hash join，而是会使用nl嵌套连接。

什么时候会出现merge join 排序归并？

两个表的连接，筛选条件不是对列和列进行筛选，而是对列和值进行筛选

hints优化器：用来强行改变表格内部运行逻辑的方法

写在select后面，写在列名的前面

/*+ 优化器的内容 */

改变表连接的逻辑：

/* +use_hash(a b) */ 强制使用哈希连接

/* +use_merge(a b)*/ 强制使用排序归并

/* +use_nl(a b) */ 强制使用嵌套连接

改变表格读取的顺序

/* +leading(a b) */ a一定要是小表（驱动表），b一定是大表（匹配表）

改变查询表格的方法

/*+ full(a) */ 强制全表扫描

/*+ index(表名索引名) */ 强制使用索引

并发的使用

/*+ parallel(8) */ 强制使用并行的资源，来执行这个sql语句

在oracle中怎么去优化查询的sql语句/怎么优化数据库？

1.要先通过执行计划看一下sql语句执行的效率

2.查看表格的数量，如果数据量很大，就先给表格进行分区表的设置

3.给表格建适当的索引

4.检查索引失效的语句

5.使用嵌套去替换部分连接的场景

查询SMITH在哪里上班

联合查询14 4 56行

嵌套查询14 4 18行

6.使用临时表去存储一个或者多个大表筛选的结果，然后使用临时表进行表格的连接

7.or的效率比较低，可以使用union all去进行替换

8. 尽量少使用union minus intersect等集合的运算，这些都是效率很差的关键字

9.使用多线程的并发的优化器去加速执行的速度

oracle的序列：sequence，一组有规律的从大到小或者从小到大的数字，一般序列都是在表格里面，充当主键的自增序号来使用的

简单序列的创建：

create sequence 序列名字;

序列的两个属性：

查看序列当前的内容

序列.currval;

使用序列，并且让序列进入到下一个数字

序列.nextval;

create sequence seq02;

create table s1(

id number,

name varchar2(20)

);

insert into s1 values(seq02.nextval,'tom');

创建一个自定义的序列：

create sequence 序列名字

start with 开始数字

increment by 自增的长度

maxvalue 最大值的数字 | nomaxvalue

minvalue 最小值的数字 | nominvalue

cache 缓存数字的数量 | nocache

cycle | nocycle;

create sequence seq03

start with 100

increment by 5

maxvalue 150

minvalue 0

cache 10

cycle;

删除序列：

drop sequence 序列名字;

oracle的视图：view 一个或者多个表查询的结果，这个结果就是一个虚拟的表，每次运行视图，都会重新运行视图中的那个查询语句

create view 视图名 as

select 语句

with read only;

视图的作用：

1. 将一个复杂的select语句，放入到一个视图里面，简化日常的操作

2. 可以隐藏一部分原表的列，简化别人在操作表的时候的业务逻辑

3. 可以添加with read only，增加表的安全性

4. 视图可以节省网络流量

视图的缺点：

对拥有复杂逻辑的视图，再次表连接等操作，会导致语句更加复杂，运行效率很慢。

oracle的物化视图：是一个真实的物理的表格，将一个或多个表格查询的结果，当成一个新的表格来保存，物化视图的表格，会对原表的数据进行同步，物化视图的表格，本身就是不能更改的

物化视图也叫做快照表。

create materialized view 物化视图名字

refresh on commit | demand

start with 时间点 next 下一次更新的时间点

select 语句;

on commit：类似于实时更新，原表提交数据时更新

create materialized view stu_nan

refresh on commit

select * from student where ssex='男' and sage>20;

on demand：定时更新

create materialized view stu_nv_20

refresh on demand

start with sysdate

next to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss')

select * from student where ssex='女' and sage>20;

表格更新的方法：

1. complete 完全刷新，整个表格全部都更新一次数据

2. fast 快速刷新，更新你变更部分的数据

3. force 默认的更新方式，默认的更新方法就是fast

4. never 不要更新表格

declare

begin

dbms_mview.refresh('物化视图的名字','更新的方法');

end;

视图和物化视图的区别是什么？

1. 视图是一张虚拟的表格，不会占用磁盘空间，物化视图是一个真实的表格，会占用磁盘空间

2. 视图为了简化select操作用的，物化视图也叫做快照表，是为了保存某个表格或者是select语句在某个时间点的数据而存在的

3. 视图是实时更新的，物化视图有两个刷新表格内容的方法，一种是提交数据的时候更新，第二种是定时更新

4. 当原表非常大的时候，视图不会减少查询时间，物化视图是会减少查询时间的

5. 可以在物化视图上建立索引，进一步加快查询速度

☐

拉链表，缓慢变化维：

拉链表就是一张普通的表格，这个表格会保存你每一次数据前后变更的状态。

1.以行的方式保存历史数据和变化状态

有开始时间和结束时间还有变更状态这三列，用来记录每一行数据前后变更的状态和顺序，这三列叫做缓慢变化维。

优点：可以保存所有的历史记录

缺点：表格的数据会特别的多

2.以列的方式保存最近的历史数据

会创建列的旧数据的备份列，会保存最近一次的变更的数据，和数据变更的时间

优点：可以保证表格的数据量不会很大

缺点：看不到详细的变化状态

sql语句的分类：dcl（权限的操作 grant revoke） ddl（对数据库对象的操作：用户表索引视图物化视图 create alter drop truncate） dml(对表里面的数据的操作 insert update delete) dql(表数据的查询 select)

数据类型：integer number number(总长度,小数精度) char(长度) varchar2(长度)

date blob clob

约束条件：primary key unique not null check foreign key

常用函数有哪些？

聚合函数：max min avg sum count

单行函数：数字 round trunc abs floor（6.3 变6） ceil(6.1 变7） mod power

字符串 substr concat replace length

日期 last_day months_between add_months sysdate

转换 to_date to_char to_number

分析函数：row_number rank dense_rank lag lead + over()

行列转换：pivot

空值处理：nvl nvl2

逻辑相关：decode

字符串的行列转换：wm_concat()

将一个列的字符串，以行的方式拼接起来，中间用逗号隔开怎么去做数据的同比和环比？

lag() over(partition by month order by year)

lag() over(partition by year order by month)

select * from a1 join a2 on a1.year=a2.year+1 and a1.month=a2.month;

select * from a1 join a2 on a1.month=a2.month+1 and a1.year=a2.year;

有a表和b表，a和b都有身份证的列，找出a表有但是b表没有的身份证号码。

select * from a left join b on a.id=b.id where b.id is null;

a表有 2 3，b表有3 4，请问

select * from a left join b on a.id=b.id and a.id=2;

2 null

3 null

select * from a left join b on a.id=b.id where a.id=2;

2 null

数据库怎么进行优化？

执行计划

分区

索引

关键字的优化

怎么看执行计划？

执行计划看什么内容呢？

表连接有哪几个逻辑和方法？

每一种表连接的方法，什么时候最快？

优化器有用过哪些？

分区有哪些类型？在什么时候会用到这么不同的类型？

索引有哪些类型？在什么时候用到不同的索引？

普通索引和位图索引有什么区别？

视图是什么？什么时候用？为什么用？

知不知道快照表？一般是用来做什么的？

视图和物化视图的区别是什么？

有没有用过with as的语句，怎么写的？

with 别名 as(select 语句)

select 查询;

临时表有没有用过，有哪些不同的临时表，什么时候用？

什么叫事务，有哪些特征？

什么是锁？锁有哪些类型？什么是死锁？怎么处理死锁？

alter system kill session(sid, serial#);

5. 数据库的代码块：PLSQL编程

匿名块：是一个没有名字的代码块，一般就是临时处理数据用的，用完一次就不要了

declare

--变量的声明部分

begin

--代码的逻辑和执行部分

end;

什么是变量呢？

变量是用来存储数据的容器。

给变量赋值： a:=100

将100的内容存放在a这个变量里面

打印和输出：

dbms_output.put_line(一个变量或者一个内容);

declare

--变量的声明部分

a number;

b varchar2(10);

c date;

begin

--代码的逻辑和执行部分

a:=100;

b:='hello';

c:=sysdate;

dbms_output.put_line(a||b||c);

end;

普通变量

a number;

v_user emp%rowtype;

--v_empno emp.empno%type;

v_ename emp.ename%type;引用型变量

变量名字表名.列名%type;

declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp where empno=7369;

dbms_output.put_line('工资是'||v_sal);

end;

记录型变量，数组型变量

变量名表名%rowtype;

declare

v_user emp%rowtype;

begin

select * into v_user from emp where empno=7369;

dbms_output.put_line(v_user.ename||','||v_user.job);

end;

变量值的输入：

数字类型的变量名:=&提示语句;

字符串类型的变量名:='&提示语句';

declare

begin

--v_empno:=&输入要查询的员工编号;

v_ename:='&输入员工姓名';

select * into v_user from emp where ename=v_ename;

dbms_output.put_line(v_user.ename||','||v_user.job);

end;

在代码块中，所有的select都一定要有into来存放你查询出来的数据

into 变量，只能放一个数据，并且一定要有数据

分支和判断：

如果怎么样那么

就怎么样

否则

就怎么样

if 条件判断 then

执行sql语句;

if 条件判断 then

执行sql语句;

else

执行sql语句;

end if;

if 条件判断 then

执行sql语句;

elsif 条件判断 then

执行sql语句;

elsif 条件判断 then

执行sql语句;

......

else

执行sql语句;

end if;

declare

v_sal emp.sal%type;

c number;

begin

select count(1) into c from emp where deptno=60;

if c=0 then

dbms_output.put_line('部门里面没有员工');

elsif c>=2 then

dbms_output.put_line('部门有多个员工');

else

--在查询工资之前，先判断结果的数量

select sal into v_sal from emp where deptno=60;

end if;

end;

练习：用户输入一个数字，然后去判断这个数字是正数，负数，零？

declare

n number;

begin

n:=&数字是;

if n>0 then

dbms_output.put_line('正数');

elsif n<0 then

dbms_output.put_line('负数');

else

dbms_output.put_line('零');

end if;

end;

输入一个用户的编号，首先要先判断有没有这个用户，如果有这个用户，就输出这个用户的工资等级，2000以下是C，2001-3000是B，3001以上是A。

declare

v_empno emp.empno%type;

c number;

v_sal emp.sal%type;

begin

v_empno:=&员工编号;

select count(1) into c from emp where empno=v_empno;

if c=0 then

dbms_output.put_line('没有这个员工');

else

select sal into v_sal from emp where empno=v_empno;

if v_sal<=2000 then

dbms_output.put_line('C');

elsif v_sal>2001 and v_sal<=3000 then

dbms_output.put_line('B');

else

dbms_output.put_line('A');

end if;

end;

和if语句相同类型和效果的另一些判断语句的写法：case when

case

when 条件判断 then 执行的sql语句;

......

else 执行的sql语句;

end case;

declare

v_empno number;

c number;

begin

v_empno:=&员工编号;

select count(1) into c from emp where empno=v_empno;

case

when c=0 then dbms_output.put_line('没有这个员工');

else dbms_output.put_line('有这个人');

end case;

end;

循环控制：

for：有循环范围的循环方式，一开始就知道运行次数的循环方法

for 变量名 in 循环范围 loop

执行的sql语句;

end loop;

declare

begin

for i in 1..10 loop

dbms_output.put_line('hello'||i);

end loop;

end;

declare

s number;

begin

s:=0;

for i in 1..10 loop

s:=s+i;

end loop;

dbms_output.put_line(s);

end;

练习：将1+3+5+99的和，计算出来

declare

s number;

begin

s:=0;

for i in 1..99 loop

if mod(i,2)=1 then

s:=s+i;

end if;

end loop;

dbms_output.put_line(s);

end;

--使用put()来进行连续的一整行的输出

declare

begin

for i in 1..10 loop

dbms_output.put('hello');

end loop;

dbms_output.put_line('');

end;

使用for循环，画下面这个图形

***

****

*****

declare

begin

for i in 1..5 loop

for j in 1..i loop

dbms_output.put('*');

end loop;

dbms_output.put_line('');

end loop;

end;

练习9 9乘法表

declare

begin

for i in 1..9 loop

for j in 1..i loop

dbms_output.put(i||'*'||j||'='||i*j||' ');

end loop;

dbms_output.put_line('');

end loop;

end;

使用for循环，完成下面的计算：

假如现在有鸡和兔子，一共有35个头，94只脚，计算出兔子和鸡分别有多少只？

declare

ji number;

begin

for tu in 0..35 loop

ji:=35-tu;

if 2*ji+4*tu=94 then

dbms_output.put_line(ji||','||tu);

end if;

end loop;

end;

declare

begin

for tu in 0..35 loop

for ji in 0..35 loop

if ji+tu=35 and 2*ji+4*tu=94 then

dbms_output.put_line(ji||','||tu);

end if;

end loop;

end;

计算出100-999之间所有的水仙花数个位的3次方+十位的3次方+百位的3次方

power(3,4) 3的4次方

declare

begin

for bai in 1..9 loop

for shi in 0..9 loop

for ge in 0..9 loop

if power(bai,3)+power(shi,3)+power(ge,3)=bai*100+shi*10+ge then

dbms_output.put_line(bai*100+shi*10+ge);

end if;

end loop;

end;

select * from scott.emp;

create table emp_2 as select * from scott.emp where 1=2; --只会复制结构，不会复制约束

select * from emp_2;

先创建一个和scott.emp结构相同的表格，从上往下查询整个表格的数据，

一行行的读取数据，将名字里面包含了A的，并且工资低于2000的用户信息，单独复制保存到新表中。

declare

c number;

v_user scott.emp%rowtype;

begin

select count(1) into c from scott.emp;

for i in 1..c loop

select empno,ename,job,mgr,hiredate,sal,comm,deptno into v_user

from (select a.*,rownum r from scott.emp a) where r=i;

if v_user.ename like '%A%' and v_user.sal<2000 then

insert into emp_2 values(v_user.empno,v_user.ename,v_user.job,v_user.mgr,v_user.hiredate,v_user.sal,

v_user.comm,v_user.deptno);

commit;

end if;

end loop;

end;

while和loop循环，当循环的范围不是特别确定的时候

while：如果判断为真，就进入到循环，否则跳出循环

while 条件判断 loop

执行的sql语句;

end loop;

declare

n number;

begin

n:=1;

while n<=10 loop

dbms_output.put_line('数字'||n);

n:=n+1;

end loop;

end;

练习：

有张纸，厚度是1mm，珠穆朗玛峰是8848m，请问，这个纸要对折多少次，厚度才会超过山的高度。

declare

zhi number;

shan number;

c number;

begin

zhi:=1;

shan:=8848000;

c:=0;

while zhi<shan loop

zhi:=zhi*2;

c:=c+1;

end loop;

dbms_output.put_line(c);

end;

loop：判断条件，当条件为真的时候，退出循环，条件为假的时候执行循环

loop

exit when 条件判断;

执行的sql语句;

end loop;

declare

n number;

begin

n:=10;

loop

exit when n<0;

dbms_output.put_line(n);

n:=n-2;

end loop;

end;

练习：

假如从今天开始存1分钱，每天翻倍，明天2分，后天4分，大后天8分，请问要多少天才能存够100万元，使用loop循环计算。

declare

q number;

money number;

d number;

begin

q:=1;

money:=0;

d:=0;

loop

exit when money>=100000000;

money:=money+q;

q:=q*2;

d:=d+1;

end loop;

dbms_output.put_line(d);

dbms_output.put_line(money);

end;

和循环相关的关键字：

continue：直接开始下一次的循环，忽略掉continue后面的代码

declare

begin

for i in 1..10 loop

if i=5 then

continue;

end if;

dbms_output.put_line(i);

end loop;

end;

exit：跳出整个循环，停止循环

declare

begin

for i in 1..10 loop

if i=5 then

exit;

end if;

dbms_output.put_line(i);

end loop;

end;

goto：标签语句，使用goto进行代码的任意的跳转，在工作中尽量不要用，容易引起逻辑的混乱

declare

begin

for i in 1..10 loop

if i=5 then

goto here;

end if;

dbms_output.put_line(i);

end loop;

dbms_output.put_line('world');

<<here>>

dbms_output.put_line('hello');

end;

continue和exit都只能控制到离关键字最近的循环。

动态sql：在动态sql的句子里面，可以写入任何一个句型

代码块只支持dml和dql语句。

declare

s varchar2(500);

begin

s:='truncate table emp_2';

execute immediate s;

end;

declare

s varchar2(500);

tb varchar2(500);

begin

tb:='&表名';

s:='truncate table '||tb;

execute immediate s;

end;

--备份所有自己的T开头的表格，表格的名字 STUDENT_1126 原表名_月日

--要先准备一个表格，用来存储当前所需要的找出来的表名

create table tmp_t(tn varchar2(50));

declare

c number;

v_tn varchar2(50);

s varchar2(500);

begin

--确定有几个T开头的表，数量是用来写循环的

select count(1) into c from user_tables where table_name like 'T%';

--将找到的表名放到临时表中

insert into tmp_t select table_name from user_tables where table_name like 'T%';

for i in 1..c loop

--从临时表里面取出数据

select tn into v_tn from

(select tn,rownum r from tmp_t)

where r=i;

--拿到表名之后进行表格的复制

s:='create table '||v_tn||'_1126 as select * from '||v_tn;

dbms_output.put_line(s);

--执行复制语句

execute immediate s;

end loop;

end;

--找到自己的所有的表，如果这个表中间的某个字符串varchar2的列，长度没有200，那么统一修改成长度200

使用select * from user_tab_columns; 查询表格和列的信息

declare

v_tn varchar2(50);

v_cn varchar2(50);

c number;

s varchar2(500);

c2 number;

begin

--找出所有字符串不够200长度的数量

select count(1) into c from user_tab_columns

where data_type='VARCHAR2' and data_length<200;

--对数量做循环，确定自己要重新修改多少次

for i in 1..c loop

--找出所有符合条件的表名，列名，拼接user_tab_columns和user_tables，确定找到的名称都是表的名字，排除掉视图的名称

select table_name,column_name into v_tn,v_cn from

(select a.table_name,column_name,rownum r from user_tab_columns a join user_tables b on a.table_name=b.table_name

where data_type='VARCHAR2' and data_length<200) where r=i;

--确定这个表不是一个分区表

select count(1) into c2 from user_tab_partitions where table_name=v_tn;

if c2=0 then

--修改确定的这个表的列

s:='alter table '||v_tn||' modify '||v_cn||' varchar2(200)';

dbms_output.put_line(s);

--执行操作

execute immediate s;

end if;

end loop;

end;

练习：

第一题，创建一个表

create table numbers(

n number

);

使用代码块，给这个表添加1-100的数字。

declare

begin

for i in 1..100 loop

insert into numbers values(i);

commit;

end loop;

end;

第二题，查询出上面这个表里面，所有两两相加等于88的数字。

declare

n1 number;

n2 number;

begin

for i in 1..100 loop

if i=44 then

exit;

end if;

for j in 1..100 loop

select n into n1 from(select n,rownum r from numbers) where r=i;

select n into n2 from(select n,rownum r from numbers) where r=j;

if n1+n2=88 then

dbms_output.put_line(n1||','||n2);

end if;

end loop;

end;

declare

begin

for i in (select n from numbers) loop

if i.n=44 then

exit;

end if;

for j in (select n from numbers) loop

if i.n+j.n=88 then

dbms_output.put_line(i.n||','||j.n);

end if;

end loop;

end;

游标：cursor，从上往下游动的箭头，每一次都会读取一行数据，每一行数据都只能读取一次

声明一个游标

cursor 游标名 is select 语句;

打开游标

open 游标名;

使用游标，获取游标当前的内容

fetch 游标名 into 变量;

关闭游标

close 游标名;

使用while循环操作游标：

declare

--声明一个游标

cursor mc is select * from scott.emp;

v_user scott.emp%rowtype;

begin

--打开这个游标

open mc;

--使用游标

fetch mc into v_user;

while mc%found loop

dbms_output.put_line(v_user.ename||','||v_user.deptno);

fetch mc into v_user;

end loop;

--关闭游标

close mc;

end;

使用loop循环操作游标：

declare

cursor mc is select table_name from user_tables where table_name like 'E%';

tn varchar2(50);

begin

open mc;

loop

fetch mc into tn;

exit when mc%notfound;

dbms_output.put_line(tn);

end loop;

close mc;

end;

使用for循环操作游标：

declare

cursor mc is select * from scott.emp where sal>=2000;

begin

for i in mc loop

dbms_output.put_line(i.ename||','||i.sal);

end loop;

end;

declare

begin

for i in (select * from scott.emp) loop

dbms_output.put_line(i.ename||','||i.sal);

end loop;

end;

备份U开头的所有的表格：

declare

begin

for i in (select table_name from user_tables where table_name like 'U%') loop

execute immediate 'create table '||i.table_name||'_1127 as select * from '||i.table_name;

end loop;

end;

将所有字符串长度不够500的，统一修改成500长度

declare

cursor mc is select * from

(select a.table_name,column_name from user_tab_columns a join user_tables b on a.table_name=b.table_name

where data_type='VARCHAR2' and data_length<500) a

where table_name not in

(select distinct table_name from user_tab_partitions);

begin

for i in mc loop

execute immediate 'alter table '||i.table_name||' modify '||i.column_name||' varchar2(500)';

end loop;

end;

上面的游标，叫做静态游标，就是一旦声明，游标的内容就不会变了。

动态的游标：为了让你只声明一个游标，可以反复的使用这个游标的类型，不需要创建多个游标

declare

--声明一个动态游标的类型自己定义的dongtai_c和number varchar2 date是一个意思

type dongtai_c is ref cursor;

--声明一个游标

mc dongtai_c;

v_user scott.emp%rowtype;

begin

--打开游标的同时，赋予游标执行的内容

open mc for select * from scott.emp where deptno=10;

--使用循环读取游标

loop

fetch mc into v_user;

exit when mc%notfound;

dbms_output.put_line(v_user.ename||','||v_user.deptno);

end loop;

--关闭游标

close mc;

end;

动态和静态的游标，都叫做显性游标，还有一种叫做隐性游标。通过sql关键字来查看，本次数据修改的范围，一共有多少行。

declare

begin

delete from emp where sal>1500;

dbms_output.put_line(sql%rowcount);

end;

sql%rowcount 统计修改的行数

sql%found 查看是否有修改数据

sql%notfound 查看是否没有修改数据

游标练习：

修改emp表格，如果员工是10号部门，就给他加10%的工资，20号部门加20%，30部门加30%。

declare

begin

for i in (select * from scott.emp) loop

if i.deptno=10 then

update scott.emp set sal=sal*1.1 where empno=i.empno;

elsif i.deptno=20 then

update scott.emp set sal=sal*1.2 where empno=i.empno;

elsif i.deptno=30 then

update scott.emp set sal=sal*1.3 where empno=i.empno;

end if;

commit;

end loop;

end;

存储过程：有名字的代码块 procedure

存储过程的硬代码是什么？

--没有参数的存储过程：

create or replace procedure 存储过程名字

变量的声明;

begin

执行的sql语句;

end;

--创建一个存储过程

create or replace procedure pro_p1

n1 number;

n2 number;

begin

n1:=100;

n2:=200;

dbms_output.put_line(n1+n2);

end;

--调用存储过程

call pro_p1();

创建一个有输入参数的存储过程

create or replace procedure 存储过程名字(输入参数名字 in 数据类型)

变量的声明;

begin

执行的sql语句;

end;

输入的参数是不能重新赋值的，是在调用的时候传入的信息。

create or replace procedure pro_p2(n1 in number,n2 in number)

n3 number;

begin

n3:=100;

dbms_output.put_line(n1+n2+n3);

end;

call pro_p2(2,3);

有输出参数的存储过程

create or replace procedure 存储过程名字(输入参数名字 out 数据类型)

变量的声明;

begin

执行的sql语句;

end;

create or replace procedure pro_p3(s out number)

n1 number;

n2 number;

begin

n1:=100;

n2:=200;

s:=n1+n2;

end;

有输出参数的存储过程，需要放入到匿名块中进行调用

declare

s number;

begin

pro_p3(s);

dbms_output.put_line(s);

end;

同时有输入和输出的存储过程：

create or replace procedure pro_p4(n1 in number,n2 in number,s out number)

begin

s:=n1+n2;

end;

declare

s number;

begin

pro_p4(1.5,1.8,s);

dbms_output.put_line(s);

end;

什么时候会使用到存储过程？一个固定的功能的代码块，并且这个代码需要经常的反复的运行

公司的常用的业务计算；

进行数据的增量、全量操作；

删除索引和恢复索引的操作；

每天自动的创建当天的新的分区；

日志的操作

....

如何使用存储过程，来每日创建新分区？

alter table 表名 add partition 分区名 values 设置的规则;

alter table sales_info add partition s20201127 values less than(date'2020-11-28');

alter table 表名 drop partition 分区名;

alter table sales_info drop partition s20201127;

--先创建一个分区表

create table sales_info(

saleid number,

saleman varchar2(50),

goods varchar2(50),

price number,

saletime date

)partition by range(saletime)

(

partition s20201124 values less than(date'2020-11-25'),

partition s20201125 values less than(date'2020-11-26'),

partition s20201126 values less than(date'2020-11-27')

);

--创建存储过程，自动添加分区

create or replace procedure pro_sales_add_partition

c number;

s varchar2(500);

begin

--检查有没有今天的新分区

select count(1) into c from user_tab_partitions

where table_name='SALES_INFO' and partition_name=concat('S',to_char(sysdate,'yyyymmdd'));

if c=0 then

--创建一个新分区

s:='alter table sales_info add partition s'||to_char(sysdate,'yyyymmdd')||

' values less than(date'''||to_char(sysdate+1,'yyyy-mm-dd')||''')';

execute immediate s;

end if;

end;

call pro_sales_add_partition();

异常处理

在代码中捕获所有出现的异常和错误

create or replace procedure 过程名

--声明部分

begin

--执行部分

exception

when 异常的名字 then

对异常的处理

when 异常的名字 then

对异常的处理

when others then

对异常的处理

end;

others表示所有的错误。

1. 系统预定义的异常 21种有名字有编号的错误，就是预定义错误

https://www.cnblogs.com/wwxbi/p/4162675.html

TOO_MANY_ROWS

NO_DATA_FOUND

ZERO_DIVIDE

VALUE_ERROR

INVALID_NUMBER

INVALID_CURSOR

CURSOR_ALREADY_OPEN

...

2. 系统的非预定义异常：有错误的代码，但是这个错误在系统中没有名字

这个名字，要我们自己来给

create or replace procedure pro_p7

--定义一个变量，这个变量数据类型是exception异常类型

fk_error exception;

--将这个变量名字和错误的代码绑定在一起

pragma exception_init(fk_error,-2291);

begin

update emp set deptno=60 where empno=7369;

commit;

exception

when fk_error then

dbms_output.put_line('父表中没有这个内容');

end;

3. 自定义异常：并不是语法错误，自己定义的逻辑上的问题，通过raise_application_error(错误编号, 错误提示)，错误编号的范围是-20001到-20999

例如：现在有人要加工资，但是规定是，每次加工资，不能超过原有工资的20%

create or replace procedure pro_p8(v_empno in number,v_sal in number)

s number;

m varchar2(500);

begin

select sal into s from emp where empno=v_empno;

if v_sal>=s*1.2 then

raise_application_error(-20001,'工资不能超过原来的20%'); --编号的范围 -20000 20999

else

update emp set sal=v_sal where empno=v_empno;

end if;

exception

when others then

m:=sqlerrm;

dbms_output.put_line(m);

end;

call pro_p8(7369,10000);

错误日志的保存：

1.先得有一个错误日志表

create table error_log(

proce_name varchar2(500),

table_name varchar2(500),

mcode varchar2(100),

merror varchar2(500),

mtime date

);

2.有一个存储过程，专门用来保存错误的信息

create or replace procedure insert_error_log(v_proce_name in varchar2,v_table_name in varchar2,

v_mcode in varchar2,v_merror in varchar2)

begin

insert into error_log values(v_proce_name,v_table_name,v_mcode,v_merror,sysdate);

commit;

end;

3.在另一个过程里面，如果运行报错了，那么就调用存入错误日志的存储过程

create or replace procedure pro_p8(v_empno in number,v_sal in number)

s number;

m varchar2(500);

c varchar2(200);

begin

select sal into s from emp where empno=v_empno;

if v_sal>=s*1.2 then

raise_application_error(-20001,'工资不能超过原来的20%'); --编号的范围 -20000 20999

else

update emp set sal=v_sal where empno=v_empno;

end if;

exception

when others then

m:=sqlerrm;

c:=sqlcode;

insert_error_log('pro_p8','emp',sqlcode,sqlerrm);

end;

call pro_p8(7369,10000);

异常有哪些类型？

常见的异常有哪几个？

怎么去处理异常？

存储过程的练习：

1. 写一个存储过程，要求往dept中添加新的部门，要求输入部门编号，部门名称，部门地点，输入的过程中，你的部门编号不能和之前的重复

create or replace procedure add_dept(v_deptno in number,v_dname in varchar2,v_loc in varchar2)

c number;

begin

select count(1) into c from dept where deptno=v_deptno;

if c=1 then

dbms_output.put_line('部门已存在');

else

insert into dept values(v_deptno,v_dname,v_loc);

commit;

end if;

end;

call add_dept(50,'BIGDATA','SHENZHEN')

2. 输入一个部门的编号，将这个部门里面所有人的名字和员工编号显示出来。

create or replace procedure select_emp(v_deptno in number)

cursor mc is select * from emp where deptno=v_deptno;

begin

for i in mc loop

dbms_output.put_line(i.ename||','||i.empno);

end loop;

end;

call select_emp(10);

之后完成41-60题

数据库的函数：自己去定义数据运行的过程，最终返回这个过程结果的代码块

create or replace function 函数名(输入的参数数据类型)

return 返回的数据类型

声明部分

begin

执行部分

end;

create or replace function qiuhe(n1 number,n2 number)

return number

s number;

begin

s:=nvl(n1,0)+nvl(n2,0);

return s;

end;

select empno,qiuhe(sal,comm) from emp;

实现一个和数据库power()相同的函数的功能，自己写一个求数字的次方的方法：

create or replace function cifang(n number,c number)

return number

s number;

begin

s:=1;

for i in 1..c loop

s:=s*n;

end loop;

return s;

end;

select cifang(2,3) from dual;

练习：数据库有个initcap()函数，这是首字母大写的函数，自己写一个最后一个字母大写的函数。

create or replace function endcap(str varchar2)

return varchar2

s varchar2(500);

begin

s:=concat(lower(substr(str,1,length(str)-1)),upper(substr(str,-1)));

return s;

end;

select endcap('allen') from dual;

存储过程和函数的区别？

1.存储过程可以没有参数，也可以有输入和输出的参数；函数一定要有输入的参数和返回的值

2.存储过程通过call调用，函数通过sql语句运行

3.函数里面不能执行dml和ddl操作，存储过程可以

4.函数是用来进行数据计算的，存储过程是用来实现一个固定的功能

数据库的触发器：在做A这个事情的时候，自动的发生B这个事情 trigger

检查输入的数据；实时备份表格的数据；记录表格操作的日志。

触发器的类型：

create or replace trigger 触发器名字

before|after insert or update or delete on 表名

for each row

begin

执行的sql语句;

end;

前置触发器：before

删除emp表格的时候，禁止删除PRESIDENT

create or replace trigger jinzhi_del_boss

before delete on emp

for each row

begin

if :old.job='PRESIDENT' then

raise_application_error(-20001,'不能删除老板');

end if;

end;

如果要去更新用户的工资，新增的用户，工资不能超过2000元，老用户涨工资不能超过原来工资的10%。

create or replace trigger ck_emp_sal

before insert or update on emp

for each row

begin

if inserting then

if :new.sal>2000 then

raise_application_error(-20001,'新员工工资不能超过2000元');

end if;

elsif updating then

if :new.sal>:old.sal*1.1 then

raise_application_error(-20002,'老用户涨工资不能超过原来工资的10%');

end if;

end;

后置触发器：after

表格数据的实时备份

create or replace trigger backup_dept

after insert or update or delete on dept

for each row

begin

if inserting then

insert into dept_old values(:new.deptno,:new.dname,:new.loc);

elsif updating then

update dept_old set deptno=:new.deptno,dname=:new.dname,loc=:new.loc

where deptno=:old.deptno;

elsif deleting then

delete from dept_old where deptno=:old.deptno;

end if;

end;

表格数据操作的日志记录

create table dept_log(

deptno number,

dname varchar2(50),

loc varchar2(50),

operte_user varchar2(50),

operte_time date,

operte varchar2(100)

);

create or replace trigger insert_dept_log

after insert or update or delete on dept

for each row

begin

if inserting then

insert into dept_log values(:new.deptno,:new.dname,:new.loc,user,sysdate,'新增数据');

elsif updating then

insert into dept_log values(:old.deptno,:old.dname,:old.loc,user,sysdate,'更新前的数据');

insert into dept_log values(:new.deptno,:new.dname,:new.loc,user,sysdate,'更新后的数据');

elsif deleting then

insert into dept_log values(:old.deptno,:old.dname,:old.loc,user,sysdate,'删除数据');

end if;

end;

create or replace trigger insert_dept_log_trunc

after DDL on database

begin

if ora_dict_obj_name='DEPT_OLD' then

insert into dept_log values(null,null,null,user,sysdate,ora_sysevent);

end if;

end;

包：统一的管理同一个模块下所有的代码的块的一个概念 package

包规范：声明你要打包的那些存储过程和函数长什么样

create or replace package 包的名字

function 函数名字(输入的参数数据类型) return 返回的数据;

procedure 过程的名字(输入的参数 in 数据类型, 输出的参数 out 数据类型);

end 包的名字;

create or replace package pkg_emp

--存储过程，编写一个新增员工的过程

procedure add_emp_info(v_empno in number,v_ename in varchar2,v_job in varchar2,v_mgr in number,

v_hiredate in date,v_sal in number,v_comm in number,v_deptno in number,v_mobile in char);

--函数，查看部门人数

function select_dept_count(v_deptno number) return number;

end pkg_emp;

包体：确定你要打包的那些存储过程和函数的内容

create or replace package body 包的名字

function 函数名字(输入的参数数据类型) return 返回的数据

变量的声明

begin

代码的执行

end;

procedure 过程的名字(输入的参数 in 数据类型, 输出的参数 out 数据类型)

变量的声明

begin

代码的执行

end;

end 包的名字;

create or replace package body pkg_emp

--存储过程，编写一个新增员工的过程

procedure add_emp_info(v_empno in number,v_ename in varchar2,v_job in varchar2,v_mgr in number,

v_hiredate in date,v_sal in number,v_comm in number,v_deptno in number,v_mobile in char)

begin

insert into emp values(v_empno,v_ename,v_job,v_mgr,

v_hiredate,v_sal,v_comm,v_deptno,v_mobile);

end;

--函数，查看部门人数

function select_dept_count(v_deptno number) return number

c number;

begin

select count(1) into c from emp where deptno=v_deptno;

return c;

end;

end pkg_emp;

call pkg_emp.add_emp_info(7777,'LUCY','CLERK',8889,null,1000,100,20,null);

select * from emp;

通过数据库链接，来远程访问数据库：

create public database link 链接的名字

connect to 用户名 identified by "密码"

using '数据库的TNS信息';

create public database link teacher_db

connect to zz identified by "123456"

using '(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = PC-202004101937)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)';

create table zq_customer_info as select * from zq_customer_info@teacher_db;

create table xt_customer_info as select * from xt_customer_info@teacher_db;

select* 和select列名的区别

结论：两者差别几乎可忽略。所以查询所有字段（或者大多数字段）的时候，大可 select * 来操作。如果某些不需要的字段数据量特别大，还是写清楚字段比较好，因为这样可以减少网络传输。

（1）减少数据的负担。

SELECT *，需要数据库先 Query Table Metadata For Columns，一定程度上为数据库增加了负担（影响网络传输的性能），但是实际上，两者效率差别不大。

（2）考虑到今后的扩展性。

因为程序里面你需要使用到的列毕竟是确定的， SELECT * 只是减少了一句 SQL String 的长度，并不能减少其他地方的代码。

（3）索引问题

select abc from table; 和 select * from table;

在 abc 字段有索引的情况下，mysql 是可以不用读 data，直接使用 index 里面的值就返回结果的。但是一旦用了 select *，就会有其他列需要读取，这时在读完 index 以后还需要去读 data 才会返回结果，这样就造成了额外的性能开销。

综上：除平时练习使用，其他情况都不推荐使用 SELECT * FROM XXX 。

oracle补位函数lpad与rpad

遇到一个需要补位的问题，蛋疼的自己写了一个半天调试不通，后来才发现原来就有。。瞎忙

LPAD是在字符串左边用设定的字符补满指定位数，RPAD则是右边

如

LPAD('1234',8,'0') 的结果是00001234

RPAD('1234',8,'0') 的结果则是12340000