oracle数据库基础语言

2019独角兽企业重金招聘Python工程师标准>>>

数据定义语言（DDL）【CREATE】创建；【ALTER】修改；【DROP】删除；【TRUNCATE】；数据库操作语言（DML）【INSERT】插入；【UPDATE】更新；【DELETE】删除；数据查询语言(DQL)：【SELECT】事务控制语言(TCL)；数据控制语言（DCL）；dual：伪表；数据库中的一些子句：ORDER BY,GROUP BY,HAVING;关联查询；子查询；分页查询；视图；序列；索引；约束

-------------------------------------------------------------------------------------------------------

数据定义语言（DDL）:create,alter,drop,truncate
一.创建数据库对象的结构：【CREATE】
1.创建表：CREATE TABLE emp(
        empno NUMBER(4,0),
        ename VARCHAR2(10),
        hirdate DATE );

2.创建对象：CREATE VIEW v_emp_depton10
        AS
        SELECT empno,ename,sal,depton
        FROM emp
        WHERE depton=10

3.创建序列：数据库对象之一（通常作为主键使用）
    CREATE SEQUENCE seq_emp_id
    START WITH 1
    INCREMENT BY 1

4.创建索引：数据库对象之一（提高表的查询、排序、去重等操作的效率。）
    CREATE INDEX idx_emp_ename
    ON emp(ename)

二.修改对象的结构：【ALTER】
1.修改表的结构：
    增加列：ALTER TABLE langlangge ADD(hiredate DATE);
    删除列：ALTER TABLE langlangge DROP(hiredate);
    修改列：ALTER TABLE langlangge MODIFY(job VARCHAR(40) DEFAULT 'CLERK');

2.重建索引：
ALTER INDEX idx_emp_ename REBUILD

三.删除对象的结构：【DROP】
1.删除表的结构：
    删除指定的表：DROP TABLE emp;
    删除表的某列：ALTER TABLE langlangge DROP(hiredate);

2.删除视图：删除视图不会对视图数据来源的基础表做任何操作。
DROP VIEW v_emp_depton10

3.删除序列：
DROP SEQUENCE seq_emp_id

4.删除索引：
DROP INDEX idx_emp_ename

四.删除表数据，保留表结构：【TRUNCATE】
**TRUNCATE没有条件子句，会将表的数据全部删除，且无法回滚，但效率高。
当需要大批量的删除表的全部数据时可以使用。
    TRUNCATE TABLE emp
-------------------------------------------------------------------------------
数据库操作语言（DML）:insert,update,delete

一.将数据插入到表中：【INSERT】
1.向表中插入数据：
INSERT INTO emp
(empno,ename,job,mgr,hirdate,sal,comm,depton)
VALUES
(7369,'SMITN','CLERK',7902,TO_DATE('1980/12/17','YYYY/MM/DD'),800.00,NULL,20)

2.更新表中已经存在的数据：【UPDATE】
UPDATE langlangge
SET salary=8000,job='MANAGER'
WHERE name='Jack'

3.删除表中的数据：【DELETE】
DELETE FROM depts
WHERE deptno = 10

---------------------------------------------------------------------------------
数据查询语言(DQL)：【SELECT】
**SELECT 与FROM之间指定的字段可以包括：
    1：表中的字段  --  SELECT ename FROM emp
    2：一个表达式  --  SELECT ename||','||sal FROM emp
    3：一个函数    --  SELECT AVG(sal)  FROM emp

1.查询表：略
2.查询视图：SELECT * FROM v_emp_salary
3.也可以查询序列：
SELECT seq_emp_id.CURRVAL
FROM dual

SELECT seq_emp_id.NEXTVAL
FROM dual
--------------------------------------------------------------------------------
事务控制语言(TCL):commit,rollback,savepoint
1.提交，确定已经进行过的数据改变：【COMMIT】
2.回滚，取消已经进行过得数据改变：【ROLLBACK】
3.保存点，当前的事务可以回滚到指定的保存点，便于取消部分改变。
**（只可以回滚到没提交的保存点。）【SAVEPOINT】
---------------------------------------------------------------------------------
数据控制语言（DCL）：grant，revork，create user
1.授予，用于给用户或角色授予权限：【GRANT】
2.用于收回用户或角色已有权限：【REVORK】
3.创建用户：【CREATE USER】
---------------------------------------------------------------------------------
dual：伪表，当查询的数据与任何表没有关系时，可以使用伪表.
修改表名：RENAME langgege TO langlangge;
查询表的结构：DESC emp
----------------------------------------------------------------------------------------
数据库中的一些子句：
1.ORDER BY子句，会根据其后指定字段的值进行升序或者降序排列后显示。
升序（ASC）:ASC可以不写，默认升序
降序（DESC）:从大到小。
ORDER 也可以对多列进行排序，排序优先级从左到右。排序字段中若有NULL值，NULL被当作为最大值。
**ORDER BY子句必须写在SELECT语句的最后一个子句上。
SELECT ename,sal FROM emp ORDER BY sal
SELECT ename,sal FROM emp ORDER BY sal DESC

