mysql之DDL(CREATE ALTER DROP)

658uhr

DDL：数据定义语言
    CREATE
    ALTER
    DROP


主要操作集中在数据库和表。

数据库：
    创建数据库：
        CREATE DATEBASE|SCHEMA [IF NOT EXISTS] DBNANE [CREATE_SPECIFICATION];

            CREATE_SEPECIFICATION   
                [DEFAULT]  CHARACTER SET [=] CHARSET_NAME 指定默认字符集
                COLLATE [=] CLOOATION_NAME] 指定排序规则

    修改数据库：
        ALTER DATABASE|SCHEMA DBNAME ALTER_SPECIFICATION;

            ALTER_PSECIFICATION:
                CHARACTER SET [=] CHARSET_NAME 默认字符集
                COLLATE [=] CLOOATION_NAME 排序规则


        ALTER DATABASE|SCHEMA DBNAME UPGRADE DATA DIRECTORY NAME; 升级数据字典名称。例如5.0数据库迁移到5.1的服务器。在5.7.6以后可能会被废除。

    删除数据库：
        DROP DATEBASE|SCHEMA  [IF EXISTS] DBNAME;

    设定默认数据库：
        USE DBNAME;


    实例：
        1.查看数据库支持的字符集和排序规则：
            mysql>SHOW CHARSET;


        2.创建一个名为test的数据库，默认字符集为utf8，默认排序规则为uft8_general_ci
            mysql>CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET=utf8  DEFAULT COLLATE = uft8_general_ci
            mysql>SHOW DATABASES;


        3.查看当前数据库使用的字符集：
            mysql>USE test;
            mysql>STATUS;
            或
            mysql>SHOW VARIABLES LIKE '%char%';

        4.修改test2的字符集为big5，排序规则为big5_chinese_ci
            mysql>ALTER DATABASE IF NOT EXISTS test CHARACTER SET=big5 COLLATE = big_chinese_ci

        5.删除test数据库：
            mysql>DROP DATABASE test;

        6.升级mysql服务器后升级test库的数据字典
            mysql>ALTER DATABASE test UPGRADE DATA DIRECTOYR NAME;


表：
    创建表：
        CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME(CREATE_DEFINITION,...) [TABLE_OPTION] [PARTITION_OPTIONS];
            直接定义空表。
        
        CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME(CREATE_DEFINITION,...) SELECT_STATEMENT;
            从其他表中查询数据，并以此创建新表。创建完毕后表中已有数据。
        
        CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME LIKE OLD_TBNAME;
            仿照OLD_DBNAME表为模板,创建按空表。

            CREATE_DEFINITION
                COLNAME COLUMN_DEFINITION
                PRIMARY KEY (COLNAME,...) 主键
                UNIQUE INDEX|KEY(COLNAME,....) 唯一键
                INDEX (COLNAME,...) 索引

            COLNAME 字段名称
            COLUMN_DEFINITION 字段定义
                DATA_TYPE [NULL|NOT NULL] [DEFAULT VALUE] 指定数据类型
                [AUTO_INCREMENT] [UNQIUE[KEY]|PRIMARY KEY]  自增长的主键或唯一键
                COMMENT 'STRING' 描述信息
                COLUMN_FORMAT{FIXED|DYNAMIC|DEFAULT}
                STORAGE {DISK|MEMORY|DEFAULT}
                REFERENC_DEFINITION 引用相关

            TABLE_OPTION
                ENGINE [=] ENGNAME 存储引擎
                AUTO_INCREMENT [=] VALUE
                AVG_ROW_LENGTH [=] VALUE 平均行的长度
                [DEFAULT] CHARACTER SET [=] CHARSETNANE 字符集
                CHECKSUM [=] {0|1} 是否启用校验和
                [DEFAULT] COLLATE [=] COLLNAME 排序规则
                COMMENT [=] 'STRING' 注释信息
                DATA DIRECTORY [=] '/PATH/DIR' 数据目录，绝对路径
                DELAY_KEY_WRITE [=] {0|1} 是否启用延迟键写入
                INDEX_DIRECTORY [=] '/PATH/DIR' 索引目录，绝对路径
                INSERT_METHOD [=] {NO|FIRST|LAST} 插入方法
                KEY_BLOCK_SIZE [=] VALUE 指定键块的大小
                MAX_ROWS [=] VALUE 最多允许储存的行数
                MIN_ROWS [=] VALUE 最少允许储存的行数
                PACK_KEYS [=] {0|1|DEFAULT}
                PASSWORD [=] 'STRING'
                ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 行格式
                TABLESPACE TABSPACENAME [STORAGE {DISK|MEMORY|DEFAULT}]
                UNION [=] (TBNAME,...) 表空间

            REFERENCE_DEFINITION 引用描述
                REFERENCES TBNAME (INDEX_COLNAME,...) 指定要引用的表名
                    [MATCH FULL|PARTIAL|SIMPLE] 指定匹配范围
                    [ON DELETE REFERENCE_OPTION] 指定引用的表删除后的操作
                    [ON UPDATE REFERENCE_OPTION] 指定引用的表更新后的操作

            REFERENCE_OPTION:
                RESTRICT  严格。不允许。默认
                CASCADE 级联，跟随引用表改动
                SET NULL 设定为空
                NO ACTION 无动作

    修改表：
        ALTER TABLE TBNAME [ALTER_SPECIFICATION,...] [PARTITION_OPTIONS]

        ALTER_SPECIFICATION
            ADD [COLUMN] COLNAME COLUMN_DEFINITION [FIRST|ATFER COLNAME] 添加字段

            ADD [COLUMN] (COLNAME COLUMN_DEFINITION,...) 添加多个字段

            ADD [INDEX|KEY] [INDEX_NAME] [INDEX_TYPE] (INDEX_COLNAME,...) [INDEX_OPTION] 添加INDEX

            ADD PRIMARY KEY (COLNAME COLUMN_DEFINITION,...) 添加主键

            ADD UNIQUE KEY (COLNAME COLUMN_DEFINITION,...) 添加唯一键

            ADD FOREIGN KEY (INDEX_COLNAME,...) REFERENCE_DEFINITION 添加外键

            ADD FULLTEXT (COLNAME COLUMN_DEFINITION,...) 添加全文索引

            ADD SPATIAL (COLNAME COLUMN_DEFINITION,...) 添加空间索引

            CHANGE [COLUMN] OLD_COLNAME NEW_COLNAME CLOLUMN_DEFINITION [FIRST|AFTER COLANME] 可以修改字段名，位置和字段定义

            MODIFY [COLUMN] COLNAME COLUMN_DEFINITION [FIRST|AFTER CLONAME] 简单修改，修改位置或者是字段定义

            RENAME  [TO|AS] NEW_TABNAME 表重命名。

            COVNERT TO CHARACTER SET [=] CHARSET_NAME [COLLATE COLLATION_NAME] 更改字符集

    重命名表：
        RENAME TABLE OLD_TBNAME TO NEW_TBNAME

    删除表：
        DROP TABLE TBNAME [RESTRICT|CASCADE]

        RESTRICT 严格。
        CASCADE 级联。删除表的时候，若表被其他表引用，则会连被引用的表一起删除。


    查看表属性状态：
        SHOW TABLE STATUS LIKE ‘TBNAME’[\G]

    查看某个库中的所有表：
        SHOW TABLES [FROM DBNAME]

    查看表的结构：
        DESC TBNAME


