MySQL数据库的基本使用

1.数据库的基本操作

1.1 数据库的创建

　　MySQL在完成安装之后，会有几个必须需要创建的数据库，我们可以使用show databases;进行查看。

　　创建数据库的命令是：CREATE DATABASE DATABASE_NAME;

　　其中database_name是需要创建的数据库的名称，这个名称不可以与已经存在的数据库重名。

　　在数据库创建完毕之后可以使用SHOW CREATE DATABASE 来进行查看数据库的定义。

1.2 数据库的删除

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

　　删除命令是：DROP DATABASE DATABASE_NAME;

　　其中database_name是要删除数据库的名称，如果指定的数据库不存在，就会删除出错。

　　PS：使用DROP DATABASE 命令的时候要非常谨慎，因为这个命令不会给出任何的提醒，如果一旦被删除，数据库中的数据会全部被删除，而且没法恢复。

1.3 创建数据表

　　数据表是属于数据库的，在创建数据表之前，应该使用语句USE <数据库名称>指定是在那个数据库中进行的操作。创建数据表的命令如下：

CREATE TABLE<表名称>
(
     字段名称1,数据类型 [列级别约束条件] [默认值]，
     ......
     [表级别约束条件]   
)

　　在创建表的时候需要注意下面信息：

  1. 要创建表的名称，不区分大小写，不能使用SQL语言中的关键字。
  2. 数据表中每一列(字段)的名称和数据类型，如果创建多列，使用逗号进行隔开。
例如：
#创建学生表
CREATE TABLE STUDENT
(
      id INT(10),
      name VARCHAR(20)
);

1.4 主键约束

　　主键又成为主码，是表中一列或者多列的组合。主键约束要求主键列唯一，并且不允许为空。主键可以唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据查询的速度。主键分为单字段主键和多字段主键。

1.4.1 单字段主键

　　主键是由一个字段组成的，主要有下面两种：

　　(1)在定义列的同时指定主键：

字段名  数据类型   PRIMARY KEY [默认值]

　　(2)在定义完所有的列之后指定主键：

[CONSTRAINT <约束名>]  PRIMARY KEY [字段名]

1.4.2 多字段联合主键

　　主键是由多个字段联合组成的，主要形式如下：

PRIMARY KEY [字段1,字段2.....]

1.5 外键约束

　　外键是用来在两个表的数据之间建立链接，它可以是一列或者是多列。一个表可以由一个或者多个外键。外键对应是参照完整性，一个表的外键可以为空值，如果不是空值，则每个外键值必须等于另外一个表中的主键的某个值。

　　外键可以不是本表中的主键，但是一定是另外一张表中的主键。外键主要的作用是保证数据引用的完整性，定义外键之后，不允许删除在另外一个表中具有关联性关系的行。

FOREINGN KEY 字段名1[,字段名2,.....]  REFERENCES<主键名> 主键列1[,主键列2....]

1.6 非空约束

　　非空约束是指字段的值不可以为空。对于使用了非空约束的字段，如果用户在进行添加的时候没有指定值，就会报错。

字段名 数据类型 NOT NULL

1.7 唯一性约束

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

　　(1)定义完之后直接指定唯一约束：

字段名 数据类型 UNIQUE

　　(2)定义完所有列之后指定唯一约束

[CONSTRAINT <约束名>] UNIQUE(<字段名>)

1.8 默认约束

　　默认约束是指顶某列的默认值。就是在系统插入的时候默认是某个值。

字段名 数据类型 DEFAULT 默认值

1.9 表的属性值自动增加

　　可以通过AUTO_INCREMENT关键字来实现自动增加记录而字段值加1。一个表中只能有一个字段使用AUTO_INCREMENT约束，并且该字段必须是主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型。

字段名 数据类型 AUTO_INCREMENT

1.10 查看数据表结构

1.10.1 DESCRIBE

　　DESCRIBE/DES语句可以查看表的字段信息，其中包括字段名、字段数据类型、是否时主键、是否有默认值等等。

DESCRIBE 表名;
或者
DESC 表名;

1.10.2 SHOW CREATE TABLE 

　　SHOW CREATE TABLE语句可以用来显示创建表的时候的CREATE TABLE语句。

SHOW CREATE TABLE　＜表名\G＞;
PS : 加上\G之后会让显示的结果更加清晰

1.11 修改数据表

　　修改数据表是指修改数据库中已经存在的数据表的结构。

1.11.1 修改表名

　　MySQL时通过ALTER TABLE语句来实现表名的修改的。

ALTER TABLE <旧表名> RENAME [TO] <新表名>
PS：其中TO是可选项

1.11.2 修改字段的数据类型

　　修改字段的数据类型是指把字段的数据类型转换成另一种数据类型。

ALTER TABLE <表名> MODIFY <字段名> <数据类型>
PS：其中表名是要修改的数据类型所在的表的名称，字段名是需要修改的字段，数据类型是修改后字段的新的数据类型

1.11.3 修改字段名称

ALTER TABLE<表名> CHANGE <旧字段> <新字段> <新数据类型>
PS：其中旧字段是需要修改的字段，新字段是修改后的字段，数据类型是修改后的数据类型，如果不需要修改那就保持和之前的一样就可以，但是这个不可以为空

PS：由于不同类型的数据在机器中存储的方式以及长度可能并不相同，修改数据类型可能会影响到数据表中已有的数据记录，所以如果表中有数据的话不要轻易去修改数据类型。

