Offshore Software Outsourcing Company, offshore IT outsourcing services, offshore software development Russia

About ALTOROS: ALTOROS Systems is headquartered in Tampa, Florida and maintains an office near Boston, Massachusetts and technology development center in Belarus and Russia. ALTOROS specializes on providing value-added e-commerce and web-based software development and offshore software outsourcing services to emerging enterprises helping them successfully plan and implement business initiatives.

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows]
      [PROCEDURE procedure_name]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT is used to retrieve rows selected from one or more tables. select_expression indicates the columns you want to retrieve. SELECT may also be used to retrieve rows computed without reference to any table. For example:

mysql> SELECT 1 + 1;
-> 2

All keywords used must be given in exactly the order shown above. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.

· A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses. For example:

mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

· It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See Problems with alias.

· The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see JOIN. For each table specified, you may optionally specify an alias.

table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]

As of MySQL Version 3.23.12, you can give hints about which index MySQL should use when retrieving information from a table. This is useful if EXPLAIN shows that MySQL is using the wrong index. By specifying USE INDEX (key_list), you can tell MySQL to use only one of the specified indexes to find rows in the table. The alternative syntax IGNORE INDEX (key_list) can be used to tell MySQL to not use some particular index. USE/IGNORE KEY are synonyms for USE/IGNORE INDEX.

· You can refer to a column as col_name, tbl_name.col_name, or db_name.tbl_name.col_name. You need not specify a tbl_name or db_name.tbl_name prefix for a column reference in a SELECT statement unless the reference would be ambiguous. See Legal names, for examples of ambiguity that require the more explicit column reference forms.

· A table reference may be aliased using tbl_name [AS] alias_name:

mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
-> WHERE t1.name = t2.name;
mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
-> WHERE t1.name = t2.name;

· Columns selected for output may be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions begin with 1:

mysql> SELECT college, region, seed FROM tournament
    ->        ORDER BY region, seed;
mysql> SELECT college, region AS r, seed AS s FROM tournament
    ->        ORDER BY r, s;
mysql> SELECT college, region, seed FROM tournament
    ->        ORDER BY 2, 3;

To sort in reverse order, add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this may be specified explicitly using the ASC keyword.

· You can in the WHERE clause use any of the functions that MySQL support. See Functions.

· The HAVING clause can refer to any column or alias named in the select_expression. It is applied last, just before items are sent to the client, with no optimisation. Don't use HAVING for items that should be in the WHERE clause. For example, do not write this:

mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead:

mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

In MySQL Version 3.22.5 or later, you can also write queries like this:

mysql> SELECT user,MAX(salary) FROM users
-> GROUP BY user HAVING MAX(salary)>10;

In older MySQL versions, you can write this instead:

mysql> SELECT user,MAX(salary) AS sum FROM users
-> group by user HAVING sum>10;

· The options DISTINCT, DISTINCTROW and ALL specify whether duplicate rows should be returned. The default is (ALL), all matching rows are returned. DISTINCT and DISTINCTROW are synonyms and specify that duplicate rows in the result set should be removed.

· All options beginning with SQL_, STRAIGHT_JOIN, and HIGH_PRIORITY are MySQL extensions to ANSI SQL.

· HIGH_PRIORITY will give the SELECT higher priority than a statement that updates a table. You should only use this for queries that are very fast and must be done at once. A SELECT HIGH_PRIORITY query will run if the table is locked for read even if there is an update statement that is waiting for the table to be free.

· SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimiser that the result set will have many rows. In this case, MySQL will directly use disk-based temporary tables if needed. MySQL will also, in this case, prefer sorting to doing a temporary table with a key on the GROUP BY elements.

· SQL_BUFFER_RESULT will force the result to be put into a temporary table. This will help MySQL free the table locks early and will help in cases where it takes a long time to send the result set to the client.

· SQL_SMALL_RESULT, a MySQL-specific option, can be used with GROUP BY or DISTINCT to tell the optimiser that the result set will be small. In this case, MySQL will use fast temporary tables to store the resulting table instead of using sorting. In MySQL Version 3.23 this shouldn't normally be needed.

· SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result, disregarding any LIMIT clause. The number of rows can be obtained with SELECT FOUND_ROWS(). See Miscellaneous functions.

· SQL_CACHE tells MySQL to store the query result in the query cache if you are using QUERY_CACHE_TYPE=2 (DEMAND). See Query Cache.

· SQL_NO_CACHE tells MySQL to not allow the query result to be stored in the query cache. See Query Cache.

· If you use GROUP BY, the output rows will be sorted according to the GROUP BY as if you would have had an ORDER BY over all the fields in the GROUP BY. MySQL has extended the GROUP BY so that you can also specify ASC and DESC to GROUP BY:

SELECT a,COUNT(b) FROM test_table GROUP BY a DESC

· MySQL has extended the use of GROUP BY to allow you to select fields which are not mentioned in the GROUP BY clause. If you are not getting the results you expect from your query, please read the GROUP BY description. See Group by functions.

· STRAIGHT_JOIN forces the optimiser to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimiser joins the tables in non-optimal order. See EXPLAIN.

· The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants.

If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset upto the end of the result set, you can use -1 for the second parameter:

mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.

If one argument is given, it indicates the maximum number of rows to return:

mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT n is equivalent to LIMIT 0,n.

· The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host and cannot already exist (among other things, this prevents database tables and files such as /etc/passwd from being destroyed). You must have the FILE privilege on the server host to use this form of SELECT.

SELECT ... INTO OUTFILE is mainly intended to let you very quickly dump a table on the server machine. If you want to create the resulting file on some other host than the server host you can't use SELECT ... INTO OUTFILE. In this case you should instead use some client program like mysqldump --tab or mysql -e "SELECT ..." > outfile to generate the file.

SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See LOAD DATA.

In the resulting text file, only the following characters are escaped by the ESCAPED BY character:

· The ESCAPED BY character

· The first character in FIELDS TERMINATED BY

· The first character in LINES TERMINATED BY

Additionally, ASCII 0 is converted to ESCAPED BY followed by 0 (ASCII 48).

The reason for the above is that you must escape any FIELDS TERMINATED BY, ESCAPED BY, or LINES TERMINATED BY characters to reliably be able to read the file back. ASCII 0 is escaped to make it easier to view with some pagers.

As the resulting file doesn't have to conform to the SQL syntax, nothing else need be escaped.

Here follows an example of getting a file in the format used by many old programs.

SELECT a,b,a+b INTO OUTFILE "/tmp/result.text"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM test_table;

· If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL will only write one row into the file, without any column or line terminations and without any escaping. This is useful if you want to store a blob in a file.

· Note that any file created by INTO OUTFILE and INTO DUMPFILE is going to be readable for all users! The reason is that the MySQL server can't create a file that is owned by anyone else than the user it's running as (you should never run mysqld as root), the file has to be word readable so that you can retrieve the rows.

· If you are using FOR UPDATE on a table handler with page/row locks, the examined rows will be write locked.

JOIN JOIN Syntax

UNION UNION Syntax