数据库（mydb）

drop database if exists mydb;
create database mydb;#创建数据库
use mydb;#使用数据库
#创建班级表
create table clazz
(
cid int primary key auto_increment,#主键，自增,班级id
cname varchar(20) not null#班级名称
);
#创建学生表
create table student 
(
sid int primary key auto_increment,#学生id，主键，自增
sname varchar(20) not null,#学生名称
ssex varchar(20) not null,#学生性别，非空
sage int not null,#学生年龄，非空
stuid int references clazz(cid)#外键
);
#添加数据
insert into clazz values 
(null,"j211001"),
(null,"j211002");
select * from clazz;

insert into student values 
(null,"张三","男",18,1),
(null,"李四","男",16,1),
(null,"张津龙","女",17,2),
(null,"王五","男",20,1);
select * from student;
#左外连接  内连接
select * from clazz c left join student s on c.cid=s.stuid; 
select * from clazz c inner join student s on c.cid=s.stuid; 
#查询所有年龄大于平均年龄的学生信息
select * from student where sage>(select AVG(sage) from student);
#分页
select * from student where sage>(select AVG(sage) from student) limit 1,1;
#修改
update student set sname="张三1" where sname="张三";
#删除
delete from student where sname="张三1";
#模糊
select * from student where sname like "张%";
select * from student where sname like "%龙";
#最大值 最小值  平均值
select sname,max(sage) from student;
select sname,min(sage) from student;
select avg(sage) from student;

select * from student where sage between 15 and 20;

select count(*) from student where ssex="男";

select * from student  order by sage desc;

select stuid,count(*) from student group by stuid;