第1章了解 SQL

1.1 数据库基础

1.1.1 什么是数据库

数据库软件称为 DBMS (Data Base Manage System, 数据库管理系统)

数据库是通过 DBMS 创建和操纵的容器.

DBMS 替你访问数据库.

1.1.2 表 table

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

同一数据库中的每个表都有一个唯一名字用来标识.

模式 (schema) 是关于数据库和表的布局及特征的信息.

1.1.3 列和数据类型

可以把数据库中的一个表理解为一个网格:

网格中的每一列存储者一条特定的信息.

1.1.4 行

表中的数据是按行存储的.

行 (row) 和记录 (record) 可互换概念.

1.1.5 主键 primary key

唯一标识表中每行的这个列 (或这组列) 称为主键

1.2 什么是 SQL

SQL, Structured Query Language. 是一种专门用来与数据库通信的语言.

第2章 MySQL 简介

2.1 什么是 MySQL

MySQL 是一种 DBMS, 即一种数据库软件。

2.1.1 客户机-服务器软件

DBMS 可分为两类:

基于共享文件系统的 DBMS
基于客户机-服务器的 DBMS

关于数据, 数据添加, 删除和数据更新的所有请求都由服务器软件完成. (也就是处理请求)

这些请求或更改来自运行客户机软件的计算机. (也就是发送请求)

MySQL 是一个客户机-服务器 DBMS.

2.2 MySQL 工具

mysql 这个命令行工具.

2.2.2 MySQL Administrator

MySQL Administrator 是一个图形交互客户机, 用来简化 MySQL 服务器的管理.

MySQL Administrator 不作为核心 MySQL 的组成部分安装.

2.2.3 MySQL Query Browser

MySQL Query Browser 为一个图形交互客户机, 用来编写和执行 MySQL 命令.

MySQL Query Browser 也不作为核心 MySQL 的组成部分安装.

第3章使用 MySQL

MySQL 与所有客户机-服务器 DBMS 一样, 要求在能执行命令之前登录到 DBMS.

为了连接到 MySQL 需要:

主机名, 本地则为 localhost
端口
用户名
用户口令

3.2 选择数据库

使用 USE 关键字.

1	`USE DATABASE;`

3.3 了解数据库和表

显示可用的数据库列表.

数据库, 表, 列, 用户, 权限等的信息被存储在数据库和表中, 但内部的表一般不直接访问.

使用 SHOW 命令来显示这些信息.

1	`SHOW DATABASE;`

返回可用数据库的一个列表.

1	`SHOW TABLES;`

获得一个数据库内的表的列表.

1	`SHOW COLUMNS FROM customers`

显示列.

还可查询:

SHOW STATUS
SHOW CREATE DATABASE 和 SHOW CREATE TABLE
SHOW GRANTS
SHOW ERRORS 和 SHOW WARNINGS

第4章检索数据

4.1 SELECT 语句

检索表数据.

4.2 检索单个列

多条 SQL 语句必须以分号 ; 分隔.

SQL 语句不区分大小写.

在处理 SQL 语句时, 其中所有空格都被忽略.

4.3 检索多个列

1 2	`SELECT prod_id, prod_name, prod_price FROM products;`

4.4 检索所有列

1 2	`SELECT * FROM products;`

4.5 检索不同的行

使用 DISTINCT 关键字. 返回不同行:

1 2	`SELECT DISTINCT vend_id FROM products;`

4.6 限制结果

使用 LIMIT, 返回的结果不多于:

1
2
3

SELECT prod_name
FROM products
LIMIT 5;

指定开始行和行数:

1
2
3

SELECT prod_name
FROM products
LIMIT 5,5;

从第 5 行开始的 5 行.

带一个值的 LIMIT 也就是默认从第一行开始.

4.7 使用完全限定的表名

完全限定的列名, 即表名加列名:

1 2	`SELECT products.prod_name FROM products;`

完全限定的表名, 即数据库名加表名.