2.GROUP BY：是配合聚合函数进行分组统计结果使用的。
GROUP BY会根据后面给定的字段值相同的记录进行分组，然后配合聚合函数再根据每个组再统计结果。
当SELECT子句中有聚合函数时，凡不在聚合函数中的其他字段，都必须出现再GROUP BY子句中，反过来不

是必须的。
GROUP BY 可以根据多个字段分组，分组的根据是这些记录的指定字段值的组合相同。

--SELECT AVG(sal),depton FROM emp WHERE AVG(sal)>2000 GROUP BY depton
SELECT AVG(sal),depton FROM emp GROUP BY depton HAVING AVG(sal)>2000

3.WHERE的过滤时机是在第一次检索表中数据时进行过滤的，WHERE决定从表中查询多少数据，
4.HAVING是在分组统计后再进行过滤的，而分组是在查询出的数据基础上进行的，
所以HAVING过滤是在WHERE之后进行的，HAVING决定取舍哪些分组。
-------------------------------------------------------------------------------------------
关联查询：将多张表数据联系起来，进行相关的查询工作。
**关联查询必须定义连接条件，否则会出现'笛卡尔积'，N张表关联查询，至少要定义N-1个连接条件。
一.普通的关联查询：
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.depton=d.deptno

二.内联接：表1 JOIN 表2 ON 条件
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.depton=d.deptno WHERE d.dname='SALES'

三.外连接：普通的关联查询中不会将不满足连接条件的记录查询出来，当需要将不满足连接条件的记录

查询出来时，要使用外连接。外连接分为：左外连接，右外连接和全外连接。

1.左外联接：左外连接的结果集包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的

行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
SELECT e.ename,d.dname FROM emp e LEFT OUTER JOIN dept d ON e.depton=d.deptno--左外连接
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.depton=d.deptno(+)--左外连接

2.右外连接：右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有

匹配行，则将为左表返回空值。   
SELECT e.ename,d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.depton=d.deptno--右外连接
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.depton(+)=d.deptno--右外连接

3.全外连接：完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表

的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
SELECT e.ename,d.dname FROM emp e FULL OUTER JOIN dept d ON e.depton=d.deptno--全外连接

4.自连接：自己表中的一条数据对应自己表中的多条数据，然后进行关联查询。自连接用来解决相同数据

但是有存在父子关系的树状结构时使用。
查看每个员工及其领导的名字：
SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno(+)
-------------------------------------------------------------------------------------------
子查询：嵌套在其他SQL语句中的一条查询语句。

**当我们执行的SQL语句需要先从数据库中获取一些数据才能运行时，那么先期执行的
这条SQL就是子查询，子查询是为了给实际要执行的SQL提供数据的。

**子查询常用于查询语句，但是也可以在DDL（数据定义语言），DML（数据操作语言）中使用。
DDL中使用：基于一个查询结果集，快速创建一张表。

**子查询根据查询结果分为：单行单列子查询，多行单列子查询，多行多列子查询
其中单列的子查询多用于WHERE的子句中，而多行多列的子查询通常用于FROM中当作表看待。

**子查询也可以应用在SELECT子句中，可以实现外连接的效果。
查看每个员工及其所在部门名称的信息？
SELECT e.ename,
(SELECT d.dname FROM dept d WHERE e.depton=d.deptno) dname
FROM emp e
--------------------------------------------------------------------------------------
分页查询：当查询的数据量过度时，会导致一些情况：
1：服务端响应慢
2：系统占用资源过多
3：数据过剩
为了解决这些问题，通常我们会分批查询。
由于分页查询没有标准的SQL语法，所以不同的数据库对于分页的机制不一致（方言）

查看工资排名6—10名的员工信息？
SELECT *
FROM(SELECT ROWNUM RN,S.*
    FROM(SELECT ename,sal
        FROM emp
        ORDER BY sal) s
        WHERE ROWNUM<=10   --此处可以提高效率，不需要的数据就不用编号了，数字十可用end代替
        )
WHERE RN >= 6  --数字六可用start代替

页数：page
每页显示的条数：pageSize
start=(page-1)*pageSize+1
end=pageSize*page
------------------------------------------------------------------------------------------
视图：数据库对象之一，视图在SQL语句中体现的角色和表相同，但视图不是真正的表，而是一个查询语

句对应的结果集。
CREATE VIEW v_emp_depton10
AS
SELECT empno,ename,sal,depton
FROM emp
WHERE depton=10

