sql

DML(Data Manipulation Language)：insert,delete,update,select

DCL(Data Control Language):grant、revoke、audit、noaudit、rename

DDL( Data Definition Language):create、alter、drop 、

1.解锁用户hr

用sys用户登录输入：

    alter user hr identified by pswd account unlock；

    alter user scott account unlock;(解锁)

2.

(1).group by   对查询结果按指定列值对记录进行分组，即该列值相等的记录为一个组，通常会在每组中使用分组函数进行汇总。统计

(2). having   在分组之后的结果集中筛选满足指定条件的组  必须与group by组合使用。

    Group function 组函数

        max,min,avg ,count,sum函数

  分组函数注意事项：

    1>.分组函数只能出现在所查询的列、order by子句、having子句中，不能出现在where子句和group by子句中

    2>.除了count(*)之外，其他分组函数，包括count(column_name),都会忽略用于分组的列的值为null的行

               count  返回记录行的行数

               select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个. 

    3>.如果所查询的列同时包含列、表达式、分组函数，那么这些列、表达式都必须出现在group by子句中。

    4>.在分组函数中可以指定all和distinct选项。其中，all是默认选项，表示该函数作用于所有的行(包括值重复的行)而distinct则只作用于不同的行。如count(distinct country_id)

       select count(distinct deptno) from emp;--统计共有多少种deptno

    5>.having子句只与组有关，where子句只与记录行有关，在having子句的后面只能使用分组函数本身，而不能使用列的别名 ，限制分组结果时，必须使用having子句 ，而不能在where子句中使用分组函数来限制分组结果(即where子句中不能使用分组函数 )，因为where子句是在进行分组处理之前过滤数据行，而having子句则是在分组处理之后才过滤数据组。

    6>. 求薪水值最高的人的名字.

       select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.

       应如下求:

       select ename from emp where sal=(select max(sal) from emp);

       Group by语句应注意,

       出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.

     7>. 组函数最多嵌套两层

(3) select 语句：

       1>.计算数据可以用空表：比如：.select 2*3 from dual

       2>.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。在sql语句中加"  "可以保持你输入的格式不变

       3>. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。

 字符串连接：

 a.select region_id ||'是指'||region_name as 地区编码的意思 from regions  

 b. select ename||'sss' from emp;

   ||  连接字符串  ''中存放字符串  如果要想显示带有单引号的字段 ,可以这么写：

   select ename||'s''ss' from emp;

   

4>.  select ename from emp where ename like '%$%%' escape '$';

--选出ename中含有%的数据  $是自定义的转义字符 默认的是 \

5>.select ename, sal, hiredate from dmp where hiredate > '20-2月-81';

        --入职年份在81年2月20日之后的数据

select...from..where ...group by..having...order by...(执行的顺序：先取数据，取完数据进行过滤（where），过滤完了进行分组（group by），分组完了再对分组后的结果进行过滤（having），最后的结果进行排序（order by））

(4)distinct

        select deptno from emp;

        select distinct deptno from emp;

        select distinct deptno from emp;

        select distinct deptno ,job from emp

        去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。

(5)sql 函数：

单行函数：

select ename from emp where lower(ename) like '_a%';

--先将ename转化为小写，然后再选择其中第二个字母是a的数据  等价于：

--select ename from emp where ename like '_a%' or '_A%';

select substr(ename, 2, 3) from emp;

--截取ename所有的元素从第二个字母开始长度为3的字符串

select chr(65) from dual;

--chr()将ascII码转化为相应的字母

select ascii('A') from dual;

-- ascii() 将字母转化为相应的ascii码

select round(34.334) from dual;

--round() 读数据四舍五入（结果显示34）

select round(34.334,2) from dual;

--四舍五入到小数的第二位（显示结果为34.33)

round(34.334,-1) 

--显示结果为30

 select round(avg(sal),2) from emp;

           --结果:2073.21

