MySql基础复习

​​​​​​                                    数据库的基本操作

• 如何创建数据库
• 数据库的删除操作
• 存储引擎的了解及其工作原理和如何选择

 1、创建数据库

创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理

 mysql安装完成以后，将会在其data目录下自动创建几个必须的数据库，可以使用  show  databases语句查看。

通过执行show databases  可以看到 mysql  安装自带的数据库是：information_schema、mysql、performance_schema、sakila、test、word,其中mysql是必须的，此数据库描述了用户的访问权限。

创建mysql数据库的语法格式是：  create database database_name,其中database_name是数据库名称。数据库创建完成以后可以执行 show  create database  查看数据库的定义。

创建数据库：create  database  database_name

查看数据库：show  databases

查看数据库的定义：show create database  database_name

以上不在举例

2、删除数据库

删除数据库是将已存在的数据库从磁盘空间上清除，清除之后，数据库中所有的数据也将一同被删除。

mysql中删除数据库的语法是：drop database  database_name,其中database_name 为数据库名称，若要删除的数据库不存在，则删除出错。是否删除成功可执行  show  databases查看。

提示：使用 drop  database命令要非常的谨慎，因为执行此命令时，mysql不会给出任何的提醒确认信息，执行命令后，数据库中存储的所有数据表和数据将一同被删除，且不可恢复。

3、数据库的储存引擎

   数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在mysql中，同一个服务器中可以针对具体的要求对每一张库表使用不同的存储引擎。

MySQL 5.7 支持存储引擎：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、Blackhole等。

可以使用  show  engines  命令查看系统所支持的存储引擎。 

我通过navicat工具执行 show  engines命令，结果如下图：

其中support列的值:YES表示此引擎可以使用，NO表示不可以被使用，default表示该引擎是当前默认存储引擎。

简述InnoDB和MyISAM存储引擎：

InnoDB引擎

 InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）,支持行锁定和外键，MySQL 5.5.5版本之后，InnoDB作为默认的存储引擎，InnoDB的主要特性有：

• InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。
• InnoDB是为处理巨大数据量的最大性能设计。他的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹配的。
• InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区） 。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。
• InnoDB支持外键完整性约束（foreign key）。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的rowid，并以此作为主键。
• InnoDB被用在众多需要高性能的大型数据库站点上。  InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。  

MyISAM引擎

MyISAM基于ISAM的存储引擎，并对其进行扩展。它是在web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。在MySQL5.5.5之前的版本中，MyISAM是默认的存储引擎。MyISAM主要特性有：

• 大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。
• 当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少的碎片。
• 每个MyISAM表最大的索引数是64，这可以通过重新编译来改变。每个索引的最大的列数是16个。
• blob和text列可以被索引。
• null值被允许在索引列中，这个值占每个键的0~1个字节。
• 每个字符列可以有不同的字符集。
• 有varchar的表可以固定或动态记录长度。
• varchar和char列可以多达64KB。

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm存储表定义，数据文件的扩展名为.MYD(MYData)，索引文件的扩展名是.MYI(MYIndex)。

存储引擎的选择

  不同的存储引擎都有各自的特点，以适应不同的需求，下面是每种引擎可以提供的功能：

存储引擎比较

功能	InnoDB	MyISAM	Memory	Archive
存储限制	64TB	256TB	RAM	None
支持事务	YES	NO	NO	NO
支持全文索引	NO	YES	NO	NO
支持数索引	YES	YES	YES	NO
支持哈希索引	NO	NO	YES	NO
支持数据缓存	NO	N/A	YES	NO
支持外键	YES	NO	NO	NO

1. 若要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是最好的选择。
2. 若数据表主要用来插入和查询记录的,MyISAM是最好的选择，因为MyISAM引擎能提高较高的处理效率。
3. 若只是临时存放数据且量不大、也不需要较高的数据安全性，可以使用Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。
4. 若只有insert和select操作，可选择Archive,Archive存储引擎支持搞并发的插入操作，但是本身不是事务安全的。此引擎适合存储归档数据 如记录日志信息。

                                   数据表的基本操作

一、创建数据表

• 创建表
• 主键约束
• 外键约束
• 非空约束
• 唯一性约束
• 默认约束
• 主键自增

二、查看数据表结构

• 查看表结构
• 查看表详情结构语句，即建表语句

三、修改表结构

• 修改表名
• 修改字段的数据类型
• 修改字段名
• 添加字段
• 删除字段
• 修改字段的排列位置
• 修改表的存储引擎
• 删除表的外键约束

四、删除数据表

• 删除未被关联的表
• 删除被其他关联的主表

一、创建数据表

1.1、表的创建：

数据表属于数据库，建表之前，应使用  use database_name  选择将要建表的数据库。

建表语法格式：

create table  <表名>（
    字段名1，数据类型 [列级别的约束条件] [默认值],
    字段名2，数据类型 [列级别的约束条件] [默认值],
    ......

    [表级别的约束条件]

）

使用create table 创建表时，要注意：

1. 表名称不区分大小写，但不能使用MySQL中的关键字，如：drop 、alter、insert等。、
2. 若创建多个列，需要逗号隔开。

命令查看数据库中所有的表：  show tables

1.2、主键约束

  主键又称主码，是表中一列或多列的组合。主键约束（primary key）要求主键列的数据唯一且不允许为空。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询速度。主键分为两种类型：单字段主键和多字段联合主键。

单字段主键：由一个字段组成

语法格式：1、 字段名  数据类型 primary key [默认值]       2、[constraint<约束名>] primary key [字段名]

