MySQL 教程 - 05 - 数据表的操作

数据表的创建与查看

    -- 创建表
    CREATE TABLE 表名(
        字段1 列类型 约束,
        列名2 列类型 约束,
        PRIMARY KEY (主键列)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;        -- 使用 InnoDB 存储引擎,默认字符集 utf8

    CREATE TABLE emp(
        id INT UNSIGNED AUTO_INCREMENT,    -- int型,无符号型,即正数,自动增长
        username VARCHAR(50) NOT NULL,     -- 变长字符串,长度50(考虑到少数民族),非空
        age TINYINT UNSIGNED NOT NULL,     -- TINYINT的范围是0-255,可以节省空间. 非空
        salary FLOAT(8,2) UNSIGNED,        -- FLOAT(8,2) 小数点后2位,小数点前6位.
        register_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 默认当前时间.
        PRIMARY KEY (id)                   -- PRIMARY KEY () 用来指定主键. 
    );

  -- 切换到指定数据库
    USE 数据库名;

    -- 显示正在使用(打开)的数据库
    SELECT DATABASE();

    -- 查看当前数据库的所有表.  可以使用LIKE或者WHERE关键字.
    SHOW TABLES;

    -- 查看其它数据库中的所有表.   可以使用LIKE或者WHERE关键字.
    SHOW TABLES FROM 数据库名;   

    -- 查看数据表的结构
    SHOW COLUMNS FROM 表名;
    -- 或者
    DESC 表名;

-- 创建表的时候同时添加注释
    create table 表名(
        字段名 类型  约束  COMMENT "字段的注释";
        字段名 类型  约束  COMMENT "字段的注释";
        字段名 类型  约束  COMMENT "字段的注释"
    )COMMENT '表的注释';

    CREATE TABLE address(
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT "地址id",
        country VARCHAR(10) NOT NULL COMMENT "国家",
        province VARCHAR(10) NOT NULL COMMENT "省份"
    ) COMMENT "地址表";

-- 查看表注释的方法:
    --在生成的SQL语句中看        
    show create table 表名;

    --在元数据的表里面看
    use information_schema;
    select * from TABLES where TABLE_SCHEMA='my_db' and TABLE_NAME='表名' \G

    -- 查看字段注释的方法
    --show        
    show full columns from 表名;
    --在元数据的表里面看    
    use information_schema;
    select * from COLUMNS where TABLE_SCHEMA='my_db' and TABLE_NAME='表名' \G

    -- 修改表的注释
    alter table 表名 COMMENT '修改后的表的注释';

    -- 修改字段的注释
    alter table 表名 modify column 字段名 类型 约束 COMMENT '修改后的字段注释';  -- 字段名和字段类型照写就行

通过其他表创建表 / 数据表的拷贝

  -- 拷贝表结构到新表 newadmin 中, 不会拷贝表中的数据. (结构)
    CREATE TABLE newadmin LIKE admin;

    -- 拷贝数据到新表中. 其实只是把 select 语句的结果建一个表, 所以新表中不会有主键,索引. (数据)
    CREATE TABLE newadmin AS ( SELECT * FROM admin ); 

    -- 真正意义上的复制一个表. (数据+结构)
    CREATE TABLE newadmin LIKE admin;   
    INSERT INTO newadmin ( SELECT * FROM admin );

    -- 可以操作不同的数据库中的表.
    CREATE TABLE newadmin LIKE shop.admin;
    CREATE TABLE newshop.newadmin LIKE shop.admin;  

    -- 也可以拷贝一个表中的部分字段.
    CREATE TABLE newadmin AS ( SELECT username, password FROM admin );

    -- 也可以将新建的表的字段改名.
    CREATE TABLE newadmin AS ( SELECT id, username AS uname, password AS pass FROM admin );

    -- 也可以拷贝一部分数据.
    CREATE TABLE newadmin AS ( SELECT * FROM admin WHERE LEFT(username,1) = 's' );

    -- 也可以在创建表的同时定义表中的字段信息.
    CREATE TABLE newadmin (
        id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
   ) AS ( SELECT * FROM admin );

数据表的修改

针对字段: 添加/删除字段, 修改列定义, 修改列名
针对约束: 添加/删除约束
针对数据表: 数据表更名

    -- 添加单列. 默认添加在最后.
     ALTER TABLE 表名 ADD (COLUMN) 列名 列类型定义;

    -- 向user表中插入一列age.
    ALTER TABLE user ADD age TINYINT UNSIGNED NOT NULL DEFAULT 20;

    -- 向user表中插入一列id,并且该列位于所有列之前.
    ALTER TABLE user ADD id INT(4) PRIMARY KEY AUTO_INCREMENT FIRST;

    -- 向user表中插入一列password 并且该列位于列username之后.
    ALTER TABLE user ADD password VARCHAR(32) NOT NULL AFTER username;

    -- 添加多列
    ALTER TABLE表名 ADD (COLUMN) 列名 列类型定义, (列名 列类型定义, ....)

    -- 删除列
    ALTER TABLE 表名 DROP (COLUMN) 列名;

    -- 删除users1表中的age列和password列.
    ALTER TABLE user DROP age, DROP password;

    -- 修改列定义
    ALTER TABLE 表名 MODIFY 列名 列定义  [ FIRET | AFTER 列名];

    -- 修改列 id 的类型为 INT 型 并且位置位于第一列.
    ALTER TABLE user MODIFY id INT UNSIGNED NOT NULL FIRST;

    -- 修改列名
    ALTER TABLE 表名 CHANGE 旧列名 新列名 列定义 [ FIRET | AFTER 列名];

    -- 修改列 id 列名为 pid
    ALTER TABLE user CHANGE id pid INT UNSIGNED NOT NULL FIRST;

    -- 添加主键约束
    ALTER TABLE 表名 ADD [CONSTRAINT 约束名] PRIMARY KEY(列名) ;

    -- 给 id 列添加主键约束.
    ALTER TABLE user ADD CONSTRAINT PK_user_id PRIMARY KEY(id);

    -- 添加唯一约束
    ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE(列名);

    -- 给 username 列添加唯一约束.
    ALTER TABLE user ADD CONSTRAINT UN_username UNIQUE(username);

    -- 添加外键约束
    ALTER TABLE 子表 ADD CONSTRAINT 约束名 FOREIGN KEY(列名)
    REFERENCES 父表(列名)

    -- 添加外键约束给列pid 参照provinces表中的id列.
    ALTER TABLE user ADD CONSTRAINT FK_pid_id FOREIGN KEY (pid)
    REFERENCES provinces(id);

    -- 添加默认约束
    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

    -- 给列age添加默认约束20.
    ALTER TABLE user ALTER age SET DEFAULT 20;

    -- 查看约束
    SHOW CREATE TABLE 表名;

    -- 删除主键约束
    ALTER TABLE表名 DROP PRIMARY KEY;

    -- 删除唯一约束
    ALTER TABLE 表名 DROP [INDEX | KEY] 唯一约束名;
    ALTER TABLE 表名 DROP INDEX 有唯一约束的列名;        -- 删除的是约束, 而不是字段.

    -- 查看users2表的约束
    SHOW INDEXES FROM 表名\G;

    -- 删除username列上的唯一约束.
    ALTER TABLE user DROP INDEX username;

    -- 删除外键约束
    ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

    -- 删除默认约束
    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

    -- 删除指定名称的约束
    ALTER TABLE 表名 DROP CONSTRAINT 约束名;

    -- 删除主键约束, 唯一性约束的同时保留唯一性索引. 默认是会删除唯一性索引的.
    ALTER TABLE 表名 DROP CONSTRAINT 约束名 KEEP INDEX;

    -- 更名数据表
    ALTER TABLE 表名 RENAME [TO | AS] 新表名;

    -- 或者
    RENAME TABLE 表名 TO 新表名;

    -- 更名多个数据表
    REANME TABLE 表名A TO 新表名A, 表名B TO 新表名B,....;

    -- 删除数据表
    DROP TABLE 表名;