13.3　MySQL中的SQL语法基础

SQL的英文全称是Structured Query Language（结构化查询语言），使用它可以完成对数据库的管理操作，比如数据添加、修改数据和删除数据等。13.2节介绍的创建表和创建索引的语句，就是SQL语句。本节将介绍MySQL中的SQL语法知识。

13.3.1　查看表结构

前文介绍了查看某个数据库中所有表的命令为SHOW TABLES，如果要查看某个表的结构，可先使用USE命令转入某个数据库，然后使用DESCRIBE命令，示例语句如图13-33所示。

图13-33　DESCRIBE命令

图13-33所示示例中使用命令DESCRIBE的输出结果描述了表users的每一列的信息，如字段名称（Field）、字段类型（Type）、是否允许为空（Null）、键的类别（Key）、默认值（Default）和附件信息（Extra）。命令DESCRIBE可以简写做DESC。

13.3.2　插入数据

一个表创建好后，可以向其中存储数据。向表中插入一条数据（记录）是最基本的数据库操作之一。在MySQL中，使用INSERT语句向表中插入一条数据，语法如下。

---

     INSERT INTO table_name (column_1,column_2,
…column_n) 
     VALUES (value_1,value_2,...value_n);

---

这个语法表示向表table_name中插入一条数据，并且指定字段column_1的值为value_1，column_2的值为value_2，依次类推。这里要注意的是，如果是向数值型字段插入数据，可以不带单引号，但对于其他字段，就必须将值放在单引号中。以13.2.5小节创建的表users为例，利用如下语句可以向表users中插入一条记录。

---

INSERT INTO users (id,name,created_time) VALUES (1,'Charles','2011-03-25 12:50:00');

---

MySQL允许在使用INSERT语句时省略字段名，但此种情况下，必须按字段顺序指定字段的值。例如，上述INSERT语句可以改写成如下形式。

---

INSERT INTO users VALUES (1,'Charles','2011-03-25 12:50:00');

---

MySQL支持另外一种INSERT语句的语法，可以在SQL语句中同时指定字段名和值，示例语句如下。

---

INSERT INTO users SET id=2,name='David',created_time='2011-03-25 12:51:37';

---

【代码解析】这段语句在INSERT语句中使用SET关键字，设置表users中各个字段的值。请读者练习使用INSERT语句向表users中插入其他数据，这些数据将会在以后介绍MySQL的其他操作时使用。

13.3.3　查询数据

查询数据，即浏览数据库中的数据，可以使用SELECT语句。SELECT语句是数据库操作中最常用的语句，因为数据库大部分时间提供的是数据读取服务。SELECT语句的语法如下。

---

SELECT column_1, column_2, ... ,column_n FROM table_name [WHERE clause];

---

该语句可以查询表table_name中某些字段的值组成的一条或多条记录，其中WHERE子句是可选的，它表示按某种条件查询表中特定的记录。

通过SELECT语句从表users中查找所有数据的示例如图13-34所示。

这个示例SQL语句中，使用星号*表示表中所有的字段。

如果只想查找表中的某些列，可用如图13-35所示的SQL语句。

图13-34　查找所有数据

图13-35　查找某些列

这个示例SQL语句中，只查找了表users中所有记录的name和created_time的值。

MySQL支持为列取别名，其用法如下。

---

SELECT column AS new_col_name FROM table_name;

---

【代码解析】上述用法表示，使用关键字AS将表table_name中的字段column重新命名为new_col_name。示例SQL及其查询结果如图13-36所示。

这句SQL语句将表users中的字段created_time取别名为ct。

13.3.4　条件查询

在实际应用中，通常不需要查找表中的所有记录，而是查找满足某些条件的特定记录。在SQL中，可以在SELECT语句中使用WHERE子句指定查询条件，从表中查找出特定的行。例如图13-37所示的SQL语句即找出表users中用户名叫Mary的用户信息。

因为WHERE子句中的列通常是用来搜索的字段，因此，一般会为这样的字段建立索引。WHERE子句可能会匹配到多行数据结果，如图13-38所示的SQL语句，就可以查询出多行满足WHERE条件的记录。

图13-36　重新命名

图13-37　查找特定行

图13-38　查出多行记录

