这篇博客详细介绍了HiveQL的数据操作和查询，包括语句命令组成、查询结构、虚拟表、聚合函数、操作符、NULL处理、去重、IN操作、LIKE模糊查询、集合运算、子查询和连接查询。同时，还涵盖了Hive中的表操作和常用函数，如数字函数、日期函数和字符函数等。
第一章:sql数据操作和查询
– 1.语句命令组成：
1. 数据定义语言（ddl）,包括create（创建）命令、alter（修改）命令、drop（删除）命令等。 -- 主要针对对象的结构
2. 数据操纵语言（dml）,包括insert（插入）命令、update（更新）命令、delete（删除）命令等。 -- 主要针对表
3. 数据查询语言（dql）,包括基本查询语句、order by子句、group by子句等。
4. 事务控制语言（tcl）,包括commit（提交）命令、rollback（回滚）命令。
5. 数据控制语言（dcl）,grant（授权）命令、revoke（撤销）命令。
– 2.查询结构
select *|列名|表达式 from 表名 where 条件 group by 列名 having 条件 order by 列名 [asc/desc] -- * 表示所有的列名称; asc 升序(默认),desc 降序
– 3.查询顺序
select 字段或表达式（多个字段用逗号隔开） -- 5
from 表（视图或者数据集） -- 1
where 字段的限定（多个限定用or或者and隔开,后面不能跟聚合函数） -- 2
group by 字段或表达式（多个字段用逗号隔开） -- 3
having 表达式(前面必须有group by,多个表达式用or或者and隔开） -- 4
order by 字段或表达式（多个字段用逗号隔开,如col1 asc, col2 desc或者1 asc,2 desc） -- 6
– 虚拟表
select 10/2 from dual;
select 1 as 数字1, '1' as 字符1 from dual;
-- select 中有*时候,并且还有其它字段或表达式,必需在*号前加表名.或表别名.
select sysdate as 当前日期,t.* from student_info t; -- 列别名可以加as , 表的别名不能加as
– 4.聚合函数
聚合函数：聚合函数同时可以对多行数据进行操作,并返回一个结果。
聚合函数与group by 使用（字段需要分组,聚合函数必须要跟group by 联用）
注意：select 中除了聚合函数包裹的内容,其它字段必须包含于group by 后面的字段,select 只是选取聚合后的字段和值
avg 平均值
sum 求和
min、max 最小值、最大值
count 统计个数
-- 计算员工表的总体平均工资、总工资、总人数、工龄最大和最小的入职日期
select avg(sal) as 平均工资,
sum(sal) as 总工资,
count(empno) as 总人数,
min(hiredate) as 最早入职日期,
max(hiredate) as 最晚入职日期
from emp;
-- 每个部门的平均工资、总工资、总人数、工龄最大和最小的入职日期
select deptno,
avg(sal) as 平均工资,
sum(sal) as 总工资,
count(empno) as 总人数,
min(hiredate) as 最早入职日期,
max(hiredate) as 最晚入职日期
from emp
group by deptno;
-- 按照部门编号和工作类型分组统计人数
select deptno,job,
count(empno)
from emp
group by deptno,job;
-- count对列和对*的区别（count对列时,有空值的列不会计数）
select count(*), --15
count(1), --15
count(1000), --15
count(comm), --4 – 不计算空值
count(job), --15
count(distinct job) – 6 – 去重
from emp;
– 5.操作符
算术运算,关系运算,和逻辑运算
(1)算术运算: +、-、*、/
select e.*, (nvl(e.sal,0)+nvl(e.comm,0))*12 as 年薪
from emp e;
(2)关系运算: =、!=、<>、>、>=、<、<=
select * from emp where empno=7369;
(3)逻辑运算: not>and>or
select (case when 1=1 or 1=3 and 1=2
then 1
else 2
end) as flag
from dual; -- 1
– 6.null操作（’’,null）
在查询条件中null值用is null作条件,非null值用 is not null做条件
空值的一些特性：
1、空值跟任何值进行算术运算,得到的结果都为空值
2、空值跟任何值进行关系运算,得到的结果都为不成立
3、空值不参与任何聚合运算
4、排序的时候,空值永远是最大的
-- 查询奖金为空
select * from emp where comm is null;
-- 1
select e.*, comm+100, sal + comm from emp e;
-- 2
select * from emp where comm<>0;
-- 3
select count(comm) from emp;
-- 4
select * from emp order by comm asc;
– 7.去重（distinct, group by, rowid）
-- 查看部门编号（去重）
select distinct deptno from emp;
select deptno from emp group by deptno;
-- distinct 只能出现在最前,不能一个字段去重一个字段不去重
select distinct job, deptno from emp;
select job, deptno from emp group by job, deptno;
-- 错误1
select job,
distinct deptno
from emp;
-- 错误2
select distinct job,
distinct deptno
from emp;
– 8.in 操作
查询出工作职责是'salesman'或者'president'或者'analyst'的员工信息。
select ename,job from emp where job = 'salesman' or job = 'president' or job = 'analyst';
select ename,job from emp where job in ('salesman','president','analyst');
select ename,job from emp where job = any('salesman','president','analyst'); -- 不建议这种写法
– 9.between…and…（包含边界从小到大顺序）
查询列值包含在指定区间内的行,包含边界。
查询工资大于等于1500且小于等于2000的员工信息。
select *
from emp
where sal >=1500 and sal <=2000;
select *
from emp
where sal between 1500 and 2000;
查询工资在1500到2000之间的员工信息
select *
from emp
where sal >1500 and sal <2000;
-- 查询不到值
select *
from emp
where sal between 2000 and 1500;
– 10.like模糊查询（对字符串）
字符匹配操作可以使用通配符'%'和'_':
%：代表任意个字符。
_：代表任意一个字符。
select * from student_info where sname like '李_';
select * from student_info where sname like '李_%';
select * from student_info where sname like '李__';
– 11.集合运算
交集：intersect -- 两者共有部分
并集（去重）： union
并集（不去重）：union all
补集：minus -- 前者有后者无
当使用集合操作的时候,要注意：
1.查询所返回的列数以及列的类型必须匹配,列名可以不同。
2.只有union all不会去重。其他三个都需要排序后去重,性能比较差
例题：
1.求员工表和部门表中的共有的部门编号（去重）
select deptno
from emp
intersect
select deptno
from dept;
2.求员工表或者部门表中所包含的部门编号（不去重）
select deptno
from emp
union all
select deptno
from dept;
3.求员工表或者部门表中所包含的部门编号（去重）
select deptno
from emp
union
select deptno
from dept;
4.求部门表中不在员工表中的部门编号
select deptno
from dept
minus
select deptno
from emp;
--
select deptno
from emp
minus
select deptno
from dept;
– 12.子查询
子查询在select、update、delete语句内部可以出现select语句。
1.单行子查询：不向外部返回结果,或者只返回一行结果。
2.多行子查询：向外部返回零行、一行或者多行结果。
-- 单行查询
select * from emp where empno=7369;
-- 多行查询
select * from emp where deptno=10;
-- 查询工资和7369相同的员工信息
select * from emp where sal = (select sal from emp where empno=7369); -- 单行结果"=",并且字段要对应
-- 查询工资和10号部门员工工资相同的其它部门的员工信息
select * from emp where sal in (select sal from emp where deptno = 10) and deptno <> 10; -- 单行结果"in",并且字段要对应
select * from emp where sal = any(select sal from emp where deptno = 10) and deptno <> 10; -- 单行结果"in",并且字段要对应
-- 查询emp表中每个部门的最低工资的员工信息（尝试练习）
select e.*
from emp e, (select deptno, min(sal) as min_sal from emp group by deptno) a -- 子查询作为临时表关联
where e.deptno = a.deptno
and e.sal = a.min_sal;
select * from emp where (deptno,sal) in (select deptno, min(sal) as min_sal from emp group by deptno); -- 可以多列对应
-- 查询员工名称及其直接上级的名称
select e1.empno 员工编号,
e1.ename 员工名称,
e1.mgr 上级员工编号,
(select e.ename from emp e where e.empno = e1.mgr) as 上级员工名称 -- 子查询作为查询内容
from emp e1;
-- 更新所有员工工资和7369工资相同
update emp set sal = (select sal from emp where empno=7369);
-- 删除和7369相同工资的员工数据
delete from emp where sal = (select sal from emp where empno=7369);
– 13.连接查询（重点）
包括内联接(inner join 1种)和外联接(outer join 3种)
（1）内连接(inner join)：inner可省略
内连接写法1（标准写法）
select
from 表名1
join 表名2
on 表名1.字段1=表名2.字段1
and 表名1.字段2=表名2.字段2;
内连接写法2（oracle写法）
select
from 表名1,表名2
where 表名1.字段1=表名2.字段1
and 表名1.字段2=表名2.字段2;
-- 查询员工信息及其部门信息
select *
from dept e
join emp d
on e.deptno = d.deptno;
-- oracle
select *
from emp e, dept d
where e.deptno=d.deptno;
（2）左外连接(left outer join): outer可省略
left join 跟表的前后顺序有关
左外连接写法1（标准写法）
select
from 表名1 -- 主表
left join 表名2 -- 从表
on 表名1.字段1=表名2.字段1
and 表名1.字段2=表名2.字段2;
左外连接写法2（oracle写法）
select
from 表名1,表名2
where 表名1.字段=表名2.字段(+);
-- 查询部门名称为'sales'部门的信息及其对应的员工信息
select *
from dept d
left join emp e
on d.deptno=e.deptno;
where d.dname='sales';
注意：
select *
from dept d
left join emp e
on d.deptno=e.deptno
and d.dname='sales';
-- oracle
select *
from dept d, emp e
where d.deptno=e.deptno(+)
and d.dname='sales';
（3）右外连接(right outer join): outer可省略
（4）全外连接(full outer join): outer可省略
-- full join
select e.*,d.*
from emp e
full join dept d
on e.deptno=d.deptno;
-- 错的
select e.*,d.*
from emp e, dept d
where e.deptno(+)=d.deptno(+);
第二章: 表操作和常用函数
创建表方式：
（1）语法结构：直接创建表
create table 表名
(列名1 数据类型 [,
列名2 数据类型]...
);
数据类型：
1.数字型 number -- 默认最大为38位长度整数
number(10) -- 最大长度为10的整数
number(8,2) -- 整数最大长度为6位,小数长度为2位
2.字符型 char -- 默认1位长度的字符
char(10) -- 最大长度为10的字符,输入值不满10位,后面补空格
varchar2(10) -- 最大长度为10的字符,输入值不满10位,后面不会填充
3.日期型 date -- 后面不要加括号(可以到时分秒)
timestamp
-- 普通创建
create table t_course (
cno number(10) default 0, -- 给默认值
cname varchar2(20),
cdate date
);
（2）语法结构：根据结果集创建表
create table 表名 as select 语句;
-- 间接创建
create table t_emp_cp as select * from emp; -- 拷贝表结构和数据
create table t_emp_cp1 as select * from emp where 1=2; -- 拷贝表结构
（3）表结构更改
1.字段名称更改
alter table t_course rename column cno to cno1;
2.字段类型更改
alter table t_course modify cno1 number(20);
3.增加字段
alter table t_course add cctv varchar2(10);
4.删除字段
alter table t_course drop column cctv;
5.表名更改
alter table t_course rename to t_course1216;
6.删除表
drop table t_course1216;
（4）表数据更改
-- insert into
insert into t_course(cno,cname,cdate) values (1,'语文',sysdate);
insert into t_course values (2,'数学',date'2019-11-15');
insert into t_course(cname,cdate) values ('英语',to_date('2019-11-15','yyyy-mm-dd'));
-- update
update t_course set cno=3 where cno=0;
update t_course set cname='政治',cdate=date'2019-11-16' where cno=3;
-- delete
delete from t_course where cno=3; -- 逐行清除某些条件下的数据
-- truncate
truncate table t_course; -- 直接清空整张表
-- 4.oracle单行函数
常用的单行函数有：
数字函数：对数字进行计算,返回一个数字。
日期函数：对日期和时间进行处理。
字符函数：对字符串操作。
（1）数字函数
abs(x) x绝对值 select abs(-2) from dual; – 2
mod(x,y) x除以y的余数 select mod(7,3) from dual; – 1
power(x,y) x的y次幂 select power(2,3) from dual; – 8
round(x[,y]) x在第y位四舍五入(默认四舍五入到整数) select round(2.989),round(2.989,2),round(5.989,-1) from dual; – 3, 2.99, 10
trunc(x[,y]) x在第y位截断(默认截断到整数) select trunc(2.989),trunc(2.989,2),trunc(5.989,-1) from dual; – 2, 2.98, 0
ceil(x) 向上取整 select ceil(2.13), ceil(2.00),ceil(-2.13) from dual; – 3, 2, -2
floor(x) 向下取整 select floor(2.89),floor(3.00),floor(-2.89) from dual; – 2, 3,-3
（2）日期函数
add_months(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期。d表示日期,n表示要加的月数（n可以为负值）
select add_months(sysdate, 1),
add_months(date’2019-08-30’, -6),
add_months(date’2020-08-30’, -6)
from dual;
last_day(d),返回指定日期当月的最后一天
select last_day(sysdate),last_day(date'2019-08-30') from dual;
-- trunc配合日期使用
trunc(d[,fmt])和round(d[,fmt])对日期处理：
trunc(d[,fmt])：
fmt: 'ddd' 截取到当月的当天（默认格式）
fmt: 'dd' 截取到当月的当天
fmt: 'd' 截取到当周的第一天
fmt: 'mm' 截取到当月的第一天
fmt: 'q' 截取到当季度的第一天
fmt: 'y' 截取到当年的第一天
select sysdate,
trunc(sysdate),
trunc(sysdate,'dd'),
trunc(sysdate,'ddd'),
trunc(sysdate,'d'), -- 周日为本周第一天
trunc(sysdate,'mm'), -- 可以写成'month'
trunc(sysdate,'q'),
trunc(sysdate,'y') -- 可以写成'year'
from dual;
（3）字符函数
ascii(x) 返回字符x的ascii码。
concat(x,y) 连接字符串x和y。（|| 也可以连接字符串）
length(x) 返回x的长度。
lengthb(x) 返回x的字节长度
lower(x) x转换为小写。
upper(x) x转换为大写。
replace(x,old,new) 替换字符 – 在x中查找old,并替换为new。
-------------------
ltrim(x[,trim_str]) 左边截去字符 – 从x左边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
rtrim(x[,trim_str]) 右边截去字符 – 从x右边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
trim([trim_str from] x)
两边截去字符 – 从x两边开始截去其中的trim_str字符串,直到不是为止,缺省则默认截去的为空格。
instr(x, str[,start] [,n]) 定位字符位置 – 在x中查找str,可以指定从start位开始,第n次出现,省去的参数默认为1。
substr(x,start[,length]) 截取某段字符 – 在x中从start位开始,截取length个字符,缺省length,默认到结尾。
-- 示例
select ascii('a') from dual; -- 65
--
select '0722' || 'bi' || '大数据' from dual;
select concat(concat('0722','bi'),'大数据') from dual;
select concat(concat('0722','bi'), (select '大数据' from dual)) from dual;
--
select lower('ab2'), upper('ab2'), replace('a2b3a','a','520') from dual;
select length('a ub好'), lengthb('中国平安'), lengthb('abcd') from dual;
--
select translate('a你好djl','ad','34') from dual;
select translate('a你好djl','adj','34') from dual;
------------------
select ltrim(' a 1 '), rtrim(' a 1 '), trim(' a 1 ')
from dual; -- 默认截去空格
select ltrim(' a 1 ', 'a'), rtrim('a 1', 'a')
from dual; -- 截去'a'（可以截去单个或多个字符）
select trim('a' from 'a 1 ') from dual; -- 只能截去单个字符
select trim('a1' from 'a 1 ') from dual; -- 报错
--
select instr('0722bi大数据bi520', 'bi') from dual; -- 从'0722bi大数据'的第1个字符开始查找'bi'第1次出现的位置(5)
select instr('0722bi大数据bi520', 'bi',6) from dual; -- 从'0722bi大数据'的第6个字符开始查找'bi'第1次出现的位置(10)
select instr('0722bi大数据bi520', 'bi',5, 2) from dual; -- 从'0722bi大数据'的第5个字符开始查找'bi'第2次出现的位置(10)
select instr('0722bi大数据bi520', 'bi',6, 2) from dual; -- 从'0722bi大数据'的第6个字符开始查找'bi'第2次出现的位置(0)
--
select substr('ra badf', 2) from dual; -- 从第2个字符截取到最后一个字符
select substr('ra badf', 2, 3) from dual; -- 从第2个字符截取3个字符
-- 5.转换函数
(1)to_char(d|n[,fmt])
select 12, to_char(12),to_char(date'2019-07-08','yyyymmdd') from dual;
-- fmt 格式
select to_char(date'2019-08-30','yyyy'), -- '2019'
to_char(date'2019-08-30','yyyymm'), -- '201908'
to_char(date'2019-08-30','ww'), -- '35' 第35周
to_char(date'2019-08-30','iw'), -- '35' 第35周（自然周）
to_char(date'2019-08-30','q'), -- '3' 第3季度
to_char(date'2019-08-30','mm'), -- '08'
to_char(date'2019-08-30','dd'), -- '30'
to_char(date'2019-08-30','d') -- '6' 当周的第几天（星期天为第一天）
from dual;
-- 数字格式显示（后面格式长度要大于前面的值,如果不加fm或在前面填充空格）
select to_char(243677865674,'fm999,999,999,999,990.00') from dual;
(2)to_number(x[,fmt])
x 里面必须全部为数字,如果有空格必须在最前或最后
select to_number('1 2'), -- 不行
to_number(' 12'), -- 可以 12
to_number('12 '), -- 可以 12
to_number(' 12 '), -- 可以 12
to_number('12a') -- 不行
from dual;
-- 日期不能直接转数字
select to_number(date'2019-08-22') from dual; -- 错误
select to_number(to_date('20190822','yyyymmdd')) from dual; -- 错误
select to_number(to_char(date'2019-08-22','yyyymmdd')) from dual;
(3)to_date(x [,fmt])
select to_date('2019-07-08','yyyy-mm-dd'),
to_date(20190708,'yyyy-mm-dd')
from dual;
注意：字符或数值中的年月日必须是日期范围内的值
select to_date('2019-13-08','yyyy-mm-dd'), -- 月份不对
to_date(20190732,'yyyy-mm-dd') -- 天数不对
from dual;
-- 转换格式前后对应
select to_date('2019-07-08','yyyymmdd'), -- 错误
to_date(20190708,'yyyy-mm-dd') -- 可以
from dual;
-- 6.空值转换函数
nvl （列,默认值） 如果列值为null,则使用默认值表示
nvl2（列,返回值1,返回值2）如果列值不为null,返回结果1;如果列值为null,返回结果2
--
select nvl(null,0), -- 空转0
nvl(null,100), -- 空转100
nvl('', 99), -- 空字符转99
nvl(10, 100), -- 非空不会转
nvl('ad',77) -- 非空不会转
from dual;
--
select nvl2(null, 0, 1), -- 空转1
nvl2('', 99, 2), -- 空字符转2
nvl2(10, 3, 100), -- 非空转3
nvl2('ad', 'ad', 7) -- 非空转'ad'
from dual;
-- 7.条件转换函数（从前往后判断,满足前面条件,执行对应操作后退出）
decode（列|值,判断值1,返回值1,判断值2,返回值2,...,默认值） -- 默认值可以给可以不给,不给的话,默认为空
case when 条件1 then 返回值1 [when 条件2 then 返回值2 ...] else 默认值 end
区别：decode 只能对单个字段进行等值判断
case when 可以对多个字段进行任意条件判断
-- decode对值
select decode(12, -- 原始值
1, 2,
12, 10), -- 10
decode(12,
1, 2,
13, 10), -- 空
decode(12,
1, 2,
13, 10,
7), -- 给定默认值7
decode(12,
1, 2,
12, 10, -- 找到后就返回值,然后退出,不会向下找
12, 88,
7) -- 给定默认值7
from dual;
-- decode对列（对emp表中的奖金为空的转换为100,不为空的转换为88）
select comm,
decode(comm,
null, 100,
88)
from emp;
练习：将部门名称进行如下更改并展示部门信息
accounting 会计部
research 研究部
sales 销售部
operations 操作部
select d.deptno,
decode(dname,
'accounting','会计部',
'research','研究部',
'sales','销售部',
'operations','操作部') as 部门,
d.loc
from dept d;
-- case when(对emp表中的奖金为空的转换为100,不为空的转换为88)
select comm,
(case when comm is null then 100
else 88
end) as comm_1
from emp;
-- 部门转换
select dept.*,
(case
when dname = 'accounting' then '会计部'
when dname = 'research' then '研究部'
when dname = 'sales' then '销售部'
else '操作部'
end) as 部门
from dept;
-- 8.exists 函数
exists(查询结果集)：查询结果集有记录则成立,否则不成立
not exists(查询结果集)：与exists相反
列出有员工的部门信息
select distinct d.dname
from dept d
join emp e
on d.deptno=e.deptno;
select d.*
from dept d
where deptno in (select deptno from emp);
-- exists
select d.dname
from dept d
where exists (select 1 from emp e where e.deptno=d.deptno);
-- 空值和无结果集的区别
select d.dname
from dept d
where exists (select null from dual);
-- 9.伪列
rowid 插入数据的时候生成,记录的是该行的物理地址(用作去重)
rownum 查询数据的时候生成,返回的是序号(用作分页)
select e.*, e.rowid, rownum -- rownum是变化的值,不能指定到某个表
from emp e;
select e.rowid,d.rowid,rownum, e.*,d.*
from emp e
join dept d
on e.deptno=d.deptno;
-- rownum 查询只能是小于或小于等于某个值（不能直接等于或者大于或者不是从1开始的某个区间段）
select e.*,rowid, rownum
from emp e
where rownum<=10;
抽取排在第7到第10行的数据
-- 1.补集
select e.*, rownum
from emp e
where rownum <= 10
minus
select e.*, rownum
from emp e
where rownum <= 6;
-- 2.子查询
select t.*
from (
select e.*, rownum as rn -- 一定要有别名
from emp e
where rownum <= 10) t
where t.rn>=7;
-- rowid
删除重复数据,相同数据只保留一条
delete from 表名 别名
where rowid not in (select min(rowid) from 表名 别名 group by 列名);
-- 从员工表找出所有部门编号（字段值进行分组找该字段每一组的一个值）
select distinct deptno
from emp;
select deptno
from emp
group by deptno;
-- 每个部门的员工数据只保留一条（字段值进行分组保留每一组的一条完整信息）
delete from emp
where rowid not in (select min(rowid) from emp group by deptno);
-- 10.临时表
创建oracle临时表,可以有两种类型的临时表：
会话级临时表（1.其它会话查询不到数据 2.本会话关掉后也查询不到数据）
事务级临时表（1.当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断
2.其它会话查询不到数据
3.本会话关掉后也查询不到数据）
1）会话级临时表
create global temporary table table_name
(col1 type1,col2 type2…) on commit preserve rows;
2）事务级临时表
create global temporary table table_name
(col1 type1,col2 type2...) on commit delete rows;
举例：会话（每一个打开的窗口）
create global temporary table student_1
(stu_id number(5),
class_id number(5),
stu_name varchar2(8),
stu_memo varchar2(200)) on commit preserve rows ;
insert into student_1 values(1,2,'a','随便');
commit;
select * from student_1;
举例：事务
create global temporary table t_pay_customer
(id number(5),
pay_mon varchar2(8)
) on commit delete rows;
create global temporary table t_pay_merchant
(id number(5),
get_mon varchar2(8)
) on commit delete rows;
insert into t_pay_customer values(1,100);
select * from t_pay_customer;
commit;
insert into t_pay_merchant values(1,100);
-- 11.表的约束
按照约束用途分类：
1.primary key：主键约束 （非空、唯一） -- 身份证号
2.foreign key：外键约束 （受外部表主键的约束）
3.check： 检查约束 （只能为空或约束的值） -- 性别
4.unique： 唯一约束 （不重复、可多行为空）
5.not null： 非空约束 （不能为空）
-- 建表时候给定约束（系统给定约束名称）
create table t_student_1 (
sno varchar2(18) primary key, -- 主键
cno varchar2(60) references t_course_1(cno), -- 外键
score number(4,1) not null,
sex varchar2(200) check(sex='男' or sex='女'),
tel number(11) unique
);
（1）primary key （主键只有一个, 但是可以对多个字段组合建立一个主键）
create table t_china_id (
id_no varchar2(18),
id_name varchar2(60),
birth_day date,
address varchar2(200),
tel number(11),
constraint pr_id_no primary key(id_no)
);
insert into t_china_id(id_no) values('1');
insert into t_china_id(id_no) values('1'); -- 重复值报错
insert into t_china_id(id_no) values(''); -- 空值报错
--
create table t_china_id_1 (
id_no varchar2(18),
id_name varchar2(60),
birth_day date,
address varchar2(200),
tel number(11),
constraint pr_id_name primary key(id_no,id_name)
);
insert into t_china_id_1(id_no) values('1'); -- 主键字段id_name为空报错
insert into t_china_id_1(id_no,id_name) values('1','a');
insert into t_china_id_1(id_no,id_name) values('1','b');
（2）foreign key （外部表要先建立主键,外键约束的字段值只能包含在外部表主键值中）
– 父项表
create table dept_1
(deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_deptno_1 primary key (deptno)
);
– 子项表
create table emp_1(
empno number(4,0),
ename varchar2(10),
deptno number(2,0),
constraint pk_empno_1 primary key (empno),
constraint fk_deptno_1 foreign key (deptno) references dept_1(deptno)
);
insert into emp_1(empno,ename,deptno) values (1,'a',null); -- 可以插入数据
insert into emp_1(empno,ename,deptno) values (2,'b',10); -- 报错,外部表主键没有10的值
insert into dept_1(deptno,dname,loc) values (10,'aa','bb');
insert into emp_1(empno,ename,deptno) values (2,'b',10); -- 可以插入数据
delete from dept_1 where deptno=10; -- 报错,字表已经引用外部表10号部门的值
（3）check
create table t_china_id_2 (
id_no varchar2(18) primary key,
id_name varchar2(60),
sex varchar2(4),
tel number(11),
constraint ck_sex check(sex=‘男’ or sex=‘女’)
);
insert into t_china_id_2(id_no,sex) values(‘1’,’’); – 可以为空
insert into t_china_id_2(id_no,sex) values(‘2’,‘未知’); – 不能为检查约束外的值
insert into t_china_id_2(id_no,sex) values(‘3’,‘男’);
（3）unique
create table t_china_id_3 (
id_no varchar2(18) primary key,
id_name varchar2(60),
sex varchar2(4),
tel number(11),
constraint uq_tel unique(tel)
);
insert into t_china_id_3(id_no,tel) values(‘1’,null); – 可以为空
insert into t_china_id_3(id_no,tel) values(‘2’,null); – 可以为空
insert into t_china_id_3(id_no,tel) values(‘3’,1);
insert into t_china_id_3(id_no,tel) values(‘4’,1); – 重复值报错
（4）not null
（5）约束组合使用（主键不能和唯一约束组合）
create table t_china_id_4 (
id_no varchar2(18) primary key check(length(id_no)=18),
id_name varchar2(60)
);
insert into t_china_id_4(id_no,id_name) values(‘1’,‘张三’); – 主键中的检查约束不满足18位, 报错
insert into t_china_id_4(id_no,id_name) values(‘123456789876543210’,‘张三’);
-- alter table命令添加约束
alter table 表名 add constraint 约束名 primary key(列名1[,列名2...])
alter table 主表名 add constraint 约束名 foreign key(列名1[,列名2...]) references 从表名(列名1[,列名2...])
alter table 表名 add constraint 约束名 check(条件)
alter table 表名 add constraint 约束名 unique(列名)
alter table 表名 modify 列名 not null
alter table 表名 drop constraint 约束名
-- 有数据不满足约束的时候,不能创建约束
create table t_test_1 (
sno varchar2(10),
sname varchar2(20)
);
insert into t_test_1(sno, sname) values ('1','张三');
insert into t_test_1(sno, sname) values ('1','李四');
alter table t_test_1 add constraint pk_t_test_1_sno primary key (sno); -- 报错
-- 11.分析函数
它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值
语法格式：分析函数语法（<> 内的内容可以选择性省略)
function_name(<参数>,…) over (<partition by 表达式,…> <order by 表达式 <asc desc>> )
(1)、函数为聚合函数（avg,sum,count)情况下：
-- 按照每个部门的工资从低到高排序,计算每个部门的累计工资
select e.*,
sum(sal) over (partition by deptno order by sal)
from emp e;
select e.*,
sum(sal) over (partition by deptno order by sal,empno)
from emp e;
(2)、函数为排序函数（row_number(),rank(),dense_rank())情况下：
①row_number：
row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②rank：
rank函数返回一个唯一的值,当碰到相同的数据时,此时所有相同数据的排名是一样的,
同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
③dense_rank：
dense_rank函数返回一个唯一的值,当碰到相同数据时,此时所有相同数据的排名都是一样的。
同时会在最后一条相同记录和下一条不同记录的排名之间不空出排名。
-- 按照员工部门分组, 给出工资从低到高的排名（排名不并列）
select e.*,
row_number() over (partition by deptno order by sal)
from emp e;
（3）、函数为位移函数（lead(列,参数), lag(列,参数)
select e.*,
lag(e.sal,1) over(order by e.sal) as 工资下移一位,
lead(e.sal,1) over(order by e.sal) as 工资上移一位
from emp e;
-- 12.行列转换
（1）行转列
有一张表s,记录了某公司每个季度的销售额,如下
y q amt
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300
y q1 q2 q3 q4
2015 100 110 130 100
2016 200 150 100 300
with t as (select deptno as 部门, count(empno) as 人数 from emp group by deptno)
select
max(decode(部门,10,人数)) as “10号部门”,
max(decode(部门,20,人数)) as “20号部门”,
max(decode(部门,30,人数)) as “30号部门”,
max(decode(部门,50,人数)) as “50号部门”
from t;
select
y,
max(case when q=1 then amt end) as q1,
max(case when q=2 then amt end) as q2,
max(case when q=3 then amt end) as q3,
max(case when q=4 then amt end) as q4
from t_y_q_amt t
group by y;
（2）列转行
/*
-- 将原表拆分4个季度的值,给上季度的标识,然后合并
y q1 q_flag
2015 100 1
2016 200 1
y q2 q_flag
2015 110 2
2016 150 2
y q3 q_flag
2015 130 3
2016 100 3
y q4 q_flag
2015 100 4
2016 300 4
*/
select y, 1 as q, q1 as amt
from t_y_q_amt_1 t1
union all
select y, 2 as q, q2 as amt
from t_y_q_amt_1 t1
union all
select y, 3 as q, q3 as amt
from t_y_q_amt_1 t1
union all
select y, 4 as q, q4 as amt
from t_y_q_amt_1 t1;
第4章 表空间、数据库对象
1.表空间
（1）系统表空间 system
（2）临时表空间 temp
（3）用户表空间 users
（4）undo表空间 undotbs1
（5）样例表空间 example
（6）系统副本空间 sysaux
2.同义词
私有同义词：拥有create synonym权限的用户（包括非管理员用户）即可创建私有同义词,创建的私有同义词只能由当前用户使用。
公有同义词：系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。
创建同义词
create [or replace] [public] synonym [schema.]synonym_name
for [schema.]object_name
-- sys 用户
create or replace public synonym emp_copy for scott.emp;
-- scott用户
select * from emp_copy;
3.序列
序列(sequence)是用来生成连续的整数数据的对象。
创建序列
create sequence sequence_name
[start with num] -- 从某一个整数开始,升序默认值是1,降序默认值是-1
[increment by increment] -- 增长数
[maxvalue num|nomaxvalue] -- 最大值
[minvalue num|nominvalue] -- 最小值
[cycle|nocycle] -- 表示如果升序达到最大值后,从最小值重新开始；如果是降序序列,达到最小值后,从最大值重新开始
[cache num|nocache] -- 预先在内存中生成序列号/不预先在内存中生成序列号
-- 最简单创建
create sequence s_1;
序列使用
--访问下一个值（初始创建后要先定义）
select s_1.nextval from dual;
--访问当前值
select s_1.currval from dual;
-- 生成序列号
create sequence s_2
start with 2
minvalue 1
maxvalue 10
increment by 2
cycle
cache 2
序列修改和删除
--序列修改
alter sequence s_1
maxvalue 10000
minvalue -300
--删除序列
drop sequence s_1;
4.视图
视图（view）实际上是一张或者多张表上的预定义查询,这些表称为基表。
从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的select…from即可。
创建视图
create [or replace] [{force|noforce}] view view_name
as
select查询
[with read only]
视图
--创建视图
create or replace view v_emp_dept
as
select e.empno, e.ename, e.job, e.hiredate, e.deptno, d.dname
from emp e
join dept d
on e.deptno = d.deptno
with read only
-- 更改基础表
delete from emp where empno<>7369
--通过视图查询
select * from v_emp_dept;
5、索引
前提：表数据量比较大的时候,查询比较慢
1.如果表中的某些字段经常被查询或者表之间的关联,以及作为查询的条件出现时,就应该考虑为该列创建索引。
2.有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时,索引就非常有用。
（1）索引种类
唯一索引
1、何时创建：当某列任意两行的值都不相同
2、当建立primary key(主键)或者unique constraint(唯一约束)时,唯一索引将被自动建立
组合索引
1、何时创建：当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建
2、组合索引中列的顺序是任意的,也无需相邻。但是建议将最频繁访问的列放在列表的最前面
基于函数的索引
1、何时创建：在where条件语句中包含函数或者表达式时
2、函数包括：算数表达式、pl/sql函数、程序包函数、sql函数、用户自定义函数。
/*
位图索引
反向键索引
键压缩索引
索引组织表(iot)
分区索引
*/
创建索引
create [unique] index index_name on table_name(column_name[,column_name…])
-- 唯一索引
create unique index u_inx_emp_bak_empno on emp_bak(empno);
-- 组合索引(字段顺序有关）
create index inx_emp_bak_deptno_job on emp_bak(deptno,job);
-- 基于函数的索引
create index inx_emp_bak_ename on emp_bak(substr(ename,-1,1));
索引失效
1.隐士转换
select * from emp_bak where empno = '7934';
-- 改 select * from emp_bak where empno = 7934;
2.字段引用函数
select * from emp_bak where to_char(empno) = '7934';
3.null 值判断
select * from emp_bak where empno is not null;
-- 改select * from emp_bak where empno>0 or empno<=0;
4.索引列进行运算
select * from emp_bak where empno + 10 = 7944;
-- 改select * from emp_bak where empno = 7944 -10;
select * from emp_bak where empno <> 7944;
-- 改
select * from emp_bak where empno > 7944
union all
select * from emp_bak where empno < 7944;
5.like首字母未知（末尾字母知道）
select * from emp_bak where ename like '%s';
-- 改select * from emp_bak where substr(ename,-1,1) ='s';
6.组合索引(字段顺序有关）
select * from emp_bak where deptno=10;
select * from emp_bak where deptno=10 and job='salesman';
select * from emp_bak where job='salesman'; -- 索引失效
6.表分区
前提：表数据量比较大的时候,查询比较慢
优点：1.提高查询效率 2.增强可用性 3.维护方便（只对某些有问题分区数据维护,不用对整张表维护）
缺点：2.分区表需要维护（维护创建的分区）
表分区的几种类型及操作方法
1.范围分区：range
2.列表分区：list
3.散列（哈希）分区：hash
4.组合分区
范围分区：
按入职日期进行范围分区
create table myemp
(
empno number(4) primary key,
ename varchar2(10),
hiredate date,
deptno number(7)
)
partition by range (hiredate)
(
partition part1 values less than (to_date('1981-1-1','yyyy/mm/dd')), --①
partition part2 values less than (to_date('1982-1-1','yyyy/mm/dd')),
partition part3 values less than (to_date('1983-1-1','yyyy/mm/dd')),
partition part4 values less than (to_date('1988-1-1','yyyy/mm/dd')),
partition part5 values less than (maxvalue) --默认最大
);
-- select * from user_tab_partitions where table_name='myemp'; 查看分区表情况
insert into myemp(empno,
ename,
hiredate,
deptno)
values(1,'张三',date'1980-1-1',10);
insert into myemp(empno,
ename,
hiredate,
deptno)
values(2,'李四',date'1981-10-02',20);
insert into myemp(empno,
ename,
hiredate,
deptno)
values(3,'王五',date'1982-11-03',30);
insert into myemp(empno,
ename,
hiredate,
deptno)
values(4,'李蕾',date'1983-07-08',40);
insert into myemp(empno,
ename,
hiredate,
deptno)
values(5,'李华',date'1987-09-09',40);
insert into myemp(empno,
ename,
hiredate,
deptno)
values(6,'赵四',date'1989-11-03',50);
-- 查看分区数据
select * from myemp partition(part5);
列表分区：
该分区的特点是某列的值比较少并且不会经常变动,基于这样的特点我们可以采用列表分区。
按deptno进行list分区
create table myemp2
(
empno number(4) primary key,
ename varchar2(10),
hiredate date,
deptno number(7)
)
partition by list (deptno)
(
partition myemp_deptno_10 values (10) ,
partition myemp_deptno_20 values (20) ,
partition myemp_deptno_30 values (30) ,
partition myemp_deptno_40 values (40)
);
-- select * from user_tab_partitions where table_name='myemp2'; 查看分区表情况
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(1,'张三',date'1980-1-1',10);
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(2,'李四',date'1981-10-02',20);
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(3,'王五',date'1982-11-03',30);
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(4,'李蕾',date'1983-07-08',40);
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(5,'李华',date'1987-09-09',40);
insert into myemp2(empno,
ename,
hiredate,
deptno)
values(6,'赵四',date'1989-11-03',50); -- 没给分区数据无法插入
散列分区/hash 分区:
组合分区：
这种分区是基于两种分区的组合,分区之中的分区被称为子分区。
按入职日期进行范围分区,再按deptno进行list子分区
create table myemp4
(
empno number(4) primary key,
ename varchar2(10),
hiredate date,
deptno number(7,2)
)
partition by range(hiredate) subpartition by list(deptno)
(
partition p1 values less than(to_date('1981-01-01','yyyy-mm-dd'))
(
subpartition p1a values (10),
subpartition p1b values (20),
subpartition p1c values (30),
subpartition p1d values (40)
),
partition p2 values less than (to_date('1982-01-01','yyyy-mm-dd'))
(
subpartition p2a values (10),
subpartition p2b values (20),
subpartition p2c values (30),
subpartition p2d values (40)
),
partition p3 values less than (to_date('1983-01-01','yyyy-mm-dd'))
(
subpartition p3a values (10),
subpartition p3b values (20),
subpartition p3c values (30),
subpartition p3d values (40)
),
partition p4 values less than (to_date('1988-01-01','yyyy-mm-dd'))
(
subpartition p4a values (10),
subpartition p4b values (20),
subpartition p4c values (30),
subpartition p4d values (40)
),
partition p5 values less than (maxvalue)
(
subpartition p5a values (10) ,
subpartition p5b values (20),
subpartition p5c values (30),
subpartition p5d values (40)
)
);
-- select * from user_tab_partitions where table_name='myemp4'; 查看分区表情况
1)查看分区数据（列表分区为例）
select * from myemp2 partition(myemp_deptno_40);
1)添加分区（原有的分区表没有给定默认分区的前提）
alter table myemp2 add partition myemp_deptno_50 values (50);
2)删除分区
alter table myemp2 drop partition myemp_deptno_50;
注意：如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
3)重命名表分区
以下代码将p21更改为p2
alter table myemp2 rename partition myemp_deptno_50 to myemp_deptno_60;
总结：
1.非分区表, 不能直接改为分区表
2.创建了分区表, 给了默认分区, 不能添加其它分区
3.建完的分区表, 数据插入只能是当前分区所能包含的数据
7.增量更新数据
语法格式：merge（不是所有数据库都通用）
merge into 目标表
using (增量)
on (匹配字段)
when matched then update set --update和set之间不需要加表名
when not matched then insert values
--insert和values之间不需要加into 表名
create table dept_bak as select * from dept where deptno in (10,20);
select * from dept_bak for update; -- 修改loc的值
merge into dept_bak d
using (select deptno,dname,loc from dept) t
on (d.deptno=t.deptno)
when matched then update set
-- d.deptno=t.deptno, 一定不能更新关联上的字段
d.dname=t.dname,
d.loc=t.loc
when not matched then insert
(d.deptno,d.dname,d.loc)
values (t.deptno,t.dname,t.loc);
select * from dept;
select * from dept_bak;
merge的灵活应用：
若数据量大时,merge删除比delete好。
merge into emp e
using (select * from emp where deptno = 10) s
on (s.empno = e.empno)
when matched then
update set e.comm = e.comm delete where 1 = 1; --set随便改,后面接delete
8.insert all
-- 多条插入
create table t1(product_id number, product_name varchar2(80),p_month number);
insert into t1 values(111, '苹果',1);
insert into t1 values(222, '橘子',1);
insert into t1 values(333, '香蕉',1);
commit;
create table t2 as select * from t1 where 1=2;
insert all
into t2
values (product_id, product_name,p_month)
into t2
values (product_id, product_name,p_month+1)
into t2
values (product_id, product_name,p_month+2)
into t2
values (product_id, product_name,p_month+3)
select product_id, product_name, p_month
from t1;
commit;
select * from t2 order by product_id, product_name, p_month;
-- 多表插入（分表--大表按照类别插入数据到小表）
create table apple_orders as select * from t1 where 1=2;
create table orange_orders as select * from t1 where 1=2;
create table banana_orders as select * from t1 where 1=2;
insert all
when product_id = 111 then
into apple_orders
when product_id = 222 then
into orange_orders
else
into banana_orders
select product_id, product_name, p_month
from t1;
commit;
select * from apple_orders;
select * from orange_orders;
select * from banana_orders;
第5章 pl/sql程序设计
1.pl/sql介绍
2.pl/sql基础
语法结构：pl/sql块的语法
[declare – 声明部分
–declaration statements]① – 声明变量、常量、游标等（声明的内容可以不在执行部分使用）
begin – 执行部分
–executable statements ② – 对于执行部分不能去定义变量等（使用的变量、常量、游标等必须在声明的内容中）
[exception – 异常处理
–exception statements] ③
end; – 分号不能省略
注意： 每一个命令语句结束后面必须加分号
特殊符号：
:= 赋值 v_cnt := 6; 对变量赋值为6
.. 连续值 1..6 表示1到6的整数
** 求幂 3**2 3的2次方为9
-- 1
begin
dbms_output.put_line('0701bi课程！');
end;
-- 2
declare
v_sql varchar2(100);
begin
v_sql :='0701bi课程！';
dbms_output.put_line(v_sql);
end;
-- 3
declare
v_1 number;
v_2 number;
begin
v_1 := 10;
v_2 := 0;
dbms_output.put_line(v_1/v_2);
exception when zero_divide then
dbms_output.put_line('分母为0');
end;
2.1声明与赋值
声明变量必须指明变量的数据类型,也可以声明变量时对变量初始化,变量声明必须在声明部分。
变量名 数据类型[ :=初始值]
代码演示：声明变量
declare
sname varchar2(20) :='jerry';
sno number := 520;
cname varchar2(10);
begin
cname := 'bi';
dbms_output.put_line(sname||sno||cname); -- 默认是以字符串打印,字符串连接的方式连接多个变量
end;
2.2 声明常量
常量在声明时赋予初值,并且在运行时不允许重新赋值。使用 constant 关键字声明常量。
declare
pi constant number := 3.14;
r number default 3;
area number;
begin
pi := 3; -- 报错,常量不可重新赋值
-- r :=4; -- 变量默认值可以更改
area := pi * r * r;
dbms_output.put_line(area);
end;
2.3 隐式游标赋值
对变量赋值还可以使用select…into 语句从数据库中查询数据对变量进行赋值。
但是查询的结果只能是一行记录,不能是零行或者多行记录。
例题1：打印出emp中员工编号为7369的姓名和工资。
declare
v_ename varchar2(20);
v_sal number(10,2);
begin
select ename,sal
into v_ename,v_sal -- 隐式游标赋值
from emp where empno=7369;
dbms_output.put_line('员工名称：'||v_ename||' 员工工资：'||v_sal); -- 不能直接打印表,只能是单行字段拼接后的字符串形式打印
end;
注意：使用select…into语句对变量赋值,要求查询的结果必须是一行,不能是多行或者没有记录。
2.4 声明属性数据类型
%type： 引用数据库中的某列的数据类型或某个变量的数据类型。
%rowtype：引用数据库中的一行（所有字段）作为数据类型。
例题1：打印出emp中员工编号为7369的姓名和工资。
-- type
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=7369;
dbms_output.put_line(v_ename||'--'||v_sal);
end;
-- rowtype
declare
v_emp emp%rowtype; -- emp的表头一致：字段名和类型
begin
select ename,sal
into v_emp.ename,v_emp.sal -- 注意插入变量表对应的字段中
from emp
where empno=7369;
dbms_output.put_line(v_emp.ename||'--'||v_emp.sal); -- 打印也只能是字段拼接
end;
-- 不建议如下写法
declare
v_emp emp%rowtype;
begin
select *
into v_emp
from emp
where empno=7369;
dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
end;
3.条件控制
3.1 if 语句
-- if-then
if 条件 then
--条件结构体
end if;
declare
v_num1 number:= 10;
v_num2 number:= 30;
begin
if v_num1>=v_num2 then
dbms_output.put_line(v_num1 ||'>=' ||v_num2);
end if;
end;
-- if-then-else
if 条件 then
--条件成立结构体
else
--条件不成立结构体
end if;
declare
v_num1 number:= 10;
v_num2 number:= 30;
begin
if v_num1>=v_num2 then
dbms_output.put_line(v_num1 ||'>=' ||v_num2);
else
dbms_output.put_line(v_num1 ||'<' ||v_num2);
end if;
end;
-- if-then-elsif
if 条件1 then
--条件1成立结构体
elsif 条件2 then
--条件2成立结构体
else
--以上条件都不成立结构体
end if;
begin
if 1=2 then
dbms_output.put_line('1=2'); -- 每个 then 面的语句结束都要加分号
elsif 1=3 then
dbms_output.put_line('1=3');
elsif 1=4 then
dbms_output.put_line('1=4');
else
dbms_output.put_line('1=1');
end if; -- if 语句结束用 end if
end;
3.2 case 语句
case
when 表达式1 then 语句序列1;
when 表达式2 then 语句序列2;
when 表达式3 then 语句序列3;
……
else 语句序列n;
end case;
begin
case
when 1 = 2 then
dbms_output.put_line('1=2'); -- 每个 then 面的语句结束都要加分号
when 1 = 3 then
dbms_output.put_line('1=3');
when 1 = 4 then
dbms_output.put_line('1=4');
else -- 可以没有此分支但是以上语句中必须有一个条件为真,如果有此分支一定不要加条件判断
dbms_output.put_line('1=1');
end case; -- case 语句结束用 end case
end;
4.循环控制
pl/sql提供了丰富的循环结构来重复执行一些列语句。oracle提供的循环类型有：
1.无条件循环loop-end loop语句
2.while循环语句
3.for循环语句
在上面的三类循环中exit用来强制结束循环。
-- loop循环
loop
--循环体
end loop;
语法格式：
1.循环体在loop和end loop之间,在每个loop循环体中,首先执行循环体中的语句序列,执行完后再重新开始执行。
2.在loop循环中可以使用exit或者[exit when 条件]的形式终止循环。否则该循环就是死循环。
例题1：打印1到100数字（换行）
declare
v_num number :=0;
begin
loop
v_num := v_num + 1; -- 自增
dbms_output.put_line(v_num);
exit when v_num>=100; -- 退出条件
end loop;
end;
-- while循环（先判断条件,条件成立再执行循环体）
while 条件 loop
--循环体
end loop;
declare
v_num number :=1;
begin
while v_num<=100 loop
dbms_output.put_line(v_num);
v_num := v_num + 1; -- 自增
end loop;
end;
-- for循环
for 循环变量 in [reverse] 循环下限..循环上限 loop
--循环体
end loop;
for循环需要预先确定的循环次数,可通过给循环变量指定下限和上限来确定循环运行的次数,然后循环变量在每次循环中递增（或者递减）
begin
for v_num in 1..100 loop -- 可以不声明变量,数字只能从小到大
dbms_output.put_line(v_num);
end loop;
end;
5.游标
游标的类型有两种：隐式游标和显示游标。
pl/sql会为所有的sql数据操作声明一个隐式的游标,包括只返回一条记录的查询操作。
显示游标四个步骤：
1.声明
2.打开游标
3.逐行获取数据
4.关闭游标
语法结构：声明游标
cursor 游标名[(参数1 数据类型[,参数2 数据类型...])]
is select 语句; --游标的声明
语法结构：执行游标
open 游标名[(实际参数1[,实际参数2...])]; --打开游标
fetch 游标名 into 变量名1[,变量名2...];
或
fetch 游标名 into 记录变量; --提取数据
close 游标名; --关闭游标（千万别忘了！）
游标属性：%found、%notfound
%found:
用于判断游标是否从结果集中提取数据。如果提取到数据,则返回值为true,否则返回值为false。
%notfound:
该属性与%found相反,如果提取到数据则返回值为false；如果没有,则返回值为trun。
declare
cursor c_emp is
select ename,sal from emp where deptno=10;
v_emp c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into v_emp;
exit when c_emp%notfound;
dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
end loop;
close c_emp;
end;
--for循环
declare
cursor c_emp is
select ename,sal
from emp
where deptno=10;
begin
for v_emp in c_emp loop
dbms_output.put_line('姓名: ' || v_emp.ename || '工资: ' || v_emp.sal);
end loop;
end;
-- 游标参数
例题1：打印某个部门的员工姓名和工资
declare
cursor c_emp(p_deptno emp.deptno%type) is -- 游标参数
select ename,sal
from emp
where deptno=p_deptno;
begin
for v_emp in c_emp(&部门号) loop -- 键盘输入变量值（键盘输入值时,字符型要加单引号）/打开游标的时候传入
dbms_output.put_line('姓名: ' || v_emp.ename || '工资: ' || v_emp.sal);
end loop;
end;
7.pl/sql 中的dml和ddl语言
-- dml
create table emp_01 as select * from emp;
begin
delete from emp_01 where deptno=10;
update emp_01 set sal=8888 where deptno=20;
insert into emp_01(empno,deptno) values(1111,40);
commit;
end;
select * from emp_01;
-- ddl
begin
-- create table emp_02 as select * from emp; -- ddl不能直接在pl/sql块中使用
-- truncate table emp_01;
-- alter table emp_01 add ct date;
drop table emp_01;
end;
8.动态sql
在pl/sql程序开发中,可以使用dml语句和事务控制语句,但是还有很多语句（比如ddl语句）不能直接在pl/sql中执行。
这些语句可以使用动态sql来实现。
语法格式：动态sql
execute immediate 动态语句字符串
[into 变量列表]
[using 参数列表]
--
begin
execute immediate 'create table yyy as select * from emp'; -- 字符串语句最后不要加分号;
end;
或者
declare
v_sql varchar2(100) :='create table yyy as select * from emp';
begin
execute immediate v_sql;
end;
-- 用参数传入
declare
v_empno emp.empno%type := &输入员工编号;
v_deptno emp.deptno%type := &输入部门编号;
v_sal emp.sal%type;
begin
execute immediate 'select sal from emp where empno = :1 and deptno= :2' -- :1和:2为参数名,用后面using的值替换
into v_sal -- 查询的结果写入变量
using v_empno, v_deptno; -- 替换动态sql中的参数:1和:2
dbms_output.put_line(v_sal);
end;
-- 关于 dbms_output.put 和 dbms_output.put_line
begin
dbms_output.put('0710'); -- 不换行（后面必须有dbms_output.put_line,才能打印）
dbms_output.put_line('bi');
dbms_output.put_line('hahaha');
end;
--
begin
dbms_output.put('0710');
dbms_output.put('bi');
dbms_output.put_line('');
end;
-- 双重循环
begin
for x in 1..3 loop
for y in 4..6 loop
dbms_output.put_line(x||'*'||y||'='||x*y);
end loop;
end loop;
end;
9.创建存储过程
语法格式：创建存储过程
create [or replace] procedure 过程名(参数1 [in|out|in out] 数据类型,参数2 [in|out|in out] 数据类型……)
is|as
pl/sql过程体;
三种参数：传入(in或省去),传出(out),传入或传出(in out)
注意: 1.数据类型不要带长度也不用括号 varchar2/number/date
2.如果存储过程不带参数,过程名后不用括号,is|as 后可以直接跟声明内容,不要declare
3.为in的参数,不能在过程中被赋值,只能作为调用传入值
4.省去参数类型则默认为in传入
5.in out 参数必需在声明时候赋初值,调用中用变量名
语法格式：调用存储过程
begin
过程名[(参数)];
end;
-- 1.不带参数
例题1：打印emp表中所有雇员的姓名,工作和薪水。
create or replace procedure sp_emp_deptno_1
is
cursor c_emp_deptno is
select ename, job, sal from emp;
begin
for v_emp_deptno in c_emp_deptno loop
dbms_output.put_line('姓名：' || v_emp_deptno.ename ||
'工作：' || v_emp_deptno.job ||
'工资：' || v_emp_deptno.sal);
end loop;
end;
-- 调用
begin
sp_emp_deptno_1;
end;
-- 2.只带传入参数
例题2：接收一个部门号,显示该部门的所有雇员的姓名,工作和薪水。
create or replace procedure sp_emp_info (p_deptno in emp.deptno%type)
is
-- 游标
cursor c_emp is
select ename,job,sal from emp where deptno=p_deptno;
begin
for v_emp in c_emp loop
dbms_output.put_line('部门:'||p_deptno||' 姓名:'|| v_emp.ename ||' 工作:' || v_emp.job || ' 薪水:' || v_emp.sal);
end loop;
end;
-- 调用
begin
sp_emp_info(10);
-- sp_emp_info(&部门编号);
end;
或者
declare
v_deptno emp.deptno%type :=10;
begin
sp_emp_info (v_deptno);
end;
或者
begin
sp_emp_info(p_deptno => 10); -- 参数定向调用（多个参数可以改变顺序）
end;
-- 3.带传入和传出参数
例题3：查询某个部门中的某个职位员工的姓名、工资、入职日期。（只考虑单行情况）
create or replace procedure sp_myemp(p_deptno in number,
p_hiredate out date,
p_sal out number,
p_job_ename in out varchar2)
is
begin
select e.ename, e.sal, e.hiredate
into p_job_ename, p_sal, p_hiredate
from emp e
where e.deptno = p_deptno
and e.job = p_job_ename;
dbms_output.put_line(p_job_ename || ' ' || p_sal || ' ' || p_hiredate);
exception -- 异常处理开始
when too_many_rows then
dbms_output.put_line('select into 返回多行值！');
when no_data_found then
dbms_output.put_line('select into 没有返回值！');
end;
--调用存储过程
declare
v_hiredate date; -- out 参数
v_sal number; -- out 参数
v_job_ename varchar2(100) :='manager'; -- in out 参数此处先声明并赋值
begin
sp_myemp(10, v_hiredate, v_sal, v_job_ename); -- 存储过程参数要对应（个数和类型）
end;
11.自定义函数
语法格式：创建函数
create [or replace] function 函数名(参数1 数据类型,参数2,[in|out|in out] 数据类型……)
return 返回的数据类型 -- 不带数据类型长度
is|as
pl/sql函数体; -- 里面必须要有一个return子句
-- 不带参数情况
create or replace function fun_test
return number -- 函数的返回类型
is
begin
return 100;
end;
select fun_test from dual;
-- 传入两个参数,返回最大值
create or replace function fun_max (p_num1 in number, p_num2 in number)
return number -- 函数的返回类型
is
begin
if p_num1>p_num2 then
return p_num1;
else
return p_num2;
end if;
end;
调用：
select fun_max(12,20) from dual;
select fun_max(12,20) from emp; -- 每行返回一个值
或者
begin
dbms_output.put_line(fun_max(12,20));
end;
或者
declare
v_num number;
begin
v_num := fun_max(12,20);
dbms_output.put_line(v_num);
end;
12.创建包
包就是把相关的存储过程、函数、变量、常量和游标等pl/sql程序组合在一起,
并赋予一定的管理功能的程序块。
一个程序包由两部分组成：包定义和包体。
其中包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素,
这些元素为包的共有元素。包主体则定义了包定义部分的具体实现。
注意：包声明的对象不一定要在包体中去使用,但是包体中的对象一定要在包声明中定义并保持一致。
语法格式：创建包头
create [or replace] package 包名
is|as
变量、常量及数据类型定义;
游标定义头部;
函数、过程的定义和参数列表以及返回类型;
end [包名];
语法格式：创建包体
create [or replace] package body 包名
is|as
procedure 过程名(参数)
is|as
begin
过程体;
end [过程名];
function 函数名(参数) return 类型
is|as
begin
函数体;
end [函数名];
end;
例题1：创建一个包, 包中含有存储过程和函数
create or replace package pk_mypackage
is
a number; -- 声明变量（声明的对象不一定要在包体中使用）
procedure my_sp(p_empno in number); -- 声明存储过程
function my_fun(p_num1 in number , p_num2 in number) return number; -- 声明函数
end;
create or replace package body pk_mypackage
is
-- 通过员工号找员工姓名和工资存储过程
procedure my_sp(p_empno in number) -- 和包中的声明要对应（并且一定要先声明）
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename, v_sal from emp where empno=p_empno;
dbms_output.put_line('姓名：'|| v_ename ||'工资：'|| v_sal);
exception
when no_data_found then
dbms_output.put_line('select into语句中没有返回任何记录!');
when too_many_rows then
dbms_output.put_line('select into语句中返回多于1条记录!');
end;
-- 比较大小函数
function my_fun(p_num1 in number , p_num2 in number)
return number
is
begin
if p_num1>p_num2 then
return p_num1;
else
return p_num2;
end if;
end;
end;
-- 用户下面的表
select * from user_tables;
-- 用户下面的过程体
select * from user_procedures where object_type ='package' ;
select * from user_procedures where object_type ='function' ;
select * from user_procedures where object_type ='procedure';
-- 调用存储过程
begin
pk_mypackage.my_sp(&员工编号);
end;
-- 调用函数
select pk_mypackage.my_fun(10,20) from dual;
13.异常处理
语法格式：异常处理
begin
--可执行部分
exception -- 异常处理开始
when 异常名1 then
--对应异常处理;
when 异常名2 then
--对应异常处理;
……
when others then
--其他异常处理;
end;
-------
declare
v_enmae emp.ename%type;
begin
select ename
into v_enmae
from emp
where deptno=10; -- 'dfad' 其它错误
dbms_output.put_line('返回单行值');
exception -- 异常处理开始
when too_many_rows then
dbms_output.put_line('select into 返回多行值！');
when no_data_found then
dbms_output.put_line('select into 没有返回值！');
when others then
dbms_output.put_line('其它错误！');
end;
13.触发器简介
触发器的定义就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。
触发器可以分为: 语句级触发器和行级触发器。
具体举例：
1、 在一个表中定义的语句级的触发器,当这个表被删除时,程序就会自动执行触发器里面定义的操作过程。
这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器,那当这个表中一行数据发生变化的时候,比如删除了一行记录,那触发器也会被自动执行了。
--触发器语法
语法：
create [or replace] trigger trigger_name
{before | after} trigger_event
on table_name
[for each row]
[when trigger_condition]
trigger_body
语法解释：
trigger_name：触发器名称
before | after : 指定触发器是在触发事件发生之前触发或者发生之后触发
trigger_event：触发事件,在dml触发器中主要为insert、update、delete等
table_name：表名,表示发生触发器作用的对象
for each row：指定创建的是行级触发器,若没有该子句则创建的是语句级触发器
when trigger_condition：添加的触发条件
trigger_body：触发体,是标准的pl/sql语句块
例题1：
create table student_123 --创建student表
(
id_no number(19), --id_no
stu_no varchar2(20), --学号
stu_name varchar2(32), --姓名
stu_age number, --年龄
stu_major varchar2(32) --专业
);
create table stu_log_123 --创建stu_log表,用于记录对student表的操作日志
(
log_id number, --日志id
log_action varchar2(100), --操作名称
log_date date, --操作时间
log_message varchar2(32) --日志信息
);
行级触发器（after触发器）
创建触发器：将对student_123表的操作都记录到stu_log_123表中
（of 用于指定一个或多个字段,指定字段被更新时才会触发触发器）
create or replace trigger modify_stu_123
after insert or delete or update of stu_name -- 字段
on student_123 -- 表
for each row -- 行级触发
begin
if inserting then
insert into stu_log_123 values(1,'insert',sysdate,:new.stu_name);
elsif deleting then
insert into stu_log_123 values(2,'delete',sysdate,:old.stu_name);
elsif updating then
insert into stu_log_123 values(3,'update_old',sysdate,:old.stu_name);
insert into stu_log_123 values(4,'update_new',sysdate,:new.stu_name);
end if;
end;
insert into student_123 values(1,'no2','李四',21,'数学系'); --插入一条数据
delete student_123 where stu_name='张三'; --删除一条数据
update student_123 set stu_age=19 where stu_name='李四'; --修改李四的年龄
update student_123 set stu_name='王二' where stu_name='李四';--修改李四的名称
select * from student_123;
select * from stu_log_123;
语句级触发器（before触发器）：用来控制对表的修改
create or replace trigger modify_stu_123_table
before insert or update or delete on student_123
begin
if deleting then
raise_application_error(-20001,'该表不允许删除数据');
elsif updating then
raise_application_error(-20002,'该表不允许修改数据');
elsif inserting then
raise_application_error(-20003,'该表不允许插入数据');
end if;
end;
delete from student_123;
drop table student_123;
14.dbms_job
该过程用于建立一个新的作业,当建立作业的时候,需要通过设置相应的参数来告诉oracle要执行的内容,
要执行的时间,要执行任务的间隔。如下格式：
dbms_job.submit(
job out binary_interger, -- 用于指定作业编号
what in varchar2, -- 用于指定作业要执行的操作
next_date in date default sysdate, -- 用于指定该操作的下一次运行的日期
interval in varchar2 default 'null', -- 用于指定该操作的时间间隔
no_parse in boolean default false, -- 用于指定是否需要解析与作业相关的过程
instance in binary_integer default any_instance, -- 用于指定哪个例程可以运行作业？
force in boolean default false -- 用于指定是否强制运行与作业相关的例程
);
-- 举例
create table testjob(
id number constraint testjob_id_pk primary key,
name varchar2(30),
ct_dt date
);
--如果序列存在就删除,然后创建一个序列
create sequence testjob_id_seq;
--创建一个序列,每一次向表中插入一条数据,并且表中的id字段值使用序列指定
create or replace procedure insert_job is
begin
insert into testjob
values
(testjob_id_seq.nextval, 'test' || testjob_id_seq.currval,sysdate);
commit;
end;
declare
jobno binary_integer;
begin
--提交,操作的时间间隔设置为1分钟
dbms_job.submit(jobno,'insert_job();',sysdate,'sysdate+1/(24*60*6)');
--打印序列号
dbms_output.put_line('jobno='||jobno);
--运行
dbms_job.run(jobno);
end;
-- 删除
begin
dbms_job.remove(jobno); -- jobno为具体数字
end;
plsql-->>oracle
navicat for mysql-->>mysql
pgadmin4-->>postgresql
mysql:
int/double char/varchar date/timestap
mysql----oracle
limit 10----rownum <=10
mysql和oracle的可能不同的地方,记得回去查询:常规查询语句，子查询，分组聚合，表关联，建表语句，
`id` bigint(20) not null auto_increment comment '主键',
primary key (`id`),
myisam和innodb 简单总结
https://blog.csdn.net/sjmz30071360/article/details/80822085
https://www.cnblogs.com/yjd_hycf_space/p/7772722.html
https://www.cnblogs.com/muchen/p/5310732.html
alias ll=‘ls -l’
pwd – 查看当前路径
ls -l – 查看当前目录下的所有文件和文件夹
mkdir 文件夹名 – 创建文件夹
touch 文件名 – 创建文件夹
vi 文件名 – 编辑文件 （先输入i，编辑完后按esc，再按shift+zz）
cat 文件名
more 文件名
rz – 上传文件
sz 文件名 – 下载文件
cd 文件夹名 – 进入某个文件夹目录
cd … 返回上一级目录
rm 文件名 – 删除文件
https://www.cnblogs.com/charisna/p/4673866.html
范式建模，维度建模（3种模型），实体建模
执行计划：
database link:
create public database link db_orcl
connect to scott identified by “123456” – 数字密码"123456"
using ‘192.168.0.222:1521/orcl’; – 需要连接的数据ip/端口/数据库名
– 查询
select * from emp@db_orcl;
– 删除
drop public database link db_orcl; – 删除database link
select * from emp@db_orcl;
扫一扫
7958
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
