注意每条语句后面都要以;
结尾。SQL语句是不区分大小写的。
USE
选择数据库
#
SHOW
#
SHOW DATABASES;
,查看数据库列表。SHOW TABLES;
,查看数据库中的表。SHOW COLUMNS
,显示某个表中的列,比如SHOW COLUMNS FROM users
。也可以使用DESCRIBE users
,效果 和SHOW COLUMNS FROM users
是一样的。SHOW STATUS
,用于显示广泛的服务器状态信息。SHOW CREATE DATABASE
和SHOW CREATE TABLE
,分别用来显示创建特定数据库或表的语句。SHOW GRANTS
,用来显示授予用户(所有用户或特定用户)的安全权限。SHOW ERRORS
和SHOW WARNINGS
,用来显示服务器错误或警告消息。HELP SHOW;
查看SHOW
的用法
SELECT
#
为了使用 SELECT
所搜表数据,必须至少给出两条信息——想选择什么,以及从什么地方选择。比如select name from users;
,会找
出 users
表中的所有 name
列。
检索多个列:
select name, age, phone from users;
检索所有列,使用星号 *
通配符:
select * from users;
DISTINCT
#
DISTINCT
关键字用来去重。比如下面的语句,只会返回 name
不同的用户:
select distinct name from users;
DISTINCT
关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price
,会分别作用于了vend_id
和prod_price
列。
LIMIT
#
SELECT
语句返回所有匹配的行,为了返回第一行或前几行,可使用 LIMIT
子句。
select name from users limit 5;
上面的语句最多返回5行。
select name from users limit 5,5;
上面的语句的意思是从第五行开始,最多返回 5 行。
MySQL 5 支持 LIMIT 的另一种替代语法。
LIMIT 4 OFFSET 3
意为从行 3 开始取 4 行,就像LIMIT 3, 4
一样。
完全限定的表名和列名 #
select users.name from demo.users;
上面的语句和 select name from users
没什么区别。但是有一些情形需要完全限定名。比如在涉及外部子查询的语句中,会使用完全限定列名,
避免列名可能存在多义性。
完全限定列名在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。
ORDER BY
#
使用 ORDER BY
子句对输出进行排序。比如 select name from users order by age
,按照 age
排序。
按多个列排序 #
select name from users order by age, weight;
会先按照 age
排序,如果有多行有相同的 age
,再按照 weight
排序。
排序方向 #
升序排序(从 A 到 Z)是默认的排序顺序,如果要进行降序排序,需要指定 DESC
关键字。
select name, age from users order by age desc;
结果会是
+--------+------+
| name | age |
+--------+------+
| ming | 18 |
| qiang | 17 |
| liang | 16 |
| long | 16 |
+--------+------+
多个列降序排序:
select name from users order by age desc, weight;
上面的示例,对 age
列降序排序,weight
还是默认的升序排序。可以看出 DESC
关键字只会作用到其前面的列。
如果想在多个列上进行降序排序,必须对每个列指定
DESC
关键字。ASC
是升序排列的关键字,没什么用,因为默认就是升序。
过滤数据 #
通常数据库检索数据都会指定过滤条件(filter condition)。
在 SELECT
语句中,数据根据 WHERE
子句中指定的搜索条件进行过滤。
select name from users where age = 18;
select name from users where name = 'ming';
会找到 age
等于 18 的行。
如果同时使用
ORDER BY
和WHERE
子句时,ORDER BY
必须位于WHERE
之后,否则将会产生错误。
WHERE
条件操作符
#
操作符 | 描述 |
---|---|
= |
等于 |
<> |
不等于 |
!= |
不等于 |
< |
小于 |
<= |
小于等于 |
> |
大于 |
>= |
大于等于 |
BETWEEN |
在指定的两个值之间 |
BETWEEN
操作符使用:
select name from users where age between 15 and 18;
检索年龄在 15 和 18 之间的用户。
AND
关键字前后分别是开始值和结束值。查询到的数据包括指定的开始值和结束值。
空值检查 #
创建表时,列的值可以为空值 NULL
。
IS NULL
子句可用来检查具有 NULL
值的列。
select name from users where phone IS NULL;
AND #
可使用 AND
操作符给 WHERE
子句附加条件。
select name from users where age = 18 and weight = 60;
OR #
和 AND
差不多,只不过是匹配任一满足的条件。
操作符优先级 #
select name from users where age = 18 or agr = 19 and weight >= 60;
上面的语句是什么结果? 是找出年龄是 18 或者 19,体重在 60 以上的行?并不是。
SQL在处理 OR
操作符前,优先处理 AND
操作符。当 SQL 看到上述 WHERE
子句时,它理解为由年龄为 19,并且体重在 60 以上的用户,或者
年龄为 18,不管体重多少的用户。
正确的语法:
select name from users where (age = 18 or agr = 19) and weight >= 60;
SQL 会首先过滤圆括号内的条件。
IN #
IN
操作符用来指定条件范围,匹配圆括号中的值。
select name from users where age in (18, 19);
IN
操作符与 OR
有相同的功能。
NOT
#
NOT
操作否定条件。
select name from users where age not in (18, 19);
LIKE
#
如果要使用通配符,需要使用 LIKE
操作符。
通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。
%
#
%
表示任何字符出现任意次数。例如找出所有以词 m
开头的用户:
select name from users where name like 'm%';
使用两个通配符,表示匹配任何位置包含文本 in
的值:
select name from users where name like '%in%';
_
#
下划线 _
的用途与 %
一样,但下划线只匹配单个字符而不是多个字符。
使用正则表达式 #
select name from users where name REGEX 'ing';
REGEXP
后跟的是正则表达式。
正则表达式并没有什么优势,但是有些场景下可以考虑使用:
select name from users where weight REGEX '.6';
.
是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符,因此,56
和 66
都匹配且返回。
OR 匹配 #
select name from users where weight REGEX '46|56';
|
为正则表达式的 OR
操作符。
匹配几个字符之一 #
想匹配特定的字符可以指定一组用 [
和 ]
括起来的字符来完成。
select name from users where weight REGEX '[456]6';
[456]
表示匹配 4,5,6。[]
是另一种形式的 OR
语句。可以使用一些正则的语法例如 [^123]
,匹配除了 1,2,3 之外的字符,
[1-9]
匹配 1 到 9 范围的字符。匹配特殊字符钱加 \\
比如 .
要用 \\.
来查找。
计算字段 #
存储在数据库表中的数据一般不是应用程序所需要的格式。
拼接字段 #
Concat()
拼接串,即把多个串连接起来形成一个较长的串。Concat()
需要一个或多个指定的串,各个串之间用逗号分隔。
mysql> select Concat(c1, '(', c2, ')') from record_format_demo;
+--------------------------+
| Concat(c1, '(', c2, ')') |
+--------------------------+
| aaaa(bbb) |
| eeee(fff) |
+--------------------------+
2 rows in set (0.01 sec)
RTrim()
函数可以删除数据右侧多余的空格,还有 LTrim()
和 Trim()
,分别是删除左边空格和删除左右空格。
别名 #
别名(alias)是一个字段或值的替换名。用 AS
关键字:
mysql> select Concat(c1, '(', c2, ')') as c5 from record_format_demo;
+-----------+
| c5 |
+-----------+
| aaaa(bbb) |
| eeee(fff) |
+-----------+
2 rows in set (0.01 sec)
算术计算 #
select price, name, quantity from orders where order_num = 2005;
price
是物品的价格,quantity
是数量,如果想汇总物品总价;
select price, name, quantity, quantity*price as total_price from orders where order_num = 2005;
total_price
就是总价。
MySQL 支持基本算术操作符 +
,-
,*
,/
。此外,圆括号可用来区分优先顺序。
数据处理函数 #
Trim()
就是一个数据处理函数。
文本函数 #
Upper()
函数将文本转换为大写,Upper(name) as newName
Left()
返回串左边的字符Length()
返回串的长度Locate()
找出串的一个子串Lower()
将串转换为小写LTrim()
去掉串左边的空格Right()
返回串右边的字符RTrim()
去掉串右边的空格Soundex()
返回串的SOUNDEX
值SubString()
返回子串的字符
SOUNDEX
是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
日期和时间处理函数 #
AddDate()
增加一个日期(天、周等)AddTime()
增加一个时间(时、分等)CurDate()
返回当前日期CurTime()
返回当前时间Date()
返回日期时间的日期部分DateDiff()
计算两个日期之差Date_Add()
高度灵活的日期运算函数Date_Format()
返回一个格式化的日期或时间串Day()
返回一个日期的天数部分DayOfWeek()
对于一个日期,返回对应的星期几Hour()
返回一个时间的小时部分Minute()
返回一个时间的分钟部分Month()
返回一个日期的月份部分Now()
返回当前日期和时间Second()
返回一个时间的秒部分Time()
返回一个日期时间的时间部分Year()
返回一个日期的年份部分
数值函数 #
Abs()
返回一个数的绝对值Cos()
返回一个角度的余弦Exp()
返回一个数的指数值Mod()
返回除操作的余数Pi()
返回圆周率Rand()
返回一个随机数Sin()
返回一个角度的正弦Sqrt()
返回一个数的平方根Tan()
返回一个角度的正切
聚合函数 #
AVG #
AVG
函数可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
select AVG(price) as avg_price from orders;
返回订单的平均价格。
AVG()
函数忽略列值为NULL
的行。
COUNT #
COUNT(*)
对表中行的数目进行计数。COUNT(column)
对特定列中具有值的行进行计数,忽略 NULL
值。
MAX #
返回指定列中的最大值。忽略列值为 NULL
的行。例如 select max(price) as max_price from products;
返回 products 表中
最贵的物品的价格。
MIN #
与 MAX()
功能相反。
SUM #
SUM()
函数返回指定列值的和。忽略列值为 NULL
的行。例如 select SUM(item_price*quantity) as total_price from products;
聚合不同值 #
上面的几个函数都可以使用 DISTINCT
,比如 AVG(DISTINCT price) as avg_total_price
DISTINCT
只能用于 COUNT()
。DISTINCT
不能用于 COUNT(*)
。
分组 #
GROUP BY
子句用来创建分组。
select vend_id, COUNT(*) as prod_num from products group by vend_id;
上面的语句按 vend_id
排序并分组数据。
注意:
GROUP BY
句必须出现在WHERE
子句之后,ORDER BY
子句之前。- 如果分组列中具有
NULL
值,则NULL
将作为一个分组返回。如果列中有多行NULL
值,它们将分为一组。 GROUP BY
子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT
中使用表达式,则必须在GROUP BY
子句中指定相同的表达式。不能使用别名。- 除了聚集计算语句,
SELECT
语句中的每个列都必须在GROUP BY
子句中给出。
过滤分组 #
HAVING
子句过滤分组。HAVING
非常类似于 WHERE
(WHERE
过滤的是行)。它们的句法是相同的,只是关键字有差别。
也可以这么理解:WHERE
在数据分组前进行过滤,HAVING
在数据分组后进行过滤。
select vend_id, COUNT(*) as prod_num from products group by vend_id having COUNT(*) >= 2;
它过滤 COUNT(*) >=2
的那些分组。
分组和排序 #
GROUP BY
和 ORDER BY
的差别:
order by |
group by |
---|---|
排序产生的输出 | 分组行。但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
一般在使用
GROUP BY
子句时,应该也给出ORDER BY
子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY
排序数据。
检索总计订单价格大于等于 50 的订单的订单号和总计订单价格:
select order_num, SUM(quantity*price) as order_total from orders group by order_num having SUM(quantity*price) >= 50;
按总计订单价格排序输出:
select order_num, SUM(quantity*price) as order_total from orders group by order_num having SUM(quantity*price) >= 50 order by order_total;
SELECT 子句顺序 #
子句 | 是否必须使用 |
---|---|
SELECT |
是 |
FROM |
仅在从表选择数据时使用 |
WHERE |
否 |
GROUP BY |
仅在按组计算聚集时使用 |
HAVING |
否 |
ORDER BY |
否 |
LIMIT |
否 |