数据库查询顺序和表联合

<1>顺序一定要:select 选项 表名  from  where group by having order by  limit

select sid,sum(score) chengji from  mark group by sid order by chengji desc limit 1

sid 字段         chengji 为sum(score)的别名        Mark为表名

 <2>多表查詢內连接 :

select  显示的内容 from 表一 join 表二(如果表长可以命别名)  on  表一中的什么内容与表二的内容相等

select name from stu join (select sid,sum(score) chengji   from mark  group by sid order by  chengji desc limit 1) s on stu.id=s.sid ;

运用为:

学生表结构

create table stu(id int primary key auto_increment,name varchar(255)) engine=innodb charet=utf8;

stu表的结构

insert into stu(name) values('小明'),('小红'),('小兰'),('小黄');

Mark成绩表的结构

CREATE TABLE `mark` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sid` int(10) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mark 表的数据

insert mark(sid,subject,score) values(1,'语文',87),(1,'数学',90),(2,'语文',67),(2,'数学',98),(3,'语文',95),(3,'数学',96);

1. 用一条sql语句求出总分最高的学生姓名?

   select name from stu join (select sid,sum(score) chengji   from mark  group by sid order by  chengji desc limit 1) s on stu.id=s.sid ;