文章: 阅读:

SQL DDL 数据定义语句

DatabaseSQL

前言

  • DDL（Data Definition Language）语句：数据定义语句，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括 create、drop、alter 等。

1、DDL 数据库操作语句

  • 1）创建数据库语句

    1
    2
    3
    4
    5
    # 创建数据库，数据使用默认编码方式 utf8mb4
    # create database 数据库名;
    > create database test;

        Query OK, 1 row affected (0.04 sec)
    1
    2
    3
    4
    5
    # 创建数据库，指定数据编码方式
    # create database 数据库名 character set 字符集;
    > create database test character set gbk;

        Query OK, 1 row affected (0.02 sec)

  • 2）删除数据库语句

    1
    2
    3
    4
    5
    # 删除数据库
    # drop database 数据库名;
    > drop database test;

        Query OK, 0 rows affected (0.05 sec)
    1
    2
    3
    4
    5
    # 删除数据库，如果数据库存在就删除
    # drop database if exists 数据库名;
    > drop database if exists test;

        Query OK, 0 rows affected (0.01 sec)

  • 3）显示所有数据库语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 显示所有数据库
    > show databases;
        
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | performance_schema |
        | mysql              |
        | sys                |
        +--------------------+
        4 rows in set (0.01 sec)

    • 系统自动创建的数据库

      • 上面的显示结果中的 4 个数据库都是安装 MySQL 时系统（macOS）自动创建的。
      • information_schema：主要存储了系统中的一些数据库对象信息，不如用户表信息、列信息、权限信息、字符集信息、分区信息等。
      • performance_schema：主要用于收集数据库服务器性能参数。
      • mysql：包含权限配置，事件，存储引擎状态，主从信息，日志，时区信息，用户权限配置等。
      • sys：performance_schema 的替代方案。

  • 4）显示数据库的创建信息语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 显示数据库具体信息
    # show create database 数据库名;
    > show create database test;
        
        +----------+---------------------------------------------------------------------------------------------+
        | Database | Create Database                                                                             |
        +----------+---------------------------------------------------------------------------------------------+
        | test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
        +----------+---------------------------------------------------------------------------------------------+
        1 row in set (0.00 sec)

  • 5）选择操作的数据库语句

    1
    2
    3
    4
    5
    # 选择操作的数据库
    # use 数据库名;
    > use test;

        Database changed

  • 6）显示正在操作的数据库语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 显示正在操作的数据库
    > select database();

        +------------+
        | database() |
        +------------+
        | test       |
        +------------+
        1 row in set (0.00 sec)

  • 7）导出数据库语句

    1
    2
    3
    4
    5
    # 导出数据库，同时倒出数据库表结构和数据
    # mysqldump -u 用户名 -p 数据库名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p test > /Users/qianchia/Desktop/test.sql;
        
        Enter password:
    1
    2
    3
    4
    5
    # 导出数据库，只倒出数据库表结构
    # mysqldump -u 用户名 -p -d 数据库名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p -d test > /Users/qianchia/Desktop/test.sql;
        
        Enter password:
    1
    2
    3
    # 导出数据库
    # mysqldump -u 用户名 -p --quick --no-create-info --extended-insert --default-character-set=字符集 源数据库名> 目标文路径.sql
    $ mysqldump -u root -p --quick --no-create-info --extended-insert --default-character-set=latin1 test> /Users/qianchia/Desktop/test.sql
    • --quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中。
    • --extended-insert：使用包括几个 values 列表的多行 insert 语法。这样使转储文件更小，重载文件时可以加速插入。
    • --no-create-info：不导出每个转储表的 create table 语句。

    • --default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。

    • 导出数据库是在系统命令模式下操作。

  • 8）导入数据库语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 导入数据库/表
    # source 导入的目标文件路径.sql;
    > source /Users/qianchia/Desktop/test.sql;
        
        Query OK, 0 rows affected (0.00 sec)
        Query OK, 0 rows affected (0.01 sec)
        ...
        Query OK, 3 rows affected (0.00 sec)
        Records: 3  Duplicates: 0  Warnings: 0
    1
    2
    3
    # 导入数据库/表
    # mysql -u 用户名 -p 数据库名 < 导入的目标文件路径.sql
    $ mysql -u root -p test < /Users/qianchia/Desktop/testdbcreatetab.sql
