MySQL初级

1 数据库概述

什么是数据库？

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库

专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方式对象数据进行管理更加的方便，快速，安全

数据库的特点：

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

数据库分为两类：关系型数据库，非关系型数据库。

关系型数据库：MySQL，Oracle，PostgreSQL，SQLserver…

费关系型数据库：Redis 内存数据库，MongoDB 文档数据库…

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

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

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

数据库基本概念：

数据库 database 表 table 表引擎 engine

表头 header 字段 filed 列 col 行 row

下图为数据库图形管理工具 Navicat ：

2 MySQL基本指令

指令快捷键：

• \G 格式化显示输出
• \s 查看服务器端信息
• \c 结束命令输入操作（不想执行当前的指令，则在末尾添加即可）
• \q 退出SQL命令行模式
• \h 查看帮助

以下为命令行操作。

MySQL命令行语法特点：

• 语句可以换行，一条完整的语句要以分号结尾
• 换行后的语句不可修改，如果输错可使用\c取消执行
• MySQL命令行操作不区分大小写

2.1 连接数据库

mysql -h 服务器地址 -u 登录账号 -p -P 端口号

上条指令回车后输入登录密码。

服务器地址为本地时（localhost）可以省略。

端口号默认为 3306（可以省略，除非另外指定）

2.2 数据库操作

查看数据库 show databases;

创建数据库 create database 库名 default charset=utf8mb4;

删除数据库 drop database 库名;

打开数据库 use 库名;

2.3 数据表操作

查看表 show tables;

创建表 create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8mb4;

创建表（存在则不执行，不存在则创建） create table if not exists 表名(字段1 类型,字段2 类型);

删除表 drop table 表名;

查看表结构 desc 表名;

查看建表语句 show create table 表名;

2.4 数据操作，增删改查

增

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

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

删

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

改

update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

查

select * from 表名;

select 字段1,字段2,字段3 from 表名;

select * from 表名 where 字段=某个值;

2.5 退出数据库

quit;

exit;

\q

3 MySQL中的数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则。

在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。 更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。

MySQL常用数据类型：数值类型，字符串类型，日期和时间类型。

3.1 数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越 大，所需存储空间越多。数值类型直接使用，无需使用引号扩住。

有符号或无符号

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

数值类型一般默认为有符号，如要设置无符号添加关键字 unsigned 即可。

有符号可以存储负值或正值，无符号只能存储正值。

补充：

decimal 使用方式：decimal(4,2)，表示数值共有4位，其中2位为小数。一般在存储货币数值时使用 decimal。

3.2 字符串类型

最常用的数据类型是字符串数据类型。一般用于存储名字、地址、电话号码、邮政编码等。

不管使用何种形式的串数据类型，串值都必须括在引号内。

字符转类型分为定长串和变长串

定长串char(n)，字符集utf8

只能接受固定长度为 n （1<=n<=255）个字符（英文中文都算）的串，不管实际串实际长度为多少个字符，占用空间都是n个字符的空间

变长串varchar(n)，字符集utf8

存储可变长度的字符串，实际插入几个字符则占个字符位置，但插入的字符长度不能超过 n 个字符 （1<=n<=255）。

**既然变长数据类型这样灵活，为什么还要使用定长数据类型？ **

回答：因为性能，MySQL处理定长列远比处理变长列快得多。

3.3 时间和日期类型

MySQL 中用于存储时间和日期的类型。

4 MySQL中的类型约束和运算

4.1 表的字段约束

• 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 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

4.2 MySQL的运算符

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

4.3 主键

主键是表中具有唯一标识的一列，主键值不可重复。

主键可能不需要，但是主键应该总是定义，表中的唯一主键有利于数据的操纵和管理。

主键要求：

• 不使用可能会更改的值做主键
• 主键不可为 null
• 记录一旦插入表中，主键最好不要修改
• 尽量使用自增类型作为主键，可以不用担心主键重复，也不用手动生成主键
• 可以使用多个列作为联合主键，但联合主键并不常用，使用多列作为主键时，所有列值的组合必须是唯一的

