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)

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

软件测试分享圈