1
SELECT [OPTIONS] [DISTINCT_OPTIONS] select_expression
2
INTO { OUTFILE | DUMPFILE } 'file_name' EXPORT_OPTIONS
3
FROM table_reference [[JOIN OPTIONS] table_refence2 [JOIN CONDITION]] ...
6
GROUP BY [ASC|DESC] c1_name|c1_num|c1_expr, [ASC|DESC] c2_name|c2_num|c2_expr, ...
7
ORDER BY [ASC|DESC] c1_name|c1_num|c1_expr, [ASC|DESC] c2_name|c2_num|c2_expr, ...
9
PROCEDURE procedure_name
15
HIGH_PRIORITY: will give SELECT higher priority than a statment
17
SQL_BIG_RESULT: used with GROUP BY or DISTINCT to tell optimizer
18
that result set will have many rows. In this case
19
MySQL will directly used disk-based temporary tables
20
SQL_SMALL_RESULT: used with GROUP BY or DISTINCT to tell optimizer
21
that the result set will be small. In this case,
22
MySQL will use fast temporary tables instead of sorting
23
SQL_BUFFER_RESULT: will force the result to be put into a temporary table.
24
This will help MySQL free the table locks early and will
25
help in cases where it takes a long time to send the
26
result set to the client
27
STRAIGH_JOIN: forces the optimizer to join the tables in the order
28
in which they are listed in FROM clause. Use this
29
to speed up a query if optimizer use non-optimal order
36
expr1 AS alias1,... if multiple tables selected columns must be name
37
as "tbl_name.col_name" or "db_name.tbl_name.col_name"
39
INTO { OUTFILE | DUMPFILE } 'file_name' EXPORT_OPTIONS:
40
OUTFILE write selected rows to a file. The file is created on
41
server host and cannot exist. Client must have FILE
42
privilege. In the resulting file next characters will
43
be escaped: ESCAPED BY character, ASCII 0, first
44
characters in FIELDS TERMINATED BY and LINES TERMINATED
46
DUMPFILE Writes all in one row without any column or line
47
termination and without any escaping.
49
FROM table_reference [[JOIN OPTIONS] table_refence2 [JOIN CONDITION]] ...
50
if named more than one table => preforming join.
51
table_reference: tbl_name1 [AS alias1] [USE INDEX (key_list)]
52
[IGNORE INDEX (key_list)]
53
tbl1_name: can be "tbl1_name" or "db_name.tbl1_name"
60
[NATURAL] LEFT [OUTER] JOIN [C]:
61
if there is no matching record for the right table
62
in the ON or USING part, a row with all columns set
63
to NULL is used for right table.
64
Example: It can be used to find records in a table
65
that have no counterpart in another table:
66
select t1.* from t1 left join t2 ON t1.id=t2.id
68
[NATURAL] RIGHT [OUTER] JOIN [C]:
69
works analogy as LEFT JOIN
70
[NATURAL] INNER JOIN [C]:
71
equivalent to "tabl1,tabl2" and do a full JOIN
72
between the tables used.
73
STRAIGHT_JOIN: equivalent to JOIN, excepts that the left table
74
always read before right table
76
NATURAL: Specifies: USING (JOIN CONDITION) that names all
77
columns that exists in both tables
81
ON conditional_expr: any condition like in WHERE
82
USING (columns_list): columns list that must exist in both tables
83
USING (C1,C2,C3) equivalent ON A.C1=B.C1 AND
84
A.C2=B.C2 AND A.C3=B.C3.
86
HAVING simmiliar to WHERE, but it is applied last,
87
just before items are sent to the client, with no
88
optimization. Must be used it the cases then used
89
functions like min(),max(),sum() & etc...
92
DESC specifies sorting in descending order.
93
Column selected by name or number(starting from 1)
95
Then used with BLOB|TEXT columns, only the first
96
max_sort_length bytes are used.
99
Then used with BLOB|TEXT columns, only the first
100
max_sort_length bytes are used.
101
In griup will selected only items have some value in
102
specified column|expression. MAX,MIN,SUM,... will work
103
separatly for each such group.
106
rows: Maximum rows to return from SELECT
107
offset: First row to return [ starting from 0 - default ]
108
(For example LIMIT 5,11 returns rows 5-16)
110
PROCEDURE procedure_name:
114
FOR UPDATE: if used on a table handler with page/row locks, the
115
examined rows will be write locked.
116
LOCK IN SHARE MODE: ???
117
------------------------------------------------------------------------------
118
SELECT ... UNION [ALL] SELECT ...;
119
Used to combine the results from many SELECT statments
120
into one result set. If ALL not used all returned rows
123
Only the last SELECT can have INTO OUTFILE
124
Only the last SELECT can have ORDER BY
125
------------------------------------------------------------------------------
126
HANDLER table COMMAND
127
Provides direct access to MySQL table interface, bypassing SQL optimiser.
128
Thus, it is faster then SELECT.
133
READ index {=,<,>} (value1,value2,...) [WHERE ...] [LIMIT ...]
134
Fetches one (or specified by LIMIT) row where the index
135
specified complies to the condition and WHERE condition
136
is met. If index consist of several parts(multi column)
137
the values are specified in comma-separated list, providing
138
values only for few first columns is possible.
139
READ index {FIRST|NEXT|PREV|LAST} [WHERE ...] [LIMIT ...]
140
Fetches rows from table in in index order.
141
READ {FIRST|NEXT} [WHERE ...] [LIMIT ...]
142
Fetcnes rows from table in natural row order