mysql 语句面试题

最新推荐文章于 2022-02-22 15:08:49 发布

假装不知道后果

最新推荐文章于 2022-02-22 15:08:49 发布

阅读量1.4k

点赞数 2

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_17758813/article/details/104496880

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

假设有一个“职工”表，表结构如下

职工号	姓名	年龄	月工资	部门号	电话	办公室
1	张三	25	2500	1	1123	101
2	李四	26	1500	1	1234	102
3	王五	22	900	2	4567	103
4	刘秋	21	2000	2	34598	103

创建表格：

//创建表格
mysql> create table workes (
    -> id int not null auto_increment,
    -> name varchar(100) not null,
    -> age varchar(5) not null,
    -> salary int(11) not null,
    -> department int not null,
    -> telephone varchar(13) not null,
    -> office varchar(5) not null,
    -> primary key(id)
    -> )default charset=utf8;

//插入数据
mysql> insert into workers (name,age,salary,department,telephone,office) values ("张三","25","2500",1,"1234567","101");
mysql> insert into workers (name,age,salary,department,telephone,office) values ("李四","26","1500",1,"5678990","102"),("王五","22","900",2,"7657875","103");
mysql> insert into workers (name,age,salary,department,telephone,office) values ("刘秋","21","2000",2,"8799765","103"),("井七","27","1900",1,"98765454","102");

//查看数据内容
mysql> select * from workers;
+----+--------+-----+--------+------------+-----------+--------+
| id | name   | age | salary | department | telephone | office |
+----+--------+-----+--------+------------+-----------+--------+
|  1 | 张三   | 25  |   2500 |          1 | 1234567   | 101    |
|  2 | 李四   | 26  |   1500 |          1 | 5678990   | 102    |
|  3 | 王五   | 22  |    900 |          2 | 7657875   | 103    |
|  4 | 刘秋   | 21  |   2000 |          2 | 8799765   | 103    |
|  5 | 井七   | 27  |   1900 |          1 | 98765454  | 102    |
+----+--------+-----+--------+------------+-----------+--------+
5 rows in set (0.00 sec)

一、问题：

查询每个部门中月工资最高的“职工号”, SQL查询语句如下：

SELECT 职工号 FROM 职工 AS A

WHERE职工.月工资＝（SELECT MAX(月工资) FROM 职工 AS B WHERE A.部门号＝ B.部门号）;

语句查询：

mysql> select id from workers AS A
    -> where A.salary = (select MAX(salary) from workers AS B
    -> where A.department = B.department);
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.00 sec)

1:请简要的说明该查询语句对查询效率的影响(6分)

2:对该查询语句进行修改，使它既可以完成相同的功能，又可以提高查询效率。(8分)

回答：

1. 效率低。对于外层职工关系A 中的每一个记录，都要对内层职工关系B进行检索，所有效率不高。

方法 1）使用临时表

SELECT MAX(月工资) as 最高工资，部门号 INTO temp FROM 职工 GROUP BY 部门号;

SELECT 职工号 FROM 职工，temp WHERE 月工资＝最高工资 AND 职工.部门号＝ temp.部门号；

//创建临时表
mysql> create TEMPORARY table temp (select MAX(salary) AS Most_Salary,department from workers group by department);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from temp;
+-------------+------------+
| Most_Salary | department |
+-------------+------------+
|        2500 |          1 |
|        2000 |          2 |
+-------------+------------+
2 rows in set (0.00 sec)

//查询
mysql> select id from workers,temp where salary=Most_Salary and workers.department=temp.department;
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.01 sec)

注意：在mysql 中不支持select MAX(salary) as Most_Salary,department into temp from workers GROUP BY department;可更改为create table temp (select MAX(salary) AS Most_Salary,department from workers group by department);

方法 2)

SELECT 职工号 FROM 职工,（SELECT MAX(月工资) as 最高工资,部门号 FROM 职工 GROUP BY 部门号） as DEPMAX

WHERE 月工资＝最高工资 AND 职工.部门号＝ DEPMAX.部门号;

mysql> select id from workers,(select MAX(salary) AS Most_Salary,department from workers group by department ) AS DEPMAX where salary=Most_Salary and workers.department=DEPMAX.department;
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.00 sec)

//列出多个属性
mysql> select id,workers.salary,workers.department from workers,(select Max(salary) as max_salary,department from workers group by department) as limittmp where workers.salary=limittmp.max_salary and workers.department=limittmp.department; 
+----+--------+------------+
| id | salary | department |
+----+--------+------------+
|  1 |   2500 |          1 |
|  4 |   2000 |          2 |
+----+--------+------------+
2 rows in set (0.01 sec)

二、问题：

