你的位置:首页 > 数据库

[数据库]Oracle 数据库二 基本查询


查询当前用户:show user

查看当前用户下的表:select *from tab;

设置行宽: show linesize;(查看行宽)     set linesize 120;(设置行宽)

设置列宽:col 列名 for a8; (设置字符串)  col 列名 for 9999;(设置数字的)

查看表的结构:dese emp;

查询所有的员工信息:select * from emp;

投影:select empno,ename,sal from emp;

滤空函数:select empno,ename,(sal*12+nvl(comm,0)) from emp;

取别名的三种方式:

as "别名"

"别名"

别名  ——不能用关键字

去重:distinct

select distinct (列) from emp;

select distince 列,列 from emp;

字符串的连接

select concat('hello','world') from emp;

select 'hello'||'world' from emp;

可用伪表来测试:dual;

where的应用:

  查询10号部门的员工:

  select * from emp where deptno=10;

  查询名字叫KING的员工

  select * from emp where ename='KING';

修改日期格式:

select * from v$nls_parameters;(查询日期格式)  alter session/system set NLS_DATE_FORMAT='yyyy-mm-dd'; (一般修改当前会话session)

between...and...  在...之间

in/not in

is null 为空的

is not null 不为空的

模糊查询:

select * from emp

where job like'%H%' 包含了H的 / 'H%' 以H开头 / '%H' 以H结尾的 / '_H%' 第二位为H的 / '%\_%'escape'\' 找带有_的;

commit 提交

rollback 事务回滚

a两个空格;  语句后追加

排序:

  order by 默认升序(asc) + desc降序

  order by +列、表达式、别名、序号

  order by 字段1,字段2;   先用字段1来排序,其中相同的再用字段2来排

null 是oracle中的最大值

null last 让空值最后显示

 

字符函数:

upper  小写转换大写

cower  大写转换小写

initcap  首字母大写

//select upper('hello') 大写,lower('HELLO') 小写,initcap('hello world') 首字母大写 from dual;

length  获取字符的长度

//select length('hello') 长度,lengthb('hello') from dual;

lengthb  获取字节的长度

//select length('中') 长度1,lengthb('中') 长度2 from dual;

replace  替换

//select replace('hello world','l','a') from dual;

substr  截取

//select substr('hello world',2,4) from dual;

instr  查找字符串中某个字符的位置

//select instr('hello world','o') from dual;

lpad  左补齐

//select lpad(999,10,'*') from dual;      *******999

rpad  右补齐

trim  去除前后的某个字符或字符串

select trim('h' from 'hello world') from dual;

 

数值函数:

round  四舍五入小数

//select round(45.926,2) 数据1,round(45.926,1) 数据2,round(45.926,0) 数据三,round(45.926,-1) 数据4, round(45.926,-2) 数据5 from dual;

trunc  截断小数

//select trunc(45.926,2) 数据1,trunc(45.926,1) 数据2,trunc(45.926,0) 数据三, trunc(45.926,-1) 数据4, trunc(45.926,-2) 数据5 from dual;

mod  除数取余

//select mod(10,3) from dual;

 

日期函数:

sysdate  今天

sysdate+1  明天

sysdate-1  昨天

// select sysdate - (sysdate -1) from dual;  今天减去昨天

last_day  某一月的最后一天

//select last_day(sysdate) from dual;

next_day  获取下个星期几

//next_day(sysdate,"星期三")

add_moths  追加几个月

//add_months(sysdate,3)

months_beween  精确计算月份

//months_beween(sysdate.hiredate)

round  四舍五入日期

//select round(sysdate,'year') from dual;

truuc  截断日期

//select round(sysdate,'month') from dual;

 

转换函数:

to_char()  将其他类型的数据转换为字符串

//select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;

//select to_char(12535,'L99,999') from dual;

to_date:  将其他格式的数据转换为日期格式

//select to-date(sydate) from dual;

to_number  将其他格式的数据转换为数字

//select to_number('999') from dual;

 

通用函数:

nvl: 虑空函数

nvl2: 是nvl函数的一个加强版 nvl2(a,b,c) 如果a是空的则返回c,否则返回b

//select comm,nvl2(comm,comm,100) from emp;

nullif  nullif(a,b) 如果a等于b则返回空,否则返回a

//select nullif('hello','world') from dual;

coalesce(a,b,c...)  从左往右一次查找第一个不为空的值返回

涨工资:

第一种方式:case....end   SQL99语句

select job, sal,

case

when job = 'SALESMAN',sal+800

when job = 'MANAGER' then sal+1000

when job = 'CLERK' then sal + 900

else sal+400

end

from emp;

第二种方式:用decode  Oracle中独有的

select job,sal,decode(job,'SALESMAN',sal+800,'MANAGER',sal+1000,'CLERK',sal+900,sal+400) from emp;

 

多行函数

多行函数也称组函数,自带滤空功能

nvl(com ,0)  取消滤空

sum  求和

count(*)  统计

//select count(empno) from emp;

avg  平均值

max  最大值

