【数据库】【MySQL】创建数据库、数据表、修改数据表字段的一些基本操作

# 创建数据库yggl_mllt9920
CREATE DATABASE yggl_mllt9920;
# 选择数据库
USE yggl_mllt9920;
# 创建数据表 salary_mllt9920
CREATE TABLE salary_mllt9920(
员工编号 CHAR(6) NOT NULL PRIMARY KEY,
收入 Float(8,2) NOT NULL,
支出 Float(8,2) NOT NULL
);
# 创建数据表 departments_mllt9920
CREATE TABLE departments_mllt9920(
部门编号 CHAR(3) NOT NULL PRIMARY KEY,
部门名称 CHAR(20) NOT NULL,
备注 TEXT(16) NOT NULL
);
# 查询所有数据表
SHOW TABLES;
DESC salary_mllt9920;
DESC departments_mllt9920;
# 复制数据表departments_mllt9920_test
CREATE TABLE departments_mllt9920_test LIKE departments_mllt9920;
DESC departments_mllt9920_test;
# 在表中新建列
ALTER TABLE departments_mllt9920_test
ADD COLUMN 电话 CHAR(12) NOT NULL;
DESC departments_mllt9920_test;
# 修改默认值
ALTER TABLE departments_mllt9920_test MODIFY  电话 CHAR(12) NOT NULL DEFAULT '010123456789';
DESC departments_mllt9920_test;
# 修改字段名
ALTER TABLE departments_mllt9920_test CHANGE 电话 部门电话 CHAR(12);
DESC departments_mllt9920_test;
# 修改字段类型
ALTER TABLE departments_mllt9920_test MODIFY  部门电话 VARCHAR(18) NOT NULL DEFAULT '010123456789';
DESC departments_mllt9920_test;
# 删除字段
ALTER TABLE departments_mllt9920_test DROP COLUMN 备注;
DESC departments_mllt9920_test;
# 修改字段名-方法2
ALTER TABLE departments_mllt9920_test RENAME COLUMN 部门电话 TO 部门主管电话;
DESC departments_mllt9920_test;