《SQL必知必会》万字精华-第1到7章

尤而小屋
283 阅读12分钟

《SQL必知必会》万字精华

本文是《SQL必知必会》一书的精华总结，帮助读者快速入门SQL或者MySQL，主要内容包含：

  • 数据库基础知识
  • 库表的相关操作
  • 检索数据的方法

思维导图

下面的思维导图中记录了这本书的整体目录结构，包含内容有：

  • 数据的检索
  • 汇总数据
  • 分组数据
  • …….

一、了解SQL

本章中主要是介绍了数据库和SQL相关的基本知识和术语。

数据库

数据库是一个以某种有组织的方式存储的数据集合。数据库是一个保存有组织的数据容器，通常是一个文件或者一组文件

表

表示一种结构化的文件，可以用来存储某种特定的数据类型。表是某种特定类型数据的结构化清单。

存储在表中的数据是同一种类型的数据或者清单

数据库中的每个表都有自己的名字，并且这个表是唯一的。

列

表是由列组成的，列存储表中某部分的信息。列是表中的某个字段。所有的表都是由一个或者多个列组成的。

数据库中的每个列都应该是具有的相同数据类型datatype。数据类型定义了列可以存储哪些数据类型。

行

表中的数据是按照行来进行存储的，所保存的每个记录存储在自己的行内。如果把表想象成一个网格，那么网格中垂直的列则为表列，水平则为表行。

行表示的是一个记录。行有时候也称之为记录。

主键

表中每一行都应该都有一列或者几列来唯一标识自己。主键用来表示一个特定的行。

主键：一列或者几列，其值能够标识表中每行。

如果表中的列可以作为主键，则它必须满足：

  • 任意两行都不具有相同的主键值（主键列不允许NULL值）
  • 每行都必须有一个主键值
  • 主键列中的值不允许修改或者更新
  • 主键值不能重用（如果某行从表中删除，则它的主键不能赋给以后的行记录）

什么是SQL

首先我们看一段来自百度百度的解释：

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL是一种专门和数据库沟通的语言

SQL特点

1、SQL不是某个特定数据库供应商专有的语言，几乎所有的DBMS都是支持SQL

2、SQL简单易学。它的语句都是由简单的、具有描述性的英文单词组成的

3、SQL虽然简单，但是实际上是一种很强有力的语言，灵活使用去语言元素，可以进行复杂和高级的数据库操作

二、检索数据

本章中介绍的是如何使用select语句从表中检索一个或者多个数据列。

每个SQL语句多有一个或者多个关键字组成，最经常使用的就是select关键字。它的用途是从一个或者多个表中检索出来信息。为了使用select检索数据，必须至少给出两个信息：

  • 检索什么（字段，列）
  • 从哪里检索（表）

检索单个列

SELECT prod_name   -- 检索什么
FROM Products;  -- 从哪里检索

下面关于SQL的语句做几点笔记：

  1. 多条SQL语句必须是以分号;结尾；如果是单条不加分号也是可以的，但是最好加上
  2. SQL语句不区分大小写，即：SELECT和select是相同的。但是一般规范的写法是：SQL关键字进行大写，列名和表名是小写的
  3. 在处理SQL语句的时候，其中所有的空格都是忽略的；但是分行写，语句更清晰，更好理解
-- 写法1
SELECT prod_name   -- 检索什么
FROM Products;  -- 从哪里检索

-- 写法2
SELECT prod_name FROM Products;  

-- 写法3
SELECT 
prod_name 
FROM 
Products; 

-- 写法：个人习惯写法
SELECT 
	prod_name 
FROM Products;

检索多个列

SELECT pro_id,prod_name,prod_price
FROM Products;

-- 个人写法
SELECT 
	pro_id,
	prod_name,
	prod_price
FROM Products;

检索所有列

SELECT *  -- *代表所有列
FROM Products;

检索不同的值

SELECT DISTINCT vend_id  
FROM products;

  • SQL中的DISTINCT关键字表示的是去重，只返回不同的值。它必须放在列的前面。

  • 不能部分使用DISTINCT关键字。它是作用于所有的列

SELECT DISTINCT vend_id, prod_price  -- DISTINCT作用于所有的列，并不仅仅是后面的列

