6.1.1 MySQL数据库的介绍

什么是数据库

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库
专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方
式对象数据进行管理更加的方便，快速，安全

作用

对数据进行持久化的保存
方便数据的存储和查询，速度快，安全，方便
可以处理并发访问
更加安全的权限管理访问机制

常见的数据库

  • 数据库分两大类，一类是 关系型数据库。 另一类叫做 非关系型数据库。

    • 关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver。。。
    • 非关系型数据库：Redis内存数据库，MongoDB文档数据库。。。

      认识mysql数据库

      MySQL是最流行的关系型数据库管理系统

  • 安装mysql

    • 关于数据库的一些概念
    • 数据库 Database
    • 数据表 Tables
    • 数据字段
    • 行 row
    • 列

如何安装MySQL

在windows安装MySQL5.7.17

  1. 在MySQL官网 http://dev.mysql.com/downloads/mysql/ 上面下载ZIP安装包（第二个：Windows (x86, 64-bit), ZIP Archive）。

  2. 下载完成后解压，将其放到想要安装的目录下。
    例如：D:\MySQL5.7\mysql-5.7.17-winx64

  3. 新建一个my.ini配置文件，原始的my-default.ini配置文件只是个模版，不要在里面改动。
    my.ini的内容如下：
    [mysql] default-character-set=utf8
    [mysqld] port = 3306
    basedir=D:\MySQL5.7\mysql-5.7.17-winx64
    datadir=D:\MySQL5.7\mysql-5.7.17-winx64\data
    max_connections=200
    character-set-server=utf8
    default-storage-engine=INNODBexplicit_defaults_for_timestamp=true

  4. 在安装路径下新建一个空的data文件夹。

  5. 以管理员身份运行cmd，进入bin目录，执行 mysqld —initialize-insecure —user=mysql 命令。不进行这一步，安装完成之后无法启动服务。

  6. 依然在管理员cmd窗口的bin目录下，执行 mysqld install 命令安装。完成后会提示安装成功。

  7. 依然在管理员cmd窗口的bin目录下，执行 net start mysql 命令启动MySQL服务。

  8. 修改环境变量，添加”D:\MySQL5.7\mysql-5.7.17-winx64\bin”。

  9. 在普通cmd窗口中，进入bin目录，执行 mysql -u root -p 命令，默认没有密码，回车进入。

6.1.2 MySQL基础操作

使用方法:

  • 方式一: 通过在命令行敲命令来操作 ( 有助于命令的掌握)
  • 方式二: 通过图型界面工具,如 Navicat 等(在熟练掌握后再使用)
  • 方式三：通过编程语言(python,php,java,go…)执行mysql命令

SQL ( Structure query language ) 结构化查询语言

SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

SQL语句中的快捷键

  • \G 格式化输出（文本式，竖立显示）
  • \s 查看服务器端信息
  • \c 结束命令输入操作
  • \q 退出当前sql命令行模式
  • \h 查看帮助

操作数据库的步骤

连接, 打开库, 操作, 关闭退出
1.通过命令行连接MySQL
image.png

数据库语法的特点

SQL 语句可以换行, 要以分号结尾

image.png

命令不区分大小写. 关键字和函数建议用大写

image.png

如果提示符为 ‘> 那么需要输入一个’回车

6.1-数据库基础 - 图4

命令打错了换行后不能修改, 可以用 \c 取消

6.1-数据库基础 - 图5

数据库操作

  • 查看数据库 show databases;
  • 创建数据库 create database 库名 default charset=utf8;
  • 删除数据库 drop database 库名;
  • 打开数据库 use 库名;

数据操作 增删改查

插入

  1. insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
  2. insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查询

  1. select * from 表名;
  2. select 字段1,字段2,字段3 from 表名;
  3. select * from 表名 where 字段=某个值;

修改

  1. update 表名 set 字段=某个值 where 条件;
  2. update 表名 set 字段1=值1,字段2=值2 where 条件;
  3. update 表名 set 字段=字段+值 where 条件;

删除

  1. delete from 表名 where 字段=某个值;

6.1.3 MySQL的数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则
数据类型限制存储在数据列列中的数据。例如，数值数据类型列只能接受数值类型的的数据
在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。
更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。

数据类型：整型、浮点型、字符串、日期等

字符串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电 话号码、邮政编码等。
不管使用何种形式的串数据类型，串值都必须括在引号内

有两种基本的串类型，分别为定长串和变长串

  • 定长串：char
  1. 接受长度固定的字符串，其长度是在创建表时指定的。
    定长列不允许存储多于指定长度字符的数据。
    2. 指定长度后，就会分配固定的存储空间用于存放数据
  • 变长串 varchar

