mysql常用数据库语句操作附加sql包

学生：student

课程：course

成绩：sc

1）查找张友同学所有成绩

SELECT sc.SNO,SCORE,CNO,SNAME FROM sc,student WHERE sc.SNO = student.SNO AND student.SNAME = '张友';

2)查找选择了编译原理的同学信息

SELECT student.SNO,SNAME,AGE,SEX,course.CNO,CNAME,SCORE FROM student,course,sc WHERE sc.SNO = student.SNO AND course.CNO = sc.CNO AND course.CNAME = '编译原理';

3）查找选了“C语言”且分数最高的同学信息，打印学生信息及分数

SELECT * FROM student,course,sc WHERE student.SNO = sc.SNO AND course.CNO = sc.CNO AND course.CNAME = 'c语言' ORDER BY SCORE DESC LIMIT 0,1;

4）求各科目成绩的平均分数

SELECT course.CNAME,AVG(SCORE) FROM course,sc WHERE course.CNO = sc.CNO GROUP BY CNAME;

5）分组显示每个学生选择的所有课程及得分

SELECT student.SNO,student.SNAME,course.CNO,course.CNAME,sc.SNO,sc.CNO,sc.SCORE FROM student,course,sc WHERE student.SNO = sc.SNO AND sc.CNO = course.CNO;

6）按选择了“C语言”课程的学生及分数，按分数倒序显示

SELECT student.SNO,SNAME,course.CNO,CNAME,SCORE FROM student,course,sc WHERE sc.SNO = student.SNO AND sc.CNO = course.CNO AND course.CNAME = 'c语言' GROUP BY sc.SCORE DESC;

7）求张友同学的总分及平均分

SELECT student.SNAME,SUM(SCORE),AVG(SCORE) FROM student,course,sc WHERE student.SNO = sc.SNO AND course.CNO = sc.CNO AND student.SNAME = '张友';

---

数据库：

use test;

createtableifnotexists student

(

SNOvarchar(20)primarykey,

SNAMEvarchar(20)charactersetgbk,

AGEint,

SEXchar(2)charactersetgbkCHECK(SEXIN('男','女'))

);

insertintostudentvalues('1','李强',23,'男');

insertintostudentvalues('2','刘丽',22,'女');

insertintostudentvalues('5','张友',22,'男');

createtableifnotexists course

(

CNOvarchar(20)primarykey,

CNAMEvarchar(20)charactersetgbk,

TEACHERvarchar(20)charactersetgbk

);

insertintocoursevalues('K1','C语言','王华');

insertintocoursevalues('K5','数据库原理','程军');

insertintocoursevalues('K8','编译原理','程军');

createtableifnotexists sc

(

SNOvarchar(20)NOTNULL,

CNOvarchar(20)NOTNULL,

SCOREintNOTNULL,

primarykey(SNO,CNO),

foreignkey(SNO)referencesstudent(SNO),

foreignkey(CNO)referencescourse(CNO)

);

insertintoscvalues('1','K1',83);

insertintoscvalues('2','K1',85);

insertintoscvalues('5','K1',92);

insertintoscvalues('2','K5',90);

insertintoscvalues('5','K5',84);

insertintoscvalues('5','K8',80);