葡京网投哪个正规 > 新葡亰-编程 > Server面试题整合,多表查询

原标题:Server面试题整合,多表查询

浏览次数:61 时间:2020-03-25

核心提示:请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录

一、表关系

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

一、多表查询

--编写多表查询语句的一般过程

--(1)、分析句子要涉及到哪些表

--(2)、对应的表中要查询哪些关联字段

--(3)、确定连接条件或筛选条件

--(4)、写成完整的SQL查询语句

1、多表查询指使用SQL查询时不只是一张表的查询,要点:

① 多个表之间必须建立连接关系

② 表别名的用法

③ 如果from后面使用了表的别名 ,则select后和where后必须用别名代替

3。表内容如下 ----------------------------- ID LogTime 1 2008/10/10 10:00:00 1 2008/10/10 10:03:00 1 2008/10/10 10:09:00 2 2008/10/10 10:10:00 2 2008/10/10 10:11:00 ...... -----------------------------

先创建如下表,并创建相关约束

name kecheng fenshu 

二、案例一

学生表student:

+-----+--------+-----+-------+------------+--------------+

| id  | name  | sex | birth | department | address      |

+-----+--------+-----+-------+------------+--------------+

| 901 | 张老大 | 男  |  1985 | 计算机系  | 北京市海淀区 |

| 902 | 张老二 | 男  |  1986 | 中文系    | 北京市昌平区 |

| 903 | 张三  | 女  |  1990 | 中文系    | 湖南省永州市 |

| 904 | 李四  | 男  |  1990 | 英语系    | 辽宁省阜新市 |

| 905 | 王五  | 女  |  1991 | 英语系    | 福建省厦门市 |

| 906 | 王六  | 男  |  1988 | 计算机系  | 湖南省衡阳市 |

+-----+--------+-----+-------+------------+--------------+

成绩表score:

+----+--------+--------+-------+

| id | stu_id | c_name | grade |

+----+--------+--------+-------+

|  1 |    901 | 计算机 |    98 |

|  2 |    901 | 英语  |    80 |

|  3 |    902 | 计算机 |    65 |

|  4 |    902 | 中文  |    88 |

|  5 |    903 | 中文  |    95 |

|  6 |    904 | 计算机 |    70 |

|  7 |    904 | 英语  |    92 |

|  8 |    905 | 英语  |    94 |

|  9 |    906 | 计算机 |    90 |

| 10 |    906 | 英语  |    85 |

+----+--------+--------+-------+

1、男同学的考试科目

select distinct(c_name) from score s1,student s2 where s2.id=s1.stu_id and sex='男‘;

select distinct(c_name) from score where stu_id in (select id from student where sex='男');

2、姓张同学的考试科目

select distinct(c_name) from score where stu_id in (select id from student where name like '张%');

select c_name from score,student where score.stu_id=student.id and name like '张%';

3、同时学习英语和计算机的学生信息

select * from student where id in (select stu_id from score where c_name='计算机'  and stu_id in (select stu_id from score where c_name='英语'));

select s1.* from student s1,score s2,score s3 where s1.id=s2.stu_id and s1.id=s3.stu_id and s2.c_name='计算机' and s3.c_name='英语';

练习:

1、女同学的考试科目

2、同时学习中文和计算机的学生信息;

3、姓王的同学并且有一科以上成绩大于80分的学生信息;

4、查询李四的考试科目(c_name)和考试成绩(grade)

select c_name,grade from score,student where student.id=score.stu_id and name='李四';

5、查询计算机成绩低于95的学生信息

select student.* from score,student where student.id=score.stu_id and c_name='计算机' and grade<95;

6、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

select name,department,c_name,grade from student,score where student.id=score.stu_id and (name like '王%' or name like '张%' )

练习:

1、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

2、成绩大于80分的女同学的信息

3、查询出女生成绩最低的人的姓名;

请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录.

  1. 班级表class

    create table class ( cid int primary key auto_increment, caption char(10), grade_id int );

    insert into class values(1,'少一一班',1),(2,'少二一班',2),(3,'少三二班',3),(4,'少四一班',4),(5,'少五三班',5);

张三 语文 81

案例二

如下,有三张表:

学生表student:

+-----+-------+-----+-----+

| SNO | SNAME | AGE | SEX |

+-----+-------+-----+-----+

|  1 | 李强  |  23 | 男  |

