MySQL基础知识点

今天刷力扣题时发现我的数据库忘得七七八八，这里总结一些刷题时查的知识点：

1. 字段名含空格或者字段名是保留字
   这里字段名可以用单引号 ' ' 或者反引号 ` ` 括起来。

select `Cancellation Rate`
from Trips

2. 比对时间区间
   语法： date(字段名) between '开始时间' and '结束'

SELECT Request_at as Day,`Cancellation Rate`
FROM Trips  
WHERE date(Request_at) between '2013-10-01' and  '2013-10-03'

除此之外，还有返回年份，月份，天数，星期几，哪个季度，
可参考链接：https://www.jb51.net/article/138562.htm

3. 查询计算字段
   语法： select 表达式 AS 字段名

SELECT cancel/total AS `Cancellation Rate`
FROM Trips  
WHERE date(cancel/total) between '2013-10-01' and  '2013-10-03'

4. 除法保留特定小数位

• DECIMAL(有效数字数的精度，小数点后的位数)
  有四舍五入
• TRUNCATE(expression,小数点后的位数)
  单纯截断小数点后多余数位

# convert(a/b,decimal(15,2)) ：保留15位有效数位，其中小数点后有两位
SELECT convert(cancel/total,decimal(15,2)) AS `Cancellation Rate`
FROM Trips  
WHERE date(Request_at) between '2013-10-01' and  '2013-10-03'

5. 查询某个字段的重复记录
   根据A字段分组，计数每个组的记录数，返回组记录数大于1的字段名
   GROUP BY 字段名： 按xx字段分组
   COUNT(字段) ： 返回记录数，

SELECT Request_at
FROM Trips
GROUP BY Request_at
HAVING COUNT(Request_at) > 1

根据A字段分组，计数每个组的记录数，返回组记录数大于1的记录

SELECT *
FROM Trips
WHERE Request_at IN (
	SELECT Request_at
    FROM Trips
    GROUP BY Request_at
    HAVING COUNT(Request_at) > 1
)

根据A字段分组，计数每个组的记录数，返回组记录数大于1的多余的记录（比如重复两条记录只返回第二条）

• 方法一

  SELECT *
  FROM Trips
  WHERE Request_at IN (
      SELECT Request_at
      FROM Trips
      GROUP BY Request_at
      HAVING COUNT(Request_at) > 1
  ) AND Id NOT IN (
      SELECT MIN(Id)
      FROM Trips
      GROUP BY Request_at
      HAVING COUNT(Request_at) > 1
  )
  

• 方法二

  SELECT * 
  FROM Trips
  WHERE Id NOT IN (
  	  SELECT MIN(Id)
  	  FROM Trips
  	  GROUP BY (Request_at)
  )
  

• 方法三

  SELECT * 
  FROM Trips AS ta 
  WHERE ta.Id <> ( 
      SELECT MIN( tb.Id ) 
      FROM Trips AS tb 
      WHERE ta.Request_at = tb.Request_at 
  );
  

6. 查询多个字段的重复记录
   根据A,B字段分组，计数每个组的记录数，返回组记录数大于1的记录

SELECT *
FROM Trips a
WHERE (a.Request_at,a.people_name) IN (
	SELECT Request_at,people_name
	FROM Trips
	GROUP BY Request_at,people_name
	HAVING count(*)>1
);

根据A,B字段分组，计数每个组的记录数，返回组记录数大于1的多余的记录（比如重复两条记录只返回第二条）

• 方法一：

  SELECT *
  FROM Trips a
  WHERE (a.Request_at,a.people_name) IN (
      SELECT Request_at,people_name
      FROM Trips
      GROUP BY Request_at,people_name
      HAVING count(*)>1
  ) AND Id NOT IN (
      SELECT MIN(Id)
      FROM Trips
      GROUP BY Request_at,people_name
      HAVING COUNT(*) > 1
  )
  

• 方法二

  SELECT * 
  FROM Trips
  WHERE Id NOT IN (
  	  SELECT MIN(Id)
  	  FROM Trips
  	  GROUP BY (Request_at,people_name)
  )
  

