oracle sql语句笔记

Oracle Sql 笔记

1.起别名的时候双引号可以保证名称就是引号内内容
2. || 连接 SELECT LastName || Id AS AAA FROM employ;
3.Distinct 消除重复行
4.escape 转义 select * from t_char where a like ‘%%%’ escape ‘’
5.order by 排序 desc 降序 asc 升序

第三单元 单行函数
！dual是oracle中的一张虚拟表，表中只有一条数据
1.LOWER = 转成小写 UPPER = 转成大写 INTCAP = 首字母大写
2.字符串操作函数
CONCAT 拼接
SUBSTR(‘HelloWorld’,1,5) 提取从第一位开始的五个字符
LENGTH 长度
INSTR(‘HelloWorld’,‘W’) W在字符串中是第几位
LPAD(salary,10,’*’) 向左补齐十位 *****24000
RPAD 向右补齐
TRIM(‘H’ from ‘HelloWorld’) 删除指定字符
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, ‘a’) “Contains ‘a’?”
FROM employees
WHERE SUBSTR(job_id, 4) = ‘REP’;
3.数字操作函数
ROUND(45.926,2) 45.93 取小数点后两位，四舍五入
ROUND(45.926,0) 45
ROUND(45.926,-1) 50
TRUNC(45.926,2) 45.92 只要小数点后两位
TRUNC(45.926,0) 45
TRUNC(45.926,-2) 0
MOD(1600,300) 100 取余

5.其他常用单行函数
NVL(expr1,expr2) 如果expr1为空，则返回expr2
NVL2(expr1,expr2,expr3) 如果expr1为空，则返回expr3,否则返回expr2
NULLIF(expr1,expr2) 如果相等返回null,否则返回expr1
COALESCE(expr1,expr2,…exprn) 直到找到不为Null的值返回，否则返回null
6.条件表达式
CASE语句
case job_id
WHEN ‘IT_PROG’ THEN
1.10 * salary
ELSE
salary
END “REVISED_SALARY”

DECODE函数
decode(job_id,
‘IT_PROG’,
1.10 * salary,
salary) revised salary

第四单元 多表关联
Oracle定义的链接类型：
1、等于链接
2、不等链接
3、外连接（可细分为左外连接、右外连接）
4、自链接
等于链接语法：SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
“不等链接”语法： 使用不等链接符，包括> , < , !=, between and
外连接：SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
左外连接，table2的信息要全
交叉连接：CROSS JOIN

 全外连接：FULL JOIN ON

第五单元 分组计算函数(多行函数)
1.求和（SUM）
2.求平均值（AVG）
3.计数（COUNT）
4.求标准差（STDDEV）
5.求方差（VARIANCE）
6.求最大值（MAX）
7.求最小值（MIN）

SELECT dept_id,job_id,sum(salary) FROM employees GROUP BY department_id,job_id;
GROUP BY 中如果要用条件语句，不能用WHERE ,用HAVING

第六单元 子查询

单行比较：= >
多行比较：IN >ANY >ALL
ANY就是任意一个条件
ALL就是满足所有条件

第七单元 DML语句（数据操作语句，增删改查语句）

INSERT INTO VALUES

WITH CHECK OPTION 可以检查要输入的内容，是否符合目标子查询的Where条件
DELETE可以回滚 TRUNCATE删除整表不能回滚
MERGE 语句：比较整合语句
MEGRE INTO copy c USING empluyees e ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.dirstname = e.firstname;
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id,e.firstname)

第七单元 事物控制

ORACLE数据库通过事务来控制数据的一致性

第八单元 锁
更新记录 Select * from test WHERE PK1=1 For UPDATE NoWait;必须加这一条

第九单元 数据库对象–表

创建表
CREATE TABLE 表名
CREATE TABLE 表名2 AS SELECT * FROM 表名1;(复制列名和数据)
CREATE TABLE 表名2 AS SELECT * FROM 表名1 WHERE 1=2;(只复制列名)