1.当视图对应的子查询的字段含有函数或表达式时，必须对该字段定义别名。
定义一个可以查看每个部门薪资情况的视图：
CREATE OR REPLACE VIEW v_emp_salary      --创建或替换
AS
SELECT AVG(e.sal) avg_sal,
       SUM(e.sal) sum_sal,
       MAX(e.sal) max_sal,
       MIN(e.sal) min_sal,
       d.dname,d.deptno
FROM emp e,dept d
WHERE e.depton=d.deptno
GROUP BY d.dname,d.deptno

2.复杂视图：
视图对应的SELECT语句中含有函数，表达式，分组等操作时该视图就是复杂视图。
对于多表关联查询的视图，叫做连接视图，也算是复杂视图的一种。复杂视图不能进行DML操作。

3.对视图进行DML操作的规则：
对简单视图可以进行DML操作，但是对复杂视图不可以。
对视图进行DNL操作就是对视图数据来源的基础表进行DML。

4.也可以为视图添加只读选项，添加后该视图不允许执行DML操作。
CREATE OR REPLACE VIEW v_emp_depton10
AS
SELECT empno id,ename name,sal salary,depton
FROM emp
WHERE depton=10
WITH READ ONLY

5.视图的污染问题：
**当插入一条视图看不到的数据时，会对基础表造成‘污染’
**更新数据同样有可能对基础表造成污染，更新后数据对于视图而言不可控。
**删除数据不会对基础表造成污染。

**对视图增加检查选项，可以避免对视图操作后视图对数据不可控的情况。
当添加检查选项后，对视图进行：
INSERT:插入的数据必须视图可见
UPDATE:更新后数据必须对视图可见

CREATE OR REPLACE VIEW v_emp_depton10
AS
SELECT empno id,ename name,sal salary,depton
FROM emp
WHERE depton=10
WITH CHECK OPTION
------------------------------------------------------------------------------------------
序列：数据库对象之一
序列可以按照指定的规则生成一系列的数字。序列生成的数字通常是为了某张表的主键提供值的。
CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

序列支持两个伪列：
NEXTVAL:让序列生成一个数字，是用序列最后生成的数字加上步长的到的，
新创建的序列调用时返回START WITH指定的值。
CURRVAL:获取序列当前数字，（即最后一次通过NEXTVAL生成的数字）。
新创建的序列必须先至少执行一次NEXTVAL以后猜可以使用CURRVAL。
NEXTVAL会导致序列增长，而且增长后不能获得原来生成过的值。

INSERT INTO emp
(empno,ename,job,sal,depton)
VALUES
(seq_emp_id.NEXTVAL,'ROSE','CLERK',6000,30)

SELECT seq_emp_id.CURRVAL
FROM dual

SELECT seq_emp_id.NEXTVAL
FROM dual

删除序列：
DROP SEQUENCE seq_emp_id
------------------------------------------------------------------------------------------
索引：数据库对象之一
为某张表的某些字段添加索引，可以提高表的查询，排序，去重等操作的效率。
序列的算法和维护是数据库自行维护的，我们不能干涉，我们只需要高数数据库是否创建索引即可。
当执行某些SQL时数据库会自行使用可用索引来提高执行效率。

CREATE INDEX idx_emp_ename
ON emp(ename)

如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上简历一个基于UPPER函数的索引：
CREATE INDEX emp_ename_upper_idx
ON emp(UPPER(ename))

重建索引：
ALTER INDEX idx_emp_ename REBUILD

删除索引：
DROP INDEX idx_emp_ename
----------------------------------------------------------------------------------------
约束：
1.非空约束：
CREATE TABLE employees(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hirdate DATE
  CONSTRAINT employees_hirdate_nn NOT NULL
)

可以在建表之后，通过修改表的定义添加非空约束：
ALTER TABLE employees
MODIFY (eid NUMBER(6) NOT NULL)

取消非空约束：
如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：
ALTER TABLE employees
MODIFY (eid NUMBER(6) NULL)

2.唯一性约束：
CREATE TABLE employees1(
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
  CONSTRAINT employees1_email_uk UNIQUE(email)
)

在建表之后增加唯一性约束条件：
ALTER TABLE employees1
ADD CONSTRAINT employees_name_uk UNIQUE(name)

3.主键约束：(联合了唯一性和非空)
在建表时添加主键约束条件：(联合了唯一性和非空)
CREATE TABLE employees2(
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hirdate DATE
)

建表后创建主键约束条件，并且自定义约束条件名称：
ALTER TABLE employees3
ADD CONSTRAINT employees3_eid_pk PRIMARY KEY(eid)

4.检查约束：
员工薪水必须大于5000的约束：
ALTER TABLE employees2
ADD CONSTRAINT employees2_salary_check CHECK(salary>5000)

转载于:https://my.oschina.net/langgege/blog/667262