LeetCode & SQL All In One

LeetCode & SQL All In One

https://leetcode.cn/study-plan/sql/?progress=sr9i61h

MySQL

SELECT * FROM table_name;

SELECT column_name,  column_name2 , ... FROM table_name;

SELECT DISTINCT * FROM table_name;

SELECT DISTINCT column_name, column_name2, ... FROM table_name;

WHERE

WHERE 子句用于提取那些满足指定条件的记录。

SELECT column_name, column_name2, ...
FROM table_name
WHERE column_name operator value;

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

# or
SELECT column_name, column_name2, ... FROM table_name where column_name > 1000 or column_name2 = 100;

# UNION
SELECT column_name, column_name2, ... FROM table_name where column_name > 1000
UNION
SELECT column_name, column_name2, ... FROM table_name where column_name2 = 100;

JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

LEFT JOIN、
RIGHT JOIN、
INNER JOIN、
OUTER JOIN

# 

demos

# Write your MySQL query statement below

# 国家名称、人口和面积  任意顺序

# select name, population, area from World where area >= 3000000 or population >= 25000000
# 执行用时： 288 ms , 在所有 MySQL 提交中击败了 20.05% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户

# 运行速度更快
# UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

select name, population, area from World where area >= 3000000 
UNION
select name, population, area from World where population >= 25000000

# 执行用时： 255 ms , 在所有 MySQL 提交中击败了 43.35% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户 ✅

# Write your MySQL query statement below

# select product_id from Products where low_fats = 'Y' and recyclable = 'Y'
# 执行用时： 638 ms , 在所有 MySQL 提交中击败了 16.88% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户

# JOIN
select Products.product_id from Products
INNER JOIN 
Products AS alias_name
ON 
Products.product_id = alias_name.product_id
where Products.low_fats = 'Y' and Products.recyclable = 'Y'
# 执行用时： 540 ms , 在所有 MySQL 提交中击败了 77.15% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户

# Write your MySQL query statement below

# select name from customer where referee_id != 2 or (referee_id IS NULL)
# select name from customer where referee_id <> 2 or (referee_id IS NULL)

# 方法： 使用 <> (!=) 和 IS NULL [Accepted]
# MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。
# 任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。
# 这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 `IS NULL` 和 `IS NOT NULL` 两种操作来对 NULL 特殊判断。

# https://leetcode.cn/problems/find-customer-referee/solution/xun-zhao-yong-hu-tui-jian-ren-by-leetcode/

select name from customer where NOT (referee_id = 2) or (referee_id IS NULL)
# 执行用时： 579 ms , 在所有 MySQL 提交中击败了 16.30% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户

# NOT 2
# {"headers":{"Customer":["id","name","referee_id"]},"rows":{"Customer":[[1,"Will",null],[2,"Jane",null],[3,"Alex",2],[4,"Bill",null],[5,"Zack",1],[6,"Mark",2],[7,"Georgi",4],[8,"Bezalel",2],[9,"Parto",2],[10,"Chirstian",4],[11,"Kyoichi",2],[12,"Anneke",3],[13,"Tzvetan",2],[14,"Saniya",3],[15,"Sumant",16],[16,"Duangkaew",21],[17,"Mary",4],[18,"Patricio",3],[19,"Eberhardt",7],[20,"Berni",12]]}}


# (select name from customer where NOT (referee_id = 2)
# UNION
# select name from customer where (referee_id IS NULL))
# ORDER BY customer.id ASC 

# Table 'customer' from one of the SELECTs cannot be used in global ORDER clause ❌

# (select name from customer where NOT (referee_id = 2)
# UNION
# select name from customer where (referee_id IS NULL))
# ORDER BY id ASC
# Unknown column 'id' in 'order clause' ❌

# Write your MySQL query statement below

# CREATE VIEW abc AS
# select name as Customers from Customers
# JOIN
# Orders
# where Customers.id = Orders.id and Orders.CustomerId IS NOT NULL



select name as 'Customers' from customers
where customers.id 
NOT IN
(select customerid from orders)

# 执行用时： 535 ms , 在所有 MySQL 提交中击败了 69.86% 的用户 内存消耗： 0 B , 在所有 MySQL 提交中击败了 100.00% 的用户

# https://leetcode.cn/problems/customers-who-never-order/solution/cong-bu-ding-gou-de-ke-hu-by-leetcode/

SQL 注释

MySQL

-- 单行注释

/* 

多行注释

 */

https://www.ibm.com/docs/zh/db2/9.7?topic=statements-comments

refs

https://github.com/xgqfrms/SQL

---

---

©xgqfrms 2012-2020

www.cnblogs.com/xgqfrms 发布文章使用：只允许注册用户才可以访问！

原创文章，版权所有©️xgqfrms, 禁止转载 🈲️，侵权必究⚠️！

---