数据库基础
- 数据库：不同数据类型的数据构成单元、行列构成表、表构成数据库
- 数据库管理系统：DBMS，例如MySQL。创建以及操作数据库的软件
- 主键：非空且唯一，由于区分每一列，在关系型数据库中用到
- SQL：结构化查询语句，每个DBMS实现的SQL都不尽相同，存在一个标准委员会，定义可供所有DBMS使用的SQL语法。
- 两类DBMS：基于共享文件系统的DBMS，如Microsoft Access；基于客户机-服务器的DBMS，如MySQL、Oracle以及Microsoft SQL Server等。
- MySQL：使用最广泛的DBMS之一，其优点有：免费开源、执行速度快、支持维护、简单易用。
常用SQL语法
示例表：president （数据已经被改变无实际意义）
| last_name | first_name | city | state | birth | death | test | age | |
|---|---|---|---|---|---|---|---|---|
| 0 | Washington | George | Wakefield | VA | 1732-2-22 | 1799-12-14 | George Washington | 670992 |
| 1 | Adams | John | Braintree | MA | 1735-10-30 | 1826-7-4 | John Adams | 909674 |
| 2 | Jefferson | Thomas | Albemarle County | VA | 1743-4-13 | 1826-7-4 | Thomas Jefferson | 830291 |
| 3 | Madison | James | Port Conway | VA | 1751-3-16 | 1836-6-28 | James Madison | 850312 |
| 4 | Monroe | James | Westmoreland County | VA | 1758-4-28 | 1831-7-4 | James Monroe | 730276 |
查询基础
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- SELECT、ORDER BY、WHERE、BETWEEN、IS NULL
SELECT city FROM president WHERE first_name = "James" ORDER BY age;
SELECT * FROM president WHERE age BETWEEN 30 AND 50;
-- AND、OR、IN、NOT
SELECT ... FROM ... WHERE k IS NULL; # ... IS NOT NULL;
SELECT ... FROM ... WHERE k1 >= v1 AND/OR k2 <= v2;
SELECT ... FROM ... WHERE k IN (v1, v2);
-- LIKE、通配符
-- %：任意字符出现任意次数
-- _：任意字符出现一次
SELECT * FROM president WHERE last_name LIKE "Wash%";
SELECT * FROM president WHERE last_name LIKE "Wash___ton";
计算字段与函数
1
2
3
-- Concat()、算术运算
SELECT CONCAT(first_name, ' ', last_name) AS 'name' FROM president;
SELECT k1+k2 AS new_k from t;
| 函数 | 说明 |
|---|---|
| length() | 返回长度 |
| left()、right() | 返回左边或右边的字符 |
| upper()、lower() | 转换为大写或小写 |
| Ltrim()、Rtrim() | 去除左边或右边的空格 |
| locate() | 找出一个子串 |
| substring() | 返回字符串的子串字符 |
| soundex() | 返回字符串的soundex值 |
| 函数 | 说明 |
|---|---|
| Now() | 返回当前日期时间 |
| AddDate()、AddTime() | 增加一个日期、时间 |
| CurDate()、CurTime() | 返回当前日期、时间 |
| Date()、Time() | 返回当前日期、时间 |
| Year()、Month()、Day()、Hour()、Minute()、Second() | 返回一个时间的年、月、日、时、分、秒部分 |
| DayOfWeek() | 返回对应日期是星期几 |
| DateDiff() | 计算两个日期之差 |
| Date_Format() | 返回一个格式化的日期时间 |
| Date_Add() | 一个高度灵活的日期运算函数 |
| 函数 | 说明 |
|---|---|
| Rand() | 返回一个随机数 |
| Pi() | 返回圆周率 |
| Exp() | 返回一个数的指数值 |
| Mod() | 取余 |
| Sqrt() | 开方 |
| Sin()、Cos()、Tan() | 返回一个角的正弦、余弦、正切值 |
分组聚合与子查询
- 关键字：GROUP BY、DISTINCT、HAVING（由于组级条件过滤）
- 聚合函数：AVG()、COUNT()、MAX()、MIN()、SUM()、……
1
2
3
4
5
6
7
8
9
10
11
-- 子查询作为筛选条件
SELECT k1 from t WHERE k2 IN (SELECT k2 FROM t WHERE ...)
-- 子查询作为计算字段加入查询结果
SELECT k,
(SELECT COUNT(*)
FROM t2
WHERE t2.k = t1.k)
AS new_k
FROM t; -- 联系了 t1 和 t2 两个表，返回 k 和 new_k 两个字段
-- 这种联系了多个表的查询称为相关子查询
联结表与组合查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 利用主键联结表
SELECT k1, k2, k3
FROM t1, t2
WHERE t1.primary_key = t2.primary_key;
-- 如果不对主键进行配对，返回的是两表的笛卡尔积
SELECT k1, k2, k3 FROM t1, t2
-- 利用主键联结称作等值联结或内部联结，可写成
SELECT k1, k2, k3
FROM t1 INNER JOIN t2
ON t1.primary_key = t2.primary_key;
-- 对联结表的数量没有限制，可联结多个表，但是会降低性能
SELECT k1, k2, k3 FROM t1, t2, t3
WHERE t1.key1 = t2.key2 AND t2.key2_ = t3.key3
-- 自联结：利用表别名和自联结代替子查询语句
SELECT t1.k1, t2.k2
FROM t AS t1, t AS t2
WHERE t1.primary_key = t2.primary_key
AND t2.k3 = 'value';
-- 太绕了，具体案例再研究。具体有：自联结、自然联结、外部联结
1
2
3
4
5
6
7
-- 将多条查询语句的结果作为结果集返回
SELECT k1, k2, k3 FROM t WHERE k1 = "value1"
UNION
SELECT k1, k2, k3 FROM t WHERE k2 = "value2";
-- 结果自动取并集，查询涉及的列或聚合函数需相同
-- 使用 UNION ALL 将不会自动去重
-- 这里可以直接在WHERE中用多个条件子句查询，但在更复杂的查询中组合查询更加方便
插入、更改与删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- INSERT
-- 标准用法：键值对相对应
INSERT INTO t(k1, k2, k3) VALUES(v1, v2, v3);
-- 简写：依赖于字段顺序，表结构改变代码将失效
INSERT INTO t VALUES(v1, v2, ...);
-- 插入多行：
INSERT INTO t(k1, k2, k3) VALUES
(v1, v2, v3),
(v4, v5, v6),
...;
-- 插入查询结果
INSERT INTO t1(k1, k2, k3) SELECT k1, k2, k3 FROM t2;
-- UPDATE
-- 标准用法：更改单行可用WHERE筛选，更改多行需传入相同长度的数据，通常应用函数
UPDATE t set k1 = v1, k2 = v2, ...
-- 空值：
UPDATE t set k = NULL WHERE ...;
-- DELETE
-- 标准用法：删除行，必须指定条件，否则删除整个表
DELETE FROM t WHERE ...;
表与视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 创建表：必须指定，表名、列名、列定义
CREATE TABLE t(
primary_key INT NOT NULL AUTO_INCREMENT,
k1 char(20) NOT NULL,
k2 char(255) NULL,
k3 FLOAT(5,10) NULL,
k4 DateTime NOT NULL,
k5 INT NULL DEFAULT 1;
...,
PRIMARY KEY (primary_key)
)ENGINE=InnoDB
-- InnoDB：数据库引擎，常见有 InnoDB、 MEMORY、MyISAM
-- 主键：唯一区分每行，非空，可由多列组成
-- 自增：每个表只能指定一个自增列，由于给新增的每一行分配ID
-- 默认值：使用 DEFAULT 指定默认值
-- 使用 IF NOT EXISTS 避免报错
-- 其他表操作
ALTER TABLE t ADD k INT NOT NULL; -- 增加列
ALTER TABLE t DROP COLUMN k; -- 删除列
DROP TABLE t; DROP TABLES t1, t2; -- 删除表
RENAME TABLE t TO new_t; -- 重命名
/* 视图的规则与限制：
必须唯一命名，且不能与其他表重名；
视图可以嵌套，可创建视图的视图；
如果对视图的查询包含 ORDER BY，该视图中的 ORDER BY 将被覆盖；
视图不能索引，不能有关联的触发器或默认值
视图可与表一起使用，例如联结查询视图和表 */
CREATE VIEW viewname AS SELECT ...; -- 创建视图
SHOW CREATE VIEW viewname; -- 查看创建视图的语句
DROP VIEW viewname; -- 删除视图
/* 视图作用与用法：
重用以简化查询；
重新格式化数据；
过滤清洗数据；
创建计算字段 */