Oracle常用SQL语句复习

很久没有写sql语句了，生疏了许多，快忘记怎么写了...
今天突然想起，抽空复习一下，把遗落的知识检起来~
虽说都是些简单的语句，但有时用到时，却一时想不起来
还是多复习复习为好~指不定什么时候能排上大用场呢。
今天复习内容如下，记录一下，便于以后查阅，明天抽空继续...

--创建一个永久性表空间 ，路径可选
create tablespace worktbs
datafile 'D:\worktbs01.dbf'  size 10M;

-- 创建一个自动增长的表空间worktbs
CREATE TABLESPACE worktbs
DATAFILE 'D:\ORACLE\ORADATA\huang\WORKTBS01.DBF' 
SIZE 10M AUTOEXTEND ON;

--创建临时表空间
create temporary tablespace tempworktbs  tempfile 'D:\Oracle\oradata\huang\tempworktbs01.dbf'  size 10M;

--删除表空间（同时删除文件）
drop tablespace worktbs including contents and datafiles  

--删除表空间（不删除文件）
drop tablespace worktbs

--查询表空间基本信息
select * from DBA_TABLESPACES;

--查看正在运行的后台进程
SELECT * FROM v$bgprocess WHERE paddr <> '00';

--创建新用户并指定表空间  
create user hk identified by hk default tablespace worktbs temporary tablespace temp;

--查看用户hk的信息
Select * from dba_users where username = 'HK';

--修改用户口令(密码需以字母开头，且不包含特殊字符)
alter user hk identified by hk;
  
--删除用户
drop user hk 

--删除用户（用户中有信息时，用cascade）
drop user hk cascade

--授予权限

--连接权限
grant connect to hk;
--使用表空间权限
grant resource to hk;
--授予创建表权限
grant create table to hk;
--DBA系统权限
grant  dba to hk;

-- 授予用户使用表空间 worktbs 的权限
ALTER USER HK
  QUOTA UNLIMITED ON worktbs;
  
  
--授予对象权限
--注意做上面这些事情时，你必须使用emp表的所有者scott登陆
grant select on emp to hk;
--使用管理员sys或者system授权，那么管理员登陆后就处于管理员所对应
--的模式下面，所以就必须加上模式名.
grant select on scott.emp to hk;
--在做其他事情也是同上。
grant delete|update|all on scott.emp to hk;

--锁定用户
alter user hk account lock

--解除锁定
alter user hk account unlock

--连接
Connnect  hk/hk

--查询用户创建的表信息
select * from user_tables;


--创建表userinfo
CREATE TABLE userinfo(
   stu_id varchar2(10) not null,
   stu_name varchar(20) not null
)

--添加记录（指定添加字段）
insert into userinfo (stu_id,stu_name)values(1,'1');
commit;

--添加记录（默认全部字段）
INSERT INTO userinfo  VALUES (2,'alex','1');
commit;

--如果表中有记录，删除所有记录，以便于修改表中字段类型
delete   userinfo

--将需要更改类型字段，全部赋值为空
update userinfo t set t.stu_sex=''

--修改userinfo表中字段类型为number      
ALTER TABLE userinfo MODIFY (stu_id NUMBER);
ALTER TABLE userinfo MODIFY (stu_name varchar(20));
ALTER TABLE userinfo MODIFY (stu_sex NUMBER);

--添加一个字段属性(注意两种创建方式)  
ALTER TABLE userinfo ADD("upwd" varchar(50) not null);
ALTER TABLE userinfo ADD(stu_sex varchar(50) );

--删除一个字段属性
ALTER TABLE userinfo DROP COLUMN "upwd";
ALTER TABLE userinfo DROP COLUMN upwd;
  
--删除表信息及结构 
DROP TABLE userinfo;


--GRANT 操作授权
--赋权限，让其他用户查询dept表
GRANT SELECT ON scott.emp TO hk
--赋角色
GRANT RESOURCE TO hk
--REVOKE –解除权限
--在scott下，解除其他用户查询dept表的权力
revoke select on scott.emp from hk

--解除alex用户的RESOURCE权限
REVOKE "RESOURCE" FROM hk

--表示把创建表的权限赋予所有人
grant create session to public;
--返回当前用户所有的对象权限
select * from user_tab_privs

--对象权限可以控制到列 ,注意:查询和删除不能控制到列    
grant update(name) on mytab to hk; 
grant insert(id) on mytab to hk; 


--权限的传递 
--系统权限的传递： 
grant alter table to A with admin option; 
--那么A可以通过把该权限传递给B，如果想B也可以传递下去那么可以也带上with admin option 
grant alter table to B; 
--对象权限的传递: 
grant select on mytab to A with grant option; 
--那么A可以把在表mytab的select权限赋予给B，如果B想也能传递该select权限也可以带上with grant option 
grant select on mytab to B; 



----------------------------------------------------------------------------------------------------
--创建表user1
CREATE TABLE user1(
   stu_id number not null,
   stu_name varchar(20) not null,
   stu_add varchar(50) 
)

--插入值
insert into user1 values (5,'用户5','四川路5号');
commit;

--创建表user2
CREATE TABLE user2(
   stu_id number not null,
   stu_name varchar(20) not null,
   stu_add varchar(50) 
)

select * from user1;
select * from user2;

--批量插值
INSERT INTO user2(stu_id,stu_name,stu_add) SELECT stu_id,stu_name,stu_add FROM user1;

--复制一个表（带结构带值）
CREATE TABLE users7 AS (SELECT * FROM user1);

--复制一个表（只复制表结构）
CREATE TABLE users6 AS (SELECT * FROM user1 where 1<0);

--复制一个表（选择字段，带值）
CREATE TABLE users5 AS SELECT stu_id,stu_name FROM user1 WHERE stu_id=5

