oracle数据库笔记.doc

《oracle数据库笔记.doc》由会员分享，可在线阅读，更多相关《oracle数据库笔记.doc（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle:SQL标准.select查询语法select columnName, .from tableNamewhere booleanExpgroup by groupVarhaving booleanExporder by orderVar;=DML:select * from tableName;员工表:empselect * from emp;desc emp; -查看表结构信息select empno, ename from emp;select ename from empwhere sal 1000;分组查询时,可以显示的信息必须满足以下两个条件之一:1.查询信息就是分组条件

2、2.查询信息被组函数影响avg() 平均值sum() 总和max() 最大值min() 最小值count() 计数select job from empgroup by job;select max(sal) from empgroup by job;子查询:将查询语句结果当做条件或表的情况,都叫子查询.select ename from emp;工资高于平均工资的员工名select ename from empwhere sal (select avg(sal) from emp);多表联合查询笛卡尔积select ename,dname fromemp, dept;select e.ena

3、me ename, d.dnamefrom emp e, dept dwhere e.deptno = d.deptno;查询每个工作岗位工资最高的员工名select job, max(sal) from empgroup by job;select ename from empwhere sal = (select max(sal) from empgroup by job);select e.ename, m.m_sal,m.job from emp e, (select max(sal) m_sal,job from empgroup by job) mwhere e.sal = m.m

4、_saland e.job = m.job;and/orselect ename from empwhere sal 1000 andjob = SALESMAN;select ename from empwhere sal 3000 ordeptno = 10;select * from tableNamewhere a = 2 and b 10and f = 50;=1.查询工作岗位是SALESMAN,且工资大于1500的员工名.select ename from empwhere job = SALESMANand sal 1500;2.查询每个部门的平均工资.select avg(sa

5、l) from empgroup by deptno;3.查询部门平均工资高于公司平均工资的部门编号.select deptno, d_sal from(select deptno, avg(sal) d_salfrom empgroup by deptno)where d_sal (select avg(sal) from emp);4.查询工资低于公司平均工资的员工名.5.查询直接下属最多的员工名.5.1 按照MGR字段分组5.2 查询分组后每组的数量select count(*) from emp;5.3 找出数据最多的组.5.4 以最多数据组为条件查询员工名6.查询每个员工的名字及其领

6、导的名字7.查询SCOTT所在部门的最高工资是多少.8.查询SCOTT所属岗位的最高工资是多少.Oracle数据类型数学类型number 任意数学类型number 整数,长度为8位number(6) 整数,长度为6number(6,2) 浮点数总长度为6,小数位2位integer 整数字符串类型varchar:变长字符串varchar(20)无默认长度char:定长字符串char(20)默认长度为1 char = char(1)varchar2:Oracle特有变长字符串*long:长字符串,可变长日期类型date:日期,年月日时分秒timestamp:时间戳精度为纳秒desc emp;+ -

7、 * / modselect ename, sal * 12from emp;select sal, mod(sal, 1000)from emp;select ename, length(ename)from emp;helloworldoracle特殊表dual哑表.哑表:没有数据,没有字段.直接返回所有查询数据.select hello world from dual;|select hello | | world from dual;select ename | |job from emp;日期处理:select hiredate from emp;日期格式化:to_char()sel

8、ect to_char(hiredate,yyyy-mm-dd HH24:mi:ss)from emp;当前系统时间:sysdateselect to_char(sysdate, yyyy-mm-dd HH24:mi:ss) from dual;日期转换:to_date()select to_date(2008-08-08,yyyy-mm-dd)from dual;查询所有01月入职的员工名select ename from empwhere to_char(hiredate,mm) = 01;select sysdate + 1 from dual;last_day()select last

