Oracle语法

---创建用户bbsuser（dba权限操作）---
create user bbsuser identified by m123;

--删除用户bbsuser（dba权限操作）--
drop user bbsuser [cascade];

---显示当前用户---
show user;

---修改用户密码---
password;

---连接另一用户---
conn bbsuser/m123;

---打开,编辑sql文件---
start，edit d:\bbsuser.sql;

--将屏幕内容输出到b.sql文件中
spool d:\b.sql; --开始--
spool off --结束--

--linesize 设置显示行的宽度，默认80个字符---
show linesize;
set linesize 90;

--pagesize 设置每页显示的行数，默认是14--
show pagesize;
set pagesize 20;

---清屏代码--
clear;

---打开显示操作时间--
set timing on;

alter session set nls_date_format ='YYYY/MM/DD HH24:MI:SS'; --Oracle改日期的默认格式--

----------------------------------------------------------------用户管理-----------------------------------------------
--授系统权限给用户,须是system,sys用户操作--
grant connect to bbsuser; --连接数据库--

--授对象权限给用户,须是对象用户操作--
grant select on emp to bbsuser; --scott授权bbsuser查询emp表--

--收回对象权限从用户,须是对象用户操作--
revoke select on emp from bbsuser; --scott收回bbsuser查询emp表的权限--

--授系统权限给用户并希望用户可以继续授权,须是system,sys用户操作--
grant connect to bbsuser with admin option; --连接数据库--

--授对象权限给用户并希望用户可以继续授权,须是对象用户操作--
grant select on emp to bbsuser with grant option; --scott授权bbsuser查询emp表--

---指定bbsuser用户最多只能尝试登录三次，锁定时间为两天(dba权限操作)--
create profile lock_account limit failed_login_attempts 3 password_lock_time 2;
alter user bbsuser profile lock_account;

---给bbsuser用户解锁(dba权限操作)--
alter user bbsuser account unlock;

---指定bbsuser用户每10天修改一次密码，宽限时间为两天(dba权限操作)--
create profile password_edit limit password_life_time 10 password_grace_time 2;
alter user bbsuser profile password_edit;


---删除profile文件，受profile文件约束用户被释放
drop profile 文件名 [cascade];


---------------------------------------------------------------数据类型---------------------------------------------
--字符型--一个汉字两个字符---
--char 最大2000字符 例:char(10) '小红' 占10个字符,不节省空间 数据定长用char型，查询速度快--
--varchar2(10) 最大4000字符 例:varchar2(10) '小红' 占4个字符,节省空间--
---clob 字符型大对象---

--数字型--
---number 10的-38次方到10的38次方 可表示整数，也可表示小数---
----例 number(5,2) 表示小数有5位有效数，两位有效小数 范围-999.99 到 999.99---
---例 number(5) 表示5位整数 范围-99999 到 99999---

--日

期类型--
--date 包含年月日和时分秒--
--timestamp 精度更高的date数据型--

--图片类型--
--blob 二进制数据 存放图片/声音/视频--


--------------------------------------------------------------关于时间的--------------------------------------------
select to_char(sysdate /*当前时间*/, 'yyyy/mm/dd hh24:mi:ss') from dual;

改日期的默认格式：alter session set nls_date_format = 'yyyy-mm-dd';

恢复ORACLE默认格式 alter session set nls_date_format = 'dd-mon-yy ('31-1月-11')
察看日期的格式： select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';

永久设置日期格式 : 改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd


--------------------------------------------------------------建表--------------------------------------------------
create table student (
xh number(4) primary key,
xm varchar2(20),
sex char(2),
birthday date,
jxj number(7,2) ---奖学金---
);

create table class (
classid number(2),
cname varchar2(20)
);

desc student; --查看表结构--

alter table student add (classid number(2)); --修改表,增加一个classid字段--
alter table student modify (xm varchar2(30)); --修改字段长度--
alter table student modify (xm char(30)); --修改字段字符类型或字段名--
alter table student drop column jxj; --删除一个字段：奖学金--
rename student to stu; --修改表student的名字为stu--
drop table student; --删除表student--


--------------------------------------------------------------数据增删改查！！！！！！！！！--------------------------------
insert into student values('0001','张三','男','01-5月-11','1000038'); --所有字段都插入数据--
alter session set nls_date_format ='yyyy-mm-dd'; --Oracle改日期的默认格式--

