MySQl基本查询

最新推荐文章于 2023-03-22 00:06:35 发布

chengqiuming

最新推荐文章于 2023-03-22 00:06:35 发布

阅读量570

点赞数 1

分类专栏： Mysql 文章标签： Mysql

本文链接：https://blog.csdn.net/chengqiuming/article/details/104221262

版权

Mysql 专栏收录该内容

94 篇文章 7 订阅

订阅专栏

一数据准备

首先定义数据表，输入语句如下。

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

插入如下数据：

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

使用SELECT语句查询f_id字段的数据。

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 (0.02 sec)

二表单查询

1 从fruits表中检索所有字段的数据。

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.60 |
| 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)

2 查询fruits表中f_name列所有水果名称。

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)

3 从fruits表中获取f_name和f_price两列

该语句使用SELECT声明从fruits表中获取名称为f_name和f_price两个字段下的所有水果名称和价格，两个字段之间用逗号分隔开。

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.60 |
| 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)

4 查询价格为10.2元的水果的名称。

SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| blackberry |   10.20 |
+------------+---------+
1 row in set (0.02 sec)

5 查找名称为“apple”的水果的价格。

SELECT f_name, f_price
FROM fruits
WHERE f_name = 'apple';
+--------+---------+
| f_name | f_price |
+--------+---------+
| apple  |    5.20 |
+--------+---------+
1 row in set (0.00 sec)

6 查询价格小于10的水果的名称。

SELECT f_name, f_price
FROM fruits
WHERE f_price < 10;
+---------+---------+
| 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.01 sec)

7 s_id为101和102的记录。

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)

8 查询所有s_id不等于101也不等于102的记录。

SELECT s_id,f_name, f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;
+------+---------+---------+
| s_id | f_name  | f_price |
+------+---------+---------+
|  103 | apricot |    2.20 |
|  104 | berry   |    7.60 |
|  103 | coconut |    9.20 |
|  104 | lemon   |    6.40 |
|  106 | mango   |   15.60 |
|  105 | melon   |    8.20 |
|  107 | xbababa |    3.60 |
|  105 | xbabay  |    2.60 |
|  105 | xxtt    |   11.60 |
|  107 | xxxx    |    3.60 |
+------+---------+---------+
10 rows in set (0.00 sec)

9 查询价格在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 |
+------------+---------+
12 rows in set (0.00 sec)

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

SELECT f_name, f_price
FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;
+--------+---------+
| f_name | f_price |
+--------+---------+
| orange |   11.20 |
| mango  |   15.60 |
| xxtt   |   11.60 |
| banana |   10.30 |
+--------+---------+
4 rows in set (0.00 sec)

11 查找所有以'b'字母开头的水果。

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)

12 在fruits表中，查询f_name中包含字母'g'的记录。

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 (0.00 sec)

13 查询以''b开头，并以'y'结尾的水果的名称。

SELECT f_name
FROM fruits
WHERE f_name LIKE 'b%y';
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 (0.00 sec)

14 在fruits表中，查询以字母'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 (0.00 sec)

三条件查询

在数据库中创建数据表customers，该表中包含了本章中需要用到的数据。

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

为了演示需要插入数据，插入执行以下语句。

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 COUNT(*) AS cust_num  FROM customers;
+----------+
| cust_num |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

15 查询customers表中c_email为空的记录的c_id、c_name和c_email字段值。

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

16 查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值。

mysql> SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;
+-------+---------+-------------------+
| c_id  | c_name  | c_email           |
+-------+---------+-------------------+
| 10001 | RedHook | LMing@163.com     |
| 10002 | Stars   | Jerry@hotmail.com |
| 10004 | JOTO    | sam@hotmail.com   |
+-------+---------+-------------------+
3 rows in set (0.00 sec)

17 在fruits表中查询s_id = 101，并且f_price大于等于5的水果价格和名称。

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.20 | apple      |
| b1   |   10.20 | blackberry |
+------+---------+------------+
2 rows in set (0.00 sec)

18 在fruits表中查询s_id = 101或者102，且f_price大于5，并且f_name=‘apple’的水果价格和名称。

mysql> SELECT f_id, f_price, f_name FROM fruits WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple';
+------+---------+--------+
| f_id | f_price | f_name |
+------+---------+--------+
| a1   |    5.20 | apple  |
+------+---------+--------+
1 row in set (0.00 sec)

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

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)

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

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

21 查询fruits表中s_id字段的值，返回s_id字段值且不得重复。

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

22 查询fruits表的f_name字段值，并对其进行排序。

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

23 查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序。

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.60 |
| 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)

24 查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序。

mysql> SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;
+------------+---------+
| f_name     | f_price |
+------------+---------+
| mango      |   15.60 |
| xxtt       |   11.60 |
| orange     |   11.20 |
| banana     |   10.30 |
| blackberry |   10.20 |
| coconut    |    9.20 |
| melon      |    8.20 |
| berry      |    7.60 |
| lemon      |    6.40 |
| grape      |    5.30 |
| apple      |    5.20 |
| xxxx       |    3.60 |
| xbababa    |    3.60 |
| cherry     |    3.20 |
| xbabay     |    2.60 |
| apricot    |    2.20 |
+------------+---------+
16 rows in set (0.00 sec)

25 查询fruits表，先按f_price降序排序，再按f_name字段升序排序。

mysql> SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name;
+---------+------------+
| f_price | f_name     |
+---------+------------+
|   15.60 | mango      |
|   11.60 | xxtt       |
|   11.20 | orange     |
|   10.30 | banana     |
|   10.20 | blackberry |
|    9.20 | coconut    |
|    8.20 | melon      |
|    7.60 | berry      |
|    6.40 | lemon      |
|    5.30 | grape      |
|    5.20 | apple      |
|    3.60 | xbababa    |
|    3.60 | xxxx       |
|    3.20 | cherry     |
|    2.60 | xbabay     |
|    2.20 | apricot    |
+---------+------------+
16 rows in set (0.00 sec)

26 根据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 |
+------+-------+
7 rows in set (0.00 sec)

27 根据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            |
+------+-------------------------+
7 rows in set (0.00 sec)

28 根据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            |
+------+-------------------------+
6 rows in set (0.00 sec)

29 根据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 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);

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

返回的结果中orderTotal列的总订单价格并没有按照一定顺序显示，接下来，使用ORDER BY关键字按总订单价格排序显示结果。

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

31 显示fruits表查询结果的前4行。

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)

32 在fruits表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录。

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)