sql语句

Oracle基本操作

创建emp表

create table emp(empID number(4),

empName varchar2(20),

job varchar2(20),

mgr number(4),

hireDate date,

sar number(7,2),

com number(7,2),

deptNo number(4)

);

插入emp信息实例

insert into emp values (1048,'noah','saleman',1010,'2010-01-05',1030,200,10);

insert into emp values (8301,'emilia','clerk',1010,'2000-08-07',2500,30,20);

insert into emp values (1501,'ava','saleman',2656,'2005-02-15',1800,100,30);

insert into emp values (3053,'jackson','saleman',5013,'1990-11-13',8000,1500,20);

insert into emp values (5208,'lucas','manager',1010,'2009-05-07',1150,500,40);

insert into emp values (8096,'andrew','president',8301,'1998-09-01',1090,0,20);

insert into emp values (2656,'albert','CEO',0000,'1989-12-12',9999,0,30);

insert into emp values (1008,'david','manager',6047,'1987-01-18',2560,0,10);

insert into emp values (1010,'danie','engineer',8089,'1995-05-09',1520,2120,40);

insert into emp values (6047,'henry','accounting',2656,'2004-08-15',3020,0,10);

insert into emp values (4011,'linda','secretary',1001,'2000-01-07',6070,50,20);

insert into emp values (5612,'bill','clerk',5612,'2003-09-05',6500,230,30);

insert into emp values (5013,'bel','administrator',1010,'1996-09-30',2350,120,40);

insert into emp values (4019,'sam','clerk',3065,'2010-05-20',1250,20,10);

创建dept表

create table dept(

deptNum number(4),

depName varchar2(20),

loc varchar2(20)

);

插入dept实例信息

insert into dept values(10,'accounting','new york');

insert into dept values(20,'research','dallas');

insert into dept values(30,'sales','beijing');

insert into dept values(40,'operations','boston');

建表

create table 表名 values(项目)

查表的项目

desc 表名

增加

alter table 表名 add(项目)

修改

alter table 表名 modify(项目)

删除

alter table 表名 drop column项目名

更改表名

rename 旧表名 to 新表名

更新语句

update 表名 set 项目 where …

删除

delete from 表名 (可以恢复)

恢复(在恢复之前要做恢复点)

rollback to 恢复点名称

创建恢复点

savepoint 恢复点名称

drop table 表名    删除表的结构和数据(不可以恢复)

truncate table 表名 删除表的所有记录,表结构还在,无法找回删除记录,速度快

导出表(要切换到安装目录下)

exp userid=用户名/密码 @数据库名  tables=(表名) file=路径

exp userid=用户名/密码 @数据库名  tables=(表名) file=路径 direct=y  (这种速度快一点)

导入自己的表

imp userid=用户名/密码 @数据库名  tables=(表名) file=路径

导入其它用户的表

imp userid=DBA用户名/密码 @数据库名  tables=(表名) file=路径 touser=用户的表

导出表的结构

exp userid=用户名/密码 @数据库名  tables=(表名) file=路径 rows=n

只导出表的数据

exp userid=用户名/密码 @数据库名  tables=(表名) file=路径 ignore=y

导出自己的方案

exp userid=用户名/密码 @数据库名 owner=用户名 file=路径

导出其它方案（需要DBA的权限）

exp userid=用户名/密码 @数据库名  owner=(DBA权限用户，导出用户) file=路径

导出数据库 需要DBA的权限或是EXP_FULL_DATABASE权限

exp userid==用户名/密码 @数据库名 full =y inctype=complete file=路径

显示当前用户所拥有的所有表 user_tables

select table_name from user_tables

显示当前用户可以访问的所有表 all_tables

select table_name from all_tables;

显示所有方案所拥有的数据库表dba_tables

select table_name from dba_tables;

查询所有方案所拥有的用户

select username from dba_users

*查询数据库的表空间

select tablespace_name from dba_tablespace

显示当前数据库的全称

select * from global_name;

显示系统正使用那个数据库

select sys_context(‘USERENV’,’DB_NAME’) from dual

名称中含有’a’的员工 ‘%xx%’

select * from emp where empname like '%a%'

首字母是’S’的员工 like

select * from emp where empName like ‘S%’

第三个字母是’S’的员工 __like

select empName,sar from emp where empName like ‘__b%’

显示部门编号为10,20,30的员工姓名 in

select empName from emp where DeptNo in(10,20,30)

显示最高工资和最低工资 max min

select max(sar),min(sar) from emp

显示按部门的平均工资和最高工资 avg  group by

select avg(sar),max(sar) ,deptNo from emp group by deptNo

显示按部门的平均工资和最高工资且平均工资高于2K的部门 having

select avg(sar),max(sar),deptNo from emp group by deptNo having avg(sar)>2000

