Mysql-select 语法

select 学习笔记

手册：5.1

Mysql：5.1

步骤：

1.       阅读手册资料                                  

2.       创建环境，对各种查询方式进行试验                        

3.       记录查询分析结果及语法要点                           

4.       整理文档                                    

5.       学习sql函数，并整理文档以便查阅                        

6.       分析查询实现原理，执行计划，效率，资源消耗

7.       总结优化sql方法

8.       笔记归档

练习表结构：

root@localhost--test--7:18:32 >desc t_1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| fname | varchar(255) | YES | | NULL | |
| lname | varchar(255) | YES | | NULL | |
| comment | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

root@localhost--test--7:00:29 >desc t_2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | | |
| name | varchar(12) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost--test--7:00:52 >desc t_3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| idd | int(11) | NO | PRI | NULL | auto_increment |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

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_expr, ...   检索并在结果中显示的列，使用别名时，as为自选。

select_expr, ...   检索的目标表（一个或者多个表（包括子查询产生的临时表））

where_definition 检索条件（表达式）

ALL | DISTINCT | DISTINCTROW  ：对结果集进行刷选，all 为全部，distinct/distinctrow 将刷选出重复列，默认为all

[HIGH_PRIORITY]：赋予语句高于UPDATE,DELETE等更新语句的权限

[STRAIGHT_JOIN]：明确指出联接查询中的联接方式

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]：提示优化器查询的结果大小，采用排序或者临时表进行处理

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]：指出对结果是否进行缓存，或者缓存多少行。

[INTO OUTFILE 'file_name'   | INTO DUMPFILE 'file_name']：保存结果至新文件（之前不存在）中，区别在于保存的格式。

Group [asc/desc]by having：对结果集进行分组，having出现则对group by的结果进行刷选，

Order by  : 对最后的结果进行排序

Limit n: 限制输出结果中的行数，limit m,n表示从第m行开始输出n条记录。

[PROCEDURE] ：调用过程

[FOR UPDATE | LOCK IN SHARE MODE]]：对目标进行加锁。

From 子句中，名称后面跟USE INDEX、IGNORE INDEX、FORCE INDEX 可以明确指出如何选择索引

语法约束：

所有的字句必须严格地按照上面格式排序，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

别名关键词AS 自选。别名可用于group by，order by和having

Where 子句：执行WHERE语句以确定哪些行应被包含在GROUP BY部分中，而HAVING用于确定应使用结果集中的哪些行。

HAVING子句可以引用总计函数，而WHERE子句不能引用,如count,sum,max,min,avg，同时，where子句可以引用除总计函数外的其他函数。Where子句中不能使用列别名来定义条件。

Group by 后跟with rollup 可以对结果进行一次或者多次统计。

联接查询：

MySQL支持以下JOIN语法。这些语法用于SELECT语句的table_references部分和多表DELETE和UPDATE语句：

JION
table_references:
    table_reference [, table_reference] …
table_reference:
    table_factor
  | join_table
table_factor:
    tbl_name [[AS] alias]
        [{USE|IGNORE|FORCE} INDEX (key_list)]
  | ( table_references )
  | { OJ table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | table_reference LEFT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
join_condition:
    ON conditional_expr
  | USING (column_list)

    USING(column_list)子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。如

root@localhost--test--7:14:09 >select * from t_1 join t_3 using(id);
+------+-------+-------+---------+------+-----+
| id | fname | lname | comment | name | idd |
+------+-------+-------+---------+------+-----+
| 3 | a | b | NULL | a | 1 |
| 3 | a | b | NULL | b | 2 |
| 3 | a | b | NULL | c | 3 |
+------+-------+-------+---------+------+-----+
3 rows in set (0.00 sec)

    INNER JOIN和，（逗号）在无联合条件下是语义相同的：两者都可以对指定的表计算出笛卡儿乘积（也就是说，第一个表中的每一行被联合到第二个表中的每一行）。

root@localhost--test--7:16:53 >desc select * from t_1 inner join t_2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_2 | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | t_1 | ALL | NULL | NULL | NULL | NULL | 7 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

    列于每个SELECT语句的对应位置的被选择的列应具有相同的类型。（例如，被第一个语句选择的第一列应和被其它语句选择的第一列具有相同的类型。）在第一个SELECT语句中被使用的列名称也被用于结果的列名称。

    HIGH_PRIORITY不能与作为UNION一部分的SELECT语句同时使用。如果您对第一个SELECT指定了HIGH_PRIORITY，则不会起作用。如果您对其它后续的SELECT语句指定了HIGH_PRIORITY，则会产生语法错误。

    只有最后一个SELECT语句可以使用INTO OUTFILE.

root@localhost--test--7:25:09 >select * from t_2 union select * from t_3;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | a |
| 3 | b |
| 3 | c |
+------+------+
5 rows in set (0.00 sec)

    如果对UNION不使用关键词ALL，则所有返回的行都是唯一的，如同已经对整个结果集合使用了DISTINCT。如果指定了ALL，您会从所有用过的SELECT语句中得到所有匹配的行。如下，重复的1  a 行将会列出：

root@localhost--test--7:25:32 >select * from t_2 union all select * from t_3;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 1 | a |
| 3 | a |
| 3 | b |
| 3 | c |
+------+------+
6 rows in set (0.00 sec)
root@localhost--test--7:25:47 >select * from t_2 union all select * from t_3 limit 1;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)

    在最后使用order by,limit n 是对整个结果集进行排序和选择输出行，用括号将单个SELECT 并在括号内加order by ,limit n 则对单个select 语句的结果集进行排序或者选择输出行。

