1、查詢出所有的男生

SELECT stuName,stuSex from stu where stuSex='男';

2、查詢出所有的女生并且大于25

SELECT stuName,stuAge from stu where stuSex='女';

3、查詢出年齡大于23，取前三名

SELECT * FROM stu where stuAge>=23 ORDER BY stuAge DESC ;

4、聯合查詢（1）內連接

語法：

select * from 表一 inner join 表二 歐尼表一的公共字段 = 表二的公共字段 where 條件

查詢所有男生筆試成績大于70分

SELECT * from stuinfo inner join stumarks on stumarks.stuNo = stuinfo.stuNo WHERE stuinfo.stuSex = '男' and stumarks.labExam>70 ;

外關聯left（左邊為主）和right（右邊為主)

語法：

select * from 表一 right join 表二 on表一的公共字段 = 表二的公共字段 where 條件

select * from 表一 left join 表二 on表一的公共字段 = 表二的公共字段 where 條件

成績求和/取平均值

SELECT sum(stumarks.labExam) score from stuinfo inner join stumarks on stumarks.stuNo = stuinfo.stuNo; 

1、查詢出所有天津的男生

select * from stuinfo where stuSex = '男'and stuAddress = '天津';

2、查詢所有的女生并且機試成績 小于 60

SELECT * from stuinfo inner join stumarks on stuinfo.stuNo = stumarks.stuNo where stuinfo.stuSex = '女' and stumarks.labExam<60;

3、查詢出年齡大于 23 ，并按倒序排列取前三名學生

SELECT stuName,stuAge from stuinfo  where stuinfo.stuAge > 23 order by stuinfo.stuAge desc LIMIT 3;

4、查詢所有的男生，筆試成績大于 70 分的

SELECT stuName,writtenExam from stuinfo inner join stumarks on stuinfo.stuNo = stumarks.stuNo where stuinfo.stuSex = '男' and stumarks.writtenExam>70;

5、查詢所有女生年齡等于23 或者 stuAddress 等于 北京的

SELECT stuName,stuAge,stuSex FROM stuinfo WHERE stuSex='女' OR stuAddress='北京';

6、查詢出筆試成績的平均分

SELECT avg(stumarks.writtenExam) avg_score FROM stuinfo inner join stumarks on stuinfo.stuNo = stumarks.stuNo;

7、查詢出機試成績的最高分

SELECT stuName,labExam FROM stuinfo inner join stumarks on stuinfo.stuNo = stumarks.stuNo order by stumarks.labExam desc LIMIT 1;

8、查詢出所有學生的總成績，并按 由高到低排列

SELECT stuName,writtenExam+labExam as scores FROM stuinfo left join stumarks on stuinfo.stuNo = stumarks.stuNo order BY scores desc;

補充測試數據庫

/*stu測試數據*/
create table stu
(
	stuNo char(6) primary key,
	stuName varchar(10) not null,
	stuSex char(2) not null,
	stuAge tinyint not null ,
	stuSeat tinyint not null,
	stuAddress varchar(10) not null,
	ch tinyint,
	math tinyint 
)charset=utf8;


insert into stu values ('s25301','張秋麗','男',18,1,'北京',80,null);
insert into stu values ('s25302','李文才','男',31,3,'上海',77,76);
insert into stu values ('s25303','李斯文','女',22,2,'北京',55,82);
insert into stu values ('s25304','歐陽俊雄','男',28,4,'天津',null,74);
insert into stu values ('s25305','諸葛麗麗','女',23,7,'河南',72,56);
insert into stu values ('s25318','爭青小子','男',26,6,'天津',86,92);
insert into stu values ('s25319','梅超風','女',23,5,'河北',74,67);



/*stuinfo測試數據*/
create table stuinfo
(
	stuNo char(6) primary key,
	stuName varchar(10) not null,
	stuSex char(2) not null,
	stuAge tinyint not null ,
	stuSeat tinyint not null,
	stuAddress varchar(10) not null
)charset=utf8;


insert into stuinfo values ('s25301','張秋麗','男',18,1,'北京');
insert into stuinfo values ('s25302','李文才','男',31,3,'上海');
insert into stuinfo values ('s25303','李斯文','女',22,2,'北京');
insert into stuinfo values ('s25304','歐陽俊雄','男',28,4,'天津');
insert into stuinfo values ('s25305','諸葛麗麗','女',23,7,'河南');
insert into stuinfo values ('s25318','爭青小子','男',26,6,'天津');
insert into stuinfo values ('s25319','梅超風','女',23,5,'河北');

/*stuMarks測試數據*/

create table stuMarks
(
	examNo char(7) primary key,
	stuNo char(6) not null ,
	writtenExam int,
	labExam int
)charset=utf8;
insert into stumarks values ('s271811','s25303',80,58);
insert into stumarks values ('s271813','s25302',50,90);
insert into stumarks values ('s271815','s25304',65,50);
insert into stumarks values ('s271816','s25301',77,82);
insert into stumarks values ('s271819','s25318',56,48);