其它数据库对象

 

范例：创建一张基本工资大于2000的雇员信息的视图

CREATE VIEW v_myview

AS

SELECT * FROM emp WHERE sal>2000 ;

范例：查看视图是否已经创建

SELECT * FROM tab WHERE tabtype='VIEW' ;

 

范例：查询v_myview视图

SELECT * FROM v_myview ;

 

范例：查询user_views数据字典

SELECT view_name,text_length,text FROM user_views ;

 

范例：创建一张只包含20部门雇员信息的视图

CREATE VIEW v_emp20

AS

SELECT * FROM emp WHERE deptno=20 ;

 

范例：查询user_views数据字典，观察视图是否已经成功创建

SELECT view_name,text_length,text FROM user_views ;

 

范例：查询v_emp20的视图

SELECT * FROM v_emp20 ;

 

范例：替换v_myview视图 —— 定义新视图，可以显示每个部门的详细信息。

CREATE OR REPLACE VIEW v_myview

AS

SELECT d.deptno,d.dname,d.loc,

COUNT(e.empno) count,NVL(ROUND(AVG(sal),2),0) avg,NVL(SUM(sal),0) sum,

        NVL(MAX(sal),0) max,NVL(MIN(sal),0) min

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname,d.loc ;

范例：查询v_myview视图

SELECT * FROM v_myview ;

 

范例：为视图中查询的列起别名

CREATE OR REPLACE VIEW v_myview

(部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资)

AS

SELECT d.deptno,d.dname,d.loc,

COUNT(e.empno) count,NVL(ROUND(AVG(sal),2),0) avg,NVL(SUM(sal),0) sum,

        NVL(MAX(sal),0) max,NVL(MIN(sal),0) min

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname,d.loc ;

 

范例：查询v_myview视图

SELECT * FROM v_myview ;

 

范例：定义只包含20部门雇员信息的视图

CREATE OR REPLACE VIEW v_emp20

AS

SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=20 ;

 

范例：查询v_emp20数据

SELECT * FROM v_emp20 ;

 

范例：向v_emp20视图之中增加一条新数据

INSERT INTO v_emp20(empno,ename,job,sal,deptno) VALUES (6688,'魔乐','CLERK',1900,20) ;

COMMIT ;

范例：查询v_emp20视图当前数据

SELECT * FROM v_emp20 ;

 

范例：查询emp表中所有20部门雇员的详细信息

SELECT * FROM emp WHERE deptno=20 ;

 

范例：对视图执行修改操作

UPDATE v_emp20 SET ename='MLDNJAVA',job='MANAGER',sal=2300 WHERE empno=6688 ;

COMMIT ;

范例：查询v_emp20视图

SELECT * FROM v_emp20 ;

 

范例：查询emp表中所有20部门雇员的详细信息

SELECT * FROM emp WHERE deptno=20 ;

 

范例：删除v_emp20视图中的数据

DELETE FROM v_emp20 WHERE empno=6688 ;

COMMIT ;

范例：查询v_emp20视图

SELECT * FROM v_emp20 ;

 

范例：查询emp表记录

SELECT * FROM emp WHERE deptno=20 ;

 

范例：创建一个视图，要求显示所有20部门中的雇员编号、姓名、职位、基本工资、部门编号、部门名称、位置

CREATE OR REPLACE VIEW v_myview AS

SELECT e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno AND d.deptno=20 ;

 

范例：v_myview视图

SELECT * FROM myview ;

 

范例：向v_myview视图之中增加一条数据

INSERT INTO v_myview (empno,ename,job,sal,deptno,dname,loc) VALUES (6688,'魔乐','CLERK',2000,50,'教学','北京') ;

范例：修改v_myview视图中的数据

UPDATE v_myview SET ename='史密斯',sal=5000,dname='教学' WHERE empno=7369 ;

范例：删除v_myview视图中的数据

DELETE FROM v_myview WHERE empno=7369 ;

范例：删除视图之中所有20部门雇员的信息

DELETE FROM v_myview WHERE deptno=20 ;

范例：查询emp表记录中所有20部门雇员信息

SELECT * FROM emp WHERE deptno=20 ;

 

范例：查询dept表记录

SELECT * FROM dept ;

 

