MySql数据库查询

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