SQL DDL Data definition statement

Objective

• DDL (data definition Language) Statements: Data definition statements that define different data segments, databases, tables, columns, indexes, and other database objects. The commonly used statement keywords include create, drop, alter and so on.

1. DDL database Operation statement

• 1) Create a database statement

  # 创建数据库，数据使用默认编码方式 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) Delete Database statements

  # 删除数据库# 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 All database statements

  # 显示所有数据库> show databases;    +--------------------+    | Database           |    +--------------------+    | information_schema |    | performance_schema |    | mysql              |    | sys                |    +--------------------+    4 rows in set (0.01 sec)

  • Database created automatically by the system

    • The 4 databases in the above display results are automatically created by the system (MacOS) when you install MySQL.
    • INFORMATION_SCHEMA: It mainly stores some database object information in the system, such as user table information, column information, permission information, character set information, partition information and so on.
    • Performance_schema: Primarily used to collect database server performance parameters.
    • MySQL: Includes permissions configuration, events, storage engine status, master-slave information, log, time zone information, user rights configuration, etc.
    • Alternative to Sys:performance_schema.

• 4) Display the database creation information statement

  # 显示数据库具体信息# 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) Select the database statement for the operation

  # 选择操作的数据库# use 数据库名;> use test;    Database changed

• 6) Display the database statement in action

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

• 7) Export Database statements

  # 导出数据库，同时倒出数据库表结构和数据# mysqldump -u 用户名 -p 数据库名 > 导出到的目标文件路径.sql;$ mysqldump -u root -p test > /Users/qianchia/Desktop/test.sql;    

  # 导出数据库，只倒出数据库表结构# mysqldump -u 用户名 -p -d 数据库名 > 导出到的目标文件路径.sql;$ mysqldump -u root -p -d test > /Users/qianchia/Desktop/test.sql;    

  • The export database is operated in System command mode.

• 8) Import Database statements

  # 导入数据库/表# 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

  • The import database is operated in MySQL command mode after the operation database has been selected.

2. DDL Table Operation statement

• 1) Create a table statement

  # 创建表# create table 表名 (      列名称字段 数据类型(长度) 约束条件,      列名称字段 数据类型(长度) 约束条件  );> create table student (      no int,      name varchar(20),      age int,      score int  );    Query OK, 0 rows affected (0.08 sec)

    # Creating tables, Constraints > CREATE TABLE student (no int unique,//SET UNIQUE constraint name varchar (2                      0) NOT NULL,//SET non-null constraint age int, score int);> CREATE TABLE student (no int primary key, Set PRIMARY KEY constraint name varchar ((), age int, score int);> CREATE TABLE student (no int, name varchar (), age int, score int primary KEY (NO)//Set PRIMARY KEY constraint);> C      reate table Student (no int primary key auto_increment,//Set Primary key self-increment constraint name varchar (), age int, Score int);> CREATE table A (ano int primary key, Aname varchar (), loc varchar);> Create Table B (bno int primary KEY, bname varchar (), Bano int, # CONSTRAINT constraint name foreign key (foreign key name) Refere NCEs source table (primary key Name) constraint Fk_a_b foreign key (Bano) references a (ANO)//Set foreign KEY constraint);  

• 2) Delete Table statement

  # 删除表# 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 all table statements

  # 显示所有表> show tables;    +----------------+    | Tables_in_test |    +----------------+    | A              |    | B              |    | student        |    +----------------+    3 rows in set (0.00 sec)

• 4) Display the creation information Statement of the table

  # Show Table details > show create TABLE student;  +---------+--------------------------------------------------------------------------------------------------+    | Table |    Create Table |  +---------+--------------------------------------------------------------------------------------------------+    | Student |                                        CREATE TABLE ' student ' (' No ' int (one) not NULL auto_increment,                                        ' Name ' varchar is not NULL, ' age ' int (one) DEFAULT NULL,                                       ' Score ' int (one) DEFAULT NULL, PRIMARY KEY (' no ')    ) engine=innodb DEFAULT charset=utf8mb4 collate=utf8mb4_0900_ai_ci |  +---------+--------------------------------------------------------------------------------------------------+ 1 Row IN Set (0.01 sec) 

• 5) Data Structure statement showing the table

  # 显示表的数据结构# 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) Renaming table statements

  # 重命名表# 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) Modify the table's character Set statement

  # 修改表的字符集# 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) Export Table statement

  # 导出数据库指定的表，同时导出表结构和数据# mysqldump -u 用户名 -p 数据库名 表名 > 导出到的目标文件路径.sql;$ mysqldump -u root -p test student > /Users/haiqianj/Desktop/test.sql;    

  # 导出数据库指定的表，只导出表结构# mysqldump -u 用户名 -p -d 数据库名 表名 > 导出到的目标文件路径.sql;$ mysqldump -u root -p -d test student > /Users/haiqianj/Desktop/test.sql;    

  • The export table is operated in System command mode.

• 9) Import Table statements

  # 导入表/数据库# 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

  • The import table is operated in MySQL command mode after the operation database has been selected.

3. DDL column field Action statement

• 1) Add Column statement

  # 添加列，添加单列# 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) Delete Column statement

    # Delete column, delete single column # ALTER TABLE name drop [column] column name;> ALTER TABLE student drop address    ; Query OK, 0 rows affected (0.10 sec) records:0 duplicates:0 warnings:0  

    # Delete columns, delete multiple columns # alter TA BLE table name drop [column] column name, drop [column] column name, ...;    > ALTER TABLE Student Drop class, drop address; Query OK, 0 rows affected (0.11 sec) records:0 duplicates:0 warnings:0  

• 3) Modify Column name statement

    # Modify column name, modify single column name # ALTER TABLE name change [column] old column name new column name type [constraint] [first | af    TER column name];> ALTER TABLE student change CLA class int; Query OK, 0 rows affected (0.10 sec) records:0 duplicates:0 warnings:0  

    # Modify column names, modify multi-column names # alte  R table name change [column] old column name new column name type [constraint] [first | After column name], change [column] old column name new column name TYPE [constraint] [First | After column name], ...;    > 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) Modify the Column Property statement

  # 修改列属性，修改单列的字段类型及约束# 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) Modify Column order statements

  • The optional parameter in the Add, change, modify statement above [first | After column name] can be used to modify the position of the field in the table.
  • First: Put it at the front of all columns

  • After column name: Placed after the specified column

  • Note: The keys for the change, first, and after column are MySQL extensions on standard SQL and do not necessarily apply in other databases.

SQL DDL Data definition statement