限制结果

如果不加限制条件，SQL返回的是全部数据。如果我们想只显示部分数据，该如何实现？

1、SQL Server 和 Access

SELECT TOP 5 prod_name  -- 最多返回5行
FROM Products;

2、DB2

SELECT prod_name
FROM Products 
FETCH FIRST 5 ROWS ONLY;   -- 字面意思：显示前5行

3、Oracle

SELECT prod_name
FROM Products 
WHERE ROWNUM <= 5;

4、MySQL、MariaDB、PostgreSQL、SQLite

使用关键字limit

SELECT prod_name
FROM Products 
LIMIT 5;   -- 使用LIMIT5

关于LIMIT的笔记：

SELECT prod_name
FROM Products 
LIMIT 4 OFFSET 5;  -- 第5行开始显示4行数据

-- 简化版本
SELECT prod_name
FROM Products 
LIMIT 5,4  -- 效果同上
  • 第一个数字表示显示多少行数据
  • 第二个数字表示从哪里开始显示

SQL注释问题

SQL中的注释分为两种：单行注释和多行注释

单行注释使用—符号，后面跟上注释的内容：

SELECT prod_name  -- 这里是一条注释，你可以写点注释
FROM Products 
LIMIT 4 OFFSET 5;

多行注释使用一对/*，符号之间的内容就是注释：

/*
注释1：SQL语句的作用是什么
注释2：SQL语句谁在什么时候写的
*/
SELECT prod_name  
FROM Products 
LIMIT 4 OFFSET 5;

三、排序检索数据

排序数据（单个列）

本节中介绍的是如何利用order by子句来对select检索的结果进行排序。为了明确地排序用select语句检索出来的数据，可使用order by子句取一个或者多个列的名字，来对输出结果进行排序。

  • 使用关键词order by
  • 排序的结果默认是升序ASC，降序是DESC
SELECT prod_name
FROM Products 
ORDER BY prod_name;  -- 根据产品名称的字母进行排序

笔记：我们需要注意order by子句的位置，一定要保证它是select语句的最后一条子句。如果它不是最后的子句，那么就会报错。

按多个列排序

在实际的需求中，我们经常会遇到根据多个列进行排序。比如根据员工的姓名排序，如果姓相同，再根据名字进行排序。

要按多个列进行排序，指定列名即可，列名之间使用逗号隔开。

SELECT prod_id,prod_price,prod_name   -- 选择3个列
FROM Products
ORDER BY prod_price,prod_name;  -- 先根据价格排序，如果有相同的价格再根据姓名排序

笔记：只有当prod_price有相同的值，才会根据prod_name进行排序

按列位置进行排序

除了可以使用列名指出排序顺序外，order by还支持使用相对位置进行排序。

SELECT 
	prod_id,
	prod_price,
	prod_name   -- 选择3个列
FROM Products
ORDER BY 2,3;     -- 2,3就是相对位置
  • 2表示的是第2个列（prod_price）
  • 3表示的是第3个列名（prod_name）

如果想在多个列上进行降序排列，则对每个列都要指定DESC关键词：

-- 正确写法
SELECT 
	prod_id,
	prod_price,
	prod_name   
FROM Products
ORDER BY prod_price DESC, prod_name DESC;  -- 每个列都指定DESC

-- 错误写法！！！
SELECT 
	prod_id,
	prod_price,
	prod_name   
FROM Products
ORDER BY prod_price, prod_name DESC;  -- DESC只对最近的prod_name起作用，那么prod_price仍然是升序排列的

四、过滤数据

本节中讲解的是使用where关键词来过滤数据。数据库中一般存在大量的数据，一般我们只需要检索表中少量的行。只检索所需数据需要指定搜索条件，搜索条件也称之为过滤条件。

使用where子句

SELECT prod_name, prod_price
FROM Products
WHERE prod_price=5;  -- 指定条件

笔记：当ORDER BY 和WHERE子句同时存在的时候，ORDER BY子句应该位于WHERE子句之后。

WHERE子句操作符

常用的where子句操作符：

操作符说明操作符说明
=等于大于
<>不等于>=大于等于
!=不等于!>不大于
<小于BETWEEN…AND...在指定的两个值之间
大于IS NULL为NULL值
!<不小于

