[size=medium]
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
[/size]
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
[/size]