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,"武當(dāng)山")，("花無缺",'男',20,"四川"

)，("張三",'男',29,"四海"),("李豐",'男',29,"蜀國")

;

select * from student;

-- 模糊查詢 like? % 表示 0-n個(gè)? ? _表示一個(gè)

select * from student;

select * from student? where? `name` like "張%";

select * from student? where? `name` like "張_";

select * from student? where? `name` like "%_豐"

--聚合函數(shù)

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 ;