To indicate what values to retrieve, name them following the SELECT keyword. In the simplest case, you specify an expression or list of expressions. MySQL evaluates each expression and returns its value. Expressions may return numbers, strings, temporal values, or NULL. The following SELECT statement retrieves a value of each of those types: mysql> SELECT 2+2, REPEAT('x',5), DATE_ADD('2001-01-01',INTERVAL 7 DAY), 1/0; +-----+---------------+---------------------------------------+------+ | 2+2 | REPEAT('x',5) | DATE_ADD('2001-01-01',INTERVAL 7 DAY) | 1/0 | +-----+---------------+---------------------------------------+------+ | 4 | xxxxx | 2001-01-08 | NULL | +-----+---------------+---------------------------------------+------+ The first expression is a sum of numbers and returns the number 4. The second expression returns a string ('xxxxx') consisting of the character 'x' repeated five times. The third expression returns a date value. The fourth expression returns NULL because it involves a divide-by-zero condition. In general, if MySQL finds it impossible to evaluate an expression because it involves some exceptional condition, the result is NULL or an error occurs. SELECT can retrieve the values of expressions, as just shown, but it's more commonly used to retrieve columns from tables. To select information from a table, it's necessary to identify the table by adding a FROM table_name clause following the list of columns to retrieve. The names of the columns can be seen with DESCRIBE: mysql> DESCRIBE City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ To retrieve the contents of these columns, write the SELECT statement as follows: SELECT ID, Name, CountryCode, District, Population FROM City; MySQL returns a result set consisting of one row of output for each row in the table. (The term "result set" refers to the set of rows resulting from a SELECT statement.) If the table is empty, the result will be empty, too. An empty result set is perfectly legal. A syntactically valid SELECT that returns no rows is not considered erroneous. For a SELECT operation that retrieves every column from a table, the shortcut * can be used to specify the output columns. The * stands for "all columns in the table," so for the City table, the following statements are equivalent: SELECT ID, Name, CountryCode, District, Population FROM City; SELECT * FROM City; The * shorthand notation is clearly more convenient to type than a list of column names. However, you should understand when it is useful and when it isn't:
You should not issue a SELECT * query to find out the current left-to-right display order for the columns in a table and then assume that they will always be displayed in that same order for future queries. The left-to-right column order produced by SELECT * retrievals depends implicitly on the internal structure of the table, which is determined by the order of the columns in the table definition. However, the table's internal structure can be changed with ALTER TABLE, so a SELECT * statement might return different results before and after an ALTER TABLE statement. 9.2.1. Renaming Retrieved ColumnsOutput column names, by default, are the same as the column or expression selected. To rename a column, provide an alias following the column in the output list: mysql> SELECT 1 AS One, 4*3 'Four Times Three'; +-----+------------------+ | One | Four Times Three | +-----+------------------+ | 1 | 12 | +-----+------------------+ Columns aliases are used as follows:
9.2.2. Identifying the Database Containing a TableWhen you name a table in a SELECT statement, it's normally assumed to be a table in the default database. (This is true for other statements as well.) For example, if world is the default database, the following statement selects rows from the Country table in the world database: SELECT * FROM Country; If there's no default database, the statement results in an error because MySQL cannot tell where to find the table: mysql> SELECT * FROM Country; ERROR 1046 (3D000): No database selected To specify a database explicitly in the SELECT statement itself, qualify the table name. That is, precede the table name with the database name and a period: SELECT * FROM world.Country; The database name acts as a qualifier for the table name. It provides to the server a context for locating the table. Qualified table names are useful under several circumstances:
|