SELECT 语句的简化语法如下:
SELECT what to retrive
FROM table or tables
WHERE conditions that data must satisfy;
1.在写SELECT语句的时候,需要先指定检索的内容,然后再加上一些可选的子句。
(i) FROM 子句通常是不可少的,但当不需要给出表名时,可以省略它。例如,下面这条查询语句只显示一些表达式的值。这些值计算并未涉及任何表,因此这里不需要FROM子句:
mysql> SELECT 2+2,'Hello, World!',VERSION(),USER();+-----+---------------+-----------+----------------+
| 2+2 | Hello, World! | VERSION() | USER() |
+-----+---------------+-----------+----------------+
| 4 | Hello, World! | 5.6.31 | root@localhost |
+-----+---------------+-----------+----------------+
1 row in set (0.00 sec)
(ii) 当的确需要FROM子句来指定要从哪个表检索数据时,还需要指明要查看哪些列。
SELECT语句最常见的一种形式是使用一个星号(*) 作为列说明符,代表"所有列"。下面这条查询语句将显示 student 表里的所有列:
mysql> SELECT *FROM student;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Jim | M | 1 |
| Tom | M | 2 |
| Lily | F | 3 |
| Lucy | F | 4 |
| Travis | M | 5 |
| Steve | M | 6 |
+--------+-----+------------+
6 rows in set (0.00 sec)
(iii)也可以把自己想要查看的那些列的名字列出来。例如,只想查看学生名字,则可以这样做:
mysql>SELECT name FROM student;+--------+
| name |
+--------+
| Jim |
| Tom |
| Lily |
| Lucy |
| Travis |
| Steve |
+--------+
6 rows in set (0.00 sec)
(iv) 要列出多个列名,那么需要使用逗号把它们分隔开。下面这条语句等价于 SELECT * FROM student 语句,但它把各列的名字明确地列了出来:
mysql>SELECT name,sex,student_id FROM student;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Jim | M | 1 |
| Tom | M | 2 |
| Lily | F | 3 |
| Lucy | F | 4 |
| Travis | M | 5 |
| Steve | M | 6 |
+--------+-----+------------+
6 rows in set (0.00 sec)
也可以按照任意顺序列出列名:
mysql>SELECT sex,student_id,name FROM student;+-----+------------+--------+
| sex | student_id | name |
+-----+------------+--------+
| M | 1 | Jim |
| M | 2 | Tom |
| F | 3 | Lily |
| F | 4 | Lucy |
| M | 5 | Travis |
| M | 6 | Steve |
+-----+------------+--------+
6 rows in set (0.00 sec)
2. 指定检索条件
(i) 要想限制SELECT语句检索出来的行数,可以使用WHERE 子句,指定列值所必须满足的检索条件。例如,可以搜索某个范围内的数值:
mysql> SELECT * FROM student WHERE student_id > 3;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Lucy | F | 4 |
| Travis | M | 5 |
| Steve | M | 6 |
+--------+-----+------------+
3 rows in set (0.00 sec)
(ii)可以查找包含字符数据的字符串值。对于默认的字符集和排序方式,字符串的比较操作通常不区分大小写:
mysql> SELECT * FROM student WHERE sex='M';+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Jim | M | 1 |
| Tom | M | 2 |
| Travis | M | 5 |
| Steve | M | 6 |
+--------+-----+------------+
4 rows in set (0.00 sec)
(iii) 还可以查找组合值
mysql> SELECT * FROM student WHERE sex='M' OR student_id > 3;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Jim | M | 1 |
| Tom | M | 2 |
| Lucy | F | 4 |
| Travis | M | 5 |
| Steve | M | 6 |
+--------+-----+------------+
5 rows in set (0.00 sec)
WHERE 子句里的表达式允许使用算术运算符、比较运算符和逻辑运算符。在表达式里还可以使用括号。在运算时,可以使用常量、表列和函数调用。
常用运算符如下:
算术运算符
运算符
含义
+
加法
-
减法
*
乘法
/
除法
DIV
整除
%
模运算(除法余数)
比较运算符
<
小于
<=
小于等于(不大于)
=
等于
<=>
等于(可用于NULL值)
<>或!=
不等于
>=
大于等于(不小于)
>
大于
逻辑运算符
AND
逻辑与
OR
逻辑或
XOR
逻辑异或
NOT
逻辑非
(iv)使用 IN() 运算符
下面这两条语句效果等价:
mysql> SELECT * FROM student WHERE student_id=3 or student_id=4;+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| Lily | F | 3 |
| Lucy | F | 4 |
+------+-----+------------+
2 rows in set (0.00sec)
mysql> SELECT * FROM student WHERE student_id IN(3,4);+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| Lily | F | 3 |
| Lucy | F | 4 |
+------+-----+------------+
2 rows in set (0.00 sec)
(v) NUL值
NULL值很特殊。其含义是“无值”或“未知值”,所以不能采用两个“已知值”的比较方式,将它与“已知值”进行比较。如果试图将NULL与常规的算术比较运算符一起使用,那么其结果将是未定的(undefined):
mysql> SELECT NULL<0,NULL=0,NULL<> 0,NULL> 0;+--------+--------+----------+---------+
| NULL<0 | NULL=0 | NULL<> 0 | NULL> 0 |
+--------+--------+----------+---------+
| NULL | NULL | NULL | NULL |
+--------+--------+----------+---------+
1 row in set (0.01 sec)
事实上,也不能让NULL与其自身进行比较,因为两个“未知值”的比较结果是无法确定的:
mysql> SELECT NULL=NULL,NULL<>NULL;+-----------+------------+
| NULL=NULL | NULL<>NULL |
+-----------+------------+
| NULL | NULL |
+-----------+------------+
1 row in set (0.00 sec)
如果需要测试某个值和NULL值是否相等,那么必须使用 IS NULL 或 IS NOT NULL,而不能使用 = , <> 或者 !=。
MySQL特有的 <=> 比较运算符可以用于 NULL 值与 NULL 值的比较:
mysql> SELECT NULL <=>NULL;+---------------+
| NULL <=> NULL |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
3. 对查询结果进行排序
(i)使用 ORDER BY 子句进行排序:
mysql> SELECT *FROM student ORDER BY name;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Jim | M | 1 |
| Lily | F | 3 |
| Lucy | F | 4 |
| Steve | M | 6 |
| Tom | M | 2 |
| Travis | M | 5 |
+--------+-----+------------+
(ii) ORDER BY 子句的排序方式是升序排列。在其中的列名后面加上关键字 ASC 或 DESC,可以指定是按照升序排列还是按照降序排列。如:
mysql> SELECT *FROM student ORDER BY name DESC;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Travis | M | 5 |
| Tom | M | 2 |
| Steve | M | 6 |
| Lucy | F | 4 |
| Lily | F | 3 |
| Jim | M | 1 |
+--------+-----+------------+
(iii) 可以对多列进行排列,而且每一列单独地按升序或降序排列:
mysql> SELECT *FROM student ORDER BY name DESC, student_id ASC;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Travis | M | 5 |
| Tom | M | 2 |
| Steve | M | 6 |
| Lucy | F | 4 |
| Lily | F | 3 |
| Jim | M | 1 |
+--------+-----+------------+
4.限制查询结果
LIMIT n 子句限制输出的行数:
mysql> SELECT * FROM student ORDER BY name ASC LIMIT 3;+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| Jim | M | 1 |
| Lily | F | 3 |
| Lucy | F | 4 |
+------+-----+------------+
LIMIT还允许从查询结果的中间抽出部分行。此时需要指定两个值:第一个,给出从查询结果的开头部分跳过的行数目;第二个,需要返回的行数目:
mysql> SELECT * FROM student LIMIT 3,2;+--------+-----+------------+
| name | sex | student_id |
+--------+-----+------------+
| Lucy | F | 4 |
| Travis | M | 5 |
+--------+-----+------------+
如果想从某个表里随机抽取一行或几行,那么可以联合使用 LIMIT 子句和 ORDER BY RAND() 子句:
mysql> SELECT * FROM student ORDER BY RAND() LIMIT 1;+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| Lucy | F | 4 |
+------+-----+------------+
1 row in set (0.00sec)
mysql> SELECT * FROM student ORDER BY RAND() LIMIT 1;+------+-----+------------+
| name | sex | student_id |
+------+-----+------------+
| Tom | M | 2 |
+------+-----+------------+
1 row in set (0.00 sec)
可以看到两次查询结果不同,这体现了随机性。