MySQL查询汇总

1.查询所有数据
SELECT * from table_name;
eg：select * from user;

2.查询指定列数据
SELECT 列名，列名，列名 from table_name;
eg：select id, name from user;

3.查询时指定常量列(别名)
SELECT 列名 as 别名 from table_name;
as 可以不写
eg: mysql> select id, name as 姓名 from user;
mysql> select id, name 姓名 from user;

4.查询时去除重复数据 DISTINCT
eg: select distinct id,name from user;

5.查询时合并
eg: select id,name,age,java+math from user;
select id,name,age,java+math as total from user;

6.按条件查询 WHERE
eg: select * from user where id =2;

a 逻辑条件查询，涉及多个条件 and &&(与) or(或)
eg: select * from user where id =2 and name = ‘lisi’;
select * from user where name =‘lisi’ or age = 22;

b 比较条件查询 > 、 >= 、< 、<= 、= 、<> , between and(谁和谁之间)
eg: select * from user where id<>2;
eg: select * from user where id>2;
eg: select * from user where id>=2;
eg: select * from user where id between 2 and 5;

c 判断是否为null（null表示没有数据）
mysql> select * from user where age is null;
mysql> select * from user where age is not null;

d 模糊查询 like
%通配符，可以匹配多个字符
通配符，匹配一个字符
mysql> select * from user where name like ‘zhao%’;
mysql> select * from user where name like ‘%s%’;
mysql> select * from user where name like 'zhaos’;

7 通过聚合函数查询
a计算表中有多少条数据（行）count
eg: select count(*) from user;
如果某些行对应的列没有数据，不计算到总数里
eg: select count(age) from user;

b求平均值 avg
eg：select avg(age) from user;

c求最大值和最小值
eg: select min(age) from user;
eg: select max(age) from user;

d求和 sum
mysql> select sum(age) from user;

–sum() 该列所有数据总和
–+ 该行数据相加的和

8.排序 order by
eg: select * from user order by age;
eg: select * from user where age is not null order by age;

desc表示降序，asc表示升序，默认升序
eg: select * from user where age is not null order by age desc;

可以指定多个排序的字段，例如先按照age排序，相同的age值再使用id排序
eg：select * from user where age is not null order by age,id;
eg：select * from user where age is not null order by age desc,id desc;

9 分组查询 group by
eg: select gender, count() from user group by gender;
eg：select gender,count() from stu group by gender order by gender desc;

having 对分组后的数据进行筛查
where 放在分组前使用
mysql> select gender,count() from user where age is not null group by gender having count()>1 order by gender desc;

10 分页查询 limit
Limit 从哪个位置开始查（索引从0开始计数）, 查询多少条数据
mysql> select * from user limit 3,3;

表示从0位置开始查询3条数据
mysql> select * from user limit 3;