oracle如何在union all中用自增序列_Oracle实战(三)-SQL语句

常用数据类型

通过下面的SQL可以查询和数据类型相关的信息。

select type_name from dba_types where owner is null;

创建用户并授权

create user jackfrued identified by 123123 password expire;
grant create session to jackfrued with admin option;
grant connect, resource to jackfrued;
revoke create session from jackfrued;

创建表

create table tb_user
(
  userid number(10) not null,
  username varchar2(20) not null,
  userpass char(32) not null,
  gender char(3) default '男',
  primary key (userid)
);
​
create table tb_login_log
(
  logid number(20) not null,
  logdate date not null,
  userid number(10) not null,
  primary key (logid)
);

修改表

alter table tb_user add email varchar2(255);
alter table tb_user add constraint uk_user_username unique (username);
alter table tb_user add constraint ck_user_gender check (gender in ('男', '女'));
alter table tb_login_log add constraint fk_login_log_userid foreign key (userid) references tb_user (userid);

删除表

drop table tb_login_log;
drop table tb_user;

使用函数

• 字符函数：concat / length / lengthb / lower / upper / substr / ltrim / rtrim / trim / translate / replace / lpad / rpad / initcap
• 数字函数：abs / ceil / floor / round / trunc
• 日期函数：add_months / last_day / months_between / sysdate
• 转换函数：to_date / to_number / to_char
• 其他函数：nvl / decode

集合运算：union（并集） / intersect（交集） / minus（差集） / in（属于） / any / all

使用select检索数据：使用scott用户的Emp和Dept表为例。

-- 查询薪资最高的员工姓名和工资
select ename, sal from Emp where sal=(select max(sal) from Emp);
​
-- 查询员工的姓名和年薪( 月薪 + 补贴 ) * 13
select ename, (sal+nvl(comm,0))*13 as total from Emp;
​
-- 查询有员工的部门的编号和人数
select deptno, count(deptno) from Emp group by deptno;
​
-- 查询所有部门的名称和人数
select dname, nvl(total, 0) as total from Dept t1 left outer join
(select deptno, count(deptno) as total from Emp group by deptno) t2
on t1.deptno=t2.deptno;
​
select dname, nvl(total, 0) as total from Dept t1,
(select deptno, count(deptno) as total from Emp group by deptno) t2
where t1.deptno=t2.deptno(+);
​
-- 查询人数超过4个人的部门名称和人数
select dname, nvl(total, 0) as total from Dept t1 inner join
(select deptno, count(deptno) as total from Emp group by deptno) t2
on t1.deptno=t2.deptno
where total>4;
​
select dname, nvl(total, 0) as total from Dept t1 inner join
(select deptno, count(deptno) as total 
from Emp group by deptno having count(deptno)>4) t2
on t1.deptno=t2.deptno;
​
-- 查询薪资最高的员工 ( Boss 除外 ) 的姓名和工资
select ename, sal from Emp where sal=(select max(sal) from Emp where mgr is not null);
​
-- 查询薪水超过平均薪水的员工的姓名和工资
select ename, sal, (select avg(sal) from Emp) as avgSal from Emp
where sal> (select avg(sal) from Emp);
​
-- 查询薪水超过其所在部门平均薪水的员工的姓名、部门名称和工资
select ename, dname, sal from
(select ename, t1.deptno, sal from Emp t1 inner join
(select deptno, avg(sal) as avgSal from Emp group by deptno) t2
on t1.deptno=t2.deptno and sal>avgSal) t3 inner join
Dept t4 on t3.deptno=t4.deptno;
​
-- 查询主管的姓名和职位
select ename, job from Emp
where empno in (select distinct mgr from Emp where mgr is not null);
​
select ename, job from Emp t1
where exists (select 'x' from Emp t2 where t1.empno=t2.mgr);
​
-- 查询月薪排名前 5 的员工姓名和工资
select ename, sal from
(select rownum as rn, ename, sal
from (select ename, sal from Emp order by sal desc) t1) t2
where rn<=5;
​
-- 查询月薪排 4 - 8 名的员工姓名和工资
select ename, sal from
(select rownum as rn, ename, sal
from (select ename, sal from Emp order by sal desc) t1) t2
where rn between 4 and 8;
​
-- 平均薪水最高的部门的名称和平均工资
select dname, avgSal from 
     (select deptno, avg(sal) as avgSal 
       from Emp group by deptno
       having avg(sal)=(
              select max(avgSal) as maxSal from
              (select deptno, avg(sal) as avgSal 
              from Emp group by deptno) t1
       )) t2 
inner join Dept t3
on t2.deptno=t3.deptno;

索引：索引是关系型数据库中存放表中每条记录位置的对象，相当于一本书的目录，可以用来加速查询，但是更新和删除数据会更新索引因此对更新和删除操作性能有一定影响，同时会使用更多的存储空间。索引要建在被查询得最多的列上才能起到更好的作用。

create unique index idx_user_userid on tb_user (userid);
create index idx_user_email on tb_user (email);
alter index idx_user_email rebuild storage (initial 1M next 512k);
alter index idx_user_email rebuild reverse;
drop index idx_user_email;

视图：视图是查询结果的存储，相当于一张虚拟的表，可以简化查询所使用的SQL语句，此外还可以起到安全和保密的作用（将访问限制在指定的列上）。

create or replace view view_name as select ...
    [with check option]
    [with read only]
drop view view_name;

序列：通过序列可以自动生成一个的整数，一般用于自增主键。

create sequence seq_name start with a increment by b minvalue c maxvalue d cache e cycle;
select seq_name.nextval from dual;
select seq_name.currval from dual;

同义词：数据库对象的别名，可以用来保护数据库对象的隐私。

create or replace synonym foo for bar;
drop synonym foo;

函数：封装常用的操作，可以产生返回值

create or replace function fn_factorial (n number) 
return number is
i number := 1;
result number := 1;
begin
for i in 1..n loop
    result := result * i;
end loop;
return result;
end fn_factorial;

过程：

create or replace procedure sp_getAvgSalByDeptNo(pDeptNo dept.deptno%type, pAvgSal out emp.sal%type) as 
begin  
    select avg(sal) into pAvgSal from Emp where deptno=pDeptNo;
end;

触发器：

create or replace trigger tr_update_deptno
after update on dept
for each row 
begin
 update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

触发器的例子。

例子1：删除员工时转移员工记录

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 
CREATE OR REPLACE TRIGGER tr_del_emp 
BEFORE DELETE ON scott.emp FOR EACH ROW 
BEGIN 
INSERT INTO emp_his
(deptno, empno, ename, job, mgr, sal, comm, hiredate) 
VALUES (:old.deptno, :old.empno, :old.ename , :old.job,:old.mgr,     :old.sal, :old.comm, :old.hiredate ); 
END;        
DELETE emp WHERE empno=7788;

例子2：限定只能在指定时间修改部门信息

CREATE OR REPLACE TRIGGER tr_dept_time 
BEFORE INSERT OR DELETE OR UPDATE ON departments 
BEGIN 
IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) 
OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN 
RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表'); 
END IF; 
END;

【重要消息】感谢知友您能够看到这部分内容，本文是软件测试系列知识中 全栈测试数据管理与数据库测试 中的一篇，笔者认为本部分全面的知识应该包含如下图所示的内容：

如果知友对这部分内容感兴趣，可以持续关注小编的账号，除此之外，小编还录制了不少这方面的技术视频，知友如果有需要，可以私聊本小编获取哦！