oracle语法大全

--解锁用户 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 ycspace

datafile 'E:\\oracle\\app\\product\\11.2.0\\dbhome_1\\oradata\\ycspace.dbf'

size 2m

autoextend on next 2m maxsize 5m

offline ;

--创建临时表空间

create temporary yctempspace

tempfile 'E:\\oracle\\app\\product\\11.2.0\\dbhome_1\\oradata\\ycspace.dbf'

size 2m

autoextend on next 2m maxsize 5m

offline ;

--查询数据文件

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:\\oracle\\app\\product\\11.2.0\\dbhome_1\\oradata\\ycspace.dbf' resize 10m;

--增加数据文件

alter tablespace ycspace add datafile 'E:\\oracle\\app\\product\\11.2.0\\dbhome_1\\oradata\\add.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='yc'where 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(age>10 and age<100) , --也可以用varchar ；age between 10 and 100 ,在10和100之间，是一个闭区间

tel number(15) unique not null, --唯一约束，

address varchar2(200) default '什么鬼'

)

insert into stuInfo values（3,'大大','男',18，4321543,default);

insert into stuInfo values(1,'张三','男',10);

select *from stuInfo;

drop table stuInfo;

create table classInfo(

cid int primary key, --班级id

cname varchar2(20) not null unique --班级名

)

create table stuInfo(

sid int primary key,

sname varchar2(20),

cid int constraint fofk_stuInfo_cid references classInfo(cid) on delete cascade

)

insert into classInfo values(1,'1班');

insert into classInfo values(2,'2班');

insert into classInfo values(3,'3班');

insert into classInfo values(4,'4班');

select *from classInfo;

select *from stuInfo;

insert into stuInfo values(1001,'张三',2);

insert into stuInfo values(1002,'张四',4);

update classInfo set cid=1 where cid=8;

drop table stuInfo;--要先删除这个

drop table classInfo; --再删除这个

delete classInfo where cid=4 ;--同时删除这两个表中的4

--删除用户的时候

drop user yc1 [cascade] --删除用户的同时把它创建的对象都一起删除

--修改表

--1、添加表中字段

--alter table 表名 add 字段名 类型

alter table classInfo add status varchar2(10) default '未毕业'

--2、修改已有字段的数据类型

--alter table 表名 modify 字段名 类型

alter table classInfo modify status number(1)

--3、修改字段名

--alter table 表名 rename column 旧字段名 to 新的字段名

alter table classInfo rename column cname to 班级名;

--4、删除字段

--alter table 表名 drop column 字段名

alter table classInfo drop column status ;

--5、修改表名

--rename 旧表名 to 新表名

rename classInfo to 班级信息；

--删除表

--1、截断表效率高，每删除一次会产生一次日志 2、截断会释放空间，而delete不会释放空间

--删除表结构和数据

drop table 表名；

--删除表中所有数据

truncate table classInfo;

delete classInfo;

create table classInfo(

cid int primary key, --班级id

cname varchar2(20) not null unique , --班级名

stasuts varchar2(100)

);

select *from classInfo;

--数据的操作

--增加数据语法

