MYSQL入门基础知识

一、数据库安装

关于数据库安装部分可以参考菜鸟教程，这个是个不错的网站。

https://www.runoob.com/mysql/mysql-install.html

另外，关于数据库操作，可以使用Navicat工具进行数据库连接和操作。也可以使用IDEA自带的插件工具。

链接：https://pan.baidu.com/s/14u_CnbfC185K2tR2krVmhQ
提取码：gkw7
这个是该软件的压缩包，有需要的自提。

安装完之后，连接测试。具体步骤就不详细写了。比较简单。

创建一个新数据库：

# 创建一个数据（单行注释方法1）
create database test;
-- 使用一个数据库 （单行注释方法2）
use test;
-- 查看所有的数据库
show databases;
-- 删库跑路
drop database test;

二、数据类型

1、数值类型

2、日期和时间类型

3、字符串类型

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

三、常用命令

1、基本术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• **行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• **外键：**外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2、常用命令

增删改查系列：

-- 创建数据表
CREATE TABLE table_name (column_name column_type);
-- 删除数据表
DROP TABLE table_name ;
-- 插入数据
INSERT INTO table_name (field1,field2,....fieldN ) Values (value1,value2,...valueN);
-- 查询语句
SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]
-- 分页查询
SELECT * FROM table_name LIMIT (start-1)*PageSize,PageSize;
-- 删除语句
DELETE FROM table_name [WHERE Clause]
-- 更新语句
UPDATE table_name SET field1=new-value,field2=new-value [WHERE Clause] 

子句系列：

WHERE 数据库中的if条件

-- where 相当于if判断条件,后可以接AND,OR,BETWEEN...AND...;BINARY 用于区分大小写，mysql默认不区分
SELECT * FROM table_name WHERE BINARY name="Tom";

LIKE 由于存在 % 导致索引失效

-- LIKE 配合WHERE使用
SELECT * FROM table_name WHERE name LIKE '%om'
-- 索引失效
SELECT * FROM table_name WHERE name LIKE '%om'
-- 索引失效
SELECT * FROM table_name WHERE name LIKE '%om%'
-- 索引不失效，但实际上无法满足开发需求
SELECT * FROM table_name WHERE name LIKE 'om%'
/*
假设有一张表，其中id和name都是索引，id是主键索引，age不是索引
*/
-- 索引生效
SELECT id,name FROM table_name WHERE name LIKE '%om%'
-- 索引失效.由于存在非索引字段。
SELECT id,name,age FROM table_name WHERE name LIKE '%om%'

总结：

1、实际开发中，禁止使用SELECT * From table_name进行查询。

2、LIKE以%开头会导致索引失效；使用覆盖索引解决之

DISTINCT 去重复值

SELECT DISTINCT fieldName FROM table_name;

UNION 集合操作

-- 用于合并两个或多个select结果集，不允许有重复值
SELECT teacher_name FROM class_one
	UNION
SELECT techer_name From class_two
-- 用于合并两个或多个select结果集，允许有重复值
SELECT teacher_name FROM class_one
	UNION ALL
SELECT techer_name From class_two

ORDER BY

-- 排序，DESC(降序);ASC(升序)
SELECT id,name,score FROM student ORDER BY score ASC(DESC);

GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
--举例 
SELECT * FROM student;

-- 计算男生女生的平均分
SELECT sex, AVG(score)FROM student GROUP BY sex;
-- 计算男生女生的总分
SELECT sex, SUM(score)FROM student GROUP BY sex;

HAVING 常用在GROUP BY 后面，相当于WHERE作用

-- 计算出男女的平均分，并返回女生的平均分结果
SELECT sex, AVG(score)FROM student GROUP BY sex HAVING sex='女';

JOIN:

INNER JOIN

-- join 默认是内连接，可以省略inner.
SELECT s.sex,s.name,c.class FROM student s
INNER JOIN class c
ON s.name = c.name

JOIN:

LEFT JOIN

-- 左连接，返回左表匹配的所有行，右表没有的数据用null值代替
SELECT s.sex,s.name,c.class FROM student s
LEFT JOIN class c
ON s.name = c.name

JOIN:

RIGHT JOIN

-- 右连接，返回右表匹配的所有行，左表没有的数据用null值代替
SELECT s.sex,s.name,c.class FROM student s
RIGHT JOIN class c
ON s.name = c.name

ALTER

