iMisty的技术栈

iMisty的技术栈

MySQL基础之表结构操作

MySql
989
2019-06-15

表结构操作

表结构操作

添加字段和删除字段

  • 添加字段语法： ALTER TABLE table_name ADD 字段名称 字段属性 +[完整性约束条件]+ [FIRST|AFTER 字段名称]

  • 删除字段语法 ALTER TABLE tb_name DROP [COLUMN] 字段名称

    注意同一张表的多个字段 操作之间用 ,分割

添加默认值和删除默认值

  • 添加默认值 ALTER TABLE tb_name ALTER 字段名称 SET DEFAULT 默认值；
  • 删除默认值 ALTER TABLE tb_name ALTER 字段名称 DROP DEFAULT；
    USE db_imist;
    CREATE TABLE IF NOT EXISTS user1(
      id INT UNSIGNED AUTO_INCREMENT KEY
    );

    -- 测试添加删除字段
    -- 添加 username字段
    ALTER TABLE user1 ADD username VARCHAR(20);

    -- 添加password字段 ，默认按顺序添加
    ALTER TABLE user1 ADD password CHAR(32) NOT NULL;
    -- 添加邮箱字段，添加到username之后 FFTER关键字
    ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;
    -- 添加 测试字段 添加到首位 FIRST关键字
    ALTER TABLE user1 ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST ;

    -- 删除test 字段
    ALTER TABLE user1 DROP  test;

    -- 批量操作 删除添加多个字段  ，选中user1表添加age addr字段，删除email字段
    ALTER TABLE user1
       ADD age1 TINYINT UNSIGNED NOT NULL DEFAULT 18,
       ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
       DROP COLUMN addr;
    DESC user1;

    -- 测试添加删除默认值

    CREATE TABLE user2(
      id INT UNSIGNED AUTO_INCREMENT KEY ,
      username VARCHAR(20) NOT NULL ,
      age TINYINT UNSIGNED NOT NULL DEFAULT  18,
      email VARCHAR(50) NOT NULL
    );

    -- 给email 字段添加默认值
    ALTER TABLE user2
        ALTER email SET DEFAULT 'iMist@qq.com';
    -- 删除age字段的默认值
    ALTER TABLE user2
         ALTER age DROP DEFAULT ;

### 修改字段类型,字段属性,字段顺序

  • ALTER TABLE tb_name MODIFY 字段名称 字段类型[字段属性][FIRST|AFTER 字段名称]

    修改字段名称,字段类型,字段属性，字段顺序

  • ALTER TABLE tb_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST|AFTER 字段名称]

-- 测试修改字段类型和字段属性

 CREATE TABLE user3(
   id INT UNSIGNED AUTO_INCREMENT KEY ,
   username VARCHAR(20) NOT NULL  UNIQUE,
   password VARCHAR(32) NOT NULL ,
   email VARCHAR(50) NOT NULL
 );

 -- 将用户名字段名类型字节长度改为20，相当于将该表中的字段重新定义 以MODIFY修饰
 ALTER TABLE user3
   MODIFY username VARCHAR(21) NOT NULL UNIQUE ;
 -- 注意：表原结构中的不需要改变的字段需要照写，不写的话会试做为修改（如：NOT NULL），但是实际操作中UNIQUE不写也不会被删除；

 -- 修改密码长度为40
 ALTER TABLE user3
     MODIFY password CHAR(40) NOT NULL ;

 -- 将email 字段改成VARCHAR(50) NOT NULL FIRST
 ALTER TABLE user3
     MODIFY email VARCHAR(50) NOT NULL FIRST;

 -- 测试CHANGE 关键字和modify 不同这个可以修改字段名称,可以在对同一张表的多条字段进行操作
 -- 将username 改为user 将password 改为pwd 其他不变
 ALTER TABLE user3
     CHANGE username user VARCHAR(20) NOT NULL UNIQUE ,
     CHANGE password pws VARCHAR(32) NOT NULL ;

 -- 将email改为userEmail 类型Varchar(100) Default 'imist@qq.com'
 ALTER TABLE user3
     CHANGE email userEmail VARCHAR(100) DEFAULT 'imist@qq.com';-- 注意这里相当于添加了默认，因为元字段中没有
 ALTER TABLE user3
     CHANGE userEmail email  VARCHAR(100) ; -- 这里改回去并且不设置默认值 ，相当于默认值删除了

 -- 特别说明这里的属性修改MODIFY 和CHANGE 修改属性对 NOTNULL 和 DEFAULT都有效但是 UNIQUEKEY修改无效添加有效（若是之前没有就相当于添加）

 DESC user3;

