MySQL指南之基础知识

叁歲伎倆 2022-01-20 04:13 304阅读 0赞

> 个人所有文章整理在此篇，将陆续更新收录:[知无涯，行者之路莫言终（我的编程之路）][Link 1]

--------------------

#### 零、如何访问服务器上的mysql数据库 ####

##### 1.远程连接服务端的MySQL #####

> 开发3306端口（端口号为你服务器上的MySQL访问端口）

--------------------

##### 2.本地访问服务端 #####

> 这里貌似不允许连接

|-- 在服务端进入mysql语句执行： 开放权限----------------- 
    Grant all privileges on *.* to 'root'@'%' identified by '你的密码' with grant option;
    复制代码

--------------------

#### 一、关于MySQL的相关知识 ####

> 简单来说MySQL服务就是向若干个客户端`提供并操作数据`的软件

> mysql的结构

MySQL服务器中有若干数据库，数据库中又有若干表，表中又有若干记录，记录又有若干字段
    复制代码

> 记录映射实体在应用中使用

在Java中有一种数据类型叫List,也就是列表，对数据库的操作会将一张表通过实体类映射成对象列表
    其中一条记录对于List中的一个对象，对象的属性和表的字段往往一一对应，
    能够远程获取数据产生实体类列表，就可以填充客户端UI,这便让数据库在编程语言中有了价值
    这也使得前后端在应用层面实现分离，明确接口后，各端可以专注各自的业务
    复制代码

--------------------

##### 1.MySQL的文件目录 #####

> `将解压文件bin的路径配置到环境变量path下,以便在任意地方都可执行命令`

--------------------

##### 2.安装MySQL服务及基本指令 #####

# 安装MySQL 将会在服务里出现一个叫MYSQL57的服务
    mysqld install MYSQL57
    # 启动MySQL
    net start mysql
    # 停止MySQL
    net stop mysql 
    # 设置密码
    update mysql.user set authentication_string=password('xxxx') where user='root';
    # 数据库备份
    mysqldump -uroot -p mycode > D:\\backupSql\\android.sql
    # 数据库还原
    mysql -u root -p mycode < D:\\backupSql\\android.sql
    # 进入MySQl 全写 和 简写
    mysql --host=toly1994.com --user=root --port=3306 --password
    mysql -htoly1994.com -P3306 -uroot -p 
    # 退出
    quit 或 exit 或 \q
    # MySQL数据库版本
    mysql -V 或 mysql --version
    复制代码

--------------------

##### 3.MySQL的配置文件 #####

> my.ini 文件

[mysql]
    default-character-set=utf8  # 设置mysql客户端默认字符集
    [mysqld]
    port = 3306     #设置3306端口
    basedir=D:B\mysql-5.7.22-winx64         # 设置mysql的安装目录
    datadir=D:\B\mysql-5.7.22-winx64\data       # 设置mysql数据库的数据的存放目录
    max_connections=200         # 允许最大连接数
    character-set-server=utf8       # 服务端使用的字符集 默认为8比特编码的latin1字符集
    default-storage-engine=INNODB       # 创建新表时将使用的默认存储引擎
    explicit_defaults_for_timestamp=true        # 开启查询缓存
    # skip-grant-tables # 无需密码登录mysql
    复制代码

--------------------

##### 4.忘了密码怎么办 #####

1. net stop mysql 
    2. 打开my.ini文件 在[mysqld]下 添加skip-grant-tables
    3. net start mysql
    4. mysql -u root -p 回车 回车
    5. use mysql
    6. update user set authentication_string=password("xxxx") where user="root";
    6. flush privileges;
    7. quit
    8. 将my.ini文件的skip-grant-tables注释掉
    9. 重启服务
    复制代码

--------------------

##### 5.其他用法 #####

|---修改前面符号
    并没有什么实际用处，就是将`mysql>` 换一下,适于装B症患者...
    
    mysql -uroot -p --prompt toly-》
    toly-》use zoom
    Database changed
    toly-》
    
    |--- mysql中有内置函数使用
    mysql> SELECT VERSION(), CURRENT_DATE,NOW(),USER();
    +-----------+--------------+---------------------+----------------+
    | VERSION() | CURRENT_DATE | NOW()               | USER()         |
    +-----------+--------------+---------------------+----------------+
    | 5.7.22    | 2019-03-15   | 2019-03-15 18:51:12 | root@localhost |
    +-----------+--------------+---------------------+----------------+
    
    |---mysql中支持简单计算
    mysql> SELECT 1+1, (4+1)*5,4/5,5^8;
    +-----+---------+--------+-----+
    | 1+1 | (4+1)*5 | 4/5    | 5^8 |
    +-----+---------+--------+-----+
    |   2 |      25 | 0.8000 |  13 |
    +-----+---------+--------+-----+
    复制代码

