With most of your queries, normally you will want to use clauses to limit the information returned. This especially true for UPDATE and DELETE commands. The four common clauses are WHERE, GROUP BY, ORDER BY, and LIMIT (LIMIT is an extension to the SQL standard). Examples: SELECT * FROM tablename ORDER BY columnname DESC (Retrieves every column and every record from the table, sorted by columnname in descending order.) SELECT * FROM tablename ORDER BY column1name ASC, column2name DESC (Retrieves every column and every record from the table, sorted by column1name in ascending order, then by column2name in descending order.) SELECT * FROM tablename LIMIT 10 (Retrieves every column of the first ten records from the table.) SELECT * FROM tablename LIMIT 100, 50 (Retrieves every column of the 101st through 150th records from the table.) You can also, and frequently will, use multiple clauses in the same query. The clauses always go in this order: SELECT columns FROM tablename WHERE clause GROUP BY columnname ORDER BY columnname LIMIT x Clauses can be applied just to a column name, or they can be used in conjunction with parentheses and operators to create more elaborate conditionals. Table B.3 lists the operators and comparators you'll commonly use. Table B.3. These operators and comparators can be used in conjunction with parentheses to create different expressions in SQL queries.Operators and Comparators |
---|
Operator | Meaning |
---|
+ | Addition | - | Subtraction | * | Multiplication | / | Division | % | Modulus | < | Less than | > | Greater than | <= | Less than or equal to | >= | Greater than or equal to | = | Equals | != (also <>) | Not equal to | % | Multiple wildcard character (used with LIKE) | _ | Single wildcard character (underscore) (used with LIKE) | IS NOT NULL | Has a value (including an empty string or zero) | IS NULL | Does not have a value | BETWEEN | Within a range | NOT BETWEEN | Outside of a range | IN | Is one of a listed set of values | NOT IN | Is not one of a listed set of values | OR (also ||) | Where at least one of two conditionals is true | AND (also &&) | Where both conditionals are true | NOT (also !) | Where the condition is not true | OR NOT (also ^) | Where only one of two conditionals is true | LIKE | Where the value matches a string | NOT LIKE | Where the value does not match a string | REGEXP | Where the value matches a pattern | NOT REGEXP | Where the value does not match a pattern | MATCH AGAINST | Where the values matches against a series of words |
|