mysql精确查询语句,MySQL根本查询语句练习

MySQL基本查询语句练习

努力很久只为获得别人尊重的眼光。

——我是，董宏宇，我为自己代言。

技术交流QQ：1358506549(请注明你的来意)

use xsx;

CREATE TABLE Course(

Cno char(3) NOT NULL ,

Cname varchar (50) NULL ,

Cpno char (3) NULL ,

Ccredit tinyint NULL

);

CREATE TABLE SC(

Sno char(5) NOT NULL ,

Cno char(3) NOT NULL ,

Grade tinyint NULL,

primary key(Sno,Cno)

) ;

CREATE TABLE Student (

Sno char(5) NOT NULL primary key,

Sname char(10) NULL ,

Ssex char(2) NULL ,

Sage int NULL ,

Sdept char(4) NULL

) ;

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95001','李勇','男',20,'CS');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95002','刘晨','女',19,'IS');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95003','王敏','女',18,'MA');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95004','张立','男',21,'IS');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95005','林燕芳','女',18,'IS');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95006','林燕虹','女',17,'IS');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95007','欧阳锋','男',19,'MA');

Insert into student (Sno,Sname,Ssex,Sage,Sdept) Values('95008','欧阳木兰','女',16,'CS');

Insert into course (Cno,Cname,Cpno,Ccredit) Values('1','数据库','5',4);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('2','数学',NULL,2);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('3','信息系统','1',4);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('4','操作系统','6',3);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('5','数据结构','6',3);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('6','数据处理', '2' ,2);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('7','PASCAL语言','6',4);

Insert into course (Cno,Cname,Cpno,Ccredit) Values('8','DB_DESIGN','6',3);

Insert into sc (Sno,Cno,Grade) Values('95001','1',92);

Insert into sc (Sno,Cno,Grade) Values('95001','2',85);

Insert into sc (Sno,Cno,Grade) Values('95001','3',88);

Insert into sc (Sno,Cno,Grade) Values('95001','4',23);

Insert into sc (Sno,Cno,Grade) Values('95001','5',34);

Insert into sc (Sno,Cno,Grade) Values('95001','6',56);

Insert into sc (Sno,Cno,Grade) Values('95001','7',86);

Insert into sc (Sno,Cno,Grade) Values('95001','8',88);

Insert into sc (Sno,Cno,Grade) Values('95002','2',90);

Insert into sc (Sno,Cno,Grade) Values('95002','3',80);

Insert into sc (Sno,Cno,Grade) Values('95003','1',50);

Insert into sc (Sno,Cno,Grade) Values('95003','3',98);

Insert into sc (Sno,Cno,Grade) Values('95005','3',null);

//查询全体学生的学号和姓名

select sno,sname from student;

//查询全体学生的姓名、学号和所在系

select sno,sname,sdept from student;

//查询全体学生的详细记录

select * from student;

//查询全体学生的姓名及其出生年份

select sname,sage from student;

//查询全体学生姓名、出生年份和所在系，要求用小写字母表示所有系名

select sname,sage,lower(sdept) from student;

//查询选修了课程的学生学号

select distinct student.sno from student,sc where student.Sno=sc.Sno;

//查询计算机系(CS)所有学生的名单

select sname from student where sdept="cs";

//查询所有年龄在20以下学生的姓名和年龄

select sname,sage from student where sage<20;

//查询考试成绩有不及格的学生的学号

select sno from sc where grade<60;

// 查询年龄在20-23 (包括20和23)之间的学生的姓名、系别和年龄

select sname,sdept,sage from student where sage<=23 and sage>=20;

//查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别

select sname,ssex from student where sdept="is" or sdept="ma" or sdept='cs';

//查询学号为95001的学生的详细情况

select * from student where sno=95001;

//查询所有姓林的学生的姓名、学号和性别

select sname,sno,ssex from student where sname like "林%";

//查询姓“欧阳”且全名为三个汉字的学生的姓名

select sname from student where sname like "欧阳_";