添加主键和删除主键

  • 添加主键 ALTER TABLE table_name ADD PRIMARY KEY (设为主键的字段); ，自动设置为NOT NULL
  • 删除主键 ALTER TABLE tb_name DROP PRIMARY KEY; ,这样就可以直接删除主键，因为一个表只有一个主键
注意 ：
  • 添加主键的时候表中不可以有主键,即使添加的主键就是该表中已有的主键，也不可以重复添加（修改同理）
  • 删除主键的时候若是主键属性包含AUTO_INCREMENT ，不可以直接删除，因为这个属性必须和主键配合使用,得先修改表结构删除AUTO_INCREMENT属性
  • CHANGE|MODIFY修改表属性的时候 ，若是表中已经有主键了,不可以带上PRIMARYKEY 原因和第一条一样
-- 测试添加主键和删除主键

 CREATE TABLE user4(
   id INT UNSIGNED ,
   username VARCHAR(20) NOT NULL
 );
 -- 添加主键
 ALTER TABLE  user4
   ADD PRIMARY KEY (id);

 -- 删除主键
 ALTER TABLE user4 DROP PRIMARY KEY ;

 DESC user4;

 CREATE TABLE user5(
   id INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY ,
   username VARCHAR(20) NOT NULL
 );
 ALTER TABLE user5 DROP PRIMARY KEY ;
 -- 执行失败 `Incorrect table definition; there can be only one auto column and it must be defined as a key`
 -- 因为AUTO_INCREMENT 和主键一起使用，若是只删除主键则报错

 -- 正确操作 先使用MODIFY或者CHANGE 去掉AUTO_INCREMENT 然后再删除主键
 ALTER TABLE user5 MODIFY id INT UNSIGNED  ;
 -- 这里修改不可以带上PRIMARY KEY 报错`Multiple primary key defined`，即使修改的就是是主键字段也不可以，但是若是没有主键就不会有多

 ALTER TABLE user5 DROP PRIMARY KEY ;
 DESC user5;

添加唯一索引和删除唯一索引

  • 添加唯一索引 ALTER table_name ADD UNIQUE KEY|INDEX index_name(字段名称);
  • 删除唯一索引 ALTER table_name DROP INDEX index_name;
-- 测试添加和删除唯一索引

   CREATE TABLE user6(
     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
     username VARCHAR(20) NOT NULL UNIQUE ,
     password CHAR(32) NOT NULL ,
     email VARCHAR(50) NOT NULL UNIQUE
   ); -- 这里不指定唯一索引的名字的话 默认使用该字段名称作为索引名

   -- 删除唯一索引
   ALTER TABLE user6 DROP INDEX  `username`;
   ALTER TABLE user6 DROP INDEX `email`;

   -- 添加唯一索引
   ALTER TABLE user6 ADD UNIQUE KEY (username); -- 不指定索引名称

   ALTER TABLE user6 ADD UNIQUE KEY uni_email(email); -- 指定索引名称

   DESC user6;
   SHOW CREATE TABLE user6 ;

<p>![](https://gitee.com/iMist/MySqlCode/raw/master/SqlDemo/readme/images2/image9.png)

修改数据表名称

  • 语法结构1,ALTER修改表结构 ALTER TABLE table_name RENAME[TO|AS] new_table_name
  • 语法结构2,RENAME关键字修改 RENAME TABLE tb_name TO new_table_name
-- 测试修改数据表表名称
   -- 将user6 改为user666
   RENAME TABLE user6 TO user666;-- 这里RENAME TO 关键字修改不可以改TO为AS
   DESC user666;

   ALTER TABLE user666 RENAME TO user6;
   ALTER TABLE user666 RENAME AS user6;
   DESC user6;

修改表的字符编码和排序方式

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Shell编程之shell运算符
MySQL基础之特殊形式的查询