1 2	`SELECT products.prod_name FROM crashcourse.products;`

第5章排序检索数据

5.1 排序数据

关系数据库设计理论认为, 如果不明确规定排序顺序, 则不应该假定检索出的数据的顺序有意义.

明确排序用 SELECT 语句检索出的数据 , 使用 ORDER BY 子句.

1
2
3

SELECT prod_name
FROM products
ORDER BY prod_name;

这里一字母顺序排列.

5.2 按多个列排序

即一次排序多个列:

1
2
3

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

也就是, 先用 prod_price 进行排列, 排好后再用 prod_name 进行排列.

5.3 指定排序方向

默认升序, 也可指定 ASC (ascending).

降序 (descending):

1
2
3

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

先以降序为基础排序产品, 再对产品名排序 (比如这里就是 price 相同的就会以 name 排序):

1
2
3

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

默认情况下, A 被视为与 a 相同.

返回最高:

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

第6章过滤数据

6.1 使用 WHERE 子句

1
2
3

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

同时使用 ORDER BY 和 WHERE 子句时, 应该让 ORDER BY 位于 WHERE 之后.

6.2 WHERE 子句操作符

单引号用来限定字符串.

BETWEEN 指定范围:

1
2
3

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

6.2.4 空值检查

IS NULL:

1
2
3

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

第7章数据过滤

AND:

1
2
3

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

OR:

1
2
3

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 OR prod_price <= 10;

使用 () 明确分组:

1
2
3

SELECT prod_id, prod_price, prod_name
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price <= 10;

IN:

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

NOT, 否定它之后跟的任何条件:

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

第8章用通配符进行过滤

8.1 LIKE 操作符

也就是模糊查找. 有点像正则. 使用这种模式就需要 LIKE 操作符.

% 表示任何字符出现任意次数. (相当于正则里面的 .* 即任意字符)

1
2
3

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%'

_ 匹配任意单个字符. (相当于正则里的 .)

第9章用正则表达式进行搜索

使用 REGEXP 操作符:

SELECT prod_name 
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name

可用元字符:

.
|
[]
$ 和 ^
*
+
?
{n,m}

第10章创建计算字段

Concat() 函数, 拼接两个列:

1
2
3

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name

RTrim() 函数 (“R” 指 “Right”, 还有 LTrim()), 去掉值右边的所有空格:

1
2
3

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name

设置别名, 使用 AS:

1
2
3

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name

10.3 执行算术计算

可用算术操作符:

SELECT prod_id
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

第11章使用数据处理函数

相对来说, 多数 SQL 语句是可移植的, 而函数的可移植性却步枪.

11.2 使用函数

11.2.1 文本处理

Upper():

1
2
3

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

常用函数:

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

如, 匹配所有发音类似于 Y.Lie 的联系名:

1
2
3

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie')

11.2.2 日期和时间处理函数

检索基本日期:

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

datetime 数据类型, 存储日期及时间值.

和日期相关, 都使用 Date() 函数:

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

11.2.3 数值处理函数

在主要 DBMS 的函数中, 数值函数是最一致最统一的函数.

第12章汇总数据

12.1 聚集函数

也就是对某一列做计算:

AVG() :

1 2	`SELECT AVG(prod_price) AS avg_price FROM products;`

COUNT() :

1 2	`SELECT COUNT(*) AS num_cust FROM customers;`

12.2 聚集不同的值

就是值相同的某一行的值不聚集起来.

1
2
3

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

默认是 ALL.

12.3 组合聚合函数

也就是在 SELECT 语句中包含多个聚集函数.

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;

第13章分组数据

13.2 创建分组

GROUP BY 即让数据以某一列来排序:

1
2
3

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

13.3 过滤分组

HAVING 类似于 WHERE, 目前为止所有学过的类型的 WHERE 子句都可以用 HAVING 来代替. 唯一的差别是 WHERE 过滤行, HAVING 过滤分组.

一种理解方法:

WHERE 在数据分组前进行过滤
HAVING 在数据分组后进行过滤

