数据库写法

drop database if exists mydb;
create database mydb;
use mydb;
create table class(
cid int primary key auto_increment,
cname varchar(20)
);
create table student(
sid varchar(20) primary key,
sname varchar(20),
sage int,
ssex char(2),
cid int,
foreign key(cid) references class(cid)
);
insert into class values(null,'j211001');
insert into class values(null,'j211002');
insert into student values
('1001','张三',20,'男',1),
('1002','李四',18,'女',2),
('1003','王五',22,'男',1);
select * from class;
select * from student;
#查询大于18岁的信息
select * from student where sage>18;
#查询18岁到20岁的信息
select * from student where sage>=18 and sage<=20;
#查询20岁以上的男生
select * from student where sage>18 and ssex='男';
#查询名字中以张开头的名字
select * from student where sname like'张%';
#查询男同学有几人
select count(*) from student;
#查询学生中最大的年龄
select max(sage) from student;
#计算所有人的平均年龄
select ROUND(sum(sage)/count(*)) from student; 
#计算每种性别的人数
select ssex,count(*) from student GROUP BY ssex ;
#每页显示2个，第一个页面
select * from student limit 0,2;
#查询一班信息
select * from class c left join student s on c.cid = s.cid where cname='j211001';

#修改张三性别为女
update student set ssex='女' where sname='张三' ;