oracle数据库课件chapter1sqlfor10计.ppt

SQL与SQL PLUS，SQL命令分类 常用SQL PLUS命令 TTITLE ，BTITLE， COL 列名 FORMAT 格式串 HEADING TEXT 格式串(字符和日期型An,数字型 999D99) SET AUTO ON|OFF 自动提交开或关 RUN或/ 运行SQL缓冲区命令 EDIT 编辑SQL缓冲区内容 SAVE 文件名 将SQL缓冲区内容写入文件 GET 文件名 将文件名内容调入SQL缓冲区 文件名 运行并调入到SQL缓冲区 SET SERVEROUT ON|OFF 控制DBMS_OUTPUT.PUT_LINE输出显示。,CLEAR BUFFER或CLEAR SQL 清除SQL缓冲 CLEAR SCREEN 清除屏幕缓冲区 SHOW USER 显示当前用户名 SHOW ALL 显示所有系统变量定义 SPOOL 文件名 out 显示结果存到文件或打印 SPOOL AA 显示结果存到AA.LST文件(OFF) SPOOL OUT 停止假解脱机，打印 SET PAGESIZE 数字 每页显示行数 3. 常用数据类型 字符型： CHAR(N),VARCHAR2(N),LONG(n) 数字型：NUMBER(p,s) 日期型: DATE 大对象：BLOB，CLOB，BFILE,4. 数据格式 数字格式：9、，、.、$、S、EEEE 日期格式:YY、YYYY、MM、MON、DDD、DD、D、WW、W、HH12、HH24、MI、SS 5. 运算符 数值型：+、-、*、/ 字符型: |(两个|) 关系型：=、!= 、 、 、= 逻辑型: NOT 、AND 、OR 特殊型：关系运算符 SOME|ANY|ALL NOT BETWEEN m AND n m NOT LIKE n %(多), _(下划线，一个字符) m IN 列表(同=ALL) m在列表集合中返回TRUE,6. 函数、表达式 ABS(n),CEIL(n)，COS(n),EXP(n), FLOOR(N), MOD(m,n),POWER(m,n),ROUND(m,n),SIGN(n)， SQRT(n) TRUNC(n,m) Initcap(字符串) 每个单词的第一个字母大写 Lower(字符串）整个字符串转换成小写 Replace(str,str1,str2) 将str中的所有str1换成str2 Substr(str,m,n) 取从m位置开始的n个字符 Length(str) 字符串长度 Ltrim(str) 去掉尾部空格 Ascii(str)取字符的ASCII char(n) n的ASCII字符 SYSDATE和CURRENT_DATE 当前日期时间,转换函数 TO_CHAR(日期型，FMT) TO_CHAR(数字型，FMT） TO_DATE(字符型，FMT) TO_MUTI_BYTE(字符串) TO_SINGLE_BYTE(字符型） 多行函数 AVG(表达式或列名) COUNT(表达式或列名) 常与GROUP BY合用 MAX(列名或表达式) MIN(列名或表达式) C或VC，D，N SUM(列名或表达式,第1章 数据库对象管理,1.1 模式概念和模式对象 模式(Schema)是数据的逻辑结构即模式对象的集合。一个模式只能被一个数据库用户所拥有，并且模式名称与用户名称相同。ORACLE数据库中的每个用户都拥有一个唯一的模式。 ORACLE数据库中的模式对象有：表、视图、索引、簇、同义词、序列、数据库链接、存储过程、PL/SQL包、存储函数、触发器、Java类与其它Java资源。,SQLCONNECT hr/hr; SQLCONNECT user1/abc; SQLSHOW USER ; SQLALTER SESSION 2 SET CURRENT_SCHEMA=模式名 SQLSHOW USER； 不改变user1用户的系统权限。,什么是方案？ “方案”是指由特定用户拥有的数据库对象的集合。对于生产数据库而言，该用户通常代表的不是一个人，而是一个应用程序。方案的名称与拥有该方案的用户的名称相同。方案对象是指直接引用数据库数据的逻辑结构。方案对象包含表、视图和索引等多种结构。 使用 SQL 或 Enterprise Manager 可创建并处理方案对象。使用 Oracle Enterprise Manager 时，系统会生成基础 SQL 语句。 注：方案不一定需要直接与单个表空间相关。您可以定义一些配置，以便一个方案中的对象可以保存在不同的表空间中，一个表空间也可以保存不同方案中的对象。 创建数据库时，系统会创建多个方案，其中包括以下两个重要的方案： SYS 方案：包含数据字典，如“管理用户安全性”一课所述 SYSTEM 方案：包含存储管理信息的其它表和视图，如“管理用户安全性”一课所述,表数据的存储过程 创建表时，也会创建用于保存其数据的段。表空间包含一个段集合。 从逻辑上来说，表包含多行列值。行最终以行片段的形式存储在数据库块中。之所以将其称为“行片段”，是因为在某些情况下可能不会在一个位置存储整个行。当插入的行太大而无法装入单个块时，或当更新导致现有行超出了其当前空间时，就会发生这种情况。,表的查询和排序 SELECT ALL|DISTINCT|UNIQUE 表的别名. 选项 ,表的别名.选项 - 输出字段表或表达式 FROM 模式.表名 别名 ，表名 别名 表名 WHERE 条件表达式 -查询条件 GROUP BY 分组选项,分组选项， HAVING 筛选条件表达式 UNION|INTERSECT|MINUS SELECT子查询 ORDER BY 排序选项ASC|DESC ,排序选项ASC|DESC，,1.2 表管理,基本查询,SELECT 字段名表或表达式表 FROM 表名 WHERE 条件； 用户只能查询自己模式中的表，如果要查询其它模式中的对象，必须在对象名前添加模式名，如hr.employees，并且对该对象要有SELECT对象权限。 例：从一个表中查询出若干字段的内容。 SQL SELECT employee_id,first_name| |last_name 2 FROM employees；,基本查询,例：同上一例，为每个字段名定义一个标题 SQLSELECT employee_id 雇员编号,first_name 2 | |last_name 姓名 FROM employees； 雇员编号 姓名 - - 100 Steven King 101 Neena Kochhar,例:从表EMPLOYEES中查询出所有名字(last_name)以G开头且工资(salary)大于3000的人的名字、工资、部门编号。 SQLSELECT last_name 名字, salary 工资, 2 department_id 部门号 FROM employees 3 WHERE last_name like G% 4 and salary3000; 名字 工资 部门编号 - - - Greenberg 12000 100 Greene 9500 80,例：当前用户为user1,查询HR模式中的表DEPARTMENTS所有字段的内容。用户USER1对HR模式中的表DEPARTMENTS有SELECT对象权限。 SQL SHOW USER； USER 为“USER1“ SQL SELECT * FROM hr.departments； DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID - - - - 10 Administration 200 1700 20 Marketing 201 1800,多表查询,在使用多表时，必须指定每个表的别名，并在字段名前用别名指定字段所属的表，如e.first_name,e是表EMPLOYEES的别名。 例：从表EMPLOYEES和DEPARTMENTS中查询出每个员工所在部门的名称，以部分编号department_id作为两个表之间关联字段 SQLSELECT e.employee_id 雇员编号, 2 d.department_name 所在部门 3 FROM employees e,departments d 4 WHERE e.department_id = d.department_id； 雇员编号 所在部门 - - 100 Executive,例：从多表中查询出所有姓SMITH的雇员的名字、所在部门的名称及部门所在的城市。 SQL SELECT e.first_name,e.last_name, 2 d.department_name,l.city 3 FROM employees e,departments d,locations l 4 WHERE e.department_id=d.department_id 5 AND d.location_id=l.location_id 6 AND last_name=Smith; FIRST_NAME LAST_NAME DEPARTMENT_NAME CITY - Lindsey Smith Sales Oxford William Smith Sales Oxford,多表查询,分组统计查询,所谓分组是指ORACLE先对选择的行按指定的值进行分组，然后返回每组中的汇总信息。 要进行分组查询必须使用GROUP BY子句或HAVING子句。GROUP子句的表达式中可以包括表中的任何字段，不管该字段是否出现在字段名表中。HAVING子句用于有条件的显示分组。 在分组统计时要用SQL多行存储函数。但在WHERE条件中不能使用多行存储函数。,例：按部分编号统计出表EMPLOYEES中每个部门的人数。 SQLSELECT department_id 部门号, 2 count(*) 部门人数 FROM employees 3 GROUP BY department_id; 部门号 部门人数 - - 10 1 20 2 30 6,分组统计查询,例：显示部门人数少于5人的部门编号及人数。 SQLSELECT department_id,count(*) FROM 2 employees WHERE count(*)5 3 GROUP BY department_id； where count(*)=5 * ERROR 位于第 2 行: ORA-00934: 此处不允许使用分组存储函数。 在WHERE条件子句中不能使用多行分组存储函数，必须使用“HAVING 条件”子句。,分组统计查询,SQLSELECT department_id 部门号, 2 count(*) 部门人数 FROM employees 3 HAVING count(*)5 4 GROUP BY department_id; 部门号 部门人数 - - 10 1 20 2 40 1 70 1 90 3 110 2,分组统计查询,分组统计查询,WHERE子句和HAVING子句可以同时使用，WHERE必须在GROUP BY HAVING前。,例：显示所有工资大于5000，且部门人数大于5个的部门编号、部门最低工资和部门人数。 SQLSELECT department_id 部门号, 2 min(salary) 最低工资, COUNT(*) 部门人数 3 FROM employees WHERE salary5000 4 GROUP BY department_id 5 HAVING count(*)5; 部门号 最低工资 部门人数 - - - 80 6100 34,数据排序,如果想把查询的结果按一定的要求进行排序，可以在SELECT语句中使用ORDER BY子句。排序时可以在ORDER BY子句中的每个字段名后用ASC（升序）和DESC（降序）来完成升序或降序排序。缺省时按升序排序。,例： SQLSELECT department_id,first_name, salary FROM employees where salary=13000 ORDER BY department_id,salary DESC,数据排序,例：从表EMPLOYEES中查询部门人数大于5的部门编号、最低工资和部门人数，并要求按部门人数降序排序。 SQLSELECT department_id 部门号, 2 min(salary) 最低工资,COUNT(*) 部门人数 3 FROM employees GROUP BY department_id 4 HAVING count(*)5 5 ORDER BY count(*) DESC; 部门号 最低工资 部门人数 - - - 50 3210 45 80 7210 34 30 3610 6,子查询,子查询是指在一个SELECT查询中含有其它SELECT语句。子查询通常用在WHERE子句中，即将一个查询的结果作为条件。 例 :统计表EMPLOYEES中所有工资小于平均工资的人数。 SQL SELECT count(*) FROM employees 2 WHERE salary(SELECT avg(salary) 3 FROM employees) COUNT(*) - 56,集合运算是指将两个或多个子查询的结果进行并(UNION)、交(INTERSECT)和减(MINUS)等操作。 SQL SELECT * FROM emp1； EMPLOYEE_ID EMPNAME DEPARTMENT_ID - - - 100 Steven King 90 101 Neena Kochhar 90 102 Lex De Haan 90 145 John Russell 80 203 John Smith 40 213 Peter Smith 40,集合运算,集合运算,SQL SELECT * FROM emp2； EMPLOYEE_ID NAME DEPARTMENT_ID SALARY - - - - 100 Steven King 90 40010 102 Lex De Haan 90 33010 108 Nancy Greenberg 100 28010 145 John Russell 80 30010 146 Karen Partners 80 29510 205 Shelley Higgins 110 28010,例：并 SQL SELECT employee_id,empname FROM emp1 2 UNION - 如果用UNION ALL，重复行出现多次 3 SELECT employee_id,name FROM emp2； EMPLOYEE_ID EMPNAME - - 100 Steven King 101 Neena Kochhar 102 Lex De Haan 108 Nancy Greenberg 145 John Russell 146 Karen Partners 203 John Smith 205 Shelley Higgins 213 Peter Smith,集合并运算,集合交运算,集合的交运算INTERSECT，两个或多个子查询的公共行。 SQLSELECT employee_id,empname FROM emp1 2 INTERSECT 3 SELECT employee_id,name FROM emp2 EMPLOYEE_ID EMPNAME - - 100 Steven King 102 Lex De Haan 145 John Russell,集合减运算,集合减运算MINUS，从第一个查询结果中去掉出现在第二个查询结果中的行： SQLSELECT employee_id,empname FROM emp1 2 MINUS 3 SELECT employee_id,name FROM emp2 EMPLOYEE_ID EMPNAME - - 101 Neena Kochhar 203 John Smith 213 Peter Smith,建表权限 自己模式要有CREATE TABLE系统权限， 在其它模式中要有CREATE ANY TABLE。 向其它用户授权对表的访问，必须带有选 项WITH ADMIN OPTION 2. 建立表前的准备 设计表结构，定义列名、类型、空值否、 缺省值有否，确定其存储特性、主键、表 间关系外键（或引用）等。,表结构的建立,建表命令,CREATE TABLE 模式名. 表名 (列名描述) -列名 类型名(长度)列名约 (表约束) -字段的约束条件 TABLESAPCE 表空间名 -表所在表空间(有配额） PCTFREE 整数 -块内预留空间的百分比 PCTUSED 整数 -块中已用空间小于该值时，块才能插入 INITRANS 整数 -并发修改块的最小事务数(1-255) MAXTRANS 整数 -并发修改块的最大事务数(1-255) STORAGE 存储子句-表中段的分配管理方式的存储参数 AS 子查询 -根据子查询的结果生成新表,例1：CREATE TABLE TTT ( name varchar2(10),age number(2) ; 上例在用户或模式的缺省表空间建立表TTT。 如果要在该用户缺省表空间以外的其它表空间建 立表，用户必须有UNLIMITED TABLESPACE 系统权限。 例2：CREATE TABLE TEST (SNO CHAR(4), PICT BLOB) TABLESPACE SWJ; 例3:在HR模式下建立表TEST CREATE TABLE HR.TEST (SNO CHAR(4), BIRTHDAY DATE,AGE NUMBER(3);,列或表约束:紧跟在列名定义后，有下面几种： 列名 类型 CONSTRAINT 约束名 约束 CHECK (条件) 列值满足指定条件 DEFAULT (表达式) 向列中放缺省值。 NOT NULL 该列中的值不能为空。 UNIQUE 在表中是唯一的。 PRIMARY KEY 表的主键，非空且唯一。 例4：CREATE TABLE TT (n1 char(3),n2 char(4) DEFAULT (TEST), PRIMARY KEY(n1,n2); 例5:CREATE TABLE T1001 (NAME CHAR(10), SSEX CHAR(2) CHECK(SSEX IN (男,女) ) ， SOLD NUMBER(3) CHECK (SOLD BETWEEN 17 and 20) DEFAULT (17) );,FOREIGN KEY 外键是另一表的主键。外键 为单列时，列前加 “REFERENCES 引用表名”。 如果是多列，必须定义FOREIGN KEY为表约束。 例6: CREATE TABLE TT (N1 CHAR(3), N2 CHAR(4) DEFAULT (TEST), PRIMARY KEY(N1)； 例7: CREATE TABLE TT2 (N CHAR(4), N1 CHAR(3) REFERENCES TT);,子表,注意： 外键加ON DELETE CASCADE表示父表中记录删除时，则子表中的相应记录也删除。 外键必须是另一表的主键，且类型一致，名称可以不同。外键可多个。 父表必须在子表之前定义。录入时先有父记录。,例12：CREATE TABLE TEST1 ( SNO CHAR(3) NOT NULL, NAME VARCHAR2(10) NOT NULL, AGE NUMBER(2) CHECK (AGE=15), CNO VARCHAR2(10) , FN1 CHAR(2), FN2 CHAR(3), PRIMARY KEY (SNO,CNO), FOREIGN KEY (FN1,FN2) REFERENCES TTT(F1,F2) ); 可给约束命名，约束名不能相同： 例13：CREATE TABLE MMM (N NUMBER(4) CONSTRAINT MN CHECK(N10), NN NUMBER(4), CONSTRAINT KN CHECK(NN 100);,例,STORAGE(参数1 值1 参数2 值2 .) 下列参数： INITIAL 值(K或M)：表创建时分配初始值 NEXT 值：第二个分配的区间大小(K或M) PCTINCREASE 值：每次增长百分比 MAXEXTENTS 值:最大区间个数 MINEXTENTS 值:最小区间个数 其它： TABLESPACE 表空间名:表所在的表空间 PCTFREE 值：块内预留自由空间百分比 PCTUSED 值：块内已用空间的最小百分数 PCTFREE+PCTUSED100,建表例题,例14:CREATE TABLE TEST (NAME CHAR(10), AGE NUMBER(3),CLASS VARCHAR2(30), JOB VARCHAR(30) ) STORAGE (INITIAL 3M NEXT 200K MINEXTENTS 1 MAXEXTENTS 50 PCTINCREASE 15) TABLESPACE SWJ, PCTFREE 20,PCTUSED 60 注意：STORAGE()中的各参数中间是空格。 DESC 表名 -显示表结构,例：根据现有表生成新的表。根据EMP4表生成表EMP5，新表的列名与原表一致。 SQL CREATE TABLE emp5 AS 2 SELECT employee_id,department_id 3 FROM employees ； SQL CREATE TABLE emp AS 2 SELECT employee_id, 3 first_name| |last_name Name,department_id, 4 salary FROM employees 5 WHERE salary=25000； =CREATE TABLE emp(name,department_id),新表列名不变(名称及类型定义不变) 例15：CREATE TABLE EMP1 AS SELECT EMPLOYEE_ID,JOB_ID FROM EMPLOYEES; -记录复制到新表 新表列名发生变化，类型不变，并指定其它参数 例16:CREATE TABLE EMP2 (E_ID,E_NAME) TABLESPACE USERS AS SELECT EMPLOYEE_ID,LAST_NAME FROM EMPLOYEES WHERE 11; 上例只复制结构。,USER_TABLES :用户是所有者的表信息 TABLE_NAME, TABLESPACE_NAME PCT_FREE: 自由空间百分比 PCT_USED: 使用空间百分比 SQLSELECT TABLE_NAME 2 FROM USER_TABLES; ALL_TABLES: 用户可以访问的表信息，列名有： OWNER,TABLE_NAME,TABLESPACE_NAME等 SQLSELECT OWNER,TABLE_NAME FROM 2 ALL_TABLES WHERE OWNER=HR；,查询表信息,USER_TAB_COLUMNS和ALL_TAB_COLUMNS 用户表或所有可访问表的列信息。OWNER USER_TAB_COLUMNS常用列名： TABLE_NAME(表名),COLUMN_NAME(列名) DATA_TYPE(类型), DATA_LENGTH(长度) DATA_PRECISION(宽度),DATA_SCALE(小数) NULLABLE(可空否,N 或Y), COLUMN_ID DATA_DEFAULT(缺省值) USER_CONSTRAINTS所有用户表的约束信息 OWNER， CONSTRAINT_NAME(约束名字) CONSTRAINT_TYPE(约束类型) TABLE_NAME,查询表信息,修改表结构,修改表可以增加字段、重定义字段、存储特性等。 ALTER TABLE 表名 ADD （列描述1,) -增加列名 DROP COLUMN 列名 -删除列名 MODIFY (字段描述1,) -修改列 例1：增加列 CREATE TABLE TEST (NN NUMBER(3); ALTER TABLE TEST ADD( N1 CHAR(2) NOT NULL, N2 CHAR(4) ); 例2：修改列： alter table test modify( n1 number(3), n2 char(30) not null);,ALTERTABLE例,例3：删除列 ALTER TABLE TEST DROP COLUMN N2 注意： 不能修改列名。长度不能改小，可以增加列宽 。 删除和修改列不能一起进行，增加与修改可以。 减少列宽或修改数据类型时，列值必须为空。 如果列约束,用CASCADE CONSTRAINTS附加功能才能删除该列的约束。 例4：删除列及列的约束 ALTER TABLE TTT DROP COLUMN T1 CASCADE CONSTRAINTS,修改表结构,DROP是删除表对象，不能回滚，要有权限。 DROP TABLE 表名 DROP TABLE TTT; 要在删除表的同时也删除由该生成的视图和约束 等，用命令： DROP TABLE TT CASCADE CONSTRAINTS； 重新命名表 表重新命名时自动更新约束、索引和权限，但对 应的视图、同义词等标记为非法。 RENAME EMPLOYEES TO EMP _NEW;,删除和重新命名表,表记录操作,一、表数据修改 插入、修改和删除。一次只对一个表。提交回滚 数据插入到表中（需要提交） INSERT INTO 表名 (列名表)VALUES (表达式表) 例1: INSERT INTO JOBS (JOB_ID, JOB_TITLE) VALUES(MY_JOB,POLICE); 注意：列名表与表达式表在类型、个数上要一致 省略列名表，在VALUES中要提供所有列的值。 例2：INSERT INTO JOBS VALUES (AD_PLA,General ,2345,3455);,例3：有日期型字段： CREATE TABLE TT(N CHAR(3),DD DATE); INSERT INTO TT VALUES(123, TO_DATE(1998-1-1,YYYY-MM-DD); INSERT INTO TT VALUES(123, DATE 1999-1-23); INSERT INTO TT VALUES(123, 2000-1-23); 例4：有约束条件的：CREATE TABLE T1 ( N NUMBER(4) DEFAULT (20) CHECK (N BETWEEN 20 AND 43) ); INSERT INTO T1 VALUES(DEFAULT); INSERT INTO T1 VALUES(40); INSERT INTO T1 VALUES(50); 错,表记录操作,例5：大对象字段：NCLOB，CLOB，BFILE CREATE TABLE T (N CLOB,M NCLOB, B BFILE, BL BLOB); INSERT INTO T VALUES(CCC,NNN, BFILENAME(H:,SE.DOC),CCCC) BFILENAME(目录名，文件名) 注意:BFILE，BLOB列不能出现SELECT列表中。,2. 插入多行 例5 : CREATE TABLE TEST AS SELECT * FROM JOBS WHERE 11; INSERT INTO TEST (SELECT JOB_ID, JOB_TITLE, MIN_SALARY,MAX_SALARY FROM JOBS WHERE JOB_ID LIKE AD%); INSERT INTO TEST(JOB_ID,JOB_TITLE) (SELECT JOB_ID,LAST_NAME FROM EMPLOYEES WHERE JOB_ID LIKE AD%); 注意：子查询的列数与类型要与表一致。,语法: UPDATE 表名 SET 列名1=表达式1， WHERE 条件 功能: 修改一个表中一个或多个记录的一个或多个列值。 UPDATE EMPLOYEES SET SALARY = SALARY*(1+0.25); UPDATE DEPARTMENTS SET DEPARTMENT_NAME =DEPARTMENT_NAME | MANAGER WHERE DEPARTMENT_NAME LIKE A%;,更新操作(UPDATE 需要提交),例1:1999-1-1以后参加工作的工资加1000,提前十天。 SQLUPDATE EMPLOYEES SET SALARY = 2 SALARY+1000, HIRE_DATE = HIRE_DATE-10 3 WHERE HIRE_DATE=DATE 1999-1-1;,更新操作(UPDATE 需要提交),例2：改Douglas Grant的工资、工作编号、部门编号 UPDATE EMPLOYEES SET JOB_ID = SA_MAN, SALARY = SALARY + 1000, DEPARTMENT_ID = 120 WHERE FIRST_NAME| |LAST_NAME = Douglas Grant;,UPDATE与子查询,例3：将Smith所在部门的每个员工的工资增加1000. UPDATE EMPLOYEES SET SALARY = SALARY+1000 WHERE DEPARTMENT_ID= SOME( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME=Smith);,例4：将部门名称Sales的员工的工资增加20% UPDATE EMPLOYEES SET SALARY=SALARY*1.2 WHERE DEPARTMENT_ID= (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME=Sales);,DELETE FROM 表名 WHERE 条件 从一个指定表中删除一个或多个记录。 例1: DELETE FROM EMPLOYEES WHERE LAST_NAME NOT LIKE A%;,删除数据（需要提交）,例2：与子查询联合使用： 删除Stiles所在部门的所有员工(子查询) DELETE FROM EMPLOYEES WHERE JOB_ID= (SELECT JOB_ID FROM EMPLOYEES WHERE LAST_NAME=Stiles),例3：从雇员表中删除工作名称以开头的所有记录(多表)。 DELETE FROM EMPLOYEES WHERE JOB_ID IN (SELECT JOB_ID FROM JOBS WHERE JOB_TITLE LIKE A%); 提交后，才真正删除表中的数据。慢(放到回滚段),例4：删除所有记录： CREATE TABLE EM1 AS SELECT LAST_NAME FROM EMPLOYEES; DELETE FROM EM1WHERE 1=1; DELETE FROM EM1; 可以回滚。提交后才真正从表中删除，速度慢。,TRUNCATE,TRUNCATE TABLE 表名 删除所有记录，不能回滚，不用提交。 必须是用户自己或有系统权限DROP ANY TABLE。 TRUNCATE TABLE SSSS; -删除 要保留原来的空间，用REUSE STOREAGE TRUNCATE TABLE SSSS REUSE STOREATE,事务控制命令,事务概念 事务是一个或多个SQL语句所组成的序列(只IUD)。 提交是指执行完数据库修改命令后，要通知数据 库进行修改操作。提交前只有修改者能看到改变。 回滚是指撤消尚未提交的更新操作。,2. 事务划分 一个事务从执行一条SQL修改命令开始，有下列情况结束该事务：遇到COMMIT或ROLLBACK； 执行DDL(建表等);程序异常结束。如果没有上述情况将把整个程序作为一个事务。,3. 事务提交(无需权限) 显式提交：执行COMMIT命令 隐式提交：执行命令ALTER,COMMENT ,CONNECT,CREATE,DROP,EXIT等命令时都 隐含COMMIT操作。 自动提交：在SQL PLUS中执行 SET AUTO ON 后每次IUD自动提交。 4. 事务回滚 显式回滚：执行ROLLBACK命令 隐式回滚：执行期间发生错误；发现死锁或事务 流产。,事务控制命令,索引表中记录索引关键字和记录号。索引块小、 查询快、自动维护、索引独立于数据可随时删除 和重建。但索引后，IDU时会降低速度。 建立索引 CREATE UNIQUE INDEX 索引名 ON 表名(列名1ASC|DESC,.) TABLESPACE 表空间名 STOREAGE 存储子句 UNIQUE 唯一索引，ASC升序，DESC 降序,索引,例1：CREATE INDEX SN ON STUDENT(SNAME); CREATE INDEX SN1 ON STUDENT(SNAME,SSEX); 注：索引的使用在SELECTWHERE的条件中 体现。即如果条件涉及索引，自动按索引查询。 CREATE UNIQUE INDEX EMM ON EMPLOYEES(EMPLOYEE_ID); 2. 修改索引 主要修改索引的存储和事务参数。 ALTER INDEX 索引名 INITRANS m MAXTRANS n STOREAGE 存储子句,建立索引,例2：ALTER INDEX SN INITRANS 5 STORAGE(NEXT 100K); 3. 删除索引：DROP INDEX 索引名 例3：DROP INDEX SN; 注意：索引的维护和使用都是自动。 4. 索引数据字典 USER_INDEXES用户索引表 INDEX_NAME,TABLE_OWNER,STATUS, TABLE_NAME,TABLESPACE_NAME UNIQUENESS select index_name,index_type,tablespace_name, table_name,tablespace_name,uniqueness from user_indexes where table_name=EMPLOYEES,USER_IND_COLUMNS:用户索引列信息 INDEX_NAME,TABLE_NAME, COLUMN_NAME,COLUMN_LENGTH COLUMN_POSITION(列在结构中的位置); DESCEND: ASC或DESC 注：每个索引字段在该表中有一条记录。 例5：CREATE INDEX JOB_IND ON JOBS(JOB_ID); SELECT INDEX_NAME,TABLE_NAME, TABLE_OWNER, UNIQUENESS,STATUS FROM USER_INDEXES;,SELECT OWNER,INDEX_NAME FROM ALL_INDEXES WHERE OWNER=SWJ001; SELECT INDEX_NAME,COLUMN_NAME, COLUMN_POSITION,DESCEND FROM USER_IND_COLUMNS;,视图是一条从一个表或多表中查询数据的 SQL语句。但用户可以象使用表一样对视 图进行查询,对视图的插入、删除和修改是 对基表进行的。建立视图的系统权限： CREATE ANY VIEW 视图的优点：安全性、隐蔽数据的复杂性。 建立视图 CREATE OR REPLACE VIEW 视图名 AS SELECT语句 WITH READ ONLY,视图(VIEW),说明：视图名是唯一的，与表名一样。 OR REPLACE 覆盖现有视图 WITH READ ONLY 只读视图，不能IDU 例1：视图中只有部分列,视图列名与原表相同。 SQLcreate view em_view as select 2 employee_id,last_name, first_name 3 from employees; SQLDESC EM_VIEW; -显示视图结构 SQLselect * from em_view;,建立视图,例：所有列，但部分记录： SQLCREATE OR REPLACE VIEW EMVIEW1 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=100; SQL SELECT * FROM EMVIEW1; 例：视图列的别名(有表达式必须有别名),SQLcreate view emp_view1 as select last_name, salary*12 annual_salary from employees where department_id = 20; SQLcreate view emp_view(last_name,annual_salary) as select last_name, salary*12 from employees where department_id = 50; SQLSelect last_name,annual_salary from emp_view;,例4：列起别名，部分行 create or replace view em1(eid,name) as select employee_id,last_name| |first_name from employees where department_id=50; SQL desc em1; 名称 是否为空? 类型 - - - EID NUMBER(6) NAME VARCHAR2(46) 查询视图内容 select name from em1 where name like G%;,例5：分组视图(部门编号及人数视图） create or replace view em2(dep_id,counts) as select department_id,count(*) from employees group by department_id; SELECT * FROM em2 WHERE COUNTS=5;,修改视图（其它模式要有ALTER ANY TABLE）是对无效视图进行重新编译。 ALTER VIEW 视图名 COMPILE 例7：ALTER VIEW EM2 COMPILE; 删除自己模式（或有DROP ANY VIEW) DROP VIEW 视图名 CASCADE CONSTRAINTS 例8：DROP VIEW EM1;,修改和删除视图,CREATE VIEW EV1 AS SELECT * FROM EMPLOYEES WHERE SALARY=5000; select department_id,max(salary),avg(salary) from ev1 group by department_id; CREATE VIEW EV2 AS SELECT * FROM EMPLOYEES WHERE SALARY=10000; 在EV1中查询工资大于EV2平均工资的人数 select count(*) from ev1 where salary=(select avg(salary) from ev2); select last_name from ev1 intersect select last_name from ev2;,视图的查询,更新视图内容,在对视图进行INSERT、UPDATE或DELETE操作时，如果没有为视图指定WITH REA