ORACLE

1. ORACLE 第一讲 SQL建表与查询

2. 学习目的： 1、正确理解并掌握Oracle数据库语言基本内容； 2、能结合Java语言开发项目。 学习要求： 1、课前作好预习；课后多作练习； 2、认真完成并上交每天的作业。 教材与参考教材： 1、郑阿奇 Oracle实用教程 电子工业出版社

3. ORACLE数据库开发课时安排 目 录 第1章 SQL建表与查询 第2章 单行函数、连接查询与子查询 第3章 DCL数据控制语言和TCL事务控制语言 第4章 数据库对象（视图、序列、同义词、索引） 第5章 PL/SQL编程基础 第6章 游标 第7章 PL/SQL表和记录类型 第8章 存储过程和函数 第9章 触发器 第10章 程序包 第11章 对象类型 第12章 可变数组和嵌套表

4. 本章目标 • 掌握ORACLE数据库92版本的安装 • 掌握ORACLE数据库五大语言 • 掌握SELECT三大子句

5. ORACLE数据库五大语言 • 数据定义语言（Data Definition Language） • CREATE 定义数据库实体结构 • ALTER 修改数据库实体结构 • DROP 删除数据库实体 • TRUNCATE 删除数据库实体的记录 • 数据操纵语言（Data Manipulation Language） • INSERT • UPDATE • DELETE

6. ORACLE数据库五大语言 • 数据查询语言（Data Query Language） • SELECT • 数据控制语言（Data Control Language） • GRANT • REVOKE • 事务控制语言（Transaction Control Language） • COMMIT • ROLLBACK • SAVEPOINT

7. ORACLE主要数据类型 • 字符数据类型 • CHAR数据类型：有固定长度和最大长度的字符串。存储在数据类型为CHAR字段中的数据将以空格的形式补到最大长度。长度定义在1——2000字节之间。 • VARCHAR2数据类型：可变长度的、有最大长度的字母数字型数据。VARCHAR2类型的字段长度可以达到4000字节，VARCHAR2类型的变量长度可以达到32676字节。存储在数据类型为VARCHAR2字段中的数据，其长度为用户实际输入的长度。 • 思考：一个空的VARCHAR2(2000)字段和一个空的VARCHAR2(2) 字段所占用的空间是否是一样的？

8. ORACLE主要数据类型 ※比较规则：对CHAR型数据，尾部的空格将被忽略掉，对于VARCHAR2型数据尾部带空格的数据排序比没有空格的要大些。比如：　　CHAR 型数据：‘YO’=‘YO ’VARCHAR2 型数据：‘YO’<‘YO ’ • LONG数据类型：可变长度的、有最大长度的字母数字型数据。 LONG类型的字段长度可以达到2GB. LONG 是一个遗留下来的而且在将来不会被支持的数据类型。它将被LOB(Large Object)数据类型所代替。在表中的某列上设置LONG数据类型时，需要遵循以下限制。

9. ORACLE主要数据类型 • 一个表中只有一列可以为LONG数据类型。 • LONG列不能定义为唯一约束或主键约束。 • LONG列上不能建立索引。 • 过程或存储过程不能接受LONG数据类型的参数 • 数值数据类型 • NUMBER数据类型：NUMBER(p,s)：p表示数据的总位数，s表示小数的位数。 • 日期时间数据类型 • DATE数据类型：用于存储日期的世纪、年、月、日、小时、分和秒。SYSDATE函数返回当前日期和时间。

10. ORACLE主要数据类型 • TIMESTAMP数据类型：用于存储日期的年、月、日以及时间的小时、分和秒。该数据类型同时包含时区信息。SYSTIMESTAMP函数返回当前日期、时间和时区。 • LOB数据类型（“大对象”数据类型） • CLOB（Character LOB）：存储大量字符数据，是内部LOB。 • BLOB（Binary LOB）：存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，是内部LOB。 • BFILE（Binary File）：存储二进制文件，是外部LOB。

