重庆思庄Oracle、Redhat认证学习论坛

标题: OCP课程4：SQL之限制和排序数据 [打印本页]

作者: stonebox 时间: 2015-11-13 09:23
标题: OCP课程4：SQL之限制和排序数据

这章主要讲了3个功能：

使用where语句限制数据
使用order by语句排序数据
使用替换变量

1、使用where子句限制行

语法：

where子句必须在from子句之后。

例子：只查询部门编号为90的人员信息

SQL> select employee_id,last_name,job_id,department_id from employees where department_id=90;

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

----------- ------------------------- ---------- -------------

100 King AD_PRES 90

101 Kochhar AD_VP 90

102 De Haan AD_VP 90

如果要限制字符类型，日期类型，必须要用单引号引起来，而且单引号里面的字符区分大小写，日期要按照指定的格式书写，默认的的格式是DD-MON-RR格式。

例子：查询名字为Whalen的人员信息

SQL> select last_name,job_id,department_id from employees where last_name='Whalen';

LAST_NAME JOB_ID DEPARTMENT_ID

------------------------- ---------- -------------

Whalen AD_ASST 10

如果'Whalen'不加单引号，则会报错

SQL> select last_name,job_id,department_id from employees where last_name=Whalen;

select last_name,job_id,department_id from employees where last_name=Whalen

ERROR at line 1:

ORA-00904: "WHALEN": invalid identifier

如果'Whalen'全部小写，则会没有结果

SQL> select last_name,job_id,department_id from employees where last_name='whalen';

no rows selected

例子：查询入职日期为23-MAY-06的人员信息

SQL> select last_name,hire_date from employees where hire_date='23-MAY-06';

LAST_NAME HIRE_DATE

------------------------- ------------

Feeney 23-MAY-06

如果格式修改为中国习惯的YYYY-MM-DD，则会报错

SQL> select last_name,hire_date from employees where hire_date='2006-05-23';

select last_name,hire_date from employees where hire_date='2006-05-23'

ERROR at line 1:

ORA-01861: literal does not match format string

这里实际上做了一个隐式转换，将字符串转换成日期类型，格式不一致的话，就报错了，当然也可以用转换函数，我们下一章会讲。

我们刚才看到限制条件的时候，字符类型和日期类型要使用单引号引起来，字符类型要区分大小写，日期类型有严格的格式匹配的。

2、比较条件

在where子句中常用的比较操作符如下表：

例子：查找薪水小于等于3000的人员

SQL> select last_name,salary from employees where salary<=3000;

LAST_NAME SALARY

------------------------- ----------

OConnell 2600

例子：查找薪水在2500到3000之间，包含2500和 3000的人员

SQL> select last_name,salary from employees where salary between 2500 and 3500;

LAST_NAME SALARY

------------------------- ----------

OConnell 2600

例子：查找被管理ID为100、101及102管理者管理的人员信息

SQL> select employee_id,last_name,salary,manager_id from employees where manager_id in (100,101,201);

EMPLOYEE_ID LAST_NAME SALARY MANAGER_ID

----------- ------------------------- ---------- ----------

201 Hartstein 13000 100

Oracle中可以使用like条件进行通配符搜索，Oracle中的通配符有2种：

%：表示0或者多个字符
_：表示一个字符

例子：查找员工的姓中第一个字母为S的员工信息

SQL> select first_name from employees where first_name like 'S%';

FIRST_NAME

--------------------

Sundar

例子：查找员工的名字中第二个字母为o的员工信息

SQL> select last_name from employees where last_name like '_o%';

LAST_NAME

-------------------------

Colmenares

如果要搜索包含%或者_的字段值，则需要使用escape关键字进行转义

例子：查询工作ID中包含_的人员信息

SQL> select employee_id,job_id from employees where job_id like 'SH\_%' escape '\';

EMPLOYEE_ID JOB_ID

----------- ----------

180 SH_CLERK

测试是否为null必须使用is null或者is not null，不能使用等号

例子：查找管理ID为空的人员信息，也就是查找没有人管的人员信息（应该只有国王没有上级吧）

SQL> select last_name,manager_id from employees where manager_id is null;

LAST_NAME MANAGER_ID

------------------------- ----------

King

3、逻辑条件

在where子句中的逻辑操作符如下表：

例子：查找薪水大于等于10000而且工作编号包含MAN的人员信息

SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 and job_id like '%MAN%';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

201 Hartstein MK_MAN 13000

例子：查找薪水大于等于10000或者工作编号包含MAN的人员信息

SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 or job_id like '%MAN%';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

201 Hartstein MK_MAN 13000

例子：查找工作编号不是'IT_PROG', 'ST_CLERK', 'SA_REP'这三个的人员信息

SQL> select last_name,job_id from employees where job_id not in('IT_PROG','ST_CLERK','SA_REP');

LAST_NAME JOB_ID

------------------------- ----------

Baer PR_REP

4、运算优先级

Oracle中的运算优先级如下表：

可以使用括号来改变优先级。

例子：查找薪水大于15000而且工作编号为'AD_PRES'的人员信息以及工作编号为'SA_REP'的人员信息

