Detailed data Definition language (DDL) for SQL statements

Action object: Database

1) Create a database

MariaDB [(None)]> help CREATE Database
Name: ' CREATE DATABASE '
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF not EXISTS] Db_name
[Create_specification] ...

Create_specification:
[DEFAULT] CHARACTER Set [=] Charset_name (Specify character Set)
| [DEFAULT] COLLATE [=] Collation_name (Specify collation)

To view the character set and collation that the system supports by default:

MariaDB [(None)]> show character set;

MariaDB [(None)]> show collation;

We can specify the character set and collation when creating the database, and if not specified, the character set and collation specified by the MySQL server level are used by default

Case:

Create a Discuz database

MariaDB [(None)]> CREATE database discuz default character set UTF8;
Query OK, 1 row Affected (0.00 sec)

2) Modify the database

MariaDB [(none)]> help ALTER DATABASE
Name: ' ALTER DATABASE '
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [Db_name]
Alter_specification ... (Modify the character set and collation of the database)
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME

Alter_specification:
[DEFAULT] CHARACTER SET [=] Charset_name
| [DEFAULT] COLLATE [=] Collation_name

(note: Rarely used in general work)

3) Delete Database

MariaDB [(None)]> help drop database
Name: ' DROP DATABASE '
Description:
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] Db_name

Case:

MariaDB [(None)]> drop database discuz;
Query OK, 0 rows Affected (0.00 sec)

Action object: Table

1) Create a table

Three ways to create a table

MariaDB [(None)]> help CREATE table
Name: ' CREATE TABLE '
Description:
Syntax:
CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
(Create_definition,...)
[Table_options]
[Partition_options]

Or:

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
[(Create_definition,...)]
[Table_options]
[Partition_options]
Select_statement

Or:

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name
{Like Old_tbl_name | (Like Old_tbl_name)}

Create_definition:
Col_name column_definition

Column_definition:
data_type [Not NULL | NULL] [DEFAULT default_value]
[Auto_increment] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT ' string ']
[Column_format {fixed| dynamic| DEFAULT}]
[STORAGE {disk| memory| DEFAULT}]
[Reference_definition]

Reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH Full | MATCH PARTIAL | MATCH Simple]
[on DELETE reference_option]
[on UPDATE reference_option]

Table_option:
ENGINE [=] Engine_name
| auto_increment [=] Value
| avg_row_length [=] Value
| [DEFAULT] CHARACTER SET [=] Charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] Collation_name
| COMMENT [=] ' string '
| CONNECTION [=] ' connect_string '
| DATA directory [=] ' absolute path to DIRECTORY '
| Delay_key_write [=] {0 | 1}
| INDEX directory [=] ' absolute path to DIRECTORY '
| 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 Tablespace_name [STORAGE {disk| memory| DEFAULT}]
| UNION [=] (Tbl_name[,tbl_name] ...)

Anyway:

CREATE TABLE table name (field 1 datatype modifier, field 2 datatype modifier ...) ) table_option

Example 1

MariaDB [testdb]> CREATE TABLE TBL1 (ID int not null,name varchar (+) not null,age tinyint);
Query OK, 0 rows affected (0.09 sec)

Example 2

MariaDB [testdb]> CREATE TABLE Products (prod_id char (TEN) not null,vend_id char (TEN) not null,prod_name varchar (254) not Null,prod_price decimal (8,2) not null,prod_desc varchar (1000));
Query OK, 0 rows affected (0.07 sec)

View table Structure

MariaDB [testdb]> desc Products;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| prod_id | char (10) | NO | | NULL | |
| vend_id | char (10) | NO | | NULL | |
| Prod_name | varchar (254) | NO | | NULL | |
| Prod_price | Decimal (8,2) | NO | | NULL | |
| Prod_desc | varchar (1000) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in Set (0.00 sec)

2) Delete Table

MariaDB [testdb]> help drop table
Name: ' DROP TABLE '
Description:
Syntax:
DROP [temporary] TABLE [IF EXISTS]
Tbl_name [, Tbl_name] ...
[RESTRICT | CASCADE]

MariaDB [testdb]> drop table tbl1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

3) Modify the table

MariaDB [testdb]> help ALTER TABLE
Name: ' ALTER TABLE '
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[Alter_specification [, Alter_specification] ...]
[Partition_options]

Alter_specification:
Table_options
| ADD [COLUMN] col_name column_definition
[First | After Col_name]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {index| KEY} [Index_name]
[Index_type] (Index_col_name,...) [Index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[Index_type] (Index_col_name,...) [Index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [index| KEY] [index_name]
[Index_type] (Index_col_name,...) [Index_option] ...
| ADD Fulltext [index| KEY] [index_name]
(Index_col_name,...) [Index_option] ...
| ADD SPATIAL [index| KEY] [index_name]
(Index_col_name,...) [Index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (Index_col_name,...)
Reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| Change [COLUMN] old_col_name new_col_name column_definition
[first| After Col_name]
| MODIFY [COLUMN] col_name column_definition
[First | After Col_name]
| DROP [COLUMN] Col_name
| DROP PRIMARY KEY
| DROP {index| KEY} index_name
| DROP FOREIGN KEY Fk_symbol
| Max_Rows = ROWS
| DISABLE KEYS
| ENABLE KEYS
| RENAME [To|as] New_tbl_name
| ORDER by Col_name [, Col_name] ...
| CONVERT to CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD tablespace
| IMPORT tablespace
| Force
| ADD PARTITION (partition_definition)
| DROP PARTITION Partition_names
| TRUNCATE PARTITION {partition_names | All}
| COALESCE PARTITION Number
| REORGANIZE PARTITION [partition_names into (partition_definitions)]
| ANALYZE PARTITION {partition_names | All}
| CHECK PARTITION {partition_names | All}
| OPTIMIZE PARTITION {partition_names | All}
| REBUILD PARTITION {partition_names | All}
| REPAIR PARTITION {partition_names | All}
| PARTITION by Partitioning_expression
| REMOVE Partitioning

Index_col_name:
col_name [(length)] [ASC | DESC]

Index_type:
USING {BTREE | HASH}

Index_option:
key_block_size [=] Value
| Index_type
| With PARSER Parser_name
| COMMENT ' String '

Table_options:
Table_option [[,] table_option] ... (see CREATE TABLE options)

Partition_options:
(see CREATE TABLE options)

Add a field to a table

MariaDB [testdb]> ALTER TABLE Products ADD vend_phone CHAR (20);

Detailed data Definition language (DDL) for SQL statements