|  2 | 刘丽  |  22 | 女  |

|  5 | 张友  |  22 | 男  |

+-----+-------+-----+-----+

课程表course:

+-----+------------+---------+

| CNO | CNAME      | TEACHER |

+-----+------------+---------+

| k1  | c语言      | 王华    |

| k5  | 数据库原理 | 程军    |

| k8  | 编译原理  | 程军    |

+-----+------------+---------+

成绩表sc:

+-----+-----+-------+

| SNO | CNO | SCORE |

+-----+-----+-------+

|  1 | k1  |    83 |

|  2 | k1  |    85 |

|  5 | k1  |    92 |

|  2 | k5  |    90 |

|  5 | k5  |    84 |

|  5 | k8  |    80 |

+-----+-----+-------+

1、检索"李强"同学不学课程的课程号(CNO);

select cno from course where cno not in (select cno from

sc,student where sname='李强' andstudent.sno=sc.sno) ;

2、查询“李强”同学所有课程的成绩:

select score from student,sc where

student.sname='李强' and student.sno=sc.sno;

3、查询课程名为“C语言”的平均成绩

select avg(score) from sc,course where cname='c语言' and course.cno=sc.cno;

练习:

1、求选修K1 课程的学生的平均年龄;

select avg(age)from student,sc where student.sno=sc.sno and cno='k1';

2、求王老师所授课程的每门课程的学生平均成绩。

select avg(score) from sc,course where teacherlike '王%' andcourse.cno=sc.cno group by sc.cno;

几道经典的SQL笔试题目

 

张三 数学 75

案例三

有四张表格:

学生表student:

+-----+-------+---------------------+------+

| sid | sname | sage                | ssex |

+-----+-------+---------------------+------+

| 01  | 赵雷  | 1990-01-01 00:00:00 | 男  |

| 02  | 钱电  | 1990-12-21 00:00:00 | 男  |

| 03  | 孙风  | 1990-05-06 00:00:00 | 男  |

| 04  | 李云  | 1990-08-06 00:00:00 | 男  |

| 05  | 周梅  | 1991-12-01 00:00:00 | 女  |

| 06  | 吴兰  | 1992-03-01 00:00:00 | 女  |

| 07  | 郑竹  | 1898-07-01 00:00:00 | 女  |

| 08  | 王菊  | 1990-01-20 00:00:00 | 女  |

+-----+-------+---------------------+------+

教室表teacher:

+-----+-------+

| tid | tname |

+-----+-------+

| 01  | 张三  |

| 02  | 李四  |

| 03  | 王五  |

| 04  | 赵六  |

+-----+-------+

课程表course:

+-----+-------+-----+

| cid | cname | tid |

+-----+-------+-----+

| 01  | 语文  | 02  |

| 02  | 数学  | 01  |

| 03  | 英语  | 03  |

| 04  | 物理  | 04  |

+-----+-------+-----+

成绩表score:

+-----+-----+-------+

| sid | cid | score |

+-----+-----+-------+

| 01  | 01  |    80 |

| 01  | 02  |    90 |

| 01  | 03  |    99 |

| 02  | 01  |    70 |

| 02  | 02  |    60 |

| 02  | 02  |    80 |

| 03  | 01  |    80 |

| 03  | 02  |    80 |

| 03  | 03  |    80 |

| 04  | 01  |    50 |

| 04  | 02  |    30 |

| 04  | 03  |    20 |

| 05  | 01  |    76 |

| 05  | 02  |    87 |

| 06  | 01  |    31 |

| 06  | 03  |    34 |

| 07  | 02  |    89 |

| 07  | 03  |    98 |

+-----+-----+-------+

题目:

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

1.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score,c.score from student a , score b , score c where a.sid = b.sid and a.sid = c.sid and b.cid = '01' and c.cid = '02' and b.score > c.score

1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程 的情况(不存在时显示为 null)

select a.* , b.score ,c.score from student a left join score b on a.sid = b.sid and b.cid = '01' left join score c on a.sid = c.sid and c.cid = '02' where b.score>IFNULL(c.score,0)

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.sid , a.sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , score b where a.sid = b.sid group by a.sid , a.sname having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.sid

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(注意成绩为null的学生,使用ifnull()函数)

