SQL Clauses


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





MySQL Visual QuickStart Guide Serie  .Covers My SQL 4 and 5
MySQL, Second Edition
ISBN: 0321375734
EAN: 2147483647
Year: 2006
Pages: 162
Authors: Larry Ullman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net