更改表

1.添加列
ALTER TABLE 表名 ADD (列名 类型(量))
2.更改列
ALTER TABLE 表名 MODIFY (列名 类型(量))
3.删除列
ALTER TABLE 表名 DROP (列名)
4.删除表
DROP TABLE 表名
5.更改表名
RENAME oldname to newname;
6.清空表中所有内容，但保留表结构
TRUNCATE TABLE 表名

TRUNCATE 和 DELETE FROM table的区别：1）没有Rollback机会 2）HWM标位复位(数据库高水位)

第十单元 数据库对象–约束

常用的约束：NOT NULL ---- 非空约束
UNIQUE ---- 唯一性约束
PRIMARY KEY ---- 主键约束
FOREIGN KEY ---- 外键约束
CHECK ---- 自定义约束

约束的创建方法：
1.在创建表的时候同时创建约束
创建语句最后
CONSTRAINT 约束名 约束类型(约束字段)
2.另外单独创建约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(约束字段)

唯一性约束：CONSTRAINT 约束名 UNIQUE(约束字段)
外键约束： CONSTRAINT 约束名 FOREIGN KEY(department_id) REFERENCE deparments(department_id) 列中的值必须在父表中存在

删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名
ALTER TABLE 表名 DROP PRIMARY KEY CASCADE; 级联删除
失效/生效 约束：
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名

第十一单元 视图

创建语法：
CREATE VIEW 视图名
AS SELECT employee_id,lastname,salary
FROM employees
WHERE department_id = 80;
复杂创建语法：
CREATE VIEW 视图名
(name,minsal,maxsal,avgsal)
AS
SELECT d.department_name
,MIN(e.salary)
,MAX(e,salary)
,AVG(e.salary)
FROM employees e
,deparments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

第十二单元 序列-索引-同义词

创建序列：
CREATE SEQUENCE 序列名
INCREMENT BY 50 每次增长序列50
MAXVALUE 999999 最大序列号999999
NOCACHE NOCYCLE;
修改序列：
ALTER SEQUENCE 序列名
INCREMENT BY 50 每次增长序列50
MAXVALUE 999999 最大序列号999999
NOCACHE NOCYCLE;

索引：
创建：CREATE INDEX 索引名 ON employees(last_name)
同义词：
创建：CREATE SYNONYM Table1 for B.Table1 (数据库用户B)
CREATE SYNONYM Table1 for table1@db-link-name

第十三单元 集合操作

并集：UNION–去除重复项 UNION ALL–所有数据的集合
SELECT empolyee_id,job_id
FROM employees
UNION
SELECT empolyee_id,job_id
FROM job;
交集： INTERSECT

差集： MINUS

第十三单元 控制用户权限

简单个人用户赋予权限：
GRANT create session,creat table TO HPOS;
多个用户赋予相同权限：
CREATE ROLE manager
GRANT create table,create view to manager
GRANT manager to A,B,C;
对象权限：
GRANT update (name,id)
ON departments
TO scott,manager;
给与赋予别人的权力：
GRANT update (name,id)
ON departments
TO scott,manager
WITH GRANT OPTION;
所有人有权限：
TO PUBLIC;
收回权限：
REVOKE select
ON departments
FROM scott;
数据库连接： 创建DBLINK访问另一数据库中的表
CREATE PUBLIC DATABASE LINK hq.acme.com
USING ‘sales’; //B数据库中的用户

     SELECT * FROM emp@HQ.ACME.COM;

第十四单元 Group By 子句的增强

Rollup – 常规分组汇总行以及分组小计
GROUP BY ROLLUP()

CUBE – 交叉式汇总
GROUPING – 是否进行了汇总，是返回1，否返回0
GROUPING SETS --代替多次union

第十五单元 子查询进阶

Exists – 是否存在 和in使用方法类似

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
 FROM employees
 WHERE manager_id =
 outer.employee_id);