数据库MySQL知识总结

绝地灬酷狼 2021-12-10 01:51 337阅读 0赞

### 数据库MySQL知识总结 ###

*  MySQL数据类型
 *   *  数值类型
     *  字符串类型：
     *  日期类型
 *  索引
 *   *  索引类型
     *  存储引擎
     *   *  InnoDB（默认）
         *  MyISAM
         *  InnoDB VS MyISAM\[^1\]
     *  数据库常用函数
     *   *  常用MySQL命令行操作：
     *  数据库SQL优化：
     *   *   *  union的使用举例
             *  视图的作用，和表的区别？
             *  游标
             *  存储过程
         *  事务在spring中的使用
         *   *  Spring的事务传播行为类型说明
             *   *  Spring @Transactional注解

# MySQL数据类型 #

## 数值类型 ##

<table> 
 <thead> 
  <tr> 
   <th>字段类型</th> 
   <th>长度（字节）</th> 
   <th>取值范围（无符号）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>1字节</td> 
   <td>0 ~ 255</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2字节</td> 
   <td>0 ~ 65 535</td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td>3字节</td> 
   <td>0 ~ 16 777 215</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>4字节</td> 
   <td>很大的11位整数</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8字节</td> 
   <td>很大很大的20位整数</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td>4字节</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8字节</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>decimal</td> 
   <td>对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

注意无符号数和有符号数的范围差别：col_name tinyint(4) unsigned;  
    表示tinyint的显示长度为4，无符号数 （范围在0-255）；
    若不显示声明，则默认为有符号，即范围在 -128~127；

## 字符串类型： ##

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

<table> 
 <thead> 
  <tr> 
   <th>字段类型</th> 
   <th>长度（字节）</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR</td> 
   <td>0-255字节</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>0-65535 字节</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0-255字节</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0-65 535字节</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0-16 777 215字节</td> 
   <td>中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0-4 294 967 295字节</td> 
   <td>极大文本数据</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0-255字节</td> 
   <td>不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>0-65 535字节</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>0-16 777 215字节</td> 
   <td>二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>0-4 294 967 295字节</td> 
   <td>二进制形式的极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

enum的使用
    当列值为确定的几个有限值时，可以使用enum，能减少存储空间；
    enum实际是使用整数值来存储，且要注意其可选值的顺序，分别是从1到n，index=0是' '值，null值在第一（为null），
    eg:  alter student add column sex enum("male","female");
    查看enum索引个数
    select sex+0 from student;

## 日期类型 ##

<table> 
 <thead> 
  <tr> 
   <th>字段类型</th> 
   <th>长度（字节）</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE</td> 
   <td>3</td> 
   <td>1000-01-01/9999-12-31</td> 
   <td>YYYY-MM-DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>‘-838:59:59’/‘838:59:59’</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1901/2155</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td>1970-01-01 00:00:00/2038 <br>结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07</td> 
   <td>YYYYMMDD HHMMSS</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

tips：针对值为NULL的情况， 若列值允许为null，用is null(/is not null)进行判断；
    null值不能进行运算，可以使用NULL函数将其作为0处理，
    eg: SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products

# 索引 #

## 索引类型 ##

*  唯一索引
 *  聚集索引
 *  普通索引

## 存储引擎 ##

### InnoDB（默认） ###

支持事务，外键，行级锁。适合写密集，需要事务控制并发的场景；

### MyISAM ###

alter table student engine=myisam;  
表级锁，不支持事务外键；  
适合读密集操作的表，select count（\*）可以直接返回结果，因为有单独存储它的数据结构

### InnoDB VS MyISAM1 ###

1.  InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2.  InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3.  InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
4.  而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
5.  InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
6.  Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

## 数据库常用函数 ##

1.ifnull

select name,sum(ifnull(score,0)) as cou from student left join score on (student.id=score.stu_id) group by stu_id order by cou desc;

### 常用MySQL命令行操作： ###

1.  切换至mysql安装目录，mysql -v 查看版本信息 若报错ERROR 1045 (28000): Access denied  
    for user ‘ODBC’@‘localhost’ (using password: NO)  
    可在my.ini中,找到里面的\[mysqld\] 然后在下面加上这句：skip\_grant\_tables (启动MySQL服务的时候跳过权限表认证)
2.  create database 创建数据库，
3.  use database 选择数据库,
4.  show databases 显示所有数据库，
5.  select database() 显示当前所在数据库，
6.  describe tablename 显示表结构信息

