MySQL如何实现单表查询
发布时间：2022-02-16 13:57:42 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关MySQL如何实现单表查询的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 单表查询指从一张表数据中查询所需的数据。 (1)查询所有字段 (2)查询指定字段 (3)查询指定记录 (4)带in关键字的查询 (5)带b
|
这篇文章给大家分享的是有关MySQL如何实现单表查询的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 单表查询指从一张表数据中查询所需的数据。 (1)查询所有字段 (2)查询指定字段 (3)查询指定记录 (4)带in关键字的查询 (5)带between and的范围的查询 (6)带like的字符匹配查询 (7)查询空值 (8)带and的多条件查询 (9)带or的多条件查询 (10)查询结果不重复 (11)对查询结果排序 (12)分组查询 (13)使用limit限制查询结果的数量 (1)查询所有字段 1.select * from fruits 2.select f_id,s_id,f_name,f_price from fruits; 上面两个语句执行后的结果都是查询所有字段: mysql> select * from fruits;+------+------+------------+---------+| f_id | s_id | f_name | f_price |+------+------+------------+---------+| 12 | 104 | lemon | 6.40 || 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 || 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 | xbabay | 3.60 |+------+------+------------+---------+16 rows in set (0.00 sec) (2)查询指定字段 1.查询单个字段:select 列名 from 表名; 【例】查询fruits表中f_name列所有水果名称,SQL语句如下: mysql> select f_name from fruits;+------------+| f_name |+------------+| lemon || apple || apricot || blackberry || berry || xxxx || orange || melon || cherry || mango || xbabay || xxtt || coconut || banana || grape || xbabay |+------------+16 rows in set (0.00 sec) 2.查询多个字段:select 字段名1,字段名2,...字段名n from 表名; 【例】从fruits表中获取名称为f_name和f_price两列,SQL语句如下: mysql> select f_name,f_price from fruits;+------------+---------+| f_name | f_price |+------------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || blackberry | 10.20 || berry | 7.60 || xxxx | 3.60 || orange | 11.20 || melon | 8.20 || cherry | 3.20 || mango | 15.70 || xbabay | 2.60 || xxtt | 11.60 || coconut | 9.20 || banana | 10.30 || grape | 5.30 || xbabay | 3.60 |+------------+---------+16 rows in set (0.00 sec) (3)查询指定记录 select 字段名1，字段名2，。。。，字段名n from 表名 where 查询条件 【例1】查询价格为10.2元的水果名称，SQL语句如下： mysql> select f_name,f_price -> from fruits -> where f_price = 10.2;+------------+---------+| f_name | f_price |+------------+---------+| blackberry | 10.20 |+------------+---------+1 row in set (0.00 sec) 【例2】查找名称为"apple"的水果价格，SQL语句如下： mysql> 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) 【例3】查询价格小于10的水果名称，SQL语句如下： mysql> select f_name,f_price -> from fruits -> where f_price<10.00;+---------+---------+| f_name | f_price |+---------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || berry | 7.60 || xxxx | 3.60 || melon | 8.20 || cherry | 3.20 || xbabay | 2.60 || coconut | 9.20 || grape | 5.30 || xbabay | 3.60 |+---------+---------+11 rows in set (0.00 sec) (4)带in关键字的查询 in操作符用来查询满足指定范围内的条件的记录，使用in操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。 【例1】s_id为101和102的记录，SQL语句如下; 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) 【例2】查询所有s_id既不等于101也不等于102的记录，SQL语句如下： mysql> 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.70 || 105 | melon | 8.20 || 105 | xbabay | 2.60 || 107 | xbabay | 3.60 || 105 | xxtt | 11.60 || 107 | xxxx | 3.60 |+------+---------+---------+10 rows in set (0.00 sec) (5)带between and的范围的查询 between and 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值。 【例1】查询价格在2.00元到10.20元之间的水果名称和价格，SQL语句如下： mysql> select f_name,f_price from fruits where f_price between 2.00 and 10.20;+------------+---------+| f_name | f_price |+------------+---------+| lemon | 6.40 || apple | 5.20 || apricot | 2.20 || blackberry | 10.20 || berry | 7.60 || xxxx | 3.60 || melon | 8.20 || cherry | 3.20 || xbabay | 2.60 || coconut | 9.20 || grape | 5.30 || xbabay | 3.60 |+------------+---------+12 rows in set (0.00 sec) 【例2】查询价格在2.00元到10.20元之外的水果名称和价格，SQL语句如下; mysql> 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.70 || xxtt | 11.60 || banana | 10.30 |+--------+---------+4 rows in set (0.00 sec) (6)带like的字符匹配查询 1.%通配符，匹配任意长度的字符，甚至包括零字符。 【例1】查找所有以"b"字母开头的水果，SQL语句如下： 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) 【例2】在fruits表中，查询f_name中包含字母"g"的记录，SQL语句如下： 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) 【例3】查询以"b"开头，并以‘y’结尾的水果的名称，SQL语句如下： mysql> select f_id,f_name -> from fruits -> where f_name like 'b%y';+------+------------+| f_id | f_name |+------+------------+| b1 | blackberry || b2 | berry |+------+------------+2 rows in set (0.00 sec) 2._通配符，一次只能匹配任意一个字符。 【例】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录，SQL语句如下： 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) (7)查询空值 数据表创建时，可以指定某列中是否可以包含空值null。空值不同于0，也不同于空字符串。空值一般表示数据位置、不使用或将在以后添加数据。在select语句中使用is null子句，可以查询某字段内容为空记录。 下面创建数据表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) ); 插入下列语句: mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email) -> values -> (10001,'redhool','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');Query OK, 4 rows affected (0.06 sec)Records: 4 Duplicates: 0 Warnings: 0 【例1】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值，SQL语句如下： 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) 【例2】查询customers表中c_email不为空的记录的c_id、c_name、和c_email字段值，SQL语句如下： mysql> select c_id,c_name,c_email -> from customers -> where c_email is not null;+-------+---------+-------------------+| c_id | c_name | c_email |+-------+---------+-------------------+| 10001 | redhool | LMing@163.com || 10002 | Stars | Jerry@hotmail.com || 10004 | JOTO | sam@hotmail.com |+-------+---------+-------------------+3 rows in set (0.00 sec) (8)带and的多条件查询 【例1】在fruits表中查询s_id=101，且f_price大于5的水果的价格和名称，SQL语句如下： 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) 【例2】在fruits表中查询s_id=101或者102,且f_price大于5,且f_name='apple’的水果价格和名称，SQL语句如下： 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.05 sec) (9)带or的多条件查询 【例1】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下： 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) 【例2】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下： 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 注意 or可以和and一起使用，但是要注意两者的优先级，由于and的优先级高于or，旖旎次先对and两边的操作数进行操作，再与or中的操作数结合。 (10)查询结果不重复 语法格式：select distinct 字段名 from 表名； 【例】查询fruits表中s_id字段的值，返回s_id字段值且不得重复,SQL语句如下; mysql> select distinct s_id from fruits;+------+| s_id |+------+| 104 || 101 || 103 || 107 || 102 || 105 || 106 |+------+7 rows in set (0.05 sec) (11)对查询结果排序 1.单列排序order by 【例】查询fruits表的f_name字段值，并对其进行排序，SQL语句如下： mysql> select f_name from fruits order by f_name;+------------+| f_name |+------------+| apple || apricot || banana || berry || blackberry || cherry || coconut || grape || lemon || mango || melon || orange || xbabay || xbabay || xxtt || xxxx |+------------+16 rows in set (0.00 sec) 2.多列排序 多列排序首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。 【例】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL语句如下： 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 || xbabay | 2.60 || xbabay | 3.60 || xxtt | 11.60 || xxxx | 3.60 |+------------+---------+16 rows in set (0.00 sec) 3.指定排序方向desc desc是降序排列，与之对应的是asc升序排列，但asc是默认的，可以不加。 【例1】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序，SQL语句如下： mysql> select f_name,f_price -> from fruits -> order by f_price desc;+------------+---------+| f_name | f_price |+------------+---------+| mango | 15.70 || 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 || xbabay | 3.60 || cherry | 3.20 || xbabay | 2.60 || apricot | 2.20 |+------------+---------+16 rows in set (0.00 sec) （编辑：临夏站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