SQL DDL 数据定义语句

前言

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

1、DDL 数据库操作语句

  • 1)创建数据库语句

    # 创建数据库,数据使用默认编码方式 utf8mb4
    # create database 数据库名;
    > create database test;
    
        Query OK, 1 row affected (0.04 sec)
    
    # 创建数据库,指定数据编码方式
    # create database 数据库名 character set 字符集;
    > create database test character set gbk;
    
        Query OK, 1 row affected (0.02 sec)
  • 2)删除数据库语句

    # 删除数据库
    # drop database 数据库名;
    > drop database test;
    
        Query OK, 0 rows affected (0.05 sec)
    # 删除数据库,如果数据库存在就删除
    # drop database if exists 数据库名;
    > drop database if exists test;
    
        Query OK, 0 rows affected (0.01 sec)
  • 3)显示所有数据库语句

    # 显示所有数据库
    > 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)显示数据库的创建信息语句

    # 显示数据库具体信息
    # 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)选择操作的数据库语句

    # 选择操作的数据库
    # use 数据库名;
    > use test;
    
        Database changed
  • 6)显示正在操作的数据库语句

    # 显示正在操作的数据库
    > select database();
    
        +------------+
        | database() |
        +------------+
        | test       |
        +------------+
        1 row in set (0.00 sec)
  • 7)导出数据库语句

    # 导出数据库,同时倒出数据库表结构和数据
    # mysqldump -u 用户名 -p 数据库名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p test > /Users/qianchia/Desktop/test.sql;
    
        Enter password: 
    # 导出数据库,只倒出数据库表结构
    # mysqldump -u 用户名 -p -d 数据库名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p -d test > /Users/qianchia/Desktop/test.sql;
    
        Enter password: 
    # 导出数据库
    # 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)导入数据库语句

    # 导入数据库/表
    # 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 -u 用户名 -p 数据库名 < 导入的目标文件路径.sql
    $ mysql -u root -p test < /Users/qianchia/Desktop/testdbcreatetab.sql
    • 导入数据库是在选择了操作数据库后在 mysql 命令模式下操作。

2、DDL 表操作语句

  • 1)创建表语句

    # 创建表
    # create table 表名 (
          列名称字段 数据类型(长度) 约束条件,
          列名称字段 数据类型(长度) 约束条件
      ) [default charset = 字符集名];
    > create table student (
          no int,
          name varchar(20),
          age int,
          score int
      );
    
        Query OK, 0 rows affected (0.08 sec)
    # 创建表,约束
    
    > 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)删除表语句

    # 删除表
    # drop table 表名;
    > drop table student;
    
        Query OK, 0 rows affected (0.07 sec)
    # 删除表,如果表存在就删除
    # drop table if exists 表名;
    > drop table if exists student;
    
        Query OK, 0 rows affected (0.06 sec)
  • 3)显示所有表语句

    # 显示所有表
    > show tables;
    
        +----------------+
        | Tables_in_test |
        +----------------+
        | A              |
        | B              |
        | student        |
        +----------------+
        3 rows in set (0.00 sec)
  • 4)显示表的创建信息语句

    # 显示表具体信息
    > 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)显示表的数据结构语句

    # 显示表的数据结构
    # 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)重命名表语句

    # 重命名表
    # alter table 旧表名称 rename [to] 新表名称;
    > alter table student rename [to] stu;
    
        Query OK, 0 rows affected (0.02 sec)
    # 重命名表
    # rename table 旧表名称 to 新表名称;
    > rename table student to stu;
    
        Query OK, 0 rows affected (0.08 sec)
  • 7)修改表的字符集语句

    # 修改表的字符集
    # 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)导出表语句

    # 导出数据库指定的表,同时导出表结构和数据
    # mysqldump -u 用户名 -p 数据库名 表名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p test student > /Users/haiqianj/Desktop/test.sql;
    
        Enter password: 
    # 导出数据库指定的表,只导出表结构
    # mysqldump -u 用户名 -p -d 数据库名 表名 > 导出到的目标文件路径.sql;
    $ mysqldump -u root -p -d test student > /Users/haiqianj/Desktop/test.sql;
    
        Enter password: 
    • 导出表是在系统命令模式下操作。
  • 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)添加列语句

    # 添加列,添加单列
    # 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
    # 添加列,添加多列
    # 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)删除列语句

    # 删除列,删除单列
    # alter table 
        表名称 
      drop [column] 
        列名称;
    > alter table student drop address;
    
        Query OK, 0 rows affected (0.10 sec)
        Records: 0  Duplicates: 0  Warnings: 0
    # 删除列,删除多列
    # 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)修改列名称语句

    # 修改列名称,修改单列名称
    # 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
    # 修改列名称,修改多列名称
    # 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)修改列属性语句

    # 修改列属性,修改单列的字段类型及约束
    # 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
    # 修改列属性,修改多列的字段类型及约束
    # 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 上的扩展,在其它数据库中不一定适用。

posted @ 2018-06-14 21:22  QianChia  阅读(...)  评论(... 编辑 收藏