Oracle
语法及其应用: 查询部门创建时间最早的三条记录 select * from ( select * from dept t order by t.cdate ) where rowmun<4; 基本查询操作 查询工资最高的第2到5条记录 伪列必须用子查询 select ename,sal,r from ( select ename,sal,rownum r from emp e order by nvl(e.sal,0) )where r>=2 and r<=5; 复制表插入数据 create table emp1 as select e.* from emp e ; insert into emp1 (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('8000', '王二',8, 'CLERK', 800, null, to_date('17-12-2012', 'dd-mm-yyyy'),NULL); delete from emp1 where empno=8009; insert into emp1 (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('8009', '王三',8, 'CLERK', 800, null, to_date('17-12-2012', 'dd-mm-yyyy'),NULL); 查询存在dept表中的所有人员 关联数据 deptno select * from emp1 e where exists( select dname from dept t where t.deptno =e.deptno ); 表的合并 union 去掉重复行 select * from emp union select * from emp1; 表的合并 unionall 所有行 select * from emp union all select * from emp1; 表的合并 minus 两表的差集 大的减小的否则为0 select * from emp1 minus select * from emp; 表的合并 insertsect 交集 select * from emp1 intersect select * from emp; ????查询当前用户下的用户表名包含dept字母的所有字段 select* from user_tables where table_name like '%dept%'; 日期函数 to_char YYYY-mm YYYY-mm-dd hh24:mi:ss 日期的格式 select t.deptno,t.dname,to_char(t.cdate,'YYYY-mm')cdateyear from dept t; 日期函数 to_date select t.deptno,t.dname,to_date('2013-10-15','YYYY-mm-dd')cdateyear from dept t; select to_date('2013-10-15','YYYY-mm-dd')cdateyear from dual; 查询当前时间 sysdate+1 明天日期 select sysdate from dual; 去重 distinct 如果雇佣日期为空赋值为明天 累加 select to_char(nvl(e.hiredate,sysdate+1),'YYYY-mm-dd') from emp e; 连接字符串 ' ['|| ||']' select '此员工的姓名为:['||e.ename||']' from emp1 e; elect '此员工的姓名为:['||e.ename||']','的岗位是['||e.job||']' from emp1 e where e.ename like '刘%'; 复杂查询 统计工资的详细信息 分组统计函数group by select max(e.sal),min(e.sal),avg(e.sal),sum(e.sal),count(*) from emp1 e group by e.job; 查询人员表中姓王的存在重名的人员名称及人数 select e.ename,count(e.ename) from emp e where e.ename like '%王%' group by e.ename having count(e.ename)>1; 统计平均奖金大于2000 的各个职位的最高工资 select job,max(comm) from emp1 e group by e.job having avg(comm) >2000; 统计每个部门最高的工资,单列分组统计 根据最高的工资倒叙和部门的生序排列 数据包括0沿用nvl函数 select e.deptno,max(nvl(sal,0)) from emp1 e group by e.deptno order by max(nvl(e.sal,0)) desc,e.deptno; 统计个部门各岗位最高的工资,多列分组统计 select e.deptno ,e.job,max(nvl(e.sal,0)) from emp1 e group by e.deptno,e.job order by e.deptno; 连接查询 sql语句累加 相等连接 = 左外连接 以左表为主表 from 主表 left join XXX表 on XXX条件 右外连接 以右表为主表 完全外连接 full join (+)以部门表为主表,显示部门表的所有内容 相当于左外连接 加号的对面为主表 select e.ename,d.dname from emp1 e,dept d where e.deptno(+)=d.deptno; 外连接,以左表为主表,显示所有人员的信息 select *from emp1 e left join dept d on e.deptno=d.deptno; select *from emp1 e ,dept d where d.deptno(+)=e.deptno; 右外连接 select * from dept d right join emp1 e on e.deptno=d.deptno; 左外连接,以左表为主表,显示人员表单的所有内容 select e.ename,nvl(d.dname,'无所属部门') from emp1 e left join dept d on e.deptno=d.deptno; 查询所有部门的名称以及上级部门的名称 select f.dname,t.dname from dept f,dept t where f.upperdeptno=t.deptno 查询所有人员的名称以及上级部门的名称 还有上级领导的姓名和所属部门的名称 最少的where查询条件n-1 表n select e.ename,d.dname from emp e,dept d,emp e1,dept d1 where e.upperdeptno=d.deptno and e.mgr=e1.empno and d.deptno=d1.deptno; 子查询 嵌套查询 先用关联查询,如何实现不了用子查询 查询刘鹏飞所在部门的名称 多行数据 in 关联的效率大于子查询 select d.dname from dept d where d.deptno=( select e.deptno from emp1 e where e.ename='刘鹏飞' ); select d.dname from dept d ,emp1 e where e.deptno=d.deptno and e.ename='刘鹏飞'; 子查询返回多列数据 select e.ename,job from emp1 e where (e.deptno,e.job)=( select e.deptno,e.job from emp1 e where e.ename='刘鹏飞' ); 查询工作岗位是办事员的工资最高人员的名字 select e.ename from emp1 e where e.sal in( select max(e.sal) from empe where e.job='clerk' )and e.job='clerk'; 查询所有员工及上级领导的姓名 select e.ename, nvl((select a.ename from emp a where e.mgr=a.empno),'无领导') from emp e; 查询所有员工的姓名及部门名称,上级部门的名称 用子查询的方式 ()内的表有效 select e.ename, (select t.dname from dept t where t.deptno=e.deptno), (select d.dname from dept t,dept d where t.upperdeptno=d.deptno and e.deptno=t.deptno) from emp e ; 在insert update 语句中使用子查询 update emp e set e.deptno=(select a.deptno from emp a where a.empno=7950) where e.empno=7950; 常用的函数 ascll select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; chr instr(str1,str2,[从第几个开始找])返回第二个字符在第一个字符串中的位置; 如果没有返回0 下标标1开始 select instr('abbbbbbbcbbb','cb') from dual; concat 连接字符串 length 长度 lengthb 字节长度 汉字数=字节数X2 lower 字符串小写 upper 字符串大写 ltrim rtrim trim 去空格 substr(str,开始,个数) 截取字符串 (str,倒数位置用负数,个数) 截取字符串 replace(str1,str2) 用str2替换str1 round 四舍五入 select round('13.5562',2) from dual; select round('13.5562',-2) from dual; trunc 截取数字 日期函数 add_months sysdate 当前日期 给指定日期家两个月 select add_months(to_date('2015-10-12','YYYY-mm-dd'),2)from dual; 查询5个月之后的前一天是哪一天 select add_months(sysdate-2,5) from dual; last_day 当前月份的最后一天 select last_day(sysdate ) from dual; 指定月份的最后一天 select last_day(to_date('2017-02-01','yyyy-mm-dd')) from dual; next_day 返回特定日期之后的第一个工作日所对应的日期 select next_day(sysdate,'星期一') from dual; 查询明年当前月份的最后一天 select last_day(add_months(sysdate,12)) from dual; 返回当前用户 select user from dual; decode 相当于 (判断字段 if 是 else 是) select decode(e.job,'clerk','办事员','salesman','管理员') from emp e ; DEL语句 插入数据 insert into 表名 (列名)values() 写列名 更新数据 update 表名 set 先查询确认是否正确 选中完整的sql语句执行 可以进行子查询修改 删除数据 delete from 表名 where 条件 commint; 先查询确认是否正确 选中完整的sql语句执行 免提交删除整个表 truncate ????如何以命令行的形式将oracle数据库的库表导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) exp system/12345@TEST file=d:\daochu.dmp tables=(DEPT ,EMP,SALGRADE); 改数据 select* from 表名 for update 展示表结构 desc 表名; 显示高度和宽度 set pagesize set linesize 进入数据库 cmd-sqlplus system 123456 ocral语句 /*==============================================================*/ /* Table: DEPT */ /*==============================================================*/ create table DEPT ( DEPTNO NUMBER(6) not null, DNAME VARCHAR2(20), UPPERDEPTNO NUMBER(6), CDATE DATE, constraint PK_DEPT primary key (DEPTNO) ); comment on table DEPT is '部门表'; comment on column DEPT.DEPTNO is '部门内码'; comment on column DEPT.DNAME is '部门名称'; comment on column DEPT.UPPERDEPTNO is '上级部门'; comment on column DEPT.CDATE is '创建日期'; /*==============================================================*/ /* Table: EMP */ /*==============================================================*/ create table EMP ( EMPNO VARCHAR2(10) not null, ENAME VARCHAR2(20), DEPTNO NUMBER(6), MGR VARCHAR2(10), JOB VARCHAR2(10), COMM NUMBER(12,3), SAL NUMBER(12,3), HIREDATE DATE, constraint PK_EMP primary key (EMPNO) ); comment on table EMP is '员工表'; comment on column EMP.EMPNO is '员工编号'; comment on column EMP.MGR is '所属领导'; comment on column EMP.ENAME is '员工姓名'; comment on column EMP.DEPTNO is '所属部门'; comment on column EMP.JOB is '用来存放员工的职位,有以下3个选项 manager:经理 clerk:办事员 salesman:推销员 '; comment on column EMP.COMM is '奖金'; comment on column EMP.SAL is '工资'; comment on column EMP.HIREDATE is '雇用日期'; /*==============================================================*/ /* Table: SALGRADE */ /*==============================================================*/ create table SALGRADE ( GRADE NUMBER(3) not null, LOSAL NUMBER(12,3) not null, HISAL NUMBER(12,3) not null, constraint PK_SALGRADE primary key (GRADE) ); comment on table SALGRADE is '工资等级表'; comment on column SALGRADE.GRADE is '等级名称'; comment on column SALGRADE.LOSAL is '最低工资'; comment on column SALGRADE.HISAL is '最高工资'; alter table EMP add constraint FK_EMP_REFERENCE_DEPT foreign key (DEPTNO) references DEPT (DEPTNO); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (0, '深海灯具公司', null, to_date('01-09-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (1, '销售部', 0, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (2, '制造部', 0, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (201, '生产一线', 2, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (202, '生产二线', 2, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (203, '生产三线', 2, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (3, '人力资源部', 0, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (4, '研究所', 0, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (5, '管理部', 0, to_date('01-10-2008', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (101, '华东', 1, to_date('01-01-2009', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (102, '华北', 1, to_date('01-01-2009', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (103, '华南', 1, to_date('01-12-2009', 'dd-mm-yyyy')); insert into DEPT (DEPTNO, DNAME, UPPERDEPTNO, CDATE) values (104, '东北', 1, to_date('01-03-2010', 'dd-mm-yyyy')); commit; insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7369', '王刚', 201, 'CLERK', 800, null, to_date('17-12-2012', 'dd-mm-yyyy'), '7902'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7499', '李萌', 101, 'SALESMAN', 1600, 300, to_date('20-02-2012', 'dd-mm-yyyy'), '7698'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7521', '李江', 102, 'SALESMAN', 1250, 500, to_date('22-02-2012', 'dd-mm-yyyy'), '7698'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7566', '刘鹏飞', 101, 'MANAGER', 2975, null, to_date('02-04-2009', 'dd-mm-yyyy'), '7839'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7654', '马建军', 103, 'SALESMAN', 1250, 1400, to_date('28-09-2011', 'dd-mm-yyyy'), '7698'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7698', '刘朋', 103, 'MANAGER', 2850, null, to_date('01-05-2011', 'dd-mm-yyyy'), '7839'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7782', '李波', 102, 'MANAGER', 2450, null, to_date('09-06-2009', 'dd-mm-yyyy'), '7839'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7788', '张强', 4, 'ANALYST', 3000, null, to_date('09-11-2012', 'dd-mm-yyyy'), '7566'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7839', '宋文', 0, 'PRESIDENT', 5000, null, to_date('01-09-2008', 'dd-mm-yyyy'), null); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7844', '赵卫国', 103, 'SALESMAN', 1500, 0, to_date('08-09-2012', 'dd-mm-yyyy'), '7698'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7876', '钱森', 203, 'CLERK', 1100, null, to_date('12-06-2012', 'dd-mm-yyyy'), '7788'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7900', '刘强', 203, 'CLERK', 950, null, to_date('03-12-2012', 'dd-mm-yyyy'), '7698'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7902', '付磊', 4, 'ANALYST', 3000, null, to_date('03-12-2012', 'dd-mm-yyyy'), '7566'); insert into EMP (EMPNO, ENAME, DEPTNO, JOB, COMM, SAL, HIREDATE, MGR) values ('7934', '华磊', 202, 'CLERK', 1300, null, to_date('23-01-2012', 'dd-mm-yyyy'), '7782'); commit; insert into SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200); insert into SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400); insert into SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000); insert into SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000); insert into SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999); commit;