得到比10部门所有员工薪水高的员工 all

select * from emp where sar >all(select sar from emp where deptNo=10);

也可以写成

select * from emp where sar>(select max(sar) from emp where deptNo=10);

得到比10部门任何一个员工薪水高的员工 any

select * from emp where sar >any(select sar from emp where deptNo=10)

也可以写成

select * from emp where sar>(select min(sar) from emp where deptNo=10);

显示工资不在(不含)1000到3000之间的员工 between

select * from emp where sar not between 1000 and 3000

在1000到3000(包含)

select * from emp where sar between 1000 and 3000

重复记录 distinct

select distinct(emp.empname) from emp

查询不满足<>

select * from emp where empid<>1000

等号的左边，左外连接（显示等号右边表的所有记录）

select emp.deptno,dept.depnum from emp,dept where emp.deptno(+)=dept.depnum

计算员工的年薪 nvl(项目名,0)

当计算遇到空值时,取0

select empname, sar*13+nvl(com,0) 年薪 from emp

加索引 index

create inext aa on emp(empid)

创建函数 fuction

create function myFun(myName varchar2) return

number is yearSar number(7,2);

begin

select  sar*12+nvl(com,0)*12 into yearSar from emps  where empname=myName;

return yearSar;

end;

函数调用  var aa number;

  call myFun(‘albert’) into:aa;

创建存储过程（向表中插入一条记录）procedure

create procedure p01 is

begin

   insert into a001 values(1,’albert’);

end;

/

执行exec 名称

创建比自己部门平均工资高的雇员，以及对应的工作的视图 view

简单示例:create view aa as select * from emp where sar >2000

a.部门的平均工资表t2

select avg(emp.sar) avgSar,emp.deptno deptno from emp group by emp.deptno

b.比自己部门平均工次高的雇员表t3

select * from emp t1,() t2 where t1.deptno=t2.deptno and t1.sar>t2.avgSar

c.对应的工作t5

select ? from dept t4,()t3 where t4.deptnum =t3.deptno

d.创建视图 aa

create view aa as t5

从控制台输入变量 &项目名

select * from emp where sar>&sar

查询重复记录 having

select * from emp where empID in(select empID from emp group by empID having count(empID)>1 )

删除重复记录 rowid

delete from emp a where a.rowid!=(select max(b.rowid) from emp b wherer a.empID=b.empID and a.empName=b.empName....)

显示8个月前入职的员工

select * from emp where sysdate>add_months(hiredate,8);

显示员工的入职天数

select sysdate-hireDate “入职天数” from emp;

显示员工入职当月的最后一天是那天

select hireDate , last_day(hireDate)  from emp;

更正显示员工入职显示时间格式

select empName ,to_char(hireDate ,‘yyyy-mm-dd hh24:mi:ss’) from emp;

更正显示薪水,以货币符号开头,带逗号读取数值 L表示本地的货币符号,也可以替换成$等

select empName ,to_char(sar,’L99,999.99’) from emp;

*提取首字母为大写的员工名字

select upper(substr(empName,1,1))||lower(substr(empName,2,length(empName)-1)) from emp;

显示姓名和工资,以及对就的工作

select t1.empName,t1.sar,t2.deptName from emp t1,dept t2 where t1.deptNo =t2.deptNum;

自表查询

select t1.empName 自己,t2.empName 上司 from emp t1,emp t2 where t2.empNo = t1.mgr

嵌套查询 与albert同一部门的员工,单项子查询

1,得到albert的部门号

select deptNo from emp where empName=’albert’;

2,得到所有员工

select * from emp where deptNo=( select deptNo from emp where empName=’albert’);

查询与部门10工作相同的所有员工 多项子查询

1,得到部门10的员工的所有工作

select job from emp where deptNo=10 ;

2,得到所有员工

select * from emp where job in(select job from emp where deptNo=10)

写出与danie部门和工作相同的所有员工

1,得到danie 的部门和工作

select deptNo ,job from emp where empName=’danie’

2,得到所有员工

select * from emp where (deptNo,job)=( select deptNo ,job from emp where empName=’danie’)

得到高于自己部门平均工资的员工

1,得到自己部门的平均工资

select avg(sar)  from emp goup by deptNo;

2,把以上得到的当作成一个子表,再次进行查询

select * from emp t1,(select avg(sar) val ,deptNo num  from emp goup by deptNo) t2 where t1.deptNo=t2.num and t1.sar>t2.val;

更新andrew员工与albert的工作,薪水,福利一样

update emp set (job,sar,comm)=(select job,sar,comm from emp where empName=’albert’) where empName=’andrew’;

添加外键约束

alter table son

add constraint xxx foreign key (parentid)

references parent(id)