mysql查询

最新推荐文章于 2023-05-11 09:48:35 发布

河静

最新推荐文章于 2023-05-11 09:48:35 发布

阅读量1.2k

点赞数 2

分类专栏： Database/HA 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_44839276/article/details/121289384

版权

Database/HA 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

mysql查询

mysql> desc fruits;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id    | char(10)     | NO   | PRI | NULL    |       |
| s_id    | int(11)      | NO   |     | NULL    |       |
| f_name  | char(255)    | NO   |     | NULL    |       |
| f_price | decimal(8,2) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

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 |
+------+------+------------+---------+

1.带in关键字的查询

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

查询s_id为101和102的记录

方法一：

mysql> select * from fruits where s_id=101 or s_id=102 or s_id=107;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| c0   |  101 | cherry     |    3.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

方法二：

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 |
+------+------------+---------+

相反可以使用not来检索不在范围内的记录

mysql> select s_id,f_name,f_price from fruits where s_id not in (101,102) order by f_name;

2.带between and的范围查询

between and用来查询某个范围内的值，该操作需要两个参数，即开始值和结束值，如果字段满足指定范围内的值则记录返回

查询价格在2.00元到10.20元之间的水果名称和价格

mysql> select f_name,f_price from fruits where f_price between 2.00 and 10.20;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| apple      |    5.20 |
| apricot    |    2.20 |
| blackberry |   10.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 |
+------------+---------+

如果查询价格在2元到10.2元之外的水果名称和价格，加一个not即可

mysql> select f_name,f_price from fruits where f_price not between 2.00 and 10.20;

3.带like的字符匹配查询

3.1 百分号%，匹配任意长度的字符，包括零字符

查询所有以'b'开头的水果

mysql> select f_name from fruits where f_name like 'b%';
+------------+
| f_name     |
+------------+
| blackberry |
| berry      |
| banana     |
+------------+

查询f_name中包含g的记录

mysql> select f_name from fruits where f_name like '%g%';

查询以b开头并以y结尾的水果的名称

mysql> select f_name from fruits where f_name like 'b%y';

3.2 下划线_，一次只能匹配一个字符

下划线_只能匹配任意单个字符，如果要匹配多个就使用相同的_个数

查询以字母y结尾，且y前面只有4个字母的记录

mysql> select f_name from fruits where f_name like '____y';
+--------+
| f_name |
+--------+
| berry  |
+--------+

查询fruits表中，f_price为空值的f_name,s_id

mysql> select f_name,s_id from fruits where f_price is null;

4.带and的查询

查询s_id=10并且f_price大于等于5的水果价格和名称

mysql> select f_price,f_name from fruits where s_id=101 and f_price >=5;
+------+---------+------------+
| s_id | f_price | f_name     |
+------+---------+------------+
|  101 |    5.20 | apple      |
|  101 |   10.20 | blackberry |
+------+---------+------------+

查询s_id=101或者102，且f_price大于5，并且f_name='apple' 的水果价格和名称

mysql> select f_name,f_price from fruits where s_id in ('101','102') and f_price >=5 and f_name='apple';

5.带or的多条件查询

只要满足一个条件就可以，可以使用多个or查询

查询s_id=101或者s_id=102的水果供应商的f_price和f_name

mysql> select f_price,f_name from fruits where s_id=101 or s_id=102;

这里也可以使用in来实现

mysql> select f_price,f_name from fruits where s_id in (101,102);

6.查询结果不重复

使用distinct关键字指示mysql消除重复的记录

语法：select distinct s_id from fruits;

7.对查询结果进行排序

查询表中f_name字段的值并排序

默认升序A-Z

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       |
+------------+

上述结果反过来排序使用desc

mysql> select f_name from fruits order by f_name desc;
+------------+
| f_name     |
+------------+
| xxxx       |
| xxtt       |
| xbabay     |
| xbababa    |
| orange     |
| melon      |
| mango      |
| lemon      |
| grape      |
| coconut    |
| cherry     |
| blackberry |
| berry      |
| banana     |
| apricot    |
| apple      |
+------------+

