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;