MySQL 数据库操作

1. DDL

数据定义语言（Data Definition Language, DDL）是一组用于创建、修改和删除数据库对象的SQL语句。这些对象包括数据库本身（schema）、表、视图、索引、触发器、存储过程等

2. 数据库管理

2.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
    [DEFAULT] CHARACTER SET charset_name
    [COLLATE collation_name];

示例：
创建数据库并指定其字符集为 UTF-8，以及排序规则为 utf8_general_ci

CREATE DATABASE IF NOT EXISTS database_name 
CHARACTER SET utf8 
COLLATE utf8_general_ci;

2.2 常用命令

-- 查看所有数据库
show databases;

-- 切换数据库
use mydb;

2.3 删除数据库

DROP DATABASE [IF EXISTS] database_name;

3. 表管理

3.1 创建表

CREATE TABLE table_name (
    column_name data_type [NOT NULL | NULL] [DEFAULT default_value],
    column_name2 data_type ...,
    PRIMARY KEY (column_list),
    FOREIGN KEY (column_list) REFERENCES other_table (column_list),
    INDEX index_name (column_list),
    ...
    [ENGINE=engine_name]
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
);

-- 删除表
DROP TABLE [IF EXISTS] table_name;

-- 重命名表
RENAME TABLE old_table_name TO new_table_name;

3.2 修改表

添加列：

ALTER TABLE table_name
    ADD COLUMN column_name data_type [NOT NULL | NULL] [DEFAULT default_value];

-- 示例：
alter table user
    add column code varchar(10);

alter table user
    add column code varchar(10) comment 'xxx',
    add column name varchar(20) comment 'xxx';

新增列并指定列位置

ALTER TABLE table_name
ADD COLUMN new_column_name data_type [NOT NULL | NULL] [DEFAULT default_value]
[COMMENT 'column_comment']
[FIRST | AFTER existing_column_name];

-- 示例：
alter table tableName 
    add column addC int default 0 after c2;

修改列定义：

-- 语法 alter table 表名 modify column 字段名 类型;
ALTER TABLE table_name
    MODIFY COLUMN column_name new_data_type [NOT NULL | NULL] [DEFAULT new_default_value];

-- 示例：
alter table tableName 
    modify column city char(30);

删除列：

ALTER TABLE table_name
    DROP COLUMN column_name;

-- 示例：（注：谨慎修改类型，可能会导致原有数据出错）
alter table user 
    drop column age, drop column address;

3.3 删除表数据

方式一：清空表

TRUNCATE TABLE table_name;
-- 注 : truncate 操作中的 table 可以省略

TRUNCATE 可快速清空表中的所有数据，并且会自动重置自增ID计数器，它比 DELETE 更高效，特别是对于大表，因为它不会记录每条被删除行的详细信息到事务日志

方式二：delete

DELETE * FROM table_name; 
-- 注：delete 操作中的 * 可以省略

-- 删除表中的部分记录，可限制 where 条件
DELETE FROM table_name WHERE condition;

DELETE 语句执行后，可以通过事务回滚来撤销操作（如果在支持事务的存储引擎上执行）。
DELETE 不会重置自增主键（如 AUTO_INCREMENT 列）的计数。

3.4 复制表

方式一：使用 CREATE TABLE ... SELECT 语句一次完成结构复制与数据迁移

CREATE TABLE new_table AS
SELECT *
FROM old_table;

方式二：分步复制：先创建结构，再插入数据

-- 1. 创建新表结构
CREATE TABLE new_table LIKE old_table;

-- 2. 复制数据
INSERT INTO new_table
SELECT * FROM old_table;

3.5 其他

-- 查看所有表
show tables;
-- 查看表结构
desc tableName;

show columns from tableName;  
describe tableName;  
show create table tableName;