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,...,字段名n
FROM 表名
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 set
mysql> 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_name
FROM fruits
WHERE 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
(3)指定排序方向
通过关键字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
(2)使用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
(4)多字段分组
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 从零开始学》笔记记录。