--------------------

#### 二、MySQL中的数据类型 ####

> 下面的四张表就是下一篇练习MySQL的资源，我真是太聪明了...如果有错欢迎指出，我及时update

|----整型----|
    +-----------+-----------+----------------------+-----------------+--------------+
    | 类型      | 字节数    | 有符号范围           | 无符号范围      | 描述         |
    +-----------+-----------+----------------------+-----------------+--------------+
    | TINYINT   |         1 | -2⁷ ~ 2⁷-1           | 0 ~ 2⁸-1        | 很小整数     |
    | SMALLINT  |         2 | -2¹⁶ ~ 2¹⁶-1         | 0 ~ 2¹⁶-1       | 小整数       |
    | MEDIUMINT |         3 | -2²⁴ ~ 2²⁴-1         | 0 ~ 2²⁴-1       | 中等整数     |
    | INT       |         4 | -2³² ~ 2³²-1         | 0 ~ 2³²-1       | 标准整数     |
    | BIGINT    |         8 | -2⁶⁴ ~ 2⁶⁴-1         | 0 ~ 2⁶⁴-1       | 大整数       |
    +-----------+-----------+----------------------+-----------------+--------------+
    复制代码

--------------------

|----小数型----|
    +---------+-----------+-------------------------------------------------------+----------------------------+
    | 类型    | 字节数    | 取值范围                                              | 描述                       |
    +---------+-----------+-------------------------------------------------------+----------------------------+
    | FLOAT   |         4 | ±1.175494351E-38 ~ ±3.402823466E+38                   | 单精度浮点                 |
    | DOUBLE  |         8 | ±2.2250738585072014E-308 ~ ±1.7976931348623157E+308   | 双精度浮点                 |
    | DECIMAL |      不定 | 是M,D而定                                             | M:总位数,D:小数位数        |
    +---------+-----------+-------------------------------------------------------+----------------------------+
    复制代码

--------------------

|----字符型----|
    +------------+-------------------+---------------+-----------------------------------+
    | 类型       | 最大字节数        | 最大范围      | 描述                              |
    +------------+-------------------+---------------+-----------------------------------+
    | CHAR(M)    | M                 | L或M或M×W     | 定长字符串,M 0~255                |
    | VARCHAR(M) | M                 | L+1或 L+2     | 变长字符串,L<=M且M[0,65535]       |
    | TINYTEXT   | 2⁸-1              | L+1个字节     | 微型的字符串                      |
    | TEXT       | 2¹⁶-1             | L+2个字节     | 小型的字符串                      |
    | MEDIUMTEXT | 2²⁴-1             | L+3个字节     | 中型的字符串                      |
    | LONGTEXT   | 2³²-1             | L+4个字节     | 大型的字符串                      |
    | ENUM       | 1或2字节          | 65535         | 枚举                              |
    | SET        | 1、2、3、4或8     | 64            | 集合                              |
    +------------+-------------------+---------------+-----------------------------------+
    复制代码

--------------------

|----时间型----|
    +-----------+-----------+---------------------------------------------------------------+------------------------------------+
    | 类型      | 字节数    | 取值范围                                                      | 描述                               |
    +-----------+-----------+---------------------------------------------------------------+------------------------------------+
    | YEAR      |         1 | 1901~2155                                                     | 年YYYY                             |
    | DATE      |         3 | 1000-01-01 ~ 9999-12-31                                       | 日期YYYY-MM-DD                     |
    | TIME      |         3 | -838:59:59[.000000] ~ 838:59:59[.000000]                      | 时间HH:MM:SS                       |
    | DATETIME  |         8 | 1000-01-01 00:00:00[.000000] ～ 9999-12-31 23:59:59[.999999]  | 日期加时间YYYY-MM-DD HH:MM:SS      |
    | TIMESTAMP |         4 | 1970-01-01 00:00:01[.000000] ～ 2038-01-19 03:14:07[.999999]  | 时间戳                             |
    +-----------+-----------+---------------------------------------------------------------+------------------------------------+
    复制代码