--TCL - 事务控制语言------------------------------------------------------------------------------------------------
--创建表 test
create table test
(
       testid number not null,
       testname varchar2(20) not null,
       testvalue varchar2(20),
       testdate date,
       testcontent varchar(1000)
)

--删除表 test 内的信息
delete from test

--查询表 test
select * from test


--插入信息
insert into test values (01,'测试1','测试1',to_date('2008-8-14','YYYY-MM-DD'),'');
--设置保存点 one
savepoint one;
--插入信息
insert into test values (02,'test3','测试2','','');
--设置保存点 two
savepoint two;
--回滚到保存点 one
rollback to savepoint one;

--oracle函数 - 单行函数（注：函数可嵌套使用）---------------------------------------------------------------

--当前字符长度（6）
select length('abcdef') from dual
--当前字符字节（10）
SELECT LENGTHB('abc你好def') FROM dual 
--ltrim：左边空格字符去掉 rtrim：右边空格字符去掉  trim：去掉两边的空格
select ltrim(' 123 ') from dual;
select rtrim(' 123 ') from dual;
select trim(' 123 ') from dual;

--截取字符串 前3个字符
SELECT SUBSTR('abcdefg',1,3) FROM dual;

--截取字符串 后3个字符 
SELECT SUBSTR('abcdefg',LENGTH('abcdefg')-3+1,3) FROM dual;

--testname字段为从第2位开始的3个字符
select testid,substr(substr(testname,1,4),length(substr(testname,1,4))-3+1,3) as test from test;

--获取当前时间
SELECT sysdate FROM dual;
select current_date from dual ;
--ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';
--next_date：给定一个时间，查询下一个日期时间
SELECT NEXT_DAY(sysdate,'星期三') FROM dual;

--to_char：转换成为char类型
--to_date：转换成日期类型，和to_date类型相反
--to_number：转换整型，非数字类型不能转换
SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual;
SELECT TO_Date('2060-09-12','yyyy-mm-dd') FROM dual;

--排序(倒排)
SELECT * FROM user1 ORDER BY stu_id DESC

--查询当前用户
SELECT user FROM dual;


--decode：语句判断函数（和switch 类似）
--语法：SELECT SUM (DECODE(字段名,'条件',为true,为false)) FROM 表名;
--查询一个表中男的数量
SELECT SUM (DECODE(sex,'男',1,0)) 男人数 FROM userinfo;

--多条件判断，0为女，1为男，其他为'未知'
select stu_name,decode(stu_sex,'0','女','1','男','未知') from userinfo

--nvl空值判断函数，为空显示'未输入'
SELECT nvl(stu_sex,'未输入') FROM userinfo

--nvl2 ,类似if-else 。
SELECT stu_id,stu_sex, NVL2(stu_sex,'不为空','空') FROM userinfo;

--NULLIF， stu_sex是0则为null，不是则返回原值
SELECT stu_id,stu_sex, NULLIF(stu_sex,'0') FROM userinfo;

--oracle函数 - 分组函数 ---------------------------------------------------------------
--聚合函数：sum，avg，max，min，count（注：聚合函数不能作为条件出现在where条件的后面）

--删除scoreinfo表
drop table scoreinfo;
commit;

--创建scoreinfo表
create table scoreinfo
(

       stu_id number primary key,
       stu_name varchar2(20) not null,
       stu_sex varchar2(2),
       stu_chinese number(10,2) default 0,
       stu_math number(10,2) default 0,
       stu_english number(10,2) default 0,
       stu_ext1 varchar2(200),
       stu_ext2 varchar2(200),
       stu_ext3 varchar2(200)
)
--插入数据
insert into scoreinfo  values (1,'jack','1',89,77.5,99,'','','');
insert into scoreinfo  values (2,'lily','2',47,56,91,'','','');
insert into scoreinfo  values (3,'jim','1',90,92,96,'','','');
insert into scoreinfo  values (4,'linda','2',98,68,88,'','','');
commit;

--利用聚合函数查询
select * from scoreinfo
select sum(stu_chinese) from scoreinfo
select avg(stu_chinese) from scoreinfo
select max(stu_chinese) from scoreinfo
select min(stu_chinese) from scoreinfo
select count(stu_id) from scoreinfo

--GROUP BY，查询各个班级chinese总成绩
SELECT stu_ext1,sum(stu_chinese) from scoreinfo group by stu_ext1;

--HAVING子句，查询chinese总成绩大于150的班级
SELECT stu_ext1,sum(stu_chinese) from scoreinfo group by stu_ext1 having sum(stu_chinese)>150;

--oracle函数 - 分析函数-----------------------------------------------------------------------------------------

--用管理员权限,赋予用户查询 scott.emp 表权限
GRANT SELECT ON scott.emp TO hk

--根据sal高低排序,排名没有并列
SELECT empno, deptno, ename,sal,ROW_NUMBER() OVER (ORDER BY sal DESC) AS rank FROM scott.emp
       
--分组排名,排名没有并列
select empno,deptno,ename,sal,row_number()over(partition by deptno order by sal desc ) as rank from scott.emp 

--排名有并列,下一名不顺排
select empno,deptno,ename,sal,rank() over(order by sal desc) as rank from scott.emp

--分组排名,排名有并列,下一名不顺排
select empno,deptno,ename,sal,rank() over(partition by deptno order by sal desc) as rank from scott.emp

--排名有并列,下一名顺排
select empno,deptno,ename,sal,dense_rank() over (order by sal desc) as rank from scott.emp

--分组排名,排名有并列,下一名顺排
select empno,deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc )as rank from scott.emp

评论

1 楼 baobaocao521 2010-07-08  

总结的很详细，很有用