Mysql数据库单表查询

(一)单表查询

1. 查询所有记录：select*from 表名;
2. 查询指定字段记录：select 列名 from 表名 ;
3. 查询指定记录：select 字段名1,字段名2…字段名n where 查询条件；
4. 使用IN关键字查询
5. 使用BETWEEN AND 查询指定范围的记录
6. 使用LIKE关键字模糊查询
7. 使用AND关键字多 条件查询
8. 使用OR关键字多条件查询
9. 查询空值用 IS NULL
10. 查询结果不重复
11. 对查询结果排序(单列排序、多列排序、指定方向排序)
12. 分组查询
13. 使用LIMIT限制查询结果的数量

(二)演示

CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(25) DEFAULT NULL,  `sex` varchar(45) DEFAULT NULL,  `address` varchar(45) DEFAULT NULL,  `number` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

1.查询所有记录：select*from 表名;

mysql> select*from person;+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  1 | 凯耐   | 男  | 长沙    | 123    ||  2 | 科比   | 男  | 北京    | 234    ||  3 | 小丽   | 女  | 武汉    | 345    ||  4 | 小五   | 男  | 深圳    | 456    ||  5 | 张毅峰 | 男  | 南京    | 567    ||  6 | 贺知章 | 男  | 上海    | 789    ||  7 | 张一山 | 男  | 北京    | 987    ||  8 | 赵丽颖 | 女  | 上海    | 876    ||  9 | 天佑   | 男  |         | NULL   |+----+--------+-----+---------+--------+9 rows in set

2.查询指定字段记录

mysql> select id,name,address from person;+----+--------+---------+| id | name   | address |+----+--------+---------+|  1 | 凯耐   | 长沙    ||  2 | 科比   | 北京    ||  3 | 小丽   | 武汉    ||  4 | 小五   | 深圳    ||  5 | 张毅峰 | 南京    ||  6 | 贺知章 | 上海    ||  7 | 张一山 | 北京    ||  8 | 赵丽颖 | 上海    |+----+--------+---------+8 rows in set

3.查询满足条件的字段数据

mysql> select id name,address from person where address='上海';+------+---------+| name | address |+------+---------+|    6 | 上海    ||    8 | 上海    |+------+---------+2 rows in set

4.使用IN关键字查询满足条件的字段信息

mysql> select id ,name,address from person where address in('上海','北京');+----+--------+---------+| id | name   | address |+----+--------+---------+|  2 | 科比   | 北京    ||  6 | 贺知章 | 上海    ||  7 | 张一山 | 北京    ||  8 | 赵丽颖 | 上海    |+----+--------+---------+4 rows in set

5.使用between and 查询指定范围内的数据

mysql> select*from person where id  between 5 and 6;+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  5 | 张毅峰 | 男  | 南京    | 567    ||  6 | 贺知章 | 男  | 上海    | 789    |+----+--------+-----+---------+--------+2 rows in set

6.使用like关键字模糊查询

mysql> select*from person where name like '张%';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  5 | 张毅峰 | 男  | 南京    | 567    ||  7 | 张一山 | 男  | 北京    | 987    |+----+--------+-----+---------+--------+2 rows in setmysql> select*from person where name like '%山';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  7 | 张一山 | 男  | 北京    | 987    |+----+--------+-----+---------+--------+mysql> select*from person where name like '%丽%';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  3 | 小丽   | 女  | 武汉    | 345    ||  8 | 赵丽颖 | 女  | 上海    | 876    |+----+--------+-----+---------+--------+2 rows in setmysql>  select*from person where name like'小_';+----+------+-----+---------+--------+| id | name | sex | address | number |+----+------+-----+---------+--------+|  3 | 小丽 | 女  | 武汉    | 345    |+----+------+-----+---------+--------+1 row in setmysql> select*from person where name like'_毅_';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  5 | 张毅峰 | 男  | 南京    | 567    |+----+--------+-----+---------+--------+1 row in setmysql> select*from person where name like'__章';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  6 | 贺知章 | 男  | 上海    | 789    |+----+--------+-----+---------+--------+1 row in set

7.AND多条件查询

mysql>  select*from person where sex='男'and address='北京';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  2 | 科比   | 男  | 北京    | 234    ||  7 | 张一山 | 男  | 北京    | 987    |+----+--------+-----+---------+--------+2 rows in set

8.OR多条件查询

mysql>  select*from person where address='上海'or '北京';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  6 | 贺知章 | 男  | 上海    | 789    ||  8 | 赵丽颖 | 女  | 上海    | 876    |+----+--------+-----+---------+--------+2 rows in setmysql> select*from person where address='上海'or name='张一山';+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  6 | 贺知章 | 男  | 上海    | 789    ||  7 | 张一山 | 男  | 北京    | 987    ||  8 | 赵丽颖 | 女  | 上海    | 876    |+----+--------+-----+---------+--------+3 rows in set

9.查询空值用 IS NULL

mysql> select*from person where number is null;+----+------+-----+---------+--------+| id | name | sex | address | number |+----+------+-----+---------+--------+|  9 | 天佑 | 男  |         | NULL   |+----+------+-----+---------+--------+1 row in set

10.查询结果不重复

mysql>  select distinct sex from person where sex='男';+-----+| sex |+-----+| 男  |+-----+1 row in set

11.对查询结果排序(单列排序、多列排序、指定方向排序)

单列升序mysql>  select  number from person order by number asc;+--------+| number |+--------+| NULL   || 123    || 234    || 345    || 456    || 567    || 789    || 876    || 987    |+--------+9 rows in set单列降序mysql>  select  number from person order by number desc;+--------+| number |+--------+| 987    || 876    || 789    || 567    || 456    || 345    || 234    || 123    || NULL   |+--------+9 rows in set

12.分组查询

1.group by的使用mysql> select sex,count(*)from person group by sex;+-----+----------+| sex | count(*) |+-----+----------+| 女  |        2 || 男  |        7 |+-----+----------+2 rows in set2.筛选分组后满足count(sex)>3的组mysql>  select sex,count(*)from person group by sex having count(sex)>3;+-----+----------+| sex | count(*) |+-----+----------+| 男  |        7 |+-----+----------+1 row in set3.分组后统计结果mysql>  select sex,count(*)from person group by sex with rollup;+------+----------+| sex  | count(*) |+------+----------+| 女   |        2 || 男   |        7 || NULL |        9 |+------+----------+3 rows in set

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

1.查询前4条记录mysql> select*from person limit 4;+----+-------+-----+---------+--------+| id | name  | sex | address | number |+----+-------+-----+---------+--------+|  1 | 凯耐  | 男  | 长沙    | 123    ||  2 | 科比  | 男  | 北京    | 234    ||  3 | 小丽  | 女  | 武汉    | 345    ||  4 | 小五  | 男  | 深圳    | 456    |+----+-------+-----+---------+--------+4 rows in set2.从第5条记录查询，且只查3条记录mysql> select*from person limit 4,3;+----+--------+-----+---------+--------+| id | name   | sex | address | number |+----+--------+-----+---------+--------+|  5 | 张毅峰 | 男  | 南京    | 567    ||  6 | 贺知章 | 男  | 上海    | 789    ||  7 | 张一山 | 男  | 北京    | 987    |+----+--------+-----+---------+--------+3 rows in set