MYSQL入门基础知识

港控/mmm° 2023-02-20 10:46 4阅读 0赞

### 文章目录 ###

*  一、数据库安装
 *  二、数据类型
 *   *  1、数值类型
     *  2、日期和时间类型
     *  3、字符串类型
 *  三、常用命令
 *   *  1、基本术语
     *  2、常用命令
 *  四、mysql事务
 *   *  1、事务的四个特性（acid）
     *  2、事务的隔离级别
     *  3、MYSQL 事务处理主要有两种方法：
 *  五、索引
 *   *  1、索引的优缺点：
     *  2、索引的分类
 *  六、如何防止SQL注入

# 一、数据库安装 #

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

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

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

![在这里插入图片描述][2020062610370881.png_pic_center]  
链接：https://pan.baidu.com/s/14u\_CnbfC185K2tR2krVmhQ  
提取码：gkw7  
这个是该软件的压缩包，有需要的自提。

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

创建一个新数据库：

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

# 二、数据类型 #

## 1、数值类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70]

## 2、日期和时间类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 1]

## 3、字符串类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 2]  
**注意**：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;

![在这里插入图片描述][20200626103733700.png]

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

![在这里插入图片描述][20200626103733301.png]  
![在这里插入图片描述][20200626103733788.png]

> 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

![在这里插入图片描述][20200626103733987.png]

> JOIN:
> 
> LEFT JOIN

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

![在这里插入图片描述][20200626103734152.png]

> JOIN:
> 
> RIGHT JOIN

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

![在这里插入图片描述][20200626103734146.png]

> 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、事务的隔离级别 ##

<table> 
 <thead> 
  <tr> 
   <th>事务隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交（read-uncommitted）</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>不可重复读（read-committed）</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>可重复读（repeatable-read）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>串行化（serializable）</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

**名词解释：**

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就相当于一个人力三轮车。

![img][]

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

![img][img 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 3]

## 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注入。**

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

[2020062610370881.png_pic_center]: https://img-blog.csdnimg.cn/2020062610370881.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200626103734489.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200626103734109.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200626103734287.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ==,size_16,color_FFFFFF,t_70
[20200626103733700.png]: https://img-blog.csdnimg.cn/20200626103733700.png
[20200626103733301.png]: https://img-blog.csdnimg.cn/20200626103733301.png
[20200626103733788.png]: https://img-blog.csdnimg.cn/20200626103733788.png
[20200626103733987.png]: https://img-blog.csdnimg.cn/20200626103733987.png
[20200626103734152.png]: https://img-blog.csdnimg.cn/20200626103734152.png
[20200626103734146.png]: https://img-blog.csdnimg.cn/20200626103734146.png
[img]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zczEuYmRzdGF0aWMuY29tLzcwY0Z2WFNoX1ExWW54R2twb1dLMUhGNmhoeS9pdC91PTI1NDkzNDAzMiwyNDQyMjc3NDE1JmZtPTI2JmdwPTAuanBn?x-oss-process=image/format,png
[img 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zczAuYmRzdGF0aWMuY29tLzcwY0Z2SFNoX1ExWW54R2twb1dLMUhGNmhoeS9pdC91PTI1NjExMjQwODEsMzY3MDY4NDI0OSZmbT0yNiZncD0wLmpwZw?x-oss-process=image/format,png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200626103736607.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTA4NTEwOQ==,size_16,color_FFFFFF,t_70