第四章 SQL 和 PL/SQL

1. 第四章 SQL和PL/SQL SQL PL/SQL

2. DDL数据定义语言：包括一些支持定义或建立数据库对象（如表、索引、序列或视图）的语句。Create、alter、dropDDL数据定义语言：包括一些支持定义或建立数据库对象（如表、索引、序列或视图）的语句。Create、alter、drop DML数据操纵语言：包括允许对数据库进行处理或操纵的语句。Select、insert、delete、update

3. 创建表 • 格式： create table 表名 (列名1 数据类型 【约束】， 列名2 数据类型 【约束】， 列名3 数据类型 【约束】 )；

4. 数据类型 varchar2(size) 存放可变长字符数据 char(size) 存放定长字符数据 number(l,d) 存放数值型数据，l代表总位数，d代表小数点后的位数 blob 二进制大对象 raw(size) 纯二进制数据 date 存放日期 long 存放可变长字符数据

5. Number number :允许在小数点左边或右边输入任何个数的数字 Number(5) : 允许在小数点左边不多于5位数，如果任何数字被插入到小数点的右边，该数字将被4舍5入。 Number(5,2) ：允许总数不超过5位数，其中2位在小数点右边，3位在左边。

6. 约束 null/not null primary key 要求进入该列的值是惟一的，且不为null foreign key unique 防止重复值进入该列，但允许为null check 限制属性列的输入值

7. 命名约束与未命名约束 在建立或修改表时，用户可明确给出约束的名称。否则约束名将由RDBMS内部命名。由用户命名的约束称为命名约束；RDBMS命名的约束由开发商决定，称未命名约束。 命名约束的格式：constraint 约束名 约束命名规定：表名－列名－后缀

8. 例1：用以下属性及设定建立表calling-card. 属性：公司名company-name、卡号card-number、初值starting-value、余额value-left、初始密码pin-number。 设定：属性company-name可具有多达25个字符，属性value-left及starting-value用元及分来度量。属性card-number属性定义为主码；将pin-number属性定义为惟一码。 请用命名约束来书写create table。

9. Create table calling-card (company-name varchar2(25), card-number varchar2(20) constraint calling-card-card-number-pk primary key, starting-value number(5,2), value-left number(5,2), pin-number char(12) constraint calling-card-pin-number-u unique );

10. 2.描述表结构 当一个表建立后，可能需要确定表的名称、数据类型及组成表的属性的一些约束。 使用：describe 表名； 或 desc 表名； 如：desc calling-card;

11. 3.插入行 • 使用insert into语句，一条语句只允许一次插入一行。 insert into 表名（列1，列2，……列N） values (值1，值2……值N); 如果写在values子句中的数据项的顺序与create table命令中表的属性顺序相同，则不用在insert into子句中写出列名。

12. 例1：将下列数据插入calling-card表 移动 1237096435 50.00 12.45 987234569871 联通 5497443544 100.00 11.37 433809835833 Insert into calling-card(company-name,card-number,starting-value,value-left,pin-number) values(‘移动’，‘1237096435’，15.00，11.37，‘987234569871’)； Insert into calling-card(company-name,card-number,starting-value,value-left,pin-number) values(‘联通 ’，‘5497443544’，100.00 ，11.37，‘433809835833’)； 注：所有的字符数据都放再单引号内。

13. 练习1： • 利用下面给出的信息，写出programmer表的create table指令。假设该表的属性满足下面所示的条件。

14. Drop table programmer cascade constraints; Create table programmer (empno varchar2(3) primary key, name varchar2(25) not null, hiredate date, project varchar2(3), language varchar2(15), taskno number(2) );

15. 用下列数据填充programmer表。

16. Insert into programmer (empno,name,hire- date,project,language,tashno) values(‘201’,’John’,’1-JAN-95’,’NPR’,’VB’,52); 注：在缺省设置中日期表示形式为：“DD-MM-YY”,其中DD是某月的任一天（1～31），MM是月份的前三个字母的缩写（JAN,FEB,DEC…）,YY是年的后两位数字。

17. 练习2： • 写一个脚本，为零售店Waves-R-US建立一个产品表product。该表属性如下所示。如果需要的话可使用命令约束，并证实建立的表是正确的。

18. Create table product ( id varchar2(5) constraint product-id-pk primary key, Name varchar2(25) constraint product-name-nn not null, Discount-precentage number(1), Price number(6,2) )； 为了证实所建立的表是正确的，需要使用describe product命令确定它的结构。 Describe product;

19. 4.Commit及rollback命令 对一个表进行insert、update或delete操作后，用户可使用commit语句使所做的变更永久性的记录到数据库中。 一般大部分数据库处理完一个DDL语句后发出一个隐式的commit语句。 假设对表的改变（插入、更新或删除）尚未提交，用户可通过rollback语句取消对表所做的全部中间变化。

20. Savepoint命令 用户希望及时回到某一个特定点（称为保留点），取消保留点之后对表的改变可使用savepoint命令。 savepoint 保留点名； rollback 保留点名 或 rollback to savepoint 保留点名

21. Savepoint A Savepoint B 在表中插入一行 在表中删除第一行 Rollback to savepoint A Savepoint C

22. 5.select语句 select 列1，列2……列N from 表1，……表N 【where 条件】 【order by 列1[asc|desc][列2[asc|desc]…]】; Where子句的比较运算符：＝，<>, <, <＝，> , >= 查询表中所有列信息：select * from 表名；

