第一节：数据库入门
数据库系统架构图：
数据库：
数据库 提供 了 一个 存储 空间 用来 存储 各种 数据， 可以 将 数据库 视为 一个 存储 数据 的 容器。
数据库 管理 系统：
专门用于 创建 和 管理 数据库 的 一套 软件， 介于 应用 程序 和 操作系统 之间， 如 MySQL、 Oracle、 SQL Server、 DB2 等。
数据库 存储 结构
SQL语言
SQL（ Structured Query Language， 结构 化 查询 语言） 是 一种 数据库 查询 语言 和 程序设计 语言， 主要 用于 管理 数据库 中的 数据， 如 存取 数据、 查询 数据、 更新 数据 等。由以下四种语言构成：
（1） 数据定义语言（ Data Definition Language， DDL）。
数据库 定义 语言 主要 用于 定义 数据库、 表等。
CREATE、ALTER、DROP
（2） 数据操作语言（ Data Manipulation Language， DML）。
数据 操作 语言 主要 用于 对 数据库 进行 添加、 修改 和 删除 操作。
INSERT、UPDATE、DELETE
（3）数据查询语言（ Data Query Language， DQL）。
数据 查询 语言 主要 用于 查询 数据
SELECT
（4） 数据控制语言（ Data Control Language， DCL）。
数据 控制 语言 主要 用于 控制 用户 的 访问 权限。
GRANT、COMMIT、ROLLBACK
连接到mysql
mysql -uroot -p
\s 查看数据库信息
set character_ set_ client = gbk 设置数据库编码集
这种方式只能对当前窗口有效，要想永久修改，需要在my.ini中进行配置
数据库和表的基本操作
1、数据库基础知识
创建和查看数据库
create database 数据库名称
create database user;
查看数据库
show databases;
查看某个数据库的创建信息
show create database 数据库名称
show create database users;
修改数据库
alter database 数据库名 default character set 编码方式 collate 编码方式_bin
删除数据库
drop database 数据库名称
2、数据类型
mysql 提供了多种数据类型，包括：整数、浮点数、定点数、日期和时间、字符串和二进制类型
2.1整数类型和浮点型
DECIMAL 类型 的有效取值范围是由M和D一起确定的，M表示的是数据的长度，D表示是小数的位数
2.2 时间和日期类型
<colgroup><col style="width: 130px;"><col style="width: 130px;"><col style="width: 130px;"><col style="width: 130px;"><col style="width: 130px;"><col style="width: 130px;"></colgroup>
| 类型 | | 大小 | 范围 | 格式 | 用途 |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 | |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 | |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 | |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | |
| TIMESTAMP | 8 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 | |
在mysql中， 如果类型为时间的列设置了CURRENT_TIMESTAMP， 那么在insert一条新记录的收， 时间字段自动获取到当前时间， 如果设置了ON UPDATE CURRENT_TIMESTAMP， 则时间字段随着update命令的更新和实时变化。 如果两个属性都设置了， 那么时间字段默认为当前时间， 且随着记录的更新而自动变化。
year类型
（1） 使用 4 位 字符串 或 数字 表示， 范围 为' 1901' ～' 2155' 或 1901 ～ 2155。
（2） 使用 两位 字符串 表示， 范围 为' 00' ～' 99'， 其中，' 00' ～' 69' 范围 的 值 会被 转换 为 2000 ～ 2069 范围 的 YEAR 值。
（3）字符串 格式 的' 0' 表示 的 YEAR 值 是 2000， 而 数字 格式 的 0 表示 的 YEAR 值 是 0000。
DATE类型
以' YYYY- MM- DD' 或者' YYYYMMDD' 字符串 格式 表示。
使用 CURRENT_ DATE 或者 NOW() 表示 当前 系统 日期。
TIME 类型
以' D HH: MM: SS' 字符串 格式 表示。
使用 CURRENT_ TIME 或 NOW() 输入 当前 系统 时间。
DATETIME 类型
以' YYYY- MM- DD HH: MM: SS' 或者' YYYYMMDDHHMMSS' 字符串 格式 表示 的 日期 和 时间
TIMESTAMP 类型
TIMESTAMP 类型 用于 表示 日期 和 时间， 它的 显示 形式 与 DATETIME 相同， 但 取值 范围 比 DATETIME 小。
使用 CURRENT_ TIMESTAMP 来 输入 系统 当前 日期 和 时间。
输入 NULL 时， 系统 会 输入 系统 当前 日期 和 时间。
无任 何 输入 时， 系统 会 输入 系统 当前 日期和 时间。
2. 4 字符串 和 二进制 类型
CHAR 和 VARCHAR 类型 都用 来 表示 字符串 数据， 不同 的 是， VARCHAR 可以 存储 可变 长度 的 字符串。
BLOB 类型 是一 种 特殊 的 二进制 类型， 它 用于 表示 数据 量 很大 的 二进制 数据， 例如 图片、 PDF 文档 等。 BLOB 类型 分为 四种，
创建数据表
CREATE TABLE 表 名 (
字段 名 1 数据 类型[ 完整性 约束 条件],
字段 名 2 数据 类型[ 完整性 约束 条件],
...
字段 名 n, 数据 类型[ 完整性 约束 条件]
)
create database lianxi;
use lianxi;
create table tb_grade
(
id int(11),
name varchar(20),
grade float
);
查看数据表
show create table 表名；
查看表的字段信息
DESC 表名；
修改数据表
修改表的操作都是alter table 语句
（1） 修改表名
alter table 旧表名 rename [to] 新表名
（2）修改字段名
alter table 表名 change 旧字段名 新字段名 新字段类型
ALTER TABLE grade CHANGE name username VARCHAR( 20);
（3）修改字段的数据类型
alter table 表名 modify 字段名 数据类型；
（4）添加字段
alter table 表名 add 新字段 数据类型[约束条件] [first|after 已存在的字段名]
（5） 删除字段
alter table 表名 drop 字段名
（6）修改字段的排列位置
alter table 表名 modify 字段名1 数据类型 first | after 字段2
删除数据表
drop table 表名
2.4表的约束
为了防止向数据库中插入错误的数据，在mysql中定义了一些约束规则。常见的约束规则如图：
（1）主键：通过primary key 来定义，唯一且非空
单字段主键：
格式：
字段名 数据类型 primary key
多字段主键
由多个字段组合而成的主键。
格式：PRIMARY KEY (字段 名 1, 字段 名 2,…, 字段 名 n)
（2） 非空约束
字段值不能为NULL
格式：字段 名 数据 类型 NOT NULL;
（3）唯一约束
保证字段的唯一性
格式：字段 名 数据 类型 UNIQUE;
（4）默认约束
插入数据时，如果字段没有被赋值，则给该字段一个默认值
字段 名 数据 类型 DEFAULT 默认值;
2.5 设置表中字段自动增加
为了给新插入的字段一个唯一的ID
格式：字段 名 数据 类型 AUTO_ INCREMENT;
2.6 索引
索引就好像一本书的目录，用于快速检索数据。
1、普通索引
由KEY 或 INDEX 定义。是基本的索引类型，可以用于任何数据类型 。其值是否唯一或为空由字段本身约束决定。
2、唯一索引
由Unique 定义的索引，该索引的字段值必须唯一。
3、全文索引
只能创建在char、varchar、text字段类型上。只有MyISAM引擎支持。
注意：索引也可创建在多列上。
创建索引
（1） 创建表的时候创建索引
CREATE TABLE 表 名(
字段 名 数据 类型[ 完整性 约束 条件],
字段 名 数据 类型[ 完整性 约束 条件], …
字段 名 数据 类型 [UNIQUE| FULLTEXT| SPATIAL] INDEX| KEY [别名] (字段 名 1 [(长度)]) [ASC|DESC]));
别名：索引的名称
字段名1：指定索引字段名称。
长度：表示索引的长度
ASC|DESC 索引的顺序
1）创建普通索引
2）创建唯一索引
3）创建多列索引
（2）使用 CREATE INDEX 语句 在 已经 存在 的 表上 创建 索引
(3) 使用 ALTER TABLE 语句 在 已经 存在 的 表上 创建 索引
删除索引：
1）
ALTER TABLE 表 名 DROP INDEX 索引
2）使用 DROP INDEX 删除 索引s
DROP INDEX 索引 名 ON 表 名;
第三章：添加、更新、删除数据
3.1 添加数据
3。1.1 指定字段添加数据
INSERT INTO 表 名( 字段 名 1, 字段 名 2,…) VALUES( 值 1, 值 2,…);
3.1.2 不指定字段名的insert
INSERT INTO 表 名 VALUES( 值 1, 值 2,…);
3.1.3 同时插入多条数据
INSERT INTO 表 名[( 字段 名 1, 字段 名 2,…) ] VALUES( 值 1, 值 2,…),( 值 1, 值 2,…), … (值 1, 值 2,…);
3.2 更新数据
UPDATE 表 名 SET 字段 名 1= 值 1[, 字段 名 2 = 值 2,…] [WHERE 条件 表达式]
3.3 删除数据
DELETE FROM 表 名 [WHERE 条件 表达式]
TRUNCATE [TABLE] 表 名
第四章：单表查询
4.1 简单查询
4.1.1 SELECT 语句
SELECT
[DISTINCT] *|{字段 名 1, 字段 名 2, 字段 名 3,…}
FROM 表 名
[WHERE 条件 表达式 1]
[GROUP BY 字段 名 [HAVING 条件 表达式 2]]
[ORDER BY 字段 名 [ASC| DESC]][LIMIT [OFFSET] 记录 数]
4.1.2 查询所有字段
1、
SELECT 字段 名 1, 字段 名 2,… FROM 表 名
2、
SELECT * FROM 表 名;
4.1.3 查询指定字段
SELECT 字段 名 1, 字段 名 2,… FROM 表 名;
4.2 按条件查询
SELECT 字段 名 1, 字段 名 2,… FROM 表 名 WHERE 条件 表达式
4.2.1 带关系运算符的的查询
select 字段1，字段2，....，字段n from 表名 where 条件表达式
SELECT id, name FROM student WHERE id= 4;
4.2.2 带IN的关键字
SELECT *|字段 名 1, 字段 名 2,… FROM 表 名 WHERE 字段 名 [NOT] IN (元素 1, 元素 2,…)
SELECT id, grade, name, gender FROM student WHERE id NOT IN( 1, 2, 3);
4.2.3 带between...and 的关键字
SELECT *|{字段 名 1, 字段 名 2,…} FROM 表 名 WHERE 字段 名 [NOT] BETWEEN 值 1 AND 值 2
SELECT id, name FROM student WHERE id NOT BETWEEN 2 AND 5;
4.2.4空值查询
SELECT *|字段 名 1, 字段 名 2,… FROM 表 名 WHERE 字段 名 IS [NOT] NULL
4.2.5 带distinct关键字的查询
SELECT DISTINCT 字段 名 FROM 表 名;
distinct 关键字可以用于多个字段
SELECT DISTINCT 字段 名 1, 字段 名 2,… FROM 表 名;
4.2.6 带LIKE 关键字的查询
SELECT *|{字段 名 1, 字段 名 2,…} FROM 表 名 WHERE 字段 名 [NOT] LIKE '匹配 字符串';
NOT 关键字可选
通配符有：
1、 % 用于匹配多个字符
2、_ 用于匹配单个字符
3、要匹配 % 或 _ 用 "" 来进行转义，如果要更改默认的转义字符，可以使用 escape 关键字指定。例如： where product_name like '{_三' escape '{';
4.2.7 带AND 关键字的多条件查询
SELECT *|{字段 名 1, 字段 名 2,…} FROM 表 名 WHERE 条件 表达式 1 AND 条件 表达式 2 [… AND 条件 表达式 n];
4.2.8 带OR关键字的条件查询
SELECT *|{字段 名 1, 字段 名 2,…} FROM 表 名 WHERE 条件 表达式 1 OR 条件 表达式 2 [… OR 条件 表达式 n];
注意：
OR 关键字 和 AND 关键字 可以 一起 使用， 需要 注意 的 是， AND 的 优先级 高于 OR。
4.3 高级查询
4.3.1 聚合查询
1、count 函数
用来统计表中记录数。
SELECT COUNT(*) FROM 表 名
2、sum()函数
SUM() 是 求和 函数， 用于 求出 表中 某个 字段 所有 值 的 总和。
SELECT SUM( 字段 名) FROM 表 名;
3、AVG() 函数
AVG() 函数 用于 求出 某个 字段 所有 值 的 平均值。
SELECT AVG( 字段 名) FROM student;
4、MAX()函数
MAX() 函数 是 求 最大值 的 函数， 用于 求出 某个 字段 的 最大值。
SELECT MAX( grade) FROM student;
5.MIN()函数
MIN() 函数 是 求 最小值 的 函数， 用于 求出 某个 字段 的 最小值。
SELECT MIN( grade) FROM student;
4.3.2 对查询的结果进行排序
SELECT 字段 名 1, 字段 名 2,… FROM 表 ORDER BY 字段 名 1 [ASC | DESC], 字段 名 2 [ASC | DESC]…
默认是升序排列
SELECT * FROM student ORDER BY gender ASC, grade DESC;
4.3.3 分组查询
SELECT 字段 名 1, 字段 名 2,… FROM 表 名 GROUP BY 字段 名 1, 字段 名 2,…[ HAVING 条件 表达式];
特别 注意
GROUP BY 一般 和 聚合 函数 一起 使用， 如果 查询 的 字段 出现 在 GROUP BY 后， 却 没有 包含 在 聚合 函数 中， 该 字段 显示 的 是 分组 后的 第一 条 记录 的 值。
GROUP BY 和 HAVING 关键字 一起 使用
HAVING 关键字 和 WHERE 关键字 的 作用 相同， 都用 于 设置 条件 表达式 对 查询 结果 进行 过滤， 两者 的 区别 在于， HAVING 关键 字后 可以 跟 聚合 函数， 而 WHERE 关键字不能。
eg。将 student 表 按照 gender 字段 进行 分组 查询， 查询 出 grade 字段 值 之和 小于 300 的 分组， SQL 语句 如下 所示：
SELECT sum( grade), gender FROM student GROUP BY gender HAVING SUM( grade) ＜ 300;
4.3.4 使用limit限制查询结果的数量
SELECT 字段 名 1, 字段 名 2,…FROM 表 名 LIMIT [OFFSET,] 记录数
OFFSET 若不设置，默认为0
4.4 函数
eg。
SELECT CONCAT( id,'_', name,'_', grade,'_', gender) FROM student;
SELECT id, IF( gender=' 男', 1, 0) FROM student;
SELECT if(total_price % 2 = 1,total_price, 0) FROM tb_order;
SELECT CASE total_price WHEN 2 THEN '便宜' WHEN 4 THEN '正常' ELSE '贵' END FROM tb_order;
SELECT DATEDIFF(t.create_time,SYSDATE()) FROM tb_order t;
4.4为表和字段取别名
4.4.1为表取别名
SELECT * FROM 表 名 [AS] 别名;
4.4.2 为字段取别名
SELECT 字段 名 [AS] 别名[, 字段 名 [AS] 别名,…] FROM 表 名;
第五章 多表操作
5.1外键
外 键 是指 引用 另一个 表中 的 一列 或 多列， 被 引 用的 列 应该 具 有主 键 约束 或 唯一 性 约束。
例如：学生 表（ student） 中的 gid 是 学生 所在 的 班级 id， 是 引入 了 班级 表（ grade） 中的 主 键 id。 那么 gid 就可以 作为 表 student 的 外 键。 被 引 用的 表， 即 表 grade 是 主表； 引用 外 键 的 表， 即表 student 是 从 表， 两个 表 是 主从 关系。
注意：
引入外键后，外键列的值只能是参照列的值，并且参照列中的值不能被删除。
5.1.2 为表添加外键约束
alter table 表 名 add constraint FK_ ID foreign key( 外 键 字段 名) REFERENCES 外表 表 名( 主 键 字段 名);
执行完毕后，可以使用show create table 表名的方式来看。
create table 表名(
字段描述，
constraint fk_name foreign key (外键字段名) references 外键表表名（主键字段）
)
constraint fk_name foreign key (cat_id) references tb_category（id）
注意：
1、建立外键的表必须是InnoDB型。
2、定义外键名时不能加引号。
添加外键约束参数
alter table 表 名 add constraint FK_ ID foreign key( 外 键 字段 名) REFERENCES 外表 表 名( 主 键 字段 名); [ON DELETE {CASCADE | SET NULL | NO ACTION || RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
5.1.3 删除外键约束
alter table 表 名 drop foreign key 外 键名;
5.2 操作关联表
1、多对一（员工、部门）外键
2、多对多（课程、学生）关系表
3、一对一（什么证号）一般情况，加一列就可以
5.3连接查询
5.3.1 交叉连接
交叉连接返回的是两个表行数的乘积（笛卡儿积）；
SELECT * FROM department CROSS JOIN employee;
5.3.2 内连接
SELECT 查询 字段 FROM 表 1 [INNER] JOIN 表 2 ON 表 1. 关系 字段 = 表 2. 关系 字段
- 在内 连接 查询 中， 只有 满足 条件 的 记录 才能 出现 在 查询 结果 中。
5.3.2 外连接
返回 查询 结果 中 不仅 包含 符合 条件 的 数据， 而且 还包括 左 表（ 左 连接 或 左 外 连接）、 右 表（ 右 连接 或 右 外 连接） 或 两个 表（ 全 外 连接） 中的 所有 数据。
SELECT 所 查字 段 FROM 表 1 LEFT| RIGHT [OUTER] JOIN 表 2 ON 表 1. 关系 字段= 表 2. 关系 字段 WHERE 条件
（1） LEFT JOIN（ 左 连接）： 返回 包括 左 表中 的 所有 记录 和 右 表中 符合 连接 条件 的 记录。
（2） RIGHT JOIN（ 右 连接）： 返回 包括 右 表中 的 所有 记录 和 左 表中 符合 连接 条件 的 记录。
没有数据的部分，用NULL补充
5.4子查询
子 查询 是指 一个 查询 语句 嵌套 在另 一个 查询 语句 内部 的 查询。
5.4.1 带IN关键字的查询
SELECT * FROM department WHERE did
IN( SELECT did FROM employee WHERE age= 20);
5.4.2 exist
SELECT * FROM department WHERE exist( SELECT did FROM employee WHERE age= 20);
5.4.3 带 ANY 关键字 的 子 查询
SELECT * FROM department WHERE did ＞ any( select did from employee);
5.4.4 带 ALL 关键字 的 子 查询
SELECT * FROM department WHERE did ＞ all( select did from employee);
5.4.5 带 比较 运算符 的 子 查询
SELECT * FROM department WHERE did=( select did from employee where name=' 赵 四');
第六章 事务
START TRANSACTION;
COMMIT;
ROLLBACK;
事务的几个特点