1.11.4 添加字段

ALTER TABLE <表名> ADD <新字段> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名]
PS：FIRST为可选择参数，其作用是将新添加的字段设置为表的第一个字段;AFTER也是可选择参数，其作用是将新添加的字段添加到指定的已存在的字段的后面。如果不选择默认添加到最后。

1.11.5 删除字段

ALTER TABLE <表名> DROP <字段名>

1.11.6 修改字段的排序位置

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>

1.12 修改表的存储引擎

ALTER TABLE <表名> ENGINE=<更改之后的存储引擎名称>

1.13 删除表的外键约束

　　对于数据库中定义的外键，如果不再需要的话可以进行删除，一旦删除外键，就会解除与主表之间的关联关系。

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
PS：其中外键约束名称是指CONSTRAINT关键字后面的参数

1.14 删除数据表

　　删除数据表就是把数据库中已经存在的表从数据库中删除。在删除之前请做好表的备份，因为一旦删除，表的定义和数据都会被删除。

1.14.1 删除没有被关联的表

DROP TABLE [IF EXISTS] 表1,表2......;
PS：IF EXISTS是可选项，加上之后如果不存在要删除的表会给你提示，不加会报错。

1.14.2 删除被其他表关联的主表

　　数据表之间如果存在外键关联的情况下，如果直接删除主表，会显示删除失败。其原因是破坏了表的参照完整性。如果确定要进行删除，就需要先删除主表对应的子表，然后再删除主表，但是这样会删除掉两个表的数据。有时候我们需要保留子表的数据，就需要单独删除主表，这个时候就可以先删除关联的外键约束，然后再进行删除即可。

2. 数据库的存储引擎

　　数据库存储引擎是数据库低层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。不同的数据引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。MySQL的核心就是存储引擎。

　　MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的需求，可以对每一张表使用不同的存储引擎。可以使用命令：show engines;来查看所有支持的引擎。

 　　其中Support列的值表示某种引擎是否可以使用，YES表示可以，NO表示不可以，DEFAULT表示该引擎是默认存储引擎。

2.1 InnoDB存储引擎

　　InnoDB是事务型数据库的首选引擎，它支持事务安全表(ACID)，支持行级锁和外键。

　　InnoDB主要特征有下面几种：

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供了一个类似于非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由的将InnoDB类型的表于其他MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
  2. InnoDB是为了处理巨大的数据量的并且获得最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎所不能匹敌的。
  3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件(或原始磁盘分区)。这与MYISAM表不一样，例如MYISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸。
  4. InnoDB支持外键完整性约束。
  5. InnoDB被用在众多需求高性能的大型数据库站点。

2.2 MYISAM存储引擎

 　　MYISAM是基于ISAM存储引擎，并对其进行扩展。它是在web、数据仓库和其他应用环境中比较常用的存储引擎之一。MyISAM拥有较高的插入、查询的速度，但是不支持事务。

　　MyISAM的主要特征有下面几种：

  1. 大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持。
  2. 当把删除和更新以及插入操作混合使用的时候，动态尺寸的行会产生更少的碎片。
  3. 每个MyISAM表的最大索引数是64，这个可以通过重新编译来进行改变，每个索引最大的列数是16个。
  4. 最大的键长度是1000字节，这个也可以通过重新编译来进行改变。
  5. BLOB和TEXT列可以被索引。
  6. NULL值被允许在索引列中。每个值占每个键的0~1个字节。
  7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。
  8. 每个表一个AUTO_INCREMENT列的内部处理。MyISAM位INSERT和UPDATE操作字段更新这一列。
  9. 可以把数据文件和索引文件放到不同的目录。
  10. 每个字符可以有不同的字符集。
  11. 有VARCHAR的表可以有不同的字符集。
  12. VARCHAR和CHAR列可以多达64KB。

2.3 MEMORY存储引擎

 　　MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

　　MEMORY的主要特征有下面几种：

  1. MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。
  2. MEMORY存储引擎执行HASH和BTREE索引。
  3. 可以在一个MEMORY表中有非唯一键。
  4. MEMORY表可以使用一个固定的记录长度的格式。
  5. MEMORY不支持BLOB或TEXT列。
  6. MEMORY支持AUTＯ_INCREMENT列和对包含NULL值的列的索引。
  7. MEMORY表在所有客户端之间共享。
  8. MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享。
  9. 当不需要MEMORY表的内容时，要释放被MEMORY表所占用的内存，可以执行DELETE FROM或者TRUNCATE TABLE，或者删除整个表。

2.4 存储引擎的选择

　　不同的存储引擎有各自的特点，下面时部分存储引擎的特点：

 　　如果要提供提交，回滚和崩溃恢复能力的事务安全能力，还要实现并发控制，InnoDB是个不错的选择。如果数据表主要是用来插入数据和查询记录，那么MyISAM引擎是个不错的选择。如果只是临时存放数据，并且数据量不大，并且也不需要较高的数据安全性，可以选择将数据保持在内存中的MEMORY引擎，mysql中使用该引擎作为临时表，存放查询的中间结果。如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive存储引擎比较适合存储归档数据，比如记录日志信息等等。

 

posted @ 2021-10-08 21:43  coffee_kai  阅读(126)  评论(0)  编辑  收藏  举报