23. 例1：使用calling-card表查询移动公司发行的电话卡的卡号和初值。查询显示按卡号从小到大排列。例1：使用calling-card表查询移动公司发行的电话卡的卡号和初值。查询显示按卡号从小到大排列。 select card-number, starting-value from calling-card where company-name=‘移动’ order by card-number [asc]；

24. 练习1： • 使用employee表显示在会计部门工作的所有雇员的姓名及职务。 2.按雇员姓名的字母顺序显示上一个查询结果。

25. Select name, title from employee where dept=‘Accounting’; 2. Select name, title from employee where dept=‘Accounting’ order by name;

26. 表行的更新update表命令 • Update 表名 set 列1＝新值1，……列N＝新值N where 条件；

27. 例1：将卡号为1237096435的电话卡的公司名改为联通。例1：将卡号为1237096435的电话卡的公司名改为联通。 update calling-card set company-name=‘联通’ where card-number=‘1237096435’;

28. Delete命令 • Delete from 表名 where 条件；…………删除指定行 Delete 表名； …………删除表中所有数据 例1：删除calling-card表中联通的所有电话卡记录。 Delete from calling-card where company-name=‘联通’ ；

29. Drop table命令 • Drop table命令可从数据库中删除一个表及其中所有数据。 drop table 表名 【cascade constraints】; 如果想要删除的某个表具有参照完整性约束时，使用可选的cascade constraints子句。

30. 练习1： • 写一个脚本，创建一个emp表，该表的属性如下：

31. 1.根据emp表，列出所有雇员的姓名、ID及职务,要求不同职务按字母顺序排列，对相同职务的雇员，按其姓名的降序排列。 1.根据emp表，列出所有雇员的姓名、ID及职务,要求不同职务按字母顺序排列，对相同职务的雇员，按其姓名的降序排列。 2. 给在部门41工作的所有雇员提高工资200元。对结果加以验证。万一出错，确保所有的变化均能取消。如果操作有误，用户如何取消这些变化。

32. 1. Select name, id,title From emp Order by title ，name desc; 2. Savepoint before_update; update emp set salary=salary+200 where dept_id=’41’; 通过查询可对改变是否正确加以验证： select name salary from emp where dept_id=’41’; 如果更新不正确，用户可通过以下命令取消这些改变。 rollback to savepoint before_update;

33. SQL中关系运算符的执行 • 使用distinct取消重复的行 select [all|distinct] 列1，列2……列N from 表1，……表N 【where 条件】 【order by 列1[asc|desc][列2[asc|desc]…]】; 例：在emp表中，有多少不同的职务？ select distinct title from emp;

34. 连接运算符的执行 连接（join）运算允许我们将来自两个或多个表的数据形成一个单一表。 主要的连接类型： • 等值连接：两表通过共同的等值列而连接 • 自连接：一个表与其本身的连接 • 外连接：两个表之间的连接，决定一个表的所有行在另一表中没有匹配的元组。

35. 等值连接 select 表1.列1，表1.列2…,表2.列1，…表2.列N from 表1，表2 where 表1.列名＝表2.列名； 如果在两个表中没有相同的列，则没有必要在列名前加表名。

36. Emp表：

37. Dept 表：

38. 例1：查询所有雇员的姓名及所在部门名称。 Select emp.name,dept.name From emp,dept Where emp.dept_id=dept.id； 使用表别名： select A.name , B.name from emp A,dept B where A.dept_id=B.id；

39. 自连接 • 自连接是一个表与其自身的连接 例1：显示每一个雇员的名字及他或她的管理者的名字。 select E.name as “employee”, M.name as “manager” from emp E,emp M where M.id=E.manager_Id

40. 外连接 • 假设有两个表的连接，有时我们想知道不满足其中一个表指定条件的另一个表的行。例如，我们想知道还没有管理者的雇员。 select 表1.列1，表1.列2…,表2.列1，…表2.列N from 表1，表2 where 表1.列名（＋）＝表2.列名； 用于计算在表1中所有与表2不匹配的行的外连接 select 表1.列1，表1.列2…,表2.列1，…表2.列N from 表1，表2 where 表1.列名＝表2.列名（＋）； 用于计算在表2中所有与表1不匹配的行的外连接

41. 例：列出无管理者的所有雇员。 select E.name as “employee”, M.name as “manager” from emp E,emp M where M.id（＋）=E.manager_Id

42. 练习1： 1.在dept表和region表中查询所有部门及它们所在的区域名。 2.显示所有销售代表及他们的客户的姓名。 3.显示目前还没有指定销售代表的客户。

43. Customer表：

44. Emp表：

45. Select D.name as “department” ,R.name as “region” from dept D, region R where D.region_id = R.id ； 2. Select E.name as “sales reps”, C.name as “customers” from emp E, customer C where C.sales_rep_id=E.id ； 3. Select C.name as “customers”， E.name as “sales reps” from emp E, customer C where C.sales_rep_id=E.id(+) ；

46. 建立外码 create table 表名 (列名1 数据类型 【constraint 约束名】 references 参照表名（列名）， 列名2 数据类型 【约束】， 列名3 数据类型 【约束】 )；

47. Emp表：

48. Dept 表： 例1：根据上面的信息，建立emp表。定义dept_Id为FK,且该FK参照另一个dept表的id。

49. Create table emp( id varchar2(3) , name varchar2(20) , userid varchar2(8)， start_date date, manager_id varchar2(3), title varchar2(25), dept_id varchar2(3) references dept(id), salary number(11,2) commision number(4,2)) ; 在加上外码约束时，必须确定dept表中属性id已经被定义为PK。

50. 在一个已存在的表中定义外码 • 使用alter table命令： alter table 表名 add 【constraint 约束名】 foreign key(列名) references 参照表名（列名）；