1 MySQL学习路线
- 基础阶段：MySQL数据库的基本操作（增删改查），以及一些高级操作（视图、触发器、函数、存储过程等）。
- 优化阶段：如何提高数据库的效率，如索引，分表等。
- 部署阶段：如何搭建真实的环境系统，如服务器集群，负载均衡等。
2 数据库的基础
2.1 什么是数据库？
- 数据库：database，存储数据的仓库。
- 数据库（专业定义）：高效的存储和处理数据的介质（介质主要是两种：磁盘和内存）。
2.2 数据库的分类？
- 基于存储介质的不同
- 关系型数据库（SQL）
- 非关系型数据库（NoSQL）
2.3 不同的数据库阵营中的产品有哪些？
- 关系型数据库:
- 大型数据库：Oracle、DB2。
- 中型数据库：SQL Server、MySQL等。
- 小型数据库：access等。
- 非关系型数据库：
2.4 两种数据库阵营的区别？
- 关系型数据库：安全（保存磁盘基本不可能丢失），容易理解，比较浪费空间。
- 非关系型数据库：效率高，不安全（断电会丢失）。
3 关系型数据库
3.1 什么是关系型数据库？
- 关系型数据库：是一种建立在关系模型（数学模型）上的数据库。
- 关系模型：一种所谓建立在关系的模型，关系模型包括以下三个方面：
- 数据结构：数据存储的问题，二维表（有行和列）。
- 操作指令集合：所有SQL语句。
- 完整性约束：表内数据约束，表与表之间约束（外键）。
3.2 关系型数据的设计？
- 从需要存储的数据需求中分析，如果是一类数据（实体，比如人、书本等）应该设计成一张二维表，表是由表头（字段名）和数据部分（实际存储的数据单元）组成，如下图所示。
- 以实际案例来进行处理：分析一个教学系统，讲师负责教学，教学生，在教室教学。
- ①找出系统中的实体：讲师表，学生表，班级表。
- ②找出实体中应该存在的数据信息：
- 讲师：姓名，性别，年龄，身高，工资等。
- 学生：姓名，性别，学好，学科等。
- 班级：班级名字，教室编号等。
- 关系型数据库：维护的是实体内部，实体与实体之间的联系。
- 实体内部的联系：每个学生都有姓名，性别，学号，学科，年龄等信息。
| 姓名 |
性别 |
学号 |
学科 |
年龄 |
| 张三 |
男 |
001 |
java |
23 |
| 李四 |
男 |
002 |
MySQL |
|
| 王二 |
|
003 |
Linux |
17 |
- 第二行的所有字段，都是描述张三这个学生（内部联系）；第二列只能存放性别（内部约束）。
- 关系型数据库的特点之一：如果表中对应的某个字段没有值（数据），但是系统依然需要分配空间，所以关系型数据库比较浪费空间。
- 实体与实体之间的联系：每个学生属于某个班级，每个班级一定有多个学生（一对多）。
- 学生表：
| 姓名 |
性别 |
学号 |
学科 |
年龄 |
| 张三 |
男 |
001 |
java |
23 |
| 李四 |
男 |
002 |
MySQL |
|
| 王二 |
|
003 |
Linux |
17 |
| 班级名称 |
教室编号 |
| java001 |
A001 |
| Linux002 |
B005 |
- 解决方案：在学生表中增加一个班级字段来指向班级（必须能够唯一的找到一个班级信息）
| 姓名 |
性别 |
学号 |
学科 |
年龄 |
所属班级 |
| 张三 |
男 |
001 |
java |
23 |
java001 |
| 李四 |
男 |
002 |
MySQL |
|
MySQL005 |
| 王二 |
|
003 |
Linux |
17 |
Linux002 |
- 学生实体与班级实体之间的关系：实体与实体之间的关系。
4 关键字说明
- 数据库：database。
- 数据库系统：database system（DBS）:是一种虚拟系统，将多种内容关联起来的称呼。DBS=DBMS+DB。
- DBMS：数据库管理系统，database management system，专门管理数据库。
- DBA：数据库管理员，database administrator。
- 行/记录：row/record,本质是一个东西，都是指表中的一行（一条记录）。行是从结构角度，记录是从数据角度出发。
- 列/字段：column/Field，本质是一个东西。
5 SQL
- SQL：Structured Query Language，结构化查询语言（数据主要以查询为主）。
- SQL分为三个部分：
- DDL：Data Definition Language，数据定义语言。用来维护存储数据的结构（数据库，表），代表指令：create、drop、alter等。
- DML：Data Manipulation Language，数据操作语言，用来对数据进行操作（数据表中的内容），代表指令：Insert、delete、update等。其中DML内部又单独进行了一个分类：DQL（Data Query Language：数据查询语言，如select）。
- DCL：Data Control Language，数据控制语言，主要负责权限管理，代表指令：grant、revoke等。
- SQL是关系型数据库的操作指令，SQL是一种约束，但不强制（类似于W3C），所以不同的关系型数据库产品（Oracle，MySQL等）内部可能有一些细微的差别。
6 MySQL数据库
- MySQL数据库是一种C/S结构的软件：客户端/服务端，如果想访问服务器必须通过客户端（服务器一直运行，客户端在需要使用的时候运行）。
- 交互方式：
- ②发送SQL指令。
- ③服务器接收SQL指令，处理SQL指令，返回操作结果。
- ④客户端接收结果，显示结果。
7 MySQL服务器对象
- 没有办法完全了解服务器内部的内容：只能粗略的去分析数据库服务器的内部结构。
- 将MySQL服务器内部对象分成了四层：
- 系统（DBMS）
- 数据库（DB）
- 数据表（Table）
- 字段（Field）
8 SQL的基本操作
- 基本操作：CRUD。
- 将SQL的基本操作根据操作对象进行分类，分为三类，如下所示：
9 库操作
9.1 新增数据库
create database 数据库名字 [库选项];
- 库选项：用来约束数据库，分为两个选项。
- 字符集设定：charset/character set 具体字符集（数据存储的编码格式）。
- 校对集设定：collate 具体校对集（数据比较的规则）。
-- 单行注释，也可以用#
# 创建数据库
CREATE DATABASE mydatabase CHARACTER SET utf8;
- 其实，数据库名字不能使用关键字（已经被使用的字符）或保留字（将来可能会使用的字符）。
- 当创建数据库的SQL语句执行之后，发生了什么？
- ①在数据库系统中，增加了对应的数据库信息。
- ②会在保存数据的文件夹下：Data目录，创建一个对应数据库名字的文件夹。
9.2 查看数据库
-- 查看所有数据库
show databases;
-- 查看指定部分的数据库
show databases like 'pattern'; -- pattern是匹配模式
%:表示匹配多个字符
_:表示匹配单个字符
-- 查看数据库的创建语句
show create database 数据库名字;
9.3 更新数据库
- 数据库名字是不可以改变的，不然如何定位哪一个数据库，换句话说，原来有一个数据库名为test，你现在将其它数据库改为test，这样好吗？
- 数据库的修改仅限库选项：字符集和校对集（校对集依赖于字符集）。
alter database 数据库名字 [库选项]。
character set [字符集]
collate 绞对集
9.4 删除数据库
-- 删除数据库
drop database 数据库名字;
- 当删除数据库语句执行之后，发生了什么？
- 在数据库内部看不到对应的数据库。
- 在对应的数据库存储的文件夹内，数据库名字对应的文件夹也被删除（级联删除：里面的数据库表一起被删除）。
- 注意：数据库的删除不是闹着玩的，不要随意的删除，应该先进行备份操作，然后才考虑是否删除（删除不可逆）。
10 表操作
10.1 新增数据表
-- 新增数据表
create table [if not exists] 数据表名字(
字段名字 数据类型,
字段名字,数据类型 -- 最后一行不不要逗号
)[表选项];
if not exists：如果表名不存在，那么就创建，否则不执行创建。
表选项：控制表的表现。
字符集：charset/character set 具体字符集;--保证表中数据存在的字符集。
校对集：collate 具体校对集。
存储引擎：engine 具体的存储引擎(innodb和myisam)
-- 显示的指定表所在的数据库
create table 数据库名.表名(
字段1 数据类型,
字段2 数据类型
);
- ②隐式的指定表所属数据库：先进入到某个数据库环境，然后这样创建的数据库表自动归属到某个数据库。
-- 进入到数据库环境
use 数据库名字;
-- 创建数据表
create table 数据表名(
字段1 数据类型,
字段2 数据类型
)；
- 当创建数据库的SQL指令执行之后，到底发生了什么？
- 指定数据库下已经存在对应的表。
- 在数据库对应的文件夹下，会产生对应表的结构文件（和存储引擎有关）。
10.2 查看数据表
-- 查看部分表
show tables like 'pattern';
-- 查看表的创建语句
show create table 表名;
-- 查看表结构
①desc 表名;
②describe 表名;
③show columns from 表名;
10.3 修改数据表
- 表本身存在，还包含字段，所以表的修改分为两个部分：修改表本身和修改字段。
-- 修改表名
rename table 老表名 to 新表名;
alter table 表名 add [column] 字段名 数据类型[列属性][位置];
位置：字段名可以存在表中的任意位置
first：第一个位置
after:在哪个字段之后，after 字段名，默认在最后
alter table 表名 modify 字段名 数据类型[列属性] [位置]；
alter table 表名 change 旧字段名 新字段名 数据类型[列属性][位置];
10.4 删除数据表
-- 删除表
drop table 表名1,表名2,……;
- 当删除数据表的指令执行之后发生了什么？
- ①在表空间中，没有了指定的表（数据也没有了）。
- ②在数据库对应的文件夹下，表对应的文件（与存储引擎有关）也会被删除。
11 数据操作
11.1 新增数据
- 有两种方案
- ①给全表字段插入数据，不需要指定字段列表：要求数据的值出现的位置必须和表中设计的字段出现的顺序一致，凡是非数值的数据，都需要使用引号包裹。
insert into 表名 values (值列表)[,(值列表)]; -- 可以一次性插入多条记录
- ②给部分字段插入数据：需要选定字段列表，字段列表出现的顺序和字段的顺序无关，但是值列表的顺序必须和选定的字段的顺序一致。
insert into 表名 (字段列表) values (值列表);
11.2 查看数据表
select */字段列表 from 表名 [where 条件] ;
11.3 更新数据
update 表名 set 字段1=值1,字段2=值2,……[where 条件];
11.4 删除数据
delete from 表名 [where 条件];
12 中文数据问题
- 中文数据问题本质是字符集问题。
- 计算机只识别二进制，人类更多的是识别符号，所以需要有个二进制和字符的对应关系（字符集）。
- 原因：\xD5\xC5\xC8\FD 代表的是“”张三”在当前编码（字符集）下的二进制转换为十六进制，两个汉字-->四个字节（GBK）。
- 报错：服务器没有识别对应的四个字节，服务器认为数据是utf8的，一个汉字对应三个字节；所以服务器读取三个字节转换成汉字，失败了;剩余的再读三个字节，最终失败。
- 所有的数据库服务器认为（表现）的一些特性是通过服务器端的变量来保存的，系统先读取自己的变量，看看应该怎么表现。
show variables like 'character_set%';
- 问题根源：客户端数据只能是GDK，而服务器认为是utf8。
- 解决方案：改变服务器。默认的接收字符集为GBK。
-- 修改服务器认为的客户端数据的字符集为GBK
set character_set_client=gbk;
- 原因：数据来源是服务器，解析数据是客户端（客户端只识别GBK：只会两个字节一个汉字），但是服务器给的数据是UTF8，所以导致乱码。
- 解决方案：修改服务器给客户端的数据字符集为GBK。
set character_set_results=gbk;
- set 变量=值；这样修改只是会话级别（当前客户端当次连接有效，关闭失效）
- 设置服务器对客户端的字符集的认知，如果按照上面的方式，太麻烦了。可以使用快捷方式。
13 校对集问题
- 校对集：数据比较的方式。
- 校对集有三种方式：
- _bin：binary，二进制比较，取出二进制位，一位一位的比较。区分大小写。
- _cs：case sensitive，大小写敏感，区分大小写。
- _ci：case insensitive.大小写不敏感，不区分大小写。
- 校对集应用：只有当数据产生比较的时候，校对集才会生效。
14 Web乱码问题
- 动态web由三个部分构成：浏览器、web服务器（如Tomcat等）、数据库服务器，三个部分都有自己的字符集（尤其是中文），数据需要在三个部分之间来回传递，很容易产生乱码。
- 如果解决乱码问题：统一编码（三码合一）。