8.分组查询

分组查询是对数据按照某个或某个字段进行分组，mysq使用group by关键字对数据进行分组，语法格式：

[group by 字段] [having <条件表达式>]

8.1 创建分组

根据s_id对fruits表中的数据进行分组：

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 |
+------+-------+

#s_id表示水果供应商的ID，Total使用count()函数计算得出
#group by子句按照s_id排序并对数据进行分组

查看每个水果供应商供应的水果种类的名称，mysql在group by字节中使用group_concat()函数，将每个分组中各个字段的值显示出来

根据s_id对fruits表中的数据进行分组，并将每个供应商的水果名称显示出来

mysql> select s_id,group_concat(f_name) as names from fruits group by s_id;
+------+-------------------------+
| s_id | names                   |
+------+-------------------------+
|  101 | apple,blackberry,cherry |
|  102 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  106 | mango                   |
|  107 | xxxx,xbababa            |
+------+-------------------------+
#group_concat函数将每个分组的名称显示出来了，名称的个数和count查询出来的个数一样

8.2 使用having过滤分组

group by和having连用，限定显示记录所需满足的条件，只有满足条件的分组才会被显示

根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息

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 | orange,banana,grape     |
|  103 | apricot,coconut         |
|  104 | berry,lemon             |
|  105 | melon,xbabay,xxtt       |
|  107 | xxxx,xbababa            |
+------+-------------------------+

#having和where的区别：
having在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录，where排除的记录不再包括在分组中

8.3 在group by中使用with rollup

作用：在所有查询的记录之后，增加一条记录，该记录计算查询所有记录的总和

根据s_id对fruits表中的数据进行分组，并显示记录数量

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.4 多字段分组

使用group by进行多个字段的分组，group by后面跟需要分组的字段

mysql根据多字段的值来进行分组，分组层次从左往右

先按第一个字段进行分组，然后在第一个字段值形同的记录中，在根据第二个字段值进行分组，以此类推

根据s_id和f_name字段对fruits表中的数据进行分组

mysql> select * from fruits group by s_id,f_name;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| c0   |  101 | cherry     |    3.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| bs1  |  102 | orange     |   11.20 |
| a2   |  103 | apricot    |    2.20 |
| o2   |  103 | coconut    |    9.20 |
| b2   |  104 | berry      |    7.60 |
| l2   |  104 | lemon      |    6.40 |
| bs2  |  105 | melon      |    8.20 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| m1   |  106 | mango      |   15.70 |
| t4   |  107 | xbababa    |    3.60 |
| b5   |  107 | xxxx       |    3.60 |
+------+------+------------+---------+

如果这里报错的话，查看链接解决https://blog.csdn.net/qq_44839276/article/details/121286088

8.5 group by和order by一起使用

order by用来对查询的记录进行排序，如果想对查询后的分组进行排序，需要配合group by一起使用

建表orderitems

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);

查询订单价格大于100的订单号和总订单价格

mysql> select o_num,sum(quantity * item_price) as orderTotal from orderitems group by o_num having sum(quantity * item_price)>=100 order by orderTotal;
+-------+------------+
| o_num | orderTotal |
+-------+------------+
| 30004 |     125.00 |
| 30005 |     236.85 |
| 30001 |     268.80 |
| 30003 |    1000.00 |
+-------+------------+

9.使用limit限制查询结果的数量

点我查看详细使用

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.00 sec)

官方说的话翻译过来就是：

1.limit 3—获取前三行数据，也就是1，2，3

2.limit 4，3—跳过四条数据，从第五条开始取，取三条，也就是5，6，7

3.limit 4 offset 3—跳过三条数据，从第四行开始取，取4条，也就是4，5，6，7

河静

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql查询

mysql查询mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id | char(10) | NO | PRI | NULL |
复制链接

扫一扫