select a.sid , a.Sname , IFNULL(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join score b on a.sid = b.sid group by a.sid , a.sname having ifnull(cast(avg(b.score) as decimal(18,2)),0) < 60 order by a.Sid

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.sid AS 学生编号, a.Sname as 学生姓名, count(b.cid) 选课总数, sum(score) 所有课程的总成绩 from Student a left join score b on a.sid = b.sid group by a.sid,a.Sname order by a.sid

表名:成绩表 姓名 课程 分数 张三 语文 81 张三 数学 75 李四 语文 56 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 49 ……

  1. 学生表student

    create table student ( sid int primary key auto_increment, sname char(10), gender enum('男','女') not null, class_id int );

    insert into student values(1,'乔丹','女',1),(2,'艾弗森','女',1),(3,'科比','男',2),(4,'葫芦娃','男',3),(5,'张三丰','男',5),(6,'洞房不败','男',4),(7,'樱木花道','男',2),(8,'松岛菜菜子','女',3),(9,'洞房不败','女',5);

李四 语文 76

给出成绩全部合格的学生信息,注:分数在60以上评为合格

 

李四 数学 90

select * from score

  1. 老师表teacher

    create table teacher ( tid int primary key auto_increment, tname char(10) );

王五 语文 81

where s_name not in

 

王五 数学 100

(select s_name from score

一、表关系请创建如下表,并创建相关约束1. 班级表class【创建表语句】create table class(cid int primary key auto_increment,caption char(10),grade_id int);
【插入记录语句】insert into class values(1,'少一一班',1),(2,'少二一班',2),(3,'少三二班',3),(4,'少四一班',4),(5,'少五三班',5); 

王五 英语 90

where score60)

  1. 学生表student【创建表语句】create table student(sid int primary key auto_increment,sname char(10),gender enum('男','女') not null,class_id int);
    【插入记录语句】insert into student values(1,'乔丹','女',1),(2,'艾弗森','女',1),(3,'科比','男',2),(4,'葫芦娃','男',3),(5,'张三丰','男',5),(6,'洞房不败','男',4),(7,'樱木花道','男',2),(8,'松岛菜菜子','女',3),(9,'洞房不败','女',5); 
  2. 老师表teacher【创建表语句】create table teacher(tid int primary key auto_increment,tname char(10));
    【插入记录语句】Insert into teacher values(1,'张三'),(2,'李四'),(3,'王五'),(4,'萧峰'),(5,'一休哥'),(6,'诸葛'),(7,'李四'); 
  3. 课程表course【创建表语句】create table course(cid int primary key auto_increment,cname char(10),teacher_id int);
    【插入记录语句】insert into course values(1,'生物',1),(2,'体育',1),(3,'物理',2),(4,'数学',3),(5,'语文',4),(6,'英语',2),(7,'土遁?沙地送葬',5),(8,'夏日喂蚊子大法',3),(9,'麻将牌九扑克千术',6); 
  4. 成绩表score【创建表语句】create table score(sid int primary key auto_increment,student_id int,course_id int,score int);
    【插入记录语句】insert score values(1,1,1,60),(2,1,2,21),(3,2,2,99),(4,3,3,56),(5,4,1,56),(6,5,3,94),(7,5,4,40),(8,6,4,80),(9,7,3,37),(10,8,5,100),(11,8,6,89),(12,8,7,0),(13,3,8,45),(14,7,1,89),(15,2,7,89),(16,2,1,61); 
  5. 年级表class_grade【创建表语句】create table class_grade(gid int primary key auto_increment,gname char(10));
    【插入记录语句】insert class_grade values(1,'一年级'),(2,'二年级'),(3,'三年级'),(4,'四年级'),(5,'五年级'); 
  6. 班级任职表teach2cls【创建表语句】create table teach2cls(tcid int primary key auto_increment,tid int,cid int);
    【插入记录语句】insert into teach2cls values(1,1,1),(2,1,2),(3,2,1),(4,3,2),(5,4,5),(6,5,3),(7,5,5),(8,6,2),(9,6,4),(10,6,3),(11,4,1),(12,1,4); 
    二、操作表★注:由于样本数量有限,为了能够得到足够的查询结果,所有题目中涉及到“超过”或“以上”字样的,均默认为包含该值(例如:查询教授课程超过2门的老师的id和姓名,视作教授课程数>=2)
    1、自行创建测试数据;(创建语句见"一、表关系")
    2、查询学生总人数;select count(*) as 学生总人数 from student;
    3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名;【查法1——子查询】select sid, snamefrom student where sid in(select student_idfrom scorewhere student_id in(select student_idfrom scorewhere course_id = (select cid from course where cname = '生物') and score >= 60)and course_id = (select cid from course where cname = '物理') and score >= 60);
    【查法2——联表】select sid, sname from studentwhere sid in (select t1.student_id from (select student_id from scorewhere course_id = (select cid from course where cname = '生物') and score >= 60) as t1inner join (select student_id from score where course_id = (select cid from course where cname = '物理') and score >= 60) as t2on t1.student_id=t2.student_id);
    4、查询每个年级的班级数,取出班级数最多的前三个年级select class.grade_id, class_grade.gname, count(class.cid) as 班级数from class inner join class_grade on class.grade_id=class_grade.gidgroup by class.grade_idorder by count(class.cid) desclimit 3;
    5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩select stu.sid, stu.sname, avg(score) as 平均成绩from student as stu inner join score as scoon stu.sid = sco.student_idgroup by stu.sidhaving avg(score) = (select avg(score) from score group by student_idorder by avg(score) desclimit 1) or avg(score) = (select avg(score) from score group by student_idorder by avg(score) asclimit 1);
    6、查询每个年级的学生人数;select t1.gname, count(s.sid) as 学生人数from (select * from class as c inner join class_grade as g on c.grade_id = g.gid) as t1inner join student as s on t1.cid = s.class_idgroup by t1.gid;
    7、查询每位学生的学号,姓名,选课数,平均成绩;select stu.sid as 学号,stu.sname as 姓名,count(sco.course_id) as 选课数,avg(sco.score) as 平均成绩from student as stu left join score as sco on stu.sid = sco.student_idgroup by sco.student_id;
    8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数;select t1.sname as 姓名,t2.cname as 课程名,t1.score as 分数from (select stu.sid, stu.sname, sco.course_id, sco.score from student as stu inner join score as sco on stu.sid = sco.student_id where stu.sid=2) as t1inner joincourse as t2 on t1.course_id = t2.cidgroup by t2.cidhaving score in (max(score),min(score));
    9、查询姓“李”的老师的个数和所带班级数;select count(te.tid) as 姓李老师个数,count(tc.cid) as 所带班级数from teacher as te inner join teach2cls as tcon te.tid = tc.tidwhere te.tname regexp "^李.*"group by te.tid;
    10、查询班级数小于5的年级id和年级名;select c.grade_id as 年级id,g.gname as 年级名from class as c inner join class_grade as gon c.grade_id = g.gidgroup by c.grade_idhaving count(c.cid)<5;
    11、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级),示例结果如下;select cid as 班级id,caption as 班级名称,gname as 年级,casewhen g.gid in (1,2) then '低年级'when g.gid in (3,4) then '中年级'when g.gid in (5,6) then '高年级'else '其他' end as 年级级别from class as c inner join class_grade as gon c.grade_id = g.gid;
    12、查询学过“张三”老师2门课以上的同学的学号、姓名;select stu.sid as 学号,stu.sname as 姓名from student as stu inner join score as sco on stu.sid = sco.student_idwhere sco.course_id in (select c.cidfrom teacher as t inner join course as con t.tid = c.teacher_idwhere t.tname = '张三')group by stu.sidhaving count(sco.course_id) >= 2;
    13、查询教授课程超过2门的老师的id和姓名;selecttid as id,tname as 姓名from teacher as t inner join course as c on t.tid = c.teacher_idgroup by c.teacher_idhaving count(c.cid) >= 2;
    14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_id from scorewhere student_id in (select student_id from scorewhere course_id = 1)and course_id = 2);
    15、查询没有带过高年级的老师id和姓名;select tid as 老师id,tname as 姓名from teacherwhere tid not in (select tc.tidfrom class as c inner join teach2cls as tc on c.cid = tc.cidwhere c.grade_id in (5,6));
    16、查询学过“张三”老师所教的所有课的同学的学号、姓名;select distinctstu.sid as 学号,stu.sname as 姓名from student as stu inner join score as sco on stu.sid = sco.student_idwhere sco.course_id in (select c.cid from teacher as t inner join course as c on t.tid = c.teacher_idwhere t.tname = "张三");
    17、查询带过超过2个班级的老师的id和姓名;select tid as id,tname as 姓名from teacherwhere tid in (select tid from teach2clsgroup by tidhaving count(cid) >= 2);
    18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;select sid as 学号,sname as 姓名from studentwhere sid in(select t1.student_idfrom (select * from scorewhere course_id = 1) as t1inner join (select * from score where course_id = 2) as t2on t1.student_id = t2.student_idwhere t1.score > t2.score);
    19、查询所带班级数最多的老师id和姓名;select tid as id,tname as 姓名from teacher where tid in (select tidfrom teach2clsgroup by tidhaving count(cid) = (select count(cid)from teach2clsgroup by tidorder by count(cid) desclimit 1));
    20、查询有课程成绩小于60分的同学的学号、姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_idfrom score where score < 60);
    21、查询没有学全所有课的同学的学号、姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_idfrom scoregroup by student_idhaving count(course_id) 葡京网投哪个正规,!= (select count(cid) from course));
    22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_idfrom scorewhere course_id in (select course_id from scorewhere student_id = 1));
    23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_idfrom scorewhere course_id in (select course_id from scorewhere student_id = 1) and student_id != 1);
    24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_idfrom scorewhere student_id != 2group by student_idhaving group_concat(course_id order by course_id asc) = (select group_concat(course_id order by course_id asc)from scorewhere student_id = 2group by student_id));
    25、删除学习“张三”老师课的score表记录;delete from scorewhere course_id in (select c.cid from teacher as t inner join course as con t.tid = c.teacher_idwhere t.tname = '张三');
    26、向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“2”课程的同学学号;②插入“2”号课程的平均成绩;【插入第一条】insert into score(student_id, course_id, score) values((select sidfrom studentwhere sid not in(select s.student_idfrom score as swhere s.course_id = 2)order by sid desclimit 0,1),2,(select avg(s.score)from score as swhere s.course_id = 2));【插入第二条】insert into score(student_id, course_id, score) values((select sidfrom studentwhere sid not in(select s.student_idfrom score as swhere s.course_id = 2)order by sid desclimit 1,1),2,(select avg(s.score)from score as swhere s.course_id = 2));
    【改limit后的第一个参数值,可继续插入第三、四、...条】
    27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;【这里题目有歧义:有效课程数和有效平均分是仅以这3门课来统计,还是以学生所有科目来统计】【解一:仅以这3门课来统计】select t2.sid as 学生ID, sum(case when t1.cname = '语文' then t1.score else null end) as 语文,sum(case when t1.cname = '数学' then t1.score else null end) as 数学,sum(case when t1.cname = '英语' then t1.score else null end) as 英语,count(case when t1.cname in ('语文','数学','英语') then 1 else null end) as 有效课程数,avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) as 有效平均分from (select *葡京正网网投, from score as s inner join course as con s.course_id = c.cid) as t1right join student as t2on t1.student_id = t2.sidgroup by t2.sidorder by avg(case when t1.cname in ('语文','数学','英语') then t1.score else null end) asc;
    【解二:以该学生所有科目来统计】select t2.sid as 学生ID, sum(case when t1.cname = '语文' then t1.score else null end) as 语文,sum(case when t1.cname = '数学' then t1.score else null end) as 数学,sum(case when t1.cname = '英语' then t1.score else null end) as 英语,count(t1.score) as 有效课程数,avg(t1.score) as 有效平均分from (select * from score as s inner join course as con s.course_id = c.cid) as t1right join student as t2on t1.student_id = t2.sidgroup by t2.sidorder by avg(t1.score) asc;
    28、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;select course_id as 课程ID,max(score) as 最高分,min(score) as 最低分from scoregroup by course_id;
    29、按各科平均成绩从低到高和及格率的百分数从高到低顺序;【这里优先按平均成绩从低到高排序,若遇到平均成绩相同的则按及格率百分数从高到低排序】select course_id as 课程ID,avg(score) as 平均成绩,concat(100*count(case when score>=60 then 1 else null end)/count(score),"%") as 及格率from scoregroup by course_idorder by avg(score) asc, count(case when score>=60 then 1 else null end)/count(score) desc;
    30、课程平均分从高到低显示(显示任课老师);select t1.cname as 课程名称,avg(t2.score) as 平均分,t1.tname as 任课老师from (select * from teacher as t inner join course as con t.tid = c.teacher_id) as t1 inner join score as t2on t1.cid = t2.course_idgroup by t2.course_idorder by avg(t2.score) desc;
    31、查询各科成绩前三名的记录(不考虑成绩并列情况)【本题与44题类似,不会做,于是百度了下"如何在mysql中查询每个分组的前几名",参照其中的一个方法,写出了答案】【注:这里仍然是按照score表默认的排序,即sid的排序】select*from scorewhere(selectcount(*)from score as swheres.course_id = score.course_idands.score <= score.score)<= 3;
    32、查询每门课程被选修的学生数;select cname as 课程名,count(s.student_id) as 选修学生数from course as c left join score as s on c.cid = s.course_idgroup by c.cid;
    33、查询选修了2门以上课程的全部学生的学号和姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_id from scoregroup by student_idhaving count(course_id) >= 2);
    34、查询男生、女生的人数,按倒序排列;select gender, count(sid)from studentgroup by genderorder by count(sid) desc;
    35、查询姓“张”的学生名单;【查法1——正则】select sname from studentwhere sname regexp "^张.*";
    【查法2——like】select snamefrom studentwhere sname like "张%";
    36、查询同名同姓学生名单,并统计同名人数;select sname as 姓名,count(sid) as 同名人数 from studentgroup by snamehaving count(sid) > 1;
    37、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;select avg(score),course_idfrom scoregroup by course_idorder by avg(score) asc, course_id desc;
    38、查询课程名称为“数学”,且分数低于60的学生姓名和分数;select stu.sname as 学生姓名,sco.score as 分数from student as stu inner join score as scoon stu.sid = sco.student_idwhere sco.course_id = (select cid from course where cname = '数学')and sco.score < 60;
    39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名;select sid as 学号,sname as 姓名from studentwhere sid in (select student_id from scorewhere course_id = 3 and score >= 80);
    40、求选修了课程的学生人数select count(1) as 学生人数from(select distinct student_idfrom score) as t1;
    41、查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;select stu.sname as 学生姓名,sco.score as 成绩from student as stu inner join score as scoon stu.sid = sco.student_idwhere score in ((select max(score)from scorewhere course_id in (select c.cidfrom teacher as t inner join course as con t.tid = c.teacher_id where t.tname = '王五')), (select min(score)from scorewhere course_id in (select c.cidfrom teacher as t inner join course as con t.tid = c.teacher_id where t.tname = '王五')));
    42、查询各个课程及相应的选修人数;select cname as 课程名,count(s.student_id) as 选修学生数from course as c left join score as s on c.cid = s.course_idgroup by c.cid;
    43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;select student_id as 学号,course_id as 课程号,score as 学生成绩from scoregroup by scorehaving count(student_id) > 1;
    44、查询每门课程成绩最好的前两名学生id和姓名;【注:这里指定了前两名,所以若出现多名同分的学生也只取倒序排的默认前2名】【与31题类似…不会写,于是百度了下"如何在mysql中查询每个分组的前几名",参照其中一种比较高端且高效的自定义变量的方法,写出了答案】
    set @num := 0, @cname := '';selectt2.cid as 课程ID,t2.cname as 课程名,t1.sid as 学生ID,t1.sname as 学生名,t1.score as 成绩,@num := if(@cname = t2.cname, @num + 1, 1) as 排名,@cname := t2.cname as 课程名确认from (select stu.sid, stu.sname, sco.course_id, sco.score from student as stu inner join score as scoon stu.sid = sco.student_id) as t1right joincourse as t2on t1.course_id = t2.cidgroup byt2.cid, t1.score, t1.snamehaving排名 <= 2;

