作者：gqk：
DDL：数据库定义语句：
作用于创建，修改，删除，管理数据库对象
会自动提交当前的事务
常见的数据库对象：
--创建学生表
CREATE TABLE student
(
stu_id NUMBER(6),
stu_name VARCHAR2(50),
stu_sex CHAR(2),
stu_hiredate DATE
);--删除学生表
DROP TABLE student;
--创建学生表(带列默认值)
CREATE TABLE student
(
stu_id NUMBER(6),
stu_name VARCHAR2(50),
stu_sex CHAR(2) DEFAULT '男',
stu_hiredate DATE DEFAULT SYSDATE
);
--利用子查询创建表
CREATE TABLE new_emp2
AS
SELECT employee_id,last_name
FROM employees;
ALTER TABLE student
ADD (phone VARCHAR2(50),address VARCHAR2(100));
--修改列
ALTER TABLE student
MODIFY (address VARCHAR2(200));
--删除列
ALTER TABLE student
DROP (phone);
--重命名对象
RENAME student TO stu;
--清空表
TRUNCATE TABLE stu;
/*
delete truncate
事务 可回退 自动提交，不可回退
记录日志 记录 不记录
释放空间 不释放空间 释放空间
*/
--列注释
COMMENT ON COLUMN stu.stu_id IS '学生编号';
COMMENT ON COLUMN stu.stu_name IS '学生姓名';
/*
五种约束：
not null 非空约束
primary key 主键约束
unique 唯一值约束
check 检查约束
foreign key 外键约束
*/
--约束可以建表的同时创建，也可以在建表之后追加创建
--创建约束实验表
CREATE TABLE newdept
AS
SELECT * FROM departments;
CREATE TABLE newemp
AS
SELECT * FROM employees;
--非空约束：限制列值不允许为空
--修改first_name列为非空
ALTER TABLE newemp
MODIFY (first_name NOT NULL);
--取消非空约束
ALTER TABLE newemp
MODIFY (first_name NULL);
--增加约束
ALTER TABLE 表名
ADD CONSTRAINTS 自定义约束名称 约束设置;
--主键约束：限制列值不允许重复，且不能为空，一个表只能由一个主键
--设置主键约束
ALTER TABLE newemp
ADD CONSTRAINTS newemp_empid_pk PRIMARY KEY (employee_id);
--删除约束
ALTER TABLE newemp
DROP CONSTRAINTS newemp_empid_pk;
--复合主键（多列组成一个主键）
ALTER TABLE newemp
ADD CONSTRAINTS newemp_ename_pk PRIMARY KEY (first_name,last_name);
--唯一值约束：限制列值不允许重复，不限制空值，一个表中可以有多个唯一值约束
--设置唯一值约束
ALTER TABLE newemp
ADD CONSTRAINTS newemp_email_uk UNIQUE (email);
--检查约束：限制自定义条件，忽略空值
--限制工资不能少于400
ALTER TABLE newemp
ADD CONSTRAINTS newemp_sal_chk CHECK (salary>=400);
--外键约束：限制外键列值必须是引用的主表中的主键列存在的列值，不限制空值
--前置条件：引用的主表中的主键列必须已经设置主键约束
--部门主键约束设置
ALTER TABLE newdept
ADD CONSTRAINTS newdept_deptid_pk PRIMARY KEY (department_id);
--员工外键约束设置
ALTER TABLE newemp
ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY (department_id)
REFERENCES newdept (department_id);
--级联删除
ALTER TABLE newemp
ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY (department_id)
REFERENCES newdept (department_id)
ON DELETE CASCADE;
--级联置空
ALTER TABLE newemp
ADD CONSTRAINTS newemp_deptid_fk FOREIGN KEY (department_id)
REFERENCES newdept (department_id)
ON DELETE SET NULL;
--创建表的同时增加约束：
CREATE TABLE emp2
(
emp_id NUMBER(6) PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
emp_email VARCHAR2(50) UNIQUE,
emp_sal NUMBER(8,2) CHECK (emp_sal>=400),
emp_deptid NUMBER(6) REFERENCES newdept (department_id)
);
CREATE TABLE emp3
(
emp_id NUMBER(6),
emp_name VARCHAR2(50) NOT NULL,
emp_email VARCHAR2(50),
emp_sal NUMBER(8,2),
emp_deptid NUMBER(6),
CONSTRAINTS emp2_empid_pk PRIMARY KEY (emp_id),
CONSTRAINTS emp2_email_uk UNIQUE (emp_email),
CONSTRAINTS emp2_sal_chk CHECK (emp_sal>=400),
CONSTRAINTS emp2_deptid_fk FOREIGN KEY (emp_deptid)
REFERENCES newdept (department_id)
);
--无效化约束（暂时使约束失效）
ALTER TABLE newemp
DISABLE CONSTRAINTS newemp_empid_pk;
--激活约束
ALTER TABLE newemp
ENABLE CONSTRAINTS newemp_empid_pk;
--视图：虚拟表，作用是保存查询结果，仅保存查询语句，而不保存真实数据（物化视图例外）
--创建视图语法（如果查询语句中有衍生列，必须起别名）
CREATE [OR REPLACE] VIEW 视图名称
AS
查询语句
--查询员工表中的50号部门的员工编号，姓名，工资
CREATE OR REPLACE VIEW v_emp
AS
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id=50;
--查询视图
SELECT * FROM v_emp;
--创建视图v_dept：查询每个部门的编号，名称，员工人数，员工工资总和
CREATE OR REPLACE VIEW v_dept
AS
SELECT d.department_id,
d.department_name,
COUNT(e.employee_id) emp_count,
SUM(e.salary) sal_sum
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id,
d.department_name;
--查询视图
SELECT * FROM v_dept WHERE emp_count>=3;
--创建只读视图
CREATE OR REPLACE VIEW v_emp
AS
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id=50
WITH READ ONLY;
--删除视图
DROP VIEW v_emp;
视图中使用DML的规定
a) 可以在简单视图中执行 DML操作
b) 当视图定义中包含以下元素之一时不能使用delete：
i. 组函数
ii. GROUP BY子句
iii. DISTINCT关键字
iv. ROWNUM 伪列 DUAL伪表
c) 当视图定义中包含以下元素之一时不能使用update：
i. 组函数
ii. GROUP BY子句
iii. DISTINCT关键字
iv. ROWNUM 伪列
v. 列的定义为表达式
d) 当视图定义中包含以下元素之一时不能使用insert:
i. 组函数
ii. GROUP BY子句
iii. DISTINCT关键字
iv. ROWNUM 伪列
v. 列的定义为表达式
vi. 中非空的列在视图定义中未包括
序列 ：
通常我们在创建一个表时，都会定义一列为主键，通常是数字，对于主键我们的要求就是非空且唯一，然而在实际插入数据的时候，我们如何才能保证这个数据是唯一的呢，一种方式就是通过java代码写个工具类，产生唯一的数字，另一种方式就是今天所要介绍的一个数据库对象–序列。
1.什么是序列？
答：序列是oracle提供的用于产生一系列唯一数字的数据库对象。
2.它有什么作用？
- 自动提供有规律且唯一的值；
- 共享对象；
- 通常用于表的主键的值；
- 将序列存入内存可以提高查询效率。
--创建序列
CREATE SEQUENCE stu_seq
START WITH 100 --起始值，默认是1
INCREMENT BY 10 --递增值，默认是1
NOMAXVALUE --最大值，默认无最大值
NOCYCLE --假如设置了最大值，到达最大值之后，是否从头开始，默认NOCYCLE
CACHE 10 --缓存数量，默认20
;
--删除序列
DROP SEQUENCE stu_seq;
--创建序列（使用默认值）
CREATE SEQUENCE stu_seq;
在使用的之前我们需要了解两个”方法”，一个是 序列名.nextval,这个可以类别我们java中的迭代器的概念，代表指向下一个序列值，而序列名.currval,则返回当前值。我们通过伪表dual，来展示一下；
SELECT stu_seq.nextval FROM dual;
SELECT stu_seq.currval FROM dual;
--在插入语句中使用序列
INSERT INTO stu VALUES (stu_seq.nextval,'tom','男',sysdate);
查询序列
查询时我们可以通过工具查看sequences文件夹，当然用命令应该更快点
select increment_by,start_with,max_value,min_value from USER_SEQUENCES;
.裂缝
在使用序列的过程中可能会出现裂缝，也就是序列不连续了，比如本来是主键值依此应该是1，2，3，4，出现裂缝的意思就是主键值是1，3，4，5。那么是什么原因导致出现裂缝的呢，主要原因如下：
1.回滚数据导致的，我们知道可以使用rollback可以对我们刚刚插入的数据进行回滚，但是我们要知道的一点就是序列不会回滚也就是这个值我们已经使用过了，尽管现在不用了，但是虚拟指针已经指向了后面的位置了，不可能回退了，所一展现给我们的也就产生了裂缝。
2系统异常：也就是我们插入数据时候系统异常了，数据并没有插入进去， 但是序列值已经使用了，所以下次在此调用序列时就是后面一个序列了，也产生了裂缝。
多个表使用同一个序列，因为有些值在其他表显示，所以在这个表上面看就出现了裂缝。
---------------------
数据库索引：
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。
--创建实验表
CREATE TABLE new_emp
AS
SELECT * FROM employees;
--创建唯一性索引：
ALTER TABLE new_emp
ADD CONSTRAINTS newemp_empid_pk PRIMARY KEY (employee_id);
--创建普通索引
CREATE INDEX newemp_lname_idx ON new_emp (last_name);
--删除索引
DROP INDEX newemp_lname_idx;
--创建基于函数的索引
CREATE INDEX newemp_lname_idx ON new_emp (LOWER(last_name));
--创建位图索引
CREATE BITMAP INDEX newemp_jobid_idx ON new_emp (job_id);
--查询实验：
SELECT * FROM new_emp WHERE employee_id=174;
SELECT * FROM new_emp WHERE last_name='King';
SELECT * FROM new_emp WHERE LOWER(last_name)='king';
SELECT * FROM new_emp WHERE job_id='xxx';
--同义词：缩短对象的名称：
SELECT * FROM user_tables;
SELECT * FROM tabs;
GRANT CREATE SYNONYM TO scott;
CREATE SYNONYM e FOR employees;
SELECT * FROM e;
DROP SYNONYM e;
--创建新用户
CREATE USER icss IDENTIFIED BY icss;
--DCL授权语句
--GRANT 权限1，权限2，…… TO 用户;
--DCL收权语句
--REVOKE 权限1，权限2，……FROM 用户;
--授予创建会话的权限给ICSS
GRANT CREATE SESSION TO icss;
--回收权限
REVOKE CREATE SESSION FROM icss;
--授予开发人员权限
GRANT CONNECT,RESOURCE TO icss;
GRANT CREATE VIEW TO icss;
--修改密码
ALTER USER icss IDENTIFIED BY abcd;
--解锁用户
ALTER USER icss ACCOUNT UNLOCK;
--授予对象访问权限
GRANT SELECT ON scott.employees TO icss;
GRANT ALL ON scott.employees TO icss;
--回收对象访问权限
REVOKE ALL ON scott.employees FROM icss;
--删除用户
DROP USER icss CASCADE;
570
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
