oracle 完整总结版

1、创建用户
CREATE USER <用户名> IDENTIFIED BY <密码>
ALTER USER SYSTEM IDENTIFIED BY a12345;
DROP USER SYSTEM
2、给用户赋予权限
连接权限 CONNECT
使用资源权限 RESOURCE
创建表的权限 CREATE TABLE
DBA DBA
GRANT <权限名称> TO <用户名>
REVOKE <权限名称> FROM <用户名>
3、创建表
CREATE TABLE demo_user(
ur_id Integer PRIMARY KEY NOT NULL,
ur_user_name VARCHAR2(20) NOT NULL UNIQUE,
ur_password VARCHAR2(20) NOT NULL
);
4、--创建表结构
DEFAULT NOT NULL UNIQUE
CONSTRAINT pk PRIMARY KEY (字段列表)
CONSTRAINT ck CHECK (check条件)
CONSTRAINT fk FOREIGN KEY (从表外键字段) REFERENCES 主表名(主表被引用的字段)
5、--修改表结构
--修改表名
ALTER TABLE demo_test1 RENAME TO demo_demo1;
--修改字段字段名
ALTER TABLE demo_demo1
RENAME COLUMN tst_name TO tst_user_name;
--修改字段的类型
ALTER TABLE demo_demo1
MODIFY (tst_sex VARCHAR2(4));
--添加字段
ALTER TABLE demo_demo1
ADD tst_name VARCHAR2(20) ;
--删除字段
ALTER TABLE demo_demo1
DROP COLUMN tst_user_name;
--删除约束
ALTER TABLE demo_demo1
DROP CONSTRAINT ck_tst_sex;
--添加约束
ALTER TABLE demo_demo1
ADD CONSTRAINT ck_tst_sex CHECK (tst_sex='男' OR tst_sex='女' OR tst_sex='a');
6、例子包含序列对象sequence
-- 聊天工具的基本表
--用户表
CREATE TABLE chat_user(
ur_id INTEGER NOT NULL,
ur_account VARCHAR2(20) NOT NULL UNIQUE,
ur_password VARCHAR2(20) NOT NULL,
ur_alia_name VARCHAR2(100),
ur_name VARCHAR2(100),
ur_sex CHAR(4) DEFAULT '女' NOT NULL ,
ur_age INTEGER DEFAULT 1 NOT NULL,

 CONSTRAINT pk_user PRIMARY KEY (ur_id),
 CONSTRAINT ck_ur_sex CHECK (ur_sex = '女' OR ur_sex='男'),
 CONSTRAINT ck_ur_age CHECK (ur_age>=1 AND ur_age<=150)

);

INSERT INTO chat_user VALUES(seq_chat.nextval,'0001','123','Demo','张三','男',20);
SELECT * FROM chat_user;

-- 聊天消息表
CREATE TABLE chat_message(
mg_id INTEGER NOT NULL,
mg_from INTEGER NOT NULL,
mg_to INTEGER NOT NULL,
mg_time timestamp NOT NULL,
mg_content VARCHAR2(300),

 CONSTRAINT pk_message PRIMARY KEY (mg_id),
 CONSTRAINT fk_mg_from FOREIGN KEY (mg_from) REFERENCES chat_user(ur_id),
 CONSTRAINT fk_mg_to FOREIGN KEY (mg_to) REFERENCES chat_user(ur_id)

);
--好友表
CREATE TABLE chat_friend(
fr_self_id INTEGER NOT NULL,
fr_friend_id INTEGER NOT NULL,

CONSTRAINT pk_friend PRIMARY KEY (fr_self_id,fr_friend_id),
CONSTRAINT fk_self_id FOREIGN KEY (fr_self_id) REFERENCES chat_user(ur_id),
CONSTRAINT fk_friend_id FOREIGN KEY (fr_friend_id) REFERENCES chat_user(ur_id)

);
--群组表
CREATE TABLE chat_group(
gr_id INTEGER NOT NULL,--群组ID
gr_account VARCHAR2(20) NOT NULL UNIQUE,--群账号
gr_name VARCHAR2(100) NOT NULL,--群名称
gr_create_ur_id INTEGER NOT NULL,--创建者id
gr_image VARCHAR2(200),--群头像
gr_create_time TIMESTAMP NOT NULL,--创建时间
gr_title VARCHAR2(400),--群主题

CONSTRAINT pk_gr_id PRIMARY KEY (gr_id),
CONSTRAINT fk_gr_create_ur_id FOREIGN KEY (gr_create_ur_id) REFERENCES chat_user(ur_id)

);
--群成员表
CREATE TABLE chat_group_member(
gm_gr_id INTEGER NOT NULL,--群id
gm_ur_id INTEGER NOT NULL,--成员id
gm_type VARCHAR2(3) DEFAULT '1' NOT NULL, --成员等级 1普通成员 2 普通管理员 3超级管理员

 CONSTRAINT pk_group_member PRIMARY KEY (gm_gr_id,gm_ur_id),
 CONSTRAINT fk_gm_gr_id FOREIGN KEY (gm_gr_id) REFERENCES chat_group(gr_id),
 CONSTRAINT fk_gm_ur_id FOREIGN KEY (gm_ur_id) REFERENCES chat_user(ur_id),
 CONSTRAINT ck_gm_type CHECK (gm_type IN ('1','2','3'))

);