这句SQL语句在表users中查找那些注册时间在2011年3月25日8点以后的用户信息，一共有两行记录匹配，MySQL会将其全部列出。

WHERE子句中可以同时出现多个查询条件，这些查询条件之间使用逻辑运算符AND或OR连接。使用AND连接的查询条件全部满足时，WHERE子句才会匹配相关记录。使用OR连接的查询条件，只要有其中之一满足，WHERE子句就会匹配相关记录。

如图13-39所示是一个使用AND连接查询条件的WHERE子句及其查询结果。

这句SQL语句表示，在表users中查找用户名为Mary并且注册时间在2011年3月25日8点以后的用户，表users中有一条记录满足WHERE子句的查询条件，MySQL将其列出。

如图13-40所示的示例是在WHERE子句中使用OR连接查询条件的SQL语句及其查询结果。

图13-39　AND连接查询条件

图13-40　OR连接查询条件

这句SQL语句表示，在表users中查找用户名为Mary或者注册时间在2011年3月27日0点之后的用户，表users中有两条记录满足WHERE子句的查询条件，MySQL将其全部列出。尽管用户名为Mary的记录，其注册时间在2011年3月27日0点之前，但因其满足WHERE子句的查询条件之一name='Mary'，所以这条记录也会被列出。

13.3.5　更新数据

更新数据是指对数据库中的某些记录做修改，更改记录字段的值。在MySQL中使用UPDATE语句完成对表中数据的修改，UPDATE语句的用法如下。

---

UPDATE table_name SET column_1=value_1,column_2=value_2, 
… ,column_n=value_n [WHERE      clause];

---

table_name是指要做更新操作的表名，在UPDATE语句中，使用SET子句指定要更新的字段及其值。UPDATE语句中，可以使用WHERE子句中的限定条件，来指定要修改的记录，如果没有使用WHERE子句，那么MySQL将修改表中所有的记录。如图13-41所示是使用UPDATE更新表users的SQL语句示例。

这句SQL语句将users表中id为3的用户名字改为Lily Cameron，如果更新成功，MySQL会给出提示信息“Query OK 1row affected”，表示该操作影响到1行记录，并且成功更新，MySQL还会列出匹配的行数、更改的行数及警告信息的数量等提示。执行上述SQL语句后，通过SELECT语句，可以查看id为3的用户名是否已经正确更改为Lily Cameron，如图13-42所示。

图13-41　使用UPDATE更新表

图13-42　SELECT查看

从以上查询结果可以看出，使用UPDATE语句后，表users中id为3的用户名的确被成功修改为了Lily Cameron。

13.3.6　删除数据

使用DELETE语句删除表中的数据，其用法如下。

---

DELETE FROM table_name [WHERE clause];

---

这句SQL表示从表table_name中删除数据，WHERE子句表示删除满足限定条件的记录，WHERE语句是可选的。

删除数据是比较简单的操作，但同时是非常危险的操作。在MySQL的DELETE语句中，如果没有使用WHERE子句，那么表中的数据会在没有任何提示确认的情况下全部被删除！因此这个操作是相当危险的，尤其是在使用root用户登录MySQL并且表的数据没有备份的情况下，所以删除操作一定要谨慎执行。

技巧　避免表中数据全部被删除的一个有效办法是，在写DELETE语句时，先写WHERE子句，最后写DELETE部分。

如下SQL语句将删除表users中用户名为Lily Cameron的记录。如果执行成功，MySQL会给出类似UPDATE语句成功执行后的提示信息，包括执行成功信息和匹配的行数等。

---

DELETE FROM users WHERE name='Lily Cameron';

---

13.3.7　对查询结果排序

MySQL中，执行SELECT语句所生成结果的默认顺序由其出现的顺序决定。SQL支持将查询结果按特定的顺序排列。使用ORDER BY子句可以完成对查询结果的排序，排序有降序和升序两种方式，ORDER BY子句中的关键字ASC表示升序排序，DESC表示降序排序方式。如图13-43所示是使用ORDER BY子句对查询结果做排序的示例。

这句SQL语句对注册时间在2011年3月25号0点以后的用户数据，按其id降序排序。

图13-43　排序

