# 管理单元

  • MySQL 的管理单元主要分为:
    • 数据库(database)
      • 一个 MySQL 实例中可以创建多个数据库。
      • MySQL 中的 database 又称为 schema 。
    • 数据表(table)
      • 一个数据库中可以创建多个数据表,每个数据表需要定义至少一个列。
    • 数据行(row)
      • 一个数据表中可以插入多行数据。
  • database、table 的名称区分大小写,而 column、index 的名称不区分大小写。
    • 这些标识符一般都采用小写,方便与 SQL 的大写关键字区分开来。
    • 如果在同一作用域创建同名的对象,则会报错:Duplicate name

# 数据库

SHOW DATABASES;             -- 显示所有数据库
SHOW DATABASES LIKE 'xx%';  -- 筛选出指定名称的数据库

USE <db>;                   -- 切换到指定的数据库(客户端同时只能切换到一个数据库)

CREATE DATABASE <db>;       -- 创建一个数据库
DROP DATABASE <db>;         -- 删除一个数据库
  • MySQL 自带了四个数据库:
    • mysql :用于存储用户表、权限表、当前引擎、事件、日志等重要信息。
    • information_schema :用于存储元数据,比如所有存在的数据库、数据表、索引,所有可用的引擎、字符集、文件。
    • performance_schema :用于存储数据库的运行状态、性能指标。
    • sys :用途与 performance_schema 相似,但内容少一些。

# 数据表

#

SHOW TABLES;            -- 显示当前数据库的所有数据表
SHOW TABLES LIKE 'xx%'; -- 筛选出指定名称的数据表

SHOW TABLE STATUS;      -- 显示所有数据表的状态
SHOW COLUMNS FROM <db>; -- 显示指定数据表中所有列的配置信息
DESC <tb>;              -- 相当于 SHOW COLUMNS FROM <tb>;
SHOW CREATE TABLE tb1;  -- 查看创建某个表的完整 CREATE 命令,便于拷贝或修改该表
  • 切换到一个数据库之后,就可以直接操作其中的数据表。否则每次操作数据表时,都需要指明是哪个数据库。如下:
    SHOW TABLES FROM <db>;
    desc <db>.<tb>;
    

#

例:

CREATE TABLE tb1(                                         -- 创建一个数据表,名为 tb1
    id            int           NOT NULL AUTO_INCREMENT,  -- 定义一个字段,名为 id ,数据类型为 int
    name          varchar(255)  NOT NULL,
    update_time   datetime      NOT NULL,
    PRIMARY KEY (id)                                      -- 将 id 字段声明为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;                   -- 设置存储引擎为 InnoDB ,默认字符集为 utf8mb4
  • 字段的配置详见另一章节:字段
  • 采用以下两种措施,可以避免重复创建某个表:
    • 如果存在同名的表,则先删掉它,再创建:
      DROP TABLE IF EXISTS tb1;
      CREATE TABLE tb1(...);
      
    • 如果不存在同名的表,才创建它:
      CREATE TABLE IF NOT EXISTS tb1(...);
      
  • 拷贝一张表:
    CREATE table tb2 LIKE tb1;          -- 拷贝表结构
    INSERT INTO tb2 SELECT * FROM tb1;  -- 拷贝每行数据
    
  • 创建临时表:
    CREATE temporary table tb1(...);
    
    • 当客户端关闭连接时,临时表会被自动删除。
    • 执行 SHOW tables; 命令时看不到临时表,必须要指定表名才能看到临时表。

#

ALTER TABLE tb1 RENAME TO tb2;        -- 修改数据表的名字
ALTER TABLE tb1 ENGINE = myisam;      -- 修改数据表的引擎

ALTER TABLE tb1 ADD update_time date;         -- 添加一个列(默认排在最后一列),列名为 update_time ,数据类型为 date
                [FIRST]                       -- 排在第一列
                [AFTER <field>]               -- 排在指定列之后
ALTER TABLE tb1 CHANGE update_time `t` date;  -- 将 update_time 列改名为 t ,数据类型为 date
ALTER TABLE tb1 MODIFY update_time char(10);  -- 修改一个字段的定义
ALTER TABLE tb1 DROP   update_time;           -- 删除一个字段
  • 修改数据表的定义结构时,可能因为数据类型变化而不兼容已有的数据行,或者字段顺序变化而不兼容旧的客户端操作。因此应该尽量提前规划数据表,避免大幅修改。
  • MySQL 不支持重命名数据库,但可以跨库移动表:
    ALTER TABLE db1.tb1 RENAME TO db2.tb1;
    

#

DROP TABLE tb1;   -- 删除数据表

# 数据行

#

SELECT * FROM tb1;                    -- 获取数据表 tb1 中,每条数据的全部字段
SELECT id,name FROM tb1 WHERE id=1;   -- 获取数据表 tb1 中,字段 id 取值为 1 的那条数据的指定字段
  • 查询语法详见另一章节:查询

#

-- 向数据表中插入一行数据,先声明字段名的列表,再传入相同元素数量的 VALUES 列表
INSERT INTO tb1 (id, name, update_time) VALUES (null, 'one', '2020-01-01');

-- 可改变字段名的顺序,与数据表的字段定义顺序不同,但 VALUES 顺序也要相应改变
INSERT INTO tb1 (name, id, update_time) VALUES ('one', null, '2020-01-01');

-- 可以不给一些字段插入值,此时会采用默认值
INSERT INTO tb1 (name, update_time) VALUES ('one', '2020-01-01');

-- 可以连续插入多行数据
INSERT INTO tb1 (id, name, update_time) VALUES (null, 'one', '2020-01-01'), (null, 'two', '2020-01-02');

-- 插入一行数据时,可省略字段名的列表,此时必须按顺序传入各个字段的值
INSERT INTO tb1 VALUES (null, 'one', '2020-01-01');
  • 需要插入几千、几万条数据时,建议合并成一条 INSERT 语句再执行,通常会大幅降低总耗时。
    • 因为每执行一条 INSERT 语句,都需要网络传输给服务器,被服务器解析后执行。合并成一条 INSERT 语句可节省大量开销。
    • 合并成一条 INSERT 语句之后的体积不宜太大,不能超过 max_allowed_packet ,也会影响 innodb_buffer_pool 内存开销。

#

UPDATE tb1 SET name='one';              -- 修改所有数据行的 name 字段的值
UPDATE tb1 SET name='one' WHERE id=1;   -- 可以加上 WHERE 子句,只修改匹配查询条件的数据行
UPDATE tb1 SET name='one', update_time='2020-01-01';    -- 可以修改多个字段
  • UPDATE 语句修改多条数据时,如果有一条修改失败,则会立即终止 UPDATE 语句,并回滚之前修改的数据行,从而实现原子性操作。
    • 如果执行 UPDATE IGNORE ... 语句,则会忽略报错,继续尝试修改之后的数据。

#

DELETE FROM <tb>;           -- 逐一删除数据表中的所有数据行,这会有一定耗时
DELETE FROM <tb> WHERE ...; -- 只删除匹配查询条件的数据行

TRUNCATE [TABLE] <tb>;      -- 先 DROP 表,然后重新创建表。这样清空一张表,耗时很少