第一篇 基本操作
--解锁用户 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 seion 权限，赋予权限的语法 grant 权限名 to 用户； grant create seion 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 seion 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 seion 。 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
insert into stuInfo values（3,\'大大\',\'男\',18，4321543,default); insert into stuInfo values(1,\'张三\',\'男\',10); select *from stuInfo;
drop table stuInfo;
create table claInfo( 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 claInfo(cid) on delete cascade ) insert into claInfo values(1,\'1班\'); insert into claInfo values(2,\'2班\'); insert into claInfo values(3,\'3班\'); insert into claInfo values(4,\'4班\');
select *from claInfo; select *from stuInfo;
insert into stuInfo values(1001,\'张三\',2); insert into stuInfo values(1002,\'张四\',4);
update claInfo set cid=1 where cid=8;
drop table stuInfo;--要先删除这个 drop table claInfo; --再删除这个
delete claInfo where cid=4 ;--同时删除这两个表中的4
--删除用户的时候
drop user yc1 [cascade] --删除用户的同时把它创建的对象都一起删除
--修改表
--
1、添加表中字段
--alter table 表名 add 字段名 类型
alter table claInfo add status varchar2(10) default \'未毕业\'
--
2、修改已有字段的数据类型
--alter table 表名 modify 字段名 类型 alter table claInfo modify status number(1)
--
3、修改字段名
--alter table 表名 rename column 旧字段名 to 新的字段名 alter table claInfo rename column cname to 班级名;
--
4、删除字段 --alter table 表名 drop column 字段名 alter table claInfo drop column status ;
--
5、修改表名
--rename 旧表名 to 新表名 rename claInfo to 班级信息；
--删除表
--
1、截断表效率高，每删除一次会产生一次日志
2、截断会释放空间，而delete不会释放空间
--删除表结构和数据 drop table 表名； --删除表中所有数据 truncate table claInfo; delete claInfo;
create table claInfo( cid int primary key, --班级id cname varchar2(20) not null unique , --班级名 stasuts varchar2(100) ); select *from claInfo;
--数据的操作
--增加数据语法
--insert into 表名[(列名，....)] values (对应的数据的值）；
insert into claInfo values(1,\'一班\',\'未毕业\');--需要按照表结构的顺序插入 insert into claInfo values(4,\'六班\',\'未毕业\'); insert into claInfo(cname,cid) values(\'二班\',2); --需要按照括号中的顺序插入，但是 not null primary key 必须插入的。
insert into claInfo(cname,cid) values(\'三班\',3);
--删除的语法
--delete 表名 [where 条件] delete claInfo where cid>=2;
--修改记录的语法
--update 表名 set [字段=\'值\' ] [where 条件] update claInfo set cname=\'三班\'; --会修改所有该字段 update claInfo set cname=\'四班\' where cid=1; update claInfo set cname=\'五班\', stasuts =\'未毕业\' where cid=3;
--alter table claInfo drop constraint SYS_C0011213;
--添加多个时可以使用序列 --用序列来做自动增长
create sequence seq_claInfo_cid start with 1001 increment by 1;
insert into claInfo values(seq_claInfo_cid.Nextval,\'七班\',\'未毕业\'); insert into claInfo values(seq_claInfo_cid.Nextval,\'八班\',\'未毕业\'); insert into claInfo values(seq_claInfo_cid.Nextval,\'九班\',\'未毕业\'); insert into claInfo values(seq_claInfo_cid.Nextval,\'十班\',\'未毕业\');
create table claInfo2( cid int primary key, --班级id cname varchar2(20) not null unique , --班级名 stasuts varchar2(100) ); select *from claInfo2; drop table claInfo2;
insert into claInfo2 select *from claInfo; insert into claInfo(cname,cid) select cname,cid from claInfo; alter table claInfo2 drop constraint SYS_C0011213;
select seq_claInfo_cid.nextval from dual; select seq_claInfo_cid.Currval from dual;
--直接创建一个新表，并拿到另一个表其中的数据 create table newTable as select cname,cid from claInfo; create table newTable1 as select *from claInfo;
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 sal1500 or comm is not null); --工资大于1500但是小于3000的
select *from emp where sal>1500 and sal
--时间区间
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
--pagesize 5 select *from(select rownum rw,a.* from (select *from emp ) a where rownum 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
--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_脚本\\user.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 paword;//建用户名和密码oracle ,oracle
2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username;
3.connect username/paword//进入。
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$seion表
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;
89.字符串的连接
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
紧急插入几条重要的：
如何在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
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改成相应的表空间名称。