假设在“职工”表中的“年龄”和“月工资”字段上创建了索引，下列的查询语句可能不会促使查询优化器使用索引，从而降低了效率，请写出可以完成相同功能又可以提高查询效率的SQL语句。

select 姓名,年龄,月工资 from 职工 where 年龄 > 45 or 月工资 < 1000;

回答：

SELECT 姓名,年龄,月工资 FROM 职工

WHERE 年龄 > 45

UNION

SELECT 姓名，年龄，月工资 FROM 职工

WHERE月工资 < 1000;

使用UNION就可以促使查询优化器使用索引。

mysql> select name,age,salary from workers where age>45 or salary <1000;
+--------+-----+--------+
| name   | age | salary |
+--------+-----+--------+
| 王五   | 22  |    900 |
+--------+-----+--------+
1 row in set (0.01 sec)

mysql> select name,age,salary from workers where age>45 UNION select name,age,salary from workers where salary <1000;
+--------+-----+--------+
| name   | age | salary |
+--------+-----+--------+
| 王五   | 22  |    900 |
+--------+-----+--------+
1 row in set (0.00 sec)

三、问题

设有关系EMP（ENO，ENAME，SALARY，DNO），其中各属性的含义依次为职工号、姓名、工资和所在部门号，

以及关系DEPT（DNO，DNAME，MANAGER），其中各属性含义依次为部门号、部门名称、部门经理的职工号。（回答下列题目）(20分)

1. 列出各部门中工资不低于600元的职工的平均工资。(10分)

2. 请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。(10分)

//创建表格
mysql> create table EMP (
    -> ENO int not null auto_increment,
    -> ENAME varchar(100) not null,
    -> SALARY int not null,
    -> DNO int not null,
    -> primary key(EMO)
    -> )default charset=utf8;

//添加数据
mysql> insert into EMP (ENAME,SALARY,DNO) values ("李四","1500","1"),("王五","400","2");
mysql> insert into EMP (ENAME,SALARY,DNO) values ("张三","800","2"),("刘秋","900","1");

// 查看数据内容
mysql> select * from EMP;
+-----+--------+--------+-----+
| ENO | ENAME  | SALARY | DNO |
+-----+--------+--------+-----+
|   1 | 李四   |   1500 |   1 |
|   2 | 王五   |    400 |   2 |
|   3 | 张三   |    800 |   2 |
|   4 | 刘秋   |    900 |   1 |
+-----+--------+--------+-----+
4 rows in set (0.01 sec)


//创建表格
mysql> create table DEPT (
    -> DNO int not null,
    -> DNAME varchar(100) not null,
    -> MANAGER int not null,
    -> primary key(DNO)
    -> )default charset=utf8;
//添加数据
mysql> insert into DEPT (DNO,DNAME,MANAGER) values (2,"销售部","3");
//查看数据内容
mysql> select * from DEPT;
+-----+-----------+---------+
| DNO | DNAME     | MANAGER |
+-----+-----------+---------+
|   2 | 销售部    |       3 |
+-----+-----------+---------+
1 row in set (0.00 sec)

回答：

1）select DNO,avg(SALARY) from EMP where SALARY>=600 group by DNO;

2）摘抄的答案不能执行，验证的答案在代码框里面。

UPDATE EMP SET SALARY=SALARY*1.1 WHERE ENO IN
(SELECT ENO FROM EMP, DEPT
WHERE EMP.DNO=DEPT.DNO AND DNAME='销售部'AND SALARY<600);
或者
UPDATE EMP SET SALERY=SALERY*1.1 FROM EMP A,DEPT B
WHERE A.DNO = B.DNO AND B.DNAME="销售部" AND B.SALARY<600;

参考的链接：

https://blog.csdn.net/Ck_Max/article/details/86502848

https://blog.csdn.net/longgeaisisi/article/details/90694573

https://blog.csdn.net/elangsun/article/details/90709870 （有用）

嵌套公式：

UPDATE 表X SET A = 1, B = 2 WHERE C IN (SELECT * FROM (SELECT C FROM 表Y WHERE D = 3) ALLINFO) AND E = 5; 标蓝处再添加一层可将里边的查询提前执行,避免N*N次这样的慢查询.

//语句一
mysql> UPDATE EMP SET SALARY=SALARY*1.1 WHERE ENO IN 
    -> (SELECT * from  
    -> (SELECT ENO FROM EMP,DEPT WHERE DEPT.DNO=EMP.DNO AND DNAME="销售部"
    -> AND SALARY<600) ALLINFO);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from EMP;