注意　使用ORDER BY子句时，只需指定一列或多列使用关键字ASC或DESC，但每列只能使用一个关键字。如果没有指定关键字，那么MySQL使用ASC（升序排列）作为默认的关键字。另外，如果对多个字段做排序，那么只有紧邻关键字的字段会按关键字排序，其他的字段按默认顺序排序。

13.3.8　对查询结果分组

MySQL支持对查询结果的分组，以汇总相关数据。假设表users中有字段city用来表示各用户所在的城市。

如果读者使用的不是可视化的MySQL数据库工具，则可以重新建一个数据库，其中的数据如图13-44所示，然后再进行本节的测试，结果如图13-45所示。如果使用的是可视化数据库工具，直接在原数据库users中添加一个city字段即可。

图13-44　重建数据库

图13-45　查询结果分组

这句SQL按字段city做分组，从查询结果可以看出，用户一共来自3个城市。

13.3.9　对查询结果进行限定

除WHERE子句外，MySQL还提供了另外两个SQL通用方法来支持查询条件，它们是LIMIT和HAVING。这两个子句主要是为已有的查询结果设置限定条件，从已有的数据结果中根据LIMIT或HAVING子句指定的条件进一步选出所需数据。

1.LIMIT

LIMIT用来限定查询返回的记录条数。如在LIMIT语句的最后加上LIMIT 3，表示返回查询结果中的最前3行记录。而LIMIT 0,10表示从查询结果中的第1行开始，返回10行记录。如图13-46所示是LIMIT子句的SQL语句及其查询结果。

这句SQL语句首先按字段name排序，然后返回查询结果的前4行。

2.HAVING

HAVING子句通常与分组有关，即和GROUP BY子句有关。HAVING子句也用来设置某种查询条件，与WHERE不同的是，它是在SELECT语句查询出结果之后才开始做条件限定。这意味着，HAVING子句所指定的条件，只能限定已经从数据库中取出的记录。假设有一个存放公司雇员薪水的表emp_salary（读者可尝试创建这个表，并向其插入一些数据，以便后续小节练习使用），所存储的数据如图13-47所示。

图13-46　限定查询

图13-47　雇员薪水表数据

从SELECT语句的查询结果中可以看出，雇员级别不同，薪水也不同。如果要找到薪水在5000.00以上的级别，可以使用如图13-48所示的SQL语句。

图13-48　HAVING查询

这句SQL语句在GROUP BY子句的查询结果中挑选出薪水大于5000.00的数据，并按薪水降序排序。

13.3.10　MySQL的数据类型

在创建一个表时，要指定字段的数据类型，MySQL根据字段的数据类型来决定如何存储数据。本小节将介绍MySQL常用的数据类型。表13-1列举了MySQL常用的数据类型。

表13-1　MySQL常用数据类型

提示　在创建表之前，首先需要考虑好各字段应该采用的数据类型。比如一个字段用来存放班级学生的人数，就要使用无符号的INT数据类型。又如一个字段用来存放用户提交的意见或建议，就应该考虑使用VARCHAR类型或者TEXT类型。这些都需要读者在实践中掌握。

13.3.11　MySQL运算符

前文介绍WHERE子句的内容中已经使用了验证两个值是否相等的运算符=。表13-2列举了MySQL所支持的SQL运算符。

表13-2　MySQL运算符

在MySQL中常用的运算符是比较运算符和逻辑运算符。使用逻辑运算符，可以使WHERE语句指定的条件更加灵活、实用。同时，使用括号可以改变MySQL运算符的优先顺序，从而建立起比较复杂的条件查询。

13.3.12　MySQL中的模式匹配

SQL中的模式匹配是指根据一个不完整的字符串，查找字段的值，例如使用'cand%'，可以匹配candy、candle等。在MySQL中使用LIKE运算符完成模式匹配。如图13-49所示的SQL查询演示了从users表中查找名字以字母J开头的用户。

图13-49　模式匹配

字符%可以匹配任意数量的字符，因此字段name值为Jenny和Jack的行都被MySQL列出。另外，模式匹配表达式中，不能使用基本比较运算符，只能使用LIKE或NOT LIKE运算符。表13-3列出了MySQL支持的常见匹配模式。

表13-3　MySQL常见匹配模式

13.3.13　使用MySQL函数

MySQL支持SQL常用的函数，如集合函数、字符串处理函数和日期函数等。本小节将介绍一些编程过程中经常遇到的MySQL函数，如下所述。

