oracle_second

Oracle-second

1.子查询

解决问题不能一次求解的情况。

 

1）单行子查询

·谁的工资比SCOTT高？

 select sal,ename from emp where sal>(select sal from emp where ename='SCOTT');

·使用子查询的方式查询出员工表级部门名称？
select e.ename,e.empno,e.deptno,(select d.dname from dept d where d.deptno=e.deptno) from emp e;

·查询部门名称是SALES的员工都有哪些?

select * from emp where deptno=(select deptno from dept where dname='SALES');

 

2）多行子查询

操作符	描述
in	和集合中的对比(10,20,30)
any	和集合中的任意一个值进行比较
all	和集合中的所有值比较

·查询部门编号小于30的所有员工信息强制使用in查询？

·查询工资是前3名的部门信息?

注意：rownum关键字会一直按照结果集的默认排序生成，观察两层查询，

rownum判断只能使用<、<=，不能使用>和>=

思考题？ Oracle中如何写分页查询语句。

·查询工资比30号部门《任意》一个员工工资高的员工信息？
select * from emp where sal>any(select sal from emp where deptno=30);

·查询工资比30号部门所有员工工资高的员工信息？

select * from emp where sal>all(select sal from emp where deptno=30);

·查询不是老板的员工？

//1.定位所有上级的员工（老板们）

select mgr from emp;//查出所有老板的员工编号。

 

//2.将不再《1》查出的结果集里员工找出来

Select * from emp where empno not in(select mgr from emp);//错误的
Select * from emp where empno not in(select mgr from emp where mgr is not null);

·查询出各个部门中比自己所在部门平均工资高的员工信息？

select * from emp e where e.sal>(select avg(sal) from emp where deptno=e.deptno);

 

查询工资比30号部门任意一个员工工资高的员工信息？

 select * from emp where sal>any(select sal from emp where deptno=30);

 

查询工资比30号部门所有员工高的员工信息？

select * from emp where sal>all(select sal from emp where deptno=30);

 

查询不是老板的员工？

 select * from emp where empno not in(select nvl(mgr,0) from emp);

 

分页逻辑

SQL> select * from ( select rownum rn,sal,ename from (select sal,ename from emp

order by sal desc)) where rn>4 and rn<=6;

 

 

注意：

*子查询和可以出现在查询列中也可以在where条件中出现

*group by 中不能使用子查询

*主查询和子查询可以不是一张表，只要子查询返回结果主查询就可以使用

*子查询一般不用排序

*top-n分析时候必须使用排序

 

 

ROWNUM        SAL ENAME -------- ---------- ----------        1        800 SMITH        2       1600 ALLEN        3       1250 WARD        4       2975 JONES        5       1250 MARTIN        6       2850 BLAKE        7       2450 CLARK        8       3000 SCOTT        9       5000 KING       10       1500 TURNER       11       1100 ADAMS     ROWNUM        SAL ENAME -------- ---------- ----------       12        950 JAMES       13       3000 FORD       14       1300 MILLER

ROWNUM        SAL ENAME ------- ---------- --------       9       5000 KING      13       3000 FORD       8       3000 SCOTT       4       2975 JONES       6       2850 BLAKE       7       2450 CLARK       2       1600 ALLEN      10       1500 TURNER      14       1300 MILLER       3       1250 WARD       5       1250 MARTIN    ROWNUM        SAL ENAME ------- ---------- --------      11       1100 ADAMS      12        950 JAMES       1        800 SMITH

实现oracle的分页查询语句，要求是1页两条，查询第三页。

select rownum,rn,sal,ename from (select rownum rn ,sal,ename from emp order by sal desc);

Start =（3-1）*2 end=3*2    4-6

 select * from (select rownum rownumnew,sal,ename from (select sal,ename from emp order by sal desc))

 where rownumnew>4 and rownumnew<=6;

*优化：能使用多表查询尽量使用多表查询

 

2.集合运算

符号	描述
union/union all	并集
intersect	交集
minus	差集

 

 

·查询10号和20号部门的员工信息？（in  or两种方式，使用集合运算）

Select * from emp where deptno in(10,20);

Select * from emp where deptno=10 or deptno=20;

 

Select * from emp where deptno=10 union Select * from emp where deptno=20;//正确的

Select ename,deptno,sal from emp where deptno=10  

union 

Select ename,sal from emp where deptno=20;
·查询即是10号部门又是工资大于2000的员工信息?(使用集合运算)

Select * from emp where deptno=10 and sal>2000;

Select * from emp where deptno=10 intersect Select * from emp where sal>2000;

·查询除了10号部门以外的员工信息?(使用集合运算)

Minus

Select * from emp minus Select * from emp where deptno=10;

3.DML数据操作语句

数据操作指的是insert  delete  update，select语句呢学名是DQL（数据查询语句），不用在意这些叫法，叫错了也没关系。

 

