oracle从零开始学习笔记 三


高级查询
随机返回5条记录
select * from (select ename,job from emp order by dbms_random.value())where rownum<=5;
处理空值排序
select * from emp order by comm desc nulls last(first);
查询跳过表中的偶数行
select ename from (select row_number() over (order by name) rn,ename from emp) x where mod(rn,2)=1;
查询员工信息与其中工资最高最低员工
select enmae,sal,max(sal) over(),min(sal) over() from emp;
连续求和
select ename,sal,sum(sal) over(),sum(sal) over(order by name) from emp;//sum(sal) over(order by ename)指的是连续求和，以ename排序的。若有两个这样的窗口函数，以后面的排序为主
分部门连续求和
select deptno,sal,sum(sal) over(partition by deptno order by ename) s from emp;
得到当前行上一行或者下一行的数据
select enamel，sal,lead(sal) over(order by sal) aaa,lag(sal) over (order by sal) bbb from emp;
根据子串分组
select to_char(hiredate,'yyyy'),avg(sal) from emp group by to_char(hiredate,'yyyy');
确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;
trunc(sysdate,'X')时间截取函数，X表示参数
dd--截取今天
d-截取本周第一天
mm-截取本月第一天
y-年
hh-小时
mi-分钟
查询EMP员工表下每个部门工资前两名的员工信息
select deptno,ename,sal from emp e1 where (select count(*) from emp e2 where e2.deptno=e1.deptno and e1.ename!=e2.ename and e2.sal>e1.sal)<2 order by deptno,sal desc;
select * from (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc)rn from emp)where rn<3;
rownum 是伪列
一个表中的ID有多个记录，把所有这个ID的记录查出来，并显示共有多少条记录数。（华为面试题）
select id ,count (*) from tb group by id having count(*)>1;

数据字典
查询某用户下所有表
select table_name fom all_tables where owner='scott'
查询EMP表中所有字段（列）
select * from all_tab_columns where table_name='emp'
列出表的索引列
select * from sys.all_ind_columns where table_name='EMP';
列出表中约束
select * from all_comstraints where table_name='EMP';
在oracle中描述数据字典视图
select table_name ,comments from dictionary where table_name like '%TABLE%';

Oracle 数据类型
由于char是固定长度的，所以它的速度会比varchar2快得多！但程序处理起来麻烦一点，要用trim之类的函数把两边的空格去掉
varchar2一般适用于英文和数字，Nvarchar2适用于中文和其他字符，其中N表示Unicode常量，可以解决多语言字符集之间的转换问题
Number(4,2)指的是整数占两位，小数占2位
Number默认为38位
数据类型
number（p[,s]）p表示有效数据位数，s表示小数位
varchar2()在oracle有更好的兼容性，基本不用varchar
date     to_date('2015-01-01 13:14:15','yyyy-mm-dd hh24:mi:ss')分钟为mi因为sql中不区分大小写

Oracle 体系结构
DDL（改变表结构）
建表
create table test
（
ID number(5) primary key,--主键
name varchar2(10) not null,--非空
birthday date,
email varchar2(25) unique,--唯一
age number check(age between 0 and 150),--check约束
deptno number references emp(id) on delete cascade --外键     级联删除
）
create table emp3 as select * from emp where sal >1000;
显示表结构:describe test
删除表：drop table test
修改表名称：rename test to testing
--------------------对字段操作-----------------------------------
增加列 alter table test add address varchar2(40);
删除列 alter table test drop column address;
修改列名称 alter table test address
插入数据 insert into test values(1,'ling',3000,1)
               insert into test (eno,salary) values(3,3000)
修改数据 update test set ename='zhangsan' where eno=3;
删除数据 delete test （无条件删除所有数据,逐条删除）
               truncate test (不产生回退信息，速度快)
               delete from test where eno=3;