min  最小值

group by  分组

//select sum(sal) ,avg(nvl(sal,0))

deptno from emp

group by;//

查询部门平均工资大于2000的部门编号:

//select deptno,avg(sal) 平均工资

  from emp
  group by deptno
  having avg(sal) > 2000;//

求20号部门的平均奖金和奖金总和

//select sum(comm) 总和,avg(comm) 奖金1,sum(comm)/count(*) 奖金2

  from emp
  where deptno=20
  group by deptno

查询大于本部门的平均工资的员工信息

//select ename,empno,deptno,avg(sal) 平均
  from emp
  group by deptno,ename,empno

//

group by 和where 不能一起使用,可以用having来代替

SQL中的顺序

1、select *from emp

2、where

3、group by

4、having

增强的group by   ——rollup()

//select deptno,job,sum(sal)
  from emp
  group by rollup(deptno,job);

//

break on deptno skip 2;  可用来调整格式

break on null;  用来取消上面的格式

注意:

 

where后面不能跟组函数

 

如条件中要使用组函数,那么只能用having条件

 

如果条件不需要使用组函数,可以使用where也可以使用having,推荐使用where

多表查询:

笛卡尔集:

//select e.empno,e.ename,job,d.deptno,d.dname
  from emp e,dept d

等值链接:等值连接 用等号去连接2个表,但是一定要注意连接的2个表一定要有关系

//select e.empno,e.ename,job,d.deptno,d.dname
  from emp e,dept d
  where e.deptno = d.deptno;

非等值连接:

//select e.empno,e.sal,s.grade,s.losal,s.hisal
  from emp e,salgrade s
  where e.sal between s.losal and s.hisal;

//-- 统计每一个部门的人数

 -- 显示部门号 部门名称 部门人数

select e.deptno,d.dname,count(*)
from emp e,dept d
where e.deptno = d.deptno
group by e.deptno,d.dname;

外连接:分类左外连接和右外连接  左外连接以左边表为主表,主表会显示所有,另一个表如果没有显示空,右外连接以右边表为主表.....

  左外连接:  where e.deptno = d.deptno 不成立的时候,无论右边是否有数据与之对应,左边的都要显示  where e.deptno = d.depton(+)

  select d.deptno,d.dname,count(e.empno)
  from emp e,dept d
  where e.deptno = d.deptno(+)
  group by d.deptno,d.dname;

  右外连接:  where e.deptno = d.deptno 不成立的时候,无论左边表是否有数据与之对应,右边表都会显示  where e.deptno(+) = d.deptno;

  select d.deptno,d.dname,count(e.empno)
  from emp e,dept d
  where e.deptno(+) = d.deptno
  group by d.deptno,d.dname;

自连接:只有一张表,虚拟出一张自身表;自连接会产生大量的笛卡尔集,只能做数据量小的表,对于大表用层次查询

select e.ename 领导名字,e.empno 领导ID,p.ename 员工名字,p.empno 员工ID
from emp e,emp p
where e.empno = p.mgr;

层次查询:

select e.ename 领导名字,e.empno 领导ID
from emp e
connect by prior empno = mgr
start with mgr is null;

 

子查询:

子查询解决什么问题: 一个查询查询不到结果的时候,可以使用子查询来丰富查询的条件

子查询的格式: 用一个小括号包含,然后在里面写sql语句

注意事项:

1. 一定要有小括号
2. 一定要注意你的书写格式
3. 子查询可以放在select,from ,where having,order by 后面
4. 子查询一定不能放在group by的后面
5. 子查询和主查询可以不是同一张表,只要子查询的结果在主查询中能用就可以
5. 子查询中一般都不排序,但是在TOP-N中需要排序
7. 一般是先执行子查询操作,在执行主查询操作,但是在相关子查询中先执行主查询,在执行子查询
8. 对于子查询的结果,如果是单行只能用单行操作符,如果是多行,只能用多行操作符
9. 自查中的null处理

-- 子查询放在select后面

// select ename,job,(select sal from emp where empno=7566) 子查询 from emp;

-- from后面放置子查询 重点要掌握的

//select * from (

  select ename,job,sal from emp
);

-- where 后面跟子查询   ——-- 一定要注意的地方: 子查询不能过多的去嵌套,一般嵌套三层,加多了就影响性能

//select * from
emp where sal > (
  select sal from emp where ename = (
    select ename from emp where empno = 7566
)
);

-- order by 后面跟子查询  order by 后面不能跟子查询,语法上是可以跟的,但是跟了不起作用  -- group by 后面不能跟子查询

//select * from emp order by (select count(*) from emp where ename='SCOTT');

//select * from emp order by (select empno from emp where ename='SCOTT') desc;

//select * from emp order by (select count(*) from emp where ename='SCOTT') desc;

--子查询和主查询可以不是同一张表,只要子查询的结果在主查询中能用就可以

 -- 查询部门名称为SAL的所有的员工信息

//select *

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

对于子查询的结果,如果是单行只能用单行操作符,如果是多行,只能用多行操作符

