Mysql基础知识--概述和索引

Myth丶恋晨 2021-12-24 03:25 239阅读 0赞

### 一、Mysql概述 ###

> MySQL原来隶属于MySQL公司，总部位于瑞典
> 
> 2008.1.16MySQL被SUN公司收购
> 
> 2009年SUN公司被Oracle公司收购

常见的软件版本

*  GA（general Availability) :官方推崇广泛使用的版本
 *  RC（release Candidate)：候选版本，最接近正式版的版本
 *  Aipha和Bean都属于测试版本，其中Alpha是指内侧版本，Bean指公测版本

MySQL的官方网站网址（[http://mysql.com][http_mysql.com])

TyPical:默认的安装类型
    
    Complete:完全的安装类型
    
    Custome:自定义的安装类型
    
    Detailed Configuration :详细配置
    Standard Configuration：标准配置

经常会修改的MySQL配置文件

*  dafault-character-set:客户端字符集
 *  character-set-server:服务端字符集
 *  port:客户端和服务端的端口号
 *  default-storage-engin:MySQL软件的存储引擎

注意：如果修改了MySQL的配置文件。则必须重新启动MySQL服务，修改的内容才能生效

**在DOS窗口中，输入net start命令，可以查看Windows系统已经启动的服务**

### 二、MySQL数据库基本操作 ###

#### （一）数据库和数据库对象 ####

> 数据库是存储数据库对象的容器
> 
> 数据库可以分为系统数据库和用户数据库

##### 1.系统数据库 #####

> 安装完MySQL服务器后，会附带的一些数据库

*  information\_schema：主要存储系统中的一些数据库对象信息。如用户表信息，列信息，权限信息，字符集信息，和分区信息等。
 *  performance\_schema:主要存储数据库服务器性能参数。
 *  mysql:主要存储系统的用户权限信息
 *  test:该数据库为MySQL数据库管理系统自动创建的测试数据库，任何用户都可以使用。

##### 2.用户数据库 #####

> 用户根据实际需求创建的数据库

#### （二）数据库相关操作 ####

###### 1.创建数据库的语法形式 ######

CREATE DATABASE DATABASE_NAME; 首字母不能是$和数字 不允许是MQSlq保留字 不允许有空格和特殊字符

##### 2.查看数据库 #####

SHOW DATABASES;

##### 3.选择数据库 #####

USE DATABASE_NAME;

##### 4.删除数据库 #####

DROP DATABASE DATABASE_NAME;

### 三、MySQL数据库中存储引擎和数据类型 ###

#### （一）认识存储引擎 ####

##### 1.MySQL所支持的存储引擎 #####

#方法一 show engines； #方法二 SHOW VARIABLES LIKE 'HAVE%'

##### 2.操作默认存储引擎 #####

*  查询默认存储引擎

#查询默认存储引擎 SHOW VARIABLES LIKE 'stronge_engine%'

*  选择存储引擎
    
     *  MYISAM存储引擎：由于该存储引擎不支持事务，也不支持外键，所以访问速度比较快，**因此对事务完整性没有要求并以访问为主的应用适合用该存储引擎**
     *  InnoDB存储引擎：支持具有提交、回滚和崩溃恢复能力的事务安装，所以比MYISAM存储引擎占用更多的磁盘空间，**因此需要频繁的更新删除操作，同时还对事务的完整性要求比较高，需要实现并发控制，此时适合使用该存储 引擎**
     *  MEMORY存储引擎：该存储引擎使用内存来存储数据，因此该存储引擎的数据访问速度快，但是安全没有保障。**如果应用中设计数据较小，需要进行快速访问，适合使用该存储引擎。**

#### （二)数据类型 ####

##### 1.整数类型 #####

<table> 
 <thead> 
  <tr> 
   <th style="text-align:left;">整数类型</th> 
   <th>TINYINT</th> 
   <th style="text-align:center;">SMALLINT</th> 
   <th style="text-align:center;">MEDIUMINT</th> 
   <th style="text-align:center;">INT和INTEGER</th> 
   <th style="text-align:center;">BIGINT</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:left;">字节</td> 
   <td>1</td> 
   <td style="text-align:center;">2</td> 
   <td style="text-align:center;">3</td> 
   <td style="text-align:center;">4</td> 
   <td style="text-align:center;">8</td> 
  </tr> 
 </tbody> 
</table>

##### 2.浮点数类型、定点数类型和位类型 #####

*  浮点数类型

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">浮点数类型</th> 
   <th style="text-align:center;">FLOAT</th> 
   <th style="text-align:center;">DOUBLE</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">字节</td> 
   <td style="text-align:center;">4</td> 
   <td style="text-align:center;">8</td> 
  </tr> 
 </tbody> 
</table>

*  定点数类型

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">定点数类型</th> 
   <th style="text-align:center;">字节</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">DEC(M,D)和(DECIMAL(M,D)</td> 
   <td style="text-align:center;">m+2</td> 
  </tr> 
 </tbody> 
</table>

> FLOAT和DOUBLE数据类型存储数据时存储的是近似值，而DECIMAL存储的是字符串，因此提供了更高的精度，在需要表示金额等货币类型是有选择DECIMAL数据类型。

*  位类型

##### 3.日期和时间类型 #####

<table> 
 <thead> 
  <tr> 
   <th>日期和时间类型</th> 
   <th>字节</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DATE</td> 
   <td>4</td> 
   <td>1000-01-01</td> 
   <td>9999-12-31</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00</td> 
   <td>9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4</td> 
   <td>19700101080001</td> 
   <td>2038年的某个时刻</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3</td> 
   <td>-835:59:59</td> 
   <td>835:59:59</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1901</td> 
   <td>2155</td> 
  </tr> 
 </tbody> 
</table>

##### 4.字符串类型 #####

*  char系列字符串类型

<table> 
 <thead> 
  <tr> 
   <th>char系列字符串类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR(M)</td> 
   <td>M</td> 
   <td>M为0~255之间的整数</td> 
  </tr> 
  <tr> 
   <td>varchar(M)</td> 
   <td>m</td> 
   <td>m为0~65535之间的整数</td> 
  </tr> 
 </tbody> 
</table>

*  text系列的字符串类型

<table> 
 <thead> 
  <tr> 
   <th>TEXT系列字符串类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0~255</td> 
   <td>值的长度为+2个字节</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0~65535</td> 
   <td>值的长度为+2个字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0~167772150</td> 
   <td>值的长度为+3个字节</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>0~4294967295</td> 
   <td>值的长度为+4个字节</td> 
  </tr> 
 </tbody> 
</table>

*  BINARY系列字符串类型

<table> 
 <thead> 
  <tr> 
   <th>BINARY系列字符串类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BINARY（M）</td> 
   <td>M</td> 
   <td>允许长度为0~M</td> 
  </tr> 
  <tr> 
   <td>VARBINARY(M)</td> 
   <td>M</td> 
   <td>允许长度为0~M</td> 
  </tr> 
 </tbody> 
</table>

*  BLOB系列字符串类型

#### 四、表的操作 ####

> 表是包含数据库中所有数据的数据库对象

##### 1.表的基本概念 #####

> 表中的数据库对象包括列、索引、触发器

*  列（Columns):也成属性列，在具体创建表时，必须指定列的名字和数据类型
 *  索引（Indexes):是指根据指定的数据库表列建立起来的顺序，提供了快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复。
 *  触发器（Triggers)：是指用户定义的事务的集合，当对一个表中的数据插入、更新或删除时，这组命令就会自动的执行，可以用来确保数据的完整性和安全性。

