11、数据库SQL查询（附建表语句）

#### 第 1 题

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

**建表语句：**

```plain
create table tableA
(
name varchar(10),
kecheng varchar(10),
fenshu int(11)
)DEFAULT CHARSET = 'utf8';
```

**插入数据**

```plain
insert into tableA values ('张三','语文',81);
insert into tableA values ('张三','数学',75);
insert into tableA values ('李四','语文',76);
insert into tableA values ('李四','数学',90);
insert into tableA values ('王五','语文',81);
insert into tableA values ('王五','数学',100);
insert into tableA values ('王五','英语',90);
```

**答案有 3 种：**

```plain
答案A：
select distinct name from tableA where name not in (select distinct name from tableA where fenshu<=80)

答案B：
select name from tableA group by name having min(fenshu)>80

答案C：
select name from tableA group by name having count(kecheng)>=3 and min(fenshu)>=80
```

#### 第 2 题

**一道 SQL 语句面试题，关于 group by 表内容：**

**2005-05-09 胜**

2005-05-09 胜

2005-05-09 负

2005-05-10 胜

2005-05-10 负

**如果要生成下列结果, 该如何写 sql 语句?**
![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620805224701-image.png)

**建表语句：**

```plain
create table tableb (
rq varchar(10),
shengfu nchar(1)
)DEFAULT CHARSET = 'utf8';
```

**插入数据：**

```plain
insert into tableb values('2005-05-09','胜');
insert into tableb values('2005-05-09','胜');
insert into tableb values('2005-05-09','负');
insert into tableb values('2005-05-09','负');
insert into tableb values('2005-05-10','胜');
insert into tableb values('2005-05-10','负');
insert into tableb values('2005-05-10','负')
```

**答案：**

```plain
select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from tableb group by rq
```

#### 第 3 题

**一个叫 team 的表，里面只有一个字段 name, 一共有 4 条纪录，分别是 a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条 sql 语句显示所有可能的比赛组合**
team 表建表语句:

```plain
CREATE TABLE team (
  `name` varchar(20)
) DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
INSERT INTO team VALUES ('a');  
INSERT INTO team VALUES ('b');  
INSERT INTO team VALUES ('c');  
INSERT INTO team VALUES ('d')
```

**答案：**

```plain
select a.name, b.name
from team a, team b 
where a.name < b.name
```

**结果如图所示：**

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620828928877-image.png)

#### 第 4 题

**怎么把这样一个表儿**
![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620829449827-image.png)

**查成这样一个结果**

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620829470934-image.png)

**建表语句：**

```plain
CREATE TABLE table1 (
`year` int (11),
`month` int (11),
`amount` DECIMAL
) DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
INSERT INTO table1 VALUES (1991,1,1.1);  
INSERT INTO table1 VALUES (1991,2,1.2); 
INSERT INTO table1 VALUES (1991,3,1.3); 
INSERT INTO table1 VALUES (1991,4,1.4); 
INSERT INTO table1 VALUES (1992,1,2.1);  
INSERT INTO table1 VALUES (1992,2,2.2); 
INSERT INTO table1 VALUES (1992,3,2.3); 
INSERT INTO table1 VALUES (1992,4,2.4);
```

**答案：**

```plain
select year, 
(select amount from   aaa m where month=1   and m.year=aaa.year) as m1,
(select amount from   aaa m where month=2   and m.year=aaa.year) as m2,
(select amount from   aaa m where month=3   and m.year=aaa.year) as m3,
(select amount from   aaa m where month=4   and m.year=aaa.year) as m4
from aaa   group by year
```

#### 第 5 题

**一张部门表，一张员工表**

**部门表建表语句：**

```plain
create table dpt
(
id int(11),
department varchar(12) 
)DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
insert into dpt values(1,'设计');
insert into dpt values(2,'市场');
insert into dpt values(3,'售后')
```

**员工表建表语句：**

```plain
create table emy
(
id int(11),
dptID int(11),
`name` varchar(12)
)DEFAULT CHARSET=utf8;
```

**插入数据**

```plain
insert into emy values(1,1,'张三');
insert into emy values(2,1,'李四');
insert into emy values(3,2,'王五');
insert into emy values(4,3,'彭六');
insert into emy values(5,4,'陈七')
```

#### 第 6 题

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

**学生表建表语句：**

```plain
create table tablestudent
(
id int(11),
studentno varchar(10),
studentname varchar(10),
classno varchar(10),
classname varchar (11),
score int (11)
)DEFAULT CHARSET = 'utf8';
```

**插入数据:**

```plain
insert into tablestudent values (1,'2005001','张三','0001','数学',69);
insert into tablestudent values (2,'2005002','李四','0001','数学',69);
insert into tablestudent values (3,'2005001','张三','0001','数学',69)
```

**如图所示：**

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620820016106-image.png)

**答案：**
在 mysql 中，不能通过嵌套子查询来直接删除或者修改记录，需要通过别名来指定嵌套子查询作为一个临时表。

**给嵌套子查询的结果取一个别名，然后从这个表中再次查询出记录,然后再做删除或者修改操作。**

```plain
delete from tablestudent
where id not in
(select a.id from
(select id from tablestudent 
group by studentno, studentname, classno,classname, score)
a)
```

#### 第 7 题

**关于年度、季度 SQL 统计查询的面试题**

**A 表 - 销售数据表，其中 adddate 是销售时间，company 是客户公司，money 是销售金额，sales 是销售员**

**B 表 - 产品销售表，aid 对应 A 表中的 id，pid 对应是 C 表中产品表。**

**C 表 - 产品类型表**

