/

《SQL必知必会》学习笔记

数据库基本概念

数据库:保存有组织的数据的容器(通常是一个文件或一组文件)。

我们常说的数据库软件是“数据库管理系统(DBMS)”,而非数据库,数据库是通过 DBMS 创建和操纵的容器,而具体它究竟是什么,形式如何,各种数据库都不一样。

:某种特定类型数据的结构化清单。

模式:关于数据库和表的布局及特性的信息。包括表具有一些特性,这些特性定义了数据在表中如何存储,包含存储什么 样的数据,数据如何分解,各部分信息如何命名等信息。

(column):表中的一个字段。所有表都是由一个或多个列组成的。

数据类型:所允许的数据的类型。每个表列都有相应的数据类型,它限制(或允许)该列中存储的数据。

(row):表中的一个记录。

主键(primary key):一列(或一组列),其值能够唯一标识表中每一行。

提示:应当把主键作为每行必须项

主键条件:

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

SQL:Structured Query Lanuage (结构化查询语言),是一种专门用来与数据库沟通的语言。

关键字:作为 SQL 组成部分的保留字。关键字不能用作表或列的名字。

SQL 语句特点

  • 多条 SQL 语句必须以分号;分隔。多数 DBMS 不需要在单条 SQL 语句后加分号,但也有 DBMS 可能必须在单条 SQL 语句后加上分号。
  • SQL 语句不区分大小写,但多数人对 SQL 关键字大写,更易于阅读和调试。
  • 在处理 SQL 语句时,其中所有空格都被忽略。SQL 语句可以写成长长的一行,也可以分写在多行。多数 SQL 开发人员认为,将 SQL 语句分成多行更容易阅读和调试。

操作符:用来联结或改变WHERE字句中的字句的关键字,也称为逻辑操作符

检索数据

检索数据使用SELECT语句

检索单列

1
SELECT name FROM students;

检索多列

传入多个列名即可,列名之间用,分隔,但最后一个列名后面不可以加。

1
SELECT name,class_id,id FROM students;

检索所有列

使用通配符*

1
SELECT * FROM students;

检索不同的值

使用DISTINCT关键字

用于指示数据库只返回不同的值,即筛选掉重复行,将会应用于所有列

1
2
SELECT DISTINCT class_id
FROM students;

限制前 n 行结果

TOP关键字限制最多返回多少行

1
2
SELECT TOP 5 name
FROM students;

对于 MySQL、MariaDB、PostgreSQL 或者 SQLite,需使用LIMIT

1
2
3
SELECT name
FROM students
LIMIT 5;

对于 DB2 DBMS 的特定语句

1
2
3
SELECT name
FROM students
FETCH FIRST 5 ROWS ONLY;

对于 Oracle DBMS

1
2
3
SELECT name
FROM students
WHERE ROWNUM <=5;

限制第 m 行开始输出 n 行结果

使用OFFSET关键字调整检索位移

1
2
3
SELECT name
FROM students
LIMIT 5 OFFSET 3;

输出从第 3 行开始的 5 行数据

行数从第0行开始计算

简化版
MySQK 和 MariaDB 支持简化版语句,例如LIMIT 4 OFFSET 3,即为LIMIT 3,4

使用注释

行尾注释

在指令后面加--即可

多行指令也可以

1
2
3
SELECT name     -- 注释1
FROM students -- 注释2
LIMIT 5; -- 注释3

行内注释

1
2
3
# 这是一个注释
SELECT name
FROM students;

多行注释

1
2
3
4
/* 这是一个
注释 */
SELECT name
FROM students;

过滤数据

使用WHERE语句过滤数据

基本用法

1
2
3
SELECT name, id, class_id
FROM students
WHERE class_id = 1;

注意:WHERE 子句的位置
在同时使用ORDER BYWHERE子句时,应当让ORDER BY位于WHERE之后,否则将会产生错误

操作符

WHERE字句支持的操作符:

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

eg

小于等于

1
2
3
SELECT name, id, class_id
FROM students
WHERE score <= 60;

等于

1
2
3
SELECT name, id, class_id
FROM students
WHERE name = "小红";

当比较字符串时应当使用引号

范围

1
2
3
SELECT name, id, class_id
FROM students
WHERE score BETWEEN 60 AND 90;

