select查询详解

–查询基本使用(条件，排序，聚合函数，分组，分页)

update user set password=password(‘root’) where user=‘root’;
–创建学生表
create table students (
id int unsigned not null auto_increment primary key,
name varchar(20) default ‘’,
age tinyint unsigned default 0,
high decimal(5,2),
gender enum(‘男’, ‘女’, ‘中性’, ‘保密’) default ‘保密’,
cls_id int unsigned default 0,
is_delete bit default 0
);

–创建班级表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20) not null
);

–往students表里插入数据
insert into students values
(0,‘小明’,18,180.00,1,1,0),
(0,‘小月月’,19,180.00,1,2,0),
(0,‘彭于晏’,28,185.00,1,1,0),
(0,‘刘德华’,58,175.00,1,2,0),
(0,‘黄蓉’,108,160.00,2,1,0),
(0,‘凤姐’,44,150.00,4,2,1),
(0,‘王祖贤’,52,170.00,2,1,1),
(0,‘周杰伦儿’,34,null,1,1,0),
(0,‘程坤’,44,181.00,1,2,0),
(0,‘和珅’,55,166.00,1,2,0),
(0,‘刘亦菲’,29,162.00,2,3,0),
(0,‘金星’,45,180.00,3,4,0),
(0,‘静香’,18,170.00,2,4,0),
(0,‘郭靖’,22,167.00,1,5,0),
(0,‘周杰’,33,178.00,1,1,0),
(0,‘钱小豪’,56,178.00,1,1,0),
(0,‘谢霆锋’,38,175.00,1,1,0),
(0,‘陈冠希’,38,175.00,1,1,0);

–向classes表里插入数据
insert into classes values (0, ‘云唯_01期’),(0, ‘云唯_02期’);

–查询
– 查询所有列
–select * from 表名

--一定条件查询(where)
select * from students where id >= 5;


-- 查询制定列
select name,age from students;


-- 使用as给字段起别名
select name as '姓名', age as '年纪' from students;

-- 通过表名字查询
select students.name, students.age from students;

-- 给表起别名查询
select s.name, s.age from students as s;

--消除重复行
-- distinct
select distinct age from students;


--条件查询
	--比较运算符
	-- 查询年纪大于18岁的信息
select * from students where age > 18;
	
	--18岁到28岁之间(and)
select * from students where age > 18 and age < 28;

	--在18岁以上或者身高180以上的人(or)
select * from students where age > 18 or high > 180;

	

-- 模糊查询
	-- like
	-- % 替代1个或者多个甚至是没有
	-- 查询姓名中有‘小’的所有名字
	select * from students where name like '%小%';
	
	-- 查询有两个字的名字
	select * from students where name like '__'; 
	
	-- 查询至少有2个字的名字
	select * from students where name like '__%';
	
	-- rlike 正则
	-- 查询以周开始的名字
	select * from students where name rlike '^周.*儿';   
	

--范围查询
	-- in (1,3,8)表示在一个非连续的范围内
	-- 查询 年纪为18和34的人
	select * from students where age not in (18,34);  
	select * from students where age=18 or age=34;      
	
	--查询 年龄在17岁到34岁之间的信息
	select * from students where age between 17 and 34;
	
	
	--查询 年纪不在18到34岁的信息
	select * from students where age not between 17 and 34;

-- 空判断
	-- 判断is null
	-- 查询身高为空的信息
	select * from students where high is null;
	
	-- 判断非空is not null
	select * from students where high is not null;

-- 排序	
	-- order by 字段
	-- asc从小到大排列,即升序
	-- desc从大到小排序，即降序
	
	-- 查询年纪在18到34岁之间的男性，按照年纪从小到大
	select * from students where (age between 18 and 34) and gender=1 order by age;
	
	
	-- 查询年纪在18到34岁之间的女性，身高从高到矮
	select * from students where (age between 18 and 34) and gender=2 order by high desc;
	
	-- order by 多字段
	-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
	select * from students where (age between 18 and 34) and gender=2 order by high desc, age asc;
	
	-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
	select * from students where (age between 18 and 34) and gender=2 order by high desc, age asc, id asc;
	
	-- 按照年纪从小到大,年纪相同情况下按照身高从高到矮的排序
	select * from students order by age, high desc;

–聚合函数
– 总数
– count
– 查询男性有多少人
select count(*) from students where gender=1;

-- 最大值
-- max
-- 查询最大的年纪
select max(age) from students;--添加name有可能错位


-- 查询女性的最高 身高
select max(high) from students where gender=2;  

-- 最小值
-- min
select min(high) from students where gender=2;  

-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;

-- 平均值
-- avg
-- 计算平均年纪
select sum(age)/count(*) from students;
select avg(age) from students;

-- 计算平均年纪 sum(age)/count(*)


-- 保留2位小数
select round(avg(age), 2) from students;

– 分组
– group by
– 按照性别分组，查询所有的性别
select gender from students group by gender;

-- 计算每组性别的人数
select gender, count(*) from students group by gender;

-- 计算男性人数

select count(*) from students where gender=1 group by gender;
--把查出来的内容拼接成一个字符串concat,concat_ws
select concat(name,age,high) from students;
select concat_ws('|',name,age,high) from students;

-- 查询男性组中的姓名 group_concat
select group_concat('|',name) from students;

-- having
-- 查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30
select * from students group by gender having avg(age) > 30;


-- 查询每种性别中的人数多于4个组的信息
select gender from students group by gender having count(*)>4; 

– 分页
– 显示5页
select * from students limit 5;

-- 分页显示，每页显示2条数据
select * from students limit 0,2;

-- 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
select * from students where gender=2 order by high desc limit 0,2;