--------------------

|----二进制型----|
    +--------------+-----------------+---------------+--------------------+
    | 类型         | 最大字节数      | 最大范围      | 描述               |
    +--------------+-----------------+---------------+--------------------+
    | BIT(M)       | M               | (M+7)/8       | 定长字符串         |
    | BINARY(M)    | M               | M             | 定长字符串         |
    | VARBINARY(M) | M               | L+1 或 L+2    | 变长字符串         |
    | TINYBLOB     | 2⁸-1            | L+1           | 微型的字符串       |
    | BLOB         | 2¹⁶-1           | L+2个字节     | 小型的字符串       |
    | MEDIUMBLOB   | 2²⁴-1           | L+3个字节     | 中型的字符串       |
    | LONGBLOB     | 2³²-1           | L+4个字节     | 大型的字符串       |
    +--------------+-----------------+---------------+--------------------+
    复制代码

> 这里只是简单的列一下，更多的详情介绍详见此篇：[MySQL入门系列：MySQL数据类型][MySQL_MySQL]

--------------------

#### 三、其他点 ####

##### 1.结束符：`; \g \G` #####

> `; \g`效果一样，`\G` 竖直分布

mysql> SELECT * FROM  number;
    +-----------+------+----------------------+----------------+--------------+
    | type      | byte | range_singed         | range_unsinged | info         |
    +-----------+------+----------------------+----------------+--------------+
    | TINYINT   |    1 | -2⁷ ~ 2⁷-1           | 0 ~ 2⁸-1       | 很小整数     |
    | SMALLINT  |    2 | -2¹⁶ ~ 2¹⁶-1         | 0 ~ 2¹⁶-1      | 小整数       |
    | MEDIUMINT |    3 | -2²⁴ ~ 2²⁴-1         | 0 ~ 2²⁴-1      | 中等整数     |
    | INT       |    4 | -2³² ~ 2³²-1         | 0 ~ 2³²-1      | 标准整数     |
    | BIGINT    |    8 | -2⁶⁴ ~ 2⁶⁴-1         | 0 ~ 2⁶⁴-1      | 大整数       |
    +-----------+------+----------------------+----------------+--------------+
    
    mysql> SELECT * FROM  number\g
    +-----------+------+----------------------+----------------+--------------+
    | type      | byte | range_singed         | range_unsinged | info         |
    +-----------+------+----------------------+----------------+--------------+
    | TINYINT   |    1 | -2⁷ ~ 2⁷-1           | 0 ~ 2⁸-1       | 很小整数     |
    | SMALLINT  |    2 | -2¹⁶ ~ 2¹⁶-1         | 0 ~ 2¹⁶-1      | 小整数       |
    | MEDIUMINT |    3 | -2²⁴ ~ 2²⁴-1         | 0 ~ 2²⁴-1      | 中等整数     |
    | INT       |    4 | -2³² ~ 2³²-1         | 0 ~ 2³²-1      | 标准整数     |
    | BIGINT    |    8 | -2⁶⁴ ~ 2⁶⁴-1         | 0 ~ 2⁶⁴-1      | 大整数       |
    +-----------+------+----------------------+----------------+--------------+
    
    mysql> SELECT * FROM  number\G
    *************************** 1. row ***************************
              type: TINYINT
              byte: 1
      range_singed: -2⁷ ~ 2⁷-1
    range_unsinged: 0 ~ 2⁸-1
              info: 很小整数
    *************************** 2. row ***************************
              type: SMALLINT
              byte: 2
      range_singed: -2¹⁶ ~ 2¹⁶-1
    range_unsinged: 0 ~ 2¹⁶-1
              info: 小整数
    *************************** 3. row ***************************
              type: MEDIUMINT
              byte: 3
      range_singed: -2²⁴ ~ 2²⁴-1
    range_unsinged: 0 ~ 2²⁴-1
              info: 中等整数
    *************************** 4. row ***************************
              type: INT
              byte: 4
      range_singed: -2³² ~ 2³²-1
    range_unsinged: 0 ~ 2³²-1
              info: 标准整数
    *************************** 5. row ***************************
              type: BIGINT
              byte: 8
      range_singed: -2⁶⁴ ~ 2⁶⁴-1
    range_unsinged: 0 ~ 2⁶⁴-1
              info: 大整数
    复制代码

