《SQL必知必会》内容汇总
|
241
|
0
|
技术

2282 字
|
20 分钟

站点对Markdown支持不是很好，复制过来的MD内容不是特别易读。就当个预览吧。附上Markdown文件和PDF，可以做本书的读书笔记对照看，方便记忆。

附件下载：SQL必知必会

检索数据

检索不同的值

结果去重，查询列的所有取值

SELECT DISTINCT xxx from xxx;

限制结果

取前五行

SQL Server: 

SELECT TOP 5 xxx from xxx;

MySQL: 

SELECT xxx from xxx LIMIT 5;

切片

SELECT xxx from xxx LIMIT 5 OFFSET 5;

或者再MySQL中使用快捷短语

SELECT xxx from xxx LIMIT 5,5;

返回从第5行起的5行数据。第一个数字是开始位置，第二个数字是检索行数

[注]：角标从第0行开始

使用注释

行内注释

SELECT xxx -- 这是一行注释
from xxx;

整行注释

# 这是一行注释
SELECT xxx from xxx;

多行注释

/* SELECT xxx
FROM xxx; */
SELECT xxx from xxx;

排序

单列排序

按照prod_name的字母顺序排序

SELECT prod_name
FROM Product
ORDER BY prod_name;

[注]：确保ORDER BY 子句是最后一行，否则会报错

多行排序

比如员工表，需要按照姓和名排序，如果同姓，则在同姓之间再按照名排序。

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

[注]： 仅在多行prod_price相同的时候再按照prod_name排序。

按列位置排序

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

ORDER BY 2，3 表示先按prod_price ，再按prod_name 进行排序。

指定方向排序

默认排序方式是从A-Z，使用DESC关键字可以降序排序。

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

如果打算选出最贵的，再加上产品名

SELECT prod_id, prod_prices, prod_name
FROM Products
ORDER BY prod_prices DESC, prod_names;

过滤数据

使用where语句

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
操作符 说明
!= 不等于
BETWEEN x AND y 在指定2个值之间
IS NULL 为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’;

求值顺序

使用( )确定OR AND操作顺序

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

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

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

NOT操作符

WHERE 子句中的NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

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

使用通配符进行过滤

LIKE操作符

%通配符

%表示任何字符出现任意次数。

例如，为了找出所有以词Fish 起头的产品，可发布以下SELECT 语句：

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

_通配符

和%不同之处在于，_只匹配一次。

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

输出▼

prod_id      prod_name
--------     --------------------
BR02         12 inch teddy bear
BR03         18 inch teddy bear

分析▼
这个WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配12 ，第二行中匹配18 。8 inch teddy bear 产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。

[ ]通配符

方括号（[] ）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询： 

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

此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配以J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）： 

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

创建计算字段

拼接字段

SELECT vend_name || '(' || vend_country || ')'
FROM Venders
ORDER BY vend_name

输出▼
———————————————————–

Bear Emporium         (USA )
Bears R Us            (USA )
Doll House Inc.       (USA )
Fun and Games     (England )
Furball Inc.          (USA )
Jouets et ours     (France )

如果使用MySQL，则使用以下语法