--公告表
CREATE TABLE chat_notice(
nt_id INTEGER NOT NULL, --公共ID
nt_gr_id INTEGER NOT NULL,--群id
nt_ur_id INTEGER NOT NULL,--发布人id
nt_time TIMESTAMP NOT NULL,--发布时间
nt_content VARCHAR2(500) NOT NULL,-- 公告内容

 CONSTRAINT pk_notice PRIMARY KEY (nt_id),
 CONSTRAINT fk_nt_gr_id FOREIGN KEY (nt_gr_id) REFERENCES chat_group(gr_id),
 CONSTRAINT fk_nt_ur_id FOREIGN KEY(nt_ur_id) REFERENCES chat_user(ur_id)

);
--群聊天信息表
CREATE TABLE chat_group_message(
grm_id INTEGER NOT NULL,--消息ID
grm_gr_id INTEGER NOT NULL,--群ID
grm_ur_id INTEGER NOT NULL,--说话人ID
grm_time TIMESTAMP NOT NULL,--说话时间
grm_content VARCHAR2(500) NOT NULL,--说话内容

 CONSTRAINT pk_group_message PRIMARY KEY (grm_id),
 CONSTRAINT fk_grm_gr_id FOREIGN KEY (grm_gr_id) REFERENCES chat_group(gr_id),
 CONSTRAINT fk_grm_ur_id FOREIGN KEY (grm_ur_id) REFERENCES chat_user(ur_id)

);

--序列对象
nextval

CREATE SEQUENCE seq_chat; --一般这样就够了
SELECT seq_chat.nextval FROM dual;
SELECT seq_chat.currval FROM dual;

CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;

CREATE SEQUENCE seq_test
INCREMENT BY -1
START WITH 100
MAXVALUE 100
NOMINVALUE
NOCYCLE
CACHE 10;

SELECT seq_test2.nextval FROM dual;

CREATE SEQUENCE seq_test2 --指定sequence的一些限制
INCREMENT BY 2
START WITH 5
MAXVALUE 10
MINVALUE -10
CYCLE
CACHE 5;
7、运算符
java sql

     >

< <

= >=
<= <=
!= <> !=
== =

&& and
|| OR
! NOT

         IN
         LIKE  % 0到n个任何符号
               _ 一个任何符号
         BETWEEN a AND b
         IS [NOT]  --是否等于 null

例子
--1、将账号为001的用户密码改成888888
UPDATE chat_user SET ur_password = '888888' WHERE ur_account = '001';
--2、将账号为002的用户姓名改成李四，年龄改成20
UPDATE chat_user SET ur_name = '李四', ur_age=20 WHERE ur_account = '002';
--3、将姓名中包含有”山“的用户的别名都改成 "华山"
--4、将姓名中包含有"A" 和 "B"的年龄都改成17
UPDATE chat_user SET ur_age=17 WHERE ur_name LIKE '%A%' AND ur_name LIKE '%B%';
--5、将年龄在18到20的所有用户的性别，改成女
UPDATE chat_user SET ur_sex='女' WHERE ur_age>=18 AND ur_age<=20;
UPDATE chat_user SET ur_sex='女' WHERE ur_name BETWEEN 'a' AND 'b';--表示 a-b之间，a一定要小于等于b

