###### tags: `learn` `DataBase`

# MySQL

首先， 命令行连接至mysql方式和linux相同：

mysql -u 用户名 -p密码

**我在第一次尝试使用命令行时候失败了！为什么呢？**
> 因为你需要事先将mysql路径添加入环境变量阿混蛋！

---

## 总体结构


---

## 操作指令

### 数据库相关： database

#### 创建数据库：CREATE

CREATE DATABASE mine_03 character set utf8 collate utf8_bin；

#### 查看，删除数据库：SHOW , DROP
> 注：有时候创建的数据库名字若是内置量名，则会报错，比如我不能直接：
> CREATE DATABASE INT
> 这样就报错。但是如果加上反引号‘ ’就可以成功创建！

#### 备份/恢复数据库：mysqldump与source

> 实际上-B 不用写！！！
> 在这里我遇到一个问题，就是我在sql命令行里疯狂地报错！
> 但是实际上**备份根本就不是在sql命令行进行！是在dos命令行进行**！！！！！
恢复相对简单。在 mysql命令行执行即可。


---

### 单表相关

#### 创建表

> 自己使用命令行创建了密码表！


##### Mysql数据库常用的数据类型！（重要）


> 其中用的比较多的：
> 整数：int
> 小数：double, decimal（精度较高）
> 文本：char, varchar
> 日期：datetime

---

1. 整型

**注意， 整型若不指定unsigned， 则默认有符号！！！（也就是说会少一位来存符号！）**

> 创建一个test数据库用于测试！看看tinyint是否真的只存到这么大。
> 可以发现！超出范围，也就是说确实范围是被限制住了！

---

2. 小数类型


> 例： 创建一个表来看看double和decimal对付长位数小数！
> 
> 
> 可以很明显看出来， decimal这边给我没满20位的地方全部补上了0！！！！很棒，精度够高。

---

3. 字符串类型

> **注意，varchar虽然最长65535个字节，我们却不能填写65535！ 为什么呢？因为我们需要最大三个字节去记录我们字符串的大小！！！！）**
> 由于utf8是三个字节表示一个字符，所以如果是utf8就是最大21844个字符！

---

##### 字符串使用细节

> 上面也有提及，varchar是变长，而且我们输入的长度是字符长度，不是字节长度，要换算成字节后小于65532（比如，utf8 则乘以3因为是3字节为1字符；gbk则乘以2）

---

> 所以说varchar和char不同在于：它是可变长！
> 可变长体现在哪里？
> 1. 创建时最大长度虽是65535字节，但会根据编码类型而变为不等长的字符（比如，utf8 则乘以3因为是3字节为1字符所以utf8最长是21844；gbk则乘以2所以最长为32766）
> 2. 插入数据的时候，若数据长度没达到最大长度，多余空间除去1-3字节存放长度信息外，是可以分出去再利用的；而char就算未满最大长度，多余空间也会被占掉。

---

> char的优点：查询快！

---

---

4. 日期类型


> 案例： 创建一个试试
> 
> 
> 发现，时间戳不需要我们手动计入，会自动更新！！！！
> （这玩意儿用在记录我的消费记录或者密码记录之类的应该很有用！）

---

##### 练习


我的代码：

---

#### 修改表


应用：

> 我的
> 0.
>
> 1.
>
> 2.
>
> 3.
>
> 4.
>
> 5.
>
> 6.

> check：

老韩答案：

---

#### 数据库的CRUD


##### insert语句


案例：在这个基础上我加了一个add_time自动记录加入数据时间！（很有用感觉hhh）

> my code
> 
> 
> 

##### insert的注意事项


---

##### update语句


> 并未使用他的案例，而是把自己的拿来随便试试。
> my code：
> 
> 

---

##### delete语句

> 不写后面的where语句则会将表全部删除！！！要删除某一列还是用alter！！
> 

案例实现：先加入老妖怪记录再删除！！


---

##### select语句


尝试自己做个student表玩玩

**开始提升**


> my code：
> 1.
>
> 2.
>
> 3.前两步都用了别名！
>
> 课后很简单没什么搞的必要哈。



> my code：
> 因为数据不同，只是用差不多形式搞了下课后那几个（也就是条件查找）
> 
>
> 
>
> 


> 

---

### 函数

#### 统计函数

> 也就是说 count(*)是不会排除null的！！！！！

> 另外同理还有max 和 min函数等！就不写啦！

---

#### select中的过滤/分组函数 groupby



---

#### 字符串函数


示例：





> 按照字节返回！！！

> replace和其他语言一样，就实现替换功能！

> 练习：以首字母大写的形式返回所有学生的姓名！
>1. 
>
>2. 
>可以看出来，这些函数真的很灵活欸！

---

#### 数学函数


> 
> 这里的函数都很简单！！！

---

#### 日期函数


注意！ CURRENT_TIME(), TIMESTAMP(), NOW()完全等价！！！

> 案例，求求我还能活多少天（按80岁）
> 

---

#### 加密函数

> 注：PASSWORD()函数已经没有了哦！