9、_day(sysdate)from dual;select last_day(to_date(2007-02-10,yyyy-mm-dd)from dual;trunc() 截取select trunc(sysdate,yyyy)from dual;add_months()select add_months(sysdate,-12*30) from dual;select ename, hiredatefrom empwhere add_months(sysdate,-12*30) hiredate;特殊值:null.null与任意值计算,结果为nullnull无法比较大小null无法比较等值

10、select ename, sal* 12 + comm * 12from emp;select ename, commfrom empwhere comm 10000;2.查询工龄在30年以上且总年薪大于15000的员工名.select ename from empwhere add_months(sysdate,-30*12) hiredate andsal*12+nvl(comm,0)*1215000;3.查询12月入职,且有提成工资的员工名select ename from empwhere to_char(hiredate,mm)= 12 andnvl(comm,-1) = 0;se

11、lect ename, comm from empwhere to_char(hiredate,mm)= 12;4.查询领导层中工龄超过31年的员工名.select nvl(mgr,-1) from empgroup by mgr;select ename, empno from empwhere add_months(sysdate,-12*31) hiredate;select e.ename,e.empno from emp e, (select nvl(mgr, -1) mfrom emp group by mgr) mgwhere add_months(sysdate,-12*31)

12、 e.hiredate ande.empno = mg.m;5.查询所有员工工龄超过30年的部门名select d.dname,e.hiredatefrom emp e, dept dwhere e.deptno = d.deptnoand add_months(sysdate,-12*30)hiredate;select deptno, hiredatefrom emp;select d.dname from dept d,(select max(hiredate) m_h,deptnofrom empgroup by deptno) mwhere add_months(sysdate,-1

13、2*30)m_h andd.deptno = m.deptno;having:selectfromwheregroup byhavingorder byselect max(d.dname)from dept d, emp ewhere d.deptno = e.deptnogroup by d.deptnohaving max(e.hiredate) hiredate andempno in(select nvl(mgr,-1) from empgroup by mgr);not inselect enamefrom empwhere add_months(sysdate,-12*31) h

14、iredate andempno not in(select nvl(mgr,-1) from empgroup by mgr);replace() substr()select ename from emp;select replace(ename,KING,XXX)from emp;当表中的数据发生变动时,数据库自动开启事务,这个事务只在当前会话(一次有效连接)有效.如果事务不提交,数据变化不会同步到其他的会话当中.提交事务:commit;回滚事务:rollback;增加insert into tableName(columnN)values(columnValue)insert into

15、 empvalues(5000,TEST,SALESMAN,7698,sysdate,2000,null,20);insert into emp(empno,ename,sal,comm, hiredate,deptno,job,mgr)values(5001,TTT,3000,100,sysdate,20,SALESMAN,7698);insert into emp(empno,deptno)values(5002,20);删除delete from tableName where .;delete from emp;delete from empwhere empno = 5000;tru

16、ncate table emp;备份create table emp_bak asselect * from emp;恢复备份数据insert into empselect * from emp_bak;修改update tableNameset columnName=columnValue,.where .;update empset sal=sal+100;update empset sal = sal-100, comm = nvl(comm,0) * 1.2where deptno=20;=drop table tableName;create table tableName(colu

17、mnName columnType,.columnName columnType);drop table studyTable;create table studyTable(col1 number(5),col2 number(5,2),col3 char(5),col4 varchar2(5),col5 date);编号 姓名 性别 电话 QQ EmailPK:Primary Key 主键约束业务无关,唯一,非空FK:foreign key 外键约束当前列引用其他表的主键AK:唯一建 unique当前列值不能重复(不包括null)CK:check约束 条件约束非空约束|选择约束create

18、 table studyTable(id number(4) primary key,name varchar2(200) not null,age number(3),sex varchar2(4) check(sex in (男,女),phone varchar2(18) unique);insert into studyTablevalues(1,zhangsan,30,男,123);insert into studyTablevalues(2,abc,30,女,1234);create table t_table(m number(4) references studyTable(id

19、);insert into t_table values(null);create table test_QQ(id number(18) primary key,qq_no number(18) not null unique,password varchar2(18) not null,login_name varchar2(200)not null,name varchar2(64),birthday date,address varchar2(200),sex varchar2(4)check(sex in (男, 女),qqemail_name varchar2(40)not nul

20、l unique,xxxx number(18) referencestest_QQ(id) not null);=索引:用于快速查询的结构使用树状结构保存信息.所有主键,唯一键所在列自动创建索引.视图:用户简化查询的工具固定化的查询语句.create view mgr_31 as(select e.ename name,e.empno idfrom emp e, (select nvl(mgr, -1) mfrom emp group by mgr) mgwhere add_months(sysdate,-12*31) e.hiredate ande.empno = mg.m);user_t

21、ables存储过程replace()函数max() min()触发器=模糊查询:select ename from empwhere ename like %LA%;select ename from empwhere ename like T%;select ename from empwhere ename like _L%;select ename from empwhere ename not like %L%;select * from empwhere sal != 1000;sal 1000区间条件查询:select ename, sal from empwhere sal 1500 andsal = 3000;select ename, sal from empwhere sal between 1500and 3000;