ITPub博客

首页 > 数据库 > Oracle > Oracle常用SQL语句复习

Oracle常用SQL语句复习

Oracle 作者:wade2004 时间:2013-09-21 14:20:55 0 删除 编辑

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

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

--创建临时表空间
create temporary tablespace tempworktbs  tempfile 'D:Oracleoradatahuang empworktbs01.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


 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12756094/viewspace-1116040/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-10-14