MySQL单表查询

单表查询
查询所有字段：select * from 表名;

mysql> select * from stus;+------+----------+| id   | name     | +------+----------+| 1623 | yangming |+------+----------+4 rows in set (0.01 sec)

查询指定字段：select 字段名 from 表名;

mysql> select id from stus;+------+| id   |+------+| 1623 |+------+4 rows in set (0.01 sec)

查询指定数据：select * from 表名 where 字段名=’xx’;

mysql> select * from stus where id=1623;+------+----------+| id   | name     | +------+----------+| 1623 | yangming |+------+----------+4 rows in set (0.01 sec)

查询不等于指定数据：select * from 表名 where 字段 数据；

select * from 表名 where 字段 !=数据;

mysql> select * from stus where id 1623;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

带in关键字查询：select * from 表名 where 字段 [not] in(值1,值2）；

mysql> select * from stus where id in (1623,1624);+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec

带between and 范围查询：select * from 表名 where 字段 between 值1 and 值2;

或：select * from 表名 where 字段>=值1 and 字段<=值2;

mysql> select * from stus where id between 12 and 123456;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

带like模糊查询（“_”一个字符；“%”一个或多个字符）：select * from 表名where 字段like ‘%xx%’;

mysql> select * from stus where id like '%1624%';+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

用is null关键字查询空值：select 字段1,字段2 from 表名 where 字段2 is null;

mysql> select id,name from stus where id is null;+------+----------+| id   | name     | +------+----------+4 rows in set (0.01 sec)

带and多条件查询：select * from 表名 where 字段1 and 字段2;

mysql> select * from stus where id =1624 and name="yangming";+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

带or的多条件查询：select * from 表名 字段1 or 字段2;

mysql> select * from stus where id =1624 or name="yangming";+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

用distinct关键字去除结果中重复行：select distinct 字段 from 表名；

mysql> select distinct id from stus;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

用order by 关键字对查询结果排序：select * from 表名 order by 字段 asc(升序/desc降序）;

mysql> select * from stus order by id ;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

用group by 关键字分组查询：select 字段1,字段2,字段3 from 表名 group by 字段3;

mysql> select * from stus group by id ;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

多个字段分组：select 字段1,字段2,字段3 from 表名group by 字段1，字段2;

mysql> select * from stus group by id,name ;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

having子句：select 字段，count(字段1),avg(字段2),min(字段3),max(字段3) from 表名group by 字段 having min(字段3)<值;–注意，having必须是返回聚合函数

mysql> select min(id),name from stus group by id having min(id)<15423 ;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

用limit限制查询结果数量：select * from 表名 order by 字段 desc limit n;

mysql> select * from stus order by  id limit 1 ;+------+----------+| id   | name     | +------+----------+| 1624 | yangming |+------+----------+4 rows in set (0.01 sec)

Count()函数：select count(*) from 表名;

mysql> select count(*) from stus;+----------+| count(*) |+----------+| 4 |+----------+1 row in set (0.00 sec)

Sum()函数：select sum(字段) from 表名;

mysql> select sum(id) from stus;+---------+| sum(id) |+---------+|    6498 |+---------+1 row in set (0.00 sec)

Avg()函数：select avg(字段) from 表名;

mysql> select avg(id) from stus;+-----------+| avg(id)   |+-----------+| 1624.5000 |+-----------+1 row in set (0.00 sec)

Max()函数：select max(字段) from 表名;

mysql> select max(id) from stus;+---------+| max(id) |+---------+|    1628 |+---------+1 row in set (0.00 sec)

Min()函数：select min(字段) from 表名；

mysql> select min(id) from stus;+---------+| min(id) |+---------+|    1620 |+---------+1 row in set (0.00 sec)

读完本文有收获吗？请转发分享更多人

软件测试分享圈