查询数据
表单查询
单表查询是指从一张表数据中查询所需的数据。主要有:查询所有字段、查询指定字段、查询指定记录、查询
空值、多条件的查询、对查询结果进行排序等方式。
查询所有字段
在select语句中使用星号()通配符查询所有字段。 select查询记录最简单的形式是从一个表中检索所有
记录,实现的方法是使用星号()通配符指定查找所有列的名称。
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.01 sec)
查询指定字段
select 字段名 from 表名;
mysql> select f_name from fruits;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
+------------+
16 rows in set (0.00 sec)
2. 查询多个字段 使用select声明,可以获取多个字段下的数据,只需要在关键字select后面指定要查询的字
段的名称,不同字段名称之间用逗号分隔,最后一个字段后面不需要加逗号
select 字段1,字段2,字段3 ....,字段n from 表名;
mysql> select f_name,f_price from fruits;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| orange | 11.20 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| mango | 15.70 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| coconut | 9.20 |
| banana | 10.30 |
| grape | 5.30 |
| xbababa | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
查询指定记录
select 字段1,字段2....字段n from 表名 where 查询条件;
操作符 说明
= 相等
<>,!= 不相等
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 位于两端之间
mysql> select f_name,f_price from fruits where f_price = 10.20;
+------------+---------+
| f_name | f_price |
+------------+---------+
| blackberry | 10.20 |
+------------+---------+
1 row in set (0.00 sec)
带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.20 |
| 102 | banana | 10.30 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
+------+------------+---------+
6 rows in set (0.00 sec)
带between and的范围查询
Between and用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,如果字段值满足
指定的范围查询条件,则这些记录被返回。
mysql> select f_name,f_price from fruits where f_price betweenn
+---------+---------+
| f_name | f_price |
+---------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
+---------+---------+
11 rows in set (0.00 sec)
带like的字符匹配查询
通配符是一种在SQL的where条件子句中拥有特殊意思的字符,SQL语句中支持多种通配符,可以和like一起使
用的通配符有‘%’和‘_’。
1. 百分号(%)通配符,匹配任意长度的字符,甚至包括零字符
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 (0.00 sec)
mysql> select f_id,f_name from fruits where f_name like 'a%';
+------+---------+
| f_id | f_name |
+------+---------+
| a1 | apple |
| a2 | apricot |
+------+---------+
2 rows in set (0.00 sec)
下划线(__)通配符,一次只能匹配任意一个字符
mysql> select f_id,f_name from fruits where f_name like '_____y';
+------+--------+
| f_id | f_name |
+------+--------+
| c0 | cherry |
| m2 | xbabay |
+------+--------+
2 rows in set (0.00 sec)
查询空值
数据表创建的时候,设计者可以指定某列中是否可以包含空值(NULL)。空值不同于0,也不同于空字符串。空
值一般表示数据未知、不适用或将在以后添加数据。在select语句中使用IS NULL子句,可以查询某字段内容为
空的记录。
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(50) null,
c_contact char(50) null,
c_email char(50) null,
primary key(c_id)
);
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’);
创表
mysql> SELECT * FROM customers
-> ;
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| c_id | c_name | c_address | c_city | c_zip | c_contact | c_email |
+-------+----------+---------------------+---------+--------+-----------+-------------------+
| 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 |
+-------+----------+---------------------+---------+--------+-----------+-------------------+
4 rows in set (0.00 sec)
查询 select c_id,c_name,c_email from customers where c_email IS NULL;
mysql> select c_id,c_name,c_email from customers where c_email IS NULL;
+-------+----------+---------+
| c_id | c_name | c_email |
+-------+----------+---------+
| 10003 | Netbhood | NULL |
+-------+----------+---------+
1 row in set (0.00 sec)
带and的多条件查询
使用select查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确。MySQL在where子句中使用
and操作符限定只有满足所有查询条件的记录才会被返回。可以使用and连接两个甚至多个查询条件,多个条件
表达式之间用and分开
mysql> select s_id,f_price,f_name from fruits where s_id = '1001' and f_price >=5;
+------+---------+------------+
| s_id | f_price | f_name |
+------+---------+------------+
| 101 | 5.20 | apple |
| 101 | 10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.01 sec)
带or的多条件查询
与and相反,在where声明中使用or操作符,表示只需要满足其中一个条件的记录即可返回。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.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
+------+------------+---------+
6 rows in set (0.00 sec)
查询结果不重复
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 (0.00 sec)
mysql> select distinct s_id from fruits;
+------+
| s_id |
+------+
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
+------+
7 rows in set (0.00 sec)
对查询结果排序
mysql> select f_name from fruits;
+------------+
| f_name |
+------------+
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
+------------+
16 rows in set (0.00 sec)
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 (0.00 sec)
多列排序
mysql> select f_name,f_price from fruits order by f_name,f_price;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.70 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.00 sec)
指定排序方向
默认情况下,查询数据按字母升序进行排序(从A~Z),但数据的排序并不仅限于此,还可以使用order by对查
询结果进行降序排序(从Z~A),这可以通过关键字DESC实现。
mysql> select f_name,f_price from fruits order by f_name,f_price DESC;
+------------+---------+
| f_name | f_price |
+------------+---------+
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.70 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
+------------+---------+
16 rows in set (0.01 sec)
分组查询
分组插叙是对数据按照某个或多个字段进行分组,MySQL中使用group by关键字对数据进行分组,基本语法形
式为:group by 字段 1、创建分组
Group by 关键字通常和集合函数一起使用,例如:MAX()、MIN()、COUNT()、SUM()、AVG()。 根据s_id对
fruits表中的数据进行分组
mysql> select s_id,count(*) as total from fruits group by s_id;d;
+------+-------+
| s_id | total |
+------+-------+
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
+------+-------+
7 rows in set (0.00 sec)
as 别名 group by 分组
根据s_id对fruits表中的数据进行分组,将每个供应商的水果名称显示出来
mysql> select s_id,group_concat(f_name) as name from fruits group by s_id;
+------+-------------------------+
| s_id | name |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
+------+-------------------------+
7 rows in set (0.00 sec)
使用having过滤分组 根据s_id对fruits表中的数据进行分组,并显示水果种类大于1的分组信息
mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) >1;
+------+-------------------------+
| s_id | name |
+------+-------------------------+
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 107 | xxxx,xbababa |
+------+-------------------------+
6 rows in set (0.00 sec)
having 再次过滤
在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 (0.00 sec)
多字段分组 使用group by可以对多个字段进行分组,group by关键字后面跟需要分组的字段,MySQL根据
多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中,
再根据第2个字段的值进行分组,以此类推。
mysql> select s_id,f_name from fruits group by s_id,f_name;
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
| 102 | banana |
| 102 | grape |
| 102 | orange |
| 103 | apricot |
| 103 | coconut |
| 104 | berry |
| 104 | lemon |
| 105 | melon |
| 105 | xbabay |
| 105 | xxtt |
| 106 | mango |
| 107 | xbababa |
| 107 | xxxx |
+------+------------+
16 rows in set (0.00 sec)
建表
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)
);
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’);
mysql> select * from orderitems;
+-------+--------+------+----------+------------+
| o_num | o_item | f_id | quantity | item_price |
+-------+--------+------+----------+------------+
| 30001 | 1 | a1 | 10 | 5.20 |
| 30001 | 2 | b2 | 3 | 7.60 |
| 30001 | 3 | bs1 | 5 | 11.20 |
| 30001 | 4 | bs2 | 15 | 9.20 |
| 30002 | 1 | b3 | 2 | 20.00 |
| 30003 | 1 | c0 | 100 | 10.00 |
| 30004 | 1 | o2 | 50 | 2.50 |
| 30005 | 1 | c0 | 5 | 10.00 |
| 30005 | 2 | b1 | 10 | 8.99 |
| 30005 | 3 | a2 | 10 | 2.20 |
| 30005 | 4 | m1 | 5 | 14.99 |
+-------+--------+------+----------+------------+
11 rows in set (0.00 sec)
查询价格大于100的订单号和总价订单价格
mysql> select o_num,sum(quantity*item_price) as ordertotal
-> from orderitems
-> group by o_num
-> having sum(quantity*item_price) >= 100;
+-------+------------+
| o_num | ordertotal |
+-------+------------+
| 30001 | 268.80 |
| 30003 | 1000.00 |
| 30004 | 125.00 |
| 30005 | 236.85 |
+-------+------------+
4 rows in set (0.00 sec)
使用limit限制查询结果的数量 select返回所有匹配的行,有可能是表中所有的行,如仅仅需要返回第一
行或者前几行,使用limit关键字,语法格式如下:limit [位置偏移量] 行数
mysql> select * from fruits limit 4;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)
mysql> select * from fruits limit 4,3;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
+------+------+--------+---------+
3 rows in set (0.00 sec)
6.3 使用聚合函数查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VBwusCR5-1608104162855)(C:\Users\Administrator\Desktop\2\11.png)]
**count()**函数
Count()函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数。 Count(*)计算表
中总的函数,不管某列有数列或者为空值 Count(字段名)计算指定列下总的行数,计算时将忽略空值的行
mysql> select count(*) as cust_num
-> from customers;
+----------+
| cust_num |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select count(c_email) as email_num
-> from customers;
+-----------+
| email_num |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
mysql> select o_num,count(f_id)
-> from orderitems
-> group by o_num;
+-------+-------------+
| o_num | count(f_id) |
+-------+-------------+
| 30001 | 4 |
| 30002 | 1 |
| 30003 | 1 |
| 30004 | 1 |
| 30005 | 4 |
+-------+-------------+
5 rows in set (0.00 sec)
**sum()**函数
mysql> select sum(quantity) as items_total
-> from orderitems
-> where o_num = 30005;
+-------------+
| items_total |
+-------------+
| 30 |
+-------------+
1 row in set (0.01 sec)
mysql> select o_num,sum(quantity) as items_total
-> from orderitems
-> group by o_num;
+-------+-------------+
| o_num | items_total |
+-------+-------------+
| 30001 | 33 |
| 30002 | 2 |
| 30003 | 100 |
| 30004 | 50 |
| 30005 | 30 |
+-------+-------------+
5 rows in set (0.00 sec)
sum()函数在计算时,忽略列值为NULL的行。
**avg()**函数
avg()函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值
mysql> select avg(f_price) as avg_price
-> from fruits
-> where s_id=103;
+-----------+
| avg_price |
+-----------+
| 5.700000 |
+-----------+
1 row in set (0.00 sec)
mysql> select s_id,avg(f_price) as avg_price
-> from fruits
-> group by s_id;
+------+-----------+
| s_id | avg_price |
+------+-----------+
| 101 | 6.200000 |
| 102 | 8.933333 |
| 103 | 5.700000 |
| 104 | 7.000000 |
| 105 | 7.466667 |
| 106 | 15.700000 |
| 107 | 3.600000 |
+------+-----------+
7 rows in set (0.00 sec)
**max()**函数
max()返回指定列中的最大值
mysql> select max(f_price) as max_price from fruits;
+-----------+
| max_price |
+-----------+
| 15.70 |
+-----------+
1 row in set (0.00 sec)
mysql> select s_id,max(f_price) as max_price
-> from fruits
-> group by s_id;
+------+-----------+
| s_id | max_price |
+------+-----------+
| 101 | 10.20 |
| 102 | 11.20 |
| 103 | 9.20 |
| 104 | 7.60 |
| 105 | 11.60 |
| 106 | 15.70 |
| 107 | 3.60 |
+------+-----------+
7 rows in set (0.00 sec)
**min()**函数
min()返回查询列中的最小值
mysql> select min(f_price) as min_price from fruits;
+-----------+
| min_price |
+-----------+
| 2.20 |
+-----------+
1 row in set (0.00 sec)
mysql> select s_id,min(f_price) as max_price from fruits group by s_id;
+------+-----------+
| s_id | max_price |
+------+-----------+
| 101 | 3.20 |
| 102 | 5.30 |
| 103 | 2.20 |
| 104 | 6.40 |
| 105 | 2.60 |
| 106 | 15.70 |
| 107 | 3.60 |
+------+-----------+
7 rows in set (0.00 sec)
连接查询
连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询,主要包括内连接、外连接。通过
连接运算符可以实现多个表查询。在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个
实体的所有信息存放在一个表中。当查询数据时,通过连接操作查询出存放在多个表中的不同实体的信息。当
两个或多个表现中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接查询。
创表
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)
);
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’);
mysql> select * from suppliers
-> ;
+------+----------------+-----------+--------+--------+
| s_id | s_name | s_city | s_zip | s_call |
+------+----------------+-----------+--------+--------+
| 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 |
+------+----------------+-----------+--------+--------+
7 rows in set (0.00 sec)
内连接查询
内连接(inner join)使用比较运算符进行表间某些列数据的比较操作,并列出这些表中与连接条件相匹配的数
据行,组合成新纪录,也就是说,在内连接查询中,只有满足条件的记录才能出现在结果关系中。
mysql> select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id = suppliers.s_id;
+------+----------------+------------+---------+
| s_id | s_name | f_name | f_price |
+------+----------------+------------+---------+
| 101 | FastFruit Inc. | apple | 5.20 |
| 103 | acme | apricot | 2.20 |
| 101 | FastFruit Inc. | blackberry | 10.20 |
| 104 | fnk inc. | berry | 7.60 |
| 107 | dk inc. | xxxx | 3.60 |
| 102 | LT Supplies | orange | 11.20 |
| 105 | good set | melon | 8.20 |
| 101 | FastFruit Inc. | cherry | 3.20 |
| 104 | fnk inc. | lemon | 6.40 |
| 106 | just eat ours | mango | 15.70 |
| 105 | good set | xbabay | 2.60 |
| 105 | good set | xxtt | 11.60 |
| 103 | acme | coconut | 9.20 |
| 102 | LT Supplies | banana | 10.30 |
| 102 | LT Supplies | grape | 5.30 |
| 107 | dk inc. | xbababa | 3.60 |
+------+----------------+------------+---------+
16 rows in set (0.00 sec)
自连接
如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接查询。自连接是一种特殊的内连
接,它是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表
mysql> select f1.f_id,f1.f_name
-> from fruits as f1, fruits as f2
-> where f1.s_id = f2.s_id and f2.f_id = 'a1';
+------+------------+
| f_id | f_name |
+------+------------+
| a1 | apple |
| b1 | blackberry |
| c0 | cherry |
+------+------------+
3 rows in set (0.00 sec)
外连接查询
外连接查询将将查询多个表中相关联的行,内连接时,返回查询结果集合中的仅是符合查询条件和连接
条件的行。但有时候需要包含没有关联的行中数据,即返回查询结果集合中的不仅包含符合连接条件的
行,而且还包含左表(左外连接或左连接)、右表(右外连接或右连接)或两个连接表(全外连接)中
的所有数据行。外连接分为左外连接或左连接和右外连接或右连接
Left join(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。 Right join(右连接):返回
包括右表中的所有记录和左表中连接字段相等的记录。
mysql> create table orders
-> (
-> o_num int not null auto_increment,
-> o_date datetime not null,
-> c_id int not null,
-> primary key(o_num) 主键
-> ) ;
Query OK, 0 rows affected (0.01 sec)
mysql> 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);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
左链接
mysql> select customers.c_id,orders.o_num
-> from customers left outer join orders
-> on customers.c_id = orders.c_id;
+-------+-------+
| c_id | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| 10001 | 30005 |
| 10002 | NULL |
+-------+-------+
5 rows in set (0.00 sec)
右链接
ysql> select customers.c_id,orders.o_num from customers right join orders on customers.c_id = orders.c_id;
+-------+-------+
| c_id | o_num |
+-------+-------+
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| NULL | 30004 |
| 10001 | 30005 |
+-------+-------+
5 rows in set (0.00 sec)
复合条件连接查询
复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询的结果,使查询的结果更加准确。
mysql> select customers.c_id,orders.o_num
-> from customers inner join orders
-> on customers.c_id = orders.c_id and customers.c_id = 10001;
+-------+-------+
| c_id | o_num |
+-------+-------+
| 10001 | 30001 |
| 10001 | 30005 |
+-------+-------+
2 rows in set (0.03 sec)
mysql> select suppliers.s_id,s_name,f_name,f_price
-> from fruits inner join suppliers
-> on fruits.s_id = suppliers.s_id
-> order by fruits.s_id;
+------+----------------+------------+---------+
| s_id | s_name | f_name | f_price |
+------+----------------+------------+---------+
| 101 | FastFruit Inc. | apple | 5.20 |
| 101 | FastFruit Inc. | blackberry | 10.20 |
| 101 | FastFruit Inc. | cherry | 3.20 |
| 102 | LT Supplies | orange | 11.20 |
| 102 | LT Supplies | banana | 10.30 |
| 102 | LT Supplies | grape | 5.30 |
| 103 | acme | apricot | 2.20 |
| 103 | acme | coconut | 9.20 |
| 104 | fnk inc. | berry | 7.60 |
| 104 | fnk inc. | lemon | 6.40 |
| 105 | good set | melon | 8.20 |
| 105 | good set | xbabay | 2.60 |
| 105 | good set | xxtt | 11.60 |
| 106 | just eat ours | mango | 15.70 |
| 107 | dk inc. | xxxx | 3.60 |
| 107 | dk inc. | xbababa | 3.60 |
+------+----------------+------------+---------+
16 rows in set (0.00 sec)
子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从mysql4.1开始引入。在select子句中先计
算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。子查询中常用的操
作符有any(some)、all、in、exists。子查询可以添加到select、update和delete语句中,而且可以进行多层嵌
套。子查询中也可以使用比较运算符,如“<”,“<=”,“>”,“>=”和“!=”等。
带any、some关键字的子查询
any和some关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列进行比
较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。
创表
mysql> create table tb1(num1 int not null);
Query OK, 0 rows affected (0.03 sec)
mysql> create table tb2(num2 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb1 values(1),(5),(13),(27);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into tb2 values(6),(14),(11),(20);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select num1 from tb1 where num1 > any (select num2 from tb2);
+------+
| num1 |
+------+
| 13 |
| 27 |
+------+
2 rows in set (0.01 sec)**
带exists关键字的子查询
mysql> select * from fruits where exists (select s_name from suppliers where s_id
= 107);
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
带in关键字的子查询
in关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句
进行比较操作。
mysql> select c_id from orders where o_num in
-> (select o_num from orderitems where f_id = 'c0');
+-------+
| c_id |
+-------+
| 10004 |
| 10001 |
+-------+
2 rows in set (0.00 sec)
带比较运算符的子查询
mysql> select s_id,f_name from fruits
-> where s_id =
-> (select s1.s_id from suppliers as s1 where s1.s_city='tianjin');
+------+------------+
| s_id | f_name |
+------+------------+
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
+------+------------+
3 rows in set (0.00 sec)
合并查询结果
利用union关键字,可以给出多条select语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数
和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。union不使用关键字all,执行的时
候删除重复的记录,所有返回的行都是唯一的;使用关键字all的作用是不删除重复行也不对结果进行自动排
序
mysql> select s_id,f_name,f_price
-> from fruits
-> where f_price < 9.0
-> union all
-> select s_id,f_name,f_price
-> from fruits
-> where s_id in (101,103);
+------+------------+---------+
| s_id | f_name | f_price |
+------+------------+---------+
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 107 | xxxx | 3.60 |
| 105 | melon | 8.20 |
| 101 | cherry | 3.20 |
| 104 | lemon | 6.40 |
| 105 | xbabay | 2.60 |
| 102 | grape | 5.30 |
| 107 | xbababa | 3.60 |
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 103 | coconut | 9.20 |
+------+------------+---------+
15 rows in set (0.00 sec)
union all 合并所有 去掉all 去掉重复的
union和union all的区别:使用union all的功能是不删除重复行,加上all关键字语句执行时所需要的资源少,所
以尽可能地使用它,因此知道有重复行但是想保留这些行,确定查询结果中不会有重复数据或者不需要去掉重
复数据的时候,应当使用union all以提高查询效率
为表和字段取别名
mysql> select * from orders as o
-> where o.o_num = 30001;
+-------+---------------------+-------+
| o_num | o_date | c_id |
+-------+---------------------+-------+
| 30001 | 2008-09-01 00:00:00 | 10001 |
+-------+---------------------+-------+
1 row in set (0.00 sec)
为字段取别名
mysql> select f1.f_name as fruits_name ,f1.f_price as fruits_price
-> from fruits as f1
-> where f1.f_price < 8;
+-------------+--------------+
| fruits_name | fruits_price |
+-------------+--------------+
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| grape | 5.30 |
| xbababa | 3.60 |
+-------------+--------------+
9 rows in set (0.01 sec)
查询以特定字符或字符串开头的记录
mysql> select * from fruits where f_name regexp '^b';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
查询以特定字符或字符串结尾的记录
mysql> select * from fruits where f_name regexp 'y$';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
+------+------+------------+---------+
4 rows in set (0.00 sec)
用符合‘.’来代替字符串中的任意一个字符
mysql> select * from fruits where f_name regexp 'a.g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.70 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
使用“*”和“+”来匹配多个字符
mysql> select * from fruits where f_name regexp '^ba*';
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
+------+------+------------+---------+
3 rows in set (0.00 sec)
mysql> select * from fruits where f_name regexp '^ba+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1 | 102 | banana | 10.30 |
+------+------+--------+---------+
1 row in set (0.00 sec)
匹配指定字符串
mysql> select * from fruits where f_name regexp 'on|ap';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
+------+------+---------+---------+
6 rows in set (0.01 sec)
mysql> select * from fruits where f_name like 'on';
Empty set (0.00 sec)
匹配指定字符中的任意一个
mysql> select * from fruits where f_name regexp '[ot]';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a2 | 103 | apricot | 2.20 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
+------+------+---------+---------+
7 rows in set (0.00 sec)
mysql> select * from fruits where s_id regexp '[456]';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2 | 104 | berry | 7.60 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
匹配指定字符以外的字符
mysql> select * from fruits where f_id regexp '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
12 rows in set (0.00 sec)
使用{n,}或者{n,m}来指定字符串连续出现的次数
mysql> select * from fruits where f_name regexp 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| m3 | 105 | xxtt | 11.60 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
mysql> select * from fruits where f_name regexp 'ba{1,3}';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| m2 | 105 | xbabay | 2.60 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
3 rows in set (0.00 sec)
| lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
±-----±-----±-------±--------+
6 rows in set (0.00 sec)
**匹配指定字符以外的字符**
```SH
mysql> select * from fruits where f_id regexp '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
12 rows in set (0.00 sec)
使用{n,}或者{n,m}来指定字符串连续出现的次数
mysql> select * from fruits where f_name regexp 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5 | 107 | xxxx | 3.60 |
| m3 | 105 | xxtt | 11.60 |
+------+------+--------+---------+
2 rows in set (0.00 sec)
mysql> select * from fruits where f_name regexp 'ba{1,3}';
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| m2 | 105 | xbabay | 2.60 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
3 rows in set (0.00 sec)