insert into student (xh,xm,sex) values('0001','张三','男'); --部分字段插入数据--

insert into student (xh,xm,sex,birthday) values('0001','张三','男',null); --插入空值--
select * from student where birthday is null; --查询生日值为空的学生--
select * from student where birthday is not null; --查询生日值为非空的学生--



update student set sex='女' where xh='1'; --修改一个字段--
update student set sex='女',birthday='1988-10-29' where xh='1'; --修该多个字段--



delete from student where xh='1'; --删除一条记录--

delete from student; --删除表记录，表结构还在，可以用回滚日志恢复,速度慢--
savepoint abc; --设置保存点--
rollback to abc; --回滚至abc--

truncate table student; --删除表记录，表结构还在，不可恢复,速度快--

drop table student; --删除表,记录和结构都不在--


-------------------------------查询表scott.emp--------------------------
desc dept; --查看表结构--
select count(*) from dept; --查看有多少条记录--
select * from dept; --查看所有列-- 费时
select ename,sal,job,deptno from emp; --查询指定列-- 省时
select distinct deptno,job from emp; --取消重复行--

--在emp表查询SMITH工资，工作，部门--
select sal,job,deptno from scott.emp where ename='SMITH';
--显示雇员年工资--
select ename "姓名",sal*12 "年收入" from scott.emp; --月工资sal乘以12，不含月奖金--
select sal*12+nvl(comm,0)*12 "年薪",ename,comm from scott.emp; --月工资sal乘以12+月奖金乘以12，含月奖金--
--处理NUlL值--
nvl(值,0) --如果值为null则返回0，否则返回该值--
--连接字符串--
select ename || ' is a ' || job from scott.emp;

--查询入职时间晚于1981.05.01但早于1981.12.31的员工--
select ename,hiredate from scott.emp where hiredate>='01-5月-1981' and hiredate<='31-12月-1981'；
ENAME HIREDATE
---------- -----------
MARTIN 28-9月 -81
BLAKE 01-5月 -81
CLARK 09-6月 -81
KING 17-11月-81
TURNER 08-9月 -81
JAMES 03-12月-81
FORD 03-12月-81

--like操作符--
--%表示0到多个字符，_表示一个字符--
---显示名字第三个字母为O的所有员工姓名和工资--
select ename,sal from scott.emp where ename like '__O%';
ENAME SAL
---------- ----------
SCOTT 3000
--where条件使用in，可以避免或表达式冗余--
---查询编号是7369或7566或7844的员工姓名和编号，用in()语句!--
select ename,empno from scott.emp where empno in (7369,7566,7844);
ENAME EMPNO
---------- ----------
SMITH 7369
JONES 7566
TURNER 7844

--查询工资高于1000或是工作为CLERK的员工，同时还要姓名首写字母为J；
select * from scott.emp where (sal>1000 or job='CLERK') and ename like 'J%';

select * from scott.emp order by sal [asc]; --工资从低到高排序--
select * from scott.emp order by sal desc; --工资从高到低排序--

---部门号升序，部门内部工资降序排列---
select * from scott.emp order by deptno [asc],sal desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000

20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30

--别名“年薪”排序，打印报表时用到--
select ename "姓名",sal*12 "年薪" from scott.emp order by "年薪" desc;
姓名 年薪
---------- ----------
KING 60000
FORD 36000
SCOTT 36000
JONES 35700
BLAKE 34200
CLARK 29400
ALLEN 19200
TURNER 18000
MILLER 15600
WARD 15000
MARTIN 15000
ADAMS 13200
JAMES 11400

-----数据分组，MAX,MIN,SUM,AVG,COUNT 分组函数只能出现在选择列里，having,order by 语句中！-----
--显示最高工资的员工和工作岗位，工资--
select ename "姓名",job "岗位",sal "工资" from scott.emp where sal=(select max(sal) from scott.emp);
姓名 岗位 工资
---------- --------- ----------
KING PRESIDENT 5000

