单表查询
#1 简介
在当前工作环境下，程序员在开发项目时一般需要保存数据到某种数据库中，而常用的数据库有Oracle，Mysql，SQL Server，DB2，MongoDB等数据库。MySQL数据库管理系统是由瑞典的MySQL AB公司开发的，但是几经辗转最后成为Oracle的产品。由于MySQL的开源、跨平台的特性，使得MySQL的使用非常普遍。本文档主要介绍的是在Mysql数据库中对单表进行的查询。
#2 操作
##2.1 简单查询
Mysql的查询语句为SELECT语句，基本的语法如下：
SELECT [DISTINCT] *|{字段名1, 字段名2, 字段名3,……}
FROM 表名
[WHERE 条件表达式1]
[GROUP BY 字段名 [HAVING 条件表达式2]]
[ORDER BY 字段名 [ASC|DESC]]
[LIMIT [OFFSET] 记录数]其中DISTINCT用于记录去重。GROUP BY用于分组。ORDER BY用于排序，LIMIT 用于限制查询到的记录数。
Create database test;
Use test;然后建立表student
create table student(
id INT(3) Primary key auto_increment,
name VARCHAR(20) not null,
grade FLOAT,
gender CHAR(2)
);使用insert语句向student表中插入多行记录：
mysql> INSERT INTO student (name, grade, gender)
-> values('songjiang', 40, 'M'),
-> ('wuyong', 100, 'M'),
-> ('qinming', 90, 'M'),
-> ('husanniang', 88, 'F'),
-> ('sunerniang', 66, 'F'),
-> ('wusong', 86, 'M'),
-> ('linchong', 92, 'M'),
-> ('yanqing', 90, NULL);
Query OK, 8 rows affected (0.09 sec)
Records: 8 Duplicates: 0 Warnings: 0查询所有字段
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
+----+------------+-------+--------+
8 rows in set (0.00 sec)查询指定字段
mysql> select name, grade, gender from student;
+------------+-------+--------+
| name | grade | gender |
+------------+-------+--------+
| songjiang | 40 | M |
| wuyong | 100 | M |
| qinming | 90 | M |
| husanniang | 88 | F |
| sunerniang | 66 | F |
| wusong | 86 | M |
| linchong | 92 | M |
| yanqing | 90 | NULL |
+------------+-------+--------+
8 rows in set (0.00 sec)需要注意的事项如下：
注意：字段之间要使用英文逗号分隔
由于student表中id为主键，但同时添加了auto_increment关键字，表示自动增长，因此添加纪录时不需要再传入记录。
注意下述的操作过程：
mysql> insert into student (id, name) values(7, 'a');
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
mysql> insert into student (id, name) values(9, 'a');
Query OK, 1 row affected (0.18 sec)mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 9 | a | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> insert into student (id, name) values(20, 'a');
Query OK, 1 row affected (0.04 sec)
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 9 | a | NULL | NULL |
| 20 | a | NULL | NULL |
+----+------------+-------+--------+
10 rows in set (0.00 sec)
mysql> insert into student (id, name) values(9, null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into student(name) values('b')
-> ;
Query OK, 1 row affected (0.05 sec)
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 9 | a | NULL | NULL |
| 20 | a | NULL | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
11 rows in set (0.00 sec)通过上述的操作过程来看
- 虽然id被标记为auto_increment，但我们仍然可以传递不重复的自定义值。如在上述的操作中，在id自动增长为9之后插入了20，再次自动增长变成21.
- 由于在建表时指定了name字段not null属性，因此必须传递非空的字符串给该字段。不然插入数据失败。在select语句中的查询字段中，字段的顺序是可以改变的，无须按照其表中定义的顺序进行排列。
- 可以使用通配符*代替所有字段。
- 查询指定字段的语法为：
select 字段名1， 字段名2， 字段名3，… from 表名###2.1.1 查询时指定字段名
####2.1.1.1 使用as关键字
在下述的代码片段中使用as关键字指定查询字段的显示名，三个字段分别使用as指定显示名，未使用as关键字指定，以及使用默认的grade字段显示。
mysql> select name as 姓名, gender 性别, grade from student;
+------------+--------+-------+
| 姓名 | 性别 | grade |
+------------+--------+-------+
| songjiang | M | 40 |
| wuyong | M | 100 |
| qinming | M | 90 |
| husanniang | F | 88 |
| sunerniang | F | 66 |
| wusong | M | 86 |
| linchong | M | 92 |
| yanqing | NULL | 90 |
| a | NULL | NULL |
| a | NULL | NULL |
| b | NULL | NULL |
+------------+--------+-------+
11 rows in set (0.00 sec)####2.1.1.2 不使用as关键字
不使用as关键字直接在字段名之后指定自定义的显示字段名称，字段之间用英文逗号隔开。
mysql> select name 姓名 ,grade 成绩 from student;
+------------+--------+
| 姓名 | 成绩 |
+------------+--------+
| songjiang | 40 |
| wuyong | 100 |
| qinming | 90 |
| husanniang | 88 |
| sunerniang | 66 |
| wusong | 86 |
| linchong | 92 |
| yanqing | 90 |
| a | NULL |
| a | NULL |
| b | NULL |
+------------+--------+
11 rows in set (0.00 sec)##2.2 按条件查询
数据库中包含大量的数据，当我们需要根据需求获取指定的数据，或者对查询的数据重新进行排列组合时，就要在SELECT语句中指定查询条件对查询结果进行过滤。
SELECT语句中最常见的是使用WHERE子句指定查询条件，查询语法为
select 字段名1, 字段名2,... from 表名 where 条件表达式###2.2.1 关系运算符
使用关系运算符对查询结果进行过滤。
常用的关系运算符如下表所示：
####2.2.1.1 范例
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> select id, name From student where id = 4;
+----+------------+
| id | name |
+----+------------+
| 4 | husanniang |
+----+------------+
1 row in set (0.00 sec)
mysql> select name, gender from student where name = 'wuyong';
+--------+--------+
| name | gender |
+--------+--------+
| wuyong | M |
+--------+--------+
1 row in set (0.06 sec)
mysql> select name 姓名, grade 成绩 from student where grade > 80;
+------------+--------+
| 姓名 | 成绩 |
+------------+--------+
| wuyong | 100 |
| qinming | 90 |
| husanniang | 88 |
| wusong | 86 |
| linchong | 92 |
| yanqing | 90 |
+------------+--------+
6 rows in set (0.05 sec)上述三个例子简单实用关系运算符进行查询结果的过滤
###2.2.2 IN关键字
带In关键字的语法为
select * | 字段名1， 字段名2，... from 表名
where 字段名 [NOT] IN (元素1， 元素2， 元素3, ...)####2.2.2.1 范例
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> select name, grade from student where name in ('wuyong', 'songjiang');
+-----------+-------+
| name | grade |
+-----------+-------+
| songjiang | 40 |
| wuyong | 100 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> select id, grade, name, gender from student where id in (1, 2, 3);
+----+-------+-----------+--------+
| id | grade | name | gender |
+----+-------+-----------+--------+
| 1 | 40 | songjiang | M |
| 2 | 100 | wuyong | M |
| 3 | 90 | qinming | M |
+----+-------+-----------+--------+
3 rows in set (0.00 sec)
mysql> select id, grade, name, gender from student where id not in (1, 2, 3);
+----+-------+------------+--------+
| id | grade | name | gender |
+----+-------+------------+--------+
| 4 | 88 | husanniang | F |
| 5 | 66 | sunerniang | F |
| 6 | 86 | wusong | M |
| 7 | 92 | linchong | M |
| 8 | 90 | yanqing | NULL |
| 21 | NULL | b | NULL |
+----+-------+------------+--------+
6 rows in set (0.00 sec)IN 关键字适用于离散的数值点集合范围的查询。
###2.2.3 Between AND关键字
带BETWEEN AND关键字的查询—判断某个字段的值是否在指定的范围之内
语法为：
SELECT *|{字段名1,字段名2,……}
FROM 表名
WHERE 字段名 [NOT] BETWEEN 值1 AND 值2####2.2.3.1 between and实例
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> select * from student where id between 2 and 4;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
+----+------------+-------+--------+
3 rows in set (0.00 sec)
mysql> select * from student where id not between 2 and 4;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
6 rows in set (0.00 sec)例子展示了使用between and关键字和not between and关键字的用法。
注意：between and关键字指定的两个值得范围是左右包含的。
###2.2.4 带Distinct关键字
Distinct关键字的语句用于去除查询结果的重复记录。有时候，处于对数据的分析需求，需要过滤掉查询记录中重复的值。语法如下：
SELECT DISTINCT 字段名 FROM 表名;####2.2.4.1 Distinct示例
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> select gender from student;
+--------+
| gender |
+--------+
| M |
| M |
| M |
| F |
| F |
| M |
| M |
| NULL |
| NULL |
+--------+
9 rows in set (0.00 sec)
mysql> select distinct gender from student;
+--------+
| gender |
+--------+
| M |
| F |
| NULL |
+--------+
3 rows in set (0.06 sec)####2.2.4.2 Distinct作用于多个字段
Distinct作用多个字段，只有DISTINCT关键字后面指定的国歌字段值都相同，才会被认为是重复记录。
SELECT DISTINCT 字段名1,字段名2,……
FROM 表名;首先插入一条数据，以达到多字段重复的效果
mysql> insert into student (name, grade, gender) VALUES ('songjiang', 20, 'M');
Query OK, 1 row affected (0.07 sec)示例：
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
| 22 | songjiang | 20 | M |
+----+------------+-------+--------+
10 rows in set (0.00 sec)
mysql> select * from student where name = 'songjiang';
+----+-----------+-------+--------+
| id | name | grade | gender |
+----+-----------+-------+--------+
| 1 | songjiang | 40 | M |
| 22 | songjiang | 20 | M |
+----+-----------+-------+--------+
2 rows in set (0.00 sec)
mysql> select distinct name, gender from student;
+------------+--------+
| name | gender |
+------------+--------+
| songjiang | M |
| wuyong | M |
| qinming | M |
| husanniang | F |
| sunerniang | F |
| wusong | M |
| linchong | M |
| yanqing | NULL |
| b | NULL |
+------------+--------+
9 rows in set (0.00 sec)在下述查询中，仅仅看到了一条’songjiang’, ‘M’的数据
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
| 22 | songjiang | 20 | M |
+----+------------+-------+--------+
10 rows in set (0.00 sec)
mysql> select distinct name, gender from student;
+------------+--------+
| name | gender |
+------------+--------+
| songjiang | M |
| wuyong | M |
| qinming | M |
| husanniang | F |
| sunerniang | F |
| wusong | M |
| linchong | M |
| yanqing | NULL |
| b | NULL |
+------------+--------+
9 rows in set (0.00 sec)仅仅显示一条数据，表明已经过滤了重复记录。
###2.2.5 空值查询
判断某些列是否有空值。
在数据表中，某些列的值可能为空值NULL，空置不同于0，也不同于空字符串。在MySQL中，使用IS NULL关键字来判断字段的值是否为空值，其语法格式为：
SELECT *|字段名1,字段名2,……
FROM 表名
WHERE 字段名IS [NOT] NULL####2.2.5.1 空值查询示例
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+------------+-------+--------+
9 rows in set (0.00 sec)
mysql> select * from student where grade is null;
+----+------+-------+--------+
| id | name | grade | gender |
+----+------+-------+--------+
| 21 | b | NULL | NULL |
+----+------+-------+--------+
1 row in set (0.00 sec)
mysql> select * from student where gender is null;
+----+---------+-------+--------+
| id | name | grade | gender |
+----+---------+-------+--------+
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
+----+---------+-------+--------+
2 rows in set (0.00 sec)
mysql> select * from student where gender is not null;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
+----+------------+-------+--------+
7 rows in set (0.00 sec)###2.2.6 带Like关键字的查询
有时候需要对字符串进行模糊匹配，例如查询student表中name字段值’b’开头的记录，为了完成类似功能，Mysql提供了Like关键字进行模糊查询。LIKE关键字可以判断两个字符串是否相匹配。语法如下：
SELECT *|{字段名1,字段名2,……}
FROM 表名
WHERE 字段名 [NOT] LIKE '匹配字符串';匹配字符串指定用来匹配的字符串，其值可以是一个普通字符串，也可以是一个包含百分号%或者下划线_的通配字符串。两个符号在匹配过程中有特殊含义。
####2.2.6.1 百分号%
%通配符用于匹配任意长度的字符串，包括空字符串。例如，字符串’c%’可以匹配以c开头的任意长度的字符串，包括’c’, ‘ct’, ‘cnt’, ‘current’。
查询完整结果集
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
| 22 | songjiang | 20 | M |
+----+------------+-------+--------+
10 rows in set (0.00 sec)查询以name以’s’开头的学生信息
mysql> select * from student where name Like 's%';
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 5 | sunerniang | 66 | F |
| 22 | songjiang | 20 | M |
+----+------------+-------+--------+
3 rows in set (0.00 sec)百分号通配符可以出现在通配符字符串中的任意位置。
查询student表中name字段以‘s’开头以‘g’开头的id和name
mysql> select id, name from student where name like 's%g'
-> ;
+----+------------+
| id | name |
+----+------------+
| 1 | songjiang |
| 5 | sunerniang |
| 22 | songjiang |
+----+------------+
3 rows in set (0.00 sec)查询name中包含字符’y’的学生id和name
mysql> select id, name from student where name like '%y%';
+----+---------+
| id | name |
+----+---------+
| 2 | wuyong |
| 8 | yanqing |
+----+---------+
2 rows in set (0.00 sec)####2.2.6.2 下划线_
下划线通配符_仅仅匹配单个字符，如果要匹配多个字符，需要使用多个下划线通配符。
查询student表中name字段值以字符串‘wu’开始，以字符串‘ong’结束，并且两个字符串之间只有一个字符的记录。
mysql> select * from student where name like 'wu_ong';
+----+--------+-------+--------+
| id | name | grade | gender |
+----+--------+-------+--------+
| 2 | wuyong | 100 | M |
| 6 | wusong | 86 | M |
+----+--------+-------+--------+
2 rows in set (0.00 sec)查询student表中字段值包含7个字符，并且以字符串’ing’结束的记录
mysql> select * from student where name like '____ing';
+----+---------+-------+--------+
| id | name | grade | gender |
+----+---------+-------+--------+
| 3 | qinming | 90 | M |
| 8 | yanqing | 90 | NULL |
+----+---------+-------+--------+
2 rows in set (0.00 sec)如果要匹配的字符串包含%或者下划线_，怎么进行呢？
注意：百分号和下划线是通配符，它们在通配字符串中有特殊含义，因此，如果要匹配字符串中的百分号和下划线，就需要在通配字符串中使用右斜线(“\”)对百分号和下划线进行转义，例如，“%”匹配百分号字面值，“_”匹配下划线字面值。
###2.2.7 带逻辑运算符的多条件查询
在使用select语句查询数据时，有时候为了使查询结果更加精确，可以使用多个查询条件。
####2.2.7.1 AND关键字
在MySQL中提供了AND关键字，使用AND关键字可以连接两个或者多个查询条件，只有满足所有条件的记录才会被返回。
SELECT *|{字段名1,字段名2,……}
FROM 表名
WHERE 条件表达式1 […… AND 条件表达式n];查询student表中id字段小于5，并且gender字段为’F’的学生姓名
mysql> select id, name, gender from student where id < 5 AND gender = 'F';
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 4 | husanniang | F |
+----+------------+--------+
1 row in set (0.09 sec)查询student表中id字段在1，2，3，4之间，name字段值以字符串‘ng’结尾，并且grade字段小于80的学生记录
mysql> select * from student where id in (1, 2, 3, 4) and name like '%ng' and grade < 80;
+----+-----------+-------+--------+
| id | name | grade | gender |
+----+-----------+-------+--------+
| 1 | songjiang | 40 | M |
+----+-----------+-------+--------+
1 row in set (0.07 sec)####2.2.7.2 OR关键字
在使用SELECT语句查询数据时，也可食用OR逻辑运算符连接多个查询条件。与AND不同，在使用OR关键字时，只要记录满足任意一个条件就会被查询出来，其语法格式为:
SELECT *|{字段名1,字段名2,……}
FROM 表名
WHERE 条件表达式1 OR […… OR 条件表达式n];示例1
mysql> select id, name, gender from student where id < 3 or gender='F';
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | songjiang | M |
| 2 | wuyong | M |
| 4 | husanniang | F |
| 5 | sunerniang | F |
+----+------------+--------+
4 rows in set (0.00 sec)示例2
mysql> select id, name, gender, grade from student where name like 'h%' or gender = 'F' or grade = 100;
+----+------------+--------+-------+
| id | name | gender | grade |
+----+------------+--------+-------+
| 2 | wuyong | M | 100 |
| 4 | husanniang | F | 88 |
| 5 | sunerniang | F | 66 |
+----+------------+--------+-------+
3 rows in set (0.00 sec)通过上述的查询示例，可以看到返回的记录只要满足其中任意一个条件都可以显示。
注意：OR关键字和AND关键字可以一起使用，需要注意的是，AND优先级高于OR，因此当两者在一起使用时，应该先运算AND量表的条件表达式，再运算OR两边的条件表达式。
###2.3 为表和字段取别名
在查询数据时，可以为表和字段取别名，这个别名可以代替其指定的表和字段。
####2.3.1 为表取别名
在查询操作时，如果表名很长使用起来不方便，这时可以为表取一个别名，用这个别名来代替表的名称。
这种语法如下：
SELECT * FROM 表名 [AS] 别名;具体示例如下：
mysql> select * from student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 4 | husanniang | 88 | F |
| 5 | sunerniang | 66 | F |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 8 | yanqing | 90 | NULL |
| 21 | b | NULL | NULL |
| 22 | songjiang | 20 | M |
+----+------------+-------+--------+
10 rows in set (0.00 sec)
mysql> select * from student AS stu where stu.gender = "M";
+----+-----------+-------+--------+
| id | name | grade | gender |
+----+-----------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 22 | songjiang | 20 | M |
+----+-----------+-------+--------+
6 rows in set (0.00 sec)
mysql> select * from student stu where stu.gender = "M";
+----+-----------+-------+--------+
| id | name | grade | gender |
+----+-----------+-------+--------+
| 1 | songjiang | 40 | M |
| 2 | wuyong | 100 | M |
| 3 | qinming | 90 | M |
| 6 | wusong | 86 | M |
| 7 | linchong | 92 | M |
| 22 | songjiang | 20 | M |
+----+-----------+-------+--------+
6 rows in set (0.00 sec)在上述的代码片段中，为表student取别名为stu，并且在条件表达式中进行使用，很方便。
注意：AS关键字可有可无，也可以在From之后在写表名之后直接跟上起的别名。上述代码片段中第二个示例便是这种用法的显示。
###2.3.2 为字段取别名
在操作sql表时，每条记录的列名并不是必须为定义时的列名，有时为了让所显示的查询结果更加直观，可以为字段取一个别名。Mysql中为字段起别名的格式如下：
SELECT 字段名 [AS] 别名[,字段名 [AS] 别名,……] FROM 表名示例：
mysql> select name 姓名, gender as 性别 from student stu where stu.gender = "M";
+-----------+--------+
| 姓名 | 性别 |
+-----------+--------+
| songjiang | M |
| wuyong | M |
| qinming | M |
| wusong | M |
| linchong | M |
| songjiang | M |
+-----------+--------+
6 rows in set (0.00 sec)在上述的代码片段中，可以看到查询出来的结果列名已经显示为姓名和性别了，这种自定义的方式使得sql查询更加的人性化。并且上述的查询语句同时包含了为表和字段起别名的演示。
#3 参考
#4 总结
mysql语法是sql类数据库查询的基础，学会使用这些基础知识构建出需要的select语句，熟悉语法规则，孰料使用数据库查询语句，对于程序员是必备的开发技能。本文档包含mysql对于单表的基本操作，对于熟悉mysql数据库，尤其是命令行操作有很好的帮助。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
