万字长文总结MySQL关系型数据库
数据库介绍
什么是数据库？
数据库的英文单词：data base，简称DB。数据库本质就是一个文件系统，它可以按照特定的格式把数据存储起来，可以方便对存储的数据进行增删改查操作。
数据库的分类
目前数据库总共分为两个大类:
- 关系型数据库：是建立在关系模型基础上的数据库。（MySQL、Oracle、DB2、SQL Server等等）。
- 非关系型数据库（NO SQL）：通常指数据之间无关系的数据库。（monggodb、redis等等）。
数据库服务器、数据库和数据表的关系
数据库服务器是一台安装了一个数据库管理系统（比如： MySQL）的主机，通常会开放一个远程连接的端口(例如MySQL的3306端口)来对外提供数据服务，通过数据库管理系统(MySQL)可以创建并管理管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
在一个数据库中可以创建多张数据表，这些数据表是真正存储数据的载体。表的每一行称为一条记录（Record）。
SQL语言简介
SQL(Structured Query Language)是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。
虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。 也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。
SQL语言定义了这么几种基础操作数据库的能力：
- DDL：Data Definition Language :DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。
- DML：Data Manipulation Language:DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。
- DQL：Data Query Language:DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。
SQL语法有如下的特点:
- 不区分大小写。
- 关键字、字段名、表名需要用空格或逗号隔开。
- 每一个SQL语句是用分号结尾。
- 语句可以写一行也可以分开写多行。
注意虽然SQL语言关键字不区分大小写，但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。
关系型数据库MySQL简介
MySQL是一款轻量级关系型数据库管理系统它免费、开源、适用于中大型网站，MySQL默认端口号：3306。由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来⼜被Oracle公司收购，⽬前属于Orac旗下。使⽤C和C++编写，并使⽤了多种编译器进⾏测试，保证源代码的可移植性。
MySQL⽀持多种操作系统和为多种编程语⾔提供了API。
安装及配置MySQL数据库
docker安装MySQL8
搜索MySQL镜像
1 | docker search mysql |
拉取MySQL镜像
1 | docker pull mysql:8 |
创建并运行MySQL容器
1 | docker run -p 3306:3306 --name mysqltest -e MYSQL_ROOT_PASSWORD=root -d mysql:8 |
参数说明：
- p 3306:3306 : 将容器的 3306 端口映射到主机的 3306 端口
- e MYSQL_ROOT_PASSWORD=root : 设置 mysql 登录密码
- d 后台运行容器，并返回容器 id
- mysql:8 运行的镜像名，也可替换成镜像 id
容器文件映射到本地目录（挂载）
在宿主机创建放置MySQL的配置文件的目录和数据目录可以防止容器被销毁导致数据被销毁问题。
- 宿主机创建数据目录和配置文件:
1
2sudo mkdir -p /usr/mysql/conf /usr/mysql/data /var/log/mysql
sudo chmod -R 777 /usr/mysql/ /var/log/mysql - 将测试容器里 MySQL 的配置文件复制到该路径。日后需改配置，直接在挂载路径的配置文件上修改即可
1 | docker cp mysqltest:/etc/mysql/my.cnf /usr/mysql/conf |
- 删除测试容器，4.创建新的 docker 容器并启动
1
2docker stop mysqltest
docker rm mysqltest1
sudo docker run -itd --name=mysql -p 3306:3306 --restart=always -e MYSQL_ROOT_PASSWORD=123456 -v /usr/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /usr/mysql/data:/usr/mysql/data -v /var/log/mysql:/var/log/mysql mysql:8
参数解释：
- -v : 挂载宿主机目录和 docker容器中的目录，前面是宿主机目录，后面是容器内部目录
- -d : 后台运行容器
- -p 3306:3306 : 将容器的 3306 端口映射到主机的 3306 端口
- -e MYSQL_ROOT_PASSWORD=root :环境参数，MYSQL_ROOT_PASSWORD设置root用户的密码
- mysql:8 运行的镜像名，也可替换成镜像 id
- –restart=always:容器自动启动
查看MySQL版本
方法1：
进入MySQL容器中使用命令：
1 | mysql -V |
方法2：
如果已经登录了MySQL ，则可以登陆MySQL之后使用内置命令
1 | mysql> select version(); |
方法3：
同样登录MySQL，使用内置命令
1 | mysql> status; |
或者
1 | mysql> \s |
解决远程无法连接MySQL问题
配置MySQL8支持远程连接
- 进入容器内部
1 | docker exec -it mysql /bin/bash |
- 连接MySQL
1 | mysql -uroot -p123456 |
- 使用MySQL库
1 | use mysql; |
- 修改访问主机以及密码等，设置为所有主机可访问
1 | ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; |
这里的密码为设置远程连接时的登录密码。
- 刷新
1 | flush privileges; |
使用Navicat等远程数据库连接工具连接数据库时就可以正常连接了。
检查防火墙是否允许访问该端口
如果MySQL设置了允许远程进行访问，但还是无法通过远程进行连接，那么就要检查主机的防火墙是否是将MySQL监听的端口是否关闭了。
1.. 查看防火墙状态:
1 | sudo ufw status |
- 开放防火墙3306端口:
1
sudo ufw allow 3306
MySql相关配置
查看安装文件路径
1 | which mysql |
查找配置文件my.cnf的位置
如果MySQL已经启动了，通过查看MySQL的线程，查看是否有明确指定加载my.cnf文件的路径。
1 | ps -aux | grep mysql | grep 'my.cnf' |
如果查询不到，则MySQL启动时会读取安装目录根目录以及默认目录下的my.cnf文件。首先确认服务器是否有my.cnf文件。
1 | locate my.cnf |
确认有my.cnf文件之后，查看MySQL启动时读取配置文件的默认目录。
1 | mysql --help|grep 'my.cnf' |
my.cnf配置文件详解
1 | [mysqld] |
MySQL用户及权限管理
添加用户
在MySQL中添加用户使用CREATE USER 和 GRANT就可以了。 例如创建一个普通用户-zhangsan. 然后赋予它一下简单的权限:
1 | CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'your_password'; |
这样， 你就可以使用MySQL -u xxx -p 进行指定用户的登录了. 如果，你想创建一个管理员账户的话， 代码就更简单了。
1 | CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_ps'; |
如果你想检查， 你创建的用户的权限对不对时， 可以使用:
1 | SHOW GRANTS FOR 'admin'@'localhost'; |
修改用户密码
有很多种方法， 简单介绍两种. 一种是使用SET，一种是使用ALERT(v5.7.6).
SET 修改密码应该算是比较常用的，使用SET直接修改密码即可，格式为:
1 | SET PASSWORD FOR 'zhangsan'@'localhost' = PASSWORD('your_ps'); |
上面那种方法，适用于root的用户进行修改， 如果你想修改自己的密码的话，直接使用:
1 | SET PASSWORD = PASSWORD('your_ps'); |
使用ALERT的相关语法也可以修改密码:
1 | ALERT USER 'zhangsan'@'localhost' IDENTIFIED BY 'your_ps'; |
如果你不想输，‘zhangsan‘@’localhost 这一串的话， 可以直接使用USER()进行代替:
1 | ALERT USER USER() IDENTIFIED BY 'your_ps'; |
另外， 如果你只想在shell 中直接修改的话，可以直接使用:
1 | mysqladmin -u user_name -h host_name password "new_password" |
删除用户
在mysql.user中查看用户信息:
1 | `SELECT User FROM mysql.user |
选择你想要删除的用户名，直接drop就行了:
1 | DROP USER 'zhangsan'@'localhost'; |
用户相关命令
– 创建用户
1
CREATE USER zhangsan IDENTIFIED BY '123456'
– 删除用户
1
DROP USER zhangsan
– 修改当前用户密码
1
SET PASSWORD = PASSWORD('666888')
– 修改指定用户密码
1
SET PASSWORD FOR zhangsan = PASSWORD('666888')
– 重命名用户
1
RENAME USER zhangsan to wangwu
– 用户授权(授予全部权限，除了给其他用户授权)
1
GRANT ALL PRIVILEGES on *.* TO wangwu
– 查询权限
1
SHOW GRANTS FOR wangwu
– 查看root用户权限
1
SHOW GRANTS FOR root@localhost
– 撤销权限
1
REVOKE ALL PRIVILEGES ON *.* FROM wangwu
MySQL数据库的基本操作
连接数据库
进入MySQL命令提示符:
1 | mysql -uroot -pxxx |
退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：
1 | mysql> exit |
选择数据库
- 查看所有的数据库:
1 | show databases; |
- 切换到某个数据库:
1 | use 数据库名; |
创建数据库
1 | create database 数据库名 charset=utf8; |
数据库名字，它的名字必须是唯一的，不能和其它数据库重名。
删除数据库
1 | drop database 数据库名; |
MySQL数据表的基本操作
数据类型
常见的数据类型:
- 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
- 浮点数类型：FLOAT、DOUBLE、DECIMAL
- 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
- 日期类型：Date、DateTime、TimeStamp、Time、Year
- 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
整型
| MySQL数据类型 | 含义（有符号） |
|---|---|
| tinyint(m) | 1个字节 范围(-128~127) |
| smallint(m) | 2个字节 范围(-32768~32767) |
| mediumint(m) | 3个字节 范围(-8388608~8388607) |
| int(m) | 4个字节 范围(-2147483648~2147483647) |
| bigint(m) | 8个字节 范围(+-9.22*10的18次方) |
取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
浮点型(float和double)
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型 8位精度(4字节) m总个数，d小数位 |
| double(m,d) | 双精度浮点型 16位精度(8字节) m总个数，d小数位 |
定点数
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。
字符串(char,varchar,_text)
| MySQL数据类型 | 含义 |
|---|---|
| char(n) | 固定长度，最多255个字符 |
| varchar(n) | 固定长度，最多65535个字符 |
| tinytext | 可变长度，最多255个字符 |
| text | 可变长度，最多65535个字符 |
| mediumtext | 可变长度，最多2的24次方-1个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 |
char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，
3.char类型的字符串检索速度要比varchar类型的快。
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text，在都创建索引的情况下，text的索引似乎不起作用。
二进制数据(_Blob)
1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集，_BLO不用指定字符集。
日期时间类型
| MySQL数据类型 | 含义 |
|---|---|
| date | 日期 ‘2021-12-2’ |
| time | 时间 ‘12:25:36’ |
| datetime | 日期时间 ‘2021-12-2 22:06:44’ |
| timestamp | 自动存储记录修改时间 |
若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。
创建数据表
常规创建
CREATE TABLE 语句的基本语法如下：
1 | CREATE TABLE IF NOT EXISTS `test_table`( |
CREATE TABLE 是 SQL 命令，告诉数据库你想创建一个新的表，IF NOT EXISTS表示如果要创建的表存在，则直接返回，不在重新创建该表。它后面紧跟的 table_name 是表的名字。然后在括号中定义表的列，以及每一列的类型。
PRIMARY KEY 关键字用来指明表的主键。
某些字段使用了 NOT NULL 约束，表名在插入数据时这些字段不能为 NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT 约束用来将某个字段的值设置为自动增长的序列。
DEFAULT 约束用来设置字段的默认值。
PRIMARY KEY 用来设置表的主键。
IF NOT EXISTS表示，如果我们当前数据库中已经存在一个同名的表将不会执行改语句，避免了系统报错。
COMMENT 为该字段添加注释，后面的字符串为注释内容属性添加注释。
ENGINE=InnoDB DEFAULT CHARACTER=utf8;是数据库默认的可以不用写，这句是指：数据库引擎使用的是InnoDB， 默认的字符编码是utf8。
示例:
- 最简单的：
1
CREATE TABLE t1(id int not null,name char(20));
- 带主键的：
1
CREATE TABLE t1(id int not null primary key,name char(20));
- 复合主键
1
CREATE TABLE t1(id int not null,name char(20),primary key (id,name));
- 带默认值的：
1
CREATE TABLE t1(id int not null default 0 primary key,name char(20) default '1');
复制表创建新表
1 | create table 目标表 like 源表 |
将tableA的部分数据拿来创建tableB
1 | create table <tableB>(id int(10),name varchar(20)); |
查看表的字段信息
MySQL命令：
1 | desc 数据表名; |
关键字属性
| MySQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集 |
修改数据表字段
随着开发进行 之前建好的表很可能不满足未来需求，所以对表的修改是必须的。同样，修改表也有通用语句：
1 | ALTER TABLE <表名> [修改选项] |
添加字段：
1
2
3
4ALTER TABLE <表名> ADD COLUMN <列名> <类型> ... //-- 在末尾添加字段
ALTER TABLE <表名> ADD COLUMN <列名> <类型> ... first //-- 在开头添加字段
ALTER TABLE <表名> ADD COLUMN <列名> <类型> ... after `name` //在指定字段之后添加字段
ALTER TABLE <表名> ADD COLUMN <列名> <类型> ...修改字段名：
1
ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列名> <新列类型> ...
优化(修改)字段类型:
1
ALTER TABLE <表名> MODIFY COLUMN <列名> <类型> ...
删除字段:
1
ALTER TABLE <表名> DROP COLUMN <列名> ...
修改表名:
1
ALTER TABLE <表名> RENAME TO <新表名>
删除数据表字段
注意 使用 DROP TABLE 命令时一定要非常小心，因为一旦删除了表，那么该表中所有的信息将永远丢失。 语法 DROP TABLE 语句的基本语法如下：
1 | DROP TABLE table_name; |
table_name 表示要删除的数据表的名字。
1、当你不再需要该表时， 用 drop; 2、当你仍要保留该表，但要删除所有记录时， 用 truncate; 3、当你要删除部分记录或者有可能会后悔的话， 用 delete。
MySQL数据的基本操作
插入数据到表
INSERT语句的基本语法是：
1 | insert into <表名> (字段1, 字段2, ...) values (值1, 值2, ...); |
注意到我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。 要注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致。 还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(…)包含的一组值：
一次性添加多条新记录:
1 | insert into students (class_id, name, gender, score) values |
删除表中数据
DELETE语句的基本语法是：
1 | delete from <表名> where ...; |
修改表中数据
1 | update <表名> set 字段1=值1, 字段2=值2, ... where ...; |
MySQL查询表中数据
查询所有的数据
1 | SELECT * FROM table_name; |
查询指定字段
1 | SELECT f_name, f_price FROM table_name; |
按条件查询
1 | SELECT * FROM students WHERE score >= 80 |
SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是。 其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩，因此，score >= 80就筛选出了指定条件的记录。
满足多个条件 AND
条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：
1 | SELECT * FROM students WHERE score >= 80 AND gender = 'M' |
满足某个条件 OR
<条件1> OR <条件2>，表示满足条件1或者满足条件2。例如，把上述AND查询的两个条件改为OR，查询结果就是“分数在80分或以上”或者“男生”，满足任意之一的条件即选出该记录：
1 | SELECT * FROM students WHERE score >= 80 OR gender = 'M' |
满足范围 IN
1 | SELECT * FROM students WHERE score IN(60,100) |
条件查找优先级
如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。
查询结果排序
使用SELECT查询时，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。 按score从低到高:
1 | select id, name, gender, score from students order by score; |
按score从高到低:
1 | select id, name, gender, score from students order by score desc; |
分页查询
分页 使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。
要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。
因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT
分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：
- LIMIT总是设定为pageSize；
- OFFSET计算公式为pageSize * (pageIndex - 1)。 这样就能正确查询出第N页的记录集。
聚合函数查询
在数据库查询过程中，不仅只返回数据的基础信息，有时还需对这些数据进行统计和汇总。MySQL 提供了聚合函数，用于实现这些高级功能。 聚合函数用于对一组值进行计算并返回一个汇总值，使用聚合函数可以统计记录行数、计算某个字段值的总和以及这些值的最大值、最小值和平均值等。
| 函数名称 | 功能 |
|---|---|
| sum | 返回选取的某列值的总和 |
| max | 返回选取的某列的最大值 |
| min | 返回选取的某列的最小值 |
| avg | 返回选取的某列的平均值 |
| count | 返回选取的某列或记录的行数 |
COUNT
– 统计班级共有多少同学
1
SELECT COUNT(*) FROM student;
– 统计班级收集的 qq_mail 有多少个，qq_mail 为 NULL 的数据不会计入结果
1
SELECT COUNT(qq_mail) FROM student;
SUM
– 统计数学成绩总分
1
SELECT SUM(math) FROM exam_result;
– 不及格 < 60 的总分，没有结果，返回 NULL
1
SELECT SUM(math) FROM exam_result WHERE math < 60;
AVG
– 统计平均总分
1
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
MAX
– 返回英语最高分
1
SELECT MAX(english) FROM exam_result;
MIN
– 返回 > 70 分以上的数学最低分
1
SELECT MIN(math) FROM exam_result WHERE math > 70;
多表查询
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
内连接查询
内连接（INNER JOIN) 使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新记录。简而言之，查找出同时存在在不同表中的关联数据形成结果表。
1 | SELECT 字段1,字段2,字段3,…… FROM 表名1 INNER JOIN 表名2 ON 关联条件； |
等同于:
1 | SELECT 字段1,字段2,字段3,…… FROM 表名1,表名2 WHERE 关联条件； |
例如，查询所有员工信息和对应的部门信息:
1 | SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`; |
等同于:
1 | SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`; |
INNER JOIN 连接三个数据表的用法：
1 | SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号 |
注意事项
- 如果某字段在多表中都有，则以”表名.列名”限定别名；
- 与INNER JOIN组合使用ON子句，而不是WHERE。ON和WHERE后面的指定条件相同， WHERE子句定义条件更简单明了，但某些时候会影响查询性能，而INNER JOIN语法是ANSI SQL的标准规范，能够确保不忘记连接条件。
外连接查询
左外连接
左连接从左表(t1)取出所有记录，与右表(t2)匹配。如果没有匹配，以null值代表右边表的列。
基本语法：
1 | SELECT * FROM [左表] LEFT OUTER JOIN [右表] on [左表].[字段]=[右表].[字段]; |
示例:
1 | SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`; |
右外连接
右连接从右表(t2)取出所有记录，与左表(t1)匹配。如果没有匹配，以null值代表左边表的列。
基本语法：
1 | SELECT * FROM [左表] RIGHT JOIN [右表] on [左表].[字段]=[右表].[字段]; |
示例:
1 | SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`; |
SQL注入
什么是SQL注入
SQL注入（SQL Injection）是一种常见的Web安全漏洞，主要形成的原因是在数据交互中，进行数据库操作时，没有做严格的判断，导致其传入的“数据”拼接到SQL语句中后，被当作SQL语句的一部分执行。
从而导致数据库受损（被脱库、被删除、甚至整个服务器权限陷）。
导致SQL注入的原因
SQL注入主要原因是程序员在开发用户和数据库的系统时没有对用户输入的字符串进行过滤、转义、限制或处理不严谨，导致攻击者可以通过精心构造的字符串去非法获取到数据库中的数据。
如何防止SQL注入
- 严格限制 Web 应用的数据库的操作权限，给此用户提供仅仅能够满足其工作的最低权限，从而最大限度的减少注入攻击对数据库的危害。
- 检查输入的数据是否具有所期望的数据格式，严格限制变量的类型，例如使用 regexp 包进行一些匹配处理，或者使用 strconv 包对字符串转化成其他基本类型的数据进行判断。
- 对进入数据库的特殊字符（’”\ 尖括号 &*; 等）进行转义处理，或编码转换。Go 的 text/template 包里面的 HTMLEscapeString 函数可以对字符串进行转义处理。
- 在应用发布之前建议使用专业的 SQL 注入检测工具进行检测，以及时修补被发现的 SQL 注入漏洞。网上有很多这方面的开源工具，例如 sqlmap、SQLninja 等。
- 避免网站打印出 SQL 错误信息，比如类型错误、字段不匹配等，把代码里的 SQL 语句暴露出来，以防止攻击者利用这些错误信息进行 SQL 注入。
SQL事务
什么是事务
事务（transaction）是作为单个逻辑单元执行的一系列操作。多个操作作为一个整体向系统提交，要么都执行，要么都不执行。 MySQL 事务主要用于处理操作量大，复杂度高的数据。
例如，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
事务的特性
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- 原子性： 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性： 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- 隔离性： 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性： 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务的控制
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
事务的控制命令语句
- BEGIN 或 START TRANSACTION 显式地开启一个事务；
- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier 把事务回滚到标记点；
- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MySQL导入导出数据
导出数据
导出整个数据库中的所有数据
1 | mysqldump -u 用户名 -p密码 数据库名 > 导出的文件名 |
示例:
1 | mysqldump -u userName -ppassword dabaseName > test_db.sql |
结尾没有分号。test_db.sql最好加上路径名。
导出数据库中的某个表的数据
1 | mysqldump -u 用户名 -p密码 数据库名 表名> 导出的文件名 |
示例:
1 | mysqldump -u userName -ppassword dabaseName tableName > fileName.sql |
导出整个数据库中的所有的表结构
1 | mysqldump -u userName -ppassword -d dabaseName > fileName.sql |
导出整个数据库中某个表的表结构
1 | mysqldump -u userName -ppassword -d dabaseName tableName >fileName.sql |
导入数据
使用 MySQL 命令导入语法格式为：
1 | mysql -u用户名 -p密码 < 要导入的数据库数据sql文件 |
示例：
1 | mysql -uroot -p123456 < student.sql |
以上命令将将备份的整个数据库 student.sql 导入。
数据库表的设计原则
- id类型如果没有特殊要求，必须使用bigint unsigned，禁止使用int，即使现在的数据量很小。
- 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。 说明:如果预计2年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
- 表必备三个字段，id，create_time，update_time，其中id为主键类型为 bigint unsigned、单表时自增、步长为 1， create_time，update_time 为datetime 类型，前者现在时表示创建时间，后者过去分词表示更新时间。
- 表名，字段名必须使用小写字母或数字，且开头不能使用数字。
- 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint ( 1表示是，0表示否)。
- 小数类型为 decimal，禁止使用 float 和 double。 说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不 正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
- varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
- 不得使用外键与级联，一切外键概念必须在程序业务端解决。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴和死锁的风险；并且外键影响数据库的插入速度。
参考资料:
docker 安装mysql8
菜鸟教程
MySQL教程
廖雪峰SQL教程
一个小时学会MySQL数据库
MySQL详细学习教程
MySql教程
MySQL 有这一篇就够
MySQL基础教程15——多表查询