--查询雇员工资中带8这个数字的
select * from emp where sal like '%8%'
--查询编号是7369,7499,7521,7799的雇员信息
select * from emp where empno=7369 or empno=7499 or empno=7521 or empno=7799 select * from emp where empno in(7369,7499,7521,7799) --查询雇员编号不是7369,7499,7521,7799的所有雇员信息 select * from emp where empno not in(7369,7499,7521,7799) --查询雇员编号为7369的雇员信息 select * from emp where empno =7369 --查询雇员编号不为7369的雇员信息 select * from emp where empno !=7369 select * from emp where empno <>7369
--查询雇员信息,按工资由低到高排序 select * from emp order by sal asc
--查询雇员信息,按工资由高到低排序 select * from emp order by sal desc
操作集合:
union:将两个记录合并,去掉重复项
select distinct deptno from emp union select deptno from dept; union:将两个记录合并,不去掉重复项
select distinct deptno from emp union all select deptno from dept; intersect:取两个集合的交集
select distinct deptno from emp intersect select deptno from dept; minus:去掉交集(集合A-(集合A和集合B的交集))
select deptno from dept minus select distinct deptno from emp ;
4高级查询
转换函数: to_char
select pname,to_char(birthday,'yyyy-mm-dd') from person; select to_char(sal,'L99,999') from emp;-->L本地伙伴符号 select to_char(sal,'$99,999') from emp;-->$:美元 select to_char(sal,'$000,000') from emp;
to_date
insert into person(pid,pname,page,birthday)values(3,'cc',21,to_date('1991-2-2','yyyy-mm-dd'));
连接查询
内连接:
select e.empno,e.ename,e.sal,d.dname from emp e, dept d where e.deptno=d.deptno;
select e.empno,e.ename,e.sal,d.dname from emp e join dept d on(e.deptno=d.deptno) 左连接
select e.empno,e.ename,e.sal,d.dname from emp e left join dept d on(e.deptno=d.deptno)
select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno=d.deptno(+);
右连接:
select e.empno,e.ename,e.sal,d.dname from emp e right join dept d on(e.deptno=d.deptno)
select e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno(+)=d.deptno; 交叉连接:笛卡尔乘积 select * from emp,dept
select e.empno,e.ename,e.sal,d.dname from emp e cross join dept d on(e.deptno=d.deptno)
全连接:
select e.empno,e.ename,e.sal,d.dname from emp e full join dept d on(e.deptno=d.deptno)
子查询:
--查询部门编号为10的员工中工资最高的员工的编号,姓名,工资
select empno,ename,sal from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10) 聚合函数:
count,sum,avg,max,min 分组:
group by
--查询平均工资>2000,的部门编号,部门名称，平均工资 --平均工资>2000的部门编号
select deptno from emp group by deptno having avg(sal)>2000 --查询平均工资>2000,的部门编号,部门名称
select deptno,dname from dept where deptno in(select deptno from emp group by deptno having avg(sal)>2000)
--查询平均工资>2000,的部门编号,部门名称，平均工资
select d.deptno,d.dname,avg(e.sal) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname having avg(e.sal)>2000
Oracle分页:
mysql: select * from emp limit 0,3
sqlserver: select top 5* from emp where empno not in(select top 0 empno from emp); 总记录数:14 分页单位:5
总页数:总记录数%分页单位==0?总记录数/分页单位:总记录数/分页单位+1
第一页: 前5条,使用rownum(行号)<6
select A.*,rownum from(select * from emp) A where rownum <6;
select * from (select A.*,rownum rn from(select * from emp) A where rownum <6) where rn>0;
第二页:rownum>5,rownum<11
select A.*,rownum from(select * from emp) A where rownum >5 and rownum<11; X
select * from (select A.*,rownum rn from(select * from emp) A where rownum <11) where rn>5; 第三页:
select * from (select A.*,rownum rn from(select * from emp) A where rownum <16) where rn>10;
开始位置=(当前页-1)*分页单位 结束位置=当前页*分页单位+1
select * from (select A.*,rownum rn from(select * from emp) A where rownum <结束位置) where rn>开始位置;
注意:Oracle中为列起别名可以加as,但为表起别名不能加as
/******************使用jdbc连接oracle********************* driverclass:oracle.jdbc.driver.OracleDriver url:jdbc:oracle:thin:@localhost:1521:orcl
//1.加载驱动
Class.forName(\
//2.获取连接 url=jdbc:oracle:thin:@IP地址:端口:数据库名称
Connection conn = DriverManager.getConnection(\\
//3.创建Statement或者PrepareStatement Statement stmt = conn.createStatement();
//4.执行增,删,改,(executeUpdate),查(executeQuery) ResultSet rs = stmt.executeQuery(\//5.遍历结果集 while(rs.next()){
int empno= rs.getInt(\
String ename= rs.getString(\System.out.println(empno+\
}
//6.释放资源 rs.close(); stmt.close(); conn.close();
5锁和表分区
Oracle中锁的优点:解决并发访问的问题 1.一致性 2.完整性
分类:行级锁和表级锁
行级锁是一种排他锁，防止其他事务修改此行 在使用以下语句时，Oracle会自动应用行级锁： INSERT UPDATE DELETE
SELECT … FOR UPDATE[wait second][no wait]
SELECT ? FOR UPDATE语句允许用户一次锁定多条记录进行更新 使用COMMIT或ROLLBACK语句释放锁 eg:
用户A:
select * from emp where empno=7934 for update[wait 5][nowait] 用户B:
update scott.emp set sal=sal+10 where empno=7934 不能更改 update scott.emp set sal=sal+10 where empno=7902 可以更改
表级锁:锁定整个表，限制其他用户对表的访问。 LOCK TABLE table_name IN mode MODE eg:
用户A:lock table emp in share mode; 用户B:
select * from scott.emp 可以
update scott.emp set sal=sal+10 where empno=7902 不可以
死锁:当两个事务相互等待对方释放资源时，就会形成死锁
Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁 eg:
用户A:lock table emp in share mode;
update emp set sal=sal+10 where empno=7902 用户B:lock table emp in share mode;
update scott.emp set sal=sal+10 where empno=7902
表分区的作用：
1.允许用户将一个表分成多个分区
2.用户可以执行查询，只访问表中的特定分区
3.将不同的分区存储在不同的磁盘，提高访问性能和安全性 4.可以独立地备份和恢复每个分区 表分区的类型 1.范围分区
以表中的一个列或一组列的值的范围分区
create table sale( sid number(4), sdate date,
scost number(10,2) )
partition by range(scost) (
partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000), partition p4 values less than (maxvalue) )
2.散列分区
允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行HASH函数决定存储的分区 将数据平均地分布到不同的分区
按empname的hash码划分3个分区:d1,d2,d3 create table employee( empId number, empname varchar(20), deptname varchar(20) )
partition by hash(empname) (
partition d1, partition d2, partition d3
)
按empname的hash码划分4个分区 create table employee2( empId number,
empname varchar(20), deptname varchar(20) )
partition by hash(empname) partitions 4 3.列表分区
允许用户将不相关的数据组织在一起 create table employee3( empId number,
empname varchar(20), empaddress varchar(200) )
partition by list(empaddress)