--insert into 表名[(列名，....)] values (对应的数据的值）；

insert into classInfo values(1,'一班','未毕业');--需要按照表结构的顺序插入

insert into classInfo values(4,'六班','未毕业');

insert into classInfo(cname,cid) values('二班',2); --需要按照括号中的顺序插入，但是 not null primary key 必须插入的。

insert into classInfo(cname,cid) values('三班',3);

--删除的语法

--delete 表名 [where 条件]

delete classInfo where cid>=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, --班级id

cname 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 sal>1500;

--有奖金的

select *from emp where comm is not null;

--没有奖金的

select *from emp where comm is null;

--有奖金且大于1500的

select *from emp where sal>1500 and comm is not null;

--工资大于1500或者有奖金的

select *from emp where sal>1500 or comm is not null;

--工资不大于1500且没奖金的

select *from emp where sal<=1500 and comm is null;

select *from emp where not (sal >1500 or comm is not null);

--工资大于1500但是小于3000的

select *from emp where sal>1500 and sal<3000;

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;--取整的四舍五入 12

select 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,

case

when job = 'CLERK' then

'业务员'

when job = 'SALESMAN' then

'销售'

when job = 'MANAGER' then

'经理'

when job = 'ANALYST' then

'分析员'

when job = 'PRESIDENT' then

'总裁'

else

'无业'

end

from 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.dname

from emp e,dept d ,salgrade s, emp e1

where e.deptno=d.deptno

and e.sal between s.losal

and s.hisal

and 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 t

where t.deptno= (select e.deptno from emp e where e.ename='SMITH')

and t.ename<> 'SMITH';

--查询工资比公司平均工资高的员工的员工号，姓名和工资

select t.empno,t.ename,t.sal

from emp t

where t.sal>(select avg(sal) from emp);

--查询各部门中工资比本部门平均工资高的员工号，姓名和工资

select t.empno,t.ename,t.sal

from emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) a

where t.sal>a.avgsal and t.deptno=a.deptno;

--查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select t.empno,t.ename from emp t

where 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 t

where 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 5

select *from(select rownum rw,a.* from (select *from emp ) a where rownum <16) b where b.rw>10;

select *from (select *from emp) where rownum>0;

--索引

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语言的过程化扩展

-----

declare

begin

dbms_output.put_line('hello world');

end;

-------

declare

age number(3);

marry boolean := true; --boolean不能直接输出

pname varchar2(10) := 're jeknc';

begin

age := 20;

dbms_output.put_line(age);

if marry then

dbms_output.put_line('true');

else

dbms_output.put_line('false');

end if ;

dbms_output.put_line(pname);

end;

--常量和变量

--引用变量,引用表中的字段的类型

Myname emp.ename%type; --使用into来赋值

declare

pname emp.ename%type;

begin

select t.ename into pname from emp t where t.empno=7369;

dbms_output.put_line(pname);

end;

--记录型变量

Emprec emp%rowtype; --使用into来赋值

declare

Emprec emp%rowtype;

begin

select t.* into Emprec from emp t where t.empno=7369;

dbms_output.put_line(Emprec.empno || ' '||Emprec.ename||' '||Emprec.job);

end;

--if分支

语法1：

IF 条件 THEN 语句1;

语句2;

END IF;

语法2：

IF 条件 THEN 语句序列1；

ELSE 语句序列 2；

END IF；

语法3：

IF 条件 THEN 语句;

ELSIF 条件 THEN 语句;

ELSE 语句;

END IF;

--1

declare

pname number:=#

begin

if pname = 1 then

dbms_output.put_line('我是1');

else

dbms_output.put_line('我不是1');

end if;

end;

--2

declare

pname number := #

begin

if pname = 1 then

dbms_output.put_line('我是1');

elsif pname = 2 then

dbms_output.put_line('我是2');

else

dbms_output.put_line('我不是12');

end if;

end;

--loop循环语句

语法2：

Loop

EXIT [when 条件];

……

End loop

--1

declare

pnum number(4):=0;

begin

while pnum < 10 loop

dbms_output.put_line(pnum);

pnum := pnum + 1;

end loop;

end;

--2 (最常用的循环)

declare

pnum number(4):=0;

begin

loop

exit when pnum=10;

pnum:=pnum+1;

dbms_output.put_line(pnum);

end loop;

end;

--3

declare

pnum number(4);

begin

for pnum in 1 .. 10 loop

dbms_output.put_line(pnum);

end loop;

end;

----------------------------------

--游标

语法：

CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;

例如：cursor c1 is select ename from emp;

declare

cursor c1 is

select * from emp;

emprec emp%rowtype;

begin

open c1;

loop

