MySQL知识（九）——单表查询

来源：互联网发布：淘宝网店排名查询编辑：程序博客网时间：2024/06/06 10:03

0 查询章节用到的数据

　　用到的数据表

CREATE TABLE customers(  c_id      int       NOT NULL AUTO_INCREMENT,  c_name    char(50)  NOT NULL,  c_address char(50)  NULL,  c_city    char(50)  NULL,  c_zip     char(10)  NULL,  c_contact char(50)  NULL,  c_email   char(255) NULL,  PRIMARY KEY (c_id));CREATE TABLE fruits(  f_id    char(10)      NOT NULL,  s_id    INT       NOT NULL,  f_name  char(255)     NOT NULL,  f_price decimal(8,2)  NOT NULL,  PRIMARY KEY(f_id)) ;CREATE TABLE suppliers(  s_id      int      NOT NULL AUTO_INCREMENT,  s_name    char(50) NOT NULL,  s_city    char(50) NULL,  s_zip     char(10) NULL,  s_call    CHAR(50) NOT NULL,  PRIMARY KEY (s_id)) ;CREATE TABLE orderitems(  o_num      int          NOT NULL,  o_item     int          NOT NULL,  f_id       char(10)     NOT NULL,  quantity   int          NOT NULL,  item_price decimal(8,2) NOT NULL,  PRIMARY KEY (o_num,o_item)) ;CREATE TABLE orders(  o_num  int      NOT NULL AUTO_INCREMENT,  o_date datetime NOT NULL,  c_id   int      NOT NULL,  PRIMARY KEY (o_num)) ;

　　插入的数据：

INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip,  c_contact, c_email) VALUES(10001, 'RedHook', '200 Street ', 'Tianjin',  '300000',  'LiMing', 'LMing@163.com'),(10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000', 'LuoCong', NULL),(10004, 'JOTO', '829 Riverside Drive', 'Haikou',  '570000',  'YangShan', 'sam@hotmail.com');INSERT INTO fruits (f_id, s_id, f_name, f_price)VALUES('a1', 101,'apple',5.2),('b1',101,'blackberry', 10.2),('bs1',102,'orange', 11.2),('bs2',105,'melon',8.2),('t1',102,'banana', 10.3),('t2',102,'grape', 5.3),  ('o2',103,'coconut', 9.2),('c0',101,'cherry', 3.2),('a2',103, 'apricot',2.2),('l2',104,'lemon', 6.4),('b2',104,'berry', 7.6),('m1',106,'mango', 15.6),('m2',105,'xbabay', 2.6),('t4',107,'xbababa', 3.6),('m3',105,'xxtt', 11.6),('b5',107,'xxxx', 3.6);INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),(102,'LT Supplies','Chongqing','400000','44333'),(103,'ACME','Shanghai','200000','90046'),(104,'FNK Inc.','Zhongshan','528437','11111'),(105,'Good Set','Taiyuang','030000', '22222'),(106,'Just Eat Ours','Beijing','010', '45678'),(107,'DK Inc.','Zhengzhou','450000', '33332');INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)VALUES(30001, 1, 'a1', 10, 5.2),(30001, 2, 'b2', 3, 7.6),(30001, 3, 'bs1', 5, 11.2),(30001, 4, 'bs2', 15, 9.2),(30002, 1, 'b3', 2, 20.0),(30003, 1, 'c0', 100, 10),(30004, 1, 'o2', 50, 2.50),(30005, 1, 'c0', 5, 10),(30005, 2, 'b1', 10, 8.99),(30005, 3, 'a2', 10, 2.2),(30005, 4, 'm1', 5, 14.99);INSERT INTO orders(o_num, o_date, c_id)VALUES(30001, '2008-09-01', 10001),(30002, '2008-09-12', 10003),(30003, '2008-09-30', 10004),(30004, '2008-10-03', 10005),(30005, '2008-10-08', 10001);

1 基本查询语句

　　MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是：
　　简单点形式：

SELECT [字段1,字段2,字段3,......]FROM [表或试图]WHERE [查询条件]

　　详细形式：

SELECT {*|<字段列表>}[　　FROM<表1>,<表2>,...　　[WHERE <表达式>　　[GROUP BY <group by definition>]　　[HAVING <expression>[{<operator><expression>}...]]　　[ORDER BY <order by definition>]　　[LIMIT[<offset>,]<row count>]]

{*|<字段列表>}包含星号通配符选字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间用逗号隔开，最后一个字段后不要加逗号
FROM<表1>,<表2>,…，表1和表2表示查询数据的来源，可以是单个或者多个
WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件
GROUP BY <字段>，如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY <字段>]，该字段告诉MySQL按什么样的顺序显示查询出来的数据，可以进行排序有：升序（ASC），降序（DESC）。
LIMIT，该子句告诉MySQL每次显示查询出来的数据条数。
　　例子：使用SELECT语句查询fruits表中f_id,f_name字段的数据：

mysql> SELECT f_id,f_name FROM fruits;+------+------------+| f_id | f_name     |+------+------------+| a1   | apple      || a2   | apricot    || b1   | blackberry || b2   | berry      || b5   | xxxx       || bs1  | orange     || bs2  | melon      || c0   | cherry     || l2   | lemon      || m1   | mango      || m2   | xbabay     || m3   | xxtt       || o2   | coconut    || t1   | banana     || t2   | grape      || t4   | xbababa    |+------+------------+16 rows in set

2 单表查询

　　单表查询是指从一张表数据中查询所需的数据。

2.1 查询所有字段

　　（1）在SELECT语句中使用星号“*”通配符查询所有字段

SELECT * FROM 表名;例如：SELECT * FROM fruits;

　　（2）在SELECT语句中指定所有字段。

SELECT f_id,s_id,f_name,f_price FROM fruits;

　　（3）注意：
　　一般情况下，除非需要使用表中所有字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

2.2 查询指定字段

　　（1）查询单个字段

SELECT 列名 FROM 表名;例如：SELECT f_name FROM fruits;

　　（2）查询多个字段

SELECT 字段1,字段2,...,字段n FROM 表名;例如：SELECT f_name,f_price FROM fruits;

2.3 WHERE子句查询指定记录

　　数据库中包含大量的数据，有时需要查询表中的指定数据，即对数据进行过滤。在SELECT语句中，通过WHERE子句对数据过滤查询，语法格式：

SELECT 字段名1,字段名2,...,字段名nFROM 表名WHERE 查询条件

　　在WHERE子句中，MySQL提供了一系列的条件判断符：

操作符说明操作符说明 = 相等 <>,!= 不想等 < 小于 <= 小于或者等于 > 大于 >= 大于或者等于 BETWEEN 位于两值之间

　　例如：

mysql> SELECT f_name,f_price    -> FROM fruits    -> WHERE f_price = 10.2;+------------+---------+| f_name     | f_price |+------------+---------+| blackberry | 10.2    |+------------+---------+1 row in setmysql> SELECT f_name,f_price    -> FROM fruits    -> WHERE f_price<10.00;+---------+---------+| f_name  | f_price |+---------+---------+| apple   | 5.2     || apricot | 2.2     || berry   | 7.6     || xxxx    | 3.6     || melon   | 8.2     || cherry  | 3.2     || lemon   | 6.4     || xbabay  | 2.6     || coconut | 9.2     || grape   | 5.3     || xbababa | 3.6     |+---------+---------+11 rows in set

2.4 带IN关键字的查询

　　IN操作符用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。

mysql> SELECT s_id,f_name,f_price    -> FROM fruits    -> WHERE s_id IN(101,102)    -> ORDER BY f_name;+------+------------+---------+| s_id | f_name     | f_price |+------+------------+---------+|  101 | apple      | 5.2     ||  102 | banana     | 10.3    ||  101 | blackberry | 10.2    ||  101 | cherry     | 3.2     ||  102 | grape      | 5.3     ||  102 | orange     | 11.2    |+------+------------+---------+6 rows in set

　　可以使用关键字NOT来检索不在条件范围内的记录。

2.5 带BETWEEN AND的范围查询

　　BETWEEN AND用来查询某个范围内的值，该操作需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。

mysql> SELECT f_name,f_price    -> FROM fruits    -> WHERE f_price    -> BETWEEN 2.00 AND 10.20;+------------+---------+| f_name     | f_price |+------------+---------+| apple      | 5.2     || apricot    | 2.2     || blackberry | 10.2    || berry      | 7.6     || xxxx       | 3.6     || melon      | 8.2     || cherry     | 3.2     || lemon      | 6.4     || xbabay     | 2.6     || coconut    | 9.2     || grape      | 5.3     || xbababa    | 3.6     |+------------+---------+12 rows in set

　　可以在BETWEEN前加关键字NOT，表示指定范围之外的值。

2.6 带LIKE的字符匹配查询

　　如果要查找所有的包含字符“ge”的水果名称，需要使用通配符进行匹配查找，通过查找模式对表中的数据进行比较。使用关键字LIKE。可以和LIKE一起使用的通配符有‘%’和‘_’。
　　（1）百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

查询以‘b’开头的水果：mysql> SELECT f_id,f_name    -> FROM fruits    -> WHERE f_name LIKE 'b%';+------+------------+| f_id | f_name     |+------+------------+| b1   | blackberry || b2   | berry      || t1   | banana     |+------+------------+3 rows in set查询包含字母‘g’的水果名称：mysql> SELECT f_id,f_name    -> FROM fruits    -> WHERE f_name LIKE '%g%';+------+--------+| f_id | f_name |+------+--------+| bs1  | orange || m1   | mango  || t2   | grape  |+------+--------+3 rows in set查询以‘b’开头，以‘y’结尾的水果名：mysql> SELECT f_name    -> FROM fruits    -> WHERE f_name LIKE 'b%y';+------------+| f_name     |+------------+| blackberry || berry      |+------------+2 rows in set

　　（2）下划线通配符‘_’，一次只能匹配任意一个字符。例如，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录：

mysql> SELECT f_id,f_name    -> FROM fruits    -> WHERE f_name LIKE '____y';+------+--------+| f_id | f_name |+------+--------+| b2   | berry  |+------+--------+1 row in set

2.7 查询空值

　　数据表创建的时候，可以指定某列中是否可以包含空值（NULL）。空值不同于0，也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。

SELECT f_id,f_nameFROM fruitsWHERE f_price IS NULL;

　　与IS NULL相反的是NOT IS NULL。

2.8 带AND的多条件查询

　　使用SELECT查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。可以在WHERE子句中使用AND操作符连接两个甚至多个查询条件，多个条件表达式之间用AND分开。

mysql> SELECT f_id,f_price,f_name    -> FROM fruits    -> WHERE s_id='101' AND f_price>=5;+------+---------+------------+| f_id | f_price | f_name     |+------+---------+------------+| a1   | 5.2     | apple      || b1   | 10.2    | blackberry |+------+---------+------------+2 rows in set

2.9 带OR的多条件查询

　　与AND相反，在WHERE声明中使用OR操作符，表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个或多个查询条件。

mysql> SELECT s_id,f_name,f_price    -> FROM fruits    -> WHERE s_id=101 OR s_id=102;+------+------------+---------+| s_id | f_name     | f_price |+------+------------+---------+|  101 | apple      | 5.2     ||  101 | blackberry | 10.2    ||  102 | orange     | 11.2    ||  101 | cherry     | 3.2     ||  102 | banana     | 10.3    ||  102 | grape      | 5.3     |+------+------------+---------+6 rows in set

　　在这里，也可以使用IN操作符实现与OR相同的功能：

mysql> SELECT s_id,f_name,f_price    -> FROM fruits    -> WHERE s_id IN(101,102);+------+------------+---------+| s_id | f_name     | f_price |+------+------------+---------+|  101 | apple      | 5.2     ||  101 | blackberry | 10.2    ||  102 | orange     | 11.2    ||  101 | cherry     | 3.2     ||  102 | banana     | 10.3    ||  102 | grape      | 5.3     |+------+------------+---------+6 rows in set

　　使用IN操作符使得检索语句更加的简洁明了，并且IN执行的速度要快于OR。更重要的是，使用IN操作符，可以执行更加复杂的嵌套查询。

2.10 查询结果不重复

　　查询fruits表中所有的s_id：

mysql> SELECT s_id     -> FROM fruits;+------+| s_id |+------+|  101 ||  103 ||  101 ||  104 ||  107 ||  102 ||  105 ||  101 ||  104 ||  106 ||  105 ||  105 ||  103 ||  102 ||  102 ||  107 |+------+16 rows in set

　　返回16条记录，其中有一些重复的s_id值。在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值：

mysql> SELECT DISTINCT s_id    -> FROM fruits;+------+| s_id |+------+|  101 ||  103 ||  104 ||  107 ||  102 ||  105 ||  106 |+------+7 rows in set

2.11 对查询结果排序

　　在SELECT语句中使用ORDER BY子句，对查询结果进行排序：
　　（1）单列排序

mysql> SELECT f_name FROM fruits ORDER BY f_name;+------------+| f_name     |+------------+| apple      || apricot    || banana     || berry      || blackberry || cherry     || coconut    || grape      || lemon      || mango      || melon      || orange     || xbababa    || xbabay     || xxtt       || xxxx       |+------------+16 rows in set

　　（2）多列排序
　　有时需要根据多列值进行排序。比如，如果要显示一个学生列表，可能会有多个学生的姓氏是相同的，因此还需要根据学生的名进行排序。对多列数据进行排序，须将需要排序的列之间用逗号隔开。
　　第一列有相同的值才会对第二列进行排序。

mysql> SELECT f_name,f_price FROM fruits ORDER BY f_name,f_price;+------------+---------+| f_name     | f_price |+------------+---------+| apple      | 5.2     || apricot    | 2.2     || banana     | 10.3    || berry      | 7.6     || blackberry | 10.2    || cherry     | 3.2     || coconut    | 9.2     || grape      | 5.3     || lemon      | 6.4     || mango      | 15.6    || melon      | 8.2     || orange     | 11.2    || xbababa    | 3.6     || xbabay     | 2.6     || xxtt       | 11.6    || xxxx       | 3.6     |+------------+---------+16 rows in set

　　（３）指定排序方向
　　通过关键字ASC实现升序；关键字DESC实现降序排序。默认情况下是升序。

mysql> SELECT f_name,f_price FROM fruits ORDER BY f_price DESC;+------------+---------+| f_name     | f_price |+------------+---------+| mango      | 15.6    || xxtt       | 11.6    || orange     | 11.2    || banana     | 10.3    || blackberry | 10.2    || coconut    | 9.2     || melon      | 8.2     || berry      | 7.6     || lemon      | 6.4     || grape      | 5.3     || apple      | 5.2     || xxxx       | 3.6     || xbababa    | 3.6     || cherry     | 3.2     || xbabay     | 2.6     || apricot    | 2.2     |+------------+---------+16 rows in set

2.12 分组查询

　　分组查询是对数据按照某个或多个字段进行分组，MySQL中使用GROUP BY关键字对数据进行分组，语法：

[GROUP BY 字段][HAVING <条件表达式>]

　　字段值为进行分组时所依据的列名称；“HAVING <条件表达式>”指定满足表达式限定条件的结果将被显示
　　（1）创建分组
　　GROUP BY关键字通常和集合函数一起使用。例如，要返回每个水果供应商提供的水果种类，就要在分组过程中用到COUNT()函数，把数据分为多个逻辑组，并对每个组进行集合计算。

mysql> SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id;+------+-------+| s_id | Total |+------+-------+|  101 |     3 ||  102 |     3 ||  103 |     2 ||  104 |     2 ||  105 |     3 ||  106 |     1 ||  107 |     2 |+------+-------+7 rows in set

　　使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。

mysql> SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;+------+-------------------------+| s_id | Names                   |+------+-------------------------+|  101 | apple,blackberry,cherry ||  102 | grape,banana,orange     ||  103 | apricot,coconut         ||  104 | lemon,berry             ||  105 | xbabay,xxtt,melon       ||  106 | mango                   ||  107 | xxxx,xbababa            |+------+-------------------------+7 rows in set

　　（２）使用HAVING过滤分组
　　GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
　　HAVING关键字与WHERE关键字都是用来过滤数据。HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前用来选择记录。另外WHERE排除的记录不再包括在分组中。

mysql> SELECT s_id,GROUP_CONCAT(f_name) AS Names    -> FROM fruits    -> GROUP BY s_id HAVING COUNT(f_name)>1;+------+-------------------------+| s_id | Names                   |+------+-------------------------+|  101 | apple,blackberry,cherry ||  102 | grape,banana,orange     ||  103 | apricot,coconut         ||  104 | lemon,berry             ||  105 | xbabay,xxtt,melon       ||  107 | xxxx,xbababa            |+------+-------------------------+6 rows in set

　　（3）在GROUP BY子句中使用WITH ROLLUP
　　使用WITH ROLLUP关键字后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录总和，即统计记录数量。

mysql> SELECT s_id,COUNT(*) AS Total    -> FROM fruits    -> GROUP BY s_id WITH ROLLUP;+------+-------+| s_id | Total |+------+-------+|  101 |     3 ||  102 |     3 ||  103 |     2 ||  104 |     2 ||  105 |     3 ||  106 |     1 ||  107 |     2 || NULL |    16 |+------+-------+8 rows in set

　　（４）多字段分组
　　MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组…依次类推。

mysql> SELECT * FROM fruits GROUP BY s_id,f_name;+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| a1   |  101 | apple      | 5.2     || b1   |  101 | blackberry | 10.2    || c0   |  101 | cherry     | 3.2     || t1   |  102 | banana     | 10.3    || t2   |  102 | grape      | 5.3     || bs1  |  102 | orange     | 11.2    || a2   |  103 | apricot    | 2.2     || o2   |  103 | coconut    | 9.2     || b2   |  104 | berry      | 7.6     || l2   |  104 | lemon      | 6.4     || bs2  |  105 | melon      | 8.2     || m2   |  105 | xbabay     | 2.6     || m3   |  105 | xxtt       | 11.6    || m1   |  106 | mango      | 15.6    || t4   |  107 | xbababa    | 3.6     || b5   |  107 | xxxx       | 3.6     |+------+------+------------+---------+16 rows in set

　　（5）GROUP BY和ORDER BY一起使用
　　对分组进行排序。

mysql> SELECT s_id,COUNT(*) AS Total FROM fruits GROUP BY s_id ORDER BY Total;+------+-------+| s_id | Total |+------+-------+|  106 |     1 ||  103 |     2 ||  104 |     2 ||  107 |     2 ||  102 |     3 ||  105 |     3 ||  101 |     3 |+------+-------+7 rows in set

2.13 使用LIMIT限制查询结果的数量

　　SELECT返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT关键字，语法：

LIMIT [位置偏移量,] 行数

　　“位置偏移量”指从哪一行开始显示，可选，从0开始；“行数”指返回的记录条数。
　　（1）查询前4行

mysql> SELECT * FROM fruits LIMIT 4;+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| a1   |  101 | apple      | 5.2     || a2   |  103 | apricot    | 2.2     || b1   |  101 | blackberry | 10.2    || b2   |  104 | berry      | 7.6     |+------+------+------------+---------+4 rows in set

　　（2）从第5个记录开始，返回3个记录

mysql> SELECT * FROM fruits LIMIT 4,3;+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| b5   |  107 | xxxx   | 3.6     || bs1  |  102 | orange | 11.2    || bs2  |  105 | melon  | 8.2     |+------+------+--------+---------+3 rows in set

3 使用集合函数查询

函数作用 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值的和

4 说明

阅读《MySQL5.5 从零开始学》笔记记录。

2 0