##### 2.创建表 #####

CREATE TABLE TABLE_NAME( 属性名 数据类型， 属性名 数据类型， 属性名 数据类型， 属性名 数据类型 )

##### 3.查看表结构 #####

#查看表定义 DESCRIBE TABLE_NAME； #查看表详细定义 DESCRIBE CREATE TABLE TABLE_NAME;

##### 4.删除表 #####

DROP TABLE TABLE_NAME；

##### 5.修改表 #####

#修改表名 ALTER TABLE old_table_name RENAME [TO] new_table_name #在表的最后一个位置增加字段 ALTER TABLE table_name ADD 属性名 属性类型 #在表的最后一个位置增加字段 ALTER TABLE table_name ADD 属性名 属性类型 FIRST; #在表的指定字段之后增加字段 ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名

##### 6.删除字段 #####

#删除字段 ALTER TABLE table_name DROP 属性名

##### 7.修改字段 #####

#修改字段的数据类型 ALTER TABLE table_name MODIFY 属性名 数据类型 #修改字段的名字 ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型 #同时修改字段的名字和属性 ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型 #修改字段顺序 ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2

##### 8.操作表的约束 #####

*  完整性约束

<table> 
 <thead> 
  <tr> 
   <th>完整性约束关键字</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NOT NULL</td> 
   <td>约束字段的值不能为空</td> 
  </tr> 
  <tr> 
   <td>DEFAULT</td> 
   <td>设置字段的默认值</td> 
  </tr> 
  <tr> 
   <td>UNIQUE KEY(UK)</td> 
   <td>约束字段的值是唯一的</td> 
  </tr> 
  <tr> 
   <td>PRIMARY KEY (PK)</td> 
   <td>约束字段为表的主键，可以作为该表记录的唯一标识</td> 
  </tr> 
  <tr> 
   <td>AUTO_INCREMENT</td> 
   <td>约束字段的值为自动增加</td> 
  </tr> 
  <tr> 
   <td>FOREIGN KEY (FK)</td> 
   <td>约束字段为表的外键</td> 
  </tr> 
 </tbody> 
</table>

`约束可分为：单列约束，每个约束只约束一列数据；多列约束，每个约束可以约束多列数据`

*  设置非空约束（NOT NULL,NK）
    
        CREATE TABLE table_name( 属性名 数据类型 NOT NULL, .... );
 *  设置字段的默认值（DEFAULT）

