MySQL数据表操作

数据类型

• MySQL常见数值类型

  类型	大小	范围(有符号)	范围(无符号)	用途
  TINYINT	1	(-128, 127)	(0, 255)	小整数值
  INT	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
  BIGINT	8	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
  FLOAT	4			单精度浮点数
  DOUBLE	8			双精度浮点数
  DECIMAL				小数值

• MySQL日期和时间类型

  类型	大小	格式	用途
  DATE	3	YYYY-MM-DD	日期值
  TIME	3	HH:MM:SS	时间值
  YEAR	1	YYYY	年份值
  DATETIME	8	YYYY-MM-DD hh:mm:ss
  TIMESTAMP	4		时间戳

• MySQL字符串类型

  类型	大小	用途
  CHAR	0-255	定长字符串
  VARCHAR	0-65535	变长字符串
  BLOB	0-65535	二进制形式的长文本数据
  TEXT	0-65535	长文本数据
  LONGTEXT		极大文本数据

• MySQL枚举和集合类型

  类型	作用
  ENUM	枚举类型,用于存储单一值,可以选择一个预定义的集合
  SET	集合类型,用于存储多个值,可以选择多个预定义的集合

命令合集

• 查询数据库下的所有数据表

  SHOW TABLES;
  

• 创建数据表

  -- 创建语句说明
  CREATE TABLE `数据表名称`  (
      `字段名称` 字段类型 [是否为NULL] [默认值] [字段注释],
       .....
       主键信息
       索引信息
  )
  

• 查询表结构

     DESC 数据表名;
     ```
  

• 查询建表语句

  SHOW CREATE TABLE 数据表名;
  

• 数据表修改

  操作类型	SQL 语法示例	说明
  添加字段	ALTER TABLE table_name ADD COLUMN column_name column_type;	添加新字段到表中
  修改字段类型	ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;	更改字段的数据类型
  修改字段名	ALTER TABLE table_name CHANGE COLUMN old_name new_name new_data_type;	修改字段的名称和类型
  删除字段	ALTER TABLE table_name DROP COLUMN column_name;	删除表中的某个字段
  添加外键	ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES other_table(id);	添加外键约束
  删除外键	ALTER TABLE table_name DROP FOREIGN KEY fk_name;	删除外键约束
  添加索引	ALTER TABLE table_name ADD INDEX index_name (column_name);	在指定字段上创建索引
  删除索引	ALTER TABLE table_name DROP INDEX index_name;	删除已存在的索引
  修改表名	ALTER TABLE old_table_name RENAME TO new_table_name;	修改表的名称
  修改存储引擎	ALTER TABLE table_name ENGINE = InnoDB;	更改表的存储引擎，如 InnoDB 或 MyISAM

• 数据表清空

  TRUNCATE TABLE 数据表名称;
  

• 数据表删除

  DROP TABLE [IF EXISTS] 数据表名称;
  

实操案例

• 新建company(公司)数据库

  -- 获取所有数据库
  SHOW DATABASES;
  -- 创建company数据库
  CREATE DATABASE IF NOT EXISTS company DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin;
  

  

• 切换company,创建workers(职工)表

  • 分析职工表的字段设计

    字段名称	字段描述	类型	是否允许为空值	默认值	主键	索引
    id	员工id	int	否		自增主键
    name	员工姓名	VARCHAR(255)	否
    gender	员工性别	CHAR(1)	否
    id_card	员工身份证号	VARCHAR(18)	否			唯一索引
    hire_date	入职日期	DATETIME	否

  • 创建职工表

    -- 切换到company数据库中
    USE company;
    -- 执行建表语句
    DROP TABLE IF EXISTS `workers`;
    CREATE TABLE `workers`  (
    `id` int NOT NULL COMMENT '员工id',
    `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1' COMMENT '员工姓名',
    `gender` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '员工性别',
    `id_card` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '身份证编号',
    `hire_date` datetime NOT NULL COMMENT '入职日期',
    PRIMARY KEY (`id`, `name`) USING BTREE,
    UNIQUE INDEX `card`(`id_card` ASC) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '职工信息表' ROW_FORMAT = Dynamic;
    

    

• 数据表查询

  -- 获取数据库下所有的数据表
  SHOW TABLES;
  -- 查询职工表结构
  DESC workers;
  -- 查询职工表建表语句
  SHOW CREATE TABLE workers;
  

  

  

• 修改职工表

  • 修改分析

    1. 将入职日期字段hire_date名称修改为employment_date,并将datetime类型,修改为date类型
    2. 新增一个phone(联系方式)字段,设置为VARCHAR(11)

  • 修改职工表

    ALTER TABLE workers
    CHANGE COLUMN hire_date employment_date DATE NOT NULL,
    ADD COLUMN phone VARCHAR(11) NOT NULL COMMENT '联系方式';
    

    

• 查询职工表信息

          ```SQL
          -- 查询职工表结构
          DESC workers;
          -- 查询职工表建表语句
          SHOW CREATE TABLE workers;
          ```
  
  

  

• 删除职工表

  • 清空职工表中数据

    TRUNCATE TABLE workers;
    

  • 删除职工表

    DROP TABLE IF EXISTS workers;
    

  • 截图

    

• 注意事项

  1. DROP TABLE 不可撤销，务必备份数据
  2. 先检查外键约束、视图依赖，必要时先删除外键或视图。
  3. 使用 DROP TABLE IF EXISTS 避免表不存在的错误。
  4. 如果只是清空数据，使用 TRUNCATE TABLE 更高效。
  5. 删除操作慎重，避免误删生产环境的数据