注：上面表格中的某个操作符是等价的。

检查单个值

SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 5;  -- 指定1个条件

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id <> 'DLL01'   -- 指定不等于条件

笔记：

1、上面where子句中，可以看到有的只会在单引号内，但是有的没有括起来。

2、单引号用来限定字符串。如果将值和字符串类型的比较，需要使用限定符号

3、用来与数值列进行比较的值，则不用括号。

不匹配检查

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id <> 'DLL01'  -- 不匹配检查条件

范围值检查

要检查某个范围的值，可以使用BETWEEN操作符。BETWEEN操作符要搭配AND同时使用，指定范围的最大值和最小值：

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 100 AND 600    -- BETWEEN ... AND...联合使用

空值检查

当我们创建表的时候，可以指定其中的列是否包含空值。在一个列不包含值时，称其包含空值NULL。

注：NULL（无值，no value），它和字段包含0、空字符串或仅仅包含空格是不同的。

SELECT中一个特殊的HERE子句用来检查具有NULL值的列：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price IS NULL;   -- 找出价格为NULL的数据

五、高级数据过滤

本节中介绍的是如何组合WHERE子句以建立功能更强、更高级的搜索条件

组合WHERE子句

操作符operator：用来联结或改变WHERE子句中的子句的关键字，也称之为逻辑操作符logical operator。

AND操作符

同时满足AND操作符两边的条件

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' AND prod_price <= 20;  -- AND操作符指定2个条件
OR操作符

满足OR操作符两边的一个条件即可

SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' OR prod_price <= 20;  -- AND操作符指定2个条件

注：当第一个条件满足的时候，第二个条件便不会执行了。

AND和OR联用

在WHERE子句中同时使用AND和OR操作符：

⚠️：AND操作符的优先级是高于OR操作符

⚠️：AND操作符的优先级是高于OR操作符

⚠️：AND操作符的优先级是高于OR操作符

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

上面的语句原本表达的含义是先挑选满足两个vend_id的条件；但是SQL在执行的时候，AND操作符先处理。如果我们想先执行AND前面的部分，可以加上括号：

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

括号具有比AND或者OR更高的优先级，能够消除歧义。

IN操作符

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

SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')   -- 功能类似于OR操作符
ORDER BY name：

笔记：IN操作符的主要优点

1、IN操作符的语法更清楚、更直观

2、使用了IN操作符更容易管理求值顺序

3、IN操作符一般比OR操作符执行的更快

4、在IN操作符中可以包含其他SELECT子句，能够动态地建立where子句

NOT操作符

NOT操作符只有一个功能：就是否定后面所跟的任何条件。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'   -- 找出不是DLLO1名字的数据
ORDER BY prod_name

上面的语句的功能也可以用<>来实现：

SELECT prod_name
FROM Products
WHERE vend_id <> 'DLLO1'   -- 不等于，效果同上
ORDER BY prod_name

六、使用通配符进行过滤

什么是通配符

通配符wildcard是用来匹配值的一部分的特殊字符；利用通配符，可以创建比较特定数据的搜索模式。

笔记：由字面值、通配符或者两者组合构成的搜索条件。

为了在搜索子句中使用通配符，必须使用LIKE操作符

⚠️通配符搜索只能用于文本字段（字符串），对于非文本数据类型不能使用通配符搜索。

百分号%

功能：匹配任意字符出现的任意次数，即任意内容

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "Fish%"  -- 找出所有Fish开头的产品，不管后面是什么内容

通配符可以在任意位置使用，可以使用多次：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "%bean bag%"   -- 匹配名字中包含bean bag的任意名字的数据，不管前后

通配符出现在中间：

SELECT prod_name
FROM Products 
WHERE prod_name LIKE 'F%y'   -- 找出F开头y结尾的数据

⚠️：百分号%能够匹配任意位置的0个、1个或者多个字符，但是不能匹配NULL

下划线_

下划线通配符的用途和百分号类似，但是它只能匹配一个字符，百分号是匹配多个字符，这是二者的区别。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear'   --  前面是两个下划线_