示例：id为主键

//单主键  格式1
create table user(
    id int(11) primary key,
    name varchar(20),
    phone  varchar(11)    
)
//单主键格式2
create table  student(
    id int(11),
    name varchar(20),
    phone varchar(11),
    primary key(id)
)

多字段联合主键：由多个字段组成

格式：primary  key [字段1,字段2,...,字段n]

示例：name和phone作为联合主键

create table  test(
    name varchar(20),
    phone varchar(11),
    address varchar(50),
    primary key(name,phone)
)

1.3、外键约束

 外键释义：

• 是表中的一个字段，可以不是本表的主键，但是对应另一个表的主键。
• 外键的主要作用是保证数据的一致性和完整性，定义外键后，不允许删除另一个表中的具有关联关系的行。
• 外键是用来在两个表的数据之间建立连接，可以是一列或多列。
• 一个表可以有一个或多个外键
• 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

语法格式：

constraint   外键名   foreign   key (字段名)   references   主表名（主键列名）

语法释义：

• 外键名是定义外键约束的名称，一个表中不能有相同的外键名称。
• 字段名是子表需要添加外键约束的字段列。
• 主表名是被子表外键所关联的表的名称。
• 主键列名是是被子表外键所关联的表的主键列。

实例：

a表中字段b_id作为外键关联b表中的主键id，外键名是fk_a_b,  

CREATE TABLE `a` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `b_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_a_b` (`b_id`),
  CONSTRAINT `fk_a_b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `b` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

提示：

• 关联是指关系型数据库中，相关表之间的关联。
• 是通过相容或相同的属性或属性组来表示的。
• 子表的外键必须关联主表的主键，且关联字段的数据类型必须匹配，若不匹配创建的时候就会报错。

1.4非空约束

非空约束指字段的值不能为空，对于非空约束的字段，在添加数据没有指定值，数据库则会报错。

语法格式：

      字段名 数据类型  not null

实例：

创建test表，并指定name字段不能为空

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1.5 唯一性约束

唯一性约束要求该列唯一，允许为空，但是只能出现一个空值。唯一约束可以确保一列或者多列不出现重复值

语法格式：

 字段名 数据类型  unique      定义完列直接指定唯一约束

 constraint  约束名 unique（字段名）  定义完所有列之后指定唯一约束(可以多字段使用唯一性约束，这样多字段确定一条数据，即唯一)

实例：

//指定完列直接使用唯一约束 UNIQUE
create table test1(
id int(11) PRIMARY key,
name varchar(20) UNIQUE
)
//指定完所有列再使用唯一约束  约束名为aa 
create table test2(
id int(11) PRIMARY key,
address varchar(20),
CONSTRAINT aa UNIQUE(address)
)
//指定完所有列可以给多字段使用唯一约束  下面 约束名为cc  则 test3表中name+phone 唯一
create table test3(
id int(11) PRIMARY key,
name varchar(20) NOT NULL,
phone VARCHAR(20) NOT null ,
CONSTRAINT cc UNIQUE(`name`,`phone`)
)

UNIQUE与PRIMARY KEY的区别：

• 一个表中可以多个字段声明为unique，但是只能有一个primary key声明。
• 声明为primary key的列不允许有空值，但是声明为unique的字段允许空值（null）存在。

1.6 默认约束

默认约束指定某列的默认值。

语法格式：

字段名 数据类型 default 默认值

实例：

address默认值为null,remark的默认值是‘1111’

create table test2(
id int(11) PRIMARY key,
address varchar(20) default null,
remark varchar(20)  default '1111'
)

1.7 主键自增

主键自增：

• 系统自动生成字段的主键值
• 为表主键设置auto_increment关键字来实现
• MySQL中auto_increment的初始值是1，每新增一条记录，字段值自动加一
• 一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分
• auto_increment约束的字段可以是任何整数类型

语法格式：

字段名  数据类型 auto_increment

实例：

create table exp(
id BIGINT(20) PRIMARY KEY auto_increment,
name VARCHAR(50) DEFAULT NULL
)

设置id为主键的时候执行insert插入操作时不用管id字段

查看数据表结构

1.1查看表结构

语法格式：desc 表名  或者 describe  表名

1.2查看表的详细结构

语法格式： show   create  table  表名

1.1实例：

desc test3

释义：

• field 表示字段
• type 表示字段类型
• null  表示该列是否可以存储null值
• key  表示该列是否已编制索引，PRI表示该列是主键的一部分；NUI表示该列是UNIQUE索引的一部分；MUL表示该列某个给定值允许出现多次
• default  表示该列是否有默认值，如果有的话值是多少
• extra  表示可以获取的与给定列的有关附加信息，比如auto_increment等

1.2 实例：

show  create  table  test3

释义：简单的说就是得到的是得到的建表语句，初次之外就是库表的存储引擎和编码格式

修改数据表

修改表是指修改已存在数据表的结构，MySQL使用alter  table 语句修改表。

常用修改表的操作：

• 修改表名
• 修改字段名
• 修改字段类型
• 增加字段
• 删除字段
• 修改字段的位置
• 更改存储引擎
• 删除表的外键约束
• 等等.....

1.1修改表名

语法格式： alter  table  旧表名   rename  to  新表名

实例：将test3表名更改为test表名        >> alter  table  test3  rename  to  test

1.2 修改字段名

语法格式： alter  table  表名  change  旧字段名   新字段名  新数据类型

• 旧字段指修改前的字段名
<