实例：
    1.直接创建表：
        mysql>USE test
        mysql>CREATE TABLE student (id INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,gender enum(''M,'F'));

        mysql>CREATE TABLE student2  (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,PRIMARY KEY(id)，UNIQUE KEY(name),INDEX(age));

        mysql>CREATE TABLE courses (CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,CourName VARCHAR(50) NOT NULL,);

    2.查看表中各自段数据类型：
        mysql>DESC students;

    3.删除表：
        mysql>DROP TABLE student2;

    4.列出当前数据库中所有的表：
        mysql>SHOW TABLES;

    5.重新创建student2表，指定存储引擎为MyISAM：
        mysql>CREATE TABLE student2  (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,PRIMARY KEY(id)，UNIQUE KEY(name),INDEX(age)) ENGINE = MyISAM;

    6.查看student2表的各个属性状态：
        mysql>SHOW TABLE STATUS LIKE 'student2'\G;

    7根据现有表中的内容创建新表：
        注意：通过这种方式创建的表，虽然字段和内容一样，但是新表和旧表相比，会丢失部分的表格式和表属性。

        mysql>CREATE TABLE  test2 SELECT * FROM student2;
        mysql>SHOW TABLES;
        mysql>SELECT * FROM student2;
        mysql>SELECT * FROM test2;
        mysql>DESC courses;
        mysql>DESC testcourses;
        mysql>SHOW TABLE STATUS LIKE 'student2';
        mysql>SHOW TABLE STATUS LIKE 'test2';


    10.以其某张表为模板创建空表
        注意：使用此方式创建的表和原表格式是完全一样的。

        mysql>CREATE TABLE test3 LIKE student2;
        mysql>DESC test3;
        mysql>SHOW TABLES STATUS LIKE 'test3';

    11.修改字段的名称和属性：
        mysql>ALTER TABLE course CHANGE CourName CouName VARCHAR(50) NOT NULL;

    13.新增字段：
        mysql>ALTER TABLE coures ADD StartTime date DEFAULT '2015-07-05';

    14.修改表名：
        mysql>ALTER TABLE testcourses RENAME TO testcourse1;
        或
        mysql>RENAME TABLE testcourses TO testcourses1;

        mysql>SHOW TABLES;

    16.更改表的存储引擎：
        注意：重命名的代价非常大。实质上是创建一个和旧表完全一样的新的空表，导入数据，删除旧表。
        mysql>ALTER TABLE courses ENGINE = InnoDB;