1.  show create table student; 查看建表语句细节（包括存储引擎等）

student | CREATE TABLE `student` (
      `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      `age` tinyint(3) unsigned DEFAULT NULL,
      `sex` enum('male','female') DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1889 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

show engines; 查看支持的存储引擎

mysql> show engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

alter table student add column class tinyint after name; 指定新增字段位置  
修改字段类型 modfiy 或 change

## 数据库SQL优化： ##

一般来说，应该在这些列上创建索引：

1.  在经常需要**搜索**的列上，可以加快搜索的速度；
2.  在作为**主键**的列上，强制该列的唯一性和组织表中数据的排列结构；
3.  在经常用在**连接**的列上，这些列主要是一些外键，可以加快连接的速度；
4.  在经常需要根据**范围**进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.  在经常需要**排序**的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6.  在经常使用在**WHERE子句**中的列上面创建索引，加快条件的判断速度。

#### union的使用举例 ####

场景：将三张表的数据一次查询出来，每个表分别查询一个字段（不存在的字段，可以用 \[’’ newcol\]替代 ），sql如下：

select size,'' color,'' resolution from tb_size
    union (select '' size,color,'' resolution from tb_color)
    union (select '' size,'' color,resolution from tb_resolution) order by size,color,resolution desc

注意：如果上面查询的每张表，都使用的索引，那这么查询 效率还是可以的；不然数据库会全表扫描3次。

#### 视图的作用，和表的区别？ ####

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

#### 游标 ####

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

#### 存储过程 ####

是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

### 事务在spring中的使用 ###

#### Spring的事务传播行为类型说明 ####

*  PROPAGATION\_REQUIRED 如果当前没有事务，就新建一个事务。如果已经存在一个事务中，加入到这个事务中。这是最常见的选择。
 *  PROPAGATION\_REQUIRES\_NEW 如果当前没有事务，就新建一个事务。如果当前存在事务，就把当前事务挂起，另建一个事务。
 *  PROPAGATION\_NESTED 如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与 PROPAGATION\_REQUIRED 类似的操作。外套的事务异常可使内嵌事务回滚，反之不会。（底层的数据源必须基于 JDBC 3.0 ，并且实现者需要支持保存点事务机制）
 *  PROPAGATION\_SUPPORTS 支持当前事务，如果当前没有事务，就以非事务方式执行。
 *  PROPAGATION\_NOT\_SUPPORTED 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
 *  PROPAGATION\_NEVER以 非事务方式执行，如果当前存在事务，则抛出异常
 *  PROPAGATION\_MANDATORY 使用当前的事务，如果当前没有事务，就抛出异常。

##### Spring @Transactional注解 #####

*  首先调用的是AOP代理对象而不是目标对象，首先执行事务切面，事务切面内部通过Transaction Interceptor环绕增强进行事务的增强，即进入目标方法之前开启事务，退出目标方法时提交/回滚事务。
 *  Spring在处理@Tranasctional注解时，会“proxy”掉当前的类。
 *  如果A和B两个类都有@Transactional时，实际上运行的是A的代理类A‘, A，B的代理类B’， B四个类的instance。一个外部服务调用A，实际上是 外部–>A’–>A–>B’–>B这样执行的。而抛出异常的代码实际上是在B‘做的。
 *  但是如果是同一个类内部方法直接调用的话，那么就是简单的方法直接调用，即 外部–>A’–>A方法1–>A方法2。 A方法1不会找到A’去调用。于是，“传播”的规则不会生效。

Spring并不直接管理事务，而是提供了多种**事务管理器**，他们将事务管理的职责委托给Hibernate或者JTA等持久化机制所提供的相关平台框架的事务来实现。 Spring事务管理器的接口是org.springframework.transaction.PlatformTransactionManager，通过这个接口，Spring为各个平台如JDBC、Hibernate等都提供了对应的事务管理器，但是具体的实现就是各个平台自己的事情了。此接口的内容如下：

Public interface PlatformTransactionManager()…\{

// 由TransactionDefinition得到TransactionStatus对象
    TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;
    // 提交
    Void commit(TransactionStatus status) throws TransactionException; 
    // 回滚
    Void rollback(TransactionStatus status) throws TransactionException; 
    }

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

1.  from 知乎：https://www.zhihu.com/question/20596402/answer/211492971 ↩︎