SQL> select last_name,job_id,salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

King AD_PRES 24000

Tucker SA_REP 10000

例子：查找薪水大于15000而且工作编号为'SA_REP'或'AD_PRES'的人员信息

SQL> select last_name,job_id,salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000;

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

King AD_PRES 24000

5、使用order by子句进行排序

select语句的结果默认是没有排序的，我们可以使用order by子句对指定字段进行排序，order by子句位于select语句的最后，使用如下2个关键字进行排序

ASC：升序，不使用关键字的话默认为升序
DESC：降序

例子：查找人员信息，按照入职时间升序排序

SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date;

LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE

------------------------- ---------- ------------- ------------

De Haan AD_VP 90 13-JAN-01

Mavris HR_REP 40 07-JUN-02

例子：查找人员信息，按照入职时间降序排序

SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date desc;

LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE

------------------------- ---------- ------------- ------------

Banda SA_REP 80 21-APR-08

order by后面除了可以指定字段进行排序，还可以使用字段的别名以及字段的位置。

例子：使用字段的别名对人员年薪进行排序

SQL> select employee_id,last_name,salary*12 annsal from employees order by annsal;

EMPLOYEE_ID LAST_NAME ANNSAL

----------- ------------------------- ----------

132 Olson 25200

例子：使用字段的位置对人员年薪进行排序

SQL> select employee_id,last_name,salary*12 annsal from employees order by 3;

EMPLOYEE_ID LAST_NAME ANNSAL

----------- ------------------------- ----------

132 Olson 25200

order by子句后面可以使用多个字段进行排序，但是ASC，DESC关键字只对其前面的一个字段有效，如果字段后面没有加上关键字，默认是ASC升序。

例子：使用部门编号进行升序排列，部门编号相同的再使用薪水进行降序排列来显示人员信息

SQL> select last_name,department_id,salary from employees order by department_id,salary desc;

LAST_NAME DEPARTMENT_ID SALARY

------------------------- ------------- ----------

Whalen 10 4400

6、使用替换变量

通过使用替换变量，可以用1条SQL语句执行不同的查询，比如我刚开始需要查询人员编号为100的信息，后来又需要查询人员编号为200的信息，如果使用替换变量，就可以沿用前面的的语句。替换变量有2个符号，一个是单&符号，一个是双&&符号，双&&符号用在语句当中这个变量出现多次的情况。

例子：通过提示输入人员编号查询不同人员的信息

SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;

Enter value for employee_num: 100

old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num

new 1: select employee_id,last_name,salary,department_id from employees where employee_id=100

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

100 King 24000 90

如果变量的值是字符或者日期，则最好在语句中使用单引号讲变量引起来，就不需要在输入变量的时候输入单引号了。

例子：通过提示输入工作编号查询不同人员的信息

SQL> select last_name,department_id,salary*12 from employees where job_id='&job_title';

Enter value for job_title: IT_PROG

old 1: select last_name,department_id,salary*12 from employees where job_id='&job_title'

new 1: select last_name,department_id,salary*12 from employees where job_id='IT_PROG'

LAST_NAME DEPARTMENT_ID SALARY*12

------------------------- ------------- ----------

Hunold 60 108000

替换变量除了用在条件比较，还可以用于select子句中的字段，where子句中的整个条件，order by子句的排序字段，甚至select关键字后面的所有内容。

例子：通过提示输入需要的字段信息来进行查找、限制和排序

SQL> select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column;

Enter value for column_name: salary

Enter value for condition: salary>15000

Enter value for order_column: last_name

old 1: select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column

new 1: select employee_id,last_name,job_id,salary from employees where salary>15000 order by last_name

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

102 De Haan AD_VP 17000

如果语句里面的变量需要重复使用，可以使用&&符号。

例子：通过提示输入需要的字段进行选择和排序

SQL> select employee_id,last_name,job_id,&&column_name from employees order by &column_name;

Enter value for column_name: department_id

old 1: select employee_id,last_name,job_id,&&column_name from employees order by &column_name

new 1: select employee_id,last_name,job_id,department_id from employees order by department_id

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

----------- ------------------------- ---------- -------------

200 Whalen AD_ASST 10

如果将上面的语句再次执行，会发现不需要输入变量的值了，这是由于刚才输入变量的值时就已经在系统中定义了该变量的值为department_id，如果要更换为其他的值，就需要使用undefine关键字删除该变量，当然也可以使用define预先定义变量的值。

例子：先定义一个变量并赋值，再在select语句中使用，最后删除变量

SQL> define employee_num=200

SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;

old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num

new 1: select employee_id,last_name,salary,department_id from employees where employee_id=200

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

200 Whalen 4400 10

SQL> undefine employee_num;

前面进行变量替换的时候，会显示替换前和替换后的语句，可以设置sqlplus的verify环境变量进行设置是否显示。

例子：设置进行变量替换的时候不显示替换前和替换后的语句

SQL> set verify off

SQL> select employee_id,last_name,salary from employees where employee_id=&employee_num;

Enter value for employee_num: 200

EMPLOYEE_ID LAST_NAME SALARY

----------- ------------------------- ----------

200 Whalen 4400