注意 HAVING 的位置:

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

13.4 分组和排序

ORDER BY 和 GROUP BY 的区别:

一般在使用 GROUP BY 子句时, 应该也给出 ORDER BY 子句, 这是保证数据正确排序的唯一办法.

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal

第14章使用子查询

也就是 SELECT 语句之下嵌套 SELECT 语句:

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2')

对于能嵌套的子查询的数目没有限制, 不过在实际使用时由于性能的限制, 不能嵌套太多的子查询.

第15章联结表

联结 (join).

15.2 创建联结

将多个表的列添加到一起. 也就是添加外部的表到当前表. 但不是真正添加到表的存储中, 只是在查询的执行中存在.

联结的数目没有限制.

如:

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id 
ORDER BY vend_name, prod_name;

注意使用了完全限定名称.

笛卡尔积 (cartesian product) 指返回的行数为第一个表的行数乘以第二个表的行数. 这里没有指定完全限定名称和 WHERE 子句而出现

如, 直接从两个表取:

1
2
3

SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;

15.2.2 内部联结

基于两个表之间的相等测试?

1
2
3

SELECT vend_name, prod_name. prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

第16章创建高级联结

16.1 使用表别名

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 = 'TNT2';

16.2 使用不同类型的联结

16.2.1 自联结

联结的两个表是同一个表. 需要结合表别名.

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND   p2.prod_id = 'DTNTR';

16.2.2 自然联结

不是很明白.

16.2.3 外部联结

联结包含了那些在相关表中没有关联行的行, 叫做外部联结.

第17章组合查询

17.1 组合查询

一般的查询只有一条 SELECT 语句, 执行多个 SELECT 并返回的称为组合查询 (union) 或复合查询 (compound query)

17.2 创建组合查询

使用 UNION 关键字.

如:

SELECT vend_id, prod_id, prod_price    
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price    
FROM products
WHERE vend_id IN (1001,1002);

17.2.2 UNION 规则

两条及以上
相同的列, 表达式或聚集函数
类型兼容

17.2.3 包含或取消重复的行

UNION 默认去掉重复的行.

返回所有行可使用 UNION ALL.

SELECT vend_id, prod_id, prod_price    
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price    
FROM products
WHERE vend_id IN (1001,1002);

17.2.4 对组合查询结果排序

在用 UNION 组合查询时, 只能使用一条 ORDER BY 子句, 且必须出现在最后一条 SELECT 语句之后.

SELECT vend_id, prod_id, prod_price    
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price    
FROM products
WHERE vend_id IN (1001,1002);
ORDER BY vend_id, prod_price;

第18章全文本搜索

18.1 理解全文本搜索

在使用全文本搜索时, MySQL 不需要分别查看每个行, 不需要分别分析和处理每个词.

MySQL 创建指定列中各词的一个索引, 搜索可以针对这些词进行.

不是很理解.

18.2 使用全文本搜索

为了进行全文本搜索, 必须索引被搜索的列, 而且要随着数据的改变不断重新索引.

18.2.1 启用全文本搜索支持

在创建表时启用全文本搜索. 指定 FULLTEXT.

CREATE TABLE productnotes
(
    note_id     int          NOT NULL AUTO_INCREMENT,
    prod_id     char(10)     NOT NULL,
    note_date   datetime     NOT NULL,
    note_id     text          NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGING=MyISAM;

MySQL 根据子句 FULLTEXT(note_text) 的指示对它进行索引, 这里的 FULLTEXT 索引单个列, 也可以指定多个.

18.2.2 进行全文本搜索

索引之后, 使用:

Match() 指定被搜索的列
Against() 指定要使用的搜索表达式

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

搜索不区分大小写.

18.2.3 使用查询扩展

返回的行数会更多:

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

18.2.4 布尔文本搜索

可指定要排斥的值:

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -repo*' IN BOOLEAN MODE);

-repo* 明确指示 MySQL 排除包含 repo* 的行.

18.2.5 全文搜索的使用说明

具体见书.

第19章插入数据

INSERT.

19.2 插入完整的行

指定表名和被插入到新行中的值:

INSERT INTO Customers
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,
    NULL);

