MySQL经典9道面试题

MySQL经典9道面试题

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

结果如下：

1.1创建表和插入数据

 CREATE table if not EXISTS scores(
	id INT PRIMARY KEY,
	stu_name VARCHAR(20) comment '学生姓名',
	subject_name VARCHAR(20) COMMENT '课程名称',
	 score float COMMENT '分数'
 );

 insert into scores(id,stu_name,subject_name,score)
VALUES (1,'张三','语文',81);
 insert into scores(id,stu_name,subject_name,score)
 VALUES (2,'张三','数学',75) ;
insert into scores(id,stu_name,subject_name,score)
VALUES (3,'李四','语文',76);
 insert into scores(id,stu_name,subject_name,score)
VALUES (4,'李四','数学',90) ;
insert into scores(id,stu_name,subject_name,score)
VALUES (5,'王五','语文',81);
insert into scores(id,stu_name,subject_name,score)
VALUES (6,'王五','数学',100) ;
 insert into scores(id,stu_name,subject_name,score)
VALUES (7,'王五','英语',90) ;

-- 查看表数据
mysql>  select * from scores;
+----+----------+--------------+-------+
| id | stu_name | subject_name | score |
+----+----------+--------------+-------+
|  1 | 张三     | 语文         |    81 |
|  2 | 张三     | 数学         |    75 |
|  3 | 李四     | 语文         |    76 |
|  4 | 李四     | 数学         |    90 |
|  5 | 王五     | 语文         |    81 |
|  6 | 王五     | 数学         |   100 |
|  7 | 王五     | 英语         |    90 |
+----+----------+--------------+-------+
7 rows in set (0.00 sec)

1.2 sql解析

select a.stu_name 
from scores as a
#按照学生分组
group by a.stu_name 
#对每一个学生的成绩进行筛选，求学生的最低成绩>80
having min(a.score)>80  
显示结果：
+‐‐‐‐‐‐+
| 姓名 |
+‐‐‐‐‐‐+
| 王五 |
 +‐‐‐‐‐‐+

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

删除后的查询表的结果如下：

2.1创建表和插入数据

 CREATE table if not EXISTS scores2(
 id INT auto_increment PRIMARY KEY,
 stu_no VARCHAR(20) COMMENT '学号',
 stu_name VARCHAR(20) comment '学生姓名',
 sub_no VARCHAR(20) COMMENT '课程编号',
 sub_name VARCHAR(20) COMMENT '课程名称',
 score float COMMENT '分数'
 );

  ‐‐ 插入测试数据
 insert into
 scores2(stu_no,stu_name,sub_no,sub_name,score)
 values('2005001','张三','001','数学',69);
 insert into
 scores2(stu_no,stu_name,sub_no,sub_name,score)
 values('2005002','李四','001','数学',89);
 insert into
 scores2(stu_no,stu_name,sub_no,sub_name,score)
 values('2005001','张三','001','数学',69);

#查看表数据
select * from scores2;
+----+---------+----------+--------+----------+-------+
| id | stu_no  | stu_name | sub_no | sub_name | score |
+----+---------+----------+--------+----------+-------+
|  1 | 2005001 | 张三     | 001    | 数学     |    69 |
|  2 | 2005002 | 李四     | 001    | 数学     |    89 |
|  3 | 2005001 | 张三     | 001    | 数学     |    69 |
+----+---------+----------+--------+----------+-------+
3 rows in set (0.00 sec)

2.2 sql解析

#第一步:对除id以外，所有的字段进行分组，目的是得到所有记录相同的信息记录
#然后再对分组的数据进行min或者max 每一条重复的信息只取id最大或者id最小的#一条，从而过滤掉重复的信息
	select min(id)
	from scores2 as s
	group by s.stu_no,s.stu_name,s.stu_no,s.sub_name,s.score
	
# 第二步:开始进行删除操作，where筛选掉重复的数据
 delete from scores2 where id not in(...)
 
#最终sql，
/**
多用一层t临时表的目的是：在mysql中不能在不能在FROM子句中为更新指定
目标表'scores2'
**/
delete from scores2 where id not in (
  select t.id 
	from(
	select min(id) as  id 
	from scores2 
	group by stu_no,stu_name,sub_no,sub_name,score
	) as t
 );

3、一个叫team的表，里面只有一个字段name，一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合，创建表，插入测试数据。

结果如下：

3.1创建表和插入数据