11. 创建表 • 数据定义语言（DDL） • 创建表 • CREATE TABLE [schema.] table_name ( column_name1 datatype, column_name2 datatype, column_name3 datatype) • 其中： • schema表示对象的所有者即模式的名称。如果用户在自己的模式中创建表，则可以不指定所有者名称。 • table_name表示表的名称。 • column_name表示列的名称。 • datatype表示该列的数据类型及其长度。

12. 创建表 • 创建表时，需要指定下列内容： • 唯一的表名称。 • 表内唯一的列名称。 • 列的数据类型及其宽度。 • 表名的命名规则： • 表名首字符应该为字母。 • 不能使用ORACLE保留字来为表命名。 • 表名的最大长度为30个字符。 • 同一用户模式下的不同表不能具有相同的名称。 • 可以使用下划线、数字和字母，但不能使用空格和单引号。 • 举例：创建student表

13. 创建表 CREATE TABLE student( stuid VARCHAR2(20) primary key, --指定这一字段为student表主键 stuname VARCHAR2(50), sex CHAR(2)); 注意：最后一个字段没有“,”，查看表的结构用desc tablename • 用查询的结果集创建一张表 • 拷贝一张表的全部信息，即建一张新表，这张表的内容与另外存在的一张表的内容完全相同而表名不同。 CREATE TABLE newtablename AS SELECT * FROM existtablename; • 举例：CREATE TABLE emp1 AS SELECT * FROM emp;

14. 创建表 • 拷贝一张表，这张表的内容为另外一张存在的表的某些字段及这些字段的全部记录值。 CREATE TABLE newtablename AS SELECT column1,column2 FROM existtablename; • 举例：CREATE TABLE emp2 AS SELECT empno,ename,job,deptno FROM emp; • 拷贝一张表，这张表的内容为另外一张存在的表的某些字段及其全部记录值，但这些字段名与另一张已存在的表的字段名不同。 CREATE TABLE newtablename (newcolumnname1, newcolumnname2) AS SELECT existcolumn1, existcolumn2 FROM existtablename; • 举例：CREATE TABLE emp3(员工编号,员工姓名) AS SELECT empno,ename FROM emp;

15. 创建表 • 拷贝一张表，只拷贝已存在表的结构，不拷贝表的内容。 CREATE TABLE newtablename AS SELECT * FROM existtablename WHERE 恒假条件; • 举例：CREATE TABLE emp4 AS SELECT * FROM emp WHERE 1=2; • 修改表 • 添加新列 ALTER TABLE table_name ADD( newcolumnname datatype); • 举例：给emp1表增加新列 ALTER TABLE emp1 ADD(remark VARCHAR2(100));

16. 修改表 • 更改现有列的数据类型和字段宽度 ALTER TABLE tablename MODIFY( columnname datatype); • 举例：修改remark列宽为200字节 ALTER TABLE emp1 MODIFY(remark VARCHAR2(200)); • 更改列名 ALTER TABLE tablename RENAME COLUMN oldcolumnname TO newcolumnname; • 举例：更改emp1表中remark列名为email ALTER TABLE emp1 RENAME COLUMN remark TO email;

17. 修改表 • 删除表中现有列 ALTER TABLE tablename DROP COLUMN columnname; • 举例：删除emp1表中的email列 ALTER TABLE emp1 DROP COLUMN email; • 添加或删除完整性约束条件 • 将某列定义为主键约束 ALTER TABLE emp1 ADD CONSTRAINT keyname PRIMARY KEY(columnname); • 举例：将emp1表中的empno列设置为主键 ALTER TABLE emp1 ADD CONSTRAINT PK_emp1 PRIMARY KEY(empno);

18. 修改表 • 将某列定义为唯一键约束 ALTER TABLE tablename ADD CONSTRAINT keyname UNIQUE(columnname); • 举例：将emp1表中的ename列设置为唯一键 ALTER TABLE emp1 ADD CONSTRAINT UN_emp1 UNIQUE(ename); • 将某列定义为外键约束 ALTER TABLE tablename ADD CONSTRAINT keyname FOREIGN KEY(columnname) REFERENCES ref_table(ref_columnname) ON DELETE CASCADE; 其中： • tablename指定被修改的从表名 • columnname为从表中外键的列名 • ref_table为主表的表名

