Oracle(一)

 

1.连接数据库：sqlplus sys/test(密码) as sysdba;管理员身份登录

             sqlplus / as sysdba

     解锁用户：alter user scott account unlock;

                  alter user scott identified by 新密码                

2.conn soctt/soctt 切换到其它用户

     查看所有的用户： select username, account_status from dba_users;

3.show user查询当前用户

4.查询当前用户下的表：select * from tab;

5.查看表的结构：desc table(表名)

6.设置行宽和列宽： set linesize 150

                  col ename for a8 (表示八个字符)

                     col sale for 9999（表示四位数字）

7.单行注释－－

8.多行注释/*  */

9.编辑上一次输入的sql语句: ed命令

10.sql中的空值：sal is (not) null  当表达式中有空值时要整个表达式为空，要采用nvl(comm,0)滤空，当comm字段为空时采用0作为其值

   例句：

    10.1.  select empno as "员工号",ename "姓名",sal "月薪",comm 奖金 ,sal*12

           年薪,sal*12+nvl(comm,0) 年收入 from emp

    10.2   select *  from emp where comm is null

11. DISTINCT：相同的只取一次 作用于后面所有的列

            select DISTINCT deptno,job from emp;（当deptno,job同时相等时为相同）

12.清屏：host cls

13.连接符：一些与数据库表无关的操作要借助dual表（目的是为了遵从规范要求，有  

select就必须有from） select concat('hello','world') from dual;

                     select 'Hello'||' World' from dual;

                     select 3+2 from dual;

14.保存sql语句：save c:\a.sql

15.导入sql语句：@c:\a.sql

16.spool 开始录屏   spool off 录屏完毕保存

17.oracle 采用的是从右至左的方式解析where

          select * from emp where deptno=10 and sal>3000的效率没有

          select * from emp where sal>3000 and deptno=10 高

18.*最好改成字段名效率会更高（9i之后的差别很小）

19.查看系统参数 select * from v$nls_parameters

   修改日期格式 alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

20.between and : 在区间上包含边界（小值在前，大值在后）

                 select *from emp where sal between 2000 and 3000;

21.in:在集合中： select * from emp where deptno=10 or deptno=20;

                select * from emp  where deptno in (10,20);

                      select * from emp  where deptno  not in (10,20);

22.like:模糊查询 %任意字符 _一个字符

              查询名字以S打头的员工

        select *  from emp where ename  like 'S%';

              查询名字是4个字的员工

              select * from emp where ename like '____';

              查询名字中含义下划线的员工

              select * from emp where ename like '%\_%' escape '\'（定义一个转义字符）

23. oracle 自动开启事务 rollback可以回滚

24.排序：order by后面：列名，别名，表达式，序号

              按照员工的薪水排序

              select * from emp order by sal;

              select empno,ename,sal,sal*12 年薪  from emp order by 4 desc

              select empno,ename,sal,sal*12 年薪  from emp order by 年薪 desc

                上面两名效果一样

           select * from emp order by deptno,sal;先按部门排，再在部门内部       

              按薪水排

        多列时，desc只作用于离他最近的一列

        select * from emp order by deptno desc,sal desc

        查询员工信息，按照奖金排序

        select * from emp order by comm;

        解决降序时空行排在前面

        select * from emp order by comm desc nulls last;

25.a命令可以在前一条sql语句后加入新的条件

26.字符函数：select upper('hellow world') 转大写, lower('Hellow world') 转小写,     

                    initcap('hellow world') 首字母大写 from dual

           select substr('Hello World',3) 值一,substr('Hello World',3,4) 值二  from dual;

           select length('中国') 值一,lengthb('中国') 值二 from dual

              instr在母串中查找子串

            select instr('Hello World','ll') from dual;

                    lpad rpad 左右填充

         select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充from  dual;

              trim:去掉某个字符

            select trim('H' from  'Hello WorldH') from dual;

              replace替换

         select replace('Hello World','l','*') from dual;

               四舍五入

         select TRUNC(45.926,2) 值一,TRUNC(45.926,1) 值二, TRUNC(45.926,0)  

                  值三, TRUNC(45.926,-1) 值四,TRUNC(45.926,-2) 值五from dual

              截断

         select TRUNC(45.926,2) 值一,TRUNC(45.926,1) 值二, TRUNC(45.926,0)

                  值三, TRUNC(45.926,-1) 值四,TRUNC(45.926,-2) 值五 from dual