1. **销售数据表建表语句：**

```plain
CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adddate` datetime NOT NULL,
  `company` varchar(10) DEFAULT NULL,
  `money` decimal(7,2) DEFAULT NULL,
  `sales` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
insert  into `a`(`id`,`adddate`,`company`,`money`,`sales`) 
values 
(1,'2011-01-02 00:00:00','A','10000.00','张三'),
(2,'2011-07-03 00:00:00','B','50000.00','王五'),
(3,'2011-09-03 00:00:00','A','20000.00','赵六'),
(4,'2012-07-03 00:00:00','A','10000.00','李四'),
(5,'2013-10-03 00:00:00','B','5000.00','麻子'),
(6,'2013-07-04 00:00:00','B','6000.00','小柒');
```

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620807910374-image.png)

**2.产品销售表建表语句:**

```plain
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product` varchar(20) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
insert  into `b`(`id`,`product`,`pid`,`price`,`number`) 
values 
(1,'AC1100',1,'10000.00',2),
(2,'AC1200',1,'20000.00',3),
(3,'SSL1100',2,'10000.00',1),
(4,'WOC1100',3,'10000.00',1);
```

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620807928978-image.png)

**3.产品类型表建表语句:**

```plain
CREATE TABLE `c` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
insert  into `c`(`id`,`type`) 
values (1,'AC'),
(2,'SSL'),
(3,'WOC');
```

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620807976615-image.png)

**问题 1：**

**请使用 SQL 语句统计 2011 年至 2013 年三年中每个季度的销售金额?**

**另外在 MySQL 中有个特殊的规定，即不允许使用列别名作为查询条件,实在要执行，只好把新字段的组成在条件里再实现一遍**

**据说是因为 MySql 中列的别名本来是返回结果的时候才显示的.这个还没有得到证实，纯属猜测！**

```plain
SELECT  
YEAR(`adddate`) AS yer, 
QUARTER(`adddate`) AS jd, 
SUM(money) 
FROM a  
WHERE YEAR(`adddate`) IN ('2011','2012','2013')  
GROUP BY QUARTER(`adddate`),YEAR(`adddate`)
```

**问题 2：**

**请使用 SQL 语句按如下格式统计 2013 年销售金额前 5 位的销售员**

**不知道是不是考察：order by 和 group by 共用的问题。order by 必须放在 group by 后面**

```plain
SELECT id AS `no`,sales,SUM(`money`) AS money FROM a WHERE YEAR(`adddate`) = '2013' GROUP BY sales ORDER BY money DESC LIMIT 5
```

**问题 3：**

**请用 SQL 语句统计出哪个产品线的销售金额最多?**

```plain
SELECT c.`type`,MAX(tab.tt) FROM (SELECT pid,SUM(price*number) AS tt FROM b GROUP BY pid) AS tab LEFT JOIN c ON tab.pid = c.`id`
```

#### 第 8 题

**数据库的表设计如下：**

**部门表：部门编号，部门名称，地址；**

**员工表：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号；**

**部门表建表语句：**

```plain
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,    
job VARCHAR(20) CHECK (job IN('CLERK','SALESMAN','MANAGER','ANALYST')),mgp INT,  
hiredate DATE ,
sal DECIMAL(10,2),
comm DECIMAL(10,2),  
deptno INT,  
CONSTRAINT pk_we FOREIGN KEY (deptno) REFERENCES dept (deptno)  
)DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK');  
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
```

**如图所示：**

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620810449745-image.png)

**员工表建表语句：**

```plain
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,    
job VARCHAR(20) CHECK (job IN('CLERK','SALESMAN','MANAGER','ANALYST')),mgp INT,  
hiredate DATE ,
sal DECIMAL(10,2),
comm DECIMAL(10,2),  
deptno INT,  
CONSTRAINT pk_we FOREIGN KEY (deptno) REFERENCES dept (deptno)  
)DEFAULT CHARSET=utf8;
```

**插入数据：**

```plain
INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK');  
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
```

**如图所示：**

![](https://gitee.com/zhanglinx/picture/raw/master/2021-5-12/1620810891502-image.png)

**问题如下：**
1、列出至少有一个雇员的所有部门

```plain
select dname,count(empno) as 部门人数 from dept,emp where dept.deptno=emp.deptno
 group by dname 
 having count(empno)>=1;
```

**2、列出薪金比"SMITH"多的所有雇员**

```plain
select * from emp where sal>(select sal from emp where ename='SMITH');
```

3. **列出入职日期早于其直接上级的所有雇员**

```plain
select * from emp e1 where hiredate <(select hiredate from emp e2 where e2.empno=e1.mgp);
```

4. **找员工姓名和直接上级的名字**

```plain
select e1.ename 员工姓名,e2.ename 直接上级 from emp e1,emp e2 where e1.mgp=e2.empno;
```

5. **显示部门名称和人数**

```plain
select dname,count(empno) as 部门人数 from dept,emp where dept.deptno=emp.deptno 
group by dname;
```

6. **显示每个部门的最高工资的员工**

```plain
SELECT ename,sal,e1.deptno FROM emp e1
WHERE sal = 
(SELECT MAX(sal) FROM emp e2 WHERE e2.`deptno`=e1.`deptno`
GROUP BY e2.`deptno`);
```

7. **显示每个部门的工资前 2 名的员工**

```plain
select deptno, ename, sal      
from emp e1     
where      
   (  
    select count(1)     
    from emp e2     
    where e2.deptno=e1.deptno and e2.sal>=e1.sal  
   ) <=2  
order by deptno, sal desc;
```