范例：创建一张只包含20部门雇员信息的视图 —— v_emp20

CREATE OR REPLACE VIEW v_emp20

AS

SELECT * FROM emp WHERE deptno=20 ;

 

范例：查询user_views数据字典

SELECT view_name,text_length,text FROM user_views ;

 

范例：查询v_emp20视图

SELECT * FROM v_emp20 ;

 

范例：修改v_emp20视图中的部门编号

UPDATE v_emp20 SET deptno=40 WHERE empno=7369 ;

范例：查询v_emp20视图的数据

SELECT * FROM v_emp20 ;

 

范例：查询emp表中的雇员编号为7369的雇员信息

SELECT * FROM emp WHERE empno=7369 ;

 

范例：替换v_emp20视图，加入WITH CHECK OPTION子句

CREATE OR REPLACE VIEW v_emp20

AS

SELECT * FROM emp WHERE deptno=20

WITH CHECK OPTION CONSTRAINT v_emp20_CK ;

范例：更新v_emp20视图，将雇员编号为7369的部门编号修改为40

UPDATE v_emp20 SET deptno=40 WHERE empno=7369 ;

 

范例：更新v_emp20视图，将雇员编号是7369的雇员姓名（ename）修改为“史密斯”，奖金（comm）修改为300。

UPDATE v_emp20 SET ename='史密斯',comm=300 WHERE empno=7369 ;

范例：查询v_emp20视图中的数据

SELECT * FROM v_emp20 ;

 

范例：创建视图，使用WITH READ ONLY子句进行限制

CREATE OR REPLACE VIEW v_emp20

AS

SELECT * FROM emp WHERE deptno=20

WITH READ ONLY ;

 

范例：查询user_views数据字典

SELECT view_name,text_length,text,read_only FROM user_views ;

 

范例：更新视图信息

UPDATE v_emp20 SET ename='SMITH',comm=null WHERE empno=7369 ;

 

范例：删除v_myview视图

DROP VIEW v_myview ;

 

范例：查询user_views数据字典

SELECT view_name,text_length,text,read_only FROM user_views ;

 

范例：创建一个myseq的默认序列

CREATE SEQUENCE myseq ;

范例：查询数据字典 —— user_sequences

SELECT * FROM user_sequences ;

 

范例：通过nextval属性操作序列

SELECT myseq.nextval FROM dual ;

 

范例：通过currval属性操作序列

SELECT myseq.currval FROM dual ;

 

范例：member表的数据库创建脚本

DROP TABLE member PURGE ;

