SQL基础语法笔记

2020-12-22 本文已影响0人火炬广场老大爷

1、数据检索

选择单列

从Products中选出prod_name列

SELECT prod_name
FROM Products;

选择多个列

SELECT prod_id, prod_name, prod_price
FROM Products;

选择所有列

SELECT *
FROM Products;

选择前五行

SELECT TOP 5 prod_name
FROM Products;

选择从第5行开始的5行数据

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

2、排序检索

排序数据

SELECT prod_name
FROM Products
ORDER BY prod_name;

提取prod-name，并且用它来排序。

按照多个列排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

按照列位置排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

指定排序方向
降序：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

只降第一个：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

所有的都降
如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

3、过滤数据

where子句

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于WHERE 之后，否则将会产生错误。一般来说，order by一般都放在最后。

where子句的操作符

be469e4d2f0882f0d8ef210254a0512.png

列出所有价格小于 10 美元的产品

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;

检索所有价格小于等于 10 美元的产品

SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;

列出所有不是供应商 DLL01 制造的产品：

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

观察上述 WHERE 子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

使用 BETWEEN 操作符，它检索价格在 5 美元和 10美元之间的所有产品:

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

空值检查

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

组合where子句
AND

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR操作符

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;

IN字符
IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取一组由逗号分隔、括在圆括号中的合法值。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

此 SELECT 语句检索由供应商 DLL01 和 BRS01 制造的所有产品。 IN 操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。 IN 操作符完成了与 OR 相同的功能！

NOT字符

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

这里的 NOT 否定跟在其后的条件，因此， DBMS 不是匹配 vend_id 为DLL01，而是匹配非 DLL01 之外的所有东西。

通配符过滤
找出所有以词 Fish 起头的产品：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

如果使用的是 Microsoft Access，需要使用*而不是%。

可以使用多个通配符

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地址的一部分来查找电子邮件，例如

WHERE email LIKE 'b%@forta.com'。

下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

如果使用的是 Microsoft Access，需要使用?而不是_。
方括号（ []）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