Answer:

或者: select * from score where s_name in

45、检索至少选修两门课程的学生学号;select sid as 学号from studentwhere sid in (select student_id from scoregroup by student_idhaving count(course_id) >= 2);
46、查询没有学生选修的课程的课程号和课程名;select cid as 课程号,cname as 课程名from coursewhere cid not in (select distinct course_idfrom score);
47、查询没带过任何班级的老师id和姓名;selecttid as 老师id,tname as 姓名from teacherwhere tid not in (select distinct tidfrom teach2cls);
48、查询有两门以上课程超过80分的学生id及其平均成绩;select student_id as 学生id,avg(score) as 平均成绩from scorewhere student_id in (select student_idfrom scorewhere score >= 80group by student_idhaving count(course_id) >= 2)group by student_id;
49、检索“3”课程分数小于60,按分数降序排列的同学学号;select distinctstudent_id as 学号from scorewhere course_id = 3 and score < 60order by score desc;
50、删除编号为“2”的同学的“1”课程的成绩;delete from scorewhere student_id = 2 and course_id = 1;
51、查询同时选修了物理课和生物课的学生id和姓名;select sid as 学生id,sname as 姓名from studentwhere sid in (select student_idfrom scorewhere course_id = (select cid from course where cname = '生物'))and sid in (select student_idfrom scorewhere course_id = (select cid from course where cname = '物理'));