/**
**
 ‐#创建表
create table if not EXISTS team(
	id int auto_increment PRIMARY key,
	name VARCHAR(20) COMMENT '球队名称'
);
# 插入测试数据
insert into team(name) value('a');
insert into team(name) value('b');
insert into team(name) value('c');
insert into team(name) value('d');

mysql> select * from team;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

3.2sql解析

因为只有4个球队，球队的组合如上图所示，组合 1 1 ，2 2，3 3，4 4 （不存在的原因是球队不能和自己比赛）

select  a.name,b.name
from team as a
join team as b
on a.id<b.id
+------+------+
| name | name |
+------+------+
| a    | b    |
| a    | c    |
| a    | d    |
| b    | c    |
| b    | d    |
| c    | d    |
+------+------+
6 rows in set (0.00 sec)

4、有如下的表结构，请创建表并插入测试数据。

用一条sql写出如下的结果

4.1创建表和插入数据

/**
** 创建表
create TABLE account(
	years varchar(20),
	months varchar(20),
	amount varchar(20)
);
-- 插入测试数据
insert into account (years,months,amount) values('1991','1','1.1');
insert into account (years,months,amount) values('1991','2','1.2');
insert into account (years,months,amount) values('1991','3','1.3');
insert into account (years,months,amount) values('1991','4','1.4');

insert into account (years,months,amount) values('1992','1','2.1');
insert into account (years,months,amount) values('1992','2','2.2');
insert into account (years,months,amount) values('1992','3','2.3');
insert into account (years,months,amount) values('1992','4','2.4');

-- 查询数据
mysql> SELECT * from account;
+-------+--------+--------+
| years | months | amount |
+-------+--------+--------+
| 1991  | 1      | 1.1    |
| 1991  | 2      | 1.2    |
| 1991  | 3      | 1.3    |
| 1991  | 4      | 1.4    |
| 1992  | 1      | 2.1    |
| 1992  | 2      | 2.2    |
| 1992  | 3      | 2.3    |
| 1992  | 4      | 2.4    |
+-------+--------+--------+
8 rows in set (0.00 sec)

4.2sql解析

#最终sql

select a.years as years,
       sum(case when  a.months=1 then a.amount end)as m1,
	   sum(case when  a.months=2 then a.amount end)as m2,
	   sum(case when  a.months=3 then a.amount end)as m3,
       sum(case when  a.months=4 then a.amount end)as m4
from account as a
group by a.years;
+-------+------+------+------+------+
| years | m1   | m2   | m3   | m4   |
+-------+------+------+------+------+
| 1991  |  1.1 |  1.2 |  1.3 |  1.4 |
| 1992  |  2.1 |  2.2 |  2.3 |  2.4 |
+-------+------+------+------+------+
2 rows in set (0.00 sec)

5、复制表结构，只是复制表结构，不拷贝表中的数据（源表名：a，新表名：b），写出sql语句。

A表的结构如下：

使用一条sql复制a表结构创建新表b如下但是不复制数据：

5.1创建表和插入数据

CREATE table if not EXISTS a(
		id int
 );
 
 insert into a(id) values(1);
 insert into a(id) values(2);
 insert into a(id) values(3);
 --查看表数据
 mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

4.2sql解析

-- 复制表不需要数据
CREATE table a_copy_b as SELECT * from a where 1=2

结果：
mysql> select * from a_copy_b;
Empty set (0.00 sec)

-- 复制表并且复制数据
CREATE table a_copy_c as SELECT * from a where 1=1;

结果：
mysql> select * from a_copy_c;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

-- 将a_copy_c 数据插入到 a_copy_b
INSERT into a_copy_b  SELECT * from a_copy_c;

6、有三张表，学生表S，课程表C，学生课程表SC，学生可以选修多门课程，一门课程可以被多个学生选修，通过SC表关联。

6.1写出建表语句；

-- 学生表
create table s(id integer primary key, name varchar(20));
-- 课程表
create table c(id integer primary key, name varchar(20));
-- 学生的选课表
create table sc(
   sid integer,
   cid integer,
   primary key(sid,cid), -- 联合主键
   foreign key (sid) references s(id), -- 外键
   foreign key (cid) references c(id) -- 外键
);
INSERT into s(id,name) values (1,'zhangsan');
INSERT into s(id,name) values (2,'lisi');
INSERT into s(id,name) values (3,'wangwu');


INSERT into c(id,name) values (10,'git');
INSERT into c(id,name) values (20,'oracle');
INSERT into c(id,name) values (30,'mysql');
INSERT into c(id,name) values (50,'linux');
INSERT into c(id,name) values (40,'hive');

SELECT * from s;
SELECT * from c;
SELECT * from sc;

6.2写出SQL语句，查询选修了所有选修课程的学生；

select s.id,count(*)
from s
join sc
on s.id=sc.sid
join c
on c.id=sc.cid
group by s.id
having count(c.id)=(select count(distinct id) from c)

6.3写出SQL语句，查询选修了至少5门以上的课程的学生。

#解析3：
select s.id,count(*)
from s
join sc
on s.id=sc.sid
join c
on c.id=sc.cid
group by s.id
having count(c.id)>=5

7、创建如下的表结构，并且插入测试数据，写出sql

表一：

表二：depId为表一的id

用一条sql查询出如下的结果

7.1创建表和插入数据；

create table testtable1
(
   id int  PRIMARY KEY,
   department varchar(12)
);
-- 插入测试数据
select * from testtable1;
insert into testtable1(id,department) values(1,'设计');
insert into testtable1(id,department) values(2,'市场');
insert into testtable1(id,department) values(3,'售后');
-- 创建表二
create table testtable2
(
   id int  PRIMARY KEY,
   dptID int,
   name varchar(12)
);
-- 插入测试数据
insert into testtable2(id,dptId,name) values(1,1,'张三');
insert into testtable2(id,dptId,name) values(2,1,'李四');
insert into testtable2(id,dptId,name) values(3,2,'王五');
insert into testtable2(id,dptId,name) values(4,3,'彭六');
insert into testtable2(id,dptId,name) values(5,4,'陈七');
select * from testtable1;
select * from testtable2;

7.2sql解析；

select b.id,b.dptid,if(a.department is not null,a.department,'黑人'),b.name
from testtable1 as a
right join testtable2 as b
on a.id=b.dptid 

8、有如下表结构，写出创建表的sql并且插入测试数据，其中：p_id为产品ID，p_num为产品库存量，s_id为仓库ID。

请用一条sql插叙出如下的结果
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，
s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，
那么就是0代替。
结果如下：

8.1创建表和插入数据；

create table if not EXISTS a1(
   p_id int COMMENT '产品id',
   p_num  int COMMENT '产品数量',
   s_id varchar(20)  COMMENT '仓库编号'
);

-- 插入测试数据
insert into a1(p_id,p_num,s_id)
values(1,10,'01');
insert into a1(p_id,p_num,s_id)
values(1,12,'02');
insert into a1(p_id,p_num,s_id)
values(2,8,'01');
insert into a1(p_id,p_num,s_id)
values(3,11,'01');
insert into a1(p_id,p_num,s_id)
values(3,8,'03');
select * from a1;

8.2sql解析:

select p_id,
ifnull(sum(case when s_id='01' then p_num end),0) as s1_id,
ifnull(sum(case when s_id='02' then p_num end),0) as s2_id,
ifnull(sum(case when s_id='03' then p_num end),0) as s3_id
from a1
group by p_id

+------+-------+-------+-------+
| p_id | s1_id | s2_id | s3_id |
+------+-------+-------+-------+
|    1 |    10 |    12 |     0 |
|    2 |     8 |     0 |     0 |
|    3 |    11 |     0 |     8 |
+------+-------+-------+-------+
3 rows in set (0.00 sec)

9、数据库表如下，创建表，插入测试数据列出所有年龄比所属主管年龄大的人的ID和名字，manger是该人所属部门领导的id

结果如下：

9.1创建表和插入数据；

create table  person1(
   id int PRIMARY key,
   name VARCHAR(20),
   age INT,
   manager INT

);
-- 插入测试数据
insert into person1(id,name,age,manager)
values(106,'a',30,104);

insert into person1(id,name,age,manager)
values(109,'b',19,104);

insert into person1(id,name,age,manager)
values(104,'c',20,111);

insert into person1(id,name,age,manager)
values(107,'d',35,109);

insert into person1(id,name,age,manager)
values(112,'e',25,120);

insert into person1(id,name,age)
values(119,'f',45);
SELECT * from person1;

9.2sql解析:

select b.id,b.name,b.age,a.id,a.name,a.age
#--领导表 a
from person1 as a 
join person1 as b
on b.manager=a.id
where b.age>a.age