这一搜索模式使用了两个不
同的通配符。 [JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。 [JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
注意：
不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

4、创建计算字段

拼接

拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。
我们来举一个简单例子，创建由两列组成的标题。
Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。此报表需要一个值，而表中数据存储在两个列 vend_name 和 vend_country 中。此外，需要用括号将 vend_country 括起来，这些东西都没有存储在数据库表中。
解决办法是把两个列拼接起来。在 SQL 中的 SELECT 语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS，此操作符可用加号（ +）或两个竖杠（ ||）表示。

SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

有的时候可能带来空格，所以需要去掉空格，可以写成这个样子：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

RTRIM()函数去掉值右边的所有空格。通过使用 RTRIM()，各个列都进行了整理。
顺便给这些新搞的数据取一个新名字：

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

算术计算

Orders 表包含收到的所有订单， OrderItems 表包含每个订单中的各项物品。下面的 SQL 语句检索订单号 20008 中的所有物品：

SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;

item_price 列包含订单中每项物品的单价。如下汇总物品的总价格

SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

5、使用函数进行计算

与 SQL 语句不一样， SQL 函数不是可移植的。这意味着为特定 SQL 实现编写的代码在其他实现中可能不正常。

文本处理函数

1.png

SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念，但多数DBMS 都提供对 SOUNDEX的支持。

时间处理函数

不同 DBMS 的日期时间处理函数可能不同。关于具体 DBMS 支持的日期时间处理函数，请参阅相应的文档。

数值处理函数

2.png

6、汇总数据

聚集函数

常用的聚集函数

3.png

举例：

SELECT AVG(prod_price) AS avg_price
FROM Products;

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

第二条前一条的不同之处在于，它包含了 WHERE 子句。此WHERE 子句仅过滤出 vend_id 为 DLL01 的产品，因此 avg_price 中返回的值只是该供应商产品的平均值。
使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（ NULL）还是非空值。使用COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值。
MAX()返回指定列中的最大值。 MAX()要求指定列名。

聚集不同值

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

只包含不同的值，指定 DISTINCT 参数。它与前面的 SELECT 语句相同，但使用了 DISTINCT 参数，因此平均值只考虑各个不同的价格。

组合聚集函数

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;

7、数据分组

分组

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

上面的 SELECT 语句指定了两个列： vend_id 包含产品供应商的 ID，num_prods 为计算字段（用 COUNT(*)函数建立）。 GROUP BY 子句指示DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到，供应商 BRS01 有 3 个产品，供应商 DLL01 有 4 个产品，而供应商 FNG01 有 2 个产品。
因为使用了 GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。 GROUP BY 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

select子句顺序

1.png

8、子查询

其实就是嵌套，完毕。

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

9、联结

SQL 最强大的功能之一就是能在数据查询的执行中联结（ join）表。

创建联结关系

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

目前为止使用的联结称为等值联结（ equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（ inner join）。

给表格改名

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

可以看到， FROM 子句中的三个表全都有别名。 Customers AS C 使用 C作为 Customers 的别名，如此等等。

不同类型联结的区别
自联结

假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');

这就是自联结，联结自己。自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。

外联结

要检索包括没有订单顾客在内的所有顾客，可如下进行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

这条 SELECT 语句使用了关键字 OUTER JOIN来指定联结类型（而不是在 WHERE 子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTERJOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（ Customers 表）中选择所有行。为了从右边的表中选择所有行，需要使用 RIGHT OUTER JOIN，如下例所示：

SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;

使用带集聚函数的联结

要检索所有顾客及每个顾客所下的订单数

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

也可以这样：

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

10、组合查询

两次查询一起进行

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION分隔（因此，如果组合四条 SELECT语句，将要使用三个 UNION关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。
这是 UNION 的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。

11、数据插入

insert

INSERT 用来将行插入（或添加）到数据库表。插入有几种方式：
 插入完整的行；
 插入行的一部分；
 插入某些查询的结果。

INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值，如上面的 cust_contact 和 cust_email 列，则应该使用 NULL 值（假定
表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充。
编写 INSERT 语句的更安全（不过更烦琐）的方法如下：

INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);

这个例子与前一个 INSERT 语句的工作完全相同，但在表名后的括号里明确给出了列名。在插入行时， DBMS 将用 VALUES 列表中的相应值填入列表中的对应项。 VALUES 中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。
下面的 INSERT 语句填充所有列（与前面的一样），但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确（因为指定了列名称，甚至可以只插入部分列，你指定哪个列就插入哪个列）：

INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip)
VALUES('1000000006',
NULL,
NULL,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111');

INSERT 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条 INSERT 语句和一条 SELECT语句组成的。假如想把另一表中的顾客列合并到 Customers 表中。不需要每次读取一行再将它用 INSERT 插入，可以如下进行：

INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

这个例子使用 INSERT SELECT 从 CustNew 中将所有数据导入Customers。 SELECT 语句从 CustNew 检索出要插入的值，而不是列出它们。 SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。这条语句将插入多少行呢？这依赖于 CustNew 表有多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果这个表确实有数据，则所有数据将被插入到 Customers。

从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用 SELECT INTO 语句。
与 INSERT SELECT 将数据添加到一个已经存在的表不同， SELECT INTO将数据复制到一个新表（有的 DBMS 可以覆盖已经存在的表，这依赖于所使用的具体 DBMS）。

SELECT *
INTO CustCopy
FROM Customers;

这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表的整个内容复制到新表中。因为这里使用的是 SELECT ，所以将在CustCopy 表中创建（并填充）与 Customers 表的每一列相同的列。要想只复制部分的列，可以明确给出列名，而不是使用通配符。

12、数据更新与删除

更新数据

更新（修改）表中的数据，可以使用 UPDATE 语句。
基本的 UPDATE 语句由三部分组成，分别是：
 要更新的表；
 列名和它们的新值；
 确定要更新哪些行的过滤条件。
举一个简单例子。客户 1000000005 现在有了电子邮件地址，因此他的记录需要更新，语句如下：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

更新多个列的语法稍有不同：

UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

在更新多个列时，只需要使用一条 SET 命令。
要删除某个列的值，可设置它为 NULL（假如表定义允许 NULL 值）。如下进行：

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE的方式：
 从表中删除特定的行；
 从表中删除所有行。
从 Customers 表中删除一行：

DELETE FROM Customers
WHERE cust_id = '1000000006';

13、操纵表

创建表

利用 CREATE TABLE 创建表，必须给出下列信息：
 新表的名字，在关键字 CREATE TABLE 之后给出；
 表列的名字和定义，用逗号分隔；
 有的 DBMS 还要求指定表的位置。

CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);

CREATE TABLE Orders
(
order_num INTEGER NOT NULL,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL
);

这条语句创建本书中所用的 Orders 表。 Orders 包含三列：订单号、订单日期和顾客 ID。这三列都需要，因此每一列的定义都含有关键字 NOTNULL。这就会阻止插入没有值的列。如果插入没有值的列，将返回错误，且插入失败。
下一个例子将创建混合了 NULL 和 NOT NULL 列的表：

CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) ,
vend_city CHAR(50) ,
vend_state CHAR(5) ,
vend_zip CHAR(10) ,
vend_country CHAR(50)
);

SQL 允许指定默认值，在插入行时如果不给出值， DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
);

更新表

更新表定义，可以使用 ALTER TABLE 语句。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除表

DROP TABLE CustCopy;

创建视图

CREAT VIEW

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

SQL基础语法笔记

1、数据检索

选择单列

选择多个列

选择所有列

选择前五行

选择从第5行开始的5行数据

2、排序检索

排序数据

按照多个列排序

按照列位置排序

指定排序方向

3、过滤数据

where子句

where子句的操作符

组合where子句

通配符过滤

4、创建计算字段

拼接

算术计算

5、使用函数进行计算

文本处理函数

时间处理函数

数值处理函数

6、汇总数据

聚集函数

聚集不同值

组合聚集函数

7、数据分组

分组

过滤分组

select子句顺序

8、子查询

9、联结

创建联结关系

给表格改名

不同类型联结的区别

自联结

外联结

使用带集聚函数的联结

10、组合查询

11、数据插入

insert

从一个表复制到另一个表

12、数据更新与删除

更新数据

删除数据

13、操纵表

创建表

更新表

删除表

创建视图

猜你喜欢

热点阅读