19. 修改表 • ref_columnname为主表中主键的列名 • ON DELETE CASCADE 级联删除 举例：dept1表为主表，deptno字段已定义为主键，emp1表 为从表，将emp1.deptno字段定义为外键。 ALTER TABLE emp1 ADD CONSTRAINT FK_dept1 FOREIGN KEY(deptno) REFERENCES dept1(deptno); • 删除主键或唯一键约束 ALTER TABLE tablename DROP CONSTRAINT keyname; • 举例1：删除emp1表中的主键约束 ALTER TABLE emp1 DROP CONSTRAINT PK_emp1; • 举例2：删除emp1表中的唯一键约束 ALTER TABLE emp1 DROP CONSTRAINT UN_emp1; • 删除表间的参照关系

20. 修改表 ALTER TABLE tablename DROP CONSTRAINT keyname; • 举例：删除对emp1.deptno字段定义的FK_dept1外键约束 ALTER TABLE emp1 DROP CONSTRAINT FK_dept1 • 重命名表 RENAME oldtablename TO newtablename; • 举例：将emp1表的表名修改为newemp; RENAME emp1 TO newemp; • 删除表 DROP TABLE tablename; • 举例：删除emp1表 DROP TABLE emp1;

21. 截断表 • 截断表 只删除表中的记录而不删除表结构。与DELECT的的区别在于，前者不使用事务处理，而后者可以通过ROLLBACK撤销删除。如果确定表中的数据不再有用，使用TRUNCATE命令效率更高。 TRUNCATE TABLE tablename; • 举例：删除emp1表中的记录 TRUNCATE TABLE emp1;

22. SELECT三大子句 • WHERE 根据给定条件，对DML、DQL操作的记录进行筛选。 WHERE子句中的条件可以是一个包含比较操作符条件表达式，也可以是一个含有SQL操作符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成的复合条件。WHERE可以和SELECT,DELETE, UPDATE一起使用，ORACLE将根据在WHERE子句中指定的条件从表中查询或删除或更新一行或多行。

23. SELECT三大子句 • 比较操作符 • =、>、<、>=、<=、!=、<> • SQL操作符 • between…and… not between…and… • in not in • like not like • is null is not nul • 逻辑运算符 • and • or • not

24. SELECT三大子句 • GROUP BY 用于将查询到的记录按给定的组进行显示或聚合运算。在包含GROUP BY子句的查询中，SELECT子句列表的项可以是分组函数、GROUP BY表达式（GROUP BY子句中出现的列或表达式）、常数或包含上述项之一的表达式。 除了聚合函数，SELECT后出现的列表项一定要在 GROUP BY后出现。 使用聚合函数不一定要加group by，使用group by不一定要有聚合函数。

25. SELECT三大子句 • 例:按照部门编号分组查询员工信息。 SELECT ename FROM emp GROUP BY deptno,ename; • 聚合函数 • 聚合函数COUNT()的几种用法及区别: • COUNT(*) 返回表中的记录数。 • COUNT(字段名) 返回指定列的记录不为空的记录数。 • COUNT(DISTINCT 字段名) 返回指定列的记录不为空且不重复的记录数。 • COUNT(0) 建立在主键上的，查找不为空的记录数。

26. SELECT三大子句 • 几种常用的聚合函数： • SUM() • AVG() • MAX() • MIN() • HAVING HAVING子句用来指定GROUP BY子句的检索条件。该子句位于GROUP BY子句后面。 有GROUP BY不一定有HAVING，但是如果有HAVING就必然有GROUP BY。

27. SELECT三大子句 • 举例： SELECT AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2500; • ORDER BY 将查询到的结果集按照某个指定的顺序排列显示出来。可以按照多个字段进行排序。默认为升序排列，如果需要降序排列则需在按照降序排列的字段名后加上DESC关键字。 ORDER BY子句应该为选择命令中的最后一个语句。

28. SELECT三大子句 • 举例： SELECT * FROM emp ORDER BY deptno,comm DESC;