目录
前言
《Head FirstSQL》由Lynn Beighley所著，旨在以一种亲切有趣的方式教授SQL知识。这本书采用了大量幽默的图形、直观的数据展示方式和易于遵循的课程，使得学习SQL变得更加轻松愉快。这本书以其“大脑友好”的副标题和丰富的视觉格式而闻名，这种格式设计可以适应我们的大脑工作方式，而不是那种让人昏昏欲睡的文本重载方法。
总的来说，这本书很适合读一读！
一、数据和表
1. 创建数据库
CREATE DATABASE database_name;2. 使用数据库
USE database_name;3. 创建表
CRATE TABLE table_name
(
col2 VAR_TYPE,
col1 VAR_TYPE NOT NULL DEFAULT xxx,
);4. 删除表
DROP TABLE table_name;5. 显示表
DESC table_name;
DESCRIBE table_name;6. 插入数据
INSERT INTO table_name
(col1, col2)
VALUES (col1_val, col2_val)二、select
1. 查询
SELECT * FROM table_name [WHERE col1 = xxxx AND col2 = yyyy]SELECT col1, col2 col3 FROM table_name [WHERE col1 = xxxx];SELECT col1, col2 col3 FROM table_name [WHERE col1 IS NULL];
SELECT col1, col2 col3 FROM table_name [WHERE col1 IS NOT NULL];SELECT col1, col2 col3 FROM table_name [WHERE col1 LIKE xxxx];
SELECT col1, col2 col3 FROM table_name [WHERE NOT col1 LIKE xxxx];SELECT col1, col2 col3 FROM table_name [WHERE col1 BETWEEN xxx AND yyy];
SELECT col1, col2 col3 FROM table_name [WHERE NOT col1 BETWEEN XXX AND yyy];SELECT col1, col2 col3 FROM table_name [WHERE col1 IN (xxx, yyy, ZZZ)];
SELECT col1, col2 col3 FROM table_name [WHERE col1 NOT IN (xxx, yyy, ZZZ)];SELECT col1, col2 col3 FROM table_name [WHERE NOT col1 LIKE xxxx AND NOT col2 = yyyy];2.比较运算符
| = |
| > |
| >= |
| < |
| <= |
| <> |
3. 通配符
| % | 匹配任意多个字符 |
| _ | 匹配任意一个字符 |
4. 字符串转义字符
| \' | 表示单引号 |
| '' | 表示单引号 |
表示字符串时不要使用双引号，双引号用来在PHP中表示SQL语句使用
三、delete和update
1. delete
DELETE FROM table_name;DELETE FROM table_name WHERE col1 = xxxx;2. update
UPDATE table_name SET col1 = xxxx, col2 = yyyy [WHERE col1 = zzzz]四、规范化
第一范式（原子性）：每个列必须是原子的，每个行必须有主键；表中没有重复类型的值，且不能有数组。
1. show命令
SHOW CREATE TABLE table_name;
SHOW CREATE DATABASE db_name;
SHOW COLUMNS FROM table_name;
SHOW INDEX FROM table_name;
SHOW WARNINGS;2. 指定主键
NOT NULL不是必须的，但是主键应该加上这个限制。
CREATE TABLE table_name(
col1 COL1_TYPE NOT NULL,
col2 COL2_TYPE,
PRIMARY KEY (col1)
);3. 指定自动递增
每个表中只有一列可以被定义为AUTO_INCREMENT
CREATE TABLE table_name(
col1 COL1_TYPE NOT NULL AUTO_INCREMENT,
col2 COL2_TYPE,
PRIMARY KEY (col1)
);五、alter
1. 修改表名称
ALTER table_name1 RENAME TO table_name2;2. 增加一列
ALTER TABLE table_name ADD COLUMN col_name COL_TYPE FIRST;需要指定位置的话使用：
SECOND, AFTER col_name, BEFORE col_name3. 增加主键
ALTER TABLE table_name ADD PRIMARY KEY (col_name);4. 修改列
ALTER TABLE table_name CHANGE COLUMN orig_name ,new_name NOT NULL AUTO_INCREMENT;CHANGE用来重新定义一个列，所以后面的部分跟重新定义一个列是一样的。
只改变数据类型，不改变列名称
ALTER TABLE table_name MODIFY col_name NEW_TYPE;5. 删除列
ALTER TABLE table_name DROP COLUMN col_name;6. 一些字符串处理函数
| LEFT(col_name, count) | 提取字符串左侧count个字符 |
| RIGHT(col_name, count) | 提取字符串右侧count个字符 |
| SUBSTRING(col_name, fs, count) | 返回第count个fs之前的所有字符 |
| UPPER(col_name) | 字符串转换为大写字母形式 |
| LOWER(col_name) | 字符串转换为小写字母形式 |
| REVERSE(col_name) | 字符串反转 |
| LTRIM(col_name) | 删除最左边的空白 |
| RTRIM(col_name) | 删除最右边的空白 |
| LENGTH(col_name) | 返回字符串长度 |
ALTER无法直接改变列的顺序，需要先删除之前的列，再把这个列插入到想要的位置。
六、select进阶
1. case
CASE:
UPDATE table_name SET col_name=
WHEN
CASE col1 = val1 THEN val11
CASE col2 = val2 THEN val22
CASE col3 = val3 THEN val33
ELSE val_default
END;2. order by
SELECT col1, col2 WHERE col3 = XXXX
ORDER BY col1 DESC, col2 ASC;先按col1排序，再按col2排序，也可以只选一个列来排序，可以指定升序（ASC, ASCENDING）或降序（DESC, DESCENDING）
3. 数值处理函数：
| SUM(col_name) |
| AVE(col_name) |
| MIN(col_name) |
| MAX(col_name) |
| COUNT(col_name) |
这些函数会忽略掉NULL的存在
4. group by
GROUP BY col_name5. distinct
DISTINCT col_name6. limit
LIMIT n;
LIMIT m, n; //从第m个开始的n个七、拓展资料表、约束
1. 外键
外键引用的主键被称为父键；
外键所在的表被称为父表；
外键的值可以为NULL；
创建带有外键的表：
CREATE TABLE table_name
(
col1 TYPE,
col2 TYPE,
CONSTRAINT parent_table parent_col_name
FOREIGN KEY (col2)
REFERENCE parent_table (parent_col_name)
);2. 其他约束
UNIQUE
当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束:
ALTER TABLE Persons
ADD UNIQUE (Id_P)如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)CHECK（Mysql不支持）
如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束：
ALTER TABLE Persons
ADD CHECK (Id_P>0)如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束：
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
CREATE TABLE piggy_bank
(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
coin CHAR(1) CHECK (coin IN ('P','N','D','Q'))
)3. 第一范式 1NF
每个数据必须包含具有原子性的值；
每个数据必须有独一无二的识别项，人称主键(Primary Key)。
4. 数据模式
1. 一对一
-
父表中的一条记录只与子表中的一条记录相关联
- 抽出数据写出更快的查询
- 某列包含未知的值，单独存储，以避免出现null
- 隔离数据，现在访问
- 大块数据，例如BLOB
2. 一对多
父表中的一条记录与子表中的多条记录关联；
但子表中的一条记录只与父表中的一条记录关联。
3. 多对多
一张表的多行记录与另一张表的多行记录相关联；
使用junction table（连接表）转化为两个一对多关系。
5. 依赖
T.x->T.y表示为在表T中，y列函数依赖于x列。
-
部分函数依赖
非主键的列依赖于组主合键的某个部分（但不完全依赖于组合主键）；（组合键：两个以上列组成的主键） -
传递函数依赖
任何非键列与另一个非键类有关联 -
第二范式2NF
符合第一范式且没有部分函数依赖；已经符合1NF的表，如果只有一个主键的话，肯定符合2NF。 -
第三范式3NF
符合第二范式且没有传递函数依赖。(传递函数依赖是指非键列之间的依赖关系 。)
八、连接
1. AS指定别名
SELECT col_name AS new_name; 指定列别名
SELECT col_name FROM table_name AS new_name; 指定表别名其中的AS也可以省略，注意别名和原名之间不能有逗号隔开
2. 交叉联接（AKA 笛卡尔联接，叉积）
CROSS JOIN：返回两张表的每一行相乘的结果
SELECT table1.col1, table2.col2 FROM table1 CROSS JOIN table2; 其中CROSS JOIN可以用逗号代替
SELECT table1.col1, table2.col2 FROM table1 INNER JOIN table2 ON some_condition;3. 内连接
内联接就是通过查询中的条件移除了某些结果数据行后的交叉联接
QEUIJOIN
SELECT table1.col1, table2.col2 FROM table1 INNER JOIN table2 ON table1.coln = table2.colm;
//相等连接NON-QEUIJOIN
SELECT table1.col1, table2.col2 FROM table1 INNER JOIN table2 ON table1.coln <> table2.colm;相等连接及不等连接中的ON换成WHERE也一样可以工作
NATURAL-JOIN
SELECT table1.col1, table2.col2 FROM table1 NATURAL JOIN table2;
//两个表里面有相同的列时可用九、子查询
IN
SELECT col_name FROM table_name WHRE col_name IN (SELECT col_name2 FROM table_name2);其它的子查询关键字还有NOT IN, EXISTS, NOT EXISTS, =, <>
子查询用于选择列中
子查询一般只返回一个值，使用IN是列外情况
SELECT col1, col2, (sub_query) ...;非关联子查询指子查询可以独立于外层查询独立的运行，关联子查询的意思刚好相反。尽量使用非关联子查询，可以加快查询的速度。
一般来说子查询应该只返回一列的结果，当用于IN, NOT IN, EXISTS, NOT EXISTS时可以返回多列的结果。
十、外部连接、自连接和联合
左外连接
SELECT table1.col1, table2.col2 FROM table1 LEFT OUTER JOIN table2;使用左表中的每一行与右表中的每一行比较，当匹配时则在结果中增加一行，如果左表中的某一行与右表中的所有行都不匹配，则也输出一行，不过结果中有NULL。左表中的第一行在输出结果中都会至少出现一次。
右外连接
右外联接只是联接的方向变在了从右向左连接，其本质与左外联接一样。在工作中可以只使用一种外联接方式
自引用外键
用属于同一张表的其他列作为外键。
| ID | NAME | BOSS_ID |
| 1 | TOM | 1 |
| 2 | JACK | 1 |
BOSS_ID引用了ID字段
自联接
自联接把单一的表当成两张具有相同信息的表来进行查询
SELECT c1.name,c2.name as BOSS
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id =c2.id集合
UNION 联合
将多张表的查询结果合并至一张表，默认无重复
SELECT col_name FROM table1 UNION
SELECT col_name FROM table2 UNION
SELECT col_name FROM table3
ORDER by col_name;这个命令会自动去重，如果要保留重复的结果，可以使用UNION ALL命令。UNION命令要求搜索出来的列数必须相同，且列类型要相同。SEL之间的顺序不会影响结果，即使不加ORDER BY命令。
INTERSECT 交集（Mysql不支持）
INTERSECT用来取两次SELECT的交集，即两个都有的部分
SELECT title FROM job_current
INTERSECT
SELECT title FROM job_desiredEXCEPT 差集（Mysql不支持）
EXCEPT用来取两次SELECT的差集，即在一个的结果中但是不在另一个结果中的部分
SELECT title FROM job_current
EXCEPT
SELECT title FROM job_desired十一、视图表、事务
创建视图
CREATE VIEW view_name AS
SELECT ...;跟用SELECT的结果创建一个新表的命令相似，事实上，创建的视图也可以当作 一个新表来使用，可以使用SELECT语句来从中选择任意列，语法与普通SELECT完全一样:
SELECT * FROM view_name;
//view_name部分会被替换成创建view时SELECT命令，作为这条SELECT语句中的子查询。视图也可以用于UPDATE, INSERT, DELETE命令中，但是一般不推荐这样用。不过有一个hack功能却值得一试，就是用视图的CHECK OPTION来模仿其它数据库的CHECK约束。
CREATE VIEW view_name AS SELECT col1, col2 FROM table_name WHERE condition WITH CHECK OPTION;
//以后对这个视图执行INSERT操作的话就会检查condition条件是否满足。删除视图
DROP VIEW view_name;查看视图
SHOW TABLES;
//查看当前数据库中的所有表和视图当一表出现在一个视图的创建命令中时，如果这个视图没有被删除，则这个表也不能被删除。
TRANSACTION 事务
事务是一组SQL命令，要么都被执行，要么都不执行，即具有原子性。且事务在提交之前可以回滚，就像这些语句一条都没有执行一样。
START TRANSACTION;
sql command;
sql command;
ROLLBACK;
//使用ROLLBACK会撤销START TRANSCATION;后面的所有语句。START TRANSACTION;
sql command;
sql command;
COMMIT;
//使用COMMIT命令会把START TRANSCATION命令后面的语句原子性都提交到数据库中。MySQL中，只有BDB和InnoDB引擎才支持事务。事务是使用日志来实现的，在执行ROLLBACK或COMMIT之前，事务被存在日志中。
十二、安全性
设置root密码
SET PASSWORD FOR 'root'@'hostname' = PASSWORD('new_password');创建用户
CREATE USER user_name IDENTIFIED BY 'password';
//使用这种方式创建的用户是没有任何权限的，也就是说，什么都干不了。GRAND
GRAND XXX ON table_name TO user_name1, username2;
GRAND XXX ON table_name TO user_name WITH GRANT OPTION; 用户还可以把这个权限再授予其它人
XXX可以是UPDATE, INSERT, DELETE, SELECT;对于SELECT权限，还可以写成SELECT(col_name)的形式，表示只可以查看col_name列。
REVOKE
REVOKE XXX ON table_name FROM username [CASCADE];
如果username有GRANT OPTION，且已经把权限授予了其它人，则这个命令也会把他授予其它人的这个权限一并撤销掉。
如果使用RESTRICT选项替代CASCADE选项，则在上面的情况下，会报错。只撤销再授予权限的权限
REVOKE GRANT OPTION ON xxx ON table_name FROM username;副作用是如果该用户已经把这个权限授予了他人，则会一并收回他授予他人的这个权限。
用通配符
GRANT SELECT ON database_name.* TO user_name;
//某个数据库中的所有表
GRANT SELECT ON *.* TO user_name;
//所有数据库中所有表总结
总的来说，《Head First SQL》是一本生动有趣的SQL入门书籍，适合初学者和有一定经验的用户。它详细介绍了数据库基础知识，包括表的设计、数据操作、查询、连接、数据类型、键、索引等。通过大量实例和练习，读者能够掌握SQL技能，为进一步学习数据库管理和数据分析打下坚实基础。
104
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
