数据库管理系统最重要的功能就是数据查询,数据查询不应只是简单的返回数据库中储存的数据,还应根据需要进行筛选,以及确定以什么样的格式显示。MySQL提供了强大的、灵活的查询语句来支持这些操作。
基本查询语句:
SELECT {*|<字段列表>}[
FROM <表1>、<表2>...
[WHERE <表达式>]
[GROUP BY <group by definition>]
[HAVING <expression> [{<operator><expresssion>}...]]
[ORDER BY <order by definition>]
[LIMIT [<offset>,]<row count>]
];
单表查询:
查询所有字段
1、使用通配符“*”查询
SELECT * FROM 表名;
2、指定所有字段
SELECT <字段1>,<字段2>,...<字段n> FROM 表名;
查询指定字段
SELECT <字段1>,<字段2>,...<字段m> FROM 表名;
查询指定记录
SELECT <字段1>,<字段2>,...<字段m> FROM 表名 WHERE 查询条件;
带IN关键字查询
IN操作符用来查询满足指定范围内的条件的记录。
例:
SELECT name FROM userinfo WHERE age IN (18,19);
可以在IN之前加NOT关键字取不在范围内的记录。
带BETWEEN AND的范围查询
BETWEEN AND 用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值。
例:
SELECT * FROM userinfo WHERE age BETWEEN 17 AND 21;
可以在BETWEEN前加NOT关键字取不再范围内的数据。
带LIKE的字符匹配查询
LIKE可以使用通配符进行匹配查询,通过创建查找模式对表中的数据进行比较。通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符,SQL语句中支持多种通配符,可以和LIKE一起使用的通配符有“%”和“_”。“%”可以匹配任意长度的字符,甚至包括零字符,而“_”只能匹配任意单个字符。
例:
使用“%”
SELECT * FROM userinfo WHERE name LIKE "大%";
使用“_”
SELECT * FROM userinfo WHERE name LIKE "大_";
空值查询
创建数据表时可以指定列中是否包含空值(NULL),空值不等同于0,也不等同于空字符串。SELECT语句中使用IS NULL查询某字段内容为空记录。
例:
SELECT * FROM userinfo WHERE age IS NULL;
可以使用NOT IS NULL 查询字段不为空的记录。
带AND的多条件查询
MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回。
例:
SELECT * FROM userinfo WHERE name LIKE "大%" AND age = 18;
带OR的多条件查询
与AND相反,在WHERE中声明使用OR操作符,表示只要满足其中一个条件的记录即可返回。
例:
SELECT * FROM userinfo WHERE name LIKE "小%" OR age > 20;
查询结果不重复
在SELECT语句中,可以使用DISTINCT关键字指示MySQL消除重复的记录值。语法格式如下:
SELECT DISTINCT 字段名 FROM 表名; # 只返回不同的“字段名”行
DISTINCT应用于其后的所有列,而不仅仅是它后面的第一个列,因此除非后面所有列的值均相同,否则会返回所有记录。
对查询结果进行排序
SELECT语句中使用ORDER BY 子句对查询的结果进行排序。
1、单例排序
SELECT * FROM userinfo WHERE name LIKE "大%" ORDER BY age;
2、多列排序
例:
SELECT * FROM userinfo ORDER BY age,name;# 先按年龄进行排序,再按姓名进行排序
3、指定排序方向
默认情况下,查询数据按字母升序进行排序(A-Z),还可以使用ORDER BY 进行降序排序(Z-A),通过关键字DESC实现。DESC只应用到直接位于其前面的字段上。
例:
SELECT * FROM userinfo ORDER BY age DESC;
分组查询
分组查询是对某个数据按照某个或者多个字段进行分组,MySQL中用GROUP BY 关键字对数据进行分组,GROUP BY 通常和聚合函数一起使用,语法格式如下:
SELECT 类别,聚合函数 AS 别名 FROM 表名 GROUP BY 字段 [HAVING <条件表达式>]
例如我想要查询用户表中每个年龄有多少人可以这样写:
SELECT age,COUNT(*) AS Total FROM userinfo GROUP BY age;
想要查看各个年龄的人的姓名可以使用GROUP_CONCAT():
SELECT age,GROUP_CONCAT(name) AS Total FROM userinfo GROUP BY age;
GROUP BY 可以与HAVING一起限定显示记录所需满足的条件,只有满足条件的分组才会被显示出来,例如我们只显示年龄大于15岁的每个年龄的人数:
SELECT age,COUNT(*) AS Total FROM userinfo GROUP BY age HAVING age > 15;
HAVING 和WHERE都是用来过滤数据的,但HAVING是在数据分组之后进行过滤来选择分组,而WHERE是在分组之前用来选择记录,另外WHERE排除的记录不再包括在分组中。HAVING中经常包含聚合函数,而WHERE中不可以使用聚合函数。
在GROUP BY 子句中还可以使用WITH ROLLUP关键字在所有查询出的分组记录之后加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。
GROUP BY 还可以对多个字段进行分组,根据字段从左到右的规则进行分组。
GROUP BY 还可以和ORDER BY 一起使用对分组进行排序:
SELECT age,COUNT(*) AS Total FROM userinfo GROUP BY age ORDER BY Total DESC;
使用LIMIT限制查询结果的数量
基本语法格式:
LIMIT [位置偏移量,] 行数
[位置偏移量]指示MySQL从哪一行开始显示,不指定将会从第一条记录(偏移量为0)开始,“行数”指定返回的记录条数。
例:
SELECT * FROM userinfo LIMIT 4;
还可以使用LIMIT 4 OFFSET 1,得出的结果和LIMIT1,4一样。如果使用了ORDER BY,则LIMIT必须在ORDER BY之后。
使用聚合函数查询
函数 | 作用 |
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值的和 |
AVG():
AVG()函数通过计算返回的函数和每一行的数据的和,求得指定列数据的平均值,例如查询用户表中所有用户年龄的平均值:
SELECT AVG(age) AS avg_age FROM userinfo;
AVG()函数的参数为要计算的列名称,如果要得到多个列的多个平均值,这需要在每一列中使用AVG()函数。
COUNT():
COUNT()函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据函数,有两种方法:
- COUNT(*)计算表中总的行数,不管某列有数值或是为空值。
- COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行。
MAX:
MAX()函数返回指定列中的最大值。
MAX()函数除了用来找出最大的列值或日期值之外,还可以返回任意列中的最大值,包括返回字符类型的最大值。对字符类型进行比较时,按照字符的ASCII码进行比较。
MIN():
MIN()函数返回指定列中的最小值。
MIN()函数和MAX()函数一样,不仅可查找数值类型,还可查找字符类型。
SUM():
SUN()是一个求总和的函数,返回指定列值的总和。
SUM()函数在计算时,会忽略列值为NULL的行。
以上聚合函数均可与GROUP BY 一起使用实现不同的功能。
连接查询
连接查询是关系型数据库的主要特点,也是关系型数据库中最主要的查询,包括内连接查询和外连接查询以及符合连接查询。
内连接查询:
内连接(INNER JOIN)使用比较运算符进行表间某些(列)数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新的记录。
例如我们现在有两个表userinfo和phone,一个存放用户信息,一个存放手机号,现在可以使用INNER JOIN将userinfo中的name字段和phone中的number字段合成一张表返回:
SELECT name,number FROM userinfo INNER JOIN phone WHERE userinfo.id = phone.user_id;
# 如果表名或字段名过长可以使用别名(不要和表中已存在的字段重名):
#SELECT name AS n,number AS num FROM userinfo AS u INNER JOIN phone AS p WHERE u.id = p.user_id;
一般情况下INNER JOIN会和ON一起使用,而不是使用WHERE,这是一种规范,而且某些情况下使用WHERE会影响查询性能。ON和WHERE 后面指定的条件相同。
自连接查询是一种特殊的内连接查询,只不过相关联的表是同一张表。
外连接查询:
内连接查询时,返回查询结果集合中仅是符合查询条件和连接条件的行。但有时需要包含没有关联的行中数据,即返回查询结果集合中不仅包含符合连接条件的行,还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有行数据行。外连接分为左外连接和右外连接。
- LEFT JOIN(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
- RIGHT JOIN(右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。
LEFT JOIN:
左连接结果包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果行中,右表的所有选择列表列均为空值。
例:
SELECT name,number FROM userinfo LEFT JOIN phone ON userinfo.id = phone.user_id;
RIGHT JOIN:
右连接是左连接的反向连接,将返回右表中的所有行。如果右表的某行在左表中没有匹配行,左表将返回空值。
复合条件链接查询:
复合条件连接查询是在连接查询过程中,通过添加过滤条件,限制查询的结果,使查询的结果更加准确。
例:
SELECT name,number FROM userinfo INNER JOIN phone WHERE userinfo.id = phone.user_id AND userinfo.age > 17;
子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性自MySQL4.1开始引入。在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或多个表。子查询中常用的操作符有ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中,而且可以进行多层嵌套,子查询中也可以使用比较运算符,如“<”、"<="、">"、">=“和”!="等。
带ANY、SOME关键字的子查询:
ANY和SOME关键字是同义词,表示满足其中任一条件,他们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。
例:
SELECT * FROM userinfo WHERE id > ANY(SELECT user_id FROM phone);
# SELECT * FROM userinfo WHERE id > SOME(SELECT user_id FROM phone);
带ALL关键字的子查询:
ALL不同于ANY和SOME,使用ALL时需要同时满足所有内层查询的条件。
例:
SELECT * FROM userinfo WHERE id > ALL(SELECT user_id FROM phone);
带EXISTS关键字的子查询:
EXISTS关键字后面的参数是任意一个子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询将进行查询;如果子查询没有返回任何行,那么EXISTS返回false,此时外层语句将不进行查询。
NOT EXISTS与EXISTS相反,使用方法相同。
带IN关键字的子查询:
IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作。
例:
SELECT * FROM userinfo WHERE id IN(SELECT user_id FROM phone WHERE user_id > 3);
合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的;使用ALL不删除重复的行也不对结果进行自动排序,基本语法格式如下:
SELECT 字段1,... FROM table1
UNION [ALL]
SELECT 字段1,... FROM table2
使用正则表达式查询
正则表达式通常用来检索和替换那些符合某个文本模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串。MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。
常用字符匹配列表:
选项 | 说明 |
^ | 匹配文本的开始字符 |
$ | 匹配文本的结束字符 |
. | 匹配任何单个字符 |
* | 匹配0个或多个在它前面的字符 |
+ | 匹配前面的字符一次或多次 |
<字符串> | 匹配包含指定字符串的文本 |
[字符集合] | 匹配字符集合中任何一个字符 |
[^] | 匹配不再括号[]中的任何字符 |
字符串{n,} | 匹配前面的字符串至少n次 |
字符串{n,m} | 匹配前面的字符串至少n次,至多m次。如果n为0,此参数为可选参数 |
例:
SELECT * FROM userinfo WHERE name REGEXP "^大";