SELECT Concat(vend_name, '(', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

以上输出包含空格，如果不需要这些空格，可以使用RTRIM()函数。

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

输出▼
———————————————————–

Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

[注]：RTRIM() 删除字符串右边的所有空格。LTRIM()删除左边空格，TRIM()删除两侧空格

使用别名

前面的SELECT语句很好地拼接了字段，但是仅仅是输出，不能通过SELECT查询。

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

如果使用MySQL，则使用：

SELECT Concat(RTRIM(vend_name), '(', RTRIM(vend_country), ')') AS vend_title
FROM Venders
ORDER BY vend_name;

执行算数计算

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

使用函数

文本处理函数

函数 说明
LENGTH() 返回字符串长度
LOWER()/UPPER() 大小写转换
SUBSTR() 字符串切片

SOUNDEX()

SOUNDEX()是将任何文本串转换为描述其语音的算法。SOUNDEX()考虑了类似的发音。

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

日期和时间处理函数

SELECT order_num
FROM orders
WHERE YEAR(order_date) = 2020

汇总数据

汇聚函数

AVG()

[注]：AVG()只作用单行，且忽略值为NULL的行。

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

输出▼

avg_price
-------------
3.8650

COUNT()

  1. COUNT(*)不会忽略NULL，默认不统计NULL
  2. 使用COUNT(Column)对特定列有取值的行数计数，忽略NULL
SELECT COUNT(*) AS num_cust
FROM Customers;

如果只统计填写电子邮件的客户：

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

MAX()

如果应用于字符串列，返回排序后的最后一行。

组合聚集函数

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;

分组数据

创建分组

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

image-20211204201147416

过滤分组

[注]：所有的where都可以用having替代

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

image-20211204201355350

分析

  1. 最后一行HAVING语句过滤了2个订单以上的分组。
  2. 这种情况下where不起作用，因为where只对行生效，having对分组进行过滤。
  3. where在分组前进行过滤，having在分组后进行过滤。

子查询

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

[注]：作为子查询的SELECT语句只能查询单个列。

联结

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

image-20211204214734314

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
    AND OrderItems.prod_id = Products.prod_id
    AND order_num = 20007;

image-20211204215205290

创建高级联结

外联结

左外联结：以左表为基础，显示所有左表的行，对右表的行只显示符合条件的行。

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

组合查询

UNION操作符

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默认去重，如果需要返回所有匹配行，包含重复的，需要使用UNION ALL

插入数据

插入完整的行

INSERT INTO customers
VALUES(1,
      'string',
      'CA',
      NULL);

这种方法简单但是不安全，如果使用更安全的方式，可以使用以下较为繁琐的方法

插入部分行

正如所述，使用 INSERT 的推荐方法是明确给出表的列名。使用这种语法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。 

INSERT INTO customers(cust_id,
                     cust_name,
                     cust_address,
                     cust_city)
VALUES(1,
      'string',
      NULL,
      'CA');

插入检索出的数据

INSERT INTO customers(cust_id,
                     cust_contact,
                     cust_email,
                     cust_name)
SELECT cust_id, cust_contact, cust_email, cust_name
FROM CustNew;

[注]：INSERT只插入一行，INSERT SELECT可以插入多行

从一个表复制到另一个表

CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新和删除数据

UPDATE操作符

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
    cust_contact = 'Kim'
WHERE cust_id = 10000000005;

DELETE语句

DELETE FROM Customers
WHERE cust_id = 1000000006;

注意事项

  1. DELETE删除整行，FROM可以根据DBMS省略。
  2. 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  3. 在UPDATE和DELETE前使用SELECT进行测试，保证过滤的是正确的记录。
  4. SQL没有undo按钮，应该非常小心的使用UPDATE和DELETE。

创建和操纵表

表创建基础

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 OrderItems
(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL PRIMARY KEY, -- 定义主键
    quantity INTEGER NOT NULL DEFAULT 1,
    item_price DECIMAL(8,2) NOT NULL
);

更新表

增加新列

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除列

ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除整个表

DROP TABLE CustCopy;

使用视图

为什么使用视图

  1. 重用SQL语句
  2. 简化复杂的SQL操作。在编写查询后，可以方便的重用而不必知道其细节。
  3. 使用表的一部分而不是整个表。
  4. 保护数据，可以授予用户访问表的特定部分的权限，而非整个表的访问权限。

创建视图

使用视图简化联结查询

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;

[注]：删除视图可以使用DROP VIEW viewname 

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

用视图重新格式化检索出的数据

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')'
       AS vend_title
FROM Vendors;
SELECT * FROM VendorLocations;

高级SQL特性

约束

主键

  1. 任意两行值不相同。

  2. 每行都有一个主键值（即不允许NULL)。

  3. 包含主键值的列从不修改或更新。

  4. 主键值不能重用，如果删掉某一行，其主键值不会重新分配给新行。

    CREATE TABLE Vendors
(
   vend_id CHAR(10) NOT NULL PRIMARY KEY,
   vend_name CHAR(50) NOT NULL,
   vend_address CHAR(50) NULL
);

外键

外键是表中的一列，其值必须列在另一表中的主键中。

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

[分析]：其中的表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表的cust_id值。

唯一约束

唯一约束用来保证一列中的数据是唯一的。它们类似主键，但是有以下区别：

  1. 唯一约束列可以包含NULL值。
  2. 唯一约束列可以修改或更新。
  3. 唯一约束列的值可重复使用。
  4. 唯一约束列不能用来定义外键。

使用UNIQUE约束

检查约束

CREATE TABLE OrderItems
(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    item_price MONEY NOT NULL
);
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