plsql查询和数据对象

626

---多表查询
--Oracle定义的链接类型
1、等于链接
2、不等链接
3、外连接（可细分为左外连接、右外连接）
  没有加号的一方是主表
4、自链接
--工业标准定义（SQL 1999)的链接类型
1、交叉连接（笛卡尔乘积）
2、自然链接（相当于Oracle的“等于连接”）
3、Using 子句
4、内连接
5、外连接（全外连接、左外连接、右外连接）

---分组计算函数和group by
--分组计算函数
1、求和(SUM)
2、求平均值(AVG)
3、计数（COUNT）
----------------------------------------------------------------
函数用法                                  意义
COUNT(*)                           返回满足选择条件的所有行的行数，包括值为空的行和重复的行
COUNT(expr)                        返回满足选择条件的且表达式不为空行数。
COUNT(DISTINCT expr)               返回满足选择条件的且表达式不为空，且不重复的行数。
-----------------------------------------------------------------
4、求标准差(STDDEV)
5、求方差(VARIANCE)
6、求最大值(MAX)
7、求最小值(MIN)
--使用GROUP BY 子句进行分组
▲SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子
句中，否则不合法。
▲不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。
▲分组计算函数也可嵌套使用


--子查询
①单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
②多行比较必须对应多行子查询（返回一个数据集合的查询）；比如IN , > ANY, > ALL 等
▲any 任意 小于最大，大于最小
▲all 所有 小于最小，大于最大


---DML数据操纵语言
--insert
INSERT INTOtable [(column [, column...])] VALUES (value [, value...]);
从另一个表中copy一行
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

--update
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
▲当存在约束的时候，某些更新可能会失败

--delete
DELETE [FROM] table [WHERE condition];
▲TRUNCATE TABLE copy_emp 截断数据，无法回滚
▲当存在约束的时候，某些删除可能会失败

--merge 比较整合语句 用来合并UPDATE和INSERT语句
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

---事务控制（控制数据一致性）
--当如下事件发生是，会隐式的执行Commit动作：
    1、数据定义语句被执行的时候，比如新建一张表：Create Table …
    2、数据控制语句被执行的时候，比如赋权GRANT …( 或者DENY)
    3、正常退出iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行COMMIT 或者ROLLBACK 语句。
--当如下事件发生时，会隐式执行Rollback 动作：
    1、非正常退出iSQL*Plus , PLSQL DEVELOPER, 或者发生系统错误。
--在Commit 或者Rollback前后数据的状态：
    1、在数据已经被更改，但没有Commit前，被更改记录处于被锁定状态，其他用户无法进行更改；
    2、在数据已经被更改，但没有Commit前，只有当前Session的用户可以看到这种变更，其他Session的用户
       看不到数据的变化。
    3、在数据已经被更改，并且被Commit后，被更改记录自动解锁，其他用户可以进行更改；
    4、在数据已经被更改，并且被Commit后，其他Session的用户再次访问这些数据时，看到的是变化后的数据。


---锁
  防止并发事务对相同的资源（所谓资源是指表、行、共享的数据结构、数据字典行等）进行更改的时候，相互破坏。


---表
表名：1、必须以字母开头
      2、长度不能超过30个字符
      3、只能包含A–Z, a–z, 0–9, _, $, and #
      4、不能与数据库中的已有对象重名
      5、不能使用Oracle 数据库的保留字
-----------------------------------------------
数据类型                             描述
VARCHAR2(size)                    可变长字符串
CHAR(size)                        定长字符串
NUMBER(p,s)                       可变长数值
DATE                              日期时间
LONG                              可变长大字符串，最大可到2G
CLOB                              可变长大字符串数据，最大可到4G
RAW and LONG RAW                  二进制数据
BLOB                              大二进制数据，最大可到4G
BFILE                             存储于外部文件的二进制数据，最大可到4G
ROWID                             64进制18位长度的数据，用以标识行的地址
TIMESTAMP                         精确到分秒级的日期类型（9i以后提供的增强数据类型）
----------------------------------------------------------------------------------------


---数据库对象-约束

--NOT NULL （非空约束）
--UNIQUE （唯一性约束）
   建表时：CONSTRAINT emp_email_uk UNIQUE(email)
--PRIMARY KEY （主键约束）
   建表时：CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
--FOREIGN KEY （外键约束）
   建表时：CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
  外键约束类型：
     ? REFERENCES: 表示列中的值必须在父表中存在
     ? ON DELETE CASCADE: 当父表记录删除的时候自动删除子表中的相应记录.
     ? ON DELETE SET NULL: 当父表记录删除的时候自动把子表中相应记录的值设为NULL
--CHECK （自定义约束）

--单独创建约束
ALTER TABLE tablename ADD CONSTRAINT constraintname constrainttype (column1,…);

--删除约束
1、ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
2、ALTER TABLE departments DROP PRIMARY KEY CASCADE;

--失效/生效约束
ALTER TABLE employees DISABLE（ENABLE） CONSTRAINT emp_emp_id_pk CASCADE;

--查看系统中的约束（user_constraints）
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';


---数据库对象-视图
--创建
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

--删除
DROP VIEW empvu80;

--TOP-N 查询
SELECT [column_list], ROWNUM
FROM (SELECT [column_list]
FROM table
ORDER BY Top-N_column)
WHERE ROWNUM <= N;


---数据库对象-序列、索引、同义词
--序列
CREATE SEQUENCE sequence
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]
       [{CACHE n | NOCACHE}];
从序列取值： CURRVAL 取当前值， NEXTVAL取下一个值

--索引
作用：加快查询
适合创建索引：查询条件中使用到这个列（或者这个列于其他列的组合），
             且这个列（或者与其他列的组合）上的数字范围跨度很大
不适合创建索引：被查询的表本身就很小，即是是全表扫描也非常快； 或者基于这张表的查询，
                大多数情况下需要获取的数据量都超过了总量的4%；或者这张表需要频繁的
                被更新，建立索引的话会引起索引的频繁更新，从而反而降低数据库的整体效率。
CREATE INDEX emp_last_name_idx ON employees(last_name);

--同义词
作用：简化写法
CREATE SYNONYM Table1 for B.Table1