fetch c1

into emprec;

exit when c1%notfound;

dbms_output.put_line(emprec.empno || ' ' || emprec.ename);

end loop;

close c1; --要记得关闭游标

end;

--------例外

--异常，用来增强程序的健壮性和容错性

-- no_data_found (没有找到数据)

--too_many_rows (select …into语句匹配多个行)

--zero_pide ( 被零除)

--value_error (算术或转换错误)

--timeout_on_resource (在等待资源时发生超时)

--写出被0除的例外程序

declare

pnum number(4) := 10;

begin

pnum := pnum / 0;

exception

when zero_pide then

dbms_output.put_line('被0除了');

when value_error then

dbms_output.put_line('算术或转换错误');

when others then

dbms_output.put_line('其他异常');

end;

--自定义异常

--No_data exception;

--要抛出raise no_data;

declare

cursor c1 is

select * from emp t where t.deptno = 20;

no_data exception;

emprec emp%rowtype;

begin

open c1;

loop

fetch c1

into emprec;

if c1%notfound then

raise no_data;

else

dbms_output.put_line(emprec.empno || ' ' || emprec.ename);

end if;

end loop;

close c1;

exception

when no_data then

dbms_output.put_line('无员工');

when others then

dbms_output.put_line('其他异常');

end;

--存储过程

语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]

AS

begin

PLSQL子程序体；

End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]

is

begin

PLSQL子程序体；

End 过程名;

-----创建一个存储过程helloworld

create or replace procedure helloworld is

begin

dbms_output.put_line('hello world');

end helloworld;

------创建一个涨工资的

create or replace procedure addsal(eno in emp.empno%type) is

emprec emp%rowtype;

begin

select * into emprec from emp t where t.empno = eno;

update emp t set t.sal = t.sal + 100 where t.empno = eno;

dbms_output.put_line('涨工资前是' || emprec.sal || ',涨工资后是' ||

(emprec.sal + 100));

end addsal;

----------------------------------------------

--java代码调用存储过程和函数

--存储过程

--

create or replace procedure acc_yealsal(eno in emp.empno%type,yearsal out number) is

pcomm emp.comm%type;

psal emp.sal%type;

begin

select t.sal,t.comm into psal,pcomm from emp t where t.empno=eno;

yearsal :=psal*12 +nvl(pcomm,0);

end;

----存储函数

create or replace function 函数名(Name in type, Name in type, .. .)

return 数据类型 is

结果变量 数据类型;

begin

return(结果变量);

end函数名;

--存储函数计算年薪

create or replace function accf_yearsal(eno in emp.empno%type)

return number is

Result number;

psal emp.sal%type;

pcomm emp.comm%type;

begin

select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;

Result := psal * 12 + nvl(pcomm, 0);

return(Result);

end accf_yearsal;

-----------------------------------

---触发器

--触发语句：增删改：

语法：

CREATE [or REPLACE] TRIGGER 触发器名

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE [OF 列名]}

ON 表名

[FOR EACH ROW [WHEN(条件) ] ]

begin

PLSQL 块

End 触发器名

---插入一个新员工则触发

create or replace trigger insert_person

after insert on emp

begin

dbms_output.put_line('插入新员工');

end;

select *from emp;

insert into emp values(1001,'李四','管理',7902,sysdate,100,100,20);

--raise_application_error(-20001, '不能在非法时间插入员工')

--==============================================================================

SQL> @ E:\\powerDesigner\\A_脚本\sql --导入脚本文件

select *from H_USER ;

insert into h_user valuer(sequserid.nextval,'a','a',sysdate,'北京',1);

--------------------------------------------------------------

--数据库建模

--一对多：多的一端是2，箭头指向的是表1，即少的一端

--在实体类中一的一端的实体类有多的一端的实体类的集合属性

--使用powerDesiger进行数据库建模，然后将数据导入，导入到plsql中进行使用

