SQL解锁常见和复杂查询的解决方案

mysql教程 2 年前 0 17

如何查找数据库表中的重复记录？

为了在数据库表中查找重复记录，我们需要确认重复项的定义。例如，在下面的联系人表中，应该存储联系人的姓名和电话号码，如果满足以下条件，则记录被认为是重复的：姓名和电话号码相同，但如果其中任何一个不同，则它们是唯一的。

当数据库上没有主键或唯一键时，就会出现数据库中的重复问题，这就是为什么建议在表中包含键列。无论如何，使用ANSI SQL 的 group by 子句很容易找到表中的重复记录。

在下面的查询中，我们使用了SELECT 查询来选择 Contacts 表中的所有记录。这里 James、Johnny、Harry 和 Ron 被重复四次。

mysql> select * from Contacts;
+——-+———-+
| name  | phone    |
+——-+———-+
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| Ruby  |  8965342 |
| Ruby  |  6888342 |
+——-+———-+
18 rows in set (0.00 sec)

以下 SELECT 查询将仅根据名称查找重复记录，如果存储两个号码相同但不同的联系人，则可能不正确，如以下结果集中 Ruby 显示为重复，这是不正确的。

mysql> select name, count(name) from contacts group by name;
+——-+————-+
| name  | count(name) |
+——-+————-+
| Harry |           4 |
| James |           4 |
| Johnny |           4 |
| Ron   |           4 |
| Ruby  |           2 |
+——-+————-+
5 rows in set (0.00 sec)

这是查找重复联系人的正确方法，它会查看姓名和电话号码，并且仅在姓名和电话相同时才打印重复的联系人。

mysql> select name, count(name) from contacts group by name, phone;
+——-+————-+
| name  | count(name) |
+——-+————-+
| Harry |           4 |
| James |           4 |
| Johnny |           4 |
| Ron   |           4 |
| Ruby  |           1 |
| Ruby  |           1 |
+——-+————-+

SQL 查询中的having 子句将从唯一记录中过滤掉重复记录。如以下查询所示，它会打印所有重复记录以及它们在表中重复的次数。

mysql> select name, count(name) as times
from contacts
group by name, phone having times>1;
+——-+——-+
| name  | times |
+——-+——-+
| Harry |     4 |
| James |     4 |
| Johnny |     4 |
| Ron   |     4 |
+——-+——-+
4 rows in set (0.00 sec)

这就是如何查找表中重复记录的全部内容。

SQL中 Truncate 和 Delete 的区别？

Truncate和Delete都是面试的热门话题，在 SQL 面试中总是会出现一些关于这些命令的问题。

在生产环境中使用Truncate或Delete或编写任何从表中清除数据的脚本之前，这是需要理解的重要一点。

1. Truncate速度快，Delete速度慢。2. Truncate不进行日志记录，而是逐行删除日志。3. 除非供应商特别支持，否则可以使用delete而不是truncate进行回滚。4. Truncate不会触发触发器，而delete会触发触发器。5. 当涉及到清除表时，不要Delete，而是Truncate它。6. Truncate会重置表中的标识列（如果有），Delete则不会。7. Truncate是DDL而Delete是DML（当你写考试时使用这个） 8. Truncate不支持where子句，而delete支持where子句。

因此，最后，如果我们有包含大量数据的表并且想要清空它，请不要Delete，而是Truncate它。

候选键和主键的区别？

主键和候选键之间有什么区别？另一个流行的 SQL 和数据库面试问题，时不时出现在各种编程面试中？

在了解候选键和主键之间的区别之前，让我们先看看它们之间的一些相似点。

唯一标识：主键和候选键都可以唯一标识数据库中表中的记录。
约束：主键和候选键都有约束 UNIQUE 和 NOT NULL。
结构：主键或候选键可以是表中的单列或多列的组合。

什么是候选键？

候选键可为NULL也可不唯一，用于唯一标识表或关系中的每条记录。不止一个候选键可用于唯一地标识表或关系。例如一个student表，我们可以选择他的编号{id}或者是出生日期{birthday}作为这个student表的主键，都具备对student表进行唯一标识的作用，所以可以为多个也可以为NULL