--显示工资高于平均工资的员工性息--
select * from scott.emp where sal>(select avg(sal) from scott.emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20

-----group by 和 having 子句 分组统计！-----
group by用于对查询结果分组统计
having 用于限制分组显示结果，对group by分组后的结果进行筛选
select 语句中同时包含 group by , having ,order by 那他们的顺序是group by,having,order by !

--显示每个部门的平均工资和最高工资--
select avg(sal) "平均工资",max(sal) "最高工资" ,deptno "部门" from scott.emp group by deptno;
平均工资

最高工资 部门
---------- ---------- ----------
1566.66667 2850 30
2175 3000 20
2916.66667 5000 10

--显示平均工资低于2000的部门的平均工资，部门号--
select avg(sal),max(sal),deptno from scott.emp group by deptno having avg(sal)<2000;
AVG(SAL) MAX(SAL) DEPTNO
---------- ---------- ----------
1566.66667 2850 30

--显示每个部门每种岗位的平均工资和最低工资--
select avg(sal) "平均工资",min(sal) "最低工资" ,deptno "部门",job "工作" from scott.emp group by deptno,job;
平均工资 最低工资 部门 工作
--------- ---------- ---------- ---------
1300 1300 10 CLERK
2450 2450 10 MANAGER
5000 5000 10 PRESIDENT
3000 3000 20 ANALYST
950 800 20 CLERK
2975 2975 20 MANAGER
950 950 30 CLERK
2850 2850 30 MANAGER
1400 1250 30 SALESMAN



-----------------------------------------------------—-----------表的复杂查询：多表查询！-------------------------------------------
--显示员工名字，所在部门，部门名字 要用到dept表和emp表---
select a.ename,a.deptno,b.dname,b.deptno from scott.emp a,scott.dept b where a.deptno=b.deptno;

--显示部门号为10的部门名，员工名和工资--
select b.dname,a.ename,a.sal,a.deptno from scott.emp a,scott.dept b where a.deptno='10' and b.deptno='10';
DNAME ENAME SAL DEPTNO
-------------- ---------- ---------- ----------
ACCOUNTING CLARK 2450 10
ACCOUNTING KING 5000 10
ACCOUNTING MILLER 1300 10

---显示各个员工的姓名，工资，以及工资级别 用到emp表和salgrade表 还有between语句---
select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b where a.sal between b.losal and b.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

--显示员工名字，员工工资，所在部门名字，按部门排序--
select a.ename,a.sal,b.dname from scott.emp a,scott.dept b where a.deptno=b.deptno order by a.deptno;
ENAME SAL DNAME
---------- ---------- --------------
CLARK 2450 ACCOUNTING
KING 5000 ACCOUNTING
MILLER 1300 ACCOUNTING
JONES 2975 RESEARCH
FORD

3000 RESEARCH
ADAMS 1100 RESEARCH
SMITH 800 RESEARCH
SCOTT 3000 RESEARCH
WARD 1250 SALES
TURNER 1500 SALES
ALLEN 1600 SALES
JAMES 950 SALES
BLAKE 2850 SALES
MARTIN 1250 SALES


-----------------自连接查询------------------
----选出雇员名字是FORD的老板----
select w.ename "下级",b.ename "上级" from scott.emp w,scott.emp b where b.empno=w.mgr and w.ename='FORD';
下级 上级
---------- ----------
FORD JONES

----------------------------------------------------------子查询----------------------------------------
子查询是指嵌入在其它SQL语句中的select语句，也叫嵌套查询
-------单行子查询，只返回一行数据的子查询语句------
---查询和SMITH在同一部门的员工---
select * from scott.emp where deptno=(select deptno from scott.emp where ename='SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7902 FORD ANALYST 7566 1981-12-03 3000 20

-------多行子查询，返回多行数据的子查询语句------
---查询和部门10工作相同的员工---
select * from scott.emp where job in (select job from scott.emp where deptno='10');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
7900 JAMES CLERK 7698 1981-12-03 950 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7369 SMITH CLERK 7902 1980-12-17 800 20

-------多行子查询，使用all操作符------
--查询工资比部门30所有员工工资高的员工(比30部门最高工资高)--
select * from scott.emp where sal>all(select sal from scott.emp where deptno='30');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -

-------- ---------- ---------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7902 FORD ANALYST 7566 1981-12-03 3000 20

-------多行子查询，使用any操作符------
--查询工资比部门30任意一个员工工资高的员工(比30部门最低工资高)--
select * from scott.emp where sal>any(select sal from scott.emp where deptno='30');

--------------------多列子查询--------------------
----查询与SMITH在同一部门且岗位相同的员工----
select * from scott.emp where (job,deptno)=(select job,deptno from scott.emp where ename='SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7876 ADAMS CLERK 7788 1987-05-23 1100 20

------在from子句中使用子查询,必须给子句取个别名 如a1!!!!!!!!!!!!!!------
---显示高于自己部门平均工资的员工的信息---
select a2.ename,a2.sal,a2.deptno,a1.mysal from scott.emp a2,(select deptno,avg(sal) mysal from scott.emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
ENAME SAL DEPTNO MYSAL
---------- ---------- ---------- ----------
BLAKE 2850 30 1566.66667
ALLEN 1600 30 1566.66667
FORD 3000 20 2175
SCOTT 3000 20 2175
JONES 2975 20 2175
KING 5000 10 2916.66667


----------------------------------------------分页查询，指定查询列分页只需改动最里面一个子查询！！！------------------------------------------------------
1.显示所有记录 select a1.*,rownum rn from (select * from scott.emp) a1;

2.显示前10记录 select a1.*,rownum rn from (select * from scott.emp) a1 where rownum<=10;

3.显示6-10条记录 select * from (select a1.*,rownum rn from (select * from scott.emp) a1 where rownum<=10) where rn>=6;

4.在6-10条记录中只显示姓名和薪水 select * from (select a1.*,rownum rn from (select ename,sal from scott.emp) a1 where rownum<=10) where rn>=6;
ENAME SAL RN
---------- ---------- ----------
BLAKE 2850 6
CLARK 2450 7
SCOTT 3000 8
KING 5000 9
TURNER 1500 10

5.显示薪水从高到低的前5条记录 select a1.*,rownum rn from (select * from scott.emp order by sal desc) a1 where rownum<=5;
EMPNO ENAME JOB

MGR HIREDATE SAL COMM DEPTNO RN
------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10 1
7788 SCOTT ANALYST 7566 1987-04-19 3000 20 2
7902 FORD ANALYST 7566 1981-12-03 3000 20 3
7566 JONES MANAGER 7839 1981-04-02 2975 20 4
7698 BLAKE MANAGER 7839 1981-05-01 2850 30 5


-----------------------------------用查询数据创建新表,可以备份表----------------------------
create table mytable (id,ename,sal) as select empno,ename,sal from scott.emp;
select * from mytable;
ID ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300

已选择14行。

SQL> desc mytable;
名称 是否为空? 类型
----------------------------------------------------- -------- -------------
ID NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)


----------------------合并查询--------------------
1.union 用于取得两个结果的并集，会自动去掉结果集中重复行。
select * from scott.emp where sal>2500 union select * from scott.emp where job='MANAGER';
2.union all 用于取得两个结果的并集，但不会去掉结果集中重复行，也不会排序！
select * from scott.emp where sal>2500 union all select * from scott.emp where job='MANAGER';
3.intersect 用于取得两个结果的交集。
select * from scott.emp where sal>2500 intersect select * from scott.emp where job='MANAGER';
4.minus 用于取得两个结果的差集，前面的结果减后面的结果！
select * from scott.emp where sal>2500 minus select * from scott.emp where job='MANAGER';





















oracle创建表空间，创建用户以及授权


1.表空间。

表空间是数据库中最大的逻辑单位，一个 Oracle 数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。

每个表空间是由一个或多个数据文件组成的，一个数据文件只

能与一个表空间相关联。

表空间的大小等于构成该表空间的所有数据文件大小之和。

创建表空间的语法是：

CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]]
[AUTOEXTEND [OFF|ON]];

举例：
Sql代码
1. CREATE TABLESPACE data01
2. DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\DATA01.dbf' SIZE 10M
3. UNIFORM SIZE 128k;

删除表空间:
Sql代码
1. DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

修改表空间大小
Sql代码
1. ALTER DATABASE DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\DATA01.dbf' RESIZE 20M;

2.
//创建临时表空间
Sql代码
1. create temporary tablespace CDPF_TEMP
2. tempfile 'C:\oracle\product\10.2.0\oradata\orcl\CDPF_TEMP01.dbf'
3. size 32m
4. autoextend on
5. next 32m maxsize 2048m
6. extent management local;

//创建用户并指定表空间
Sql代码
1. create user CDPFZX identified by CDPFZX
2. default tablespace CDPFZX_DATA
3. temporary tablespace CDPF_TEMP;

//给用户授予权限
Sql代码
1. GRANT CONNECT,RESOURCE TO CDPFZX;

//以后以该用户登录，创建的任何数据库对象都属于CDPF_TEMP 和CDPFZX_DATA表空间，这就不用在每创建一个对象给其指定表空间了。