删除一张表重复记录，age,name相同即为重复记录，id是自增唯一的
delete from test where id not in (select min(id) from test group by  name,age);

DML(改变数据结构)
表间数据拷贝 insert into dept(id,name) select deptno,dnamem from dept;
update myemp set (job,mgr)=(select job mgr from myemp where empno=7556)where empno=7779;

merge into test2 
using test1
on(test1.eid=test2.eid)
when matched then update set name=test1.name,birth=test1.birth,sal=test1.sal
when not matched then insert (eid,name,birth,sal) values(test1.eid,test1.name,test1.birth,test1.sal);

约束
not null--非空约束
primary key--主键约束（不能重复，不能为空）
unique--唯一约束，值不能重复（空值除外）
check--条件约束，插入的数据必须满足某些条件
foreign key--外键
添加主键        alter table person add constraint person_pid_pk PRIMARY KEY(pid)
添加唯一约束 alter table person add constraint person_tel_uk UNIQUE(tel)
添加检查约束 alter table person add constraint person _age_ck CHECK(age between 0 and 150)
添加主-外键约束，要求带级联删除 alter table book add constraint person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;
删除约束 
alter table student drop unique(tel)
alter table book drop CONSTRAINT person_book_pid_fk
启用约束 ALTER TABLE book enable CONSTRAINT person_book_pid_fk;
禁用约束 ALTER TABLE book disable CONSTRAINT person_book_pid_fk;

视图
创建视图 CREATE VIEW 视图名字（字段） AS 子查询
CREATE VIEW empv20 (empno,ename,sal) AS select empno,ename,sal from emp where deptno=20;
高级视图
create or replace view empv20 (deptno,msal) as (select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20)) with check option constraint empv20_ck;

SQL优化
1.尽量少用IN操作符
2.尽量用NOT EXISTS 或者外连接替代NOT IN操作符
3.尽量不用“<>”或者“!=”操作符
4.在设计表时，把索引列设置为NOT NULL
5.尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
6.Where 字句中避免在索引列上使用计算
7.用“>=”替代“>”
8.利用SGA共享池，避开parse阶段
9.where后面的条件顺序要求
10.使用表别名，并将之作为每列的前缀
11.进行了显式或隐式的运算字段不能进行索引
12.UNION all代替union
13.其他操作
尽量使用packages
尽量使用cached sequence 来生成 primary key
很好的利用空间：如用varchar2数据类型代理char等
利用SQL优化工具：如SQLexpert，toad,explain-table,PL/SQL;OEM
14.通过改变oracle的SGA（数据库的系统全局区）的大小
 
 
 
笔试题
设有关系EMP(ENO,ENAME,SALARY,DNO)其中各属性的含义依次为职工号、姓名、工资、所在部门号， 以及关系DEPT(DNO,DNAME,MANAGER)其中各含义依次为部门号、部门名称、部门经理的职工号
1.请通过SQL语句创建表EMP、DEPT。
create table emp(eno number(5) primary key ,ename varchar2(5),salary number(8),dno number(3));
create table dept(dno number(3) primary key,dname varchar2(10),manager number(5));
2.试用SQL语句完成以下查询：列出各部门中工资不低于600元的职工的平均工资。
select avg(salary) from emp where salary>=600;
3.写出“查询001号职工所在部门名称”的关系代数表达式
select dname from dept where dno in (select dno from emp where eno='001')
4.请用SQL语句将”销售部“的那些工资低于600元的职工工资上调10%。
update emp set salary=salary*1.1 where sal<600 and dno=(select dno from dept where dname='销售部')
5.请用SQL语句查询”销售部“员工数量
select count (*) from emp group by dno where dno in (select dno from dept where dname="销售部")
 
 
数据库就先到这，其他的一些模块该文件内也有介绍http://pan.baidu.com/s/1o6iK00y
posted @ 2015-03-19 20:23  凌晨灵修  Views(254)  Comments(0)  Edit  收藏  举报