黑帽联盟

标题: oracle查询语句总结(可用于复习) [打印本页]

作者: yun 时间: 2016-11-28 23:47
标题: oracle查询语句总结(可用于复习)

Oracle创建表

语法：create table 表名（字段名数据类型，...，…）；

使用alter table 来修改表当中字段的属性或者增加字段。

Alter table 表名 add（字段数据类型）为表增加字段

Alter table 表名 modify （字段修改后的数据类型）为表修改字段属性

和oracle语法有点不同，而且这里没有change，不能改变字段名称。

Alter table 表名 drop column 字段删除表当中相应的字段

Oracle表的管理

Crud 即（create、retrive、update、delete）增删改查

Insert语句增加数据

Delete语句删除数据

Update语句修改语句

Select语句查询语句

Insert语句（略）

Update语句

语法：update 表名 set 字段=修改的数据 where 条件；

Delete语句

语法：delete from 表名 where 条件进行约束；（不加条件就是删除表中所有记录，但不删除结构）

Drop table 表名；（删除记录和结构）

Truncate table 表名；（删除记录，结构存在，但数据通过回滚也找不回了）

Oracle保存点和回滚事务

前提设置提交方式是手动提交即set autocommit off

Savepoint：（设置保存点）

语法：savepoint 保存点名称

Rollback：（回滚事务）

语法：rollback to 保存点名称；

直接使用rollback，回滚所有。即之前的一切操作都没有生效。

Oracle查询语句

Oracle里面要求大小写的，仅仅针对的是字段值，对字段名和表名没有要求。

Mysql没有大小写要求。

对表中字段值为空时，进行表达式计算，最终结果还是空值。所以需要函数来处理

Select sal*12+nvl(comm，0) 年薪 from emp；

Nvl(comm.,0) 如果comm为null，给它赋值0，如果不等于0，按原值计算。

用户锁定

要求：如果某个用户不用了，不想让这个用户查看数据，但允许管理员可以。

Alter user 用户 account lock/unlock

Oracle查询操作符

Like关键词（模糊查询）

要求：查看名字开头是S的人的工资

Select ename,sal from emp where like ‘S%’;

%表示0个或者任意多个字符。

_表示任意单个字符。

in操作符

要求：查询empno为123，345，800的员工情况

Select * from emp where enpno=123 or empno=345 or empno=800；

或者简便点

Select * from emp where empno in（123，345，800）；

Is null操作符

要求：查询么有上司的员工，即董事长

Select ename from emp where mgr is null;

Order by操作符

和oracle语法一样

Select * from emp order by deptno，hiredate desc；

按照部门编号升序的同时，在对雇员入职时间进行降序排列（有多个排序，即可在后面添加即可）

Oracle复杂查询

聚合函数：max、min、sum、count、avg

Group by和having字句

Group by用于对结果进行分组统计

Having用于限制结果分组显示

Having后面条件表达式不能用别名。如：having 平均工资>2000。这种事错误的。

Group by和having和order by同时出现的话，顺序依次是group by、having、order by

多表查询

在此处就涉及到了外键这个概念，通过外键让多表进行关联。

Select ename，sal，grade from emp,salgrade where sal between losal and hisalorder by grade

含义：查询员工的工资，以及工资所在的级别。

注：order by 后面的字段如果是一个表当中的外键的话，在这里就要明确标出是哪个表的字段，如dept和emp表里都有deptno，所在在这里应该这样写：order by dept . deptno；

自连接

定义：自己连自己，一张表被当作两张表以及多张表来用。

Select worker.ename，boss.ename from emp worker，emp boss where worker.mgr=boss.deptno；

查询所有员工姓名以及他们对应的老板姓名，此结果不是最完美的。应该最高级别的员工没有老板，也就没有显示出来。

解决办法：使用外连接

Select worker.ename，boss.ename from emp worker，emp boss where worker.mgr=boss.deptno(+);

子查询

定义：嵌套在其他sql语句中的select字句。

分为：单行子查询和多行子查询

单行子查询：子查询语句值返回一行记录的语句

多行子查询：子查询语句返回多行记录的语句

多行子查询：

使用in操作符

查询与10号部门当中职位相同的雇员名称、职位、部门编号

Select ename，job，deptno from emp where job in（select distinct job from emp）；

使用all操作符

查询大于30号部门所有员工工资的姓名、工资、岗位、部门编号。

Select ename，job，sal，deptno from emp where sal>all（select sal from emp wheredeptno=30）；

第二种方法：

Select ename，job，sal，deptno from emp where sal>（select max（sal） from emp where deptno=30）；

使用any操作符

查询大于30号部门任意一个员工工资的姓名、工资、岗位、部门编号。

Select ename，job，sal，deptno from emp where sal>any（select sal from emp wheredeptno=30）；

第二种：

Select ename，job，sal，deptno from emp where sal>（select min（sal） from emp where deptno=30）；

多列子查询

定义：针对返回结果是多列的数据

查询与姓名是SMITH员工的部门编号和岗位完全的相同的员工的姓名

Select ename from emp where （deptno，job） in（select deptno，job from emp where

ename=’SMITH’）；

在from字句中使用子查询（子查询语句相当于一个临时表表）

查询高于自己部门平均工资的员工信息

Select t1.*，t2.myavg from emp t1，（select deptno，avg(sal) myavg from emp group by deptno）t2 where t1.deptno=t2.deptno andt1.sal>t2.myavg；

分页查询

Oracle表中都有这么一个默认字段rownum。

Select *，rownum from emp；

案例：查询emp表中第四条到第六条记录

Select * from （Select t1.*，rownum rn from emp t1where rownum<=6）t2 where rn>=4；

也可以这样写

Select * from （Select t1.*，rownum rn from emp t1）t2 where rn between 4 and 6；但效率太低，

快速创建表

Create table 表名 as select 字段，字段，… from 存在的表；

快速复制（插入内容）

Insert into mytest select * from mytest；多执行几次（主要用于测试分页查询）

联合查询

Union操作符（去除重复行）

Union all操作符（不去除重复行）

Intersect 操作符（交集）

Minus 操作符（差集）

不经常用

表连接

分为内连接和外连接

内连接

定义：利用where字句对两张表进行笛卡尔集进行筛选。

语法：select 字段，… from 表名1 inner join 表名2 on 条件表达式；

案例：显示员工的姓名以及所在的部门名称

Select ename,dname from emp,dept whereemp.deptno=dept.deptno;

相当于

Select ename,dname from emp (inner) joindept on emp.deptno=dept.deptno;

外连接
分为左外连接，右外连接，完全外连接

左外连接：如果左侧的表完全显示我们就说是左外连接

右外连接：如果右侧的表完全显示我们就说是右外连接

完全外连接：完全显示两个表，没有匹配的记录置为空

左外连接和右外连接可以相互转换

Insert into stu values(1,’jack’);

Insert into stu values(2,’ton’);

Insert into stu values(3,’kity’);

Insert into stu values(4,’nono’);

Insert into exam values(1,88);

Insert into exam values(2,99);

Insert into exam values(11,66);

案例：查询部门里所有员工的信息，以及部门没有员工的也显示出来

select dname,emp.* from emp right join depton emp.deptno=dept.deptno;

select dname,emp.* from emp right join depton emp.deptno(+)=dept.deptno;

select dname,emp.* from dept left join empon emp.deptno(+)=dept.deptno;

select dname,emp.* from dept left join empon emp.deptno=dept.deptno;

欢迎光临黑帽联盟 (https://bbs.cnblackhat.com/)