mysqldaima

liujiaying

-- MySQL 代码

USE t178;
select sex,round(avg(score))
from student01
group by sex;

SELECT *from student01;
select s_name
from student01
group by s_name
having count(s_name)>=2;

SELECT sex,count(*)
from student01
where age>20
group by sex;

select s_name,score
from student01
ORDER BY  score asc
limit 1,3;

select * from student01 where score=(select min(score)from student01);
SELECT *from stu_info;
select *from stu_score;
select *from cource_info;

select stu_info.*,stu_score.*
from stu_info,stu_score
where stu_info.sid=stu_score.sid;

SELECT c1.sid,c1.sname,c2.score
from  stu_info c1,stu_score c2
where c1.sid=c2.sid;

SELECT c1.score,c2.cname
from stu_score c1 ,cource_info c2
where c1.cid=c2.cid;

select c1.sid,c1.sname,c3.cname,c2.score
from stu_info c1,stu_score c2,cource_info c3
where c1.sid=c2.sid and c2.cid=c3.cid;

select date_add(now(),interval -age year)as'出生年份'
from student01;

select c1.sname,c2.score
from stu_info c1,stu_score c2
where c1.sid=c2.sid;

select c1.sname,count（*）
FROM stu_info c1,stu_score c2
Where c1.sid=c2.sid
group by c1.sname;

select c1.sname
FROM stu_info c1,stu_score c2
Where c1.sid=c2.sid
group by c1.sname
having count(*)=4;

select stu_info.*,stu_score.*
from stu_info
right join stu_score on stu_info.sid=stu_score.sid
where stu_score.score is  not null;

select max(hight)
from student01

select s_name from student01 where hight=(select max(hight) from student01);

SELECT hight,s_name
from student01
order by hight DESC
limit 3;

SELECT s_name from student01 where hight in (SELECT hight from student01 where hight>=176);
select * FROM student01 where(age,sex,hight)=(select 19 as'age','女' as'sex',172 as 'hight')
SELECT * from student01 where (age,sex,hight)in(SELECT age,sex,hight from student01 where sid>4);


select cource_info.cname,max(stu_score.score) as 'm_srcoe'
from cource_info,stu_score
where cource_info.cid=stu_score.cid
group by cource_info.cname;

select cource_info.*,stu_score.score,stu_info.*
from cource_info,stu_score,stu_info
where stu_info.sid=stu_score.sid AND cource_info.cid=stu_score.cid;

select cource_info.*,stu_score.score,stu_info.*,max_s.m_srcoe
from cource_info,stu_score,stu_info,(select cource_info.cname,max(stu_score.score) as 'm_srcoe'
from cource_info,stu_score
where cource_info.cid=stu_score.cid
group by cource_info.cname
) as max_s
where stu_info.sid=stu_score.sid AND cource_info.cid=stu_score.cid;

select
sid,s_name,age from student01;

SELECT*
from (SELECT (sid,s_name,age from student01)as 's'
where age>22;

SELECT c1.s_name,c2.s_level,c1.score
from student01 c1 ,scroe_level c2
where c1.score between c2.min_scroe and c2.max_scroe;

SELECT c1.cno,c1.cname ,c1.cpno ,c2.cname
from cource c1, cource c2
where c1.cno=c2.cpno;