mysql数据库查询语句

1.简单的查询基本表的SQL语句

(1)查询语句

SELECT * FROM course;

(2)查询语句

SELECT * FROM student;

Student表的删除SQL语句：

DROP TABLE student;

选课表的操作

(3) 查询

SELECT * FROM sc ；

(4) 查询SC表按成绩降序排列

SELECT * FROM sc ORDER BY grade DESC;

(5) 查询选择了课程号为2,3,4,的选课信息

SELECT * FROM sc WHERE courseId IN(2,3,4);

(6) 查询没有选择课程号为2,3,4,的选课信息

SELECT * FROM sc WHERE courseId NOT IN(2,3,4);

(7) 查询每门课程的总分以及该门的平均成绩

SELECT sc.courseId,SUM(grade) '总分',AVG(grade) '平均分' FROM sc GROUP BY courseId;

(8) 查询被选修人数超过2个人的课程的名称

SELECT course.cname FROM course WHERE course.id IN (SELECT courseId FROM sc GROUP BY courseId HAVING COUNT(*)>2);

(9)查询所有选了课程学生的信息

SELECT '总选课人数',COUNT(DISTINCT sno) FROM sc;

(10) 利用查询的子查询结果创建视图

CREATE VIEW is_234course AS SELECT scId,sno,grade,courseId FROM sc WHERE courseId BETWEEN 2 AND 4;

(11)对视图的简单操作

DROP VIEW is_234course;
SELECT * FROM is_234course;

(12)查询选择2号课程且成绩为85以上的学生信息

SELECT * FROM student WHERE sno IN(SELECT sno FROM sc WHERE courseId=2 AND grade>=85);

(13)查询课程平均成绩为85以上的课程信息

/不相干嵌套查询/

SELECT * FROM course WHERE course.id  IN(SELECT courseId FROM sc GROUP BY courseId HAVING AVG(grade)>90);

(14)连接查询

SELECT * FROM course,sc WHERE course.id=sc.courseId GROUP BY courseId HAVING AVG(grade)>90;

(15)查询所有的选课信息

SELECT student.sno,student.sname,sc.courseId,sc.grade FROM student LEFT OUTER JOIN sc ON(student.sno=sc.sno);

(16)/查询选修了Java的学生的课程/

SELECT s.sno,s.sname,c.cname,sc.grade FROM sc ,student s,course c WHERE sc.sno=s.sno AND sc.courseId=c.id AND c.cname='Java';

(17)查询和黎涛选了同一门课的学生的信息

SELECT student.* FROM student WHERE student.sno IN (SELECT sno FROM sc WHERE courseId 
IN (SELECT courseId FROM sc,student WHERE sc.sno =student.sno AND student.sname='黎涛')) ;

(18) 查询和黎涛选了同一门课的学生的选课信息

SELECT student.*,course.cname FROM student,sc,course WHERE course.id=sc.courseId 
AND sc.sno=student.sno 
AND student.sno IN (SELECT sno FROM sc WHERE courseId IN (SELECT courseId FROM sc,student
WHERE sc.sno =student.sno AND student.sname='黎涛')) ;

(19)查询和黎涛在一个系的所有学生的信息

SELECT student.* FROM student WHERE sdept =(SELECT sdept FROM student WHERE sname='黎涛');

(20)相干查询

/查询每个学生超出自己选修课平均成绩的课程号/

SELECT sno,courseId FROM sc sc1 WHERE grade > (SELECT AVG(grade)FROM sc sc2 WHERE sc1.sno=sc2.sno);

(21)查询非cs系比cs系的任意一个学生年龄都小的学生的姓名、年龄...

/*注意用any表示的任意结果时求取子查询中最大值的作为父查询的条件，而不是最小 值 或者用MAX() */

SELECT sname,sage FROM student WHERE sdept<>'cs' AND sage< ANY (SELECT sage FROM student WHERE sdept='cs');

SELECT sname,sage FROM student WHERE sdept<>'cs' AND sage< (SELECT MAX(sage) FROM student WHERE sdept='cs');

(22)查询非cs系比cs系的所有学生年龄都小的学生的姓名、年龄...

/*当为小于所有值时，采用all作为约束条件，或者聚合函数MIN() */

SELECT sname,sage FROM student WHERE sdept<>'cs' AND sage<ALL (SELECT sage FROM student WHERE sdept='cs');

SELECT sname,sage FROM student WHERE sdept<>'cs' AND sage< (SELECT MIN(sage) FROM student WHERE sdept='cs');

(23)查询没有选修1号课程的学生的姓名、年龄...

SELECT student.* FROM student WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno=student.sno AND courseId=1 );

(24)查询选修了所有课程的学生的信息

/题目的意思可以翻译成不存在该学生没有选修的课程/

/规定查询的结果要用全表显示，即，不能用其他的形式，由于带有exists的谓词查 询结果只返回true 或 false*/

/基于派生表的查询/**

(25)查询每个学生超出自己选修课平均成绩的课程号

SELECT sc.sno,sc.courseId FROM sc,(SELECT sno,AVG(grade) FROM sc GROUP BY sno) AS sc_avg(avg_sno,avg_grade) 
WHERE sc.sno=sc_avg.avg_sno AND sc.grade >= sc_avg.avg_grade;

(26)查询没有选修1号课程的学生的姓名、年龄...

SELECT student.* FROM student,(SELECT sno FROM sc WHERE sc.courseId=1)AS sc1 WHERE student.sno = sc1.sno;

(27)左外链接

/查询所有选课信息 ,显示了没有选课的学生的信息/

SELECT s.sno,s.sdept,sc.grade,sc.courseId FROM student s LEFT OUTER JOIN sc ON(s.sno=sc.sno);