oracle数据操作语句

--desc 表名 描述表的内容     
desc emp;  
 
--加上数学表达式和列名  ""保持格式  
select ename "name space", sal*12 year_sal from emp;      
 
select 2*3 from dual;  
 
select sysdate from dual;  
 
--空值的数学表达式 结果都是空值  
select ename, sal*12 + comm from emp;     
 
--"||"字符串连接 单引号中的是字符串，字符串中的单引号，''表示  
select ename||sal from emp;       
 
--distinct 修饰两个字段  
select distinct deptno, job from emp;  
 
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 from emp where comm is null;  
 
 
--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句  
--正常断开连接 提交 非正常断开连接 回滚  
 
select ename, sal from emp where sal not in (800 ,1500);  
 
-- 模糊查询  
select ename, sal from emp where ename like '_A%';  
select ename, sal from emp where ename like '%\%%';  
 
--转义字符  
select ename, sal from emp where ename like '%$%%' escape '$';  
 
--排序  
select * from dept order by deptno desc;  
select ename, sal from emp order by deptno asc, ename desc;  
 
--函数  
select lower(ename) from emp;  
select substr(ename,1,3) from emp;  
select round(23.652, 1) from dual;  
select to_char(sal, '$99,999.999') from emp;  
select to_char(sal, 'L000,000.000') from emp;  
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;  
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;  
select ename, hiredate from emp  
    where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS');  
select sal from emp where sal > to_number('$1,250.000', '$9,999.000');  
select ename,sal*12 + nvl(comm, 0) from emp;  
 
--组函数  多行输入，只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行  
select max(sal) from emp;  
select sum(sal) from emp;  
select count(distinct deptno) from emp;  
 
--分组函数  
select deptno,avg(sal) from emp group by deptno;  
select deptno, job, max(sal) from emp group by deptno, job;  
 
--出现在select后，不是组函数的，必须出现在group by里  
--where语句对单挑记录进行过滤，分组后过滤用having  
 
--子查询 把一次查询结果当成表  
select ename from emp where sal = (select max(sal) from emp);  
select ename, sal from emp  
    join (select max(sal)max_sal, deptno from emp group) t  
    on(t.max_sal = emp.sal and t.deptno = emp.deptno);  
 
--自连接  
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.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 <> 'CLERK';  
 
--SQL 1999  链接条件不写在where中，where只负责过滤数据  
select ename, dname from emp cross join dept;  
select ename, dname from emp join dept on (emp.deptno = dept.deptno);  
select ename, dname from emp join dept using (deptno)；  
 
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);  
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);  
 
--左外连接  
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);  
--右外连接  
select ename, dname from emp e right join dept d on (e.deptno = d.deptno);  
select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno);  
 
--求部门中那些人薪水最高  
select ename, sal  from emp   
join (select deptno, max(sal) max_sal from emp group by deptno) t   
on (emp.sal = t.max_sal and emp.deptno = t.deptno);  
 
--求部门平均薪水的等级  
select deptno, grade from salgrade s   
join (select avg(sal) avg_sal, deptno from emp group by deptno) t   
on (avg_sal between losal and hisal);  
 
--求平均薪水最高的部门的部门标号  
select deptno, avg_sal from 
(select avg(sal) avg_sal, deptno from emp group by deptno)  
where avg_sal =  
(select max(avg(sal)) from emp group by deptno);  
 
--平均薪水的等级最低的部门的部门名称  
select dname, t1.deptno, grade, avg_sal from 
(  select deptno, grade, avg_sal from 
  (select deptno, avg(sal) avg_sal from emp group by deptno) t  
  join salgrade s on (t.avg_sal between s.losal and s.hisal)  
) t1  
join dept on (t1.deptno = dept.deptno)  
where t1.grade =   
(  
  select min(grade) from 
  (  select deptno, grade, avg_sal from 
    (select deptno, avg(sal) avg_sal from emp group by deptno) t  
    join salgrade s on (t.avg_sal between s.losal and s.hisal)  
  )  
)  
 
--建立视图  
create view v$_dept_avg_sal_info as 
select deptno, grade, avg_sal from 
  (select deptno, avg(sal) avg_sal from emp group by deptno) t  
join salgrade s on (t.avg_sal between s.losal and s.hisal);  
 
--变简单了  
select dname, t1.deptno, grade, avg_sal from 
v$_dept_avg_sal_info t1  
join dept on (t1.deptno = dept.deptno)  
where t1.grade =   
(  
  select min(grade) from v$_dept_avg_sal_info  
)  
 