①select distinct name from table where name not in (select  distinct name from table where fenshu<=80)

(select s_name from score

②select   name from table group by name having  min(fenshu)>80

group by s_name

2. 学生表 如下:

having min(score)=60)

自动编号    学号          姓名    课程编号      课程名称   分数

表名:商品表 名称 产地 进价 苹果 烟台 2.5 苹果 云南 1.9 苹果 四川 3 西瓜 江西 1.5 西瓜 北京 2.4 ……

1               2005001    张三       0001              数学         69

给出平均进价在2元以下的商品名称

2               2005002    李四       0001              数学         89

select 名称 from 商品表 group by 名称 having avg(进价) 2

3               2005001    张三       0001              数学         69

表名:高考信息表 准考证号 科目 成绩 2006001 语文 119 2006001 数学 108 2006002 物理 142 2006001 化学 136 2006001 物理 127 2006002 数学 149 2006002 英语 110 2006002 语文 105 2006001 英语 98 2006002 化学 129 ……

删除除了自动编号不同, 其他都相同的学生冗余信息

给出高考总分在600以上的学生准考证号

Answer:

select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) 600

①delete from table where 自动编号 not in (select nin(自动编号) from table group by  学号, 姓名 ,课程编号,课程名称,分数)

表名:高考信息表 准考证号 数学 语文 英语 物理 化学 2006001 108 119 98 127 136 2006002 149 105 110 142 129 ……

