MySQL - 数据库基础

数据库基础

一、数据库简介

1.什么是数据库：

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

2.数据库的作用：

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

3.常见的数据库：

数据库分两大类，一类是关系型数据库，另一类叫做非关系型数据库。
关系型数据库：MySQL、Oracle、PostgreSQL、SQLserver……
非关系型数据库：Redis内存数据库、MongoDB文档数据库……
MySQL是最流行的关系型数据库管理系统。

4.数据库的名词：

数据库Database、数据表Tables、数据字段、行row、列column。

5.操作MySQL的方式：

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

二、基本操作命令

SQL（Structure query language）结构化查询语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）。操作数据库的步骤分为连接、打开库、操作、关闭退出。

1.SQL语句中的快捷键：

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

2.通过命令行连接MySQL：

mysql -h localhost -u root -p -P 3306
-h：服务器地址（可省略，默认为localhost本机）
-u：登录帐号
-p：回车后输入密码
-P：（大写）端口号（可省略，默认为3306）
一般写法：mysql -u root -p

mysql -u root -p //一般写法

3.数据库语法的特点：

SQL 语句可以换行，要以分号结尾；
命令不区分大小写，关键字和函数建议用大写；
如果提示符为 '> 那么需要输入一个 ’ 然后回车；
命令打错了换行后不能修改，可以用 \c 取消。

4.数据库操作：

查看数据库：show databases;
创建数据库：create database 库名 default charset=utf8mb4;
删除数据库：drop database 库名;（删库有风险，操作需谨慎）
打开数据库：use 库名;

show databases; #查看数据库
create database mingdan default charset=utf8mb4; #创建数据库
use mingdan; #打开数据库
drop database mingdan; #删除数据库

5.数据表操作：

数据库管理系统中，可以有很多库，每个数据库中可以包括多张数据表。其中我们把每一项称为字段，把每一行称为一条记录。
查看表：show tables;
创建表（无论表是否存在都执行）：create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8mb4;
创建表（表不存在则创建，存在则不执行）：create table if not exists 表名(字段1 类型,字段2 类型)engine=innodb default charset=utf8mb4;
删除表：drop table 表名;
表结构：desc 表名;
查看建表语句：show create table 表名;

create table if not exists users( /*创建表*/
	id int not null, /*id为int类型不能为空*/
	name varchar(4), /*name为字符串最大长度为4*/
	age tinyint, /*age为tinyint类型*/
	sex enum('男','女') /*sex为枚举类型*/
)engine=innodb default charset=utf8mb4;
show tables; #查看表
desc users; #表结构
show create table users; #查看建表语句
drop table users; #删除表

6.数据操作（增删改查）：

插入：
insert into 表名 values(值1,值2,值3);
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);
查询：
select * from 表名;
select 字段1,字段2,字段3 from 表名;
select * from 表名 where 字段=某个值;
修改：
update 表名 set 字段=某个值 where 条件;
update 表名 set 字段1=值1,字段2=值2 where 条件;
update 表名 set 字段=字段+值 where 条件;
删除：
delete from 表名 where 字段=某个值;

insert into users values(10,'张三',23); #插入一条数据，字段必须按照定义时的顺序全部指定
insert into users(id,name,age) values(10,'张三',23); #插入一条数据
insert into users(name,id,age) values('李四',11,28),('王五',11,18); #插入多条数据
select * from users; #查询所有数据的所有字段
select id,name from users; #查询所有数据的指定字段
select * from users where id=11; #查询满足指定条件数据的所有字段
update users set age=30; #设置所有数据的单个字段
update users set age=23 where name='张三'; #设置满足指定条件数据的单个字段
update users set age=18,id=12 where name='王五'; #设置满足指定条件数据的多个字段
delete from users where name='李四'; #删除满足指定条件的数据
delete from users; #删除所有数据

7.退出MySQL：

exit; quit; \q #三选一即可

三、基本数据类型

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

1.字符串数据类型：

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

定长串char

