所在位置：

mysql的数据库管理

admin mysql mysql

在本章中，主要是对mysql数据库管理的一些概念的理解和实践，如果对某一部分有兴趣，可以上网去详细地了解，下面是关于数据库的一些基本概念：

数据库（DB）

数据库就是按照数据结构来组织、存储数据的仓库

数据库管理系统（DBMS）

数据库管理系统(Database Management System)是一套操纵和管理数据库的软件，是用于建立、使用和维护数据库

数据库系统（DBS）

数据库系统包括下面几个方面：

  • 数据库
  • 数据库管理系统（及其开发工具）
  • 应用系统
  • 数据库管理员
  • 用户

数据库类型

关系型数据库

关系数据库就是采用关系模型作为数据组织方式。简单地说数据的逻辑结构是一张二维表，由行和列组成。表的每一行为一个元组，每一列为一个属性。

关系型数据库具有以下的四个 ACID 特性：

  1. 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节

  2. 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏

  3. 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

  4. 持久性（Durability）：事务处理结束后，对数据的修改就是永久的

非关系型数据库

非关系型数据库是对不同于传统的关系数据库的数据库管理系统的统称

常见的非关系型数据库：

  1. Redis
  2. MongoDB
  3. Memcached

数据库事务

数据库事务（简称：事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的：

  1. 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。

  2. 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

数据库索引

数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

数据库索引的分类：

  1. 普通索引

  2. 唯一性索引

  3. 单列索引

  4. 多列索引

  5. 全文索引，只能是 MYISAM 引擎

索引的设计原则：

  1. 为经常需要排序、分组和联合操作的数据列要建立索引

  2. 为经常用来作为查询条件的数据列建立索引

  3. 索引不是建的越多越好

  4. 数据量少的数据列不需要建立索引

数据库的常用数据类型

数据库的常用数据类型可以分为以下的三种：

1. 数字

数字包括两种数据类型：

  • 整数

    • TINYINT，大小为 1 字节
    • SMALLINT，大小为 2 字节
    • MEDIUMINT，大小为 3 字节
    • INT，大小为 4 字节
    • BIGINT，大小为 8 字节

  • 浮点数

    • FLOAT，大小为 4 字节
    • DOUBLE，大小为 8 字节
    • DECIMAL，大小为要看 M 和 D的值，DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
2. 字符串

字符串的数据类型：

  • TINYTEXT，大小为 0-255 字节
  • CHAR，大小为 0-255 字节
  • VARCHAR，大小为 0-65535 字节
  • TEXT，大小为 0-65535 字节
  • MEDIUMTEXT，大小为 0-16777215 字节
  • LONGTEXT，大小为 0-4294967295字节 字节
3. 时间和日期

时间和日期的类型有：

  • YEAR，大小为 1 字节
  • DATE，大小为 3 字节
  • TIME，大小为 3 字节
  • DATETIME，大小为 8 字节
  • TIMESTAMP，大小为 4 字节

数据库操作

查看所有数据库
SHOW DATABASES;
选中某个数据库
USE 数据库名;
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;

注意： 可以进入到数据库目录去删除某个数据库名的目录，但不建议这样做，如果你使用了二进制日志文件，这些操作不会被记录

显示创建某个数据库的详情
SHOW CREATE DATABASE 数据库名\G;

表管理操作

查看某个数据库的所有表
show tables;
显示创建某个表的详情
SHOW CREATE TABLE 表名\G;
分析查询语句
EXPLAIN SELECT * FROM 表名\G;
显示表的字段属性
SHOW COLUMNS FROM 表名; # 等同于 DESC 表名;
创建数据库表
CREATE TABLE [IF NOT EXISTS] 表名 (列名1 数据类型 [约束] [默认值], 列名2 数据类型 [约束] [默认值])[ENGINE=INNODB] [DEFAULT] [CHARSET=utf8];
删除数据库表
DROP TABLE [IF EXISTS] 表名;

表数据操作

插入数据

单行插入有两种方式：

第一种：

INSERT INTO 表名 SET 字段名1=字段值1, 字段名2=字段值2;

第二种：

INSERT INTO 表名[(字段名1, 字段名2)] VALUES(字段值1, 字段值2);

多行插入：

INSERT INTO 表名[(字段名1, 字段名2, 字段名3)] VALUES(字段值1, 字段值2, 字段值3), (字段值4, 字段值5, 字段值6);

完整地复制另外一个表

  1. 复制表结构：
CREATE TABLE 表名1 like 表名2;
  1. 复制表数据：
INSERT INTO 表名1(字段名1, 字段名2) SELECT 字段名a, 字段名b FROM 表名2 [WHERE ...];