什么是主键？

主键是唯一且非空的，用于唯一标识表或关系中的每条记录。例如一个student表，我们选择他的编号{id}作为主键，因为每个人的姓名可能相同，但是我们命名的编号肯定不同，所以就起到了唯一标识的作用。并且主键唯一且非空，{id}做到了。

二者关系？

主键是候选键的子集。也就是所有候选键都有成为主键的资格，就看你选谁当主键。

如何查找员工的第二高或第二最高工资？

如何找到某个员工的第二高或第二高薪水是最常见的 SQL 面试问题之一。

我们将编写 SQL 查询来获取员工第二高的工资。在编写查询之前，最好熟悉架构以及表中的数据。这是我们将在此 SQL 示例中使用的 Employee 表：

mysql> SELECT * FROM Employee;
+——–+———-+———+——–+
| emp_id | emp_name | dept_id | salary |
+——–+———-+———+——–+
| 1      | James    | 10      |   2000 |
| 2      | Jack     | 10      |   4000 |
| 3      | Henry    | 11      |   6000 |
| 4      | Tom      | 11      |   8000 |
+——–+———-+———+——–+

SQL中的子查询是解决这种情况的好工具，这里我们首先选择最大工资，然后选择子查询的另一个最大排除结果

mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
+————-+
| max(salary) |
+————-+
| 6000 |
+————-+
1 row IN SET (0.00 sec)

下面是另一个使用子查询和 < 运算符而不是 IN 子句来查找第二高薪水的 SQL 查询：

mysql> SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);
+————-+
| max(salary) |
+————-+
| 6000 |
+————-+
1 row IN SET (0.00 sec)

使用MYSQL数据库的LIMIT关键字的第二个最高工资，

MySQL 数据库的 LIMIT 关键字与 SQL Server 数据库的 TOP 关键字有点相似，并且只允许从结果集中获取某些行。查看下面的 SQL 示例，我们会发现它与 SQL Server TOP 关键字示例非常相似。

mysql> SELECT salary FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;
+——–+
| salary |
+——–+
| 6000 |
+——–+
1 row IN SET (0.00 sec)

12 个用于技术面试的 SQL 查询示例和练习题

现在是时候编写 SQL 查询了。本节包含 SQL 查询面试问题，将测试许多 SQL 技能，例如联接、分组和聚合数据、如何处理 SQL 中的空值等。

在面试中，询问有关员工和部门的问题是很常见的，下面是表格，下面是问题和答案。

1. 你能编写一个 SQL 查询来显示工资比经理高的员工（姓名）吗？

— 工资比经理高的员工（姓名）
SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.salary > b.salary;

2. 编写一个 SQL 查询来查找其部门中薪水最高的员工。

— 部门中薪资最高的员工
SELECT a.emp_name, a.dept_id
FROM Employee a JOIN
(SELECT a.dept_id, MAX(salary) as max_salary
FROM Employee a JOIN Department b ON a.dept_id = b.dept_id
GROUP BY a.dept_id) b
ON a.salary = b.max_salary AND a.dept_id = b.dept_id;

3. 编写一个 SQL 查询来列出少于 3 人的部门？

— 少于 3 人的部门
SELECT dept_id, COUNT(emp_name) as ‘Number of Employee’
FROM Employee
GROUP BY dept_id
HAVING COUNT(emp_name) < 3;

4. 编写一个 SQL 查询来显示所有部门及其人数。

— 所有部门及其人数
SELECT b.dept_name, COUNT(a.dept_id) as ‘Number of Employee’
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id
GROUP BY b.dept_name;

5. 你可以编写一个 SQL 查询来显示同一部门中没有经理的所有员工吗？

— 同一部门没有经理的员工
SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.dept_id != b.dept_id;

6. 你能编写一个 SQL 查询来列出所有部门以及那里的总工资吗？

— 所有部门以及总工资
SELECT b.dept_name, SUM(a.salary) as ‘Total Salary’
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id
GROUP BY b.dept_name;

这就是本文有关SQL 查询示例、练习题和 SQL 查询面试题的全部内容。

扫码领红包

微信赞赏支付宝扫码领红包