--------------------

##### 2.清除命令 #####

`\c 或 Ctrl+c`
    复制代码

--------------------

##### 3.一条语句可换行 #####

mysql> SELECT
        ->  *
        -> FROM
        ->  number;
    +-----------+------+----------------------+----------------+--------------+
    | type      | byte | range_singed         | range_unsinged | info         |
    +-----------+------+----------------------+----------------+--------------+
    | TINYINT   |    1 | -2⁷ ~ 2⁷-1           | 0 ~ 2⁸-1       | 很小整数     |
    | SMALLINT  |    2 | -2¹⁶ ~ 2¹⁶-1         | 0 ~ 2¹⁶-1      | 小整数       |
    | MEDIUMINT |    3 | -2²⁴ ~ 2²⁴-1         | 0 ~ 2²⁴-1      | 中等整数     |
    | INT       |    4 | -2³² ~ 2³²-1         | 0 ~ 2³²-1      | 标准整数     |
    | BIGINT    |    8 | -2⁶⁴ ~ 2⁶⁴-1         | 0 ~ 2⁶⁴-1      | 大整数       |
    +-----------+------+----------------------+----------------+--------------+
    复制代码

--------------------

##### 4.一行可执行多条语句 #####

mysql> SELECT * FROM  number;SELECT * FROM  number_float;
    +-----------+------+----------------------+----------------+--------------+
    | type      | byte | range_singed         | range_unsinged | info         |
    +-----------+------+----------------------+----------------+--------------+
    | TINYINT   |    1 | -2⁷ ~ 2⁷-1           | 0 ~ 2⁸-1       | 很小整数     |
    | SMALLINT  |    2 | -2¹⁶ ~ 2¹⁶-1         | 0 ~ 2¹⁶-1      | 小整数       |
    | MEDIUMINT |    3 | -2²⁴ ~ 2²⁴-1         | 0 ~ 2²⁴-1      | 中等整数     |
    | INT       |    4 | -2³² ~ 2³²-1         | 0 ~ 2³²-1      | 标准整数     |
    | BIGINT    |    8 | -2⁶⁴ ~ 2⁶⁴-1         | 0 ~ 2⁶⁴-1      | 大整数       |
    +-----------+------+----------------------+----------------+--------------+
    5 rows in set (0.00 sec)
    
    +---------+------+-------------------------------------------------------+----------------------------+
    | type    | byte | num_range                                             | info                       |
    +---------+------+-------------------------------------------------------+----------------------------+
    | FLOAT   |    4 | ±1.175494351E-38 ~ ±3.402823466E+38                   | 单精度浮点                 |
    | DOUBLE  |    8 | ±2.2250738585072014E-308 ~ ±1.7976931348623157E+308   | 双精度浮点                 |
    | DECIMAL | NULL | 是M,D而定                                             | M:总位数,D:小数位数        |
    +---------+------+-------------------------------------------------------+----------------------------+
    复制代码

##### 5.select可以通过逗号拼表 #####

mysql> select 'a','b','c','d';
    +---+---+---+---+
    | a | b | c | d |
    +---+---+---+---+
    | a | b | c | d |
    +---+---+---+---+
    复制代码

--------------------

#### 四、数据库范式 ####

##### 1.先对接一下，简单描述相关概念 #####

关系：  表/若干元组的集合
    元组：  一行/一条记录/一个实体对象
    属性:   一列/字段
    分量：  元组中的一个属性值/一个实体对象的某一字段
    域：    属性实际取值范围
    
    超键：能够唯一标识一条记录的属性或属性集(可含多个属性)
    候选键/候选码：能够唯一标识一条记录的属集(不含多余属性)
    主键/主码：一个[候选键]作为主键
    
    主属性：[候选键]中的属性
    非主属性：[非候选键]中的属性
    复制代码

--------------------

##### 2.ER图 #####

--------------------

##### 3.第一范式：`1NF` #####

在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的
    即数据库表的每一列(属性)都是不可分割的原子数据项。
    即实体中的某个属性有多个值时，必须拆分为不同的属性。
    在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分
    复制代码

> 这里的价格成了二维，不符合第一范式，应该展平