注意： 如果两个表结构一样，可以不写字段名，比如：

INSERT INTO 表名1 SELECT * FROM 表名2;
删除数据
DELETE FROM 表名 [WHERE ...];
修改数据
UPDATE 表名 SET 字段1=字段值1, 字段2=字段值2 [WHERE ...];

事务操作

事务的三个命令：

  1. BEGIN：开启一个事务
  2. ROLLBACK：撤销尚未保存到数据库中的事务
  3. COMMIT：保存事务对数据库所做的更改

说明： MySQL默认是执行一条语句就是一个事务的，我们可以通过运行下面的命令来查看

show variables like '%commit';

如果看到 'autocommit' 的值是 'ON' 的话，就是默认执行一句语句就是一个事务，我们可以改理 'autocommit' 的值为 'OFF'，来手动开启事务，执行下面的命令：

SET autocommit=0;

举例

SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO mytable values(1, 'test');
SELECT * FROM mytable; # 注意，在这里查询是有值的，如果新开一个连接，这时查询 mytable 是没有值的，因为没有 commit;
ROLLBACK; # 这里是可以撤消的，但是 commit 之后就不能了，一定要在 commmit 之前
COMMIT;

应用： 如果在导入很多数据的时候，可以先把 autocommit 关闭，等导完 commit 了再打开，因为autocommit=1的时候导入每条数据都是一个事务处理，要消耗时间，而 autocommit=0,就可以把所有的数据当作一个事务来处理，最后要记得 commit;

数据库约束

数据库常用的有五种约束：

主键约束：唯一标识数据表中的行/记录，要求主键列的数据唯一并且不允许为空
CREATE TABLE IF NOT EXISTS tb1 (
    id INT AUTO_INCREMENT,
    name varchar(10),
    PRIMARY KEY(id)
) ENGINE=innodb DEFAULT CHARSET=utf8;
外键约束：唯一标识其他表中的一条行/记录，如果不为空值，则每个外键值必须等于另一个表中主键的某个值，并且外键的数据类型必须跟主键一致
CREATE TABLE IF NOT EXISTS tb2 (
    id INT,
    name varchar(10),
    CONSTRAINT fk_1 FOREIGN KEY(id) REFERENCES tb1(id)
) ENGINE=innodb DEFAULT CHARSET=utf8;

说明： 如果 tb1 插入了一条 id 为 1 的数据，那 tb2 只能插入 id 为 1 的值，tb1 没有的 id 值，tb2 不能插入

NOT NULL 约束：用于约束对应列中的值不能有空值
CREATE TABLE IF NOT EXISTS tb3 (
    id INT NOT NULL,
    name varchar(10),
    PRIMARY KEY(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
DEFAULT 约束：用于约束对应列中的值的默认值，除非默认为空值，否则不能插入空值
CREATE TABLE IF NOT EXISTS tb4 (
    id INT NOT NULL,
    name varchar(10) DEFAULT 'test',
    PRIMARY KEY(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

INSERT INTO tb4 SET id=1;

INSERT INTO tb4 VALUES(1, 'good');
UNIQUE 约束：用于约束对应列中的值不能有重复值
CREATE TABLE IF NOT EXISTS tb5 (
    id INT NOT NULL,
    name varchar(10) UNIQUE,
    PRIMARY KEY(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

INSERT INTO tb5 VALUES(1, 'test');
INSERT INTO tb5 VALUES(2, 'test'); # 报错，name 不能重复

表结构操作

修改表名
ALTER TABLE 旧表名 RENAME 新表名;
修改字段的数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
修改字段
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
添加字段
ALTER TABLE 表名 ADD 新字段 新字段类型 [约束条件] [FIRST | AFTER 已存在的字段名];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表的存储引擎
ALTER TABLE 表名 ENGINE=新的存储引;
添加索引
ALTER TABLE 表名 ADD INDEX 索引名(字段);
删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

索引操作

创建普通索引
CREATE TABLE IF NOT EXISTS tb_index(
    id INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    INDEX(name)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
创建唯一性索引
CREATE TABLE IF NOT EXISTS tb_index(
    id INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    UNIQUE INDEX id_idx(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
创建单列索引
CREATE TABLE IF NOT EXISTS tb_index(
    id INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    age INT(2),
    INDEX(age)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
创建多列索引
CREATE TABLE IF NOT EXISTS tb_index(
    id INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    age INT(2),
    INDEX(id, name, age)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
创建全文索引
CREATE TABLE IF NOT EXISTS tb_index(
    id INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    age INT(2),
    FULLTEXT INDEX(name)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

【上一篇】Mac常用软件

【下一篇】mac下tmux的配置和常用用法

相关文章