与MYSQL的零距离接触（笔记三） 慕课网

约束与修改数据表

   1.约束保证数据的完整性和一致性。
    2.约束分为表级约束和列级约束。
    3.约束类型分为：
        NOT NULL(非空约束)
        PRIMARY KEY（主键约束）
        UNIQUE KEY（唯一约束）
        DEFAULT（默认约束）
        FOREIGN KEY（外键约束）

表级约束可以同时用于两个或两个以上的字段使用，而列级约束只能针对某一个字段
默认和非空约束不存在表级约束中，只能存在列级约束中，而主键、唯一、外键约束 既可以存在表级约束，也可以存在列级约束中。

        
外键约束
FOREIGN KEY
    保持数据一致性，完整性。实现一对一或一对多关系。
外键约束的要求：
    1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
    2.数据表的存储引擎只能为InnoDB。
(编辑数据表的默认存储引擎    MySQL配置文件    default-storage-engine=INNODB)
    3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
    4.外键列和参照列必须创建索引。如果外键列不存在索引的话，mysql将自动创建索引。


net start mysql
mysql -uroot -p123456        
USE test；
创建数据表:
    CREATE TABLE provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL
    );
SHOW CREATE TABLE provinces;     查看数据表
创建数据表:
    CREATE TABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
    pid BIGINT,                //（创建不同的数据类型  无法创建成功）)
    FOREIGN KEY(pid) REFERENCES provinces(id)    //(外键约束)
重新修改：
    CREATE TABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
    pid SMALLINT,        //（相同的数据类型  不同的符号位也不能创建成功）)
    FOREIGN KEY(pid) REFERENCES provinces(id)    //(外键约束)
重新修改：
    CREATE TABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
    pid SMALLINT UNSIGNED,    //（相同的数据类型  相同的符号位 能创建成功）)
    FOREIGN KEY(pid) REFERENCES provinces(id)    //(外键约束)

显示索引：SHOW INDEXES FROM provinces;
换成网格的形态显示：SHOW INDEXES FROM provincesG;
    SHOW CREATE TABLE users;

    
外键约束的参照操作：
    1.CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
    2.SET NULL:从父表删除或更新行，并设置子表中的外键列为 NULL。
        如果使用该项，必须保证子表没有指定NOT NULL
    3.RESTRICT:拒绝对父表的删除或更新操作。
    4.NO ACTION :标准的MYSQL的关键字，在MYSQL中与RESTRICT相同。
例如：
    CREATE TABLE users(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL,
    pid SMALLINT UNSIGNED,
    FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
    );
    
    INSERT provinces(pname) VALUE("A") ;
    INSERT provinces(pname) VALUE("B") ;
    INSERT provinces(pname) VALUE("C") ;
查询插入记录  SELECT * FROM provinces;
    INSERT users1(pname,pid) values ("Tom",3);
    INSERT users1(pname,pid) values ("John",7);
    INSERT users1(pname,pid) values ("John",1);
    INSERT users1(pname,pid) values ("Rosc",3);
    
    DELETE FROM provinces WHERE id=3;


表级约束与列级约束
    对一个数据列创建的约束，称为列级约束。
    对多个数据表创建的约束，称为表级约束。
    列级约束既可以再列定义时声明，也可在列定义后声明。
    表级约束只能在列定义后声明。
    
修改数据表
    添加单列
    ALTER TABLE tb1_name ADD [COLUMN] col_name column_definition [FIRST | ALTER col_name]
    例如：
    ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;  添加在列最后
    ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username;  添加到username后面
    ALTER TABLE users1 ADD  truename VARCHAR(20) NOT NULL FIRST;  添加列最前面
    
    添加多列
    ALTER TABLE tb1_name ADD [COLUMN] (col_name column_definition,...)
    
    删除列
    ALTER TABLE tb1_name DROP [COLUMN] col_name；
    ALTER TABLE users1 DROP truename; 删除truename列
    ALTER TABLE users1 DROP password，DROP age；删除password，age列
    
    添加主键约束
    ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)
    ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id)
    
    SHOW COLUMNS FROM users2;
    
    添加唯一约束
    ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] UNIQUR [INDEX | KEY] [inex_name] [index_type] (index_col_name,...)
    ALTER TABLE users2 ADD UNIQUE (username);
    
    添加外键约束
    ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]]  
    ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);

    添加/删除默认约束
    ALTER TABLE tb1_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
    
    ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;    users2添加一个数据类型
    SHOW COLUMNS FROM users2 查看数据表结构
    ALTER TABLE users2 ALTER age SET DEFAULT 15;  设置默认值15
    ALTER TABLE users2 ALTER age DROP DEFAULT; 删除默认约束
    
    删除主键约束
    ALTER TABLE tb1_name DROP PRIMARY KEY;
    ALTER TABLE users2  DROP PRIMARY KEY;
    

    删除唯一约束
    ALTER TABLE tb1_name DROP {INDEX|KEY} index_name;

            查找默认约束的名字    SHOW INDEXES FROM user;
    ALTER TABLE user DROP INDEX username;    //删除默认约束

    ALTER TABLE users2 DROP {INDEX|KEY} users2;
    
    删除外键约束
    ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1；
    
    
    
修改列定义
ALTER TABLE tb1_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;   把id放在第一位
ALTER TABLE users2 MODIFY  id TINYINT UNSIGNED NOT NULL FIRST;  把id的数据类型变成TINYIN                                                                                      T类型

修改列名称
ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; 把pid改成p_id

数据表更名
方法1：
    ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name
    例如：ALTER TABLE tb1 RENAME TO tb_1；
方法2：
    RENAME TABLE tb1_name TO new_tb1_name [,tb1_name2 TO new_tb1_name2]
    例如 RENAME TABLE tb_1 to tb1;