单行操作符: ><= <= !=

多行操作符 int not in any all

//select * from emp where deptno in(

    select deptno from emp where ename='SCOTT' or ename='CLARK'
  );

//select * from emp where deptno not in(

    select deptno from emp where ename='SCOTT' or ename='CLARK'
  );

//select * from emp where sal > any(

    select sal from emp where deptno = 30
  );

 

集合:借鉴:http://1632004.blog.163.com/blog/static/29991497201282653334529/

注意:集合运算的时候必须要保证每一个集合拥有同样多的列数,并且每一列的类型必须要一致,如果不一致可以使用to_char(null)或者to_number(null) 来补齐,补齐的时候一定要注意类型

课堂练习:

1、找到员工表中工资最高的前三名

//select rownum r,empno,ename,sal

  from (select empno,ename,sal from emp order by sal,ename,empno

  desc)
  where rownum<=3

rownum  ——伪列   可用来做表的序列号

1、它是按照默认的顺序生成,一单生成后不在发生变化

2、它只能使用<  <=,不能使用>   >=

//select * from (
  select rownum r,empno,sal
  from (
  select rownum,empno,sal
  from emp
  order by sal desc
  )
  where rownum < 5
  )
  where r > 2;

rowid  ——也是伪列 返回的是地址,可用来检索

2、找到员工表中薪水大于本部门平均薪水的员工

//select empno,ename,sal, (select avg(sal) agv from emp where deptno = e.deptno group by deptno) avgsal

  from emp e,(select deptno,avg(sal) agv from emp group by deptno) t
  where e.deptno = t.deptno and e.sal > t.agv

//select empno,ename,sal,(select avg(sal) from emp where deptno = e.deptno) avgsal

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

3、统计每年入职的员工(不能用子查询)

//select count(ename) total,sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980" from emp;

表相关:

 insert 插入数据

插入一行完整的数据:insert into emp (empno,ename) values (1,'ss');

insert into empvalues (插入的数据)  直接插入值,每一列都要有

更新表里的数据:update

//update emp set sal=1300 where empno=2015;

//update emp set job ='值',mgr='值' where empno= 2015;

删除表里的数据:

//delete from emp where empno =2015;

复制表结构:

//create table 表名 as select * from emp where 1=2;

复制表:

 //create table 表名 as select * from emp;

根据自己的需求创建一张表:

//create table 表名

  (id number,name varchar(20),pwd varchar2(20))

删除表:

//drop table 表名;

//delete from 表名;

//truncate table 表名;

修改表里列的名字:

//alter table 表名 rename column 名字 to 修改的名字;

修改表的类型:

//alter table 表名 modfy 列名 类型;

删除表里的某一列:

//alter table 表名 drop column 列名;

向表里追加一列:

//alter table 表名 add 列名 类型;

修改表名(重命名):

//rename 表名(原) to 表名:

delete 和truncate 的差别:

1、delete是DML操作,truncate是DDL操作

2、delete会产生碎片,而truncate不会

3、delete不会真正的删除表数据,而truncate会将整个表删除以后再从新新建一个表

4,、delete不会释放空间,而truncate会

事物:

将多个操作做成一个原子,要么这几个操作都成功,要么都失败

事物的开启和关闭

  开启:

  显示开启:start transaction

  隐示开启:执行第一个DML的时候会开启事物

  关闭;

  显示关闭:commit rollback

  隐示关闭:非正常关闭

创建闪回点:savepoint a;

回滚到闪回点:rollback to a;

其他4个对象:

视图:

是一个虚拟化的表,是一个逻辑表,本身不包含数据

创建一个视图:

//create view 名 as select * from emp;

删除一个视图:

//drop view 名

清空回收站:purge reyclebin

创建视图要给用户授权:

用SYS给Scott用户授权:

grant create view to scott;

移除授权:

revoke create view from scott;

索引:index

主要用来优化查询效率 

create index 名 on 表名( 列名)

注意:

通过查看索引的条用日志才能看到是否调用了索引

索引使用用于经常发生变化的列

索引可以提高查询的效率,但是不适应于经常变化的列上创建索引

序列:

一组由oracle来负责维护的数组,主要用来做ID的自动增长

创建序列:sequence

create sequence 名

序列的两个成员变量:nextval(取下一个值),currval(当前的值)

//inser into emp10 (empno,ename) values(mgse.nextval,'ss');

//select mgse.currval from dual;

删除序列:drop sequeale 名;

复杂的序列创建:

create sequence 名

increment by 2  增加2

start with 100  从100开始

minvalue 100 最小100

maxvalue 200 最大200

nocache  不缓存

同义词: synonym  别名

主要用来给另外一个用户的表取别名

create synonym emp,foremp;

Oracle中表的约束条件

1、主键约束:用来唯一表示一行记录

2、非空约束:用来控制某一列不能为空  not null

3、检查约束:用来控制某一列的值只能是固定几个

4、外键约束:别约束的这一列的值是另一个表的主键

5、唯一性约束:有些值唯一