第1章 简单查询语句 SQL（Structured Query Language）是标准的关系数据库（Relational Database Management Systems）操作语言。SQL语言包括查询语言（Data Retrieval）、数据操作语言（Data Manipulation Language，DML）、数据定义语言（Data Definition Language，DDL）、数据控制语言（Data Control Language，DCL）和事务控制（Transaction Control）。SQL是一种非过程化的第四代高级语言，它的语法与英语非常相似，因此它是一种很容易学习的计算机语言。对初学者几乎没有任何要求，换句话说，初学者可以没有任何计算机背景。 本书将详细地介绍以上各个部分的内容。为了讲解方便，在本书中将利用一个虚构的跨国公司——Sun & Moon Limited Corporation（日月神有限公司）来介绍SQL语言在实际商业环境中的应用。以下是日月神有限公司的简介。 日月神有限公司是一个总部设立在一个白云缭绕的太平洋岛国上的大型跨国公司。该公司的总部原来设在某个西方国家的首都。日月神有限公司的创始人，也是该公司的老板，是一个难得的企业家，公司在他的领导下生意蒸蒸日上，已经成为一个实力雄厚的大型跨国公司，其经营的范围涵盖了许多领域，该公司的触角已遍及五大洲，并在许多国家设立了子公司。 该公司在很早就开始利用Oracle数据库管理系统建立了信息系统，用来减轻公司管理人员的日常工作负担和帮助公司的经理们作出正确的决策。但是日月神的老板并不认为信息技术（IT）能决定公司的命运。他一直认为真正决定公司成败的是人不是技术。 正是基于“真正决定公司成败的是人不是技术”这一理念，日月神公司在它的信息管理系统中最先实现的是人事管理部分。这部分包含了3个最重要的表，分别为员工表（emp）、部门表（dept）和工资级别表（salgrade）。其中： ? 员工表（emp）包含了日月神公司中所有员工的基本信息，如姓名和工资等。 ? 部门表（dept）包含了日月神公司中所有部门的基本信息，如部门名和所在地址等。 ? 工资级别表（salgrade）包含了每一工资级别的工资下限和上限。 本书将主要利用这3个表讲解SQL语言和SQL*Plus命令。虽然日月神公司有几十万甚至上百万名员工和几百个或上千个部门，但为了讲解方便，我们只使用这些表中非常小的一部分。 在本书中有如下的约定：如果没有明确说明，书中所介绍的一切都发生在日月神公司；书中的老板、老总、总裁等就是指日月神的老板；书中的您是指日月神公司的数据库管理员（DBA）或数据库操作员。现在我们就从最简单的查询语句开始SQL语言学习的愉快旅程。 1.1 最简单的查询语句 在查询语句中实际上只有SELECT和FROM子句是必需的，也是最简单的查询语句。如果您的经理想知道公司中所有员工的信息，可以使用例1-1的查询语句来得到他所需要的信息。假设公司所有员工的信息都保存在一个叫emp（员工）的表中。 例1-1 SQL> SELECT * 2 FROM emp; 例1-1结果 EMPNO ENAME JOB MGR HIREDATE ---------- -------------- ------------------------------- --------- SAL COMM DEPTNO --------- --------- ------------ 7369 SMITH CLERK 7902 17-12? –80 800 20 7499 ALLEN SALESMAN 7698 20-2? -81 1600 300 30 7521 WARD SALESMAN 7698 22-2? -81 1250 500 30 … ? 注意： 在本书中采用如下的约定： ? 没有阴影的内容为输入的SQL语句或SQL*Plus命令。 ? 阴影中的内容为系统显示（输出）的结果。 这里的“*”号表示所有的列，它与在SELECT之后列出所有的列名是一样的。查询语句以分号（;）结束。emp（employee）是一个员工表。显示结果后的（…）表示在其后还有显示被省略了。 Oracle有许多UNIX和C的影子，在对表和列命名时大量地使用缩写形式，如salary（工资）——sal。可能您已经注意到了HIREDATE一列的显示有些奇怪，这是因为emp表中存的是英文数据，而我使用的是中文操作系统。以后会再介绍转换的方法。 1.2 在查询语句中如何选择特定的列 您也许觉得上面的显示有些乱。在许多情况下，我们只想知道若干个列的信息，使用SELECT语句很容易做到。在现实生活当中也是这样，如您到超市去买东西，当然不会买下超市中的所有东西。例如： 买 羊肉，兔肉 从 大灰狼超市； 又如公司的会计在每次发工资时，她可能需要确定每个员工的工号（empno）、名字（ename）和工资（sal）。可以使用例1-2的查询语句来实现她的这一要求。 例1-2 SQL> SELECT empno, ename, sal 2 FROM emp; 例1-2结果 EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 已选择14行。 在查询语句中选择指定的列就是关系数据库中所称的投影（Project）。可以在SELECT之后选择任意列，列与列之间以逗号隔开，而且可以根据需要来指定列的显示次序，如 例1-3所示。 例1-3 SQL> SELECT sal,ename,empno 2 FROM emp; 例1-3结果 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 已选择14行。 其实，例1-3的显示结果与例1-2完全相同，只是列的显示次序不同而已。 1.3 如何书写查询语句 到目前为止我们只学习了两个关键字（Keyword）SELECT和FROM。Oracle规定组成SQL语句的关键字是不能缩写的，也就是您不能在查询语句中将SELECT写成SEL或SELEC等，不能把FROM写成FRO或FR等，也不能把一个关键字分开书写，即关键字必须原样书写。 可以用大写、小写或大小写混写来书写SQL语句，如例1-4所示。 例1-4 SQL> select Sal, EName, EMPNO 2 From Emp; 例1-4结果 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 已选择14行。 可以看出，例1-4显示的结果与例1-3完全相同。 为了增加可读性，本书所有的关键字尽量采用大写英文字母，其他的部分用小写，这也是Oracle公司推荐的。 既可以像例1-3那样把一个SQL语句放在多行上，也可以把一个SQL语句写在一行上，如例1-5所示。 例1-5 SQL> select sal, ename, empno from emp; 例1-5结果 SAL ENAME EMPNO ---------- -------------------- ------ 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 已选择14行。 可以看出，例1-5显示的结果也与例1-3完全相同，只是易读性不如例1-3。 因此，为了增加可读性，应该把SQL语句中的每一个子句写在一行上，且最好以缩进法来书写例1-6的SQL语句。 ? 注意： 一个完整的SQL命令叫语句（statement），每一个关键字和后面跟着的选项叫子句（clause），例如，“SELECT * FROM emp;”叫语句，而“SELECT *”叫子句，“FROM emp”也叫子句。 例1-6 SQL> SELECT empno, ename, sal, 2 deptno, job 3 FROM emp; 例1-6结果 EMPNO ENAME SAL DEPTNO JOB ---------- -------------------- ---------- ---------- --------- 7369 SMITH 800 20 CLERK 7499 ALLEN 1600 30 SALESMAN 7521 WARD 1250 30 SALESMAN 7566 JONES 2975 20 MANAGER 7654 MARTIN 1250 30 SALESMAN 7698 BLAKE 2850 30 MANAGER 7782 CLARK 2450 10 MANAGER 7788 SCOTT 3000 20 ANALYST 7839 KING 5000 10 PRESIDENT 7844 TURNER 1500 30 SALESMAN 7876 ADAMS 1100 20 CLERK 已选择14行。 从例1-6的语句中可以很容易地看出，第1行和第2行为SELECT子句，第3行为FROM子句。例1-6的查询结果显示了emp表中每个员工的工号（empno）、名字（ename）、工资（sal）、部门号（deptno）和职位（job）。 1.4 列标题和数据的默认显示格式 在本章开始时，已经看到了由于字符集的不同使日期型的显示有些问题，为了解决这个问题可以使用例1-7的SQL语句。 例1-7 SQL> alter session 2 set NLS_DATE_LANGUAGE = 'AMERICAN'; 例1-7结果 会话已更改。 为了使显示更加清晰，可以使用例1-8和例1-9的SQL*Plus格式化语句。 例1-8 SQL> col hiredate for a15 例1-9 SQL> COL ENAME FOR A8 ? 注意： 如果对以上的SQL和SQL*Plus命令不太理解，请不要担心，以后还要详细介绍。 SQL*Plus默认的列标题结果显示是： ? 字符和日期型数据为左对齐。 ? 而数字型数据为右对齐。 可以使用例1-10的例子来验证以上的结论。 例1-10 SQL> SELECT empno, ename, sal, 2 hiredate, job 3 FROM emp; 例1-10结果 EMPNO ENAME SAL HIREDATE JOB ---------- -------- ---------- --------------- --------- 7369 SMITH 800 17-DEC-80 CLERK 7499 ALLEN 1600 20-FEB-81 SALESMAN 7521 WARD 1250 22-FEB-81 SALESMAN 7566 JONES 2975 02-APR-81 MANAGER 7654 MARTIN 1250 28-SEP-81 SALESMAN 7698 BLAKE 2850 01-MAY-81 MANAGER 7782 CLARK 2450 09-JUN-81 MANAGER 7788 SCOTT 3000 19-APR-87 ANALYST 7839 KING 5000 17-NOV-81 PRESIDENT 7844 TURNER 1500 08-SEP-81 SALESMAN 7876 ADAMS 1100 23-MAY-87 CLERK 已选择14行。 由于在运行例1-10的查询语句之前使用了例1-7~例1-9的命令，所以例1-10显示输出的HIREDATE一列已经很整齐了。 1.5 如何在SQL语句中使用算术表达式 可以在SQL语句中使用表达式。在表达式中可以使用“+”、“-”、“*”、“/”4种运算符，它们分别代表加、减、乘、除。 设想一下，在某一天有社会团体和工会团体参观你们公司，您的老板为了树立公司的光辉形象，他要求您把午餐和茶水的费用加到员工的工资里并打印一张工资清单。于是您大概算了一下，其费用约合500元/月。之后您用例1-11的查询得到了老板所要的员工的工资清单。 例1-11 SQL> SELECT empno, ename, sal, 500+sal 2 FROM emp; 例1-11结果 EMPNO ENAME SAL 500+SAL ---------- -------- ---------- ---------- 7369 SMITH 800 1300 7499 ALLEN 1600 2100 7521 WARD 1250 1750 7566 JONES 2975 3475 7654 MARTIN 1250 1750 7698 BLAKE 2850 3350 7782 CLARK 2450 2950 7788 SCOTT 3000 3500 7839 KING 5000 5500 7844 TURNER 1500 2000 7876 ADAMS 1100 1600 已选择14行。 当您把例1-11显示的结果拿给老板时，老板认为应该用年薪并且不能列出原工资。因此，可以将例1-11的查询语句做一些修改并产生例1-12的SQL语句。 例1-12 SQL> SELECT empno, ename, 500+sal*12 2 FROM emp; 例1-12结果 EMPNO ENAME 500+SAL*12 ---------- -------- ----------- 7369 SMITH 10100 7499 ALLEN 19700 7521 WARD 15500 7566 JONES 36200 7654 MARTIN 15500 7698 BLAKE 34700 7782 CLARK 29900 7788 SCOTT 36500 7839 KING 60500 7844 TURNER 18500 7876 ADAMS 13700 已选择14行。 看到例1-12显示的结果让您大吃一惊，很显然这并不是您的老板所要的结果。其原因是运算符的优先级在作怪。 算术运算符的优先级是： ? 先乘除后加减。 ? 在表达式中同一优先级的运算符计算次序是从左到右。 ? 如果使用了括号，括号中的运算优先。 ? 如果有多重括号嵌套，内层括号中的运算优先。 因此您不得不再次重新书写例1-13的SQL语句。 例1-13 SQL> SELECT empno, ename, (500+sal)*12 2 FROM emp; 例1-13结果 EMPNO ENAME (500+SAL)*12 ---------- -------- ------------ 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 已选择14行。 毫无疑问，例1-13显示的结果就是老板所要的员工的工资清单。虽然您得到了正确的结果，但显示的列标题确实是令人费解，特别是对您的老板和那些团体的头头们，因为他们对Oracle数据库可能是一窍不通。 1.6 如何在SQL语句中使用列的别名 为了让老板明白，您不辞辛苦地再次修改了例1-13中的查询语句，这次您使用了列的别名，写出了例1-14的SQL语句。 例1-14 SQL> SELECT empno AS "Employee Number", 2 ename name, (500+sal)*12 "Annual Salary" 3 FROM emp; 例1-14结果 Employee Number NAME Annual Salary --------------- -------------------- ------------- 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 … 已选择14行。 上面例1-14显示的结果正是您的老板所要的。 Oracle在为列命名时大量使用缩写方式，这些以缩写方式表示列名的好处之一是它减少了输入量，但对非计算机专业的人员来说，这些缩写方式的列名如同天书一般。有没有一种方法既能满足Oracle专业人员习惯于使用缩写的癖好，又使非计算机专业人员看到显示的结果时一目了然呢？为列起一个别名就解决了这一难题（就像在上面的例子中所做的 那样）。 给一列起一个别名的方法很简单。您只需在列名和别名之间放上AS或空格就可以了。虽然从语句的易读性角度来说应该使用AS关键字，但是使用这一关键字需要多输入两个字符，所以您会发现许多的Oracle专业人员很少使用AS这一关键字。 别名对处理表达式表示的列非常有用。您可能已经注意到了，当别名没有被双引号括起来时，其显示结果为大写。如果别名中包含了特殊字符，或想让别名原样显示，您就要使用双引号把别名括起来。 1.7 连接运算符 您的老板对您为他做的员工工资清单很满意，但他想使显示的结果是一个完整的英文句子，而且表达式的列标为“Employee’s Salary”。这样，那些团体的头头们更容易理解。为此，您不得不再一次修改查询语句，写下了例1-15的SQL语句。 例1-15 SQL> SELECT ename ||' annual salary is '||(500+sal)*12 "Employee's Salary" 2 FROM emp; 例1-15结果 Employee's Salary ----------------------------- SMITH annual salary is 15600 ALLEN annual salary is 25200 WARD annual salary is 21000 JONES annual salary is 41700 MARTIN annual salary is 21000 BLAKE annual salary is 40200 CLARK annual salary is 35400 SCOTT annual salary is 42000 KING annual salary is 66000 TURNER annual salary is 24000 ADAMS annual salary is 19200 … 当您把这份报告呈给老板时，老板灿烂的微笑已经告诉您，这正是他所期望的报告。 如果您使用的是中文系统，也可以使用例1-16的带有中文的查询语句。 例1-16 SQL> SELECT ename ||' 年薪为：'||(500+sal)*12 "员工的年薪" 2 FROM emp; 例1-16结果 员工的年薪 -------------------- SMITH 年薪为：15600 ALLEN 年薪为：25200 WARD 年薪为：21000 JONES 年薪为：41700 MARTIN 年薪为：21000 BLAKE 年薪为：40200 CLARK 年薪为：35400 SCOTT 年薪为：42000 KING 年薪为：66000 TURNER 年薪为：24000 ADAMS 年薪为：19200 JAMES 年薪为：17400 FORD 年薪为：42000 MILLER 年薪为：21600 已选择14行。 在这一查询语句中使用了文本字符串（literal）和连接运算符。 文本字符串是包含在SELECT子句中的字符、数字或表达式，而不是任何的列名或列的别名。如果文本字符是日期型和字符型，就必须将它们用单引号括起来。每个字符串在每行输出结果中都输出一次。 连接运算符由两个竖线（||）表示，它用于把一个或多个列或字符串连接在一起。 1.8 DISTINCT运算符 设想您刚刚被公司聘为Oracle数据库管理员（DBA），想知道您的公司究竟有多少个部门，可能发出例1-17的查询语句。 例1-17 SQL> select deptno 2 from emp; 例1-17结果 DEPTNO ----------- 20 30 30 20 30 30 10 20 10 30 … 已选择14行。 Oracle显示了全部的记录，这是Oracle默认的显示方式。如果您所在的公司是一个大型跨国企业，它雇佣了一百多万名员工，可以想象这一查询会造成什么样的后果。 可以用DISTINCT帮助您去掉重复的行。请看例1-18的查询。 例1-18 SQL> SELECT DISTINCT deptno 2 FROM emp; 例 1-18结果 DEPTNO ---------- 10 20 30 很显然，例1-18显示的结果要比例1-17的结果清楚多了。 ? 注意： 当查询比较大的表时应尽可能地避免使用DISTINCT，因为Oracle系统是通过排序的方式来完成DISTINCT这一功能的，所以它会造成Oracle系统的效率降低。通常您可以使用不同的查询语句来完成同样的工作，如可以使用例1-19的查询语句来得到所需的部门的信息。 例1-19 SQL> SELECT deptno 2 FROM dept; 例1-19结果 DEPTNO ---------- 10 20 30 40 例1-19的查询与前面的查询一样得到了所有的部门号，但对Oracle系统没什么冲击，因为部门号（deptno）在dept表中本身就是唯一的。 DISTINCT可以作用于多列，此时显示的结果为每一种列组合只显示一行，如例1-20。 例1-20 SQL> SELECT DISTINCT deptno, job 2 FROM emp; 例1-20结果 DEPTNO JOB ---------- --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN 已选择9行。 到此为止，我们已经讨论了最基本的查询语句。下面将给出基本查询语句的格式。 1.9 基本查询语句的格式 在这一章中我们只学习了一个基本的查询语句，它只包含了两个子句SELECT和FROM，其格式如下： SELECT *|{[DISTINCT] 列表,…} FROM 表名; 其中，列表的格式为： 列名|表达式 [别名] “SELECT * FROM 表名;”为从表名所指定的表中选择所有的列；“SELECT DISTINCT 列表,… FROM 表名;”为从表名所指定的表中选择列表所规定的列，但不显示重复的数据行（记录）。 1.10 应该掌握的内容 在学习下一章之前，请检查一下您是否已经掌握了以下的内容： ? 在查询语句中哪两个子句是必需的？ ? 什么是投影操作？ ? 如何在查询语句中选择特定的列及列的顺序？ ? 书写查询语句的约定。 ? 什么是语句？ ? 什么是子句？ ? 列标题的默认显示格式。 ? 字符和日期型数据的默认显示格式。 ? 数字型数据的默认显示格式。 ? SQL语句中的算术表达式及它们的优先级。 ? SQL语句中列的别名及其使用。 ? 如何使用文本字符串（literal）和连接运算符（||）？ ? 如何使用DISTINCT关键字？使用它可能产生的问题有哪些？