这里插入的是一行.

这种语法并不安全, 应尽量避免使用.

安全做法是明确给出列名:

INSERT INTO Customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,
    NULL);

19.3 插入多个行

使用多条 INSERT 语句, 每条语句用一个分号结束:

INSERT INTO Customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,);
INSERT INTO Customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,);
INSERT INTO Customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,
   NULL);

或:

INSERT INTO Customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contact,
    cust_email)
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,
    NULL),
    (NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles', 
    'CA', 
    '90046',
    'USA',
    NULL,
    NULL
    );

19.4 插入检索出的数据

即将一条 SELECT 语句的结果插入表中.

如, 将 custnew 表中的内容插入 customers 表.

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;

也可包含 WHERE 子句来过滤.

第20章更新和删除数据

20.1 更新数据

UPDATE 语句由 3 部分组成:

要更新的表
列名和它们的新值
确定要更新行的过滤条件

SET 命令用来将新值赋给被更新的列.

如:

UPDATE customers
SET cust_name = 'The Fudds'
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

注意 WHERE 的使用, 如果没有 WHERE 则会更新所有行.

即使是发生错误, 也继续进行更新, 使用 IGNORE 关键字. 如 UPDATE IGNORE customers...

20.2 删除数据

DELETE.

如:

1 2	`DELETE FROM customers WHERE cust_id = 10006;`

DELETE 不需要列名, DELETE 删除整行而不删除列, 为了删除指定的列, 使用 UPDATE 语句.

MySQL 没有撤销 (undo).

第21章创建和操纵表

两种创建表的方法:

交互式创建和管理表的工具
MySQL 语句操纵 (CREATE TABLE 语句)

21.1.1 表创建基础

CREATE TABLE 需要的信息:

表的名字
列的名字和定义, 用 , 分隔

如:

CREATE TABLE productnotes
(
    note_id     int          NOT NULL AUTO_INCREMENT,
    prod_id     char(10)     NOT NULL,
    note_date   datetime     NOT NULL,
    note_id     text         NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGING=MyISAM;

PRIMARY KEY 指定主键.

如果仅想在一个表不存在时创建, 应该在表名之后给出 IF NOT EXISTS.

21.1.2 使用 NULL 值

创建表时指定 NULL 或 NOT NULL:

允许 NULL 值的列也允许在插入行时不给出该列的值.
不允许 NULL 值的列不允许在插入行时不给出该列的值.

21.1.3 主键再介绍

主键是用来唯一标识某一行用的. 表中的每一行必须具有唯一的主键值.

也可以指定多个列组成主键:

CREATE TABLE productnotes
(
    note_id     int          NOT NULL AUTO_INCREMENT,
    prod_id     char(10)     NOT NULL,
    note_date   datetime     NOT NULL,
    note_id     text         NULL,

    PRIMARY KEY(note_id, prod_id),
    FULLTEXT(note_text)
)   ENGING=MyISAM;

主键也可以创建表之后定义.

21.1.4 使用 `AUTO_INCREMENT`

AUTO_INCREMENT 告诉 MySQL, 本列每增加一行时自动增量. (即, 每次执行一个 INSERT 操作, MySQL 自动对该列增量)

注意每个表值允许一个 AUTO_INCREMENT 列.

可以指定值来替代自动生成的值.

SELECT last_insert_id() 可返回最后一个 AUTO_INCREMENT 值.

21.1.5 指定默认值

SQL

MySQL 必知必会 Notes

http://example.com/2022/12/27/MySQL-必知必会-Notes/

作者

Jie

发布于

2022年12月27日

许可协议

Lua-程序设计-Notes 上一篇

解决启动-mysql-时-can-not-connect-to-local-mysql-server-through-socket-问题下一篇