存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7个字符。

注意:
既然变长数据类型这样灵活，为什么还要使用定长数据类型？
回答：因为性能，MySQL处理定长列远比处理变长列快得多。

Text 变长文本类型存储
image.png

数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多
与字符串不一样，数值不应该括在引号内
image.png
decimal(5, 2) 表示数值总共5位, 小数占2位
tinyint 1字节(8位) 0-255。-128，127
int 4字节。 -21亿，21亿。0-42亿
float.

MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)

有符号或无符号

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号

  • 有符号数值列可以存储正或负的数值
  • 无符号数值列只能存储正数。
  • 默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

注意:
如果将邮政编码类似于01234存储为数值类型，则保存的将是数值1234，此时需要使用字符串类型
手机号应该用什么进行存储呢？ _用定长字符串

日期和时间类型

MySQL使用专门的数据类型来存储日期和时间值
image.png
datetime 8字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等
image.png

表的字段约束

  • unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
  • 字段类型后面加括号限制宽度
    • char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度
    • int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)
    • int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。
  • not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
  • default 设置默认值
  • primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。
  • auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
  • unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

MySQL的运算符

  • 算术运算符： +、 -、 *、 /、 %
  • 比较运算符： =、 >、 <、 >=、 <=、!=
  • 数据库特有的比较： in、not in、is null、is not null、like、between、and
  • 逻辑运算符： and、or、not
  • like: 支持特殊符号%和_ ;

其中%表示任意数量的任意字符，_表示任意一位字符

主键

1、表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值
2、应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主，以便于以后的数据操纵和管理。

要求

  • 记录一旦插入到表中，主键最好不要再修改
  • 不允许NULL
  • 不在主键列中使用可能会更改的值。
  • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键
  • 可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的

6.1.4 MySQL数据库与数据表操作

  • 数据库的操作
    • 数据库创建
    • 数据库删除
  • 数据表的操作
    • 数据表的创建
    • 数据表的修改 （表结构）
    • 数据表的删除

数据库的创建

  1. # 链接mysql数据库后，进入mysql后可以操作数据
  2. # 1. 创建库
  3. create database if not exists tlxy default charset=utf8；
  4. -- 1. 数据库 tlxy 如果不存在则创建数据库，存在则不创建
  5. -- 2. 创建 tlxy 数据库，并设置字符集为utf8
  6. -- 3. 无特殊情况都要求字符集为utf8或者utf8mb4的字符编码

查看所有库

  1. # 1. 查看所有库
  2. show databases;

打开库/进入库/选择库

  1. # use 库名
  2. use tlxy

删除库

  1. # 删除库，那么库中的所有数据都将在磁盘中删除。
  2. drop database 库名

数据表操作

创建表

  1. # 以下创建一个 users 的表
  2. create table users(
  3. -- 创建ID字段，为正整数，不允许为空 主键，自动递增
  4. id int unsigned not null primary key auto_increment,
  5. -- 创建 存储 名字的字段，为字符串类型，最大长度 5个字符，不允许为空
  6. username varchar(5) not null,
  7. -- 创建存储 密码 的字段，固定长度 32位字符， 不允许为空
  8. password char(32) not null,
  9. -- 创建 年龄 字段，不允许为空，默认值为 20
  10. age tinyint not null default 20
  11. )engine=innodb default charset=utf8;
  13. # 查看表结构
  14. desc users；
  15. #查看建表语句
  16. show create table users;

