oracle数据库笔记.doc
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.查询信息被组函数影响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.ename 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_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 < 3 or d > 10and f = 50;=1.查询工作岗位是SALESMAN,且工资大于1500的员工名.select ename from empwhere job = 'SALESMAN'and sal > 1500;2.查询每个部门的平均工资.select avg(sal) 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.查询每个员工的名字及其领导的名字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;+ - * / 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()select 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_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无法比较等值select ename, sal* 12 + comm * 12from emp;select ename, commfrom empwhere comm < 200;select ename, commfrom empwhere comm = null;select ename, commfrom empwhere comm is null;nvl()处理null值的函数select ename, sal*12 + nvl(comm,0)*12from emp;=1.查询总年薪大于10000的员工名及员工基本年薪和提成年薪.select ename,sal * 12 sal,nvl(comm,0) * 12from empwhere (sal*12+nvl(comm,0)*12)> 10000;2.查询工龄在30年以上且总年薪大于15000的员工名.select ename from empwhere add_months(sysdate,-30*12)> hiredate andsal*12+nvl(comm,0)*12>15000;3.查询12月入职,且有提成工资的员工名select ename from empwhere to_char(hiredate,'mm')= '12' andnvl(comm,-1) >= 0;select 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)> 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,-12*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) <add_months(sysdate,-12*30);order by:select ename, salfrom emporder by sal desc, ename;集合判断:inselect enamefrom empwhere add_months(sysdate,-12*31)> hiredate andempno in(select nvl(mgr,-1) from empgroup by mgr);not inselect enamefrom empwhere add_months(sysdate,-12*31)> hiredate 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 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;truncate 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(columnName 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 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);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 null 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_tables存储过程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;