CREATE TABLE member (

  mid   NUMBER        ,

  name  VARCHAR2(50)  NOT NULL ,

  CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

 

范例：通过数据字典查看member表是否已经成功创建

SELECT * FROM tab ;

 

范例：编写数据插入语句，向member表中增加记录

INSERT INTO member (mid,name) VALUES (myseq.nextval,'魔乐科技软件学院（MLDN）') ;

范例：检索全部member表数据

SELECT * FROM member ;

 

范例：删除myseq序列

DROP SEQUENCE myseq ;

范例：查询全部的序列

SELECT * FROM user_sequences ;

 

范例：创建一个新的序列，让其每次的增长步长3

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq INCREMENT BY 3 ;

 

范例：通过user_sequences数据字典查询序列是否已经成功创建

SELECT sequence_name,increment_by FROM user_sequences ;

 

范例：调用nextval属性，操作序列（本语句将执行三次）

SELECT myseq.nextval FROM dual ;

 

范例：创建序列，让其初始值设置为30，每次增长步长为2

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq

INCREMENT BY 3 

START WITH 30 ;

范例：操作myseq序列，调用三次nextval属性观察结果

SELECT myseq.nextval FROM dual ;

 

范例：创建序列，缓存设置为100

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq CACHE 100 ;

 

范例：通过user_sequences数据字典表，查询序列信息

SELECT cache_size,last_number FROM user_sequences ;

 

范例：创建序列，不使用缓存

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq NOCACHE ;

 

范例：通过user_sequences数据字典表，查询序列信息

SELECT cache_size,last_number FROM user_sequences ;

 

范例：创建循环序列，让序列的内容在1、3、5、7、9之间循环

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq

START WITH 1

INCREMENT BY 2

MAXVALUE 10

MINVALUE 1

CYCLE

CACHE 3 ;

范例：通过user_sequeces数据字典查询序列信息

SELECT sequence_name,max_value,min_value,increment_by,cache_size FROM user_sequences ;

 

范例：创建一个基本序列

DROP SEQUENCE myseq ;

CREATE SEQUENCE myseq ;

 

范例：通过user_sequences数据字典查看所有序列

SELECT * FROM user_sequences ;

 

范例：修改myseq序列

ALTER SEQUENCE myseq

INCREMENT BY 10

MAXVALUE 98765

CACHE 100 ;

 

范例：通过user_sequences数据字典查看所有序列

SELECT * FROM user_sequences ;

 

范例：创建带有自动增长列的数据表

DROP TABLE mytab PURGE ;

CREATE TABLE mytab (

mid NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) ,

name VARCHAR2(20) NOT NULL ,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

范例：察看数据字典

SELECT sequence_name,min_value,max_value,increment_by FROM user_sequences ;

 

范例：增加表数据

INSERT INTO mytab(name) VALUES ('魔乐科技') ;

INSERT INTO mytab(name) VALUES ('MLDN') ;

INSERT INTO mytab(name) VALUES ('李兴华') ;

COMMIT ;

 

范例：查询mytab表数据，观察自动增长列

SELECT * FROM mytab ;

范例：清空回收站，此时自动序列删除

PURGE recyclebin ;

范例：使用sys登陆，并查询是否存在dual表

SELECT * FROM tab WHERE TNAME='DUAL' ;

 

范例：现在为c##scott.emp表创建一个同义词为myemp

CONN sys/change_on_install AS SYSDBA ;

CREATE SYNONYM myemp FOR c##scott.emp ;

范例：查询myemp同义词是否成功创建

SELECT * FROM user_synonyms WHERE synonym_name='MYEMP' ;

 

范例：查询同义词 —— myemp

SELECT * FROM myemp ;

 

范例：使用system用户登录，并查询myemp这个同义词

CONN system/manager;

SELECT * FROM myemp ;

 

范例：删除myemp同义词

DROP SYNONYM myemp ;

 

范例：验证myemp同义词是否被删除

SELECT * FROM user_synonyms WHERE synonym_name='MYEMP' ;

 

范例：创建公共同义词 —— myemp

CREATE PUBLIC SYNONYM myemp FOR c##scott.emp ;

 

范例：观察ROWID的存在

SELECT ROWID,deptno,dname,loc FROM dept ;

 

范例：拆分ROWID，取数据

SELECT ROWID ,

DBMS_ROWID.rowid_object(ROWID) 数据对象号 ,

DBMS_ROWID.rowid_relative_fno(ROWID) 相对文件号 ,

DBMS_ROWID.rowid_block_number(ROWID) 数据块号 ,

DBMS_ROWID.rowid_row_number(ROWID) 数据行号,

deptno,dname,loc

FROM dept ;

 

范例：将dept表中的数据复制到mydept表中

DROP TABLE mydept PURGE ;

CREATE TABLE mydept AS SELECT * FROM dept ;

范例：向mydept表中增加一些数据

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (10,'ACCOUNTING','NEW YORK') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

INSERT INTO mydept(deptno,dname,loc) VALUES (20,'RESEARCH','DALLAS') ;

COMMIT ;

范例：查询mydept表

SELECT ROWID,deptno,dname,loc FROM mydept ;

范例：对mydept表分组，统计出唯一的ROWID数据

SELECT deptno,dname,loc,MIN(ROWID)

FROM mydept

GROUP BY deptno,dname,loc ;

 

范例：编写删除语句删除重复数据

DELETE FROM mydept

WHERE ROWID NOT IN(

SELECT MIN(ROWID)

FROM mydept

GROUP BY deptno) ;

范例：查询雇员编号、姓名、职位、基本工资、雇佣日期等信息并且显示每条记录的行号

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp ;

 

范例：查询30部门的雇员编号、姓名、职位、基本工资、雇佣日期等信息并且显示每条记录的行号

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE deptno=30 ;

 

范例：列出薪金高于公司平均薪金的所有员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，公司的工资等级，但是为了信息浏览方便，要求在每一行数据显示前都增加一个行号。

SELECT AVG(sal) FROM emp ;

SELECT e.empno,e.ename,e.sal,e.job,e.hiredate

FROM emp e

WHERE e.sal> (

    SELECT AVG(sal) FROM emp) ;

SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc

FROM emp e,dept d

WHERE e.sal> (

    SELECT AVG(sal) FROM emp)

    AND e.deptno=d.deptno ;

第四步：根据雇员的工资与salgrade表联合查询，显示出雇员的工资等级

SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade

FROM emp e,dept d,salgrade s

WHERE e.sal> (

    SELECT AVG(sal) FROM emp)

    AND e.deptno=d.deptno

    AND e.sal BETWEEN s.losal AND s.hisal ;

SELECT ROWNUM rn,e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,s.grade

FROM emp e,dept d,salgrade s

WHERE e.sal> (

    SELECT AVG(sal) FROM emp)

    AND e.deptno=d.deptno

    AND e.sal BETWEEN s.losal AND s.hisal ;

 

范例：查询emp表中的第一行雇员信息

SELECT * FROM emp WHERE ROWNUM=1 ;

 

范例：显示雇员表中前5条记录

SELECT * FROM (

SELECT empno,ename,job,hiredate,sal,mgr,deptno,ROWNUM rn

FROM emp WHERE ROWNUM<=5) temp

WHERE temp.rn>0 ;

 

范例：显示雇员表中的6~10条记录

SELECT * FROM (

SELECT empno,ename,job,hiredate,sal,mgr,deptno,ROWNUM rn

FROM emp WHERE ROWNUM<=10) temp

WHERE temp.rn>5 ;

 

范例：取得emp表中的前5行纪录

SELECT * FROM emp FETCH FIRST 5 ROW ONLY;

 

范例：为数据排序，取得前5行纪录

SELECT *

FROM emp

ORDER BY sal DESC

FETCH FIRST 5 ROW ONLY;

 

范例：取得表中4~5条纪录

SELECT *

FROM emp

ORDER BY sal DESC

OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY ;

 

范例：按百分比取部分数据

SELECT *

FROM emp

ORDER BY sal DESC

FETCH NEXT 10 PERCENT ROWS ONLY ;

 

范例：查询工资大于1500的全部雇员

SELECT * FROM emp WHERE sal>1500 ;

范例：使用自动跟踪功能

CONN sys/change_on_install AS SYSDBA ;

SET AUTOTRACE ON ;

范例：根据ROWID查找雇员信息

SELECT * FROM c##scott.emp WHERE rowid='AAAWeeAAGAAAADGAAA' ;

范例：在emp.sal字段上创建emp_sal_ind索引

CREATE INDEX emp_sal_ind ON emp(sal) ;

范例：通过“user_indexes”数据字典查看索引

SELECT index_name , index_type , table_owner , table_name , uniqueness , status FROM user_indexes ;

 

 

范例：查询emp_sal_ind索引设置列

SELECT * FROM user_ind_columns WHERE index_name='EMP_SAL_IND' ;

 

范例：在hiredate字段上设置降序索引

CREATE INDEX emp_hiredate_ind_desc ON emp(hiredate) ;

范例：查询在1981年雇佣的雇员信息

SELECT *

FROM c##scott.emp

WHERE hiredate BETWEEN TO_DATE('1981-01-01','yyyy-mm-dd') AND TO_DATE('1981-12-31','yyyy-mm-dd')

ORDER BY hiredate DESC ;

范例：创建函数索引

CREATE INDEX emp_ename_ind ON emp(LOWER(ename)) ;

范例：执行雇员姓名查询

SELECT * FROM c##scott.emp WHERE LOWER(ename)='smith' ;

范例：在deptno字段上设置位图索引

CREATE BITMAP INDEX emp_deptno_ind ON emp(deptno) ;

范例：根据部门编号查找雇员信息

SELECT * FROM c##scott.emp WHERE deptno=10 ;

SELECT * FROM c##scott.emp WHERE deptno=10 AND deptno=20 ;

范例：通过“user_indexes”数据字典查看索引

SELECT index_name , index_type , table_owner , table_name , uniqueness , status FROM user_indexes ;

 

范例：删除emp_sal_ind索引

DROP INDEX emp_sal_ind ;