--6、将所有聊天消息中包含有 "资本主义" 或 "反党" 的内容，都改成 "*****"
UPDATE chat_message SET mg_content='*******' WHERE mg_content LIKE '%资本主义%' OR mg_content like '%反党%';
--7、将id为2的聊天消息前面，都加上 "002说："
UPDATE chat_message SET mg_content='002说:'||mg_content WHERE mg_id=2;
--8、给群聊消息表中所有的聊天消息前面，都加上 对应的聊天人id+说:开头
UPDATE chat_group_message SET grm_content = grm_id || grm_content;
8、统计函数及日期
统计函数
sum--求和
avg--平均
count--总数
max--最大值
min--最小值
NVL (expr1, expr2):expr1为NULL，返回expr2；不为NULL，返回expr1
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1
SELECT count(*) FROM demo_user;--10
SELECT count(ur_age) FROM demo_user;--不统计null 9
SELECT sum(ur_age + ur_id) FROM demo_user;--不把null计算 null当0计算
SELECT avg(nvl(ur_age,0)+ur_id) FROM demo_user;
SELECT max(ur_age) FROM demo_user;
--ASCII
--abcdef
--abcdefa

SELECT seq_test3.nextval FROM dual;
SELECT * FROM dual;
--日期
--ADD_MONTHS(日期,增值):追加一个月份
SELECT add_months(sysdate,1) FROM dual;
--MONTHS_BETWEEN(日期，日期):两日期相差多少月
SELECT months_between(to_date('2015-10-9','yyyy-mm-dd'),sysdate ) FROM dual;

LAST_DAY(日期):返回当月最大天数
NEXT_DAY(日期,星期)：获得某一日期后的第一个星期的值
TRUNC(date,[fmt]):舍去某一日期类型
EXTRACT(fmt from date):从当前日期提取一日期类型

9、字符串
--Initcap(char) Select initcap(‘hello’) from dual; Hello
SELECT initcap('hello world') FROM dual;
Lower(char) Select lower(‘FUN’) from dual; fun
Upper(char) Select upper(‘sun’) from dual; SUN
--Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams --trim()
--Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
SELECT rtrim('123abcefg123123', '123') FROM dual;

--Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back
SELECT Translate('11223344556633333','3456','abc') FROM dual;
--Replace(char, searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue
SELECT replace('11223344556633333','33','abc') FROM dual;

Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world

10、算术
Abs(n) Select abs(-15) from dual; 15
--Ceil(n) Select ceil(44.778) from dual; 45 获取大于这个数的最小整数 向上取整
Cos(n) Select cos(180) from dual; -.5984601
Sin(n) Select sin(0) from dual; 0
--Floor(n) Select floor(100.2) from dual; 100 小于自己的最大整数 Math
Power(m,n) Select power(4,2) from dual; 16
Mod(m,n) Select mod(10,3) from dual; 1
--Round(m,n) Select round(100.256,2) from dual; 100.26
Trunc(m,n) Select trunc(100.256,2) from dual; 100.25
Sqrt(n) Select sqrt(4) from dual; 2
Sign(n) Select sign(-30) from dual; -1

11、转换
TO_CHAR
SELECT to_char(1111) FROM dual;
TO_DATE
TO_NUMBER
SELECT to_number('1111')+2 FROM dual;
SELECT '111' + 2 FROM dual;

12、其它
--CHR和ASCII
SELECT ascii('A') FROM dual;
LPAD和RPAD
SELECT lpad('abcd',12,'ab') FROM dual;
TRIM
LENGTH
SELECT length('abc') FROM dual;
--DECODE 相当于java的switch语句
SELECT ur_id,decode(ur_id,
1,'啊啊啊啊啊啊啊啊啊',
6,'哦哦哦哦哦哦哦哦') test
FROM demo_user;
SELECT * FROM demo_user;
SELECT * FROM demo_user;

13、经典例子总结
--1.分析清楚结果 select
--2.来源 from
--3.过滤条件 where
--4.其它 order by

--1. 找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
SELECT ename
FROM scott.emp
WHERE ename like '__A%';

--2. 找出EMP表员工名字中含有A 和N的员工姓名。
SELECT ename
FROM scott.emp
WHERE ename like '%A%' AND ename like '%N%';

--3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
SELECT ename 姓名,sal + nvl(comm,0) as 工资, nvl(comm,0) 佣金
FROM scott.emp
WHERE comm IS NOT NULL
ORDER BY sal + nvl(comm,0) ASC, nvl(comm,0) DESC;

--4. 列出部门编号为20的所有职位。
SELECT DISTINCT job
FROM scott.emp
WHERE deptno=20;

--5. 列出不属于SALES 的部门。
SELECT * FROM scott.dept;
SELECT dname
FROM scott.dept
WHERE dname <> 'SALES';

--6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
SELECT ename, sal + nvl(comm,0) 工资
FROM scott.emp
WHERE NOT (sal + nvl(comm,0) BETWEEN 1000 AND 1500)
ORDER By (sal + nvl(comm,0)) DESC;

--7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
SELECT ename, job, (sal + nvl(comm,0))12
FROM scott.emp
WHERE job IN ('MANAGER','SALESMAN') AND (sal + nvl(comm,0))12 BETWEEN 15000 AND 20000;

--8. 说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM scott.EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

*9. 让SELECT 语句的输出结果为
(提示：USER_TABLES表中可以查出所有表的信息，如表名)
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM user_tables;
SELECT 'SELECT * FROM ' || table_name || ';' FROM user_tables;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
--10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？

--子查询
--GROUP BY 和 HAVING
--联合查询

--在查询语句中，嵌套另外一个查询 子查询
SELECT
FROM
WHERE
ORDER BY
--select 查询出每个员工的姓名 和 部门名称
SELECT scott.emp.ename, (SELECT scott.dept.dname FROM scott.dept WHERE scott.dept.deptno = scott.emp.deptno)
FROM scott.emp;

--from 用子查询查询出sal 在1500 DAO 2000之间的所有员工的姓名
SELECT ename, sal FROM(
SELECT * FROM scott.emp WHERE sal>=2500
)
WHERE sal<=4000;

--where 查询出SALES 和 RESEARCH部门的所有员工名称
SELECT ename
FROM scott.emp
WHERE scott.emp.deptno IN (SELECT deptno FROM scott.dept WHERE dname = 'SALES' OR dname='RESEARCH');

--分页 查询出第二页的员工信息（每页显示3条）
SELECT * FROM (SELECT scott.emp.*, rownum mynum FROM scott.emp WHERE rownum<=12)
WHERE mynum>=9;

SELECT * FROM scott.emp;

-- GROUP BY
--统计每个部门的人数
SELECT scott.emp.deptno,count(*)
FROM scott.emp
GROUP BY scott.emp.deptno;

SELECT (SELECT dname FROM scott.dept WHERE scott.dept.deptno=scott.emp.deptno),count(*)
FROM scott.emp
GROUP BY scott.emp.deptno;

--统计每个岗位的平均工资，显示岗位名称和平均工资
SELECT job, avg(sal + nvl(comm,0))
FROM scott.emp
--WHERE avg(sal + nvl(comm,0))>2000
GROUP BY job
HAVING avg(sal + nvl(comm,0))>2000;

-- 统计每个部门中，每个岗位的平均工资，显示部门名称、岗位名称、和平均工资
SELECT (SELECT dname FROM scott.dept WHERE scott.dept.deptno=scott.emp.deptno),job,avg(sal + nvl(comm,0))
FROM scott.emp
GROUP BY scott.emp.deptno,job
ORDER BY scott.emp.deptno;

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

--1．列出至少有一个员工的所有部门。
SELECT dname
FROM dept
WHERE deptno IN(
SELECT deptno
FROM emp
GROUP BY deptno
HAVING count(*)>0);

SELECT d.dname,count(e.ename)
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.dname
HAVING count(e.ename)>0;

SELECT d.dname,count(e.ename)
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.dname
HAVING count(e.ename)>0;

--2．列出薪金比“SMITH”多或相同的所有员工
SELECT ename FROM emp WHERE sal >= (SELECT sal FROM emp WHERE ename='SMITH') AND
ename <> 'SMITH';

--3．列出所有员工的姓名及其直接上级的姓名。
SELECT B.ename, (SELECT A.ename FROM emp a WHERE a.empno=B.mgr)
FROM emp B;

SELECT A.ename,B.ename
   FROM emp A  LEFT OUTER JOIN emp B   ON A.Mgr = B.Empno;

SELECT A.ename,B.ename
   FROM emp A  INNER JOIN emp B   ON A.Mgr = B.Empno;

--4．列出受雇日期早于其直接上级的所有员工。
SELECT B.ename,B.hiredate
FROM emp B
WHERE B.hiredate < (SELECT A.hiredate FROM emp A WHERE A.Empno=B.Mgr);

 SELECT A.Ename, A.Hiredate, B.ename, B.hiredate
    FROM emp A, emp B
    WHERE a.mgr = b.empno AND A.Hiredate<B.Hiredate;

--5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

--6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT A.ename, (SELECT B.dname FROM dept B WHERE B.Deptno=A.deptno)
FROM emp A
WHERE A.job='CLERK';

--7．列出最低薪金大于1500的各种工作。
SELECT distinct job FROM emp
GROUP BY job
HAVING min(sal)>1500;

--8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
SELECT ename FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname='SALES');