·函数COUNT()，用来统计表中记录个数或这列中值的个数。

·函数MAX()，返回某列中的最大值。

·函数MIN()，返回某列中最小值。

·函数SUM()，将指定列的值求和。

·函数AVG()，计算指定列的平均值。

·函数LENGTH()，计算字符串的长度。

·函数SUBSTRING()，截取子字符串。

·函数YEAR()，返回指定日期的年份，范围为1000～9999。类似的还有MONTH()、HOUR()等函数。

·函数UNIX_TIMESTAMP()，返回一个UNIX时间戳。

·函数DATE_FORMAT()，将一个日期格式化。

·函数NOW()，返回MySQL服务器系统的当前日期和时间。

1.集合函数

图13-50　获取总数

例如图13-50所示的SQL语句，使用函数COUNT()计算表users中总共有多少条记录。

这句SQL语句使用星号*作为函数COUNT()的参数，表示返回表users中记录总数。从其执行结果可以看出，表users一共有6行，即共有6条记录。

如果想获取表中特定列的值的个数，可以使用列名作为函数COUNT()的参数，如图13-51所示的SQL语句。

图13-51　获取特定值个数

这句SQL语句使用字段name作为函数COUNT()的参数，查找以字母J开头的用户数量。该SQL语句返回查询结果为2，说明表users中的name列有两个以字母J开头的值。

图13-52所示的SQL语句使用函数MAX()来统计表emp_salary中salary列的最大值。

图13-52　获取最大值

图13-53所示的SQL语句使用函数MIN()来统计表emp_salary中salary列的最小值。

图13-53　获取最小值

函数SUM可以将指定列的值求和，如图13-54所示的SQL语句。

图13-54　求和

这句SQL语句通过函数SUM()计算出表emp_salary中salary列所有值的和。而函数AVG()可以计算该列的平均值，SQL语句如图13-55所示。

图13-55　求平均值

2.字符串处理函数

以上介绍的是MySQL的集合函数，接下来介绍一些字符串处理函数。

在MySQL中使用函数LENGTH来计算字符串的长度，如下SQL语句可以返回字符串'string in MySQL'的长度。

---

SELECT LENGTH('string in MySQL');

---

在MySQL中执行这句SQL语句后，会得到指定字符串的长度为15。

函数SUBSTRING可以截取一个字符串中的一部分，语法格式如下。

---

SUBSTRING(string,pos,len)

---

该函数有3个参数，参数string是原字符串；参数pos表示要截取字符串的起始位置；参数len表示要截取字符串的长度。如SUBSTRING('sometext',4,4)，表示从字符串sometext的第5个字符（其在sometext中的位置为4，第1个字符的位置为0）开始，截取4个字符长度的子串，截取结果为text。

3.日期函数

函数YEAR()可以返回指定日期的年份，SQL语句如图13-56所示。

图13-56　返回年份

这句SQL语句返回表users中id等于3的记录，并显示该记录中created_time字段值的年份。类似的函数还有MONTH()，HOUR()等，它们的用法和函数YEAR()类似，这里不再赘述。

在MySQL中，可以使用函数UNIX_TIMESTAMP()获取一个UNIX时间戳。如果没有给该函数指定参数，它将返回当前时间的时间戳，否则返回指定日期的时间戳。该函数的示例SQL语句及其显示结果如图13-57所示。

图13-57　返回指定日期的时间戳

这句SQL语句返回当前时间的UNIX时间戳。

在MySQL中可以使用函数DATE_FORMAT格式化一个日期，它的语法格式如下。

---

DATE_FORMAT(date,format)

---

该函数有两个参数：参数date表示要格式化的日期时间；format用来指定日期时间格式。该函数的示例SQL语句及显示结果如图13-58所示。

图13-58　DATE_FORMAT示例

图13-59　返回当前的日期时

函数NOW()可以返回MySQL系统的当前日期时间，如图13-59所示的SQL语句将返回当前的日期时间。

事实上，MySQL提供了丰富的各类函数，为SQL提供支持。本小节限于篇幅，只能通过一些基本的、和编程相关的函数作为示例向读者加以介绍，读者还需通过实践和相关参考资料学习更多的内容。