很久没有写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