--9．列出薪金高于公司平均薪金的所有员工。
SELECT ename
FROM emp
WHERE sal>(SELECT avg(sal) FROM emp);

10．列出与“SCOTT”从事相同工作的所有员工。
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
14．列出所有员工的姓名、部门名称和工资。
15．列出所有部门的详细信息和部门人数。
16．列出各种工作的最低工资。
17．列出各个部门的MANAGER（经理）的最低薪金。
18．列出所有员工的年工资,按年薪从低到高排序。
SELECT * FROM employees;

--函数
--1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是￥和$。
SELECT to_char(salary,'L99,999.99') FROM employees;

--2. 列出前五位每个员工的名字，工资、涨薪后的的工资（涨幅为8%），以“元”为单位进行四舍五入。
SELECT FIRST_Name || LAST_NAME, SALARY, ROUND(salary*1.08, 2) FROM employees WHERE ROWNUM <=5;

14、关联及视图
--关联
-- 将多个表的字段，整合在一起，形成一个表（临时表）
-- 内关联
-- 相等内关联

-- 不等内关联
-- 外关联
-- 左外
-- 右外
-- 全关联
-- 笛卡尔乘积关联

--视图
create or replace view view_emp_dept
as select emp.ename,emp.job,dept.dname from scott.emp left join scott.dept on scott.emp.deptno=scott.dept.deptno
--也可以指定列名
create or replace view view_emp_dept (ename,job,dname)
as select emp.ename,emp.job,dept.dname from scott.emp left join scott.dept on scott.emp.deptno=scott.dept.deptno
--本次这二种效果一模一样
-- 查询出所有员工的部门名称和自己名称
SELECT A.ename, B.dname
FROM emp A INNER JOIN dept B
ON A.Deptno <> B.Deptno;

SELECT A.ename, B.dname
FROM emp A, dept B WHERE A.Deptno = B.Deptno;

--左外
以左边的表为标准，先列出左边的所有记录，跟右边的记录进行关联。
能关联上，就关联
不能关联上：
1）多余的数据，不敢
2）没有的数据，用null值代替

SELECT A.deptno,A.dname, B.ename
FROM dept A LEFT OUTER JOIN emp B
ON A.Deptno = B.Deptno;
--左外关联
SELECT A.dname, count(B.ename)
FROM dept A LEFT OUTER JOIN emp B
ON A.Deptno = B.Deptno
GROUP BY A.dname;

--右外关联
SELECT A.dname, count(B.ename)
FROM dept A RIGHT OUTER JOIN emp B
ON A.Deptno = B.Deptno
GROUP BY A.dname;

SELECT A.dname, count(B.ename)
FROM dept A INNER JOIN emp B
ON A.Deptno = B.Deptno
GROUP BY A.dname;

-- 全外关联
SELECT A.deptno,A.dname, B.ename
FROM dept A FULL JOIN emp B
ON A.Deptno = B.Deptno;

-- 笛卡尔乘积关联
SELECT A.deptno,A.dname, B.ename
FROM dept A CROSS JOIN emp B;