python面试----连接、表操作

重磅干货，第一时间送达

| 0x00 表连接操作

到目前为止显示的所有查询一次只从一个表中进行选择。SQL 最有益的特性之一是能够组合来自两个或多个表的数据。

在下面的两个表中，名为 customers 的表存储有关客户的信息：

orders 表存储有关单个订单的信息及其相应的金额：

Tips : 在 SQL 中，“连接表”意味着合并来自两个或多个表的数据。表连接创建一个临时表，显示来自连接表的数据。

订单表没有在两个表中都存储客户名称，而是包含对出现在客户表中的客户 ID 的引用。与在两个表中存储相同的文本值相比，这种方法更有效。

要连接两个表，请在 FROM 子句中将它们指定为逗号分隔的列表：

SELECT customers.ID, customers.Name, orders.Name, orders.AmountFROM customers, ordersWHERE customers.ID=orders.Customer_IDORDER BY customers.ID;

每个表都包含“ID”和“Name”列，因此为了选择正确的 ID 和 Name，使用了完全限定的名称。

Tips : 请注意，WHERE 子句“连接”表的条件是客户表中的 ID 应等于订单表中的 customer_ID。

| 0x01 连接类型

| 自定义名

自定义名称也可用于表格。您可以通过给表“昵称”来缩短连接语句：

SELECT ct.ID, ct.Name, ord.Name, ord.AmountFROM customers AS ct, orders AS ordWHERE ct.ID=ord.Customer_IDORDER BY ct.ID;

| 连接类型

以下是MySQL中可以使用的JOIN类型：

- INNER JOIN
- LEFT JOIN
- RIGHT JOIN

| INNER JOIN

INNER JOIN 等同于 JOIN。当表之间存在匹配项时，它会返回行。

SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

Tips : 请注意用于指定内部连接条件的 ON 关键字。

下图演示了 INNER JOIN 的工作原理：

| LEFT JOIN

LEFT JOIN 返回左表中的所有行，即使右表中没有匹配项。这意味着如果右侧表中的 ON 子句没有匹配项，连接仍将返回结果中第一个表中的行。

下图演示了 LEFT JOIN 的工作原理：

考虑下面的表:

customers:

items:

以下 SQL 语句将返回所有客户以及他们可能拥有的物品：

SELECT customers.Name, items.Name FROM customers LEFT OUTER JOIN items ON customers.ID=items.Seller_id;

Tips : 如果未找到特定行的匹配项，则返回 NULL

| RIGHT JOIN

RIGHT JOIN 返回右表中的所有行，即使左表中没有匹配项。

SELECT table1.column1, table2.column2...FROM table1 RIGHT OUTER JOIN table2ON table1.column_name = table2.column_name;

对上面的数据表使用右连接

SELECT customers.Name, items.Name FROM customersRIGHT JOIN items ON customers.ID=items.Seller_id;

Tips : SQL 语言中还有其他类型的连接，但 MySQL 不支持它们。

| 0x02 UNION

有时，您可能需要将多个表中的数据组合成一个综合数据集。这可能适用于同一数据库中具有相似数据的表，或者可能需要跨数据库甚至跨服务器组合相似数据。为此，请使用 UNION 和 UNION ALL 运算符。

• UNION 将多个数据集组合成一个数据集，并删除任何现有的重复项。

• UNION ALL 将多个数据集合并为一个数据集，但不删除重复行。

| UNION

UNION 中的所有 SELECT 语句必须具有相同的列数。这些列还必须具有相同的数据类型。此外，每个 SELECT 语句中的列必须采用相同的顺序。

SELECT column_name(s) FROM table1UNIONSELECT column_name(s) FROM table2;

下面有两张表

SELECT ID, FirstName, LastName, City FROM FirstUNIONSELECT ID, FirstName, LastName, City FROM Second;

如果您的列在所有查询中不完全匹配，您可以使用 NULL（或任何其他）值，例如：

SELECT FirstName, LastName, Company FROM businessContactsUNIONSELECT FirstName, LastName, NULL FROM otherContacts;

| UNION ALL

UNION ALL 从每个表中选择所有行并将它们合并到一个表中。以下 SQL 语句使用 UNION ALL 从两个表中选择数据：