3. 面试题:怎么把这样一个表儿(aaa)

给出高考总分在600以上的学生准考证号

year       month     amount

select 准考证号 from 高考信息表 where (数学+语文+英语+物理+化学) 600

1991          1            1.1

(四部分) 表名:club

1991          2            1.2

id gender age 67 M 19 68 F 30 69 F 27 70 F 16 71 M 32 ……

1991          3            1.3

查询出该俱乐部里男性会员和女性会员的总数

1991          4            1.4

select gender,count(id) from club group by gender

1992          1            2.1

表名:team ID(number型) Name(varchar2型) 1 a 2 b 3 b 4 a 5 c 6 c 要求:执行一个删除语句,当Name列上有相同时,只保留ID这列上值小的 例如:删除后的结果应如下: ID(number型) Name(varchar2型) 1 a 2 b 5 c 请写出SQL语句。

1992          2            2.2

delete from team where id not in

1992          3            2.3

(

1992          4            2.4

select min(a1.id) from team a1

查成这样一个结果

where a1.name=team.name )

year m1 m2 m3 m4

delete from team where id not in

1991 1.1 1.2 1.3 1.4

(

1992 2.1 2.2 2.3 2.4

select min(id) from team group by name)

Answer:

表名:student

①:select year ,

name course score 张青 语文 72 王华 数学 72 张华 英语 81 张青 物理 67 李立 化学 98 张燕 物理 70 张青 化学 76

