欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-05 22:03:49
表连接 表连接 在SQL中,“表连接”表示组合来自两个或多个表的数据。表连接创建一个临时表,显示连接表中的数据。 要连接customers与 orde...

表连接

表连接

在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;