第九章 数据查询（一）

9.1 基本查询语句

1.SELECT查询一个数据表

select * from tb_admin;

2.查询表中一列或多列

select id,user from tb_admin;

3.从一个或多个表中获取数据

select tb_admin.id,tb_user.id from tb_admin,tb_user;

9.2 单表查询

9.2.1 查询所有字段

SELECT * FROM tb_admin;

9.2.2 查询指定字段

SELECT user FROM tb_admin; 

9.2.3 查询指定数据

SELECT * FROM tb_login where user='mr';

9.2.4 带IN的查询(或NOT IN)

SELECT * FROM tb_login WHERE user IN('mr','aw');

9.2.5 BETWEEN AND的范围查询

SELECT * FROM tb_login WHERE id BETWEEN 5 AND 7;

9.2.6 带LIKE的字符匹配查询

select * from tb_admin where user like '%mr%';

9.2.7 IS NULL查询空值

SELECT books,row FROM WHERE row IS NULL;

9.2.8 带AND的多条件查询

SELECT * FROM tb_admin WHERE user='mr' and sectiom='php';

9.2.9 带OR的多条件查询

SELECT * FROM tb_admin WHERE user='mr' or sectiom='php';

9.2.10 用DISTINCT去除结果中重复行

SELECT DISTINCT name FROM tb_login;

9.2.11 ORDER BY对结果排序

select * from tb_login order BY id DESC;

DESC为降序，ASC为升序，null为最小

9.2.12 GROPE BY分组查询

1.单独使用GROUP BY(每组只显示一条)

SELECT id,books,talk FROM tb_book GROUP BY talk;

2.GROUP BY和GROUP_CONCAT()一起使用(全部显示)

select id,books,GROUP_CONCAT(talk) from tb_book GROUP BY talk;

3.按多个字段分组

select id,books,talk user from tb_book GROUP BY user,talk;

9.2.13 LIMIT限制查询结果的数量

select * from tb_login order by id asc limit 3;