查询当前用户: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、唯一性约束:有些值唯一
原标题:Oracle 数据库二 基本查询
关键词:数据库