实践：

总结：

学生分数，总和，降序排列。

SELECT id,stname,math+english+chiniese AS ss FROM score ORDER BY ss DESC;

查询平均成绩大于60分的同学的学号和平均成绩。（后两者可行，前一个是我随意写的。）

SELECT stuid FROM score WHERE cid=1;

SELECT stuid,SUM(s)/3 AS ss FROM score WHERE stuid=1;

SELECT stuid,AVG(s) FROM score GROUP BY stuid HAVING AVG(s)>10;

查询所有同学的学号、姓名、选课数、总成绩。

SELECT student.stuid,student.stuname,COUNT(score.cid),SUM(score.s) FROM Student LEFT OUTER JOIN score ON student.stuid=score.stuid GROUP BY Student.stuid, Student.stuname;

 

题目1：

源代码：

执行：select语句变成如下：

源代码：

CREATE DATABASE t_table;

 

CREATE TABLE score(

id INT PRIMARY KEY AUTO_INCREMENT,

stname VARCHAR(10) NOT NULL,

math INT,

english INT,

chiniese INT

)CHARSET utf8;

 

INSERT INTO score(id,stname,math,english,chiniese) VALUES

(1,'xiaoming',7,5,6),

(3,'cai',7,5,6),

(2,'hong',5,2,3)

 

学生分数降序排列。

SELECT id,stname,math+english+chiniese AS ss FROM score ORDER BY ss DESC;

 

题目2：

源代码：

CREATE DATABASE test;

CREATE TABLE student(

stuid INT PRIMARY KEY AUTO_INCREMENT,

stuname VARCHAR(20),

age INT,

gender VARCHAR(20)

)

CREATE TABLE course(

cid INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(20),

tid INT

)

CREATE TABLE score(

stuid INT,

cid INT,

s INT

)

INSERT INTO student(stuid,stuname,age,gender)VALUES      //学生

(1,'ming',18,'girl'),

(2,'hong',19,'boy'),

(3,'king',10,'boy'),

(4,'ging',13,'girl');

 

INSERT INTO course(cid,cname,tid)VALUES             //课程

(1,'english',1),

(2,'chiniese',2),

(3,'math',3);

 

INSERT INTO score(stuid,cid,s)VALUES             //分数

(1,1,90),

(1,3,30),

(1,2,50),

(4,2,99),

(3,1,44);

 

查询平均成绩大于60分的同学的学号和平均成绩。

SELECT stuid FROM score WHERE cid=1;

SELECT stuid,SUM(s)/3 AS ss FROM score WHERE stuid=1;

SELECT stuid,AVG(s) FROM score GROUP BY stuid HAVING AVG(s)>10;

查询所有同学的学号、姓名、选课数、总成绩。

SELECT student.stuid,student.stuname,COUNT(score.cid),SUM(score.s) FROM Student LEFT OUTER JOIN score ON student.stuid=score.stuid GROUP BY Student.stuid, Student.stuname;