Download
1 / 81
810 likes | 1k Views
学习情境 4 ： 雇员信息录入修改查询. 情境导入. 项目应用的需求： 对雇员信息 录入 修改 查询. 2. 学习目标. 掌握 SQL 的概念。 掌握 Oracle 中常用的数据类型。 掌握 SQL 中数据查询和数据操纵功能，即数据定义、数据操纵语言。 创建和使用表、同义词、序列、视图、索引。 掌握事务控制和数据控制的功能。 掌握常用的运算符和常用函数的使用方法。. SQL 简介. SQL （ Structured Query Language ）称为结构化查询语言，最早是由 Boyce 和 Chamberlin 在 1974 年提出的。
E N D
情境导入 • 项目应用的需求： 对雇员信息 • 录入 • 修改 • 查询 2
学习目标 掌握SQL的概念。 掌握Oracle中常用的数据类型。 掌握SQL中数据查询和数据操纵功能，即数据定义、数据操纵语言。 创建和使用表、同义词、序列、视图、索引。 掌握事务控制和数据控制的功能。 掌握常用的运算符和常用函数的使用方法。
SQL简介 • SQL（Structured Query Language）称为结构化查询语言，最早是由Boyce和Chamberlin在1974年提出的。 • SQL是一种灵活、有效的语言，专门用来处理和检验关系型数据。SQL成为数据库领域的一个主流语言。 • SQL是所有RDBMS使用的公共语言，它不遵循任何特定的执行模式，一次可以访问多个记录。 • 能够完成数据查询（Data Query）、数据操纵（Data Manipulation）、数据定义（Data Definition）和数据控制（Data Control）等功能。
Oracle 数据类型6-1 常用数据类型： • 字符型 • 日期型 • 逻辑型 • 数字型 • RAW 数据类型 • LOB “大对象”数据类型
Oracle 数据类型 6-2 字符型： 1．CHAR 用来存储固定长度的字符数据。其数值的长度不超过2000字节。 语法格式： Var_field CHAR（n） n指定的字符长度。默认值为1。 例如： Var_field CHAR（8） 例如： Mytext1 CHAR（8）：=’Jack’; 2 ． LONG LONG 数据类型存储可变长度字符数据 LONG 数据类型最多能存储 2GB 3．VARCHAR 用来存储可变长度的字符数据。最大有32767个字节 语法格式： Var_field VARCHAR（n）n必须是正整数。 例如： Var_field VARCHAR（10） 例如： Mytext2 VARCHAR（10）：=’Hello world’; 6
Oracle 数据类型 6-3 日期型 4.DATE 用来存储日期时间类型的数据，用7字节分别描述 世纪、年、月、日、时、分、秒。 语法格式： date_field DATE 说明：日期默认格式为DD-MON-YY，分别对应日、月、年，例如17-JUN-08。月份的表达要用英文单词的缩写格式。日期的格式可以设置为中文格式，例如17-六月-08。 逻辑型： 5.BOOLEAN 逻辑型（布尔型）变量的值只有TRUE或FALSE。逻辑型变量一般用于判断状态，根据 “真”或“假”值决定程序执行分支。 关系表达式的值就是一个逻辑值。 7
Oracle 数据类型 6-4 数字型： 6.NUMBER NUMBER数据类型可用来表示所有的数值类型。 语法格式： num_field NUMBER(percision，scale) 说明：percision表示总的位数，scale表示小数的位数，默认表示小数位为0。 例如： num_field NUMBER(8，2)； 其中，num_field是一个整数部分最多6位，小数部分最多2位的变量。 8
Oracle 数据类型 6-5 RAW 数据类型： 7. RAW ：RAW 数据类型用于存储二进制数据 RAW 数据类型最多能存储 2000 字节 LONG RAW 数据类型用于存储可变长度的二进制数据 LONG RAW 数据类型最多能存储 2 GB 9
Oracle 数据类型 6-6 LOB “大对象”数据类型： 8. LOB ：可以存储多达 4GB 的非结构化信息，例如声音剪辑和视频文件等。 CLOB 即 Character LOB（字符 LOB），它能够存储大量字符数据 BLOB 即 Binary LOB（二进制 LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件 BFILE 即 Binary File（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中 10
数据类型转换 数据类型之间常见的转化函数： • TO_CHAR：将NUMBER和DATE类型转换成VARCHAR类型。 • TO_DATE：将CHAR转换成DATE类型。 • TO_NUMBER：将CHAR转换成NUMBER类型。
数据定义语言 • 数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象 • 用于操纵表结构的数据定义语言命令有： • CREATE TABLE 创建新的数据表 • ALTER TABLE 修改表，包括增加列、修改列的属性和删除列 • DROP TABLE 删除表及表中存储的数据 • TRUNCATE TABLE删除了指定表中所有行，但表的结构及其列、约束、索引等保持不变。
任务1：创建表 -1 创建表 用企业管理控制台方式创建表 命令行方式创建表 CREATE TABLE [ schema.]table_name (column_name datatype [DEFAULT expression][column_constraint],…n) [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace_name] [STORAGE storage_clause] [CLUSTER cluster_name(cluster_column,…n)] [AS subquery]; 13
数据库块组成 Header Free space Data
块空间利用情况参数 INITRANS MAXTRANS PCTFREE PCTUSED
块空间的使用 PCTFREE=20 PCTUSED=40 80% Inserts Inserts 1 2 80% Inserts Inserts 40% 3 4
PCTFREE和PCTUSED的设置 计算PCTFREE 计算PCTUSED (平均行大小 -初始行大小) * 100 平均行大小 平均行大小 * 100100 - PCTFREE -可用数据空间
表-2 创建表 例5.4 创建教师表teacher。 SQL> CREATE TABLE teacher ( t_no char (6) PRIMARY KEY , t_name char(8) NOT NULL, t_sex char(2) CHECK(t_sex IN ('男','女')), t_birthday DATE, tech_title char(10)) TABLESPACE users PCTFREE 10 PCTUSED 60 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS 20 PCTINCREASE 50); 18
表-3 • 查看表 • 用企业管理控制台方式创建表 • 命令行方式创建表 DESC命令查看表 例查看teacher表的方案、表空间、存储参数、块空间管理参数等信息。 SQL> Select owner 方案名,tablespace_name 表空间, pct_free 保留更新百分比,pct_used 插入空间阈值百分比, initial_extent 第一个区大小,next_extent 下一个区大小, min_extents 最小数量,max_extents 最大数量, pct_increase 增量 FROM dba_tables WHERE table_name='TEACHER'; 例 SQL> DESC teacher; 19
表-4 • 修改表 • 用企业管理控制台方式创建表 • 命令行方式创建表 ALTER TABLE [schema.]table_name [ADD(column_name datatype [DEFAULT expression][column_constraint],…n)] [MODIFY ([datatype][DEFAULT expression] [column_constraint],…n)]; [STORAGE storage_clause] [DROP drop_clause] 20
表-5 例向表teacher中添加age、salary、salary_add三个字段。 SQL> ALTER TABLE teacher ADD (age number(2),salary number(3), salary_add number(2)); 例从表teacher中删除一个字段age。 SQL> ALTER TABLE teacher DROP COLUMN age; 例从表teacher中删除两个字段salary、salary_add 。 SQL> ALTER TABLE teacher DROP (salary,salary_add) DESC teacher； 注意：删除一个字段和删除多个字段的命令格式不同。 例 修改表teacher字段。 SQL> ALTER TABLE teacher MODIFY (t_name char(10),tech_title DEFAULT '讲师') 21
表-6 维护表中的数据 删除表 DROP TABLE [schema.] table_name; 22
同义词 -1 同义词是现有对象（表、视图、序列、过程、函数、包等）的别名 简化SQL语句 隐藏对象的名称和所有者 提供对对象的公共访问 同义词共有两种类型： 同义词 私有同义词 公有同义词 公有同义词可被所有的数据库用户访问。 私有同义词只能在其模式内访问，且不能与当前模式的对象同名。
同义词 -2 语法： CREATE ［ PUBLIC］SYNONYM ［schema.］Synonym_Name FOR ［schema.］Object_Name[@dblink]; 私有同义词 CREATE SYNONYM emp FOR SCOTT.emp; SCOTT.emp的别名 模式名 表名 公有同义词 CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp; 同义词名称 注：只有被授权CREATE PUBLIC SYNONYM的用户才可以创建公共同义词。
同义词 -3 创建或替换现有的同义词 CREATE OR REPLACE SYNONYM emp_syn FOR SCOTT.emp; 替换现有的同义词 删除同义词 SQL> DROP SYNONYM emp; SQL> DROP PUBLIC SYNONYM emp_syn; 公用同义词的好处： 多个不同的用户需要使用一个用户下的对象，可以只创建一次公用同义词。
同义词-4 查看同义词 企业管理控制台方式 命令行方式 DBA_SYNONYMS，ALL_SYNONYMS，USER_SYNONYMS视图 26
序列 序列是用于生成唯一、连续序号的对象 序列可以是升序的，也可以是降序的 使用CREATE SEQUENCE语句创建序列 SQL> CREATE SEQUENCE tID_Seq START WITH 10 INCREMENT BY 10 MAXVALUE 2000 MINVALUE 10 NOCYCLE CACHE 10; 指定序号之间的间隔为 10 表示序列的最大值为 2000 表示序列的最小值为 10 在达到最大值后停止生成下一个值 指定内存中预先分配的序号数
序列 创建序列 企业管理控制台方式 命令行方式 语法格式： CREATE SEQUENCE [schema.]sequence_name INCREMENT BY integer START WITH integer [MAXVALUE integer | NOMAXVALUE] [MINVALUE integer | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE | NOCACHE] [ORDER|NOORDER]; 28
序列优点及访问 优点： 减少编写序列生成代码所需的工作量。 程序员经常用序列来简化一些程序的设计工作。 通过序列的伪列来访问序列的值 NEXTVAL 返回序列的下一个值 CURRVAL 返回序列的当前值
访问序列 例 对teacher表的t_no列插入新值，使用NEXTVAL利用序列tID_Seq自动地生成t_no列的值。 SQL> INSERT INTO teacher (t_no,t_name,t_sex,t_birthday,tech_title) VALUES (tID_Seq.NEXTVAL,'jack','男',to_date('02-04-1978','dd-mm-yyyy'),null); SQL> INSERT INTO teacher (t_no,t_name,t_sex,t_birthday,tech_title) VALUES (tID_Seq.NEXTVAL, 'peter','男',to_date('03-05-1978','dd-mm-yyyy'),null); 指定序列的下一个值 SQL> SELECT tID_Seq.CURRVAL FROM dual; 检索序列的当前值
管理序列 USER_SEQUENCES数据字典视图可查询序列的设置。 SQL> SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE, INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES; 31
修改序列 修改序列 企业管理控制台方式 命令行方式 语法格式： ALTER SEQUENCE [schema.]sequence_name INCREMENT BY integer START WITH integer [MAXVALUE integer | NOMAXVALUE] [MINVALUE integer | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE | NOCACHE]; SQL> ALTER SEQUENCE tID_Seq MAXVALUE 5000 CYCLE; 使用ALTER SEQUENCE语句修改序列， 不能更改序列的START WITH参数 32
删除序列 删除序列 企业管理控制台方式 命令行方式 语法格式： DROP SEQUENCE sequence_name; 使用DROP SEQUENCE语句删除序列 SQL> DROP SEQUENCE tID_Seq; 33
视图 视图以经过定制的方式显示来自一个或多个表的数据 视图可以视为“虚拟表”或“存储的查询” 创建视图所依据的表称为“基表” 视图的优点有： 确保数据表的安全性和提高数据的隐蔽性 从一个或多个表中或其他视图中使用SELECT语句导出的虚表 仅存放视图的定义，而不是存放视图对应的数据，数据仍存放在基础表中，对视图中数据的操纵实际上仍是对组成视图的基础表的操纵。 通过使用视图，基础表中的数据能以各种不同的方式提供给用户，以加强数据库的安全性。
创建视图 -1 Stud_details 创建视图 CREATE VIEW stud_view AS SELECT studno, studname, subno FROM Stud_details; Stud_view
创建视图 -2 创建视图 企业管理控制台方式 命令行方式 语法： CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY];
视图上的DML语句 在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE 视图上的DML语句有如下限制： 只能修改一个底层的基表 如果修改违反了基表的约束条件，则无法更新视图 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图 如果视图包含伪列或表达式，则将无法更新视图
键保留表 Stud_details Sub_details 联接视图 Stud_details为什么 是键保留表？ 键保留表 因为 Studno既是 Stud_details 中的主键， 也是联接结果中的主键
获取视图可更新信息 视图: dba_update_columns all_update_columns User_update_columns SQL> Select column_name,updateable from user_update_columns where table_name=‘emp_dept’; (view)
管理视图 查询数据 更改视图 语法格式：CREATE OR REPLACE VIEW view_name AS subquery; 重命名视图 语法格式： RENAME <old_view_name> TO <new_view_name>; 删除视图 语法格式：DROP VIEW view_name; 查询视图与数据字典 ALL_VIEWS 描述当前用户可用的视图信息 USER_VIEWS 描述当前用户自己创建的视图信息 DBA_VIEWS 描述所有视图信息
索引优缺点 • 索引 供服务器在表中快速查找一个行的数据库结构，在逻辑上和物理上都独立于基表。Oracle 自动维护索引 • 优点： ① 快速存取数据。 ② 改善数据库性能，实施数据的唯一性和参照完整性。 ③ 多表检索数据的过程快。 ④ 进行数据检索时，利用索引可以减少排序和分组的时间。 • 缺点： ① 索引将占用磁盘空间。 ② 创建索引需要花费时间。 ③ 延长了数据修改的时间，因为在数据修改的同时，还要更新索引
索引分类 按存储方法分类 B*树索引 位图索引 按功能和索引对象分类 唯一索引 非唯一索引 分区索引 正向索引 反向索引 基于函数的索引 索引的分类： 索引的类型 组合索引 反向键索引 唯一索引 基于函数的索引 位图索引
B-树索引 索引条目 根 分支 索引条目头 键列长度 叶 键列值 ROWID
反向键索引 EMP的索引 (EMPNO) EMP表 EMPNO ENAME JOB ... ----- ----- -------- 7499 ALLEN SALESMAN 7369 SMITH CLERK 7521 WARD SALESMAN ... 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER ... ... ... ... ... ... ... ... KEY ROWID EMPNO (BLOCK# ROW# FILE#) ----- ------------------- 1257 0000000F.0002.0001 2877 0000000F.0006.0001 4567 0000000F.0004.0001 6657 0000000F.0003.0001 8967 0000000F.0005.0001 9637 0000000F.0001.0001 9947 0000000F.0000.0001 ... ... ... ...
位图索引 开始 ROWID 结束 ROWID 键 位图 <Blue, 10.0.3, 12.8.3, 1000100100010010100> <Green, 10.0.3, 12.8.3, 0001010000100100000> <Red, 10.0.3, 12.8.3, 0100000011000001001> <Yellow, 10.0.3, 12.8.3, 0010001000001000010> 文件 3 表 块 10 块 11 块 12 索引
B-树与位图索引的比较 B-树 适合于高基数的列 键值的更新相对昂贵 对使用或谓词的查询语句无效适用于OLTP 位图 适合于低基数的列 键值的更新非常昂贵 对使用或谓词的查询语句有效适用于 DSS
标准索引 创建标准索引 SQL> CREATE INDEX emp_ename ON emp(ename) TABLESPACE users ; 删除索引 SQL> DROP INDEX emp_ename;
唯一索引 唯一索引确保在定义索引的列中没有重复值 Oracle 自动在表的主键列上创建唯一索引 使用CREATE UNIQUE INDEX语句创建唯一索引 SQL> CREATE UNIQUE INDEX emp_empno ON emp(empno) Tablespace users ;
组合索引 组合索引是在表的多个列上创建的索引 索引中列的顺序是任意的 如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度 SQL> CREATE INDEX comp_index ON emp(empno,ename) ;
反向键索引反转索引列键值的每个字节 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上 创建索引时使用REVERSE关键字 反向键索引 SQL> CREATE INDEX emp_ename_rev ON emp (ename) tablespace users REVERSE; SQL> ALTER INDEX emp_ename_rev REBUID NOREVERSE;