-- 删除数据表中的字段
ALTER TABLE table_name DROP fieldName;
-- 增加数据表中的字段
ALTER TABLE table_name ADD fieldName int
-- 修改数据表中的字段:
-- 方法一：例如将fieldName varchar(10)修改为varchar(50)
ALTER TABLE table_name MODIFY fieldName varchar(50);
-- 方法二：例如将fieldName varchar(10)修改为newFieldName varchar(50)
ALTER TABLE table_name CHANGE fieldName newFieldName varchar(50);
-- 修改字段默认值
ALTER TABLE table_name ALTER fieldName SET DEFAULT values;
-- 修改表名字
ALTER TABLE table_name RENAME TO new_table_name;
-- 删除外键约束
ALTER TABLE table_name FOREIGN KEY keyName;

四、mysql事务

1、事务的四个特性（acid）

• 原子性： 一个事务中的所有操作，要么全部成功，要么都失败。如若中途发生错误，将会回滚。
• 一致性： 事务前和事务后，数据库的完整性没有被破坏，
• 隔离性： 数据库允许多个事务同时对数据库进行数据修改和读写的能力，隔离性可以预防多个事务并发执行时造成的数据不一致。
• 持久性： 事务处理结束后，对数据的修改就是永久的。

2、事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

名词解释：

1、脏读

当事务A进行了数据增加，尚未提交事务，此时，如果数据库设置的隔离级别为读未提交（即可以读到未提交的数据）的话，事务B进行SELECT操作，可以读到新增数据。但是如果事务A进行了回滚，而不是提交该更新操作，则该数据就是一条脏数据，该操作即为脏读。

2、不可重复读

事务A在对某个数据进行多次读取时，事务B对该数据进行了更新修改操作（UPDATE），导致多次读取同一数值时，结果不一致。可以看做是发生了质变。

3、幻读

事务A在对数据表进行读取操作时，事务B对该数据表进行了增删操作（INSERT/DELETE），导致多次读取同一数据时，前后内容数量上有差异。可以看做是发生了量变。

mysql默认的隔离级别是可重复读，而oracle默认的隔离级别是不可重复读。串行化虽然保证了数据的安全性，但是效率非常差，一般不会用。

3、MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

五、索引

索引可以看做是数据字典的目录，没有索引的设计和使用的mysql就相当于一个人力三轮车。

而如果索引建立且设计较好，mysql就相当于一个兰博基尼。

1、索引的优缺点：

优点：

• 索引大大减小了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机IO变成顺序IO
• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
• 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

2、索引的分类

主键索引： 根据主键建立的索引，加速查询，列值唯一(不能为null)，表中只能有一个

-- 其实就是主键。如果主键存在则默认为主键索引
ALTER TABLE table_name ADD PRIMARY KEY pk_index('col');

唯一索引： 加速查询，列值唯一，允许为null。不允许行中有重复值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

普通索引： 仅仅是用于构建索引，无限制。

ALTER TABLE 'table_name' ADD INDEX index_name('col');

组合索引： 多列值组成一个索引，专门用于组合搜索，列中不允许有null值。效率大于索引合并。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
-- 在对多列组合建立索引时，会遵循「最左前缀」原则。
-- 只要条件中都用到了三个索引，索引就生效，与col1、col2、col3的顺序位置无关
SELECT * FROM table_name WHERE col1=1 AND col3=3 AND col2=2;
-- col2出现了断点，col1索引会生效，col3索引不会生效
SELECT * FROM table_name WHERE col1=1 AND col3=3;
-- col1出现了断点，col2和col3索引不会生效
SELECT * FROM table_name WHERE col3=3 AND col2=2;
-- 范围值也算断点，col1出现了断点，col2和col3索引不会生效。例如：
SELECT * FROM table_name WHERE col1>1 AND col3=3 AND col2=2;

六、如何防止SQL注入

在实际开发过程中，由于一些参数是不确定的，因此我们经常会将sql语句进行拼接，那么，在拼接sql语句过程中，无法预知用户会输入什么样的内容，因此，会出现意想不到的结果。

防止SQL注入，我们需要注意以下几个要点：

• 1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
• 2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
• 3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
• 4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
• 5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
• 6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

我们常用的mybatis框架是如果做SQL防止注入的呢？

#{}：相当于JDBC中的PreparedStatement

${}：是输出变量的值

简单说，#{}是经过预编译的，是安全的；${}是未经过预编译的，仅仅是取变量的值，是非安全的，存在SQL注入。

所以我们尽可能的使用#{}进行占位。如果真要${}这个，就需要对输入内容进行严格的过滤。在银行系统中，要求较高，多为数据库的存储过程。