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);