数据的插入与查询

create database three20210603 default charset=utf8;
use three20210603;
create table student(
`name`varchar(20),
sex char,
age int,
address varchar(100)
);

insert into student values ('张三','男',20,"河南省郑州市"),
('张三峰','男',50,"河南省少林寺"),('张无忌','男',18,"河南省洛阳市"),
('王三','男',30,"河南省郑州市"),('王无','男',20,"河南省郑州市"),
('王峰','男',31,"河南省安阳市");

insert into student values ("任盈盈",'女',17,"日月神教黑木崖"),
("许盈",'女',20,'华山');
;
select*from student;

# 模糊查询 % 通配 零个或者多个字符_通配一个字符
select*from student where 	`name`like "张%";
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;



create table score(
sid int,
cid int,
grade int
);
insert into score values(1,201,98),(2,202,86),(3,203,76);
select*from score;
insert into score values(1,202,98),(1,203,97),(2,201,80),(2,203,79),
(3,201,79),(3,202,86);

select cid, avg(grade)from score group by cid;
select sid,avg(grade)from scroe group by sid;

select cid,avg(grade)from score group by cid having avg(grade)>84;select cid,avg(grade)from score group by cid having avg(grade)>84;


SELECT cid ,avg(grade) from score GROUP BY cid HAVING avg(grade)>85 ORDER BY avg(grade) desc ;