CREATE TABLE table_name( 属性名 数据类型 DEFAULT 默认值, .... );

*  设置唯一约束（UNIQUE,UK）

CREATE TABLE table_name( 属性名 数据类型 UNIQUE I, .... );

*  设置主键约束（PRIMARY KEY,PK)

#单字段主键 CREATE TABLE table_name( 属性名 数据类型 PRIMARY KEY, .... ); #多字段主键 CREATE TABLE table_name( 属性名 数据类型 .... [CONSTRAINT 约束名] PRIMARY KEY (属性名，属性名。。。) );

*  设置字段值自动增加（AUTO\_INCREMENT)

CREATE TABLE table_name( 属性名 数据类型 AUTO_INCRMENT, .... );

*  设置外键约束（FOREIGN KEY ,FK)

CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型 .... CONSTRANT 外键约束名 FOREIGN KEY(属性名 1) REFERENCES 表名 （属性名2） );

#### 五、索引的操作 ####

##### （一）索引的概述 #####

> 1.索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构（B-树或哈希表）中，通过MySQL可以快速有效的查找出与键值相关联的字段。
> 
> 2.根据索引的存储类型，可以将索引分为B型树索引和哈希索引
> 
> 3.InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为前者索引

*  MySQL支持6中索引：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

###### 1.适合创建索引的情况 ######

*  经常被查询的字段，即在WHERE子句中出现的字段
 *  在分组的字段，即在GROUP BY子句中出现的字段
 *  存在依赖关系的字表和父表之间联合查询，即主键或外键字段。
 *  设置唯一约束的字段

###### 2.不适合创建索引的情况 ######

*  在查询中很少被使用的字段
 *  拥有许多重复值的字段

##### （二）创建和查看索引 #####

###### 1.创建和查看普通索引 ######

> 普通索引：就是在创建索引时，不附加任何条件，该类型的索引可以创建在任何数据类型的字段上。

*  创建表时创建普通索引

CREATE TABLE table_name( 属性名 数据类型， 属性名 数据类型， ... ... 属性名 数据类型， INDEX|KEY [索引名]（属性名 1 [(长度)] [ASC|DESC] ) INDEX或KEY参数用来指定字段为索引 索引名参与用来指定所创建索引名 ’属性名 1‘参数用来指定索引所关联的字段的名称， 长度用来指定索引的长度

> MySQL所支持的存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。

*  在已经存在的表上创建索引

CREATE INDEX 索引名 ON 表名 （属性名 [(长度)] [ASC|DESC]）

*  通过sql语句ALTER TABLE创建普通索引

ALTER TABLE table_name ADD INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]）

###### 2.创建和查看唯一索引 ######

> 唯一索引：在创建索引时，限制索引的之必须是唯一的。通过该类型的索引可以更快速的查询某条记录、
> 
> 在MySQL中，根据创建索引的方式，可以分为自动索引和手动索引两种。
> 
>  自动索引：在数据库表里设置完整性约束时，该表会被系统自动创建索引
> 
>  手动索引：手动在表上创建索引
> 
> 当设置表中的某个字段设置主键或是唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

*  创建表时创建唯一索引

CREATE TABLE table_name( 属性名 数据类型， 属性名 数据类型， ... ... 属性名 数据类型， UNIQUE INDEX|KEY [索引名]（属性名 1 [(长度)] [ASC|DESC] )

*  在已经存在的表上创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名 （属性名 [(长度)] [ASC|DESC]）

*  通过SQL语句ALTER TABLE创建唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]）

###### 3.创建和查看全文索引 ######

> 全文索引主要关联在数据类型为CHAR/VARCHAR和TEXT的字段上，以便能够更加快速的查询数据量较大的字符串类型的字段。
> 
> 只能在MyISAM的数据库表上创建全文索引
> 
> 在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，则区分大小写的搜索方式执行。

*  创建表时创建全文索引

CREATE TABLE table_name( 属性名 数据类型， 属性名 数据类型， ... ... 属性名 数据类型， FULLTEXT INDEX|KEY [索引名]（属性名 1 [(长度)] [ASC|DESC] )

*  在已经存在的表上创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 （属性名 [(长度)] [ASC|DESC]）

*  通过SQL语句ALTER TABLE创建全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]）

###### 4.创建和查看多列索引 ######

> 多列索引：在创建索引时，所关联的字段不是一个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段红的第一个字段，多列索引才会被使用

*  创建表时创建多列索引

CREATE TABLE table_name( 属性名 数据类型， 属性名 数据类型， ... ... 属性名 数据类型， INDEX|KEY [索引名]（属性名 1 [(长度)] [ASC|DESC]， ... ... （属性名 1 [(长度)] [ASC|DESC] )

*  在已经存在的表上创建多列索引

CREATE INDEX 索引名 ON 表名 （属性名 [(长度)] [ASC|DESC]） ... ... （属性名 [(长度)] [ASC|DESC]）