美文网首页MySQL
25-约束-ALTER

25-约束-ALTER

作者: 紫荆秋雪_文 | 来源:发表于2022-08-30 17:14 被阅读0次

    一、创表

    CREATE TABLE offices
    (
        officecode INT(10),
        city       VARCHAR(50) NOT NULL,
        address    VARCHAR(50),
        country    VARCHAR(50) NOT NULL,
        postalcode VARCHAR(15) UNIQUE,
        PRIMARY KEY (officecode)
    );
    
    • 外键
    CREATE TABLE employees
    (
        employeenumber INT(11) PRIMARY KEY AUTO_INCREMENT,
        lastname       VARCHAR(50) NOT NULL,
        firstname      VARCHAR(50) NOT NULL,
        mobile         VARCHAR(25) UNIQUE,
        officecode     INT(10)     NOT NULL,
        jobtitle       VARCHAR(50) NOT NULL,
        birth          DATETIME    NOT NULL,
        note           VARCHAR(255),
        sex            VARCHAR(5),
        CONSTRAINT fk_emp_ofcode FOREIGN KEY (officecode) REFERENCES offices (officecode)
    );
    

    二、ALTER

    • 将表employees的mobile字段修改到officeCode字段后面
    ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
    
    • 将表employees的birth字段改名为employee_birth
    ALTER TABLE employees CHANGE birth employee_birth DATETIME; 
    
    • 修改sex字段,数据类型为CHAR(1),非空约束
    ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
    
    • 删除字段note
    ALTER TABLE employees DROP COLUMN note; 
    
    • 增加字段名favoriate_activity,数据类型为VARCHAR(100)
    ALTER TABLE employees ADD favoriate_activity VARCHAR(100); 
    
    • 将表employees名称修改为employees_info
    ALTER TABLE employees RENAME employees_info;
    

    相关文章

      网友评论

        本文标题:25-约束-ALTER

        本文链接:https://www.haomeiwen.com/subject/soingrtx.html