(select amount  from aaa m where month=1 amd m.year=aaa.year) as m1,

查询出“张”姓学生中平均成绩大于75分的学生信息

(select amount  from aaa m where month=2 amd m.year=aaa.year) as m2,

select * from student where name in

(select amount  from aaa m where month=3 amd m.year=aaa.year) as m3,

(select name from student where name like 张%

(select amount  from aaa m where month=4 amd m.year=aaa.year) as m4

group by name having avg(score) 75)

from aaa 

group by year

4. 说明:拷贝表( 拷贝数据, 源表名:a 目标表名:b)

Answer:

①:insert into b(a,b,c) select d,e,f from a

***5.有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路): 


大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。 

显示格式: 

语文 数学 英语 

及格 优秀 不及格 

Answer:

①:select 

(case   when  语文>=80 then '优秀' when  语文>=60 then '及格' else '不及格') AS 语文,

(case   when  数学>=80 then '优秀' when  数学>=60 then '及格' else '不及格') AS 数学,

(case   when  英语>=80 then '优秀' when  英语>=60 then '及格' else '不及格') AS 英语

from table

6、编写SQL语句

1) 创建一张学生表,包含以下信息,学号,姓名,年龄,性别,家庭住址,联系电话

2) 修改学生表的结构,添加一列信息,学历