-- 结果
prod_id           prod_name
-------           ---------
BR02              12 inch teddy bear   -- 12或者18刚好是两个字符
BR03              18 inch teddy bear

一个下划线刚好只能匹配一个字符，不能多也不能少

方括号[]

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

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'    -- 匹配JM当中一个字母开头，后面是任意字符的内容
ORDER BY cust_contact
  • [JM]：匹配其中一个字符
  • %：匹配任意内容

取反字符^

使用取反符号^来否定内容：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'   -- 匹配不是JM开头的任意内容
ORDER BY cust_contact;

使用NOT操作符可以得到类似上面的结果：

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'   -- 匹配不是JM开头的任意内容；NOT表示取反，否定内容
ORDER BY cust_contact;

通配符使用技巧

⚠️通配符使用的几点注意事项：

  1. 不要过度使用通配符
  2. 如果确实需要使用通配符，尽可能不要将它们放在搜索模式的开始位置。通配符处于开始处，搜索是最慢的。
  3. 仔细注意通配符的位置。如果放错地方，可能得不到我们想要的结果。
  4. 通配符应该要细心使用，不要过度使用。

七、创建计算字段

计算字段

存储在数据库表中的字段一般不是应用程序中所需要的格式。我们需要直接从数据库中检索出来进行转换、计算或者格式化过的数据。计算字段并不实际存在于数据库表中。计算字段是运行时在select语句内创建的。

拼接字段

将多个字段联结在一起构成单个字段。根据不同的DBMS，使用+或者||来进行联结。但是在MySQL中必须使用特殊的函数来进行操作。

拼接字段函数Concat的使用：

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

代码解释：

  • 存在列vend_name列中的名字
  • 包含一个空格和一个左圆括号的字符串
  • 存在vend_country列中的国家
  • 包含一个右圆括号的字符串

小知识：MySQL中如何去掉空格？

RTRIM(col)  -- 去掉值右边的所有空格
LTRIM(col)  -- 去掉值左边的所有空格
TTRIM(col)  -- 去掉字符串左右两边的所有空格

使用别名

别名（alias）是一个字段或者值的替换明，别名是使用关键词AS来赋予的。

SELECT 
   Concat(vend_name, ' (', vend_country, ')')  AS vend_title    -- 使用别名
FROM Vendors
ORDER BY vend_name;

笔记：AS关键词语是可选的，但是最好使用

执行算术运算

计算字段的另一个常见运算是对检索出来的数据进行算术运算。

SELECT
	prod_id
	,quantity
	,item_price
	,quantity * item_price AS expanded_price   -- 计算字段执行算术运算，并使用别名
FROM OrderItems
WHERE order_num = 2008;

SQL算术操作符

SQL中支持的算术操作符：

操作符说明
+加
-减
*乘
/除

使用函数处理数据

常用函数

与其他计算机语言一样，SQL中也提供函数来处理数据。

  • 用于处理文本字符串：删除或填充值、转换值或者大小写转化
  • 用于在数值数据上进行算术操作：返回绝对值、代数运算等
  • 用于处理日期和时间，并从中提取出特定成分的日期和时间函数等
  • 返回DBMS正使用的特殊信息的系统函数

文本处理函数

函数说明
LEFT()返回字符串左边的字符
LENGTH()返回字符串的长度
LOWER()将字符串转换为小写
LTRIM()去掉值左边的所有空格
RIGHT()返回字符串右边的字符
RTRIM()去掉值右边的所有空格
SOUNDEX()返回字符串的SOUNDEX值
UPPER()将字符串转换为大写

SOUNDEX是一个将任何文本串转成描述其语音表示的字母数字模式的算法。

SELECT
  cust_name
  ,cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')   -- 转化成对应的值

日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，以特殊的格式来存储。

SELECT  
	order_num
FROM Orders
WHERE YEAR(order_date) = 2012;   -- 提取年份

数值处理函数

MySQL中常用的数值处理函数：

函数说明
ABS()返回一个数的绝对值
COS()返回一个角度的余弦值
EXP()返回一个数的指数值
PI()返回圆周率
SIN()返回一个角度的正弦
SQRT()返回一个数的平方根
TAN()返回一个角度的正切值

汇总数据

聚集函数

avatar
文章
阅读
粉丝