--------------------连接远程数据库

--方法1，修改localhost的地址

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl.lan)

)

)

--方法2

--或者直接在登陆界面在database中输入远程数据库的ip地址和端口号进行远程登陆

1.create user username identified by password;//建用户名和密码oracle ,oracle

　　2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username;

　　3.connect username/password//进入。

　　4.select table_name,column_name from user_tab_columns where table_name='TABLE_NAME';//查询表中的表名，字段名等等。 最后的table_name要大写。

　　5. 如何执行脚本SQL文件? SQL>@PATH/filename.sql; 

　　7.查询用户下的所有表 select distinct table_name from user_tab_columns; ===仅显示一列表名。

8.如何搜索出前N条记录？ 

select * from tablename where rownum　　9.查找用户下的所有表：select * from tab; --查询该用户下的所有表及视图（显示表名tname， 类型tabname和clusterid）

　　2、显示当前连接用户

　　SQL> show user –不能用在sql窗口 只能用在command命令窗口。

　　3、查看系统拥有哪些用户

　　SQL> select * from all_users;

　　4、新建用户并授权

　　SQL> create user a identified by a;(默认建在SYSTEM表空间下)

　　SQL> grant connect,resource to a;

　　5、连接到新用户

　　SQL> conn a/a –或者是connect a/a

　　6、查询当前用户下所有对象

　　SQL> select * from tab; --table或是view

　　7、建立第一个表

　　SQL> create table a(a number);

　　8、查询表结构

　　SQL> desc a

　　9、插入新记录

　　SQL> insert into a values(1);

　　10、查询记录

　　SQL> select * from a;

　　11、更改记录

　　SQL> update a set a=2;

　　12、删除记录

　　SQL> delete from a;

　　13、回滚

　　SQL> roll;

　　SQL> rollback;

　　14、提交

　　SQL> commit;

　　select * from

　　(select t.*,dense_rank() over (order by cardkind) rank from cardkind t)

　　where rank = 2;

　　46. 如何在字符串里加回车?

　　select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ; --‘||chr(10)||’作为换行符

　　53. 如何使select语句使查询结果自动生成序号?

　　select rownum COL from table; --主要就是oracle中引入了rownum

　　54. 如何知道数据裤中某个表所在的tablespace?

　　select tablespace_name from user_tables where table_name='TEST'; --table_name名称要大写。

　　select * from user_tables中有个字段TABLESPACE_NAME，（oracle）;

　　select * from dba_segments where …;

　　55. 怎么可以快速做一个和原表一样的备份表?

　　create table new_table as (select * from old_table);

　　59. 请问如何修改一张表的主键?

　　alter table aaa drop constraint aaa_key ;

　　alter table aaa add constraint aaa_key primary key(a1,b1) ;

　　60. 改变数据文件的大小?

　　用 ALTER DATABASE .... DATAFILE .... ;

　　手工改变数据文件的大小，对于原来的 数据文件有没有损害。

　　61. 怎样查看ORACLE中有哪些程序在运行之中？

　　查看v$session表

　　62. 怎么可以看到数据库有多少个tablespace?

　　select * from dba_tablespaces;

　　72. 怎样查看哪些用户拥有SYSDBA、SYSOPER权限？

　　SQL>conn sys/change_on_install –登不上去

　　SQL>select * from V_$PWFILE_USERS;

76. 如何显示当前连接用户?

　　SHOW USER

　　77. 如何查看数据文件放置的路径 ?

　　col file_name format a50

　　SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 

　　79. 如何改变一个字段初始定义的Check范围？

　　SQL> alter table xxx drop constraint constraint_name;

　　之后再创建新约束:

　　SQL> alter table xxx add constraint constraint_name check(); 

　　83. 如何执行脚本SQL文件?

SQL>@所在的文件路径 /filename.sql;

例如放在E盘的根目录下则应该是

@E:\\a.sql;回车就OK了。

