SQL学习笔记之连接、表操作详解

表连接

表连接

在SQL中，“表连接”表示组合来自两个或多个表的数据。表连接创建一个临时表，显示连接表中的数据。

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

SQL 语句如下所示：

SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID;

注：WHERE 子句 “连接” 表，条件是来自 customers 表的 ID 应等于 orders 表的 customer_ID。

连接类型

自定义别名

通过使用 SQL，可以为表名称或列名称指定别名。

基本上，创建别名是为了让列名称的可读性更强。

例：

SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;

连接类型

以下是可以在 SQL 中使用的连接类型：

- 内连接（INNER JOIN）

- 左连接（LEFT JOIN）

- 右连接 （RIGHT JOIN）

内连接（INNER JOIN）

SQL INNER JOIN 语法：

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

注：

- INNER JOIN 与 JOIN是相同的。

- ON 用于指定内部连接条件。

左连接（LEFT JOIN）

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。

如果右表中没有匹配，则结果为 NULL。

SQL LEFT JOIN 语法：

SELECT table1.column1, table2.column2...
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

注：在某些数据库中，LEFT JOIN 等价于 LEFT OUTER JOIN。

右连接（RIGHT JOIN）

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。

如果左表中没有匹配，则结果为 NULL。

SQL RIGHT JOIN语法：

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

注：在某些数据库中，RIGHT JOIN 等价于 RIGHT OUTER JOIN。

UNION 操作符

设置操作符

UNION 将多个数据集合并到单个数据集中，并删除任何现有的重复项。 UNION ALL 将多个数据集合并到一个数据集中，但不会删除重复的行。

UNION 操作符

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

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注：

- UNION 内部的每个 SELECT 语句必须拥有相同数量的列。

- 列也必须拥有相似的数据类型。

- 同时，每个 SELECT 语句中的列的顺序必须相同。

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

例：

SELECT FirstName, LastName, Company FROM table3
UNION
SELECT FirstName, LastName, NULL FROM table4;

UNION ALL 操作符

UNION ALL 从每个表中选择所有行，并将它们合并成一个表中。

例：使用 UNION ALL 从 “table1” 和 “table2” 表中选取所有的记录（也有重复的值）：

SELECT ID, FirstName, LastName, City FROM table1
UNION ALL
SELECT ID, FirstName, LastName, City FROM table2;

INSERT 语句

插入数据

INSERT INTO 语句用于向数据库中的表添加新的数据行。

SQL INSERT INTO 语法如下：

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

注：

- 确保值的顺序与表中的列的顺序相同。

- 必须为没有默认值或不支持 NULL 的每个列提供一个值。

UPDATE 和 DELETE 语句

UPDATE 语句

UPDATE 语句用于更新表中的记录。

SQL UPDATE 的语法：

UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE condition;

SET 关键字之后的逗号，用于分隔列表中指定列及其新值。

例：我们要更新员工表中“John”（ID=1） 的工资为6000，则 SQL 语句为：

UPDATE Employees 
SET Salary=6000
WHERE ID=1;

注：WHERE 子句规定哪条记录或者哪些记录需要更新。如果你省略了 WHERE 子句，所有的记录都将被更新！

更新多列

在更新数据时，我们也可以通过逗号分隔来同时更新多个列。

例：

UPDATE Employees 
SET Salary=5500, FirstName='Robert'
WHERE ID=1;

DELETE 语句

DELETE 语句用于删除表中的记录。

SQL DELETE 的语法：

DELETE FROM table_name
WHERE condition;

例：我们要从 “Employees” 表中删除 ID 为 “1” 的员工：

DELETE FROM Employees
WHERE ID=1;

注：WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

CREATE TABLE

SQL 表

SQL 表由表行和列组成。表列负责存储许多不同类型的数据，包括数字，文本，日期和甚至文件。 CREATE TABLE 语句用于创建数据库中的表。 创建基本表包括命名表，并定义其列和每列的数据类型。

CREATE TABLE

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_name 参数规定表中列的名称。 data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。 size 参数规定表中列的最大长度。

例：创建一个名为 “users” 的表，包含四个列：UserID，FirstName，LastName 和 City，则：

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

UserID 列的数据类型是 int，包含整数。 FirstName、LastName和 City 列的数据类型是 varchar，包含字符，且这些字段的最大长度为 255 个字符。

数据类型

最常见的数据类型：

Number 类型：

INT(size)：-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 FLOAT(size,d)：带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 DOUBLE(size,d)：带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。

Date 类型：

DATE()：日期。格式：YYYY-MM-DD。 DATETIME()：*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS。 TIMESTAMP()：*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS。 TIME()：时间。格式：HH:MM:SS。

String 类型：

VARCHAR(size)：保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 TEXT：存放最大长度为 65,535 个字符的字符串。

注：SQL 开发人员必须在创建 SQL 表时决定表中的每个列将要存储的数据的类型。

创建主键

每个表最好选择一个唯一非空的字段作为主键。

例：下面的 SQL 在 “users” 表创建时，使用 PRIMARY KEY 关键字将 “UserId” 列定义为主键：

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

NOT NULL 和 AUTO_INCREMENT

SQL 约束

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

在 SQL 中，我们有如下约束：

- NOT NULL ：指示某列不能存储 NULL 值。

- UNIQUE ： 保证某列的每行必须有唯一的值。

- PRIMARY KEY ：等价于 NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

- FOREIGN KEY ： 保证一个表中的数据匹配另一个表中的值的参照完整性。

- CHECK ： 保证列中的值符合指定的条件。

- DEFAULT ： 规定没有给列赋值时的默认值。

例：以下表示 “name” 列不允许 NULL 值。

name varchar(100) NOT NULL  

注：约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

自动递增（AUTO INCREMENT）

AUTO INCREMENT 会在新记录插入表中时生成一个唯一的数字。

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

例：我们把 “UserID” 列定义为 auto-increment 主键字段：

UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID)

使用约束创建表

例：强制 “id”，“username” 和 “password” 列不接受NULL值，还将 “id” 列定义为 auto-increment 主键字段：

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

ALTER, DROP, RENAME TABLE

ALTER TABLE

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。 你还可以使用 ALTER TABLE 命令在现有表上添加和删除各种约束。

例：在 “people” 表中添加一个名为 “Birthdy” 的列：

ALTER TABLE people 
ADD Birthdy date;

DROP TABLE

例：在 people 表中删除名为 “Birthday” 的列：

ALTER TABLE people 
DROP COLUMN Birthday;

若要删除整个表，则用DROP TABLE 语句：

DROP TABLE people;

RENAME TABLE

例：将名为 “FirstName” 的==列重命名==为 “name”：

ALTER TABLE people
CHANGE FirstName name varchar(55);

例：将 “people” ==表重命名==为 “users”：

RENAME TABLE people TO users;

SQL 视图

视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。 你可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

SQL CREATE VIEW 语法：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

创建视图

例：创建一个视图，显示每个员工的 “FirstName” 和 “Salary”：

CREATE VIEW List AS
SELECT FirstName, Salary
FROM  Employees;

更新视图

你可以使用下面的语法来更新视图：

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

例：向 “List” 视图添加 “LastName” 列：

CREATE OR REPLACE VIEW List AS
SELECT FirstName, LastName, Salary
FROM  Employees;

注：通过 DROP VIEW 命令来删除视图：

DROP VIEW List;