转化数字的显示格式

select to_char(sal,'$99,999.999') from emp;

select to_char(avg(sal),'99999999,99') from emp;

--to_char() 将显示的结果显示为指定的格式 ，'$99,999.999'其中9代表一位数字 结果显示中如果相应位没有数字就不显示，有就显示，‘,’代表千位符 ‘.’ 代表小数点{显示结果举例：$1,600.000} 这种函数在对齐数据时非常有用

select to_char(sal,'L99,999.999') from emp;

--  结果格式为￥1,600.000   (L:代表本地符号)

select to_char(sal,'L00,000.000') from emp;

--将结果中相应位没有的数字补0 如￥01,600.0000

<对数字的转化非常的少用，因为这可以在java中处理>

转化日期的显示格式：

select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;

--12小时格式

select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;

--小时数是24进制显示

select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:45','YYYY-MM-DD HH:MI:SS');

--to_date(b,a)将b按照a的格式进行解析 即上句中将1981-2-20 12:34:45按照YYYY-MM-DD HH:MI:SS这种格式进行解析

select sal from emp where sal > to_number('$1,200.000','$9,999,999');

-- to_number(b,a) 将特定的字符串转换为相应格式的数字

处理空值：

select ename, sal*12+nvl(cumm,0) f rom emp;

--nvl(a,0)  如果a的值是空则用0代替它，否则用a的值进行计算

(6).Oracle表连接和子查询练习：

--求部门中哪些人的薪水最高：

select ename, sal from emp 

join (select max(sal) max_sal, deptno from emp group by deptno) t 

on(emp.sal =t.max_sal and emp.deptno = t.deptno);

--求部门平均薪水的等级：

select deptno, avg_sal, grade 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 deptno, avg(grade) from 

(select deptno, grade, sal from emp e join salgrade s on (e.sal between s.losal and hisal )) t 

group by deptno ;

--雇员中哪些是经理人：

select ename,job, mgr from emp where empno in (select distinct mgr from emp);

--不用组函数求薪水的最高值：（用自连接）

select sal from emp where sal not in ( select e1.sal from emp e1,emp e2 where e1.sal < e2.sal)

老师写的：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 deptno, avg_sal from

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

where avg_sal=

(select max(avg_sal) max_val from

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

-----------------------***********

组函数是可以嵌套的，但是最多嵌套两层 ：

如上面的可以写成：

select deptno, avg_sal from

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

where avg_sal=

(select max(avg(sal)) max_val from emp group by deptno)

----------------------------*********

--求平均薪水最高的部门名称：

select dname from dept d join

(

       (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)

      )

) a on(d.deptno = a.deptno)

另一种写法：

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 dname from dept where deptno =

(select deptno from 

 (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

where grade =

       (select min(grade) from

          (select avg(sal) avg_sal,deptno from emp group by deptno) t

join salgrade s on(t.avg_sal between s.losal and hisal)

       )

)

老师：

select dname,t1.deptno, grade avg_sal from 

 (

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  ) t1 

join dept on(t1.deptno = dept.deptno)

where t1.grade =

     (

         select min(grade) from

           (select avg(sal) avg_sal,deptno from emp group by deptno) t

          join salgrade s on(t.avg_sal between s.losal and hisal)  

         ) 

select dname,t1.deptno, grade avg_sal from 

(

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  ) t1 

join dept on(t1.deptno = dept.deptno)

where t1.grade =

     ( 

         select min(grade) from

           (

  select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and hisal)

  )

       )

       

       

多列子查询：

 查询公司中与employee_id=198 的雇员的工资、岗位都相同的员工的信息：

 select first_name,last_name ,salary,job_id

from employees 

where (salary,job_id) = (select salary,job_id 

                         from employees

                         where employee_id = 198)

                         

查询每个部门中最高工资的雇员信息：

select distinct department_id,

     (select max(salary) from employees b

       where b.department_id=a.department_id)  max_salary 

