MySQL基本查询示例

「爱情、让人受尽委屈。」 2023-06-19 11:29 5阅读 0赞

### 1、创建所需表及插入数据 ###

mysql> 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) );
    
    mysql> 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.7'),
        -> ('m2',105,'xbabay','2.6'),
        -> ('t4',107,'xbababa','2.6'),
        -> ('m3',105,'xxtt','11.6'),
        -> ('b5',107,'xxxx','3.6');
    Query OK, 16 rows affected (0.01 sec)
    Records: 16  Duplicates: 0  Warnings: 0
    
    mysql> 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)
        -> );
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 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'),
        -> (1002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com'),
        -> (10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',NULL),
        -> (1004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0

### 2、查看两个表的所有数据 ###

![MySQL基本查询示例][MySQL]

![MySQL基本查询示例][MySQL 1]

### 3、查询fruits表中f\_name列的数据 ###

mysql> select f_name from fruits;

查询结果：

![MySQL基本查询示例][MySQL 2]

### 4、查询fruits表中f\_name和f\_price两列的数据 ###

mysql> select f_name,f_price from fruits;

返回结果如下：

![MySQL基本查询示例][MySQL 3]

### 5、查询fruits表中f\_name和f\_price的列，并且f\_price的数值等于5.2 ###

mysql> select f_name,f_price from fruits where f_price=5.2;

返回的结果如下：

![MySQL基本查询示例][MySQL 4]

### 6、查询fruits表中f\_name和f\_price的列，并且f\_price的数值大于或等于10 ###

mysql> select f_name,f_price from fruits where f_price >= 10;

返回的结果如下：

![MySQL基本查询示例][MySQL 5]

### 7、查询fruits表中f\_name和f\_price的列，并且f\_price的数值在2到8之间 ###

mysql> select f_name,f_price from fruits where f_price between 2 and 8;

返回的结果如下：

![MySQL基本查询示例][MySQL 6]

### 8、查询fruits表中的f\_name和s\_id列，并且s\_id的值为101或者103 ###

#### 查询方法1： ####

mysql> select f_name,s_id from fruits
        -> where s_id = 101 or s_id = 103;

#### 查询方法2： ####

mysql> select f_name,s_id from fruits
        -> where s_id in(101,103);

上述两种查询语句，返回的结果都一样，如下：

![MySQL基本查询示例][MySQL 7]

### 9、查询fruits表中的f\_name和s\_id列，并且s\_id的值不为101或者103 ###

#### 查询方法1： ####

mysql> select f_name,s_id from fruits
        -> where s_id != 101 and s_id != 103;

#### 查询方法2： ####

mysql> select f_name,s_id from fruits
        -> where s_id not in(101,103);

返回的结果如下：

![MySQL基本查询示例][MySQL 8]

### 10、模糊查询“%”和“\_”的使用 ###

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

返回的结果如下：

![MySQL基本查询示例][MySQL 9]

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

返回的结果如下：

![MySQL基本查询示例][MySQL 10]

mysql> select f_name from fruits where f_name like 'b___y';

![MySQL基本查询示例][MySQL 11]

### 11、查询fruits表中s\_id的值为101并且f\_price的值大于2.0的行 ###

mysql> select * from fruits
        -> where s_id = 101 and f_price > '2.0' ;

返回的结果如下：

![MySQL基本查询示例][MySQL 12]

### 12、查询fruits表中s\_id的值为101或103并且f\_price列的值大于5 ###

mysql> select * from fruits where
        -> s_id in(101,103) and f_price > 5;

![MySQL基本查询示例][MySQL 13]

### 13、查询fruits表中的s\_id列，并去除重复值 ###

mysql> select distinct s_id from fruits;

返回结果如下：

![MySQL基本查询示例][MySQL 14]

### 14、查询fruits表中的s\_id和f\_name列，并以s\_id对结果进行排序 ###

mysql> select s_id,f_name from fruits order by s_id;

返回的结果如下：

![MySQL基本查询示例][MySQL 15]

### 15、查询fruits表中的f\_name及f\_price列，并以f\_name和f\_price列进行排序 ###

mysql> select f_name,f_price from fruits
        -> order by f_name,f_price;

返回的结果如下：  
![MySQL基本查询示例][MySQL 16]

**注：多字段排序，如果第一个排序的字段一致，会依靠第二个字段排序，依次类推，如果第一个字段不一样，则直接以第一段来进行排序。**

### 16、查询fruits表中的f\_price列，并对结果以降序进行排序 ###

mysql> select f_price from fruits order by f_price desc;

![MySQL基本查询示例][MySQL 17]

### 17、查询fruits中s\_id列不同值出现的次数，并对其进行分组显示 ###

mysql> select s_id,count(*) as total from fruits group by s_id;

返回结果如下：

![MySQL基本查询示例][MySQL 18]

### 18、查询fruits表中每个相同的s\_id对应的f\_name列的所有值，f\_name的值以一行显示，并且其值在1个以上 ###

mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

返回的结果如下：

![MySQL基本查询示例][MySQL 19]

### 19、查询customers表中c\_email列为空值的行 ###

mysql> select * from customers where c_email is null;

查询结果如下：

![MySQL基本查询示例][MySQL 20]

———————— 本文至此结束，感谢阅读 ————————

[MySQL]: https://s1.51cto.com/images/blog/201912/06/905c83fe56b5e460df97ab0c545815ed.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 1]: https://s1.51cto.com/images/blog/201912/06/87c9375d9f26bf0dd0a06f0992ad67ee.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 2]: https://s1.51cto.com/images/blog/201912/06/76e0349f42d5494ffbb52758a73976ae.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 3]: https://s1.51cto.com/images/blog/201912/06/7b648796f3ea6f896ba63d451b10cd45.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 4]: https://s1.51cto.com/images/blog/201912/06/0210e56208ba6d69e2b0e8faffa967e1.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 5]: https://s1.51cto.com/images/blog/201912/06/c1e0b5529dd7fce8a4dcefb6f8a19f8d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 6]: https://s1.51cto.com/images/blog/201912/06/3c965ae226592925623004b88ebb1ece.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 7]: https://s1.51cto.com/images/blog/201912/06/3cd8e494c88a5759cb6420ec377b442a.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 8]: https://s1.51cto.com/images/blog/201912/06/c7bbf09f013d2c151d7c433e4e923f5d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 9]: https://s1.51cto.com/images/blog/201912/06/3a4de1c5e327ff2787f7539022d60764.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 10]: https://s1.51cto.com/images/blog/201912/06/695bc38c5eb24b69510fe6a68f2ea928.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 11]: https://s1.51cto.com/images/blog/201912/06/bf3a3fcf79c5a8a27ecc5dff3b662088.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 12]: https://s1.51cto.com/images/blog/201912/06/f07b5a471a0073c1aaf750bfe334c110.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 13]: https://s1.51cto.com/images/blog/201912/06/4bfbe98fd32f430f47eba6f8fddfad26.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 14]: https://s1.51cto.com/images/blog/201912/06/d66e13f3c0386cdee15d8ae35871c311.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 15]: https://s1.51cto.com/images/blog/201912/06/2b2c4b41052d282412a5bbd03b07f790.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 16]: https://s1.51cto.com/images/blog/201912/06/78ab89a43ec756e17f822bb366f65b0d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 17]: https://s1.51cto.com/images/blog/201912/06/73063ea60db0f400ab19eb4b7f6a36cf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 18]: https://s1.51cto.com/images/blog/201912/06/a16bce4e0c5f1dca08eb18fcb000b4b6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 19]: https://s1.51cto.com/images/blog/201912/06/ddab9264c74d0ee7cbcecf5ce7a68286.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[MySQL 20]: https://s1.51cto.com/images/blog/201912/06/f0f1aa21ce5183ecb51e68f59f507a0c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=