27.日期函数：select sysdate-1 昨天, sysdate, sysdate+1 明天from dual;

             两个时间只能减不能加，可以减数字单位是天

  利用months_between计算员工的工龄

  select ename,months_between(sysdate,hiredate) 值一,  (sysdate-hiredate)/30 值二from emp;

  add_months；计算123月后是哪一天

select add_months(sysdate,123) from dual;

计算本月的最后一天

select last_day(sysdate) from dual;

计算下一个星期一

select next_day(sysdate,'星期一') from dual;

对日期进行四舍五入

select round(sysdate,'MONTH') ,round(sysdate,'YEAR') from dual;

对日期进行截断

select trunc(sysdate,'MONTH') ,trunc(sysdate,'YEAR') from dual;

隐式转换的前提：被转换对象是可以转换的(最好少用隐式转换)

显式转换：

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

        select to_char(sysdate,'yyyy-mm-dd"今天是"day') from dual;

查询员工的姓名和薪水，薪水：货币代码 两位小数 千位符

        select ename, to_char(sal,'L9,999.99')  from emp;     

28.普通函数

     nvl2(a,b,c) 当a=null时，返回c;否则返回b 

        select ename,sal,comm, sal*12+nvl2(comm,comm,0) from emp;  

     nullif(a,b) 当a=b时,返回null; 否则返回a

        select nullif('abc','abc'),  nullif('abc','abcd') from dual;

给员工涨工资,根据职位涨工资 总裁1000 经理800 其他400

Case: select ename,job, sal 涨前工资, case job when 'PRESIDENT' then sal+1000

           when 'MANAGER' then sal+800 else sal+400 end 涨后工资 from emp;

        Decode: select ename,job, sal 涨前工资, decode(job,'PRESIDENT',sal+1000,

'MANAGER', sal+800,sal+400) 涨后工资  from emp;

29.多行函数：

           组函数会自动滤空，可以采用以下方式阻止其滤空

                select count(*), count(nvl(comm,0)) from emp;

求各个部门的平均工资

                select deptno,avg(sal) from emp group by deptno;

group by的要求:在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中

select deptno,job,avg(sal)from emp group by deptno,job

group by有多列：先按照第一列分组，如果相同再按照第二列分组，过滤分组: having

           求各个部门的平均工资,且平均工资大于2000的部门

select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>2000;

group by 的增强：roollup

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

break on deptno skip 2    break on deptno相同只显示一次，skip2不同部门相隔两行

break on null  取消上面的设置

30.多表查询：

      等值连接：

select * from emp,dept where emp.deptno=dept.deptno

   不等值连接：

              查询员工的工资级

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

外连接操作:当where条件不成立时，仍然希望在结果中包含某些不成立的记录

左外连接:where e.deptno=d.deptno不成立时，等号左边所代表的表的记录仍然显示

         写法：where e.deptno=d.deptno(+)

    右外连接:where e.deptno=d.deptno不成立时，等号右边所代表的表的记录仍然显示

         写法：where e.deptno(+)=d.deptno

查询员工信息： ***的老板是****

        自连接：通过表的别名，将同一张表视为多张表，再使用对应的连接操作

自连接：一般只适用于小表

        select e.ename||'的老板是'||b.ename from emp e,emp b  where e.mgr=b.empno;

层次查询;

同一张表的前后两次操作进行连接  level:伪列

select level,empno,ename,mgr from emp connect by prior empno=mgr  start with mgr is null