* 导入数据库是在选择了操作数据库后在 mysql 命令模式下操作。

2、DDL 表操作语句

  • 1）创建表语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 创建表
    # create table 表名 (
          列名称字段 数据类型(长度) 约束条件,
          列名称字段 数据类型(长度) 约束条件
      ) [default charset = 字符集名];
    > create table student (
          no int,
          name varchar(20),
          age int,
          score int
      );

        Query OK, 0 rows affected (0.08 sec)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    # 创建表，约束
        
    > create table student (
          no int unique,					       // 设置唯一约束
          name varchar(20) not null,		       // 设置非空约束
          age int,
          score int
      );

    > create table student (
          no int primary key,				       // 设置主键约束
          name varchar(20),
          age int,
          score int
      );

    > create table student (
          no int,
          name varchar(20),
          age int,
          score int
      
          primary key(no)					       // 设置主键约束
      );

    > create table student (
          no int primary key auto_increment,       // 设置主键自增约束
          name varchar(20),
          age int,
          score int
      );

    > create table A (
          ano int primary key,
          aname varchar(20),
          loc varchar(100)
      );

    > create table B (
          bno int primary key,
          bname varchar(20),
        
          bano int,
          # constraint 约束名称 foreign key(外键名称) references 源表(主键名称)
          constraint fk_a_b foreign key(bano) references A(ano)                // 设置外键约束
      );

  • 2）删除表语句

    1
    2
    3
    4
    5
    # 删除表
    # drop table 表名;
    > drop table student;

        Query OK, 0 rows affected (0.07 sec)
    1
    2
    3
    4
    5
    # 删除表，如果表存在就删除
    # drop table if exists 表名;
    > drop table if exists student;

        Query OK, 0 rows affected (0.06 sec)

  • 3）显示所有表语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # 显示所有表
    > show tables;

        +----------------+
        | Tables_in_test |
        +----------------+
        | A              |
        | B              |
        | student        |
        +----------------+
        3 rows in set (0.00 sec)

  • 4）显示表的创建信息语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # 显示表具体信息
    > show create table student;
        
        +---------+--------------------------------------------------------------------------------------------------+
        | Table   | Create Table                                                                                     |
        +---------+--------------------------------------------------------------------------------------------------+
        | student | CREATE TABLE `student` (
                                            `no` int(11) NOT NULL AUTO_INCREMENT,
                                            `name` varchar(20) NOT NULL,
                                            `age` int(11) DEFAULT NULL,
                                            `score` int(11) DEFAULT NULL,
                                            PRIMARY KEY (`no`)
                                           ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci        |
        +---------+--------------------------------------------------------------------------------------------------+
        1 row in set (0.01 sec)

  • 5）显示表的数据结构语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 显示表的数据结构
    # desc 表名称;
    > desc student;
        
        +-------+-------------+------+-----+---------+----------------+
        | Field | Type        | Null | Key | Default | Extra          |
        +-------+-------------+------+-----+---------+----------------+
        | no    | int(11)     | NO   | PRI | NULL    | auto_increment |
        | name  | varchar(20) | NO   |     | NULL    |                |
        | age   | int(11)     | YES  |     | NULL    |                |
        | score | int(11)     | YES  |     | NULL    |                |
        +-------+-------------+------+-----+---------+----------------+
        4 rows in set (0.01 sec)

  • 6）重命名表语句

    1
    2
    3
    4
    5
    # 重命名表
    # alter table 旧表名称 rename [to] 新表名称;
    > alter table student rename [to] stu;
        
        Query OK, 0 rows affected (0.02 sec)
    1
    2
    3
    4
    5
    # 重命名表
    # rename table 旧表名称 to 新表名称;
    > rename table student to stu;

        Query OK, 0 rows affected (0.08 sec)

  • 7）修改表的字符集语句

    1
    2
    3
    4
    5
    6
    # 修改表的字符集
    # alter table 表名称 cahracter set 字符集;
    > alter table student character set gbk;
        
        Query OK, 0 rows affected (0.05 sec)
        Records: 0  Duplicates: 0  Warnings: 0

  • 8）导出表语句

    1
    2
    3
    4
    5
    # 导出数据库指定的表，同时导出表结构和数据
    # mysqldump -u 用户名 -p 数据库名 表名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p test student > /Users/haiqianj/Desktop/test.sql;
        
        Enter password:
    1
    2
    3
    4
    5
    # 导出数据库指定的表，只导出表结构
    # mysqldump -u 用户名 -p -d 数据库名 表名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p -d test student > /Users/haiqianj/Desktop/test.sql;
        
        Enter password:
* 导出表是在系统命令模式下操作。

  • 9）导入表语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 导入表/数据库
    # source 导入的目标文件路径.sql;
    > source /Users/qianchia/Desktop/test.sql;
        
        Query OK, 0 rows affected (0.00 sec)
        Query OK, 0 rows affected (0.01 sec)
        ...
        Query OK, 3 rows affected (0.00 sec)
        Records: 3  Duplicates: 0  Warnings: 0
    • 导入表是在选择了操作数据库后在 mysql 命令模式下操作。

3、DDL 列字段操作语句

  • 1）添加列语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 添加列，添加单列
    # alter table 
        表名称 
      add [column] 
        列名称 类型 [约束] [first | after 列名称];
    > alter table student add cla varchar(20);

        Query OK, 0 rows affected (0.04 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 添加列，添加多列
    # alter table 
        表名称 
      add [column] 
        列名称 类型 [约束] [first | after 列名称],
      add [column]  
         列名称 类型 [约束] [first | after 列名称], 
       ...;
    > alter table student add cla varchar(20), add addr varchar(100);

        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0

  • 2）删除列语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 删除列，删除单列
    # alter table 
        表名称 
      drop [column] 
        列名称;
    > alter table student drop address;

        Query OK, 0 rows affected (0.10 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 删除列，删除多列
    # alter table 
        表名称 
      drop [column] 
        列名称, 
      drop [column] 
        列名称, 
      ...;
    > alter table student drop class, drop address;

        Query OK, 0 rows affected (0.11 sec)
        Records: 0  Duplicates: 0  Warnings: 0

  • 3）修改列名称语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # 修改列名称，修改单列名称
    # alter table 
        表名称 
      change [column] 
        旧列名称 
        新列名称 类型 [约束] [first | after 列名称];
    > alter table student change cla class int;

        Query OK, 0 rows affected (0.10 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # 修改列名称，修改多列名称
    # alter table 
        表名称 
      change [column] 
        旧列名称 
        新列名称 类型 [约束] [first | after 列名称],
      change [column]
        旧列名称 
        新列名称 类型 [约束] [first | after 列名称],
      ...;
    > alter table student change cla class int, change addr address varchar(100);

        Query OK, 0 rows affected (0.10 sec)
        Records: 0  Duplicates: 0  Warnings: 0

  • 4）修改列属性语句

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 修改列属性，修改单列的字段类型及约束
    # alter table 
        表名称 
      modify [column] 
        列名称 类型 [约束] [first | after 列名称];
    > alter table student modify cla int;
       
        Query OK, 0 rows affected (0.11 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    # 修改列属性，修改多列的字段类型及约束
    # alter table 
        表名称 
      modify [column] 
        列名称 类型 [约束] [first | after 列名称], 
      modify [column] 
        列名称 类型 [约束] [first | after 列名称], 
      ...;
    > alter table student modify cla int, modify addr varchar(200);

        Query OK, 0 rows affected (0.10 sec)
        Records: 0  Duplicates: 0  Warnings: 0

  • 5）修改列顺序语句

    • 在上边的 add、change、modify 语句中的可选参数 [first | after 列名称] 可以用老修改字段在表中的位置。
    • first：放在所有列最前边

    • after 列名称：放在指定列之后

    • 注意：change、first、after column 这些关键字都属于 MySQL 在标准 SQL 上的扩展，在其它数据库中不一定适用。

如果您觉得阅读本文对您有帮助，请用微信、支付宝 or QQ 扫码赞助，您的支持是我写作最大的动力！欢迎各位转载，但是未经作者本人同意，转载文章之后必须在文章页面明显位置给出作者和原文链接，否则保留追究法律责任的权利。

Qian Chia

Posted @ 2018-04-10 06:02


评论


文章目录
  1. 1. 前言
  2. 2. 1、DDL 数据库操作语句
  3. 3. 2、DDL 表操作语句
  4. 4. 3、DDL 列字段操作语句
隐藏目录