编程语言研究书库
学习 PHP、MySQL 与 JavaScript(第 7 版)

MySQL 基础 — 从建库到多表查询的第一次数据库旅程

MySQL 基础 — 从建库到多表查询的第一次数据库旅程

这一章讲什么: 前面七章都在学「怎么算」,从这一章开始学「怎么存」。 你会从零建一个数据库、建表、选数据类型、插数据,然后学会查、改、删、排序、分组、多表联查—— 一趟完整的旅程,终点是「两张表靠一列共同的 ISBN 缝在一起」。 原书用一张存文学经典的 classics 表走完全程,我们也照它的教法接住。 不需要任何数据库经验,每个术语第一次出现都当场解释。

1. 顶层全景:四个词,三种门

先把数据库的全部概念压缩到一张图:

  数据库 publications(一个项目的数据的总容器)
    └── 表 classics(一种实物的格子)
          ├── 行 = 一条记录(一本具体的经典)
          │     列:author | title | type | year | isbn
          └── 每格 = 一个字段的具体值('Mark Twain')

  图说:库套表,表装行,行分格列。Excel 文件的「工作簿/工作表/行/列」是同构的比喻。

数据库(database)的定义原书写得很干:「结构化的记录集合,组织方式让它能被快速检索」1。 里面的 SQL 三个字母是 Structured Query Language(结构化查询语言)—— 一门「松散基于英语」的语言,SELECT title FROM publications WHERE author = 'Charles Dickens' 基本就是英语句子,而且同门语法也用在 Oracle、SQL Server 等其他数据库上2。

和 MySQL 说话有三扇门:命令行(本章主角)、图形工具(如 MySQL Workbench、 phpMyAdmin,以及 VS Code/PhpStorm 的插件)、编程语言(第 10 章的 PHP)3。 学命令行不是因为它是最好的门,而是因为它不遮羞——每一条命令、每一个错误都摊在面前, 图形界面把什么藏了起来,你得先在命令行里见过。

2. 走进命令行

2.1 登录

AMPPS 装好 MySQL 之后,初始用户是 root、密码是 mysql(教学默认值,别带进生产)。 macOS 上的登录命令是 /Applications/ampps/mysql/bin/mysql -u root -pmysql—— -u 后接用户名,-p 后紧跟密码(中间无空格)4。 进来的第一件事,发一条验收命令:

SHOW databases;

能看到系统自带的几行数据库名单(如 information_schema、mysql),说明整条链路通了。 如果连不上报 Can't connect to local MySQL server through socket, 九成是 MySQL 服务没启动——回 AMPPS 控制面板点 Start5。

三条登录的额外经验,原书散在各平台说明里,收拢如下:

  • 远程服务器经 SSH 登录后,用管理员给你的用户名密码连; 有的主机商要求你的库名带唯一前缀(防止用户之间撞名)6。
  • 密码可以贴在 -p 后面省得交互输入,但这是坏习惯:同机其他人可能看到你的命令行历史, 密码就这么泄了7。
  • 一旦进来,Windows/macOS/Linux 就没区别了——命令和报错全平台一致8。

2.2 分号与六个提示符

MySQL 命令行第一个要习惯的规矩:每条命令以分号结尾;漏了,MySQL 不换行执行, 而是换个提示符等你续完9。这是故意的——SQL 命令往往很长,分号让你能跨多行输入, 回车只是换行不是提交。常见的「卡住」几乎都是漏分号:补一个 ; 再回车就好10。

六个提示符,等于六种「MySQL 正在等你什么」的状态11:

