【MySQL】 MySQL基础DDL详解(数据库、表、约束等操作)

数据定义语言（Data Definition Language,DDL）是SQL语言集中负责数据结构定义与数据库对象定义的语言。由CREATE、ALTER与DROP三个语法所组成，现在是SQL指令中一个子集。

一、DDL操作数据库

• 创建数据库
  语法：

Create database 数据库名称;

• 示例：

#创建一个db_test数据库
create database db_test;

• 查看当前数据库实体有多少个数据库
  语法：

show 数据库名;

• 示例：

#查看数据db_test信息
show db_test

• 修改数据库字符集
  语法：

alter database 数据库名称 default character set 字符集

• 示例：

#更改数据库db_test为utf-8
alter database db_test default character set utf8

• 查看数据属性
  语法：

show create database 数据库名

• 示例：

show create database db_test

• 删除数据库
  语法：

drop database 数据库名

• 示例：

drop database db_test

• 切换数据库：
  语法：

use 数据库名

• 示例：

#切换到db_test数据库
use db_test

二、DDL操作表

• 创建表
  语法：
  命名 ，列名是自定义，多列之间使用“，” 隔开，最后一列不能使用"，" 【约束】 可有可无
  示例：

create table gy_user(
   u_name varchar(12) not null,
   u_age  int default 0,
   u_desc varchar(2000),
   u_datetime time 
) 

• 修改表修改表是指修改数据库中已经存在的表的定义。修改表比重新定义一个表更简单，不需要重新加载数据。不会影响正在进行的环境。修改表不但可以修改表中的列，同时也可以修改表名

• 修改表名
  语法：

alter table 旧表名 rename [to] 新表名

• 示例：

#把表db_test重新命名为db_test_new
alter table db_test rename db_test_new

• 修改表字符集
  语法：

alter table 表名 character set 字符集名称;

• 示例：

#修改字符集
alter table db_test character set gbk

• 修改表的列类型
  语法：

alter table 表名 modify 属性名 数据类型

• 示例

alter table db_test modify name varchar(32) not null;

• 修改字段名称
  语法：

alter table 表名 change 列名 新列名 新数据类型

• 示例：

alter table db_name change name truename varchar(22)

• 添加表列
  语法：

alter table 表名 add新字段名 数据类型；

• 示例：

alter table db_name add deptId int default 0;

• 设置列为自动增量属性
  语法：

alter table 表名 change column 列名 数据类型 NOT NULL AUTO_INCREMENT;

• 示例：

alter table db_name change column u_id not null auto_incremen

• 从表中删除列
  语法：

alter table 表名 drop column 列名

• 示例：

alter table db_name drop column name

• 删除表
  语法：

drop table 表名

• 示例：

drop table db_name
• 1

• 如果表有主外键约束时，删除表数据库会报错：

Cannot delete or update a parent row: a foreign key constraint fails。

• 此时应该先删除子表的外键约束，然后再删除表。此方法不有影响正常业务。
• 查看表属性

• 创建时查看表字段信息
  语法：

1. show create table 表名
2. show create view 视图名
3. show create database 数据库

• 示例：

#创建是查看表信息
show create table db_name;
#创建时查看视图信息
show create view vw_name_info
#创建数据库是查看信息

• 查看表字段信息
  语法：

describe 表名称

• 示例:

describe db_name

• 查询表详细的字段信息
  语法:

查看表信息

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA =数据库名 and table_name=表名;

查看字段信息

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA =数据库名 and table_name=表名;

• 示例：

#查看表信息
SELECT * FROM `information_schema`.`TABLES`  WHERE TABLE_SCHEMA ='goyeer_db' and table_name='db_name';
#查看字段信息
SELECT * FROM `information_schema`.`COLUMNS`  WHERE TABLE_SCHEMA ='goyeer_db' and table_name='db_name';

三、DDL添加约束

• 约束分类（六大约束）

1. NOT NUL（非空约束）： 用于保证该字段的值不能为NULL；
2. DEFAULT （默认值约束）： 用于保证该字段的值有默认值；
3. PRIMARY KEY（主键约束）： 用于保证该字段的值具有唯一性且非空；
4. UNIQUE（唯一约束）： 用于保证该字段的值具有唯一性，但可以为空
5. FOREIGN KEY REFERENCES：外键：用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值（在从表添加外键约束，用于引用主表某列的值）

• 添加约束的时机：
  

• 创建表
• 修改表

• 约束的权限分类：
  

1. 列级约束：六大约束都支持，但外键约束没有效果，不支持起约束名，可以在一个字段上追加多个，中间用空格隔开，没有顺序要求。
2. 表级约束：除非空和默认约束，其他的都支持，可以起约束名（对主键无效）。

• 创建表时添加约束

• 添加列级约束
  语法：

直接在字段名或类型后，追加约束类型。此类型只支持：默认约束、非空约束、主键约束、唯一约束；

• 示例：

CREATE TABLE goyeer_username(
    u_id INT PRIMARY KEY,  #主键
    u_name VARCHAR(20) NOT NULL,  #非空
    u_job_name INT UNIQUE,  #唯一
    u_age INT DEFAULT 18,  #默认约束
    u_depart_id INT REFERENCES u_depart(id) #外键
);

• 添加表级约束
  语法：

在创建表所有列的下方，CONSTRAINT 约束名 约束类型（字段名）

• 示例：

CREATE TABLE goyeer_username(
    u_id INT, 
    u_name VARCHAR(20), 
    u_job_name int  , 
    u_age INT ,  
    u_depart_id INT,
    CONSTRAINT ab PRIMARY KEY(id),  #主键
    CONSTRAINT uq UNIQUE(u_job_name),  #唯一键
    CONSTRAINT fk_depart_id FOREIGN KEY(u_depart_id) REFERENCES u_depart(id) #外键    
);

• 修改表约束
  
• 特点：

（1）添加列级约束：

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;

（2）添加表级约束：

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名) 【外键的引用】;

• 添加非空约束

ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 NOT NULL;

• 添加默认约束

ALTER TABLE 表名 MODIFY COLUMN 列名 INT DEFAULT 0;

• 添加主键约束

ALTER TABLE 表名 MODIFY COLUMN 列名 INT PRIMARY KEY;

或：

ALTER TABLE 表名 ADD PRIMARY KEY(id);

• 添加唯一键

ALTER TABLE 表名 MODIFY COLUMN 列名 INT UNIQUE;

或：

ALTER TABLE 表名 ADD UNIQUE(列名);

• 添加外键

ALTER TABLE 表名 ADD FOREIGN KEY(列名) REFERENCES 表名(列名);

或添加外键名：

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY(列名) REFERENCES 表名(列名);

• 删除表约束

• 删除非空约束

ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 NULL;

• 删除默认约束

ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型;

• 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

• 删除唯一

ALTER TABLE 表名 DROP INDEX 列名;

• 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;