==**示例：使用MD5加密存放密码！！！**==

> 注意，MD5()是不可逆的！查不到原密码！

---

#### 流程控制函数



> ==首先，尝试if！==
> if()是典型的三元表达式：若满足第一元，则输出第二元；否则输出第三元。
> 

> ==其次尝试IFNULL！==
> 这个函数也很简单，如果第一元为空则返回第二元；否则返回第一元。
> 
> 因为我第一个为空，所以全返回第二个辣！


---

### 多表查询（难点）

#### 复习单表的查询要点

首先我们创建了三张表！然后会根据这三张表进行慢慢的学习！

先注意一点！在sql中日期可以直接进行比较！！！

模糊查询：

==查询null不用 = null 而是 is null！==

==如何同时进行两次排序的查询？
按主次顺序写在后面就可以！==

==分页查询==



my code：

---

#### 多表查询部分


my code：



my code：

#### 自连接

my code：


---

#### 子查询


my code：


> my code：


==同样地，子查询出来的结果也可以作为临时表与其他结合进行结合查询！！！==

**==也就是说，子查询不仅可以用在WHERE句，也可以用在FROM句！==**

---

**all & any**

满熟悉了，all其实就是对比出来取最高的，而any就是对比仅仅去除最低的！

++示例：用all查最高！++

++示例：用any排除最低！++

---

**多列子查询**
由于我表里smith没有符合要求的这里我换成了WARD。
my code：

---

##### 子查询例题（综合提升

> 1.
> 
>
> my code：
> 
>

> 2.
> 
>
> my code：
> 

> 3.
> 
>
> my code：
> 

---

##### 表复制

> 每一次自我复制会让表的内容翻倍！也就是2的指数次方爆炸。
> 但是注意，==若表内有主键，自我复制就会报错！（毕竟主键不可重复）==

面试题：如何对表进行去重？（这个应该是指不带主键的表吧，不然没必要去）

my code：
首先，我们创建一张表用于测试。这里有个语法： create table xxx like xxx.
会创建和后者格式一样的空表！

之后，我们将emp表的内容复制入新建的test表中。

其次，我们再复制一遍test表内内容，这样表内就有重复了！


---

#### 合并子查询

> 第一眼会让人觉得直接用 OR！
> 注意哪里不同于OR？
> union all：不去重！！！不同于OR。
> 
> union：去重，很像OR。
> 

---

#### 外连接


> 为了搞明白，我们也创建这个表来试试！
>
> 
> 
> 

创建完成后，我们就可以开始尝试测试左右外连接！

my code：
> 1.左连接
> 
>
> 2.右连接
> 

> 课堂习题：
> 
> 1.右外连接
> 
> 2.左外连接
> 

---

### 约束


#### 键约束


创建一个带主键的表
> 会发现当主键重复时候就会报错！

==主键的细节==


==复合主键==

---

==非空 & 非重复约束==

---

==外键==


使用细节

> 注：==只有innoDB引擎才支持外键！！！！==

---

#### check约束


举例：尝试修改emp表的工资上下限并试试效果。

> 为啥？？？？？

那我新建一个表试试

> 可以发现是有用的！
> 但是，通过desc 查出来的信息却看不到？？？
> 

##### 综合案例


my code：
1.goods：

2.customer：
> 可以发现这个check约束生效！

3.purchase：

==总的来说，搞清楚谁是外键，就很简单！==

---

#### 自增长


自增长尝试（用于自动更新主键id）

> 很神奇地自动更新了！

==细节==

> 修改自增长！
> 
> 
> 

> 成功！相当于就是修改自增长的底数！！！

---

## 索引！（常问到哦！）


### 创建索引
> 注意，==创建索引后，仅仅对创建的索引的列的查询有加速效果！==
> **这是当然的，毕竟相当于给这个列加了个目录**。

### 索引机制

#### 1. 索引的效率问题
==面试常见问题==：（我网易就被问到了！！呜呜呜）
> 为什么用索引快？因为会形成索引的数据结构！比如二叉树！！！（当然，根据数据库课程来看，是B树和B+树啦，B树所有节点都有对应源文件的目录索引，而B+树仅叶节点有对应源文件目录索引。）
> 索引代价：每次进行dml（插入，修改与删除）时，索引的结构文件也会被修改，当然会花时间了！

#### 2. 索引种类

> 1. 主键自动成为索引！所以根据主键查询很快呀！
> 2. ==unique 属性也会自动成为索引，所以查起来也比较快。仅仅在InnoDB引擎下才可以用！==
> 3. 普通索引用的最多！因为我们并不总是根据唯一值查询！同时，普通索引由于其不唯一性，一定是稀疏索引！！毕竟我们定位到了某一个可能有重复项后还要继续查也有可能！
> 4. ==全文索引：用于MyISAM引擎！==

#### 3. 创建，修改及删除索引


show indexes from table_name;



---

## 事务（面试高频！）


