SQL-谓词与量词(国外英语资料).docVIP

SQL-谓词与量词(国外英语资料) = = = = = = = = 1, predicate, Predicates allow you to construct conditions so that only those lines that satisfy these conditions are processed. Basic predicates in Select the row discussion. This section discusses IN, BETWEEN, LIKE, EXISTS, and quantitative predicates. Use IN predicates Use the IN predicate to compare a value against several other values. Such as: SELECT NAME FROM STAFF WHERE DEPT IN (20, 15) This example is the equivalent of: SELECT NAME FROM STAFF WHERE DEPT = 20 OR DEPT = 15 When the subquery returns a set of values, the IN and NOT IN operator can be used. For example, the following query lists the names of the employees in the project MA2100 and OP2012: SELECT LASTNAME FROM EMPLOYEE WHERE EMPNO IN SELECT RESPEMP FROM PROJECT WHERE PROJNO = MA2100 OR PROJNO = OP2012) Compute a subquery and plug the result list directly into the outer query. For example, the subquery above selects the employee number 10 and 330, and the external layer query is calculated as if the WHERE clause is as follows: WHERE EMPNO IN (10, 330) The list of values returned by a subquery can contain zero, one or more values. Use the BETWEEN predicate Use the BETWEEN predicate to compare a value with the value within a range. The values on both sides are included, and consider the two expressions for comparison in the BETWEEN predicate. The next example looks for the names of employees who earn between $10,000 and $20,000: SELECT LASTNAME FROM EMPLOYEE WHERE SALARY is BETWEEN 10,000 AND 20,000 This is the equivalent of: SELECT LASTNAME FROM EMPLOYEE WHERE SALARY = 10,000 AND SALARY = 20,000 The next example looks for employees with less than $10, 000 or more than $20,000. SELECT LASTNAME FROM EMPLOYEE WHERE SALARY is NOT BETWEEN 10,000 AND 20,000 Use the LIKE predicate Search for strings with certain patterns using the LIKE predicate. Specify the schema by the percent and the underscore. The underscore character (_) represents any single character. A percen