LeetCode 数据库刷题笔记

重置 mysql 自增列

ALTER TABLE table_name AUTO_INCREMENT = 1; 

不过这种方式自能设置大于当前使用的值，不能设置小于等于当前已经使用的自增列的值。myisam 如果设置小于等于，则自增列的值会自动设置为当前最大值加 1。innodb 则不会改变。

phpMyAdmin 让 blob 字段显示出来

$_REQUEST['display_blob'] = true; 

避免错误的秘诀在于使用 IS NULL 或者 IS NOT NULL 两种操作来对 NULL 值做特殊判断。

count(case when order_date = customer_pref_delivery_date then 1 end)

此表无主键，因此可能会存在重复行。

COUNT(if(rating < 3, 1, NULL)

# Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

SELECT query_name,
    round(AVG(rating / position), 2) AS quality,
    round(COUNT(if(rating < 3, 1, NULL)) / COUNT(*) * 100, 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name 

赞助博客