　　84. 如何快速清空一个大表?

　　SQL>truncate table table_name;

　　85. 如何查有多少个数据库实例?

　　SQL>SELECT * FROM V$INSTANCE;

　　86. 如何查询数据库有多少表?

　　SQL>select * from all_tables;

　　87. 如何测试SQL语句执行所用的时间?

　　SQL>set timing on ;

　　SQL>select * from tablename;

　　. 字符串的连接

　　SELECT CONCAT(COL1,COL2) FROM TABLE ;

　　SELECT COL1||COL2 FROM TABLE ;

　　90. 怎么把select出来的结果导到一个文本文件中？

　　SQL>SPOOL C:\\ABCD.TXT;

　　SQL>select * from table;

　　SQL >spool off;

　　91. 怎样估算SQL执行的I/O数 ?

　　SQL>SET AUTOTRACE ON ;

　　SQL>SELECT * FROM TABLE;

　　OR

　　SQL>SELECT * FROM v$filestat ;

　　可以查看IO数

　　92. 如何在sqlplus下改变字段大小?

　　alter table table_name modify (field_name varchar2(100));

　　改大行，改小不行（除非都是空的）

　　93. 如何查询某天的数据?

select * from a where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd'); 

若是date型数据

insert into bsyear values(to_date('20130427','yyyymmdd'));

或者是insert into bsyear values('27-4月-2013');

　　94. sql 语句如何插入全年日期？

　　create table BSYEAR (d date);

insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1 

from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd'); --在表后直接插入365行数据日期。

紧急插入几条重要的：

如何在Oracle中复制表结构和表数据

1. 复制表结构及其数据： 

create table table_name_new as select * from table_name_old 

2. 只复制表结构： 

create table table_name_new as select * from table_name_old where 1=2; 

或者： 

create table table_name_new like table_name_old 

3. 只复制表数据：

如果两个表结构一样：

insert into table_name_new select * from table_name_old 

如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

创建带主键的表：

create table stuInfo(stuID int primary key,stuName varchar2(20),age int);

或是不直接增加主键

alter table stuInfo add constraint stuInfo _PK primary key (stuID)

　　95. 如果修改表名?

　　alter table old_table_name rename to new_table_name;

　　97. 如何知道用户拥有的权限?

　　SELECT * FROM dba_sys_privs ; --一个权限对应一条数据，这样对于同一个用户就有多条数据了。

　　98. 从网上下载的ORACLE9I与市场上卖的标准版有什么区别？

　　从功能上说没有区别，只不过oracle公司有明文规定；从网站上下载的oracle产品不得用于商业用途，否则侵权。

　　101. 如何搜索出前N条记录？

SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno; 

Select * from a where rownum　

　　104. 如何统计两个表的记录总数?

　　select (select count(id) from aa)+(select count(id) from bb) 总数 from dual; --总数那是没有单引号的，双引号可以。

　　106. 如何在给现有的日期加上2年？(

　　select add_months(sysdate,24) from dual; -- 2015/4/27 9:28:52

　　110. tablespace 是否不能大于4G?

　　没有.

　　111. 返回大于等于N的最小整数值?

　　SELECT CEIL(N) FROM DUAL;

　　112. 返回小于等于N的最小整数值?

　　SELECT FLOOR(N) FROM DUAL;

　　113. 返回当前月的最后一天?

　　SELECT LAST_DAY(SYSDATE) FROM DUAL;

　　;

　　115. 如何找数据库表的主键字段的名称?

　　SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME'; --我没有查出来。

　　116. 两个结果集互加的函数?

　　SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;

　　117. 两个结果集互减的函数?

　　SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

　　139. 如何查找重复记录？

　　SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAMe WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

　　140. 如何删除重复记录？

　　DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

　　162. 如何知道表在表空间中的存储情况?

　　select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name; --把&tablespace_name改成相应的表空间名称。