//查询名字中第二个字为“燕”字的学生姓名和学号

select sname,sno from student where sname like "_燕%";

//查询所有不姓“刘”的学生的姓名

select sname from student where sname not like "刘%";

//查询课程名为“DB_DESIGN”的课程号的学分

select cno,ccredit from course where cname="DB_DESIGN";

//查询缺少成绩的学生的学号和相应的课程号(成绩字段值为Null)

select Sno,Cno from sc where grade<=>null;

//查询所有有成绩的学生的学号和课程号

select sno,cno from sc where not grade<=>null;

//查询所有计算机系年龄在20以下的学生姓名

select sname from student where sage<20 and sdept="cs";

//查询选修了3号课程的学生的学号和成绩，查询结果按分数降序排列

select sno,grade from sc where cno=3 order by grade desc;

//查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

select * from student order by sdept,sage desc; ( asc是默认排序方式)

//查询学生总人数

select count(*) as '学生总人数' from student;

//查询选修了课程的学生人数

select count(*) as '选修了课程的学生人数' from (select sno,count(*) as '选修了课程数' from sc group by sno)as s;

//计算1号课程的学生的平均成绩

select avg(grade)as'1号课程的学生的平均成绩' from sc where cno='1';

//计算1号课程的学生的最高成绩分数

select max(grade)as '1号课程的学生的最高成绩分数' from sc where cno='1';

//求各个课程号及相应的选课人数

select cno as '专业号',count(*) from sc group by cno;

//查询选修了三门以上课程的学生学号

select sno from sc group by sno having count(cno)>3;

//查询每个学生及其选修课情况

select student.sno,sname,cname from sc join student on student.Sno=sc.Sno join course on sc.Cno=course.Cno;

select student.sno,sname,cname from sc join student on student.Sno=sc.Sno join course on sc.Cno=course.Cno order by sno asc;

//查询每一门课的间接先行课

select course.Cno,course.Cname,course2.cpno from course,course course2 where course.Cpno=course2.cno order by cno asc;

//选修2号课程且成绩在90以上的学生的学号和姓名

select student.sno,sname from sc join student on student.Sno=sc.Sno where sc.Cno=2 and sc.Grade>90;

//查询每个学生的学号、姓名、选修的课程名及成绩

select student.Sno,sname,course.Cname,sc.Grade from sc join student on sc.Sno=student.Sno join course on sc.Cno=course.Cno order by sno asc;

//查询与’林燕芳’在同一个系学习的学生姓名

select sname from student where sdept = (select sdept from student where sname='林燕芳') and sname!='林燕芳';

// 查询选修了“信息系统”的学生学号和姓名

select sno,sname from student where sno=any(select sno from sc where Cno=all(select cno from course where Cname='信息系统'));

//查询其他系中比信息系某一学生小的学生姓名和年龄

select sname,sage from student where sage

//查询所有选修了1号课程的学生的学生姓名

select sname from student where sno in (select sno from sc where cno=1);

//查询选修了全部课程的学生姓名

select sname from student where not exists (select * from course where not exists (select * from sc where sno=student.Sno and cno=course.Cno));

//至少选修了学生95002选修全部课程的学生号码

select distinct sno from sc scx

where not exists

(select * from sc scy where scy.sno='95002' and not exists

(select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));

select student.sno,student.sname,student.ssex,student.sage,student.Sdept,sc.cno,sc.grade,course.cname,course.cpno,

course.ccredit from student,sc,course where student.sno=95001 and student.Sno=sc.Sno and sc.Cno=course.Cno;

select student.Sno,sname,ssex,sage,sdept,course.Cname,sc.Grade from sc join student on student.Sno=sc.Sno

join course on sc.Cno=course.Cno where student.Sno=95001;

选修数据库的人详细信息

select * from student where sno in(select sno from sc where cno in(select cno from course where cname='数据库'));

努力很久只为获得别人尊重的眼光。

——我是，董宏宇，我为自己代言。

技术交流QQ：1358506549(请注明你的来意)