空值检查

1
2
3
SELECT name, id, class_id
FROM students
WHERE name IS NULL;

NULL
无值,它与字段包含 0、空字符串或仅仅包含空格不同
检查值是否为 NULL,不可以用= NULL判断,只能用IS NULL语句

高级数据过滤

AND操作符

用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行(且)。

1
2
3
SELECT name, id, class_id, score
FROM students
WHERE class_id = 1 AND score >= 60;

OR操作符

WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行。在部分 DBMS 里会出现逻辑短路

1
2
3
SELECT name, id, class_id, score
FROM students
WHERE class_id = 1 AND name = '小红';

操作符优先级

ANDOR更先运算,在使用时可以通过圆括号对操作符进行分组,控制运算顺序

IN操作符

IN用于WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当。

1
2
3
SELECT name, id, class_id, score
FROM students
WHERE class_id IN (1, 2, 3);

IN操作符与OR类似

下面为等价代码

1
2
3
SELECT name, id, class_id, score
FROM students
WHERE class_id = 1 OR class_id = 2 OR class_id = 3;

NOT操作符

WHERE子句中用来否定其后条件的关键字

1
2
3
SELECT name, id, class_id, score
FROM students
WHERE NOT class_id = 1 ;

用通配符进行过滤

LIKE操作符

通配符:用来匹配值的一部分特殊字符

搜索模式:由字面值、通配符或两者组合构成的搜索条件

谓词:从技术上说,LIKE是谓词而不是操作符

%通配符

表示任意字符出现任意次数(包括 0 次)(在 MS Access 中,通配符为*

筛选李姓的同学

1
2
3
SELECT *
FROM students
WHERE name LIKE '李%'

LIKE搜索区分大小写

空格问题:
在 MS Access 等 DBMS 中,会在字段内容尾部填补空格,所以像WHERE name LIKE 'F%y'的语句,匹配以 y 结尾的数据时,无法匹配到尾部补全空格的数据,解决办法是使用'F%y%'

NULL 问题
WHERE name LIKE '%'不会匹配 NULL

_通配符

用于匹配单个字符

DB2 不支持_通配符

在 MS Access 中应使用?

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

[]通配符

表示匹配方括号内的任意一个字符

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

只有 MS Access 和 SQL Server 支持[]

否定方法:方括号里面加^前缀表示否定,例如[^JM]表示匹配除 J、M 以外的字符

在 MS Access 中,应将^替换为!使用,即[^JM]换为[!JM]

也可以使用NOT来否定

创建计算字段

字段:基本上与列的意思相同

拼接字段

拼接:将值联结到一起构成单个值。

可以使用一个特殊的操作符用来拼接两个列

  • +加号:MS Access、SQL Server(多数 DBMS 使用此方法)
  • ||两个竖杠:DB2、Oracle、PostgreSQL、SQLite、Open Office Base

样例代码拼接了名称和国家,并在国家两边加了括号

使用+的样例:

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

使用||的样例

1
2
3
SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

在 MySQL 或 MariaDB 中需要使用的语句有所不同

使用 Concat()函数完成

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

在上述代码中,有的 DBMS 会将字段用空格填充使其宽度保持一致,而我们并不需要空格,可以使用 SQL 的RTRIM()函数完成

+形式代码为例:

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

TRIM 函数
RTRIM():去掉右边空格
LTRIM():去掉左边空格
TRIM():去掉左右两边空格

使用别名

拼接后的字符串需要有一个名字才能正常用于客户端,所以可以给拼接后字符串起一个别名

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

即 在Select后的表达式后面加上 AS title

建议在使用拼接字符串后最好加一个AS

别名有时也称为导出列

执行算术计算

例:

1
2
3
4
5
6
SELECT prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

这段代码输出了order_num = 20008的数据,并且每行数据加一个expanded_price字段显示quantity * item_price

支持的算数操作符:

  • +
  • -
  • *
  • /

测试计算表达式
虽然SELECT常用于检索数据,但如果省略FROM字句,则用与简单的访问和处理表达式
例如:

  • SELECT 3 * 2将返回6
  • SELECT Trim(' abc ')将返回abc
  • SELECT Now()将返回当前日期和时间

使用函数处理数据