5 MySQL中的表引擎和字符集

5.1 表引擎

服务器处理客户端请求过程：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

MySQL 服务器把数据的存储和提取操作都封装到了存储引擎中，为了实现不同的功能，MySQL 提供了不同的存储引擎，不同存储引擎管理的表的存储结构和存储算法可能不同。

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

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（存储引擎API）。所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL支持的存储引擎：

5.2 MyISAM和InnoDB表引擎的区别

区别	MyISAM	InnoDB
事务支持	不支持	支持
存储结构	分成三个文件存储 1 .frm文件存储表结构 2 .MYD文件存储数据 3 .MYI文件存储索引	分成两个文件存储 1 .frm 存储表结构 2 .ibd 存储数据和索引 （也可能是多个.ibd文件，或者是独立的表空间文件）
表锁差异	只支持表级锁，用户在执行select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据	支持事务和行级锁，是 innodb 的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁是基于索引的，当索引失效或索引不存在时，行锁会升级为表锁
表主键	允许没有任何索引和主键的表存在，索引都是保存行的地址	如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
表的具体行数	保存有表的总行数，如果select count() from table;会直接取出出该值	没有保存表的总行数 (只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大
CURD操作	执行select语句性能更优	执行create、delete、update语句性能更优
外键	不支持	支持
查询效率	相对简单，查询效率更高，小型应用推荐使用	查询效率较低，但具有诸多优良特点，使用较多
应用场景	管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。	用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要 执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用 InnoDB。

5.3 字符集

计算机只能存储二进制数据，构建字符与二进制数据的映射关系即可存储字符串。

将一个字符映射成一个二进制数据的过程叫做编码 ，将一个二进制数据映射到一个字符的过程叫做解码 。

字符集既是来描述某个字符范围的编码规则。

常用字符集：

• ASCII 字符集

  共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。可以使用1个字节来进行编码。

• ISO 8859-1 字符集

  共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。

• GB2312 字符集

  收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个， 其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：（变长编码方式）

  • 如果该字符在 ASCII 字符集中，则采用1字节编码
  • 否则采用2字节编码

• GBK 字符集

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

• Unicode 字符集

  收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。实际上，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、 utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个 字符，utf32使用4个字节编码一个字符

5.4 MySQL中的uft8和utf8mb4

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

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

在 MySQL 中 utf8 是 utf8mb3 的别名，所以使用 utf8 就意味着使用 1~3个字节来表示一个字符。如果有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，则需要使用 utf8mb4 。在开发中，推荐使用 utf8mb4 。

6 MySQL中的数据表操作

6.1 创建表

语法格式： create table 表名(字段名 类型 字段约束，字段名 类型 字段约束...);

创建表的基本原则：

• 表明和字段名尽可能的符合命名规范，并且最好能够见名知意
• 表具有唯一主键，一般为自增数字
• 表中字段类型设置合理，且限制合理长度
• 表引擎一般使用 InnoDB ，字符集一般使用 utf8 或 uft8mb4

6.2 修改表结构

添加字段：

在表的最后面添加字段： alter table 表名 add 字段名 类型 字段约束;

在表的最前面添加字段： alter table 表名 add 字段名 类型 字段约束 first;

在表的指定字段1后面添加字段2： alter table 表名 add 字段名1 类型 字段约束 after 字段名2;

修改字段：

alter table 表名 change 字段名 字段名 类型 字段约束;

alter table 表名 modify 字段名 类型 字段约束;

change: 可以修改字段名， modify: 不能修改字段名。

删除字段： alter table 表名 drop 字段名;

6.3 修改表名

修改表名： alter table 原表名 rename as 新表名;

6.4 更改表中的自增值

alter table 表名 auto_increment = 自增值;

一般情况下，自增值默认从1开始。

6.5 修改表引擎

alter table 表名 engine = 引擎名;

6.6 删除表

drop table 表名;

7 select查询语句

7.1 检索数据select

检索单列数据： select 字段名 from 表名;

检索多列数据： select 字段名1,字段名2，...字段名n from 表名;

检索所有列数据： select * from 表名;

使用通配符*检索所有列虽然省事，但是检索不需要的列会降低检索和应用程序的性能，所以，除非需要所有的列数据，否则不推荐使用通配符检索所有数据。

检索去重后的行： select distinct 字段名 from 表名;

限制检索行数： select * from 表名 limit m,n; 逃过前 m 行，取 n 行数据。

使用完全限定名检索： select 表名.字段名 from 数据库名.表名; 多表查询时，使用完全限定名可以区分重复的字段名。

7.2 对检索数据进行排序

对数据进行升序排序： select * from 表名 order by 字段名 asc; 一般默认为升序排序，所以asc可以不写。

对数据进行降序排序： select * from 表名 order by 字段名 desc;

多列排序： select * from 表名 order by 字段名1,字段名2 asc|desc;

7.3 条件过滤where

条件过滤： select * from 表名 where 字段名 条件;

where条件操作符：

操作符	说明
=	等于
<	大于
>	小于
<=	小于等于
>=	大于等于
!= <>	不等于
between … and …	在指定两个值之间
is null	是空值
is not null	不是空值

where条件组合：

多个检索条件同时成立： select * from 表名 where 字段名1 条件1 and 字段名2 条件2;

多个检索条件任一成立： select * from 表名 where 字段名1 条件1 or 字段名2 条件2;

多个条件组合时，and 的优先级高于 or，即优先判断 and 左右两侧的条件，所以当多条件同时成立时，且其中一个条件是多添件任一条件成立时，需要将任一条件成立语句使用小括号扩住。

条件1或条件2成立，且条件3成立： select * from 表名 where (字段名1 条件1 or 字段名2 条件2) and 字段3 条件3;

条件1成立，或条件2且条件3成立： select * from 表名 where 字段名1 条件1 or 字段名2 条件2 and 字段3 条件3;

in 与 not：

多条件中匹配任一条件： select * from 表名 where 字段名 in (条件1，条件2);

否定任一多条件： select * from 表名 where 字段名 not in (条件1，条件2);

in 的功能相当于 or，推荐优先使用 in，原因如下：

• in 的使用更加清楚，直观
• 使用 in 时，计算次序更容易管理
• in 操作执行比 or 操作执行更快
• in 操作可以包含其他 select 语句，更加动态的建立 where 字句

7.4 通配符的使用

like 与通配符

like 的作用可以等价于 = ，这是在值已知时进行过滤，如果值未知，仅知道部分信息即可使用 like + 通配符来过滤，此时将不能使用 =。

通配符 % 表示任意数量个任意字符，通配符 _ 表示一个任意字符，通配符使用需用引号引住。

字段以a开头： select * from 表名 where 字段名 like 'a%';

字段以a结尾： select * from 表名 where 字段名 like '%a';

字段包含a： select * from 表名 where 字段名 like '%a%';

字段第二个字母是a： select * from 表名 where 字段名 like '_a%';

通配符使用方便，但是相比于操作符的过滤，通配符过滤花费的时间更长，使用统配符有如下注意事项：

• 能够使用操作符达到目的则尽量使用操作符而不是通配符
• 在必须使用通配符时，除非必要，否则不要将通配符放在搜索模式的开始处（如’%a’）
• 统配符处于搜索模式开始处时，搜索时间较长，因为会全表遍历搜索
• 通配符的位置非常重要，放错位置将得不到正确的结果

MySQL支持正则表达式：

使用正则过滤： select * from 表名 where 字段名 regexp '正则表达式';

7.5 聚集函数与分组 group by

聚集函数： 有时并不需要将数据检索出来，仅是汇总数据获取相关的信息，如总数，列的最大值、最小值、平均值、和等信息，此时可以使用聚集函数。

函数	作用
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列值的和
avg()	返回某列值得平均值

补充： 对于 count 函数，指定某列计算行数时，若某行值为 null，则该行将会被忽略，想确保计数准确可以指定不能为空的列或使用count(*)，而不是指定某一列。

计算行数： select count(*) from 表名; select count(字段名) from 表名;

计算某列满足条件的行数： select count(*) from 表名 where 字段名 条件;

以上可知，聚集函数是针对于表中的所有数据或匹配某 where 条件的所有数据的，如果既要针对所有数据又要满足某条件呢，此时可以使用分组 group by。

group by 可以将某列数据按照特征分为多个不同的逻辑组，并针对每一个逻辑组进行聚集计算。

按字段进行分组并计算每组的行数： select 字段名1,count(*) from 表名 group by 字段名1;

按多个字段进行分组并计算每组的行数： select 字段名1,字段名2,count(*) from 表名 group by 字段名1,字段名2;

分组后可能会获得多行数据，如果想对分组后的数据进行过滤可以使用 having。

按字段进行分组并计算每组的行数并按条件过滤： select 字段名1,count(*) from 表名 group by 字段名1 having 条件;

8 MySQL的数据库导入导出和授权

8.1 数据库导出

导出一个数据库的数据将会获得一个由建表和添加数据语句组成的后缀名为 .sql 的文件，一般导出文件名为数据库名。

导出数据库时不需要连接数据库，先在命令行输入：mysqldump -u root -p 数据库名 > 路径\文件名，回车后输入密码即可导出。

将名为 student 的数据库导出到当前目录下的 student.sql 文件： mysqldump -u root -p student > .\student.sql

8.2 数据库导入

导入数据库时先要确保该数据库存在，连接数据库并创建一个空的数据库即可。

执行导入操作时也不必连接数据库，在命令行输入 mysql -u root -p 数据库名 < 路径\文件名，回车后输入密码即可导入。

将当前目录下的名为 student.sql 文件导入到名为 student 的数据库中： mysql -u root -p student < .\student.sql

8.3 数据库权限管理

数据库的 root 用户是权限最高的用户，在项目开发中最好不要使用。为了保证数据库的安全性，在项目开发中，开发人员拿到的都是数据库管理人员已分配好权限的用户，开发人员只能在用户的权限范围内，对指定的数据库中指定的数据表做指定的操作。

创建用户格式： grant 授权操作 on 授权数据库.授权数据表 to 账户@登录地址 identified by '密码';

创建用户：user，密码：123，指定对库中名为 student 的数据库的所有表有 select 查找和 insert 添加操作权限，不限制登录IP：

grant select,insert on student.* to user@'%' identified by '123';

创建用户：admin，密码：123，指定对库中名为 student 的数据库的所有表有所有操作权限，不限制登录IP：

grant all on student.* to admin@'%' identified by '123';

删除用户格式： drop user '用户名'@'%';

数据库的 root 用户是权限最高的用户，在项目开发中最好不要使用。为了保证数据库的安全性，在项目开发中，开发人员拿到的都是数据库管理人员已分配好权限的用户，开发人员只能在用户的权限范围内，对指定的数据库中指定的数据表做指定的操作。

创建用户格式： grant 授权操作 on 授权数据库.授权数据表 to 账户@登录地址 identified by '密码';

创建用户：user，密码：123，指定对库中名为 student 的数据库的所有表有 select 查找和 insert 添加操作权限，不限制登录IP：

grant select,insert on student.* to user@'%' identified by '123';

创建用户：admin，密码：123，指定对库中名为 student 的数据库的所有表有所有操作权限，不限制登录IP：

grant all on student.* to admin@'%' identified by '123';

删除用户格式： drop user '用户名'@'%';