MySQL练习题

create 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,'202101')
insert into classinfo values (null,'202102')
insert into classinfo values (null,'202103')

insert into student values ('001','张三','男',18,'湖北武汉',1)
insert into student values ('002','张思','男',22,'湖南长沙',2)
insert into student values ('003','李四','女',19,'河南南阳',3)
insert into student values ('004','张赞','男',23,'河北承德',3)
insert into student values ('005','王五','女',28,'湖北武汉',2)
insert into student values ('006','赵六','男',16,'北京',1)
insert into student values ('007','王和','女',17,'上海',1)
insert into student values ('008','钱德','男',18,'广州',2)
insert into student values ('009','赵塔','女',20,'深圳',1)
--查询班级信息
select * from classinfo
select*from student
--修改学生地址
update student set stuaddress='美国洛杉矶' where stuid = '001'
--删除某个学生信息
delete from student where stuid = '009'
--查询某个学生年龄大于20的学生信息
SELECT * FROM student WHERE stuage >20
--查询学生年龄在20到30之间的学生信息
SELECT * FROM student WHERE stuage BETWEEN 20 AND 30
--查询性别实女生并且年龄大于30的学生信息
SELECT * FROM student WHERE stuage >30 AND stusex = '女'
--查询性别为女的学生信息
SELECT * FROM student WHERE stusex = '女'
--查询地址是湖北武汉的学生信息
SELECT * FROM student WHERE stuaddress = '湖北武汉'
--查询班级编号为3的学生信息
SELECT * FROM student WHERE stuclassid = 3
--查询学生姓张的学生信息  模糊查询
SELECT * FROM student WHERE stuname LIKE '张%'
--查询学生姓C的学生信息并且年龄大于24岁的学生信息
SELECT * FROM student WHERE stuname LIKE '张%' AND stuage>24
--查询学生姓名中有c的学生信息
SELECT * FROM student WHERE stuname LIKE '%张%'
--内连接查询   inner join on 查询学生信息，显示班级名称
SELECT s.*,c.className as 班级名称 FROM student s INNER JOIN classinfo c ON s.stuclassid = c.classid
--查询每个班级的学生人数
SELECT count(*) as 人数,stuclassid FROM student GROUP BY stuclassid
--查询每个班级的学生人数,学生性别是男
SELECT count(*) as 人数,stuclassid FROM student WHERE stusex = '男' GROUP BY stuclassid 
--查询每个班级平均年龄大于24的信息  分组再筛选
SELECT * FROM student GROUP BY stuclassid HAVING AVG(stuage) > 24
--对年龄做一个升序的排序  降序desc
SELECT * FROM student ORDER BY stuage DESC

SELECT * FROM student ORDER BY stuage 
--分页-每页显示5行数据  pageSize=5,PageCode=3
SELECT * FROM student LIMIT 0,5
--外连接  左外（以左边的表为基准），右外(以右边的表为基准)
SELECT s.*,c.className FROM student s LEFT OUTER JOIN classinfo c on s.stuclassid = c.classid
--查询前10条数据
SELECT * FROM student LIMIT 0,10
--查询班级编号为1的学生信息（子查询）
SELECT * FROM student WHERE stuclassid = 1
--查询年龄大于平均年龄的所有学生信息
SELECT * FROM student WHERE stuage>(SELECT AVG(stuage) FROM student)