1.接受长度固定的字符串，其长度是在创建表时指定的，定长列不允许存储多于指定长度字符的数据。
2.指定长度后，就会分配固定的存储空间用于存放数据。
例如：char(7)不管实际插入多少字符，它都会占用7个字符位置。

变长串varchar

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

注意

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

变长文本类型存储Text

2.数值类型：

数值数据类型存储数值，MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多。
与字符串不一样，数值不应该括在引号内。

decimal

例如：decimal(5, 2)表示数值总共5位，小数占2位。
MySQL中没有专门存储货币的数据类型，一般情况下使用decimal(8, 2)。

tinyint

1字节（8位），无符号0-255，有符号-128-127。

int

4字节，无符号0-42亿，有符号-21亿-21亿。

float

有符号与无符号

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号，有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。
默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字。

注意

如果将邮政编码类似于01234存储为数值类型，则保存的将是数值1234，此时需要使用字符串类型。
那么，手机号应该用什么进行存储呢？
答：一般采用11位定长字符串char来存储。

3.日期和时间类型：

MySQL使用专门的数据类型来存储日期和时间值。

datetime

8字节，从1000-01-01 00:00:00到9999-12-31 23:59:59。

4.二进制数据类型：

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。但是通常情况下，不会使用二进制数据类型进行多媒体数据的存储。

5.表的字段约束：

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

6.主键：

1.表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值；
2.应该总是定义主键虽然并不总是都需要，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。
要求：
记录一旦插入到表中，主键最好不要再修改；
不允许NULL；
不在主键列中使用可能会更改的值，例如：如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键；
自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
可以使用多个列作为联合主键，但联合主键并不常用，使用多列作为主键时，所有列值的组合必须是唯一的。

7.MySQL的运算符：

算术运算符：+、-、*、/、%
比较运算符：=、>、<、>=、<=、!=
数据库特有的比较：in（是否在范围内）、not in（是否不在范围内）、is null（是否为空）、is not null（是否不为空）、like、between、and
逻辑运算符：and、or、not
like：支持特殊符号 %（任意数量的任意字符）和 _（任意一位字符）

四、数据表的操作

1.创建表：

语法格式：create table 表名(字段名, 类型, [字段约束], …);

/*以下创建一个users的表*/
create table users(
/*创建ID字段，为正整数，不允许为空，主键自动递增*/
id int unsigned not null primary key auto_increment,
/*创建存储名字的字段，为字符串类型，最大长度5个字符，不允许为空*/
username varchar(5) not null,
/*创建存储密码的字段，固定长度32位字符，不允许为空*/
password char(32) not null,
/*创建年龄字段，不允许为空，默认值为20*/
age tinyint not null default 20
)engine=innodb default charset=utf8mb4;

查看表结构：desc users;
查看建表语句：show create table users;
创建表的基本原则：
表名和字段名尽可能的符合命名规范，并且最好能够见名之意；
表中数据必须有唯一标示，即主键定义，无特殊情况，主键都为数字并自增即可；
表中字段所对应的类型设置合理，并限制合理长度；
表引擎推荐使用innodb，并无特殊情况都要求为utf8或者utf8mb4的字符编码。

2.修改表结构：

语法格式：alter table 表名 action(更改的选项);
添加字段：alter table 表名 add 添加的字段信息;

#在users表中追加一个num字段
alter table users add num int not null;
#在指定字段后面追加字段在users表中age字段后面添加一个email字段
alter table users add email varchar(50) after age;
#在指定字段后面追加字段，在users表中age字段后面添加一个phone字段
alter table users add phone char(11) not null after age;
#在表的最前面添加一个字段
alter table users add abc int first;

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

alter table users drop abc;

修改字段：alter table 表名 change|modify 被修改的字段信息;
change：可以修改字段名，modify：不能修改字段名。

#修改表中的num字段类型，使用modify不修改表名
alter table users modify num tinyint not null default 12;
#修改表中的num字段为int并且字段名为mn
alter table users change num mn int;

注意：一般情况下，无特殊要求，不要轻易修改表结构。

