iNiBuBian的博客

关于SQL优化的思路，一般都是使用执行计划看看是否用到了索引，主要可能有两大类情况：对业务字段建立了二级联合索引，但是MySQL错误地觉得走主键聚族索引全表扫描效率更高，而没有走二级索引走二级索引，但是引起了几万、几十万的回表，此时还不如利用聚簇索引进行正序或者倒序的全表扫描，配合limit n，全表扫描只需要扫到符合条件的n条就停止。

Write a SQL query to find all duplicate emails in a table namedPerson.+----+---------+| Id | Email |+----+---------+| 1 | a@b.com || 2 | c@d.com || 3 | a@b.com |+----+---------+For e...

TheEmployeetable holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.+----+-------+--------+-----------+| Id | Name | Salary | ...

Table:Person+-------------+---------+| Column Name | Type |+-------------+---------+| PersonId | int || FirstName | varchar || LastName | varchar |+-------------+---------+Pe...

Write a SQL query todeleteall duplicate email entries in a table namedPerson, keeping only unique emails based on itssmallestId.+----+------------------+| Id | Email |+----+------...

Suppose that a website contains two tables, theCustomerstable and theOrderstable. Write a SQL query to find all customers who never order anything.Table:Customers.+----+-------+| Id | Name ...

Given aWeathertable, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.+---------+------------------+------------------+| Id(INT) | Re...

Write a SQL query to get the second highest salary from theEmployeetable.+----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+For exam...

2019/03/09 -- sql必知必会order by:1）使用order by 列名1，进行排序（默认是升序排列）2）可以使用order by 列名1, 列名2； 则是先根据列名1排序，列名1相同的，再根据列名2进行排序3）可以使用order by 列名1 desc, 列名2 desc； 来降序排列，让最大的排在表的第一行4）order by语句，必须是select语句...

SQL中EXISTS的用法 一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。https://www.cnblogs.com/netserver/archive/2008/12/25/1362615.htmlSQL 子查询 EXISTS 和...