SELECT ID, FirstName, LastName, City FROM FirstUNION ALLSELECT ID, FirstName, LastName, City FROM Second;

| 0x03 插入语句

SQL 表按行存储数据，一行接一行。INSERT INTO 语句用于将新数据行添加到数据库中的表中。SQL INSERT INTO 语法如下：

INSERT INTO table_nameVALUES (value1, value2, value3,...);

看一下下面这个表:

让我们使用插入语句插入一条数据

INSERT INTO Employees VALUES (8, 'Anthony', 'Young', 35);SELECT * from Employees;

Tips ：使用 SQL INSERT 语句向表中插入记录时，必须为没有默认值或不支持 NUL 的每一列提供一个值。

或者，您可以在 INSERT INTO 语句中指定表的列名：

INSERT INTO table_name (column1, column2, column3, ...,columnN)  VALUES (value1, value2, value3,...valueN);

例如:

INSERT INTO Employees (ID, FirstName, LastName, Age)VALUES (8, 'Anthony', 'Young', 35);SELECT * FROM Employees;

也可以仅将数据插入特定列。

INSERT INTO Employees (ID, FirstName, LastName) VALUES (9, 'Samuel', 'Clark');SELECT * from Employees;

Tips : 该行的 Age 列自动变为 0，因为这是它的默认值。

| 0x04 更新和删除语句

| UPDATE

UPDATE 语句允许我们更改表中的数据。带有 WHERE 子句的 UPDATE 查询的基本语法如下：

UPDATE table_nameSET column1=value1, column2=value2, ...WHERE condition;

在 SET 关键字后以逗号分隔的列表中指定列及其新值。

Tips ： 如果省略 WHERE 子句，表中的所有记录都会被更新！

考虑一下下表:

要更新 John 的薪水，我们可以使用以下查询：

UPDATE Employees SET Salary=5000WHERE ID=1;SELECT * from Employees;

| 更新多列

也可以通过逗号分隔同时更新多个列

UPDATE Employees SET Salary=5000, FirstName='Robert'WHERE ID=1;SELECT * from Employees;

| 删除数据

DELETE 语句用于从表中删除数据。DELETE 查询的工作方式与 UPDATE 查询非常相似。

DELETE FROM table_nameWHERE condition;

例如:

DELETE FROM EmployeesWHERE ID=1;SELECT * from Employees;

Tips : 如果省略WHERE子句，表中的所有记录都会被删除！DELETE 语句从表中永久删除数据。

| 0x05 创建表

一个数据库可以容纳数百个表，每个表在数据库模式中扮演着自己独特的角色。SQL 表由表行和列组成。表列负责存储许多不同类型的数据，包括数字、文本、日期，甚至文件。CREATE TABLE 语句用于创建新表。

CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....columnN data_type(size));

• column_names 指定我们要创建的列的名称。

• data_type 参数指定列可以保存的数据类型。例如，对整数使用 int。

• size 参数指定表列的最大长度。
  

假设您要创建一个名为“Users”的表，该表包含四列：UserID、LastName、FirstName 和 City。使用以下 CREATE TABLE 语句：

CREATE TABLE Users( UserID int, FirstName varchar(100), LastName varchar(100), City varchar(100));

Tips : varchar 是存储字符的数据类型。您可以在类型后的括号中指定字符数。所以在上面的例子中，我们的字段最多可以容纳 100 个字符的文本

| 数据类型

• 数字
  

• INT - 可以有符号或无符号的正常大小的整数。

• FLOAT(M,D) - 不能无符号的浮点数。您可以选择定义显示长度 (M) 和小数位数 (D)。

• DOUBLE(M,D) - 不能无符号的双精度浮点数。您可以选择定义显示长度 (M) 和小数位数 (D)。

• 日期和时间
  

• DATE - YYYY-MM-DD 格式的日期。

• DATETIME - YYYY-MM-DD HH:MM:SS 格式的日期和时间组合。

• TIMESTAMP - 时间戳，从 1970 年 1 月 1 日午夜开始计算

• TIME - 以 HH:MM:SS 格式存储时间。
  

• 字符串类型
  

