mysql数据库常用的查询语句(单表查询)

1 使用select语句查询一个数据表 select* from 数据表名; 2 查询表中的一个字段或多个字段 select 字段1,字段2 from 数据表; 3 查询表中指定的数据信息 select* from数据表名order by id desc limit 2,1; //查询的数据按id倒序排序 4 查询指定路径 Select* f

1 使用select语句查询一个数据表

select* from 数据表名;

2 查询表中的一个字段或多个字段

select 字段1,字段2 from 数据表;

3 查询表中指定的数据信息

select* from数据表名order by id desc limit 2,1; //查询的数据按id倒序排序

4 查询指定路径

Select* from 数据表 where 属性 = ‘…’;

5 带IN 关键字的查询

Select* from 数据表 where 字段 [NOT] IN (字段值1，字段值2…字段值n);

<注：最适于数组作为查询条件，即：in (数组数据)>

例如：

mysql>select * from library where price in (99,50);

+----+--------------+--------+-------+

| id | name | author |price |

+----+--------------+--------+-------+

| 1 | java范例大全 | 张帆 | 99 |

| 2 | mySQL | 潘凯华 | 50 |

+----+--------------+--------+-------+

mysql>select * from library where price NOT IN(99,50);

+----+---------------+--------+-------+

| id | name | author| price |

+----+---------------+--------+-------+

| 3 | SQLserver2005 | 刘智勇 | 80 |

+----+---------------+--------+-------+

6 带BETWEEN and 的范围查询

select * from 数据表 where 字段 [NOT] BETWEEN 取值1 and 取值2；

例如：

mysql> select * from country where idBETWEEN 2 and 4;

+----+----------+------------+------+----------+

| id | name | population | area | language |

+----+----------+------------+------+----------+

| 2 | American | 489 | 60 | English |

| 3 | Japan | 89 | 30 | Jpanese |

| 4 | England | 2 | 300 | English |

+----+----------+------------+------+----------+

3 rows in set(0.09 sec)

mysql> select* from country where id NOT BETWEEN 2 and 4;

+----+-------+------------+------+----------+

| id | name | population | area | language |

+----+-------+------------+------+----------+

| 1 | china | 13 | 960 | chinese |

+----+-------+------------+------+----------+

7 带like的字符匹配查询

(1) select * from 数据表 where 属性 like '%SQL%'；//查询属性中包含SQL字符的数据

(2) select * from 数据表 where 属性 like 'a%b';// //查询属性中以a开头以b结尾的字符串的数据

(3) select * from 数据表 where 属性 like 'm_n';//查询属性中以m开头以n结尾的3个字符的数据，中间的‘_’只能代表一个字符

例如：

mysql> select * from library where name like '%SQL%';

+----+---------------+--------+-------+

| id | name | author| price |

+----+---------------+--------+-------+

| 2 | mySQL | 潘凯华 | 50 |

| 3 | SQLserver2005 | 刘智勇 | 80 |

+----+---------------+--------+-------+

mysql> select * from library where name like 'myS_L';

+----+-------+--------+-------+

| id | name | author | price|

+----+-------+--------+-------+

| 2 | mySQL | 潘凯华 | 50 |

+----+-------+--------+-------+

8 带AND的多条件查询

mysql> select * from library whereprice=50 and name like 'myS_L';

+----+-------+--------+-------+

| id | name | author | price |

+----+-------+--------+-------+

| 2 | mySQL | 潘凯华 | 50 |

+----+-------+--------+-------+

9 带OR的多条件查询

mysql> select * from library where price=50or price=99;

+----+--------------+--------+-------+

| id | name | author | price |

+----+--------------+--------+-------+

| 1 | java范例大全 | 张帆 | 99 |

| 2 | mySQL | 潘凯华 | 50 |

+----+--------------+--------+-------

10 用DISTINCT关键字去除结果中的重复行

例如：

原表：mysql> select * from library;

+----+---------------+--------+-------+

| id | name | author | price |

+----+---------------+--------+-------+

| 1 | java范例大全 | 张帆 | 99 |

| 2 | mySQL | 潘凯华 | 50 |

| 3 | SQLserver2005 | 刘智勇 | 80 |

| 4 | mySQL | 李慧 | 50 |

+----+---------------+--------+-------+

mysql> select distinct name fromlibrary;

+---------------+

| name |

+---------------+

| java范例大全 |

| mySQL |

| SQLserver2005 |

+---------------+

11 用ORDER BY 关键字对查询结果排序

mysql> select * from library orderby id desc;//倒序排列

+----+---------------+--------+-------+

| id | name | author | price |

+----+---------------+--------+-------+

| 4 | mySQL | 李慧 | 50 |

| 3 | SQLserver2005 | 刘智勇 | 80 |

| 2 | mySQL | 潘凯华 | 50 |

| 1 | java范例大全 | 张帆 | 99 |

+----+---------------+--------+-------+

mysql> select * from library orderby id asc;//正序排列

+----+---------------+--------+-------+

| id | name | author | price |

+----+---------------+--------+-------+

| 1 | java范例大全 | 张帆 | 99 |

| 2 | mySQL | 潘凯华 | 50 |

| 3 | SQLserver2005 | 刘智勇 | 80 |

| 4 | mySQL | 李慧 | 50 |

+----+---------------+--------+-------+

12 用GROUP BY关键字分组查询

(1) 用GROUP BY关键字分组查询

例如：mysql> select name,price from library GROUP BY price;

+---------------+-------+

| name | price |

+---------------+-------+

| mySQL | 50 |

| SQLserver2005 | 80 |

| java范例大全 | 99 |

+---------------+-------+

3 rows in set (0.00 sec)

mysql> select name,price from library GROUP BY name;

+---------------+-------+

| name | price |

+---------------+-------+

| java范例大全 | 99 |

| mySQL | 50 |

| SQLserver2005 | 80 |

+---------------+-------+

(2) GROUP BY 关键字与 GROU_CONCAT函数一起使用

mysql> selectname,GROUP_CONCAT(price) from library GROUP BY name;

+---------------+---------------------+

| name |GROUP_CONCAT(price) |

+---------------+---------------------+

| java范例大全 | 99 |

| mySQL | 50,50 |

| SQLserver2005 | 80 |

+---------------+---------------------+

（3）按多个字段进行分组

mysql> selectid,name,price from library GROUP BY name,price;//当price字段的值相等时，再按照name字段分组

+----+---------------+-------+

| id |name | price |

+----+---------------+-------+

| 1 | java范例大全 | 99 |

| 2 | mySQL | 50 |

| 3 | SQLserver2005 | 80 |

+----+---------------+-------+

13 用LIMIT限制查询结果的数量

mysql> select * from library orderby id asc limit 2,3; //取两条数据，正序，从第三条开始

+----+---------------+--------+-------+

| id | name | author | price |

+----+---------------+--------+-------+

| 3 | SQLserver2005 | 刘智勇 | 80 |

| 4 | mySQL | 李慧 | 50 |

+----+---------------+--------+-------+

2 rows in set (0.00 sec)

mysql> select * from libraryorder by id desc limit 2,3; //取两条数据，倒序，从倒数第三条结束，只显示倒数前两个

+----+--------------+--------+-------+

| id | name | author | price |

+----+--------------+--------+-------+

| 2 | mySQL | 潘凯华 | 50 |

| 1 | java范例大全 | 张帆 | 99 |

+----+--------------+--------+-------+