·（insert）插入一条员工信息？

Insert into 表名(列1，列2.。。) values(值1，值2,。。)

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

values(1,'王振','sallers',7788,to_date('2016-01-01','yyyy-mm-dd'),2000,20000,10);

 

使用地址符的方式来动态添加参数--不重要

·将某一个部门的员工数据都插入到另一张表中去？

Insert into newemp select * from emp where deptno=12

 

上面的需求中我们可以使用insert语句中嵌入子查询来完成，将子查询查出的数据插入到另一个表。

·（update）更新一条员工信息，使他的工作和工资和某一个员工一致？

把7788这个员工工资和工作修改成与7839一样。

Update emp set job=(select job from emp where empno=7839),sal=(select sal from emp where empno=7839) where empno=7788

·（delete）删除一条员工信息？

delete from emp where empno=2;

·（truncate、delete）删除整张表的数据? TRUNCATE TABLE 表名

注意：delete的是逐条删除记录（可回滚），truncate是先摧毁表然后在重建（不可回滚）。

 

 

4.使用DDL语句来管理数据库对象

 

1）数据库表

表空间：oracle数据库的逻辑单元，一个表空间可以和多个数据文件（硬盘文件）关联，那一个数据库下可以建立多个表空间，一个表空间下可以建立多个用户、一个用户下可以建立多张表。

创建表空间的语法:

create tablespace [表空间名]  datafile‘文件路径.dbf’ size  50M(初始文件大小)

autoextend on next 50M maxsize 20480M(文件最大限制);

create tablespace huidashop datafile 'C:\oracle\data\huidashop.dbf' size 20M autoextend on next 1M maxsize 50M;

用户：

 

与mysql 不同，oracle中，是没有数据库的概念的【mysql：create database】，拿用户来区分，可以理解成一个oracle中的用户就相当于mysql中数据库的概念，我们mysql中一个项目创建一个数据库，在oracle中一个项目就创建一个用户。

创建用户的语法：

create user [用户名] identifed by itcast default tablespace默认表空间 temporary tablespace temp account unlock;

--创建用户

create user huidashop identified by huidashop default tablespace huidashop

temporary tablespace temp account unlock;

--给用户授权

grant resource,connect,dba to huidashop;

 

给用户授权:

创建用户完成后，用户来不能对本数据库进行操作，还需要赋予权限，我们企业级应用开发人员学会简单的到顶授权就行，语句如下：

grant connect,resource,dba to [用户名];

意思是给这个用户手续，连接，访问资源，数据库管理员权限（包含所有权限）。

1.grant create session to test;--赋予create session的权限
2.grant create table,create view,create trigger, create sequence,create procedure to test;--分配创建表，视图，触发器，序列，过程 权限
3.grant unlimited tablespace to test; --授权使用表空间

具体的权限怎么划分，我们不用太关心了。

 

创建表:

Oracle数据库中的sql语句也是遵循sql标准的，所以我们根据在mysql数据库学习的创建的表的语法我们可以照着葫芦画瓢拿过来。

语法：

create table [表名](

列1 类型,

列2 类型,

...

)

 

 create table SHOP_CATEGORY(

    ID varchar2(50)  primary key,

    CATEGORYNAME varchar2(20),

    CATEGORYDESC varchar2(200),

    ORDER_NUMBER number(16,2),

    CATEGORYSTATE varchar2(10)

 );

语法和mysql是一致的，那么他们之间的不同点就在与数据类型上。

oracle的数据类型如下：

数据类型	描述
VARCHAR2(长度)	可变长的字符数据
CHAR(长度)	定长字符数据
NUMBER(整数位，小数位)	可变长的数值数据
DATE	日期型数据
LONG	可变长字符数据，最大可达2G
CLOB	字符数据，最大可达4G
RAW and LONG RAW	原始二进制
BLOB	二进制可达4G
BFILE	存储外部文件的二进制数据可达4G
ROWID	行地址

 

使用create table 结合子查询可以复制一个表结构

create table as  [子查询]

create table emp01 as select * from emp where 1=2; 当子查询有数据的时候会复制表结构的同时，将数据拷贝到新表中。

·将用户的编号，姓名，薪水，部门编号，部门名称复制出来新建一张表

对表结构的修改：

1)新增列

alter  table 表名  add  列 列类型;

 alter table shop_category add testcol varchar2(20);

2)修改列
alter table  表名  modify列 列类型;
alter table shop_category modify testcol number(10,2);

3)删除列
alter table 表名  drop column列;

 alter table shop_category drop column testcol;

4)重命名列

alter table 表名 rename column原列名 to新列名;

 alter table shop_category rename column testcol to testcolnew;

5)重命名表

rename 原表名 to新表名;

rename shop_category to shop_categorynew;

6)删除表

drop  table  表名;  show recyclebin;查看回收站   purge recyclebin;

