Oracle实战练习

#登录到sqlplus; 
#用户名:scott 密码:tiger

#或者在命令提示符下
sqlplus sys/dys as sysdba ;    #管理员登录到sqlplus

alter user scott account unlock;   #解开用户

#清空sqlplus屏幕中的记录
clear scr;
#查看表结构
desc emp;  

#select 语句
select ename, sal*12 from emp;
desc emp;
#表字段的别名
select ename, sal*12 as annual_sal from emp;

#在表中运用运算
select 2*3 from emp; #查出来的是14条记录, 因为emp表中有14条记录

#查看虚表dual
desc dual;

#在dual表中运算
select 2*3 from dual;

#查看系统时间
select sysdate from dual;   #显示一条记录

#使用""来保持原来的格式
select ename, sal*12 as"annual sal" from emp;

#查看表中的字段值为空的记录
select ename, comm from emp; #因为任何含有空值的表达式最后显示结果仍是空值

#字符串连接,使用||
select ename||sal from emp;
 
#distinct 查询,去除重复行
 select distinct deptno from emp;
 select distinct deptno,job from emp;   #两个在一起的组合重复的去掉,distinct 可以修饰一个或多个字段

#where过滤条件
#查询部门编号是10的员工信息
 select * from emp where deptno=10;
#查询名称是CLARKR部门员工信息
 select * from emp where ename ='CLARK';
 select ename, sal from emp where sal>1500;
#比较每个字符的ascii码谁大谁小
 select ename, sal from emp where ename>'CBA';
#查询工资在800和1500之间
 select ename, sal from emp where sal between 800 and 1500;
#或者
 select ename, sal from emp where sal>=800 and sal<=1500;
 
#空值处理
select ename, sal,comm from emp where comm is null;
select ename, sal,comm from emp where comm=null; #这样写是错误的
select ename, sal ,comm from emp where comm is not null;
#in查询
 select ename,sal, comm from emp where sal in (800,1500,2000);
 select ename, sal, comm from emp where ename in('SMITH','KING','ABC'); #表示查询符合在集合范围内的一个或者多个
 select ename, sal from emp where sal not in(800,1000);
#日期处理
 select ename, sal, hiredate from emp where hiredate>'20-2月-81';
 #或者
 select ename, sal, hiredate from emp where hiredate >'20-2月-1981';
 
 #and 处理, or处理
 select ename, sal from emp where deptno=10 and sal>1000;
 select ename, sal from emp where deptno=10 or sal>1000;
 
 #模糊查询
 select ename from emp where ename like'%ALL%';  #%百分号表示1个或者多个
 #查出名称第二个是A的
 select ename from emp where ename like'_A%';
 #如果是%,用转义字符
  select ename from emp where ename like'%/%%';
 #用escape识别转义字符
  select ename from emp where ename like'%$%%' escape '$';
 #按部门编号降序排列
  select * from dept order by deptno desc;
 #按empno升序排列
 select empno, ename from emp order by empno asc;  #默认是升序排列
 #过滤后再排序
 select empno, ename from emp where deptno<>10 order by empno asc;
 select ename, sal, deptno from emp order by deptno asc;
 #排序两个条件
 select ename, sal, deptno from emp order by deptno asc, sal ;
 
 #综合排序条件
  select ename, sal*12 annual_sal from emp where ename not like'_A%' and sal
  800 order by deptno;
 
  #SQL函数
  select lower(ename) from emp; #转换成小写
  #第二个字母是a的
   select ename from emp where lower(ename) like'_a%';
  #或者
   select ename from emp where ename like'_a%' or ename like'_A%';
 #截取字符串
  select substr(ename,2,3) from emp; #从第二个字符串开始,截取3个字符串
  select ascii('A') from dual;   #查出ascii码
  select round(23.652) from dual;  #四舍五入
 #四舍五入,保留小数点两位
  select round(23.652,2) from dual;
  select round(23.652,1) from dual;
  select round(23.652,-1) from dual;
 #把数字或者日期转换为某种形式
 select to_char(sal,'$99,999.9999') from emp;  #9代表一位数字
 #人民币
 select to_char(sal, 'L99,999.9999') from emp;
 
 #对日期进行转换
 select hiredate from emp;
 select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
 select to_char(sysdate, 'YYYY-MM-DD') from dual;
 
 select ename, hiredate from emp where hiredate> to_date('1981-2-20 12:34:36
','YYYY-MM-DD HH24:Mi:SS');

 select sal from emp where sal>to_number('$1,250.00','$9,999.99');
#计算年薪,如果comm是为null的话,则让其显示0
 select ename, sal*12 + nvl(comm, 0) from emp; 
 
 #max函数
 select max(sal) from emp;
 #min函数
 select min(sal) from emp;
 #avg函数
 select avg(sal) from emp;
 #avg函数,将其转换成to_char形式
 select to_char(avg(sal),'99,999.9999') from emp;
 #保留两位小数
 select round(avg(sal),2) from emp;
 select sum(sal) from emp;
 select count(*) from emp;
 select count(*) from emp where deptno=10;
 select count(ename) from emp;   #14条记录
 select count(distinct(deptno)) from emp; #3条记录
 
 #group by 语句
 select avg(sal) from emp group by deptno;
 select max(sal) from emp group by deptno, job;
 select ename from emp where sal=(select max(sal) from emp);
 select deptno, max(sal) from emp group by deptno;
 
 #having子句,使用having对分组进行限制
 select avg(sal), deptno from emp group by deptno;
 select avg(sal), deptno from emp group by deptno having avg(sal) >2000;
 select avg(sal) from emp where sal>1200 group by deptno having avg(sal) >15
00 order by avg(sal) desc;

#子查询
 select ename, sal from emp where sal=(select max(sal) from emp);
 select ename, sal from emp where sal> (select avg(sal) from emp);
 #下面这么写法是错误的
 select ename, sal ,deptno from emp where sal=(select max(sal) from emp group by
deptno);    #第 1 行出现错误:
#RA-01427: 单行子查询返回多个行

 select ename, sal from emp join(select max(sal) max_sal, deptno from emp gr
oup by deptno) t on (emp.sal=t.max_sal and emp.deptno = t.deptno);

#理解子查询的关键是把其当成一张表
#求出每个员工的上司是谁
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr=e2.empno;
select ename, dname, grade from emp e, dept d, salgrade s where e.deptno =
d.deptno and e.sal between s.losal and s.hisal and job<>'CLEAK';

#没有表连接,默认是笛卡儿积
select ename, dname from emp, dept;
#或者
select ename, dname from emp cross join dept;
#有连接条件
select ename, dname from emp,dept where emp.deptno = dept.deptno;
#或者
select ename, dname from emp join dept on(emp.deptno = dept.deptno);
select ename, dname from emp join dept using(deptno);  #使用using的条件是两个表中有相同的字段