MySQL-数据库管理

创建数据库

• DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删
  旧库完成的

# 创建数据库
CREATE DATABASE 数据库名;

# 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

# 判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;

数据库使用

• 要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数
  据库名.表名”

# 查看当前所有的数据库
SHOW DATABASES; # 有一个S，代表多个数据库

# 查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数

# 查看指定库下所有的表
SHOW TABLES FROM 数据库名;

# 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE 数据库名\G

# 使用/切换数据库
USE 数据库名;

修改数据库

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

删除数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;

创建表

1. 基本语法

• IF NOT EXISTS
  • 如果当前数据库中不存在要创建的数据表，则创建数据表
  • 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表
• 必须指定：表名，列名(或字段名)，数据类型，长度
• 可省略指定：约束条件，默认值

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
);

2. 创建表并插入数据

• 使用 AS subquery 选项，将创建表和插入数据结合起来
• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

3. 查看数据表结构

SHOW CREATE TABLE 表名\G
DESC 表名;

修改表

1. 增加字段

# 语法
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

# 示例
ALTER TABLE dept80 ADD job_id varchar(15);

2. 修改字段

• 可以修改列的数据类型，长度、默认值和位置

# 语法
ALTER TABLE 表名 
    MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

# 示例
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;

3. 重命名字段

# 语法
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

# 示例
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

4. 删除字段

# 语法
ALTER TABLE 表名 DROP 【COLUMN】字段名

# 示例
ALTER TABLE dept80 DROP COLUMN job_id;

重名表

# 语法一：使用RENAME 
RENAME TABLE emp TO myemp;

# 语法二：使用ALTER 
ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

删除表

• 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除
• 数据和结构、相关索引都被删除
• 所有正在运行的相关事务被提交
• DROP TABLE 语句不能回滚

# 语法
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

# 示例
DROP TABLE dept80;

清空表

• 删除表中所有的数据
• 释放表的存储空间
• TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
• TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句

# 语法
TRUNCATE TABLE 表名
DELETE FROM 表名;

# 示例
TRUNCATE TABLE detail_dept;
DELETE FROM emp2;

MySQL8新特性—DDL的原子化

• 在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚
• DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作
• 通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中

# 在MySQL 5.7版本中，执行删除表1和表2，表2不存在报错，表1依旧被删除
# 在MySQL 8.0版本中，删除表2报错，操作回滚，表1依旧存在
DROP TABLE book1,book2;

数据增删改

1. 插入数据

• 为表的所有字段按默认顺序插入数据，值的顺序必须和数据表中字段定义时的顺序相同

# 语法
INSERT INTO 表名 VALUES (value1,value2,....);

# 示例
INSERT INTO departments VALUES (70, 'Pub', 100, 1700);

• 为表的指定字段插入数据，值与字段的顺序、类型必须保持一致

# 语法
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);

# 示例
INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');

• 同时插入多条
  • 一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句
  • 但是多行的INSERT语句在处理过程中效率更高

# 语法一
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

# 语法二
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

• 将查询结果插入到表中，插入列与查询列的顺序保持一致

# 语法
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

# 示例
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

2. 更新数据

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

# 语法
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

# 示例
UPDATE employees SET department_id = 70 WHERE employee_id = 113;

3. 删除数据

# 语法
DELETE FROM table_name [WHERE <condition>];

# 示例
DELETE FROM departments WHERE department_name = 'Finance';

4. MySQL8新特性：计算列

• 插入a、b数据，c自动得到a+b的值

CREATE TABLE tb1(
    id INT,
    a INT,
    b INT,
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

INSERT INTO tb1(a,b) VALUES (100,200);