sql语句

reate database demo
use demo

create table classinfo(
classid int primary key auto_increment,
className varchar(20)
)

create table student(
stuid varchar(20) primary key,
stuname varchar(20),
stusex char(2),
stuage int,
stuaddress varchar(50),
stuclassid int references classinfo(classid)

)
插入数据
INSERT INTO classinfo  VALUES (null,1);
INSERT INTO classinfo  VALUES (null,2);
INSERT INTO classinfo  VALUES (null,3);

INSERT INTO student  VALUES ('12121','zx','男',25,'湖南长沙',1);
INSERT INTO student   VALUES ('12122','zc','男',25,'湖南长沙',1);
INSERT INTO student   VALUES ('12123','cv','男',26,'湖南长沙',1);
INSERT INTO student   VALUES ('12124','vc','男',24,'湖南长沙',1);
INSERT INTO student   VALUES ('12125','bc','男',27,'湖南长沙',1);

INSERT INTO student  VALUES ('12111','xx','男',25,'湖北武汉',2);
INSERT INTO student   VALUES ('12112','rc','男',25,'湖北武汉',2);
INSERT INTO student   VALUES ('12113','cv','男',26,'湖北武汉',2);
INSERT INTO student   VALUES ('12114','vc','男',24,'湖北武汉',2);
INSERT INTO student   VALUES ('12115','bc','男',27,'湖北武汉',2);

INSERT INTO student  VALUES ('12131','ax','女',22,'河南郑州',3);
INSERT INTO student   VALUES ('12132','nc','女',26,'河南郑州',3);
INSERT INTO student   VALUES ('12133','nv','女',29,'河南郑州',3);
INSERT INTO student   VALUES ('12134','vm','女',30,'河南郑州',3);
INSERT INTO student   VALUES ('12135','bm','女',34,'河南郑州',3);

查询班级信息
SELECT * from classinfo

修改学生地址
UPDATE student set stuaddress='河南开封' WHERE stuid='12112'

删除某个学生的信息
DELETE FROM student WHERE stuid='12114'

查询某个学生年龄大于二十的学生信息
SELECT * FROM student WHERE stuage>20

查询年龄在20到30之间的学生信息
SELECT * frOM student WHERE  stuage BETWEEN 20 and 30

查询性别为女并且年龄大于三十的学生信息
SELECT * FROM STUDENT where stusex='女' and stuage>30

查询性别为女的学生信息
select * from Student where stusex='女'

--查询地址是湖北武汉的学生信息
select * from student where  stuaddress='湖北武汉'

--查询班级编号为3的学生信息
select * from student where stuclassid='3'

--查询学生姓C的学生信息  模糊查询
select * from  student where stuName like 'c%'

--查询学生姓C的学生信息并且年龄大于24岁的学生信息
select * from student where stuname like 'c%' and stuage>24

--查询学生姓名中有c的学生信息
select * from student where stuname like '%c%'

--内连接查询   inner join on 查询学生信息，显示班级名称
select s.*,c.classname from student s inner  join classinfo c  on s.stuclassid=c.classid
select s.*,c.classname    from student s ,classinfo c where s.stuclassid=c.classid 

--查询每个班级的学生人数
SELECT count(*) as 人数 ,stuclassid as 班级 from student GROUP BY stuclassid

--查询每个班级的学生人数,学生性别是男
SELECT count(*) as 人数 ,stuclassid as 班级 from student where stusex='男' GROUP BY stuclassid

--查询每个班级平均年龄大于24的信息  分组再筛选
select avg(stuage),stuclassid  from student GROUP BY stuclassid HAVING avg(stuage)>24

--对年龄做一个升序的排序  降序desc
SELECT * FROM student  ORDER BY stuage
--分页-每页显示5行数据  pageSize=5,PageCode=3
select * from  student LIMIT 0,5

--外连接  左外（以左边的表为基准），右外(以右边的表为基准)

--查询前10条数据
select * from  student LIMIT 0,10

--查询班级编号为1的学生信息（子查询）
 SELECT * from student where stuclassid in (SELECT classid from classinfo where classname='2')                                                                                            

--查询年龄大于平均年龄的所有学生信息
select * from student where stuage>(select avg(stuage) from student )