--------------------

##### 4.第二范式：`2NF` #####

在第一范式的基础上：
    [非主属性]对[主属性]不存在部分函数依赖关系
    复制代码

<table> 
 <thead> 
  <tr> 
   <th>商品名</th> 
   <th>供应商</th> 
   <th>标准价格</th> 
   <th>价格</th> 
   <th>差价</th> 
   <th>供应商电话</th> 
   <th>分类</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>小刀</td> 
   <td>晨光文具</td> 
   <td>1.00</td> 
   <td>1.00</td> 
   <td>0</td> 
   <td>7654321</td> 
   <td>文具</td> 
  </tr> 
  <tr> 
   <td>铅笔</td> 
   <td>得力文具</td> 
   <td>1.00</td> 
   <td>1.50</td> 
   <td>0.5</td> 
   <td>1234567</td> 
   <td>文具</td> 
  </tr> 
  <tr> 
   <td>卷笔刀</td> 
   <td>晨光文具</td> 
   <td>5.00</td> 
   <td>5.00</td> 
   <td>0</td> 
   <td>7654321</td> 
   <td>文具</td> 
  </tr> 
  <tr> 
   <td>练字本</td> 
   <td>得力文具</td> 
   <td>2.00</td> 
   <td>2.00</td> 
   <td>0</td> 
   <td>1234567</td> 
   <td>文具</td> 
  </tr> 
  <tr> 
   <td>小刀</td> 
   <td>得力文具</td> 
   <td>1.00</td> 
   <td>1.20</td> 
   <td>0.2</td> 
   <td>1234567</td> 
   <td>文具</td> 
  </tr> 
 </tbody> 
</table>

插入异常：A实体依赖于B实体，B实体的缺失而使A实体表示异常
    |-- 
    
    更新异常：更新A表的某一属性，需要多行进行更新
    |-- 如果更新[得力文具]的供应商电话，需要更新三条数据
    
    删除异常：A实体删除一行，导致B实体信息丢失
    |-- 当删除第一、三行，晨光文具的供应商信息丢失
    
    数据冗余：相同的数据在多张表中存在，或表的某属性可通过计算得到(如平均值、和等)
    |-- 得力文具以及电话号码重复了很多次。标准价格,价格和差价之间有运算关系
    复制代码

<table> 
 <thead> 
  <tr> 
   <th>供应id</th> 
   <th>供应商</th> 
   <th>供应商电话</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>晨光文具</td> 
   <td>7654321</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>得力文具</td> 
   <td>1234567</td> 
  </tr> 
 </tbody> 
</table>

--------------------

##### 5.第三范式:`3NF` #####

在第二范式的基础上：
    [非主属性]对任意[主属性]不存在传递性函数依赖
    
    |--- 比如下面的(书名)-->(作者)-->(年代)  
    复制代码

<table> 
 <thead> 
  <tr> 
   <th>书名</th> 
   <th>作者</th> 
   <th>年代</th> 
   <th>上架建议</th> 
   <th>价格</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>海子</td> 
   <td>海子</td> 
   <td>近现代</td> 
   <td>诗集</td> 
   <td>30.6</td> 
  </tr> 
  <tr> 
   <td>朝花夕拾</td> 
   <td>老舍</td> 
   <td>近现代</td> 
   <td>名著</td> 
   <td>19.9</td> 
  </tr> 
  <tr> 
   <td>你是人间的四月天</td> 
   <td>林徽因</td> 
   <td>近现代</td> 
   <td>经典文学</td> 
   <td>25.6</td> 
  </tr> 
  <tr> 
   <td>资治通鉴</td> 
   <td>司马光</td> 
   <td>北宋</td> 
   <td>古典文学</td> 
   <td>55.6</td> 
  </tr> 
 </tbody> 
</table>

--------------------

##### 6.BC范式：`BCNF` #####

在第三范式的基础上：
    [所有属性]对任意[主属性]不存在传递性依赖
    复制代码

> 第四、第五范式暂不提及，注意范式并非严格的标准。  
> 可以根据实际情况反范式化来用空间换时间

> 这篇就到这里，以后想到什么再加吧。

[Link 1]: https://juejin.im/post/5c2881c95188252b5627475b
[MySQL_MySQL]: https://juejin.im/post/5c8096a3f265da2d8d6a06b7