SQL> show verify

verify OFF

sqlplus里面环境变量还有很多，可以所有show all进行显示，如果要设置，就使用set。

7、相关习题

（1）View the Exhibit and examine the description of the EMPLOYEES table. Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary: SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWE(SYSDATE, hire_date)/12 >= 5 When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly ?

A.Change  the  SELECT  clause  to  SELECT last_name, department_id,  salary*12 +50 "Annual Compensation".
B.Change  the  SELECT  clause  to  SELECT  last_name,  department_id,  salary+(50*12) "Annual Compensation".
C.Change  the  SELECT  clause  to  SELECT  last_name,  department_id,  (salary+50)*12  "Annual Compensation".
D.Change  the  SELECT  clause  to  SELECT  last_name,  department_id,  (salary*12)+50 "Annual Compensation".

答案：C

（2）View the Exhibit and examine the description of the EMPLOYEES table. Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement: SELECT first_name, salary, salary*12+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees? Which statement is true regarding the above query?

A.It would execute and give you the desired output.

B.It would not execute because the AS keyword is missing between the column name and the alias.

C.It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.

D.It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.

答案：A

（3）View the Exhibit and examine the description of the EMPLOYEES table. You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table where in the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000. Which SQL statement would get you the desired result?

A.SELECT employee_id, first_name FROM employees WHERE job_id like 'MAN%' OR job_id like 'CLERK%' AND department_id = 30 AND salary > 3000;

B.SELECT employee_id, first_name FROM employees WHERE job_id like '%MAN' OR job_id like '%CLERK' AND (department_id = 30 OR salary > 3000);

C.SELECT employee_id, first_name FROM employees WHERE (job_id like '%MAN' AND job_id like '%CLERK') AND department_id = 30 OR salary > 3000;

D.SELECT employee_id, first_name FROM employees WHERE (job_id like '%MAN' OR job_id like '%CLERK' ) AND department_id = 30 AND salary > 3000;

答案：D

（4）View the Exhibit and examine the details of the PRODUCT_INFORMATION table. You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: SELECT product_name, list_price FROM product_information WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088 ； Which statement is true regarding the execution of the query？

A.It would execute but the output would return no rows.
B.It would execute and the output would display the desired result.
C.It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
D.It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

答案：A

（5）View the Exhibit and examine the description of the PRODUCT_INFORMATION table. SELECT product_name, list_price, min_price, list_price-min_price Difference FROM product_information； Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE (Choose all that apply.)

A.ORDER BY 4
B.ORDER BY MIN_PRICE
C.ORDER BY DIFFERENCE
D.ORDER BY LIST_PRICE
E.ORDER BY LIST_PRICE - MIN_PRICE

答案：ACE

（6）View the Exhibit and examine the description of the EMPLOYEES table. You executed the following SQL statement: SELECT first_name, department_id, salary FROM employees ORDER BY department_id, first_name, salary desc；Which two statements are true regarding the output of the above query？(Choose two.)

A.The values in all the columns would be sorted in the descending order.
B.The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID column.
C.The values in the FIRST_NAME column would be sorted in ascending order for all the employees having the same value in the DEPARTMENT_ID column.
D.The values in the FIRST_NAME column would be sorted in the descending order for all the employees having the same value in the DEPARTMENT_ID column.
E.The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

答案：CE

（7）Evaluate the following SQL statement: SELECT 2 col1,'y' col2 FROM dual UNION SELECT 1,'x' FROM dual UNION SELECT 3,NULL FROM dual ORDER BY 2; Which statement is true regarding the output of the SQL statement？

A.It would execute and the order of the values in the first column would be 3, 2, 1.
B.It would execute and the order of the values in the first column would be 1, 2, 3.
C.It would not execute because the column alias name has not been used in the ORDER BY clause.
D.It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement

答案：B

备注：在对含有空值的列进行排序时，空值可以当作最大值

（8）View the Exhibit and examine the data in the PRODUCT_INFORMATION table. There are some products listed in the PRODUCT_INFORMATION table that have no value in the LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME for these products: SELECT product_name, list_price FROM product_information WHERE list_price = NULL；The query returns no rows. What changes would you make in the statement to get the desired result ？

A.Change the WHERE clause to WHERE list_price = 0
B.Change the WHERE clause to WHERE list_price = ' '.
C.Change the WHERE clause to WHERE list_price IS NULL.
D.In the WHERE clause, enclose NULL within single quotation marks.
E.In the WHERE clause, enclose NULL within double quotation marks.

答案：C

（9）View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.

You want to see the product names and the date of expiration of warranty for all the products,

if the product is purchased today. The products that have no warranty should be displayed at

the top and the products with maximum warranty period should be displayed at the bottom.

Which SQL statement would you execute to fulfill this requirement?

A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM

product_information ORDER BY SYSDATE-warranty_period;

B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM

product_information ORDER BY SYSDATE+warranty_period;

C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM

product_information ORDER BY SYSDATE;

D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM

product_information WHERE warranty_period >SYSDATE;

答案：B

作者: zhangyh 时间: 2015-11-17 09:44
赞一个，写的非常好！

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)