root@localhost--test--7:35:51 >(select * from t_2 limit 1) union all (select * from t_3 limit 2);
+------+------+
| id | name |
+------+------+
| 1 | a |
| 3 | a |
| 3 | b |
+------+------+
3 rows in set (0.01 sec)

Subquery 

子查询的主要优势为：

·         子查询允许结构化的查询，这样就可以把一个语句的每个部分隔离开。

·         有些操作需要复杂的联合和关联。子查询提供了其它的方法来执行这些操作。

·         在许多人看来，子查询是可读的。实际上，子查询给予人们调用早期SQL"结构化查询语言"的原本的想法，这是子查询的创新。

Subquery 的限定，一个子查询的外部语句必须是以下语句之一：SELECT, INSERT, UPDATE, DELETE, SET或DO。还有一个限定是，目前，您不能在一个子查询中修改一个表，又在同一个表中选择。这适用于DELETE, INSERT, REPLACE和UPDATE语句。

1.标量subquery

    子查询最简单的形式是返回单一值的标量子查询，。操作数具有的特性包括：一个数据类型、一个长度、一个指示是否可以为NULL的标志等。

root@localhost--test--7:43:54 >select (select name from t_3 where 0);
+--------------------------------+
| (select name from t_3 where 0) |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.00 sec)

一个标量子查询可以为一个表达式的一部分。不要忘记圆括号。包括在函数调用中。如

root@localhost--test--7:01:30 >select upper((select name from t_3 limit 1));
+---------------------------------------+
| upper((select name from t_3 limit 1)) |
+---------------------------------------+
| A |
+---------------------------------------+
1 row in set (0.00 sec)

如果一个语句只允许一个文字值，您不能使用子查询，如limit 和into file 子句：

root@localhost--test--7:56:41 >select * from t_1 limit (select count(*) from t_3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use n
ear '(select count(*) from t_3)' at line 1

2.Subquery 比较

non_subquery_operand comparison_operator (subquery)

comparison_operator是操作符之一：

=  >  <  >=  <=  <>

root@localhost--test--7:15:21 >desc select * from t_1 t where 2=(select count(*) from t_1 t2 where t.id=t2.id and t.fname=t2.fname);
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

    查询出t_1表中重复的ID列的值。

3.Subquery（Any /in/ some ）

    ANY关键词："对于在子查询返回的列中的任一数值，如果比较结果为TRUE的话，则返回TRUE"。

root@localhost--test--7:22:51 >select * from t_1 where id >= any (select id from t_3);
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 3 | a | b | NULL |
+------+-------+-------+---------+
1 row in set (0.00 sec)

In 和=any 意义相同：对子查询返回的结果集中任一一个值相等，则返回true.

Not in 是<>all 的别名，不是<>any的别名，not in 为对子查询结果任一值比较都不为true，而<>any/<>some 表示不等于其中的一部分。

root@localhost--test--7:31:01 >select * from t_1;
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 1 | a | b | NULL |
| 1 | a | b | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 3 | a | b | NULL |
| 1 | b | c | |
+------+-------+-------+---------+
7 rows in set (0.00 sec)

root@localhost--test--7:31:14 >select * from t_2;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 1 | a |
+----+------+
3 rows in set (0.00 sec)

t_1,t_2中的值。

root@localhost--test--7:24:43 >select * from t_1 where id <> any (select id from t_2);
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 1 | a | b | NULL |
| 1 | a | b | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 3 | a | b | NULL |
| 1 | b | c | |
+------+-------+-------+---------+
7 rows in set (0.00 sec)

    子查询中的值为（1,2,1）；用<>any/some得出的结果。    

    下面是用<>all和not in 的结果

root@localhost--test--7:31:20 >select * from t_1 where id <> all (select id from t_2);
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 3 | a | b | NULL |
+------+-------+-------+---------+
1 row in set (0.00 sec)

4.All subquery

    ALL的意思是"对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE。

root@localhost--test--7:37:21 >select * from t_1 where id> all(select id from t_2);
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 3 | a | b | NULL |
+------+-------+-------+---------+
1 row in set (0.00 sec)

Exist 和 not exist

    如果一个子查询返回任何的行，则EXISTS subquery为True

root@localhost--test--7:44:05 >select * from t_1 where exists (select * from t_2 where t_1.id=t_2.id);
+------+-------+-------+---------+
| id | fname | lname | comment |
+------+-------+-------+---------+
| 1 | a | b | NULL |
| 1 | a | b | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 2 | a | c | NULL |
| 1 | b | c | |
+------+-------+-------+---------+
6 rows in set (0.00 sec)