MySQL(2) 数据库的增删改查

MySQL(2) 数据库的增删改查

#子查询
select
	(select stuName from student where stuId=s.stuId) stuName,
	(select subjectName from subject where subjectId=s.subjectId) subjectName,
	score
from
	score s;

#查询江苏南京的学院信息
select
	*
from
	(
		select
			stuName,phone,gender,age,cityId
		from
			student
		where
			provinceId=1
	)stu
where
		stu.cityId=1;
#查询HTML科目的所有学员成绩
select
	*
from
	score
where
	subjectId
in
	(
		select
			subjectId
		from
			subject
		where
			subjectName = 'html'
	);
#合并查询
select
	S.scoreId,D.stuName,U.subjectName,score
from
	score S,subject U,student D
where
	S.stuId=D.stuId
and
	S.subjectId=U.subjectId;
#创建表单tcity
create table tcity(
	select cityId,cityName from city
);
#创建表单tcounty
create table tcounty(
	select county,countyName,cityId from county
);
#添加城市
insert into tcity(cityId, cityName) value (30,'盐城湖'),(31,'费城');
insert into tcounty(countyId, countyName,cityId) value (199,'盐城小湖'),(200,'小费城');
insert into tcounty(countyId, countyName,cityId) value (201,'盐城小湖',32);
insert into tcounty(countyId,countyName,cityId) value(199,'不可能',32)
#内连接
select
	countyId,cityName,countyName
from
	tcounty O
inner join
	tcity C
on
	O.cityId=C.cityId;
#左外连接
select
	countyId,cityName,countyName
from
	tcounty O
left join
	tcity C
on
	O.cityId=C.cityId;

#右外连接
select
	countyId,cityName,countyName
from
	tcounty O
right join
	tcity C
on
	O.cityId=C.cityId;
#查询总页码
select
	ceil(count(countyId)/10)
from
	tcounty O
left join
	tcity C
on
	O.cityId=C.cityId;
#分页查询
#1,页码 2,记录数
select
	countyId,cityName,countyName
from
	tcounty O
left join
	tcity C
on
	O.cityId=C.cityId
#order by
#	countyId
limit
	0,10;

查询练习

#查询练习
#1、查询考试各科平均成绩最低的三个科目
select
	subjectName,avg(score) avgScore
from
	score S
inner join
	subject J
on
	S.subjectId=J.subjectId
#group by
#	subjectName
order by
	avgScore ASC
limit
	0,3
#2、查询各市平均成绩最高的前三个城市
select
	cityName 城市名称,avg(score) 平均成绩
from
	score S
inner join
	student U
on
	S.stuId=U.stuId
inner join
	city C
on
	U.cityId=C.cityId
group by
	城市名称
order by
	平均成绩 DESC
limit
	0,3;
#3、查询学员平均成绩最高的前5名学院所在的省市县区
#自练、分页查询
select
	S.stuId,stuName,provinceName ,cityName ,countyName ,avg(score) 平均成绩
from
	score S
inner join
	student U
on
	S.stuId=U.stuId
inner join
	province P
on
	U.provinceId=P.provinceId
inner join
	city C
on
	U.cityId=C.cityId
inner join
	county N
on
	U.countyId=N.countyId
group by
	stuName
order by
	平均成绩 DESC
limit
	0,5;

#方法一、子查询
select
	S.stuId,stuName,provinceName ,cityName ,countyName
from
	student S
inner join
(
	select
		stuId
	from
		score
	group by
		stuId
	order by
		avg(score) DESC
	limit
		0,5
)A
on
	S.stuId=A.stuId
inner join
	Province P
on
	S.provinceId=P.provinceId
inner join
	city C
on
	S.cityId=C.cityId
inner join
	county Y
on
	S.countyId=Y.countyId;
#方法二、分组查询
select
	S.stuId,stuName,provinceName ,cityName ,countyName
from
	student S
inner join
	score R
on
	S.stuId=R.stuId
inner join
	Province P
on
	S.provinceId=P.provinceId
inner join
	city C
on
	S.cityId=C.cityId
inner join
	county Y
on
	S.countyId=Y.countyId
group by
	S.stuId
order by
	avg(score) DESC
limit
	0,5;