3) 修改学生表的结构,删除一列信息,家庭住址

4) 向学生表添加如下信息:

学号 姓名年龄性别联系电话学历

1 A 22 男 123456 小学

2 B 21 男 119 中学

5) 修改学生表的数据,将电话号码以11开头的学员的学历改为“大专”

6) 删除学生表的数据,姓名以C开头,性别为‘男’的记录删除

7) 查询学生表的数据,将所有年龄小于22岁的,学历为“大专”的,学生的姓名和学号示出来

8) 查询学生表的数据,查询所有信息,列出前25%的记录

9) 查询出所有学生的姓名,性别,年龄降序排列

10) 按照性别分组查询所有的平均年龄

Answer:

1)create table stu (学号 int,姓名 varchar(10),年龄 int ,性别 varchar(4) ,家庭住址 varchar(50),联系电话 int)

2)alter table stu add 学历 varchar(5)

3)alter table stu drop column 家庭住址

4)insert into stu values(1 ,'A' ,22, '男' ,123456, '小学'),(2 ,'B' ,21 ,'男' ,119 ,'中学')

5)update stu set 学历='大专' where 联系电话 like '11%'

6)delect from stu where 姓名 like 'C%' and  性别=‘男’

7)select 姓名 ,学号 from stu where 学历='大专'  and 年龄<22

8)select top 25 percent * from stu 

9)select 姓名,性别 from stu order by  年龄 desc

10)select avg(年龄)   from stu group by 性别

7、查询A(ID,Name)表中第31至40条记录,ID作为主键可能是不是连续增长的列,完整的查询语句如下:

Answer:

①:select * from A limit 30,10

②:select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A ) T) order by A---怀疑

***8、查询表A中存在ID重复三次以上的记录,完整的查询语句如下


Answer:

①:select * from A where ID in (select ID from A group by ID having count(ID)>3)

②:select b.id,b.name,(select ID,Name,ROW_NUMBER() over(partition by ID) '排名'from A) b where b.排名>3---仅SQL SEVER

**9、说出以下聚合数的含义:avg ,sum ,max ,min , count ,count()


Answer:

AVG:求平均值

SUM:求和

MAX:求最大值

MIN:求最小值

10、说明:随机取出10条数据

Answer:

select top 10 *  from tablename order by newid()---SQL SEVER

***11、查询平均成绩大于60分的同学的学号和平均成绩;


*Answer:
*

select  id,avg(score) from stu group by id having avg(score) >60

12、解释名词

事务 Transaction 触发器 TRIGGER 继续 continue 唯一 unqiue

主键 primary key 标识列 identity 外键 foreign key 检查 check

约束 constraint

补充:

1. 不使用 min,找出表 ppp 中 num(列)最小的数

select num from ppp where num <= all(select num from ppp);

不可以使用 min 函数,但可以实用 order by 和 limit 相组合呀;

select * from ppp order by num desc limit 1;

自然,不使用 max,找出表 ppp 中 num 最大的数:

select num from ppp where num >=all(select num from ppp);

select num from ppp order by num limit1;

2. 选择表 ppp 中的重复记录

select * from ppp

    where num in (select num from ppp group by num having count(num) > 1);

只返回单独的一条记录

select * from ppp group by num having count(num) = 1;

查询表中出现 四 次的记录,group by having

select * from ppp

    where num in (select num from ppp group by num having count(num) = 4);

3. 影分身,一表当做两表用

表形式如下: 

Year Salary 

2000 1000 

2001 2000 

2002 3000 

2003 4000

想得到如下形式的查询结果 

Year Salary 

2000 1000 

2001 3000 

2002 6000 

2003 10000

sql语句怎么写?

select b.year, sum(a.salary) from hell0 a, hello b where a.year <= b.year group by b.year;

本文由葡京网投哪个正规发布于新葡亰-编程,转载请注明出处:Server面试题整合,多表查询

关键词:

上一篇:没有了

下一篇:批量更新数据库所有表中字段的内容,Server数据库内容替换方法