oracle语法大全.doc

第一篇 基本操作-解锁用户 alter user 用户 account unlock;-锁定用户 alter user 用户 account lock;alter user scott account unlock;-创建一个用户yc 密码为a create user 用户名 identified by 密码；create user yc identified by a;-登录不成功，会缺少create session 权限，赋予权限的语法 grant 权限名 to 用户；grant create session to yc;-修改密码 alter user 用户名 identified by 新密码；alter user yc identified by b;-删除用户drop user yc ;-查询表空间select *from dba_tablespaces;-查询用户信息select *from dba_users;-创建表空间create tablespace ycspacedatafile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbfsize 2mautoextend on next 2m maxsize 5moffline ;-创建临时表空间create temporary yctempspacetempfile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbfsize 2mautoextend on next 2m maxsize 5moffline ;-查询数据文件select *from dba_data_files;-修改表空间-1、修改表空间的状态-默认情况下是online,只有在非离线情况下才可以进行修改alter tablespace ycspace offline ; -离线状态，不允许任何对象对该表空间的使用，使用情况：应用需要更新或维护的时候；数据库备份的时候alter tablespace ycspace read write;-读写状态alter tablespace ycspace online;alter tablespace ycspace read only; -只读，可以查询信息，可以删除表空间的对象，但是不能创建对象和修改对象 。使用情况：数据存档的时候-2、修改表空间的大小-增加文件的大小alter database datafile E:oracleappproduct11.2.0dbhome_1oradataycspace.dbf resize 10m;-增加数据文件alter tablespace ycspace add datafile E:oracleappproduct11.2.0dbhome_1oradataadd.dbf size 2m;-删除表空间的数据文件alter tablespace 表空间的名字 drop datafile 数据文件名；-删除表空间drop tablespace ycspace;-删除表空间且表空间中的内容和数据文件drop tablespace ycspace including contents and datafiles;-指定表空间 的 创建用户的语法create user yc1 identified by a default tablespace ycspace temporary tablespace temp;-删除用户drop user yc1;-权限-赋予创建会话的权限grant create session to yc1;-创建一个表create table studentInfo(sid int,sname varchar2(10);-赋予yc1用户创建表的权限grant create table to yc1;-赋予yc1使用表空间的权限grant unlimited tablespace to yc1;-系统权限-对象权限-插入insert into studentInfo values (2,abcd);-查询select *from studentInfo;-修改update studentInfo set sid=1;-删除delete studentInfo ;drop table studentInfo; -系统权限删除表-赋权的语法-系统权限grant 权限名（系统权限或对象权限,角色，all) to 用户(角色，public) with admin option;-对象权限grant 权限名（系统权限或对象权限,角色，all) on 用户(角色，public) with grant option;-收权语法-系统权限revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with admin option;-对象权限revoke 权限名（系统权限或对象权限,角色，all) from 用户(角色，public) with grant option;-赋予创建用户的权限并且把这个权限传递下去，即yc1可以给别人赋权grant create user to yc1 with admin option;-收回权限,只能收回scottd ,不能收回由scott赋权的yc1的权限revoke create user from scott;-查看用户所具有的权限select *from user_sys_privs;-对象权限详解select * from emp;-使用yc1来查询scott里面的emp表select * from scott.emp;-赋予yc1查询emp表和插入的权限grant select on emp to yc1;grant insert on emp to yc1;grant update(empno,ename) on emp to yc1;grant delete on emp to yc1;-对scott的emp表添加数据insert into scott.emp(empno,ename) value(111,acv);update scott.emp set ename=ycwhere empno=111;-赋予查询、赋予删除、添加、修改grant select on 表名 to 用户-grant select,delete,update,insert on 表名 to 用户grant select,delete,update,insert on emp to yc1;grant all on dept to yc1; -all代表所有的对象权限select *from scott.emp;select *from scott.dept;insert into scott.dept values(50,企事业文化部,bumen);-查看角色-dba:数据库管理员，系统最高权限，可以创建数据结构（表空间等）-resource:可以创建实体（表、视图），不可以创建数据库的结构-connect:连接的权限，可以登录数据库，但是不可以创建实体和不可以创建数据库结构select *from role_sys_privs;grant connect to yc1;-将可以连接的角色赋予给yc1，则yc1就是应该可以连接数据库的人，类似于 create session 。create table StuInfos(sid int);select *from StuInfos;create table stuInfo(sid int primary key , -主键 primary key 非空且唯一 （主键约束）sname varchar2(10) not null, -姓名不能为空,（非空约束）sex char(2) check(sex in(男,女), -（检查约束），check,age number(3,1) constraint ck_stuInfo_age check(age10 and age=2;-修改记录的语法-update 表名 set 字段=值 where 条件update classInfo set cname=三班; -会修改所有该字段update classInfo set cname=四班 where cid=1;update classInfo set cname=五班, stasuts =未毕业 where cid=3;-alter table classInfo drop constraint SYS_C0011213;-添加多个时可以使用序列-用序列来做自动增长create sequence seq_classInfo_cid start with 1001 increment by 1;insert into classInfo values(seq_classInfo_cid.Nextval,七班,未毕业);insert into classInfo values(seq_classInfo_cid.Nextval,八班,未毕业);insert into classInfo values(seq_classInfo_cid.Nextval,九班,未毕业);insert into classInfo values(seq_classInfo_cid.Nextval,十班,未毕业);create table classInfo2(cid int primary key, -班级idcname varchar2(20) not null unique , -班级名stasuts varchar2(100);select *from classInfo2;drop table classInfo2;insert into classInfo2 select *from classInfo;insert into classInfo(cname,cid) select cname,cid from classInfo;alter table classInfo2 drop constraint SYS_C0011213;select seq_classInfo_cid.nextval from dual;select seq_classInfo_cid.Currval from dual;-直接创建一个新表，并拿到另一个表其中的数据create table newTable as select cname,cid from classInfo;create table newTable1 as select *from classInfo;select *from newTable;select *from newTable1;insert into newTable1 values(1008,dg,);第二篇：高级操作直接在使用scott登陆，进行查询操作-简单查询select *from emp;select empno as id,ename as name from emp;select empno 编号,ename 姓名 from emp;-去除重复select job from emp;select distinct job from emp;select job,deptno from emp;select distinct job,deptno from emp;-字符串的连接select 员工编号是 |empno | 姓名是 |ename |工作是|job from emp;-乘法select ename,sal *12 from emp;-加减乘除都类似-限定查询-奖金大于1500的select *from emp where sal1500;-有奖金的select *from emp where comm is not null;-没有奖金的select *from emp where comm is null;-有奖金且大于1500的select *from emp where sal1500 and comm is not null;-工资大于1500或者有奖金的select *from emp where sal1500 or comm is not null;-工资不大于1500且没奖金的select *from emp where sal1500 or comm is not null);-工资大于1500但是小于3000的select *from emp where sal1500 and sal3000;select *from emp where sal between 1500 and 3000; -between是闭区间，是包含1500和3000的-时间区间select *from emp where hiredate between to_date(1981-01-01,yyyy-MM-dd) and to_date(1981-12-31,yyyy-MM-dd);-查询雇员名字select *from emp where ename=SMITH;-查询员工编号select *from emp where empno=7369 or empno=7499 or empno=7521;select *from emp where empno in(7369,7499,7521);select *from emp where empno not in(7369,7499,7521); -排除这3个，其他的都可以查-模糊查询select *from emp where ename like _M%; -第2个字母为M的select *from emp where ename like %M%;select *from emp where ename like %; -全查询-不等号的用法select * from emp where empno !=7369;select *from emp where empno 7369;-对结果集排序-查询工资从低到高select *from emp order by sal asc;select *from emp order by sal desc,hiredate desc; -asc 当导游列相同时就按第二个来排序-字符函数select *from dual;-伪表select 2*3 from dual;select sysdate from dual;-变成大写select upper(smith) from dual;-变成小写select lower(SMITH) from dual;-首字母大写select initcap(smith) from dual;-连接字符串select concat(jr,smith) from dual; -只能在oracle中使用select jr |smith from dual; -推荐使用-截取字符串select substr(hello,1,3) from dual; -索引从1开始-获取字符串长度select length(hello) from dual;-字符串替换select replace(hello,l,x) from dual; -把l替换为x-通用函数-数值函数-四舍五入select round(12.234) from dual;-取整的四舍五入 12select round (12.657,2) from dual; -保留2位小数select trunc(12.48) from dual;-取整select trunc(12.48675，2) from dual; -保留2位小数-取余select mod(10,3) from dual;-10/3取余 =1-日期函数-日期-数字=日期 日期+数字=日期 日期-日期=数字-查询员工进入公司的周数select ename,round(sysdate -hiredate)/7) weeks from emp;-查询所有员工进入公司的月数select ename,round(months_between(sysdate,hiredate) months from emp;-求三个月后的日期select add_months(sysdate,6) from dual;select next_day(sysdate,星期一) from dual; -下星期select last_day(sysdate) from dual; -本月最后一天select last_day(to_date(1997-1-23,yyyy-MM-dd) from dual;-转换函数select ename ,to_char(hiredate,yyyy) 年，to_char(hiredate,mm)月，to_char(hiredate,dd) 日from emp;select to_char(10000000,$999,999,999) from emp;select to_number(20)+to_number(80) from dual; -数字相加-查询员工年薪select ename,(sal*12+nvl(comm,0) yearsal from emp; -空和任何数计算都是空-Decode函数，类似if else if (常用）select decode(1,1,one,2,two,no name) from dual;-查询所有职位的中文名select ename, decode(job,CLERK,业务员,SALESMAN,销售,MANAGER,经理,ANALYST,分析员,PRESIDENT,总裁,无业)from emp;select ename,casewhen job = CLERK then业务员when job = SALESMAN then销售when job = MANAGER then经理when job = ANALYST then分析员when job = PRESIDENT then总裁else无业endfrom emp;-多表查询select *from dept;select *from emp,dept order by emp.deptno;select *from emp e,dept d where e.deptno=d.deptno;select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查询出雇员的编号，姓名，部门编号，和名称，地址select e.empno,e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;-查询出每个员工的上级领导select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;select e.empno,e.ename,d.dnamefrom emp e,dept d ,salgrade s, emp e1where e.deptno=d.deptnoand e.sal between s.losaland s.hisaland e.mgr=e1.empno;select e.empno,e.ename,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno(+) ;-外连接select *from emp order by deptno;-查询出每个部门的员工/*分析：部门表是全量表，员工表示非全量表，在做连接条件时，全量表在非全量表的哪端，那么连接时全量表的连接条件就在等号哪断*/-左连接select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;-右连接select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;-作业-查询与smith相同部门的员工姓名和雇佣日期select *from emp twhere t.deptno= (select e.deptno from emp e where e.ename=SMITH)and t.ename SMITH;-查询工资比公司平均工资高的员工的员工号，姓名和工资select t.empno,t.ename,t.salfrom emp twhere t.sal(select avg(sal) from emp);-查询各部门中工资比本部门平均工资高的员工号，姓名和工资select t.empno,t.ename,t.salfrom emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) awhere t.sala.avgsal and t.deptno=a.deptno;-查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名select t.empno,t.ename from emp twhere t.deptno in( select e.deptno from emp e where e.ename like %U%)and t.empno not in ( select e.empno from emp e where e.ename like %U%) ;-查询管理者是king的员工姓名和工资select t.ename,t.sal from emp twhere t.mgr in(select e.empno from emp e where e.ename=KING);-sql1999语法select *from emp join dept using(deptno) where deptno=20;select *from emp natural join dept;select *from emp e join dept d on e.deptno=d.deptno;select *from dept;select *from dept d left join emp e on d.deptno=e.deptno;select *from dept d,emp e where d.deptno=e.deptno(+);-分组select count(empno) from emp group by deptno;select deptno,job,count(*) from emp group by deptno,job order by deptno;select *from EMP for UPDATE;-group by 后面有的字段，select后才可以有，group by后面没有的字段，select后面绝对不能有select d.dname, d.loc, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc ;-子查询select *from emp t where t.sal(select *from emp e where e.empno=7654);select rownum ,t.* from emp t where rownum 6 ;-pagesize 5select *from(select rownum rw,a.* from (select *from emp ) a where rownum 10;select *from (select *from emp) where rownum0;-索引create index person_index on person(p_name);-视图create view view2 as select *from emp t where t.deptno=20;select *from view2;-pl/sql-plsql是对sql语言的过程化扩展-declarebegindbms_output.put_line(hello world);end;-declareage number(3);marry boolean := true; -boolean不能直接输出pname varchar2(10) := re jeknc;beginage := 20;dbms_output.put_line(age);if marry thendbms_o