from employees a

order by department_id     

等价于：

select department_id,job_id,salary

           from employees a

           where salary=(select max(salary) from employees b

                          where b.department_id = a.department_id)

           order by department_id                     

                         

--求比普通员工的最高薪水还要高的经理人的名称：

select ename form emp

where empno in (select distinct mgr from emp where mgr is not null)

and

sal>

( 

  select max(sal) from emp whereempno not in 

     (select distinct mgr from emp where mgr is not null)

)

3.表的连接：

相等连接往往是通过主键、外键关系建立的，而不相等连接时相当的随意的，只要在数据类型上可以比较即可。

(1).自连接 （给自己起一个别名，当做两张表的链接）：

     select e1.ename, e2.ename from emp e1,emp e2 where e.mgr = e2.empno;

(2).等值链接 ：

select ename, dname from emp join dept on( emp.deptno = dept.deptno)

--等价于 select ename, dname from emp ,dept where emp.deptno = dept.deptno;(旧的语法)      新的语法通常在where中只写过滤条件

(3).非等值链接 ：

slect ename,dname, grade from

     emp e join dept d on(e.deptno=d.deptno)

     join salgrade s on(e.sal between s.losal and s.hisal)

     where ename not like '_A%';

把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。

(4).外连接：

a.左外连接：会把左边这张表多余数据显示出来。

     select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer

     

b.右外连接：

   select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。

      

c.全外连接：既把左边多余数据，也把右边多余数据拿出来。

     select ename,dname from emp e full join dept d on(e.deptno =d.deptno);

     

      d.using 简化外连接：

select e.first_name,d.department_name from employees e

inner join departments d

using(department_id)

where e.employee_id=177

 注意：如果希望在所查询的列中包括用于连接的列，则只能使用该列的列名，如上例连接的department_id,在查询中只能用department_id 而不能用d.department_id;

     在using子句中的连接列前面也不能使用表明限定词即：不能用using(d.department_id)代替using(department_id)

4.集合查询：

 语法：

 select 语句1 

 [union all  | union  | minus  | intersect]

 select 语句2

 order by column_name

 union all: 返回两个结果集的所有的行，包括重复行 即并

 union    ： 返回两个结果集的所有行， 不包括重复行 即并

 minus    : 返回第一个结果集中有但是第二个结果集中没有的行，即差

 intersect: 返回两个结果集中都有的行，即 交

 注意：a.两个结果集的列的名称可以不同，最后结果集中的列名采用的是第一个结果集中的列名称

       b.只能有一个order by子句，且只能出现在最后一个select 语句的最后面。 order by子句中只能使用第一个select 语句所查询列的列名或者别名，如果列名cc.与第二个select 语句的列名重复，则必须使用别名或列的次序号

       d.查询嵌套最多嵌套255层   

5.子查询：

(1).create table中使用子查询：

   create table

   emp(emp_id,f_name,h_date,sal,dept_id)

   as

   select employee_id,first_name,hire_date,salary,department_id

   from employees

   where department_id in (90,110)

   order by department_id,employee_id;

(2).create view 中使用子查询

首先 创建一个视图(create view V$(再加上你的视图名称)){权限不足时可这么做 conn sys/tiger as sysdba; 

grant create table,create view to scott}

 

create  or replace view v$dept_avg_sal_info as

select deptno,grade,avg_sal from 

     (select avg(sal) avg_sal,deptno from emp group by deptno) t

  join salgrade s on(t.avg_sal between s.losal and 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

       )

       

(3).insert语句中使用子查询：

    insert into emp

    select employee_id,first_name,hire_date,salary,department_id

    from employees

    where department_id =20;

    如果要针对某些列插入数据，其完整语法如下：（一定要有主键列和not null列）

     insert into emp(employee_id,first_name,hire_date,salary,department_id)

    select employee_id,first_name,hire_date,salary,department_id

    from employees

    where department_id =20;

