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

本是古典何须时尚 2023-09-27 20:53 114阅读 0赞

## 【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
    
    如果表有主外键约束时，删除表数据库会报错：
    
        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 外键名称;