Database基础概念与常用SQL 语句

I. Database 基础概念

Primary Key

参考廖雪峰的解释 https://www.liaoxuefeng.com/wiki/1177760294764384/1218728391867808

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

例如，假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

FOREIGN KEY

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

例如：

• 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
• 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

Eg.

Leetcode sql 1327. List the Products Ordered in a Period

Product_id 就是用来和Products table连接的外键

#1327. List the Products Ordered in a Period

# Write your MySQL query statement below

SELECT p.product_name, SUM(o.unit) AS unit
FROM products p INNER JOIN orders o
ON p.product_id = o.product_id
WHERE YEAR(o.order_date) = 2020 AND MONTH(o.order_date) = 2
GROUP BY p.product_name
HAVING SUM(o.unit) >= 100
;

---

II. 常用SQL 语句

LEFT JOIN statement

保留左边Table所有的行，即使右边的Table没有与之匹配的项。其余可以加条件，让左边Table和右边Table关联起来。

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

原始的表 (用在例子中的)：

“Persons” 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

“Orders” 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

左连接（LEFT JOIN）实例

现在，我们希望列出所有的人，以及他们的定购 – 如果有的话。

您可以使用下面的 SELECT 语句：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

练习： https://leetcode.com/problems/combine-two-tables/

参考：

https://www.cnblogs.com/njczy2010/p/5196034.html

---

RIGHT JOIN statement

RIGHT JOIN 同理，保留右边Table所有的行，即使左边Table没有与之对应的项。

---

INNER JOIN

INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 两个表中的元素没有匹配，则不会列出这些行。

eg.

# 613. Shortest Distance in a Line
# Write your MySQL query statement below

SELECT MIN(ABS(p1.x - p2.x)) AS shortest
FROM point p1
INNER JOIN point p2 ON p1.x != p2.x
;

---

SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

The following SQL statement lists the number of different (distinct) customer countries:

SELECT COUNT(DISTINCT Country) FROM Customers;


---

TO_DAYS()

TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATE

Eg.

#197. Rising Temperature
#TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATE

# Write your MySQL query statement below
SELECT wt1.Id
FROM Weather wt1, Weather wt2
WHERE wt1.Temperature - wt2.Temperature > 0 AND
    TO_DAYS(wt1.RecordDate) - TO_DAYS(wt2.RecordDate) = 1;

---

DELETE FROM

删除某一条

Eg.

# 196. Delete Duplicate Emails

# Write your MySQL query statement below

DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
    p1.Id > p2.Id;

---

GROUP BY

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理

HAVING

HAVING 子句可以让我们筛选分组后的各组数据。

eg.

#596. Classes More Than 5 Students
# Write your MySQL query statement below

SELECT class
FROM courses

GROUP BY class
HAVING COUNT(DISTINCT student) >=5
;

参考：

https://www.jianshu.com/p/8b135d373df1

---

CASE Statement

The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Eg.

# 627. Swap Salary
# Write your MySQL query statement below

UPDATE salary
SET 
    sex = CASE 
        WHEN sex = 'm' THEN 'f'
        WHEN sex = 'f' THEN 'm'
    END

;

---

MOD()

The MOD() function returns the remainder of a number divided by another number.

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

Eg.

# 620. Not Boring Movies
# Write your MySQL query statement below

SELECT *
FROM cinema
WHERE mod(id, 2) = 1 AND description != 'boring'
ORDER BY rating DESC
;

---

Window Functions

引用， “窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

那么语法中的<窗口函数>都有哪些呢？

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。”

参考：

https://zhuanlan.zhihu.com/p/92654574 附带例子

EG.

#1303. Find the Team Size

# Write your MySQL query statement below

SELECT employee_id,
       COUNT(employee_id) OVER (PARTITION BY team_id) AS team_size
FROM Employee
;

---

IFNULL

MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

两个参数可以是文字值或表达式。

以下说明了IFNULL函数的语法：

IFNULL(expression_1,expression_2);

SQL

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

---

AVG

求出某一列，满足某个条件的平均值。

Eg.

#1173. Immediate Food Delivery I
# Write your MySQL query statement below
# AVG， 求出满足一定条件，某一数列的平均值

SELECT ROUND(100*AVG(order_date = customer_pref_delivery_date), 2) AS immediate_percentage
FROM Delivery
;

0 赞 Like