语法及其应用：
查询部门创建时间最早的三条记录
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;