> 事务的特性：ACID （from数据库md）
>
> 原子性Atomicity: 事务的一组更新操作原子不可分；要么都做，要么都不做。
>
> 一致性Consistency: 事务的操作状态应正确且符合一致性规则。
>
> 隔离性Isolation: 多个并发执行事务间互不影响。
>
> 持久性Durability: 已提交事务的影响时持久的，且被撤销事务的影响是可恢复的。

### 事务内容 & 细节 （注，事务仅仅在InnoDB中才有！！）


举例：（当然我已经很熟悉了）

> 尝试保存点！
>
> 设置保存点a，之后插入一条数据
> 
>
> 再设置保存点b，再插入一条数据
> 
>
> 我们回退至b保存点！
> 
>
> 再回退回a保存点！
> 

如果我们直接写rollback 就是全体撤销该事务！
反正，如果使用commit 就是提交！提交后就没法修改啦！

总之，要使用rollback就必须在开始前开启事务也就是 START TRANSACTION!!!

> 注意！！！只有InnoDB存储引擎才可以使用事务机制！！！！

---

### 事务的隔离级别


> 注意： 不可重复读指的是修改和删除；幻读指的是插入！
> 注意2：在mysql8.0里，查看隔离级别语句： SELECT @@transaction_isolation

> ==注意，幻读和不可重复读 是在事务已提交的基础上！==

==举例说明：==

> 我们可以使用两个控制台实现！
> 
>
> ==**1.使用语句查看当前隔离级别： SELECT @@transaction_isolation**==
> 
> 也就是说，我们的默认隔离级别是可重复读！对应上面的第三个级别！（不加锁但不会出现上面的几种问题）
>
> ==**2.修改其中一个控制台的隔离级别！SET SESSION TRANSCATION ISOLATION LEVEL READ UNCOMMITED；**==
> 直接设置到最低的隔离级别！
> 
> 
> 修改完成！
>
> ==**3.两边都开始事务！**==
> 
>
> ==**4.在隔离级别高的那边创建一张表！**==
> 
>
> 
> 可以发现，现在在两边查都是空表！
>
> ==**5.现在在隔离级别高的命令行里添加一条数据但是先不提交！**==
> 
>
> 我们用隔离级别低的那个看一下能不能检索到
> 
> ==明明还没提交，就能查到了！这就是脏读！==
>
> **==6.我们在隔离等级高的那个命令行进行一系列修改，再用低的查一查！==**
> **6.1 修改**
> 
> 
>
> **6.2 查询**
> 
> 发现不可重复读（读到修改内容）和幻读（读到添加内容）都有发生！！！
> ==注意，幻读和不可重复读 是在事务已提交的基础上！==

==可串行化：有加锁读功能==
如上图示意，若有其他终端在对表进行事务操作，那么隔离级别设置为可串行化的终端，在事务提交或撤回完成前无法读取那张表！但是一旦提交，就立马能看到了！好神奇哦


---

## 存储引擎 InnoDB & MyISAM （面试高频！）


使用 SHOW ENGINES 显示支持的引擎
> InnoDB：写的很清楚，支持事务，外键以及行级锁。
> MEMORY：存在内存的表，巨快但是通常用于临时表！


---

## 视图

### 基本原理
> 视图可以由一张表或多张表构成！
> 视图与原表的关系是相互对照；任意一侧的修改都会影响另一侧。

### 视图使用


> my code：
> 
> 

> 视图上的修改也会影响到原表。
> 我在原表的Engineer并非全大写，那么我们尝试通过update视图来修改原表。
> 
> 下面查原表：
> 
> 还真的是！

==视图的实践==


> my code：
> 
>
> 

---

## 用户管理！


> 创建用户并设置密码
> 
>
> 尝试用该用户登录
> 
> 成功！
> 删除用户：
> 

### 用户权限管理


==老师代码实例==

### 用户管理细节


---

## 恭喜你，看完了！（但是他的课程里没有讲触发器的知识，还有比较重要的数据库三范式，这些都可以参照隔壁DataBase的md文件。

---

## 习题


2.--------

3.--------

3.1

3.2（这里会用到if或者ifnull，我认为ifnull更合适题目要求就用了它）

4.--------

4.1

4.2（我用的not，当然也可以创建一个在该范围的子表然后排除掉）

4.3

4.4

4.5

5.--------

5.1

5.2

6.--------

6.1

6.2

6.3

6.4

6.5

6.6

6.7

6.8

6.9（迷惑？）

6.10

6.11（两种方法： 1.replace， 2. concat）
1.

2.

6.12

6.13

6.14

6.15

6.16

6.17

6.18

6.19（考察多排序）

6.20（考察多排序）

6.21

6.22

6.23

6.24

6.25

7.--------

7.1

7.2

7.3

7.4（外连接）

7.5

7.6

7.7

7.8

7.9

7.10

7.11

7.12

7.13

7.14

7.15

7.16

8.--------


==创建数据库1~2==





+++++++++++++++++++
==CURD3~4==

3.（查）
3.1

3.2

3.3

4.（改）
4.1

4.2


- [x] ==全部习题已自己动手做完！！！没有看答案！！！==