java mysql查询试题_java 面试题三十二 mysql查询面试题

题一：

新建学生-课程数据库的三个表:

学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;

课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;

学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码;

Student

学号

Sno姓名

Sname性别

Ssex年龄

Sage所在系

Sdept

95001李勇男20CS

95002刘晨女19IS

95003王敏女18MA

95004张立男19IS

学号

Sno课程名

Cname先行课

Cpno学分

Credit

1数据库54

2数学2

3信息系统14

4操作系统63

5数据结构74

6数据处理2

7Pascal语言64

Course:

SC:

学号

Sno课程号

Cno成绩

Grade

95001192

95001285

95001388

95002290

95002380

一:查询表中的列和行

1:查询全体学生的学与姓名

sele sno,sname from student

2:查询全体学生的姓名、学号、所在系。

sele sno,sname,sdept from student

3:查询全体学生的详细记录

sele * from student

4:查询全体学生的姓名及出生年份

sele sno,sage from student

5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名

6:查询选修了课程的学生学号

sele sno,cno from sc

7:查询选修了课程的学生姓名

sele distinct sname from student,sc where student.sno=sc.sno

二:条件查询:

常用的查询条件

查询条件谓词

比较=,,>=,<=,!=,<>,!>,!

not+上述比较运算符

确定范围Between and,Not between And,

确定集合IN,not IN

字符匹配Like,Not Like

空值IsNull,ISNOTNULL

多重条件AND,OR

1:查询计算机系全体学生的姓名

sele sname from student where sdept=”CS”

2:查询所有年龄在20岁以下的学生姓名及其年龄

sele sname,sage from student where sage<20

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

sele student.sno from student,sc where student.sno=sc.sno and grade<60

4:查询年龄在20到23间的学生的姓名,系别及年龄

sele sname,sdept,sage from student where sage between 20 and 23

5: 查询年龄不在20到23间的学生的姓名,系别及年龄

sele sname,sdept,sage from student where sage not between 20 and 23

6:查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别

sele sname,ssex from student where sdeptin("IS","MA","CS")

7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别

sele sname,ssex from student where sdept notin("IS","MA","CS")

8:查询学号为”95001”的学生详细情况

sele * from student where sno=95001

9:查询所有姓刘的学生的姓名,学号和性别(where namelike ‘刘%’)

sele sname,sno,ssex from student where sname like '刘%'

10:查询姓”欧阳”且命名为三个汉字的学生的姓名

sele sname from student where sname like '欧阳_'

11:查询名字中第2个字为”阳”字的学生姓名和学号(where sname like ‘_ _阳%’)

sele sname,sno from student where sname like '_ _阳%'

12:查询所有不姓刘的学生姓名

sele sname from student where sname not like '刘%'

13:查询DB_Design课程的课程号和学分(wherecname like ‘Db\_Design’Escape’\’)

sele cno,gredit from course where cname like ‘Db\_Design’Escape’\’

14:查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况(wherecname like ‘DB\_%i__’escape’\’)

‘DB\_%i__’escape’\’) sele cno,gredit from course where cname like‘Db\_%i__’escape’\’

15:查询缺少成绩的学生的学号和相应的课程号

sele student.sno,cno from student,sc where grade is null

16:查询所有成绩的学生学号和课程号(where grade is not null)

sele student.sno,cno from student,sc where grade is not null

17:查询计算机系年龄在20岁以下的学生姓名

sele sname from student where sdept=”CS” and sage<20

18:查询选修了3号课程的学生的学号及其成绩,分数降序排列

sele student.sno,grade from student,sc where student.sno=sc.sno and sc.cno=3order by grade desc

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

sele * from student order by sdept,sage desc

三:使用集函数

count,sum,avg,max,min

1:查询学生的总人数

2:查询选修了课程的学生人数(select count(distinct sno))

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

4:查询选修1号课程的学生最高分数

5:求各个课程号及相应的选课人数( selsect cno,count (sno); from sc;group by cno)

6:查询选修了3门以上的课程的学生学号

select sno

from sc

group by sno

having count(*)>3四:连接查询:

<1>等值与非等值的连接查询

在连接查询中用来连接两个有的条件称为连接条件或连接谓词,,当连接运算符号为”=”时,称为等值连接,使用如,=,,<=,>=,!=连接时称非等值连接

1:查询每个学生及其选修课程的情况

select student.*,sc.*

from student,sc

where student.sno=sc.sno

<2>自身连接

连接操作在同一个表中进行连接查询

2:查询每一门课的间接先修课(即先修课的先修课)

select first .cno,second.cno

from course first ,course second

where first.cno=second.cno

五：复合条件连接

1:查询选修2号课程且成绩在90分以上的所有学生。

Select student,sname

form student, sc

Where student.sno=sc.sno And

Sc.cno=’2’ andsc.grade>90

六：嵌套查询

1：带有谓词in的子查询

<1>查询与“刘晨”在同一个系学习的学生

select sno,sname,sdept

from student

where sdept in(

select sdept

from student

where sname=”刘晨”)

或：select s1.sname,s1.sdept

from student s1,student s2

where s1.dept=s2.dept and s2.name=”刘晨”

<2>查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname

from student

where sno in

( select sno

from sc

where cno in

(select cno

from course

where cname-“信息系统”)

或：select sno,sname

from student,sc,course

where student.sno=sc.sno and

sc.cno=course.cno and

course.cname=’信息系统’)

2:带有Any 或all谓词的子查询

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

select sname, sage

from student

where sage

from student

where sdept=’is’)

and sdept<>’is’

或用集函数：select sname, sage

from student

where sage<

(select max(sage)

from student

where sdept=’is’)

and sdept<>’is’

<2> 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄

select sname, sage

from student

where sage

(select sage

from student

where sdept=’is’)

and sdept<>’is’3 带有Exitst谓词的子查询

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

select sname

from student

where exists

(select *

from sc

where sno=student.sno and cno=’1’)

<2>查询没有选修1号课程的学生姓名

select sname

form student

where not exists

(select *

form sc

where sno=stuedent.sno andcno=’1’)

<2>查询选修所有全部课程的学生姓名

select sname

from student

where not exists

(select *

from course

where not exists

(select *

from sc

where sno=student.sno

andcno=course.cno)

自己写的

SELECT sc.sno,COUNT(sc.cno) FROM sc GROUPBY sc.sno HAVING COUNT(sc.cno) = (SELECT COUNT(course.Cno) FROM course)

<3>查询到少选修了学生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)[/color]

建表的sql

DROP DATABASE examtest;

CREATE DATABASE examtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE examtest;

CREATE TABLE Student(

Sno INT(5) PRIMARY KEY ,

Sname CHAR(10) NOT NULL,

Ssex CHAR(5),

Sage INT(5),

Sdept CHAR(10)

);

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,'张立','男',19,'IS');

CREATE TABLE Course(

Cno INT(5) PRIMARY KEY,

Cname CHAR(10) ,

Cpno CHAR(10),

Credeit INT(5)

);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(1,'数据库','',54);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(2,'数学','',2);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(3,'信息系统','',14);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(4,'操作系统','',63);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(5,'数据结构','',74);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(6,'数据处理','',2);

INSERT INTO Course(Cno,Cname,Cpno,Credeit) VALUES(7,'Pascal语言','',64);

CREATE TABLE SC(

Sno INT(5) NOT NULL,

Cno INT(5) NOT NULL,

Grade INT(5),

PRIMARY KEY(Sno,Cno)

);

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(95002,3,80);