(4).delete语句中使用子查询：

    delete from emp

    where sal>(select avg(max_salary) from jobs);

(5).update语句中使用子查询：

    update emp

    set (h_date,sal)=

    (select h_date,sal from emp where emp_id = 205)

    where dept_id =20;

6.insert

   insert into table [column1,.....]  values ( value1,....)  

   如果数字为插入数据，可以直接提供数字数据；如果为字符列或者日期插入数据，则必须使用单引号。如果字符串中本来就有单引号，则要在其前面再加一个单引号如：“亚洲’asia”写成“亚洲‘’asia”。日期要符合默认的日期格式，否则用to_date函数进行格式转换

7.update

  update tablename

  set column1 = value1[,column2 = value2]

  [where condition]

  如果数字为更新数据，可以直接提供数字数据；如果为字符列或者日期更新数据，则必须使用单引号。如果字符串中本来就有单引号，则要在其前面再加一个单引号。

  日期要符合默认的日期格式，否则用to_date函数进行格式转换  

8.delete

  delete from tablename

  [where condition]

9.事务

  事务是用户定义的一组操作序列，由一条或多条相关的sql语句组成，是数据库应用程序的基本逻辑单位。一般来讲一个应用程序是由一个或多个事务组成的。事实上，事务中的sql语句仅限制于dml命令，而ddl和dcl语句是不能被回退的（即事务对其不起作用，他们会自动提交事务）  

 1>.commit   提交

 2>.rollback  回滚

 3>.savepoint 设置保存点

 4>.rollback to savepoint spname/rollback to spname   回滚到保存点

 5>.set transaction  设置事务的属性

                (1).set transaction read write

                用于设置可读写的事务。在这种事务中可以执行dml语句来更新数据。这是事务的默认设置。

                (2).set transaction read only

                用于设置只读事务，在只读事务中不能用dml语句来更新数据。可以将数据库“冻结”到该事务开始的那一点上，即查询到的是已经存在于数据库中的数据，即便在此同时其他事务更改并提交了数据库中的数据。

                (3).set transaction isolation level read committed

                用于设置“读已经提交事务”的隔离等级。在此隔离等级（默认的隔离等级）下，事务中每个dml语句所操作的数据，是在该语句开始之前已提交了的数据，它提供语句级的读一致性。

                (4).set transaction isolation level serializable

                用于设置"串行化事务”的隔离等级。在serializable隔离等级下，事务中每个dml语句所操作的数据，是在该事务开始之前已提交了的数据，并且可以执行dml语句来更新数据库中的数据，还可以查看到更新的结果。提供事务级的读一致性。

 6>.set constraints 设置可延迟约束的检验时机

 set constraints {constraints_name1[,constraints_name2].....|  all}

 {deferred | immediate};

 all 表示该事务所涉及的所有可以延迟的约束；immediate 表示在媒体dml语句之后就立即检验；deferred（默认选项）表示在提交事务的时候才检验。

 在创建表的时候就可以创建约束并指定是否可以用set constraints 语句进行延迟检验

 如：

  create table test (

 a   number not null,

 constraint pk_a primary key(a) validate,

 constraint ck_a check(a>0) deferrable initially immediate);

7>.事务的acid属性（atomicity[原子性]、consistency[一致性]、isolation[隔离性]、durability[持久性]）

8>.在oracle中通过执行insert、update、delete语句修改数据时，oracle都会生成两种重要信息：undo和redo

  在数据库由于系统故障或错误而在事务执行期间崩溃之后，再次启动数据库时：smon后台进程就会根据undo信息(位于undo表空间中的数据文件)、redo信息(位于重做日志文件)负责对数据库进行恢复。

  在处理commit语句时，oracle会静重做日志缓冲区(在内存中)中的内容用lgwr后台进程写入重做日志文件（在外存或磁盘中）。