create database schooldb default charset=utf8;
use schooldb;
create table student(
`name`varchar(10),
sex char,
age int,
address varchar(100)
);
insert into student values("张无忌",'男',18,"光明顶")，("张三丰",'男',50,"武当山")，("花无缺",'男',20,"四川"
)，("张三",'男',29,"四海"),("李丰",'男',29,"蜀国")
;
select * from student;
-- 模糊查询 like % 表示 0-n个 _表示一个
select * from student;
select * from student where `name` like "张%";
select * from student where `name` like "张_";
select * from student where `name` like "%_丰"
--聚合函数
select avg(age) from student;
select count(*) from student;
select max(age) from student;
select min(age) from student;
select sum(age) from student;
insert into student values("任盈盈",'女',17,"黑木崖")，("东方不败",'女',35,"黑木崖")，("依琳",'女',25,"峨眉");
--分组求平均年龄
select sex , avg(age) from student group by sex;
select sex , avg(age) from student group by sex='女';
select address , `name` ,sex ,age from student group by address having sex='男' order by age desc ;
select address , `name` ,sex ,age from student where `name` like "张%"group by address having sex='男' order by age desc ;