• 方法三

  SELECT * 
  FROM Trips AS ta 
  WHERE ta.Id <> ( 
      SELECT MIN( tb.Id ) 
      FROM Trips AS tb 
      WHERE ta.Request_at = tb.Request_at AND ta.people_name = tb.people_name
  );
  

7. 删除多余的重复字段

• 方法一：

  DELETE FROM Person
  WHERE id IN (
      SELECT id
      from (
          SELECT * FROM Person
          WHERE Email IN (
    	      SELECT Email
    	      FROM Person
    	      GROUP BY Email
    	      HAVING count(Email) > 1
          ) AND id NOT IN (
              SELECT min(id) AS minId
              FROM Person
              GROUP BY Email
              HAVING count(Email) > 1
          )
      ) dt
  )
  

• 方法二

  // 直接删除同一张表查询出来的结果会报错
  // You can't specify target table 'Person' for update in FROM clause
  DELETE FROM
  WHERE id NOT IN (
      SELECT MIN(id) AS minno
      FROM Person
      GROUP BY Email
  ) dt
  // 原因是：mysql不允许对一张表同时进行查询和更新
  // 所以这里需要一个间接表
  DELETE FROM Person
  WHERE id NOT IN (
      SELECT dt.minno
      FROM (
          SELECT MIN(id) AS minno
    	  FROM Person
    	  GROUP BY Email
      ) dt	
  )
  

• 方法三

  DELETE 
  FROM Person AS ta 
  WHERE ta.id <> (
      SELECT t.minid 
      FROM ( 
          SELECT min( tb.id ) AS minid 
          FROM Person AS tb 
          WHERE ta.Email = tb.Email 
      ) t 
  );
  

8. 查询结果只有一个字段，而且指定字段刚好为空时返回null

SELECT (SELECT employee_id FROM employees WHERE employee_id=10086) AS 查询结果

9. 查询记录里指定列为空时默认为null，可以自定义null值为0（通常用于计算）

SELECT Request_at,IF(COUNT(Request_at),COUNT(Request_at) ,0) AS Total
FROM Trips  
GROUP BY Request_at

SELECT Request_at,IF(COUNT(Request_at) is null ,0) AS Total
FROM Trips  
GROUP BY Request_at

SELECT Request_at,IFNULL(COUNT(Request_at) ,0) AS Total
FROM Trips  
GROUP BY Request_at

10. 排序
    ASC --升序 ; DESC--降序

SELECT Score, Student
FROM Scores
GROUP BY Score
Order BY Score DESC

11. 组内排序并展示序号
    SELECT @rn := 0 ---给变量赋值
    mysql中用户变量赋值有两种方式，一种是=另一种是:=。
    其中区别在于使用set赋值时两种方式都可以使用，使用select赋值时只能使用:=。

SELECT ta.Score , `Rank`
FROM Scores ta LEFT JOIN (
    SELECT Score, convert(@rn := @rn + 1,decimal(15,0)) as `Rank`
    FROM (
        SELECT Score,id 
        FROM Scores 
        GROUP BY Score 
    ) a , (
        SELECT @rn := 0
    ) b
    ORDER BY Score DESC
) tb ON ta.Score = tb.Score
ORDER BY `Rank`;

ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS idx
含义: 按 category 分组, 组内按 id 排序, 组内排序的序号(行号)作为列 idx
ROW_NUMBER() 在 mysql8 才开始支持

SELECT Score, ROW_NUMBER() OVER (ORDER BY Score DESC) AS `Rank`
FROM Scores
GROUP BY Score 

SELECT ta.Score , `Rank`
FROM Scores ta LEFT JOIN (
    SELECT Score, ROW_NUMBER() OVER (ORDER BY Score DESC) AS `Rank`
    FROM Scores
    GROUP BY Score 
) tb ON ta.Score = tb.Score
ORDER BY ta.Score DESC

12. 查询结果去重，过滤重复数据
    可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

SELECT DISTINCT Score
FROM Scores
Order BY Score DESC