创建表的基本原则：

  • 表明和字段名 尽可能的符合命名规范，并且最好能够‘见名之意’
  • 表中数据必须有唯一标示，即主键定义。无特殊情况，主键都为数字并自增即可
  • 表中字段所对应的类型设置合理，并限制合理长度
  • 表引擎推荐使用innodb，并无特殊情况都要求为utf8或者utf8mb4的字符编码

    修改表结构

    添加字段

    语法格式：alter table 表名 action (更改的选项)
    1. # 语法：alter table 表名 add 添加的字段信息
    2. -- 在 users 表中 追加 一个 num 字段
    3. alter table users add num int not null;
    4. -- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段
    5. alter table users add email varchar(50) after age;
    6. -- 在指定字段后面追加字段，在 users 表中 age字段后面 添加一个 phone
    7. alter table users add phone char(11) not null after age;
    8. -- 在表的最前面添加一个字段
    9. alter table users add aa int first;

    删除字段

    1. # 删除字段 alter table 表名 drop 被删除的字段名
    2. alter table users drop aa;

    修改字段

    1. 语法格式： alter table 表名 change|modify 被修改的字段信息
    2. change: 可以修改字段名，
    3. modify: 不能修改字段名。
    4. # 修改表中的 num 字段 类型，使用 modify 不修改表名
    5. alter table users modify num tinyint not null default 12;
    6. # 修改表中的 num 字段 为 int并且字段名为 nn
    7. alter table users change num mm int;
    8. # 注意：一般情况下，无特殊要求，不要轻易修改表结构

    修改表名

    1. # 语法：alter table 原表名 rename as 新表名

    更改表中的自增的值

    1. # 在常规情况下，auto_increment 默认从1开始继续递增
    2. alter table users auto_increment = 1000;

    修改表引擎

    1. # 推荐在定义表时，表引擎为 innodb。
    2. # 通过查看建表语句获取当前的表引擎
    3. mysql> show create table users\G;
    4. *************************** 1. row ***************************
    5. Table: users
    6. Create Table: CREATE TABLE `users` (
    7. PRIMARY KEY (`id`)
    8. ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
    9. 1 row in set (0.00 sec)
    10. # 直接查看当前表状态信息
    11. mysql> show table status from tlxy where name = 'users'\G;
    12. *************************** 1. row ***************************
    13. Name: users
    14. Engine: InnoDB
    15. # 修改表引擎语句
    16. alter table users engine = 'myisam';

    删除表

    1. drop table 表名

    6.1.5 Mysql数据库表引擎与字符集

    服务器处理客户端请求

    其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们这里以比较复杂的查询请求为例来画个图展示一下大致的过程：
    image.png
    虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

    存储引擎

    MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

存储引擎以前叫做 表处理器 ，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQLserver 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像”读取索引第一条内容”、”读取索引下一条内容”、”插入记录”等等。

所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。
MySQL 支持非常多种存储引擎：

存储引擎 描述
ARCHIVE 用于数据存档（行被插入后不能再修改）
BLACKHOLE 丢弃写操作，读操作会返回空内容
CSV 在存储数据时，以逗号分隔各个数据项
FEDERATED 用来访问远程表
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 用来管理多个MyISAM表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL集群专用存储引擎

MyISAM和InnoDB表引擎的区别

1) 事务支持

MyISAM不支持事务，而InnoDB支持。
事物：访问并更新数据库中数据的执行单元。事物操作中，要么都执行要么都不执行

2) 存储结构

  • MyISAM：每个MyISAM在磁盘上存储成三个文件。
    • .frm文件存储表结构。
    • .MYD文件存储数据。
    • .MYI文件存储索引。
  • InnoDB：主要分为两种文件进行存储
    • .frm 存储表结构
    • .ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）

      3) 表锁差异

      MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。 InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

      4) 表主键

      MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。

      5) 表的具体行数

      MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。 InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

      6) CURD操作

      MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

      7) 外键

      MyISAM：不支持 InnoDB：支持

      8) 查询效率

      MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

      9）MyISAM和InnoDB两者的应用场景：

      MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用InnoDB。

字符集和乱码

字符集简介

我们知道在计算机中只能存储二进制数据，那该怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚两件事儿：
1. 你要把哪些字符映射成二进制数据？
也就是界定清楚字符范围。
2. 怎么映射？
将一个字符映射成一个二进制数据的过程也叫做 编码 ，将一个二进制数据映射到一个字符的过程叫做 解码 。

人们抽象出一个 字符集 的概念来描述某个字符范围的编码规则
我们看一下一些常用字符集的情况：

ASCII 字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式：
ISO 8859-1 字符集
共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。

GB2312 字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：如果该字符在 ASCII 字符集中，则采用1字节编码。否则采用2字节编码。
这种表示一个字符需要的字节数可能不同的编码方式称为 变长编码方式 。比方说字符串 ‘爱u’ ，其中 ‘爱’ 需要用2个字节进行编码，编码后的十六进制表示为 0xCED2 ， ‘u’ 需要用1个字节进行编码，编码后的十六进制表示为 0x75 ，所以拼合起来就是 0xCED275 。
小贴士： 我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了 ASCII 字
符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意
味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

GBK 字符集

GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。

Unicode 字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：
‘L’ -> 01001100（十六进制：0x4C）
‘啊’ -> 111001011001010110001010（十六进制：0xE5958A）

小贴士： 其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。更详细的Unicode和其编码方案的知识不是本书的重点，大家上网查查哈～ MySQL中并不区分字符集和编码方案的概念，所以后边唠叨的时候把utf8、utf16、utf32都当作一种字符集对待。

MySQL中的utf8和utf8mb4

我们上边说 utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计 MySQL的大叔偷偷的定义了两个概念：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用 utf8mb4 。

字符集的查看

MySQL 支持好多好多种字符集，查看当前 MySQL 中支持的字符集可以用下边这个语句：
show charset;