提示符意思
mysql>就绪,等你下一条命令
->在等一条多行命令的下一行
'>单引号字符串没关
">双引号字符串没关
`>反引号(标识符引用)没关
/*>注释没关

看到带引号的提示符,先想想是不是哪个字符串没闭合——这是排错的第一线索。 输入到一半想整段作废?敲 \c 回车,MySQL 丢弃已输入的内容、给一个新提示符。 注意:如果还开着引号/注释,先把它关上再 \c,否则 \c 会被当成字符串内容12。

3. 建库、建用户、授权

3.1 两个大小写规矩

SQL 的关键字不区分大小写:CREATE、create、CrEaTe 一样; 习惯上写大写是为了在满眼小写的命令里一眼看到骨架13。 表名分平台:Linux/macOS 敏感、Windows 不敏感——一律小写表名,和上一章「文件名一律小写」 是同一条移植性纪律14。

3.2 建库

CREATE DATABASE publications;
USE publications;

CREATE DATABASE 建容器,返回的 Query OK, 1 row affected 是成功信号; USE 切换进这个库,返回 Database changed——之后的命令都默认作用于当前库15。

3.3 用户与授权:最小权限原则

为什么不直接让 PHP 脚本用 root?原书一句话点破: 你不会想把 root 权限授给 PHP 脚本——一旦网站被黑,那就是数据库的全部权限拱手让人16。 所以标准动作是两步:

CREATE USER 'jim'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON publications.* TO 'jim'@'localhost';

第一句造用户:'用户名'@'主机名' 是账号的完整形态(同名的用户从不同主机登录可以是不同账号), IDENTIFIED BY 设密码。第二句授权:publications.* 表示「publications 库的全部对象」—— GRANT 的目标可以细到 库.表,也可以宽到 *.*(所有库所有表)17。 两条附带的安全提醒:你只能授出自己已经有的权限;建新用户不指定密码 = 无密码账号,极其危险18。 这套「按需授权」的思路叫最小权限原则——之后的章节里,每个连接数据库的脚本都该有自己的专用账号。

4. 建表:classics 表的诞生(主线例子开始)

旅程的主角上场——一张存文学经典的表:

CREATE TABLE classics (
 author VARCHAR(128),
 title VARCHAR(128),
 type VARCHAR(16),
 year CHAR(4)) ENGINE InnoDB;

逐件拆:① CREATE TABLE 表名 起头;② 括号里是列清单,每列「列名 + 数据类型」; ③ 末尾的 ENGINE InnoDB 指定存储引擎(MySQL 内部处理数据的引擎有多种, InnoDB 从 5.6 起是默认,支持事务和 FULLTEXT,写上只为显式)19。 成功返回 Query OK, 0 rows affected——0 行是因为建的是结构不是数据20。

验收工具 DESCRIBE:DESCRIBE classics; 把表的结构列成六列——Field(列名)、 Type(类型)、Null(允许空吗)、Key(索引)、Default(默认值)、Extra(附加规则)。 原书称它是「invaluable debugging aid」:表建得对不对、列名类型记不清,都靠它21。 走完这步,我们的主线例子有了空地;下面给它选格子的大小(数据类型),再往里搬数据。

5. 数据类型:每个格子该多大

数据类型 = 每列能装什么、装多少。选型的总原则原书给了两次:选「够用」里最小的—— 省磁盘、访问快;过大的类型是纯浪费,过小的类型装不下或截断22。

5.1 字符串家族

类型存储特点
CHAR(n)定长 n 字节不满 n 用空格补齐;CHAR(5) 存 "Hello" 用 5 字节
VARCHAR(n)变长,最多 n按实际长度存;"Hello" 在 VARCHAR(7) 里用 5 字节 + 少量管理开销
TEXT 系(TINYTEXT→LONGTEXT)变长大文本不能有默认值;索引只索引前 n 个字符
BINARY/VARBINARY/BLOB 系二进制字节无字符集概念,适合图片等

CHAR 与 VARCHAR 的选择判据:长度都差不多 → CHAR(省管理开销); 长度参差 → VARCHAR(省空间)23。超长的处理:塞得比声明长,MySQL 直接截断到上限—— 原书提醒你知道这个行为24。TEXT 与 VARCHAR 的边界:要全文搜 → VARCHAR; 只搜前若干字符 → TEXT 也行25。

5.2 数值家族与「带符号」

类型字节有符号范围无符号范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2-32768 ~ 327670 ~ 65535
MEDIUMINT3约 ±8.4 百万0 ~ 约 1677 万
INT4约 ±21.5 亿0 ~ 约 42.9 亿
BIGINT8约 ±9.2 × 10¹⁸0 ~ 约 1.8 × 10¹⁹
FLOAT / DOUBLE4 / 8浮点(只能有符号)——

「有符号 / 无符号」原书的比喻最好:同样的位数,有符号像把数轴左移一半—— 负数占一半,正数占一半;无符号从 0 起全给正数。计数器、年份这类永不为负的, 加 UNSIGNED 白赚一倍上限:INT UNSIGNED26。

两个易混点,原书专门纠正:

  • INT(4) 的 4 不是存储字节数,是显示宽度;它只在与 ZEROFILL 联用时才可见—— 不足 4 位的前补零(0071)。存储范围照旧由 INT 本身决定27。
  • 没有 INT(n) 限定存储大小这回事——字节数永远是表左列那些固定值。

5.3 日期时间家族

DATETIME、DATE、TIMESTAMP、TIME、YEAR。DATETIME 与 TIMESTAMP 显示格式一样, 区别在范围和自动性:TIMESTAMP 范围窄(1970–2037)但能「你不填,它自动填当前时间」, 还能设成每次改行自动更新;DATETIME 装得下几乎任何日期但没有这个自动功能28。

5.4 AUTO_INCREMENT:给每行发一个唯一的号

怎么保证每一行都独一无二?作者可能重复、年份可能重复——靠数据本身保证唯一既容易错, 在多个脚本同时插行时又根本做不到。通解:加一列专职发号, AUTO_INCREMENT 属性让它自动等于「上一行的值 + 1」,第一行从 1 开始,往后递增29:

ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

一次见四个新词:ALTER TABLE(改已有表,和 CREATE 类似但作用于现存结构)、 NOT NULL(此列不许空——NULL 意味着「无值」,发号列里绝不允许)、 AUTO_INCREMENT(自动发号)、KEY(把它设成索引,因为你会常按它查行)30。 当然也能在建表时一步到位(例 8-6);练完用 ALTER TABLE classics DROP id; 删掉31。 ALTER 一族的其他动作,顺手收齐:

动作命令
改表名ALTER TABLE classics RENAME pre1900;
改列类型ALTER TABLE classics MODIFY year SMALLINT;(数据能转则自动转)
加列ALTER TABLE classics ADD pages SMALLINT UNSIGNED;
改列名ALTER TABLE classics CHANGE type category VARCHAR(16);(必须连类型重写)
删列ALTER TABLE classics DROP pages;
删表DROP TABLE disposable;

DROP 不可逆——Warning 原书给得毫不含糊:手滑能删掉整张表甚至整个库32。

5.5 搬数据:INSERT

INSERT INTO classics(author, title, type, year)
 VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876');

结构两截:INSERT INTO 表名(列清单) 指定往哪几列塞,VALUES(值清单) 给出值—— 两边个数必须相等、顺序必须对应:顺序一错,数据就进了错的列(「更安全」的写法第 9 章讲)33。 连插五行后,SELECT * FROM classics; 第一次展示全表——注意此时行的顺序没有保证, 排序要轮到 ORDER BY 登场34。多行也能一条 INSERT:VALUES (...), (...), (...) 逗号连排, 是原书顺手教的快捷写法35。

6. 索引:给表配目录

6.1 为什么需要

表小到几百行,直接全表扫没问题;再大下去,每次查询都翻遍所有行—— 原书的比方:像查资料要翻遍图书馆的每一本书。索引就是图书馆的卡片目录: 费一点内存和磁盘,换检索时的闪电速度36。

6.2 普通索引与前缀索引

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);

括号里的 20 是前缀长度:索引只存每行前 20 个字符—— 存 "The Adventures of Tom Sawyer" 只记 "The Adventures of To"。 为什么截短?索引越小,占空间越少、查得越快;代价是前 20 字符相同的两个条目, MySQL 得回表里确认到底哪行匹配,浪费一点时间。 原书选 20 的依据:这两个列大多在前 20 字符内就能区分37。 DESCRIBE 验收时,被索引列的 Key 栏出现 MUL——表示「可重复值」,正是作者、书名该有的样子38。

等价写法:CREATE INDEX author ON classics (author(20));——和 ALTER 版等价, 只是它不能建主键39。索引在建表时一起规划更好:往大表补索引很耗时; 海量数据先入库、后建 FULLTEXT,避免建数据的过程中反复更新索引40。

6.3 PRIMARY KEY:整行的身份证

普通索引只管「快查」;主键(primary key)管「唯一」:它的每一行值都不许重复、不许为 NULL。 classics 表的最佳主键候选是 ISBN——国际公认、每书唯一。但有个坑:

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;   -- 报错:Duplicate entry for key 1

原因:表已有五行数据,新列默认是 NULL,五行的 NULL 撞了「必须唯一」的规矩。 正解三步走:先无索引地加列 → 用 UPDATE 填上每行的 ISBN → 再补 PRIMARY KEY41:

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
/* …其余四行同理… */
ALTER TABLE classics ADD PRIMARY KEY(isbn);

(UPDATE 和 WHERE 马上细讲;这里借年份先定位各行,因为现数据里年份恰好唯一。) 原书点出这个练习暗含的道理:用唯一键查行,比用作者名之类可重复的列更可预测—— Mark Twain 以后可能再加书,ISBN 永远只指一本42。

6.4 FULLTEXT:全文搜索引擎

FULLTEXT 索引把列里每个词都收进一张特殊索引,于是可以像用搜索引擎那样自然语言搜索。 三条须知:① 只能建在 CHAR/VARCHAR/TEXT 上;② 5.6 起 InnoDB 才支持(此前是 MyISAM 的专利); ③ 有个 stopword(停用词)清单——the、as、is、of 这类高频而无检索价值的词被直接忽略, 清单有 500 多个词,省空间也提速43。

ALTER TABLE classics ADD FULLTEXT(author,title);

加在 author、title 两列上——原书畅想:要是把全书正文也入库, 配上 FULLTEXT 就成了一台文学搜索引擎44。

7. 查询:SELECT 全家

7.1 骨架与两个计数器

SELECT something FROM tablename;

something 可以是 *(所有列)、列名清单、COUNT(*)(行数)。 SELECT COUNT(*) FROM classics; 返回 5——五行数据,和原书插的一致45。 (Linux 下 pager less; 能让输出分页、nopager; 取消,大表浏览小技巧46。)

7.2 DISTINCT:去重

SELECT author FROM classics; 在 Dickens 有两本书时会打出他两次; SELECT DISTINCT author FROM classics; 相同的只留一次—— DISTINCT 就是查询结果的去重开关47。

7.3 DELETE 与 WHERE:先瞄准,再开火

DELETE FROM classics WHERE title='Little Dorrit';

DELETE 永远该带 WHERE——不带的话是删全表。原书把 WHERE 称为「the heart and soul of SQL」: 它用布尔条件圈定哪些行中招,条件写错,删的就是错的行(或不匹配删了个寂寞)48。 WHERE 的弹药:

  • 精确匹配:WHERE author="Mark Twain"、WHERE isbn="9781598184891"。 两条查询现在结果相同,但 Mark Twain 的书会增多、ISBN 永远唯一——用唯一键定位,结果可预测49。
  • LIKE 模式匹配:% 是通配符,放前面表示「前面任意」,放后面表示「后面任意」, 前后都放表示「中间夹这个」;它也能匹配空串50:
    • WHERE author LIKE "Charles%" → Charles Darwin 和 Charles Dickens 都中;
    • WHERE title LIKE "%Species" → 只有 The Origin of Species;
    • WHERE title LIKE "%and%" → Pride and Prejudice 和 Romeo and Juliet。
  • 逻辑组合:AND、OR、NOT(LIKE 也有反义 NOT LIKE)任意混排: author LIKE "Charles%" AND author LIKE "%Darwin"(全名的 Charles Robert Darwin 也能中)、 author LIKE "%Mark Twain%" OR author LIKE "%Samuel Langhorne Clemens%"(笔名或真名)51。

7.4 LIMIT:截断与翻页

LIMIT 3 取前三行;LIMIT 1,2 跳过 1 行取 2 行;LIMIT 3,1 跳过 3 行取 1 行。 口诀:第一个参数是「跳过几行」,第二个是「取几行」——Warning 原书特意加了框: 偏移从 0 开始数、行数从 1 开始数,LIMIT 1,3 是「跳过第 1 行、取 3 行」, 即第 2、3、4 行52。LIMIT 就是网页分页(第 10 页、每页 20 条)背后的机制。

7.5 MATCH...AGAINST:对 FULLTEXT 提问

SELECT author,title FROM classics
 WHERE MATCH(author,title) AGAINST('curiosity shop');

自然语言模式:给几个词,任一位置的任意次序都算中; 大小写不敏感;stopword 直接跳过——查 'and' 在 MyISAM 下恒为空集, 在 InnoDB 下却可以查到(引擎的停用词表不同)53。

布尔模式(加 IN BOOLEAN MODE)是搜索引擎的进阶语法54:

写法意思
+charles -species必须含 charles,且不得含 species
"origin of"精确短语(引号内 stopword 也生效)

例:AGAINST('+charles -species' IN BOOLEAN MODE) 只回 Dickens 的 The Old Curiosity Shop——Darwin 那行被 -species 一票否决55。

7.6 UPDATE、ORDER BY、GROUP BY

UPDATE 改值,语法骨架和 SELECT 同源:UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain';—— SET 列出新值,WHERE 圈定哪些行;第二条命令把三行的 Fiction 改成 Classic Fiction, 一行还是三百行被波及,全看 WHERE 的圈56。

ORDER BY 排序:默认升序;DESC 降序,且只作用于它前面的那一列—— ORDER BY category,year DESC 是「类别升序、同类别内年份降序」57。 GROUP BY 分组统计:SELECT category,COUNT(author) FROM classics GROUP BY category; 按类别分组、数每组的行数,输出三类各几本的汇总表58。 和 DISTINCT 的区别(章末问答题的标准答案):DISTINCT 只是去重显示; GROUP BY 先分组、还能对每组跑 COUNT/SUM 等聚合——GROUP BY 是带着计算器去重的。

8. 连接表:两张表缝成一张视图

8.1 靠共同列缝合

再建一张 customers 表,三列客户和他们买的书(存的也是 ISBN)。 它和 classics 的缝合点是 isbn 列——同名列、同含义:一个 ISBN 永远指同一本书59。 于是:

SELECT name,author,title FROM customers,classics
 WHERE customers.isbn=classics.isbn;

结果:每个客户名旁边列出他买的那本书的作者和标题—— 「谁买了什么」这个分散在两表中的问题,一条查询缝合回答60。 原书预告:连接是个大题目,这里飞快掠过;第 9 章的规范化(normalization)才是系统解法61。

8.2 三种等价写法与 AS

同一件事,三种写法,结果一模一样62:

-- ① 逗号 + WHERE 连接条件(上面那个)
-- ② NATURAL JOIN:自动找同名列缝合
SELECT name,author,title FROM customers NATURAL JOIN classics;
-- ③ JOIN...ON:显式声明缝合列
SELECT name,author,title FROM customers
 JOIN classics ON customers.isbn=classics.isbn;

AS 起别名,省打字也护眼睛:customers AS cust, classics AS class WHERE cust.isbn=class.isbn—— 表名缩短,列前缀跟着短。AS 也能给列起别名:SELECT name AS customer FROM customers ORDER BY customer; (输出表头变成 customer,排序按这个新名字走)63。

9. 收尾三件套:MySQL 函数、phpMyAdmin、本章旅程总结

MySQL 内置函数存在的理由,原书一句话:它们在数据所在的地方干活。 换成 PHP 做同样的事,你得先把原始数据拽出数据库、加工、再查一遍; 内置函数省掉往返,复杂查询更简单更快(字符串、日期函数各有一箩筐,细节查文档)64。

phpMyAdmin 是浏览器里的 MySQL 图形管家:AMPPS 装好就能用, 开 http://localhost/phpmyadmin,默认登录 root/mysql。建库、建表、建索引全能点出来。 原书的态度值得原样接住:命令必须先学会(图形界面背后就是这些命令), 但学会之后,图形工具确实更快更简单65。

旅程到此的全貌:从空库出发,会了建库建表、类型与 ALTER、五种索引、 SELECT 全家、两表三式连接。下一章(第 9 章)把「怎么设计才高效」补上; 第 10 章让 PHP 进场——这些命令将从「人手敲」变成「程序发」。

10. 作者的判断与证据

书中的说法成色
别给 PHP 脚本 root 权限安全基线,机制解释(被黑即全权限丢失)一句到位,是本章最该带走的一条16
索引「费一点空间换闪电速度」机制性陈述,图书馆卡片目录的比喻贴切;「几百行以下不用索引」是经验值,非实测36
FULLTEXT 停用词表「500 多个词」具体数字,出自 MySQL 文档口径;不同引擎(InnoDB/MyISAM)的词表不同,书中以对照方式呈现43
用唯一键查询「更可预测」设计经验,ISBN vs 作者名的对比论证干净,是主键价值的最佳教学瞬间49
「LIMIT 的偏移从 0 起」语言事实,原书专门 Warning 框提醒——常见错,值得框52
图形工具「先学命令再用」教学主张,不是技术结论;但理由(知道工具背后是什么)对初学者成立65

11. 边界与局限

  • 事务、外键、视图一概没出现:InnoDB 选了,但它的看家本领(事务)要到第 9 章才露面; 外键约束(数据库层面保证引用完整性)全书基本缺席——多表连接靠「约定」而非「约束」维持。
  • 连接只讲内连接:LEFT JOIN、RIGHT JOIN 这些「保留左/右表全部行」的变体没有讲, 连接大家族只开了 NATURAL/ON 两扇窗;查询里有匹配不上行的客户时会静默消失, 这个行为差异要补 LEFT JOIN 才能解决。
  • 规范化只是预告:「库该怎么设计」这个大问题,本章用 Note 框指给第 9 章, 于是本章的多表例子(customers 存 isbn)其实是反规范化的——明白这一点,第 9 章会更有收获。
  • 数据类型的选择停留在「够用就好」:字符集(utf8mb4)、排序规则(collation) 对中文网站是生死攸关的两项,本章未提;原书只在 CHAR 一节捎带了一句「可以为列指定字符集」23。
  • SQL 注入的预防缺席:本章所有命令都是人手敲的;一旦第 10 章把这些命令拼成 PHP 字符串, 注入风险就来了——原书把防线(预处理语句)放到了第 10 章,顺序合理,但读者要带着「命令拼串很危险」 的警觉走过去。
  • AMPPS 默认口令 root/mysql:教程的方便之门,生产环境的灾难之门; 书中未强调「部署第一件事改口令」,属于教材常见的默认配置盲区。

12. 章末自测:原书的问答题怎么答

原书的问题我们的回答
分号的作用?命令结束符;漏了 MySQL 换提示符等你续完,也借此支持多行命令9
看有哪些库/表?SHOW databases; / SHOW tables;3
建 newuser@localhost、密码 newpass、全权访问 newdatabase?CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpass'; + GRANT ALL ON newdatabase.* TO 'newuser'@'localhost';17
看表结构?DESCRIBE 表名;(六列:Field/Type/Null/Key/Default/Extra)21
索引的目的?用少量空间换检索速度,免全表逐行扫描36
FULLTEXT 的好处?整列文本的自然语言搜索,像搜索引擎43
什么是 stopword?太常见而无检索价值、被 FULLTEXT 忽略的词(如 the、of),500+ 个43
DISTINCT 与 GROUP BY 的区别?前者只去重显示;后者分组并允许聚合(COUNT 等)58
查 author 含 Langhorne 的行?SELECT * FROM classics WHERE author LIKE '%Langhorne%';50
两表能连接的前提?有一列同义(如两表都存 isbn 且 ISBN 恒指同一本书)59

13. 可带走的

  1. 四个词:库装表、表装行、行分列为字段;SQL 是「松散基于英语」的命令语言1。
  2. 命令行三规矩:分号结尾、小写表名、密码别贴 -p 后面9。
  3. CREATE DATABASE 建、USE 进;CREATE USER + GRANT 按最小权限授权17。
  4. CREATE TABLE 列名配类型;DESCRIBE 验收;ALTER TABLE 改名/改类型/加列/删列;DROP 不可逆21。
  5. 类型选型:够用里最小;永不为负加 UNSIGNED;INT(4) 是显示宽度不是字节数26。
  6. AUTO_INCREMENT 专职发号;NOT NULL 守门;主键 = 唯一 + 不空,最佳人选是 ISBN 这类天然唯一键29。
  7. 索引 = 目录:普通索引可重复(MUL)、前缀截短换速度;FULLTEXT 收词入索引,stopword 被忽略36。
  8. SELECT 骨架 + WHERE(=、LIKE 的 %、AND/OR/NOT)+ LIMIT(跳几,取几)+ DISTINCT + ORDER BY(单列升降序)+ GROUP BY(分组聚合)。
  9. MATCH...AGAINST 用自然语言问 FULLTEXT;布尔模式 +必须 -排除 "精确短语"54。
  10. DELETE/UPDATE 永远带 WHERE——WHERE 是 SQL 的心脏48。
  11. 两表靠同名列连接:逗号+WHERE、NATURAL JOIN、JOIN...ON 三式等价;AS 起别名60。
  12. 命令学透之后,phpMyAdmin 之类的图形工具是加速器,不是替代品65。

14. 原文地图

主题原书章原文位置
MySQL 的分量与数据库定义Chapter 8. Introduction to MySQLtext/11-ch08-chapter-8-introduction-to-mysql.txt:7(搜「10 million」)· :20(搜「structured collection」)
SQL 与示例查询同上text/11-ch08-chapter-8-introduction-to-mysql.txt:24(搜「loosely based on English」)· :29(搜「SELECT title FROM publications」)
三种访问方式同上text/11-ch08-chapter-8-introduction-to-mysql.txt:126(搜「three main ways」)· :135(搜「Workbench」)
登录与验收Starting the Command-Line Interfacetext/12-fm-starting-the-command-line-interface.txt:86(搜「initial AMPPS MySQL user」)· :98(搜「SHOW databases」)· :107(搜「Can't connect」)
分号与六个提示符Using the Command-Line Interfacetext/13-fm-using-the-command-line-interface.txt:14(搜「semicolon is used by MySQL」)· :27(搜「six different prompts」)· :91(搜「meaningless gibberish」)
大小写规矩与建库MySQL Commandstext/14-fm-mysql-commands.txt:133(搜「case-insensitive」)· :139(搜「Table names are case-sensitive」)· :156(搜「CREATE DATABASE」)
用户与授权同上text/14-fm-mysql-commands.txt:176(搜「grant your PHP scripts」)· :225(搜「CREATE USER 'jim'」)· :272(搜「no password」)
建表与 ENGINE同上text/14-fm-mysql-commands.txt:302(搜「CREATE TABLE classics」)· :314(搜「default storage engine」)· :351(搜「DESCRIBE classics」)· :385(搜「invaluable debugging aid」)
字符串与 TEXT/BLOB 类型Data Typestext/15-fm-data-types.txt:17(搜「truncated to the maximum length」)· :121(搜「TEXT fields」)· :133(搜「first n characters」)· :193(搜「Binary Large Object」)
数值类型与 UNSIGNED/ZEROFILL同上text/15-fm-data-types.txt:242(搜「smallest data type」)· :246(搜「signed number」)· :351(搜「display width」)· :356(搜「ZEROFILL」)
日期时间与 TIMESTAMP同上text/15-fm-data-types.txt:384(搜「DATETIME」)· :411(搜「very narrow range」)· :415(搜「automatically inserted」)
AUTO_INCREMENT 与 ALTER 一族同上text/15-fm-data-types.txt:426(搜「error-prone」)· :444(搜「AUTO_INCREMENT」)· :493(搜「CREATE TABLE classics」)· :519(搜「DROP id」)· :693(搜「irreversible」)
INSERT 与顺序不定同上text/15-fm-data-types.txt:540(搜「INSERT INTO classics」)· :564(搜「order is unspecified」)
索引的理由与前缀Creating an Indextext/16-fm-creating-an-index.txt:14(搜「Adding indexes」)· :27(搜「first 20 characters」)· :41(搜「going to the table」)· :49(搜「key MUL」)
CREATE INDEX/建表时建索引/先装数据后建索引同上text/16-fm-creating-an-index.txt:76(搜「CREATE INDEX author」)· :87(搜「time-consuming」)· :239(搜「much faster to load」)
主键与回填 ISBN同上text/16-fm-creating-an-index.txt:133(搜「ADD isbn」)· :135(搜「Duplicate entry」)· :144(搜「Populating the isbn」)
FULLTEXT 与 stopwords同上text/16-fm-creating-an-index.txt:202(搜「every word」)· :209(搜「stopwords」)· :221(搜「only with」)· :256(搜「ADD FULLTEXT」)
SELECT/COUNT/DISTINCT/DELETEQuerying a MySQL Databasetext/17-fm-querying-a-mysql-database.txt:35(搜「SELECT something」)· :66(搜「result returned is 5」)· :87(搜「weed out」)· :145(搜「DELETE FROM」)· :153(搜「heart and soul」)
WHERE、LIKE、LIMIT同上text/17-fm-querying-a-mysql-database.txt:174(搜「Mark Twain」)· :183(搜「pattern matching」)· :199(搜「empty string」)· :231(搜「LIMIT 3」)· :240(搜「offsets start at 0」)
MATCH...AGAINST 与布尔模式同上text/17-fm-querying-a-mysql-database.txt:264(搜「empty set」)· :275(搜「AGAINST('and')」)· :301(搜「must be present」)· :317(搜「BOOLEAN MODE」)
UPDATE/ORDER BY/GROUP BY同上text/17-fm-querying-a-mysql-database.txt:361(搜「UPDATE classics SET」)· :375(搜「affected three rows」)· :400(搜「ORDER BY author」)· :424(搜「single column」)· :444(搜「GROUP BY category」)
多行 INSERT 与连接三式Joining Tablestext/18-fm-joining-tables.txt:48(搜「shortcut for inserting」)· :70(搜「FROM customers,classics」)· :98(搜「automatically joins」)· :118(搜「JOIN classics ON」)· :133(搜「AS cust」)
逻辑运算符与收尾Using Logical Operatorstext/19-fm-using-logical-operators.txt:16(搜「AND author」)· :37(搜「right there in the database」)· :52(搜「quicker and simpler」)· :61(搜「phpmyadmin」)

Footnotes

  1. 出处:「Chapter 8. Introduction to MySQL」第 20 段(text/11-ch08-chapter-8-introduction-to-mysql.txt:20,搜「structured collection」)。 ↩ ↩2

  2. 出处:「Chapter 8. Introduction to MySQL」第 24 段(text/11-ch08-chapter-8-introduction-to-mysql.txt:24,搜「loosely based on English」)。「This language is loosely based on English and also used in other databases such as Oracle and Microsoft SQL Server」。 ↩

  3. 出处:「Chapter 8. Introduction to MySQL」第 126 段(text/11-ch08-chapter-8-introduction-to-mysql.txt:126,搜「three main ways」)。命令行、phpMyAdmin、编程语言三扇门;SHOW 命令清单见「MySQL Commands」第 90 段(text/14-fm-mysql-commands.txt:90,搜「SHOW」)。 ↩ ↩2

  4. 出处:「Starting the Command-Line Interface」第 86 段(text/12-fm-starting-the-command-line-interface.txt:86,搜「initial AMPPS MySQL user」)与第 91 段(:91,搜「mysql -u root -pmysql」)。 ↩

  5. 出处:「Starting the Command-Line Interface」第 107 段(text/12-fm-starting-the-command-line-interface.txt:107,搜「Can't connect」)与第 98 段(:98,搜「SHOW databases」)。 ↩

  6. 出处:「Starting the Command-Line Interface」第 176 段(text/12-fm-starting-the-command-line-interface.txt:176,搜「preface all database names」)。 ↩

  7. 出处:「MySQL Commands」第 265 段(text/14-fm-mysql-commands.txt:265,搜「poor practice」)。「this is considered poor practice because... they may be able to look at the command you entered」。 ↩

  8. 出处:「Using the Command-Line Interface」第 5 段(text/13-fm-using-the-command-line-interface.txt:5,搜「identical」)。「all the commands used (and errors you may receive) are identical」。 ↩

  9. 出处:「Using the Command-Line Interface」第 14 段(text/13-fm-using-the-command-line-interface.txt:14,搜「semicolon is used by MySQL」)。「The semicolon is used by MySQL to separate or end commands」;漏分号的表现见第 21 段。 ↩ ↩2 ↩3

  10. 出处:「Using the Command-Line Interface」第 21 段(text/13-fm-using-the-command-line-interface.txt:21,搜「forgot the final semicolon」)。 ↩

  11. 出处:「Using the Command-Line Interface」第 27 段(text/13-fm-using-the-command-line-interface.txt:27,搜「six different prompts」)。Table 8-2 列出全部六个。 ↩

  12. 出处:「Using the Command-Line Interface」第 81 段(text/13-fm-using-the-command-line-interface.txt:81,搜「don't wish to execute」)与第 95 段(:95,搜「close it first」)。 ↩

  13. 出处:「MySQL Commands」第 133 段(text/14-fm-mysql-commands.txt:133,搜「case-insensitive」)。「SQL commands and keywords are case-insensitive」。 ↩

  14. 出处:「MySQL Commands」第 139 段(text/14-fm-mysql-commands.txt:139,搜「Table names are case-sensitive」)。「For the sake of portability, you should always choose a case and stick to it. The recommended style is to use lowercase」。 ↩

  15. 出处:「MySQL Commands」第 156 段(text/14-fm-mysql-commands.txt:156,搜「CREATE DATABASE」)与第 163 段(:165,搜「Database changed」)。 ↩

  16. 出处:「MySQL Commands」第 176 段(text/14-fm-mysql-commands.txt:176,搜「grant your PHP scripts」)。「you probably won't want to grant your PHP scripts root access to MySQL—it could cause a real headache should you get hacked」。 ↩ ↩2

  17. 出处:「MySQL Commands」第 185 段(text/14-fm-mysql-commands.txt:185,搜「CREATE USER 'username'」)与第 193 段(:193,搜「Table 8-4」)。*.*/database.*/database.object 三档见 GRANT 参数表。 ↩ ↩2 ↩3

  18. 出处:「MySQL Commands」第 272 段(text/14-fm-mysql-commands.txt:272,搜「no password」)。「if you create a new user but do not specify an IDENTIFIED BY clause, the user will have no password, a situation that is very insecure」。 ↩

  19. 出处:「MySQL Commands」第 306 段(text/14-fm-mysql-commands.txt:306,搜「ENGINE InnoDB」)与第 314 段(:314,搜「default storage engine」)。「From version 5.6 onward InnoDB is the default storage engine... we use it here because it supports FULLTEXT searches」。 ↩

  20. 出处:「MySQL Commands」第 342 段(text/14-fm-mysql-commands.txt:342,搜「Query OK, 0 rows affected」)。 ↩

  21. 出处:「MySQL Commands」第 385 段(text/14-fm-mysql-commands.txt:385,搜「invaluable debugging aid」)。「The DESCRIBE command is an invaluable debugging aid when you need to ensure that you have correctly created a MySQL table」;六列含义在同段展开。 ↩ ↩2 ↩3

  22. 出处:「Data Types」第 242 段(text/15-fm-data-types.txt:242,搜「smallest data type」)。「you are well advised to choose the smallest data type that will adequately handle the largest value you expect」;字符串版同义句在第 182 段。 ↩

  23. 出处:「Data Types」第 1–32 段(text/15-fm-data-types.txt:5,搜「VARCHAR stands for」)。CHAR 补齐与 VARCHAR 变长的对比;字符集一句见第 13 段(「You can assign the character set to a character or text column」);截断行为见第 17 段。 ↩ ↩2

  24. 出处:「Data Types」第 17 段(text/15-fm-data-types.txt:17,搜「truncated to the maximum length」)。「if you ever attempt to assign a string value longer than the length allowed, it will be truncated」。 ↩

  25. 出处:「Data Types」第 140 段(text/15-fm-data-types.txt:140,搜「better and faster」)。「VARCHAR is the better and faster data type to use if you need to search the entire contents of a field」。 ↩

  26. 出处:「Data Types」第 246 段(text/15-fm-data-types.txt:246,搜「signed number」)。「picture a signed number as being shifted halfway to the left」;范围表见 Table 8-10(第 251 段起)。 ↩ ↩2

  27. 出处:「Data Types」第 351 段(text/15-fm-data-types.txt:351,搜「display width」)。「It may seem counterintuitive, but what the number actually represents is the display width」;ZEROFILL 行为在同段。 ↩

  28. 出处:「Data Types」第 411 段(text/15-fm-data-types.txt:411,搜「very narrow range」)与第 415 段(:415,搜「automatically inserted」)。「If you don't specify the value when adding a row, the current time is automatically inserted」。 ↩

  29. 出处:「Data Types」第 426 段(text/15-fm-data-types.txt:426,搜「error-prone」)。「This approach is error-prone and works only in certain circumstances... It is also difficult to guarantee unique rows when multiple scripts can insert rows into the same table in parallel」;通解(extra column)在第 430 段。 ↩ ↩2

  30. 出处:「Data Types」第 444 段(text/15-fm-data-types.txt:444,搜「AUTO_INCREMENT」)。四部件逐一解释见第 448–473 段;「The first starting at 1 and the others counting upward」在第 479 段。 ↩

  31. 出处:「Data Types」第 493 段(text/15-fm-data-types.txt:493,搜「CREATE TABLE classics」)与第 519 段(:519,搜「DROP id」)。 ↩

  32. 出处:「Data Types」第 693 段(text/15-fm-data-types.txt:693,搜「irreversible」)。「Remember that DROP is irreversible」。 ↩

  33. 出处:「Data Types」第 575 段(text/15-fm-data-types.txt:575,搜「INSERT INTO classics」)与第 583 段(:583,搜「one-to-one correspondence」)。「If you accidentally listed the columns in a different order from the data, the data would go into the wrong columns」;更安全的写法预告在同段。 ↩

  34. 出处:「Data Types」第 558 段(text/15-fm-data-types.txt:558,搜「SELECT * FROM classics」)与第 564 段(:564,搜「order is unspecified」)。 ↩

  35. 出处:「Joining Tables」第 48 段(text/18-fm-joining-tables.txt:48,搜「shortcut for inserting multiple rows」)。单行多值 INSERT 的写法。 ↩

  36. 出处:「Data Types」第 741 段(text/15-fm-data-types.txt:741,搜「every new row added」)与第 745 段(:745,搜「card index」)。「at the expense of a slight overhead in memory and disk space, you can create a 'card index' for a table」。 ↩ ↩2 ↩3 ↩4

  37. 出处:「Creating an Index」第 27 段(text/16-fm-creating-an-index.txt:27,搜「first 20 characters」)与第 41 段(:41,搜「going to the table」)。「If MySQL finds two indexes with the same contents, it will have to waste time going to the table itself」;20 的选法在同段。 ↩

  38. 出处:「Creating an Index」第 49 段(text/16-fm-creating-an-index.txt:49,搜「key MUL」)。「This key means that multiple occurrences of a value may occur within that column」。 ↩

  39. 出处:「Creating an Index」第 65 段(text/16-fm-creating-an-index.txt:65,搜「CREATE INDEX」)。「They are equivalent, except that CREATE INDEX cannot be used for creating a PRIMARY KEY」。 ↩

  40. 出处:「Creating an Index」第 87 段(text/16-fm-creating-an-index.txt:87,搜「time-consuming」)与第 239 段(:239,搜「much faster to load」)。 ↩

  41. 出处:「Creating an Index」第 133 段(text/16-fm-creating-an-index.txt:133,搜「ADD isbn」)与第 135 段(:135,搜「Duplicate entry」)。「If there were no data already in the table, this command would work just fine」;三步走见第 139–156 段。 ↩

  42. 出处:「Querying a MySQL Database」第 179 段(text/17-fm-querying-a-mysql-database.txt:179,搜「more predictable」)。「searches using a unique key are more predictable」。 ↩

  43. 出处:「Creating an Index」第 202 段(text/16-fm-creating-an-index.txt:202,搜「every word」)与第 209 段(:209,搜「stopwords」)。「a built-in list of more than 500 words」;CHAR/VARCHAR/TEXT 限制见第 227 段。 ↩ ↩2 ↩3 ↩4

  44. 出处:「Creating an Index」第 256 段(text/16-fm-creating-an-index.txt:256,搜「ADD FULLTEXT」)与第 260 段(:260,搜「entire text」)。 ↩

  45. 出处:「Querying a MySQL Database」第 66 段(text/17-fm-querying-a-mysql-database.txt:66,搜「result returned is 5」)。「As you'd expect, the result returned is 5, as there are five publications in the table」。 ↩

  46. 出处:「Querying a MySQL Database」第 19 段(text/17-fm-querying-a-mysql-database.txt:19,搜「pager less」)。 ↩

  47. 出处:「Querying a MySQL Database」第 87 段(text/17-fm-querying-a-mysql-database.txt:87,搜「weed out multiple entries」)。 ↩

  48. 出处:「Querying a MySQL Database」第 153 段(text/17-fm-querying-a-mysql-database.txt:153,搜「heart and soul」)。「So now we'll spend some time on that clause, which is the heart and soul of SQL」。 ↩ ↩2

  49. 出处:「Querying a MySQL Database」第 179 段(text/17-fm-querying-a-mysql-database.txt:179,搜「more predictable」)。 ↩ ↩2

  50. 出处:「Querying a MySQL Database」第 183 段(text/17-fm-querying-a-mysql-database.txt:183,搜「pattern matching」)与第 199 段(:199,搜「empty string」)。「The % will also match if there is nothing in the position it occupies」。 ↩ ↩2

  51. 出处:「Using Logical Operators」第 16–24 段(text/19-fm-using-logical-operators.txt:16,搜「AND author」)。三个查询的解释在第 24 段:「starts with Charles and ends with Darwin」。 ↩

  52. 出处:「Querying a MySQL Database」第 240 段(text/17-fm-querying-a-mysql-database.txt:240,搜「offsets start at 0」)。「So, LIMIT 1,3 means return three rows starting from the second row」。 ↩ ↩2

  53. 出处:「Querying a MySQL Database」第 260 段(text/17-fm-querying-a-mysql-database.txt:260,搜「natural-language searches」)与第 264 段(:264,搜「empty set」)。「because and is a stopword in that engine, MySQL will ignore it and the query will always produce an empty set」。 ↩

  54. 出处:「Querying a MySQL Database」第 293 段(text/17-fm-querying-a-mysql-database.txt:293,搜「Boolean mode」)与第 301 段(:301,搜「must be present」)。+「This word must be present」、-「its presence disqualifies the row」。 ↩ ↩2

  55. 出处:「Querying a MySQL Database」第 331 段(text/17-fm-querying-a-mysql-database.txt:331,搜「Old Curiosity Shop」)。布尔查询结果的解释;短语查询中 of 被引号激活见第 338 段。 ↩

  56. 出处:「Querying a MySQL Database」第 361 段(text/17-fm-querying-a-mysql-database.txt:361,搜「UPDATE classics SET」)与第 375 段(:375,搜「affected three rows」)。 ↩

  57. 出处:「Querying a MySQL Database」第 400 段(text/17-fm-querying-a-mysql-database.txt:400,搜「ORDER BY author」)与第 424 段(:424,搜「single column」)。「The DESC keyword applies only to the preceding column」。 ↩

  58. 出处:「Querying a MySQL Database」第 439 段(text/17-fm-querying-a-mysql-database.txt:439,搜「how many publications of each category」)。GROUP BY 例与输出表。 ↩ ↩2

  59. 出处:「Joining Tables」第 60 段(text/18-fm-joining-tables.txt:60,搜「same meaning in both tables」)。「Because it has the same meaning in both tables (an ISBN refers to a book, and always the same book), we can use this column to tie the two tables together」。 ↩ ↩2

  60. 出处:「Joining Tables」第 70 段(text/18-fm-joining-tables.txt:70,搜「FROM customers,classics」)与第 89 段(:89,搜「neatly linked」)。三行联查输出见第 83–87 段。 ↩ ↩2

  61. 出处:「Joining Tables」第 12 段(text/18-fm-joining-tables.txt:12,搜「normalization」)。「In Chapter 9, you'll also learn more about database design, which includes a process called database normalization」。 ↩

  62. 出处:「Joining Tables」第 98 段(text/18-fm-joining-tables.txt:98,搜「automatically joins columns」)、第 112 段(:112,搜「identical to those of Example 8-29」)、第 127 段(:127,搜「aliases」)。三种写法的等价性原书逐条声明。 ↩

  63. 出处:「Joining Tables」第 133 段(text/18-fm-joining-tables.txt:133,搜「AS cust」)与第 154 段(:154,搜「AS customer」)。 ↩

  64. 出处:「Using Logical Operators」第 37 段(text/19-fm-using-logical-operators.txt:37,搜「right there in the database」)。「If you were to use PHP, you would first have to extract raw data from MySQL, manipulate it, and then perform the database query you wanted」。 ↩

  65. 出处:「Using Logical Operators」第 52 段(text/19-fm-using-logical-operators.txt:52,搜「quicker and simpler」)。「once you understand them, it can be much quicker and simpler to use a program such as phpMyAdmin」;「to use MySQL you have to learn these main commands」在同段。 ↩ ↩2 ↩3

实用 PHP — 格式化输出、日期时间、文件操作与上传

Previous Page

数据库设计、事务与 PHP 访问 MySQL — 让数据各就其位、让程序替人敲 SQL

Next Page

On this page

MySQL 基础 — 从建库到多表查询的第一次数据库旅程1. 顶层全景:四个词,三种门2. 走进命令行2.1 登录2.2 分号与六个提示符3. 建库、建用户、授权3.1 两个大小写规矩3.2 建库3.3 用户与授权:最小权限原则4. 建表:classics 表的诞生(主线例子开始)5. 数据类型:每个格子该多大5.1 字符串家族5.2 数值家族与「带符号」5.3 日期时间家族5.4 AUTO_INCREMENT:给每行发一个唯一的号5.5 搬数据:INSERT6. 索引:给表配目录6.1 为什么需要6.2 普通索引与前缀索引6.3 PRIMARY KEY:整行的身份证6.4 FULLTEXT:全文搜索引擎7. 查询:SELECT 全家7.1 骨架与两个计数器7.2 DISTINCT:去重7.3 DELETE 与 WHERE:先瞄准,再开火7.4 LIMIT:截断与翻页7.5 MATCH...AGAINST:对 FULLTEXT 提问7.6 UPDATE、ORDER BY、GROUP BY8. 连接表:两张表缝成一张视图8.1 靠共同列缝合8.2 三种等价写法与 AS9. 收尾三件套:MySQL 函数、phpMyAdmin、本章旅程总结10. 作者的判断与证据11. 边界与局限12. 章末自测:原书的问答题怎么答13. 可带走的14. 原文地图Footnotes