+-----+--------+--------+-----+
| ENO | ENAME  | SALARY | DNO |
+-----+--------+--------+-----+
|   1 | 李四   |   1500 |   1 |
|   2 | 王五   |    484 |   2 |
|   3 | 张三   |    800 |   2 |
|   4 | 刘秋   |    900 |   1 |
+-----+--------+--------+-----+
4 rows in set (0.00 sec)

//语句二
mysql> UPDATE EMP A,DEPT B SET SALARY=SALARY*1.1 
    -> WHERE A.DNO = B.DNO AND B.DNAME="销售部" AND A.SALARY<600;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+-----+--------+--------+-----+
| ENO | ENAME  | SALARY | DNO |
+-----+--------+--------+-----+
|   1 | 李四   |   1500 |   1 |
|   2 | 王五   |    532 |   2 |
|   3 | 张三   |    800 |   2 |
|   4 | 刘秋   |    900 |   1 |
+-----+--------+--------+-----+
4 rows in set (0.00 sec)

四、设职工---社团数据库有三个基本表：(20分)

职工（职工号，姓名，年龄，性别）；

社会团体（编号，名称，负责人，活动地点）；

参加（职工号，编号，参加日期）；

其中：

1）职工表的主键为职工号。

2）社会团体表的主键为编号；外码为负责人，被参照表为职工表，对应属性为职工号。

3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。

试用SQL语句表达下列操作：

1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。

2）建立下列两个视图。

社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；

参加人情况（职工号，姓名，社团编号，社团名称，参加日期）

3）查找参加唱歌队或篮球队的职工号和姓名。

4）求参加人数超过100人的社会团体的名称和负责人。

答：

1）定义职工表、社会团体表和参加表，并说明其主键和参照关系。

//1）职工表的主键为职工号。
mysql> CREATE TABLE 职工 (职工号 CHAR(10) NOT NULL,
    -> 姓名 CHAR(8) NOT NULL,
    -> 年龄 SMALLINT,
    -> 性别 CHAR(2),
    -> CONSTRAINT C1_PK PRIMARY KEY (职工号)
    -> );
Query OK, 0 rows affected (0.01 sec)

//2）社会团体表的主键为编号；外码为负责人，被参照表为职工表，对应属性为职工号。
mysql> CREATE TABLE 社会团体(
    -> 编号 CHAR(8) NOT NULL,
    -> 名称 CHAR(12) NOT NULL,
    -> 负责人 CHAR(8),
    -> 活动地点 VARCHAR(50),
    -> CONSTRAINT C2_PK PRIMARY KEY (编号),
    -> CONSTRAINT C2_FK FOREIGN KEY (负责人) REFERENCES 职工(职工号)
    -> );
Query OK, 0 rows affected (0.02 sec)

//3）参加表的职工号和编号为主键；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
mysql> CREATE TABLE 参加 (
    -> 职工号 CHAR(8),
    -> 编号 CHAR(8),
    -> 参加日期 DATE,
    -> CONSTRAINT C3_PK PRIMARY KEY(职工号,编号),
    -> CONSTRAINT C3_FK_Z FOREIGN KEY(职工号) REFERENCES 职工(职工号),
    -> CONSTRAINT C3_FK_B FOREIGN KEY(编号) REFERENCES 社会团体(编号)
    -> );
Query OK, 0 rows affected (0.02 sec)

2）建立下列两个视图。

//社团负责人（编号,名称,负责人职工号,负责人姓名,负责人性别);
mysql> CREATE VIEW 社团负责人 (编号,名称,负责人职工号,负责人姓名,负责人性别)
    -> AS SELECT 编号,名称,负责人,姓名,性别
    -> FROM 社会团体,职工
    -> WHERE 社会团体.负责人=职工.职工号;
Query OK, 0 rows affected (0.02 sec)

//参加人情况（职工号,姓名,社团编号,社团名称,参加日期);
mysql> CREATE VIEW 参加人情况 (职工号,姓名,社团编号,社团名称,参加日期)
    -> AS SELECT 参加.职工号,姓名,社会团体.编号,名称,参加日期
    -> FROM 职工,社会团体,参加 
    -> WHERE 职工.职工号=参加.职工号 AND 参加.编号=社会团体.编号;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------------+
| Tables_in_cre_data_mysql |
+--------------------------+
| 参加                     |
| 参加人情况               |
| 社会团体                 |
| 社团负责人               |
| 职工                     |
| DEPT                     |
| EMP                      |
| alter_tbl                |
| clone_import_vegetables  |
| import_vegetables        |
| mysql_vegetables         |
| workers                  |
+--------------------------+
12 rows in set (0.00 sec)

3）查找参加唱歌队或篮球队的职工号和姓名。

4）求参加人数超过100人的社会团体的名称和负责人。