3.修改表名：

语法：alter table 原表名 rename as 新表名;

4.更改表中的自增的值：

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

5.修改表引擎：

推荐在定义表时，表引擎为innodb，通过查看建表语句获取当前的表引擎。
通过查看建表语句获取当前的表引擎：show create table 表名;
直接查看当前表状态信息：show table status from 库名 where name = ‘表名’\G;
修改表引擎语句：alter table users engine = ‘myisam’;

6.删除表：

drop table 表名;

五、数据库的表引擎

服务器处理客户端请求：

其实不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？客户端可以向服务器发送增删改查各类请求，我们这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

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

存储引擎：

MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能， MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。
存储引擎以前叫做表处理器，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。
为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像读取索引第一条内容、读取索引下一条内容、插入记录等。
所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。
MySQL支持非常多种存储引擎：

其中最常用的是InnoDB和MyISAM。

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的行锁是基于索引建立，如果索引失效或者没有使用索引，那么会由行锁升级为表锁。

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

六、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;

七、数据的DML操作

数据的DML操作：添加数据、修改数据、删除数据。

1.添加数据：

格式：insert into 表名[(字段列表)] values(值列表…);

#标准添加（指定所有字段，给定所有的值）
insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');
#指定部分字段添加值
insert into stu(name,classid) value('lisi','lamp138');
#不指定字段添加值（字段必须按照定义时的顺序全部指定）
insert into stu value(null,'wangwu',21,'w','lamp138');
#批量添加值
insert into stu values
(null,'zhaoliu',25,'w','lamp94'),
(null,'uu01',26,'m','lamp94'),
(null,'uu02',28,'w','lamp92');

2.修改数据：

格式：update 表名 set 字段1=值1,字段2=值2,字段n=值n… where 条件;

#将id为11的age改为35，sex改为m值
update stu set age=35,sex='m' where id=11;
#将id值为12和14的数据值sex改为m，classid改为lamp92（以下两行等价）
update stu set sex='m',classid='lamp92' where id=12 or id=14;
update stu set sex='m',classid='lamp92' where id in(12,14);

3.删除数据：

格式：delete from 表名 [where 条件];

#删除stu表中id值为100的数据
delete from stu where id=100;
#删除stu表中id值为20到30的数据
delete from stu where id>=20 and id<=30;
#删除stu表中id值为20到30的数据（等价于上面写法）
delete from stu where id between 20 and 30;
#删除stu表中id值大于200的数据
delete from stu where id>200;

八、数据库导入导出和授权

1.数据库数据导出：

不要进入mysql，然后输入以下命令导出某个库中的数据。

mysqldump -u root -p mingdan > ~/Desktop/code/mingdan.sql

导出一个库中所有数据，会形成一个建表和添加语句组成的sql文件。
之后可以用这个sql文件到别的库，或着本机中创建或回复这些数据。

2.将数据库中的表导出：

不要进入mysql，然后输入以下命令导出某个库中指定的表的数据。

mysqldump -u root -p mingdan users > ~/Desktop/code/mingdan-users.sql

3.数据导入：

把导出的sql文件数据导入到mysql数据库中。
在新的数据库中导入备份的数据，导入导出的sql文件。

mysql -u root -p ops < ./mingdan.sql

把导出的表sql导入数据库。

mysql -u root -p ops < ./mingdan-users.sql

4.权限管理：

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。
可以给不同的用户或者项目创建不同的mysql用户，并适当的授权，完成数据库的相关操作，这样就一定程度上保证了数据库的安全。
创建用户的语法格式：
grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;
示例：
在mysql中创建一个zhangsan用户，授权可以对mingdan这个库中的所有表进行添加和查询的权限。
grant select,insert on mingdan.* to zhangsan@’%’ identified by ‘123456’;
用户lisi，密码123456，可以对mingdan库中的所有表有所有操作权限。
grant all on mingdan.* to lisi@’%’ identified by ‘123456’;
删除用户：
drop user ‘lisi’@’%’;