|
ame, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) REVISED_SALARY
FROM employees;
--CASE THEN示例
CASE
WHEN THEN
WHEN THEN
ELSE
END
SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END "REVISED_SALARY"
FROM employees;
--##分组函数
AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE
/*
IN、ANY、ALL运算符
IN 等于列表中的任意一个。
< ANY 意味着低于最高值；> ANY 意味着高于最低值；= ANY 等同于 IN。
< ALL 意味着低于最低值；> ANY 意味着高于最高低值。
示例：
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
*/
--创建脚本示例
INSERT INTO departments (department_id, department_name, location_id)
VALUES (&"Department Id",'&"Department Name"',&Location_Id);
/*
MERGE语句
提供有条件地在数据表中更新或插入数据的功能。
如果该行存在就执行UPDATE,如果是新行则执行INSERT
示例：在COPY_EMP表中插入或更新行，以便与EMPLOYEES表匹配
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
...
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);
*/
--显式事务控制语句
COMMIT
ROLLBACK
SAVEPOINT name_values
ROLLBACK TO SAVEPOINT name_values
--ALTER TABLE语句示例
ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
ALTER TABLE dept80
DROP COLUMN job_id;
--SET UNUSED标记不可用与删除表全部不可用字段
ALTER TABLE emp SET UNUSED(department_id)
ALTER TABLE emp DROP UNUSED COLUMNS; --不能删除某一个不可用字段，若删除将全部删除。
--RENAME重命名对象
RENAME employees2 TO emp
--COMMENT向表中添加字段
COMMENT ON TABLE employees
IS 'Employee Information';
--约束类型
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
CREATE TABLE employees(
employee_id NUMBER(6),
email VARCHAR2(25),
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
CREATE TABLE employees(
employee_id NUMBER(6),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
CREATE TABLE employees(
employee_id NUMBER(6),
...
salary NUMBER(2)
CONSTRAINT emp_salary_min CHECK (salary > 0),...
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
ALTER TABLE departments
DROP PRIMARY KEY CASCADE; --删除departments表上的PRIMARY KEY约束，并删除employees.department_id列上关联的FOREIGN KEY约束。
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE; --禁用约束
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk; --启用约束
/*
级联约束
CASCADE CONSTRAINTS子句是和 DROP COLUMN子句一起使用的。
CASCADE CONSTRAINTS子句会删除涉及到在已删除列上定义的主键或唯一关键字的所有引用完整性约束。
CASCADE CONSTRAINTS子句还将删除在已删除列上定义的?多列约束。
CREATE TABLE test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0));
ALTER TAB |