了解mysql中select语句操作实例

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT columna columnb FROM mytable;

SELECT college, region, seed FROM tournament  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;

SELECT college, region, seed FROM tournament  ORDER BY 2, 3;
--要以相反的顺序进行分类，应把DESC（降序）关键字添加到ORDER BY子句中的列名称中。默认值为升序；该值可以使用ASC关键词明确地指定。

SELECT a, COUNT(b) FROM test_table ORDER BY a DESC；

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

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
-- HAVING不能用于应被用于WHERE子句的条目,不能编写如下语句：

SELECT col_name FROM tbl_name HAVING col_name > 0;

--而应该这么编写

SELECT col_name FROM tbl_name WHERE col_name > 0;
--HAVING子句可以引用总计函数，而WHERE子句不能引用：

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

SELECT * FROM tbl LIMIT 10;  # Retrieve rows 0-9;

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15;
--如果要恢复从某个偏移量到结果集合的末端之间的所有的行，您可以对第二个参数是使用比较大的数。

--以下语句可以恢复从第96行到最后的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' 

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"' 

LINES TERMINATED BY '\n'

FROM test_table;

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

(SELECT a FROM tbl_name WHERE a=10 AND B=1)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2)

ORDER BY a LIMIT 10;

SELECT DISTINCT a FROM table_name;

SELECT COUNT(DISTINCT a) FROM table_name;