• CHAR(M) - 固定长度的字符串。大小在括号中指定。最多 255 个字节。

• VARCHAR(M) - 可变长度字符串。最大大小在括号中指定。

• BLOB - “Binary Large Objects”用于存储大量二进制数据，例如图像或其他类型的文件。

• TEXT - 大量文本数据。
  

| 主键

UserID 是我们 Users 表主键的最佳选择。在创建表时使用 PRIMARY KEY 关键字将其定义为主键。

CREATE TABLE Users(   UserID int,   FirstName varchar(100),   LastName varchar(100),   City varchar(100),   PRIMARY KEY(UserID));

| 0x05 SQL约束

SQL 约束用于指定表数据的规则。

• NOT NULL - 表示列不能包含任何 NULL 值

• UNIQUE - 不允许在列中插入重复值。UNIQUE 约束维护表中列的唯一性。一张表中可以使用多个 UNIQUE 列。

• PRIMARY KEY - 强制表接受特定列的唯一数据，并且此约束创建唯一索引以更快地访问表。

• CHECK - 根据逻辑表达式确定值是否有效。

• DEFAULT - 在向表中插入数据时，如果没有向列提供任何值，则该列将获得设置为 DEFAULT 的值。
  

| 自增

自动递增允许在将新记录插入表时生成唯一编号。通常，我们希望每次插入新记录时自动创建主键字段的值。

默认情况下，AUTO_INCREMENT 的起始值为 1，每条新记录都会递增 1。让我们将 UserID 字段设置为自动生成新值的主键：

UserID int NOT NULL AUTO_INCREMENT,PRIMARY KEY (UserID)

下面的示例演示了如何使用约束创建表。

CREATE TABLE Users (id int NOT NULL AUTO_INCREMENT,username varchar(40) NOT NULL, password varchar(10) NOT NULL,PRIMARY KEY(id));

| 0x06 修改表

| 修改

ALTER TABLE 命令用于添加、删除或修改现有表中的列。您还可以使用 ALTER TABLE 命令在现有表上添加和删除各种约束。考虑以下名为 People 的表：

以下 SQL 代码添加一个名为 DateOfBirth 的新列

ALTER TABLE People ADD DateOfBirth date;SELECT * from People;

| 删除

以下 SQL 代码演示了如何删除 People 表中名为 DateOfBirth 的列。

ALTER TABLE People DROP COLUMN DateOfBirth;SELECT * from People;

要删除整个表，请使用 DROP TABLE 命令：

DROP TABLE People;

| 重命名

ALTER TABLE 命令也用于重命名列：

ALTER TABLE PeopleRENAME FirstName TO name;SELECT * from People;

| 0x07 视图

在 SQL 中，VIEW 是一个基于 SQL 语句结果集的虚拟表。视图包含行和列，就像真正的表一样。视图中的字段是来自数据库中一个或多个实际表的字段。

视图使我们能够：

• 以用户或用户类别自然或直观的方式构建数据。

• 限制对数据的访问，使用户可以查看和（有时）修改他们需要的内容，仅此而已。

• 汇总来自各种表格的数据并使用它来生成报告。

CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition;

| 创建视图

考虑这样一张表:

让我们创建一个视图来显示每个员工的 FirstName 和 Salary。

CREATE VIEW List ASSELECT FirstName, SalaryFROM  Employees;

现在，您可以像查询实际表一样查询列表视图。

SELECT * FROM List;

| 更新视图

CREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition;

下面的示例更新我们的列表视图及同时选择姓氏。

CREATE OR REPLACE VIEW List ASSELECT FirstName, LastName, SalaryFROM Employees;

| 0x08 写在最后

本节给大家介绍了SQL中的连接和表的操作，包括视图、增删改查等，可以收藏保存作为备忘录。勤能补拙是良训，希望大家还是动手做一做，有问题可以私信我，欢迎交流和提出您的宝贵意见。

文末推荐:

勘误:

由于我自己也不是资深编程高手，在创作此内容时尽管已经力求精准，查阅了诸多资料，还是难保有所疏漏，如果各位发现有误可以公众号内留言，欢迎指正。

你要偷偷学Python，然后惊艳所有人。