Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

SELECT column name FROM table name WHERE conditions

The column name can be one or more columns that are in a table in the FROM clause. You cannot

select a column name that is not available in the FROM clause. For example, if you wanted to select the

Last_Name field of the Customers table, but you did not have the Customers table defined in the

FROM clause, MySQL would generate an error. As mentioned before, you can use aliasing as a

shortcut. You only have to mention the table name if there is an ambiguity in the field names. This

means that if one table has a field named Order_ID and another table has a field named Order_ID,

you must identify which table/field name pair you want to use. If the field name is unique to all the tables

in your table parameter list, you do not have to specify a table.

The order in which you specify the column names is the order in which they will be displayed. If you

choose First_Name, Last_Name, the data will be displayed in that order. If you change them around

to Last_Name, First_Name, the output will match the order specified.

The SELECT statement can also be used to show outcomes of equations. For example, the following

statement

SELECT 1 + 1

would produce the following output:

2

The following statement is also legal:
SELECT concat(First_Name, " ",Last_Name) AS Name FROM Customers
This statement would return a column named Name with the results of the concatenation function. The
concat() function is an intrinsic function of MySQL and will be covered in more detail in tomorrow's
lesson. So, if you wanted a resultset that contained the person's name and address, you could use the
following SQL statement:
Note The concat() function combines two strings into one. Intrinsic functions will be
covered on Day 10, "Letting MySQL Do the Work—Intrinsic Functions."
SELECT concat(First_Name, " ", Last_Name)
AS Name, Address, City, State
FROM Customers;
The resulting output would look like Table 9.2.
Table 9.2 Results of Concatenation

Name Address City State

Glen Brazil 133

Foxview

Ln.

Hoptown KY

Roxanne Tor 123

Center

Ave.

Glen

Burnie

MD

Notice that the columns have the names that you assigned them in the AS clause. This technique is

highly useful, especially when using intrinsic functions. Another example is the MAX() function. The MAX

function returns the maximum value of a numeric type column. This could be used instead of the

auto_increment modifier. For example, to get the current maximum value of a numeric column, you

could use the following statement:

SELECT MAX(Customer_ID) as Current_Num FROM Customers

The resultset would look like the following:

Current_Num

78

In a MySQL SELECT statement, you can also set the priority in which the MySQL engine serves this

query. If MySQL receives a request for an INSERT and a request for a SELECT at the same time,

MySQL will process the INSERT before the SELECT. To force MySQL to perform the SELECT first, you

can set the priority of the query by doing the following:

SELECT HIGH_PRIORITY Customer_ID FROM Customers

The HIGH_PRIORITY argument should only be used on small queries.