MySQL习题 求数据库高手解答

create table stu1 (

Id int auto_increment primary KEY,

Name VARCHAR(20) not null ,

Sex varchar(4) ,

Brith Date ,

department VARCHAR(20)not null,

address VARCHAR(50)

)

create table score(

Id INT auto_increment PRIMARY KEY,

stu_id int,

FOREIGN KEY(stu_id) REFERENCES stu1(Id) on DELETE CASCADE,

C_name VARCHAR(20),

grade int

)

SELECT *from stu1

INSERT INTO stu1 VALUES( 901,'张老大', '男','1985-06-05','计算机系', '北京市海淀区')

INSERT INTO stu1 VALUES( 902,'张老二', '男','1986-06-05','中文系', '北京市昌平区')

INSERT INTO stu1 VALUES( 903,'张三', '女','1990-06-05','中文系', '湖南省永州市')

INSERT INTO stu1 VALUES( 904,'李四', '男','1990-06-05','英语系', '辽宁省阜新市')

INSERT INTO stu1 VALUES( 905,'王五', '女','1991-06-05','英语系', '福建省厦门市')

INSERT INTO stu1 VALUES( 906,'王六', '男','1988-06-05','计算机系', '湖南省衡阳市')

SELECT *from Score

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '计算机',98)

INSERT INTO score(stu_id,C_name,grade) VALUES(901, '英语', 80)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '计算机',65)

INSERT INTO score(stu_id,C_name,grade) VALUES(902, '中文',88)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '中文',95)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '计算机',70)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '英语',92)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '英语',94)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '计算机',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(906, '英语',85)

/*查询 student 表的第 2 条到 4 条记录*/

SELECT * from stu1 LIMIT 2,2

/*从 student 表中查询计算机系和英语系的学生的信息*/

SELECT *from stu1 WHERE department in('计算机系','英语系')

/*从 student 表中查询年龄 18~22 岁的学生信息*/

SELECT YEAR('1990-09-09')

SELECT YEAR(NOW())-YEAR('1990-09-09')

SELECT s.Name,s.age,s.Brith from (SELECT *,YEAR(NOW())-YEAR(Brith)as age FROM stu1)as s WHERE s.age>23 and s.age<28

/*8.从 student 表中查询每个院系有多少人*/

SELECT department,COUNT(id) from stu1 GROUP BY(department)

/*从 score 表中查询每个科目的最高分*/

(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

SELECT id,h.C_name,h.scote_m FROM score,(SELECT C_name,max(grade)as scote_m from Score GROUP BY C_name)as h

WHERE h.C_name=Score.C_name and h.scote_m=Score.grade

/*10.查询李四的考试科目（c_name）和考试成绩（grade）*/

SELECT name,C_name,grade from stu1,score where name='李四'and stu1.id=score.stu_id

SELECT stu_id,C_name,grade FROM score WHERE stu_id in(SELECT id FROM stu1 where name='李四')

/*12.计算每个学生的总成绩*/

SELECT grade_sum_t.grade_sum,grade_sum_t.stu_id,NAME,department FROM stu1,

(SELECT sum(grade)as grade_sum,stu_id from score GROUP BY stu_id )as grade_sum_t

where grade_sum_t.stu_id=stu1.id

/*13.计算每个考试科目的平均成绩*/

SELECT C_name,AVG(grade) from score GROUP BY C_name

/*14.查询计算机成绩低于 95 的学生信息*/

SELECT * FROM stu1 WHERE id IN(SELECT stu_id from score WHERE C_name='计算机' AND grade<95)

/*15.查询同时参加计算机和英语考试的学生的信息*/

SELECT * FROM stu1 WHERE id IN(

SELECT stu_id FROM score WHERE C_name='英语' AND stu_id in(

SELECT stu_id FROM score WHERE C_name='计算机'))

/*16.将计算机考试成绩按从高到低进行排序*/

/*18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩*/

SELECT Name,department,C_name,grade FROM stu1,score WHERE stu1.id=score.stu_id AND (Name LIKE '王%' OR Name LIKE '张%')

SELECT *from Score

/*19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩*/

SELECT YEAR(NOW())-YEAR(Brith) FROM stu1

SELECT Name,YEAR(NOW())-YEAR(Brith) as age,department,C_name,grade FROM stu1,score WHERE stu1.Id=score.stu_id AND address LIKE '湖南%'

/*20-*/

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '物理',80)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '化学',53)

INSERT INTO score(stu_id,C_name,grade) VALUES(903, '生物',59)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '物理',55)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '化学',56)

INSERT INTO score(stu_id,C_name,grade) VALUES(904, '生物',50)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '物理',100)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '化学',90)

INSERT INTO score(stu_id,C_name,grade) VALUES(905, '生物',54)

SELECT stu_id,AVG(grade) FROM score WHERE stu_id IN(SELECT stu_id FROM score1 WHERE grade<60 GROUP BY stu_id HAVING COUNT(stu_id)>=2)GROUP BY stu_id

SELECT *FROM score1

请参考下列语句

1)用最早购买时间作为判断条件

select b.userid,min(u.`name`) as `name`,

min(b.`time`) as `time` from user u inner join buy b on u.id=b.userid group by b.userid

2) 用每个用户第一条购买记录id做判断。

select b.userid,u.`name`,b.`time` from

user u inner join

(select * from buy where id in

(select min(id) from buy group by

userid)) b on u.id=b.userid

如果想将没有购买记录的客户也列出来，那么上述两个语句中的inner join请该为left join。

---