面试SQL语句，学会这些就够了！！！

SQL语句从简单到困难，这篇文章足以应付面试了！多张表都是一步一步来的，很容易看懂，最后一行才是最终结果（有加粗印记）。

一、单张表

        表名：afinfo

1.请编写sql语句对年龄进行升序排列

select * from afinfo order by age

2.请编写sql语句查询对“徐”姓开头的人员名单

select * from afinfo where name like "徐%"

3.请编写sql语句修改“陈晓”的年龄为“45”

update afinfo set age = 45 where name ="陈晓"

4.请编写sql删除王芳芳这表数据记录。

delete form afinfo where id = 2

二、多表

        现有以下几张表

        

学生信息表（student）

考试信息表(exam)

1.李四语文成绩被登记错误，成绩实际为85分，更新到考试信息表中，SQL语句怎么编写？

select code from student where name="李四"

update exam set score = 85 where code = (select code from student where name="李四") and subject = "语文"

2.查询出各科成绩的平均成绩，显示字段为：学科、平均分，SQL怎么编写？

//聚合函数

select subject,count(subject),sum(score) from exam group by subject;

select subject,sum(score)/count(subject) from exam group by subject;

select subject,avg(score) (as 可以省略) avgScore from exam group by subject

3.查询出所有学生各科成绩，显示字段为：姓名、学号、学科、成绩，并以学号与学科排序，没有成绩的学生也需要列出，SQL怎么编写？

select * from student,exam;//全连接

select student.*,subject,score from student,exam(student inner join exam 一样) where student.code = exam.code order by code,subject;//只能实现相交数据，不能查出没有成绩的

select student.*,subject,score from student left join exam on student.code = exam.code order by code,subject

4.查询出单科成绩最高的，显示字段为：姓名、学号、学科、成绩，SQL怎么编写？

select subject,max(score) from exam group by subject;

select student.*,subject,score from student left join exam on student.code = exam.code where (subject,score) in (select subject,max(score) from exam group by subject)

三、根据要求写出SQL语句。

表名和字段

–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表Score
(s_id,c_id,s_score) --学生编号,课程编号,分数

 测试数据

-- 建表
-- 学生表
CREATE TABLE `Student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`c_id`)
);
 
-- 教师表
CREATE TABLE `Teacher`(
	`t_id` VARCHAR(20),
	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(`t_id`)
);
 
-- 成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);
 
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
 
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
 
 
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
 
 
 
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

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

select s_score,s_id from score where c_id = 01;

select s_score,s_id from score where c_id = 02;

select * from (select s_score,s_id from score where c_id = 01) t1,(select s_score,s_id from score where c_id = 02) t2 where t1.s_score>t2.s_score and t1.s_id=t2.s_id //自连接

select * from student where s_id in (select t1.s_id from(上面的3)) //没有查出成绩

select student*,s_score_01,s_score_02 from student,(select t1.s_id,t1.s_score s_score_01,t2.score s_score_02 from(上面的3)) t3 where t3.s_id = student.s_id

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

//where 这个条件是在group by 分组之前用的，而用group by分组之后条件需要用having select * from student,(select s_id,avg(s_score) avg_score from score group by s_id having avg_score >= 60) t1 where student.s_id = t1.s_id

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

-- (包括有成绩的和无成绩的)

select * from student left join (select s_id,avg(s_score) avg_score from score group by s_id ) t1 on student.s_id = t1.s_id where avg_score < 60 or avg_score is null

4.查询学过"张三"老师授课的同学的信息

select t_id from teacher where t_name="张三"

select c_id from course where t_id = (select t_id from teacher where t_name="张三" ) //看这个老师教什么课

select * from student where s_id in(select s_id from score where c_id =(select c_id from course where t_id =(select t_id from teacher where t_name="张三")))

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

select student.s_id,student.s_name,count(score.s_id) 选课总数,sum(s_score) 总成绩 from student left join score on student.s_id = score.s_id group by score.s_id

6.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

//也是需要自连接

select student.* from student,(select s_id from score where c_id=01) t1 ,(select s_id from score where c_id=02) t2 where student.s_id = t1.s_id and t1.s_id = t2.s_id

7.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select student.* from student left join (select * from score where c_id = 01) t1 on student.s_id = t1.s_id left join (select * from score where c_id = 02) t2 on t1.s_id = t2.s_id where t1.s_score is not null and t2.s_score is null