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后台进程写入重做日志文件(在外存或磁盘中)。