7)删除闪回

flashback  table 表名 to before drop;

5.ORACLE中的约束

约束类型	关键字
主键约束	primary key
非空约束	not  null
唯一约束	unique
外键约束	foreign key
检查性约束	check

 

 

1)主键约束 primary key

create table student_info(

id varchar2(50) primary key ,

name varchar2(10) ,

stucode varchar(20) ,

sex varchar2(5) ,

age number(3)

)

 

2)非空约束  not  null

create table student_info(

id varchar2(50)  ,

name varchar2(10) not null,

stucode varchar(20),

sex varchar2(5) ,

age number(3)

)

 

3)唯一性约束 unique

create table student_info(

id varchar2(50)  ,

name varchar2(10),

stucode varchar(20) unique,

sex varchar2(5) ,

age number(3)

)

 

4)外键约束 foreign key

create table teacher_info(

id varchar2(50) primary key,

name varchar2(20) not null

)

create table student_info(

id varchar2(50) primary key ,

name varchar2(10),

stucode varchar(20) unique,

sex varchar2(5) ,

age number(3) ,

teach_id varchar2(50) references teacher_info(id) on delete set null

)

On delete cascade级联删除

5)检查性约束  check

检查性约束是对数据进行一定的规则校验，比如取值范围等等

例如：1、同学们的性别只有男，女两种如果出现第三种就是错误数据。

      2、学生们的年龄只能是大于零的数

create table student_info(

id varchar2(50) primary key ,

name varchar2(10),

stucode varchar(20) unique,

sex varchar2(5)  check(sex in (‘男’,’女’)),

age number(3)  check(age >0),

teach_id varchar2(50) references teacher_info(id) on delete set null

)

对于约束我们可以给他们命名，当不指定约束的名称时，数据库会给随机分配一个。那么随机分配的名字就不是那么的容易懂了。如果出现了问题定位起来会麻烦一些。创建约束的两种方式是1.创建表的时候同时创建约束，2.创建表的时候不指定约束，在后期添加

create table student_info(

id varchar2(50) primary key ,

name varchar2(10),

stucode varchar(20) constraint stucode_unique unique,

sex varchar2(5) constraint sex_type_check check(sex in (‘男’,’女’)),

age number(3) constraint age_min_check check(age >0),

teach_id varchar2(50) constraint stu_fk_teach_teachid references teacher_info(id) on delete set null

)

删除约束

alter table student_info drop constraint age_min_check ;

添加约束

alter table student_info add constraint age_min_check check(age >0);

 

6.视图

和mysql中的视图的概念是一样的，就是封装了一个复杂的查询语句；

视图实际上是一个虚表。

最大的意义就是简化了复杂的查询。

语法：

create or replace view 视图名称 as一个查询语句

查表怎么查，视图就怎么查，我们的视图是不存数据的。

·查询scott用户下的员工信息（部门名称）？

7.序列

序列从功能上来说是，和mysql 中auto increment。

create sequence 序列名

increment by 数字

start with 数字

(maxvalue 数字/nomaxvalue)

(minvalue 数字/nominvalue)

(cycle / nocycle)

(cache n/nocache)

 

select myfirst_seq.nextval from dual;//获得下一个序列值

select myfirst_seq.currval from dual; //获取当前索引值

序列就是一个递增序列就这么简单，那么我们在使用这个序列的时候一般是用来生成编号或者主键，可以这么用.

insert into test_seq(myfirst_seq.nextval,’史密斯’)

 

 

8.索引

索引是用于加速数据存取的数据对象，合理使用索引可以大大降低i/o次数,从而提高数据访问性能。

适合建索引的表：

①列中的范围分布很广。

②列经常出现在WHERE子句或者在连接条件中出现。

③表经常被访问而且数据量很大，访问的数据大概占数据总量的2%-4%之间。

 

语法：create index 索引名 on 表名(列名)

 

create index emp_deptno on emp(deptno);

创建索引前我们执行一句sql

explain plan for select * from emp where deptno=10;

select * from table(dbms_xplan.display);

创建索引后我们再次执行sql
explain plan for select * from emp where deptno=10;
select * from table(dbms_xplan.display);

 

对比两次结果我们可以发现，创建索引之后我们的sql的执行会更加的省cpu，才这么十几条数据效果就这么明显。索引能够极大的提高我们的查询效率。

 

9.同义词

同义词相当于给某个表起了个别名，通过别名可以对繁琐的表名简化。

语法：

create synonym 名称 for原名

hr用户有一张表employees，我们可以给scott用户授权。

我们给hr.employees创建一个同义词，我们就可以通过同义词访问另一个表。

 

10.数据的导入导出

导出：exp scott/tiger file=D:\exporacle\scott.emp;

导入: imp scott/tiger file=D:\exporacle\scott.emp ignore=y full=y;