--求部门平均薪水的等级  
select deptno, avg_sal, grade from 
  (select avg(sal) avg_sal, deptno from emp group by deptno)  
join salgrade s  
on (avg_sal between s.losal and s.hisal);  
 
--求部门平均的薪水等级  
select avg(grade), deptno from 
(  
  select deptno, ename, grade from emp  
  join salgrade s  
  on emp.sal between s.losal and s.hisal  
)   
group by deptno  
 
--雇员中有哪些人是经理人  
select ename from emp where empno in 
(select distinct mgr from emp);  
 
--不使用组函数，求薪水的最高值  
select distinct sal from emp where sal not in 
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));  
 
--平均薪水最高的部门的部门名称  
select dname from dept where deptno =  
(  
  select deptno from 
    (select avg(sal) avg_sal, deptno from emp group by deptno)  
  where avg_sal =  
  (select max(avg_sal) from 
    (select avg(sal) avg_sal, deptno from emp group by deptno)  
  )  
)  
 
--求比普通员工的最高薪水还要高的经理人名称  
select ename from emp   
where empno in (select distinct mgr from emp where mgr is not null) and sal >  
(  
  select max(sal) from emp where empno not in 
  (select distinct mgr from emp where mgr is not null)  
)  
 
--rownum只能和小于等于号联合使用  
--使用rownum 要关联表  
select ename from 
(select rownum r, ename from emp) where r > 10;  
 
--薪水最高的5个人  
select ename, sal from 
(select ename, sal from emp order by sal desc)  
where rownum <= 5;  
 
--薪水最高的第六个到第十个雇员  
select ename, sal from 
  (  
  select ename, sal, rownum r from 
    (select ename, sal from emp order by sal desc)  
  )  
where r >= 6 and r <= 10  
 
 
--SQL面试题  
--找出没选过“黎明”老师的所有学生姓名。   
select sname from s   
join sc on (sc.sno = s.sno)  
join c on (c.cno = s.cno)   
where c.cteacher <> 'liming';  
 
--列出2门以上（含2门）不及格学生姓名及平均成绩。   
select sname where sno in   
  (  
  select sno from sc where scgrade < 60  
  group by sno having count(*) >= 2  
  )  
 
--即学过1号课程有学过2号课所有学生的姓名  
select sno from sc where cno = 1 and sno in 
(  
  select sno from sc where cno = 2  
);  
 
--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句  
--正常断开连接 提交 非正常断开连接 回滚 



Oracle安装完后，其中有一个缺省的数据库，除了这个缺省的数据库外，我们还可以创建自己的数据库。

    对于初学者来说，为了避免麻烦，可以用'Database Configuration Assistant'向导来创建数据库。

    创建完数据库后，并不能立即在数据库中建表，必须先创建该数据库的用户，并且为该用户指定表空间。

    下面是创建数据库用户的具体过程：



    1.假如现在已经建好名为'news'的数据库，此时在F:\oracle\product\10.1.0\oradata\目录下已经存在news目录（注意：我的Oracle10g安装在F:\oracle下，若你的Oracle安装在别的目录，那么你新建的数据库目录就在*\product\10.1.0\oradata\目录下）。



    2.在创建用户之前，先要创建表空间：

    其格式为：格式:  create tablespace 表间名 datafile '数据文件名' size 表空间大小;

    如：

    SQL> create tablespace news_tablespace datafile 'F:\oracle\product\10.1.0\oradata\news\news_data.dbf' size 500M;

    其中'news_tablespace'是你自定义的表空间名称，可以任意取名；'F:\oracle\product\10.1.0\oradata\news\news_data.dbf'是数据文件的存放位置，'news_data.dbf'文件名也是任意取；'size 500M'是指定该数据文件的大小，也就是表空间的大小。



    3.现在建好了名为'news_tablespace'的表空间，下面就可以创建用户了：

    其格式为：格式:  create user  用户名 identified by 密码  default tablespace 表空间表;

    如：

    SQL> create user news identified by news default tablespace news_tablespace;

    默认表空间'default tablespace'使用上面创建的表空间。



    4.接着授权给新建的用户：

    SQL> grant connect,resource to news;  --表示把 connect,resource权限授予news用户

    SQL> grant dba to news;  --表示把 dba权限授予给news用户

    授权成功。