sql for mysql

来源：互联网发布：知乎人均985 编辑：程序博客网时间：2024/05/18 01:12

# 显示张永生的三科成绩
SELECT a.name,b.coursename,b.course FROM student a,courses b WHERE a.studentid=b.studentid AND a.name='张永生'
#显示物理的平均分

SELECT AVG(course) FROM courses WHERE coursename='物理'

# 找出物理成绩大于平均分的同学

SELECT a.name,b.coursename,b.course FROM student a,courses b WHERE a.studentid=b.studentid AND coursename='物理' AND course>(SELECT AVG(course) FROM courses WHERE coursename='物理')

# 显示所有人员的自己的三科总成绩和自己的平均分，缺科的按0分算

SELECT a.name,SUM(b.course),AVG(b.course) FROM student a,courses b WHERE a.studentid=b.studentid GROUP BY a.name

#显示所有人员三科总成绩的平均分

SELECT AVG(total) FROM ( SELECT SUM(b.course) AS total   FROM student a,courses b WHERE a.studentid=b.studentid GROUP BY a.name) bbb

# 找出总分数少于平均分的人员

SELECT a.name,SUM(b.course) FROM student a,courses b WHERE a.studentid=b.studentid GROUP BY a.name HAVING ( SUM(b.course) < (SELECT AVG(total) FROM ( SELECT SUM(bb.course) AS total   FROM student aa,courses bb WHERE aa.studentid=bb.studentid GROUP BY aa.name) temp) )

#between的用法
SELECT * FROM courses WHERE course BETWEEN 60 AND 100
# orderby 排序

SELECT DISTINCT coursename   FROM courses ORDER BY coursename ASC
# 多个条件排序
SELECT a.name,b.coursename,b.course FROM student a,courses b WHERE a.studentid=b.studentid ORDER BY coursename ASC,course DESC

# in语句
SELECT * FROM courses WHERE course IN(26,98,33)
# 找出缺科的人 (总共3科）
     # 1 简单的，只统计成绩表找出学生ID
SELECT COUNT(0) kemu, studentid FROM courses GROUP BY studentid HAVING(COUNT(0)< ( SELECT COUNT(DISTINCT coursename) FROM courses) )
     # 2 复杂点关联 student表，显示学生姓名
SELECT COUNT(0) kemu,a.name FROM courses b,student a WHERE a.studentid=b.studentid GROUP BY b.studentid HAVING(COUNT(0)< ( SELECT COUNT(DISTINCT coursename) FROM courses) )

---------------------------------------------------------------建表和数据------------------------

成绩表：
DROP TABLE IF EXISTS `courses`;

CREATE TABLE `courses` (
`coursename` varchar(20) default NULL,
`course` int(3) default NULL,
`studentid` int(4) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

insert into `courses`(`coursename`,`course`,`studentid`) values ('物理',156,1),('物理',26,2),('物理',53,3),('物

理',46,4),('物理',77,6),('物理',36,7),('语文',32,1),('语文',98,2),('语文',53,3),('语文',52,4),('语文',79,6),('语

文',136,7),('化学',73,1),('化学',32,2),('化学',74,3),('化学',32,6),('化学',36,7);

学生表：
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
`studentid` int(4) NOT NULL auto_increment,
`name` varchar(20) default NULL,
`sex` char(1) default NULL,
`age` int(3) default NULL,
PRIMARY KEY (`studentid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=gbk;

insert into `student`(`studentid`,`name`,`sex`,`age`) values (1,'张木生','男',23),(2,'张永生','男',24),(3,'王敏','

女',22),(4,'高峰','男',34),(5,'刘德华','男',32),(6,'乌海飞','男',24),(7,'张学友','男',21);