Data Definition Language (DDL)

Thebestindonesia.com – Data Definition Language (DDL) is a language that can define data related to the creation and deletion of objects, such as tables, indexes, and even the database itself. In principle, DDL is a collection of SQL commands used to create, modify and delete structures and data type definitions from database objects.
Basic commands in DDL include:

1. CREATE: used to create the database, table, view, and index 2. ALTER: used to change the table structure
2. DROP: used to delete the database, table, view, and index
   

A. Data Type
A data type is a form of data modeling declared when making a table. This data type will affect every data entered into a table. The data to be joined must match the declared data type. Data types in MySQL consist of several types, namely text, numeric, and date data types.

Read also : Data Manipulation Language (DML)

Text Data Type

Data Type	Description
CHAR()	A fixed section from 0 to 255 characters long
VARCHAR()	A variable section from 0 to 255 characters long
TINYTEXT	A string with a maximum length of 255 characters
TEXT	A string with a maximum length of 65535 characters

Data Type	Description
BLOB	A binary string with a maximum length of 65535 bytes
MEDIUMTEXT	A string with a maximum length of 16777215 characters
MEDIUMBLOB	A string with a maximum length of 16777215 bytes
LONGTEXT	A string with a maximum length of 4292967295 characters
LONGBLOB	A string with a maximum length of 4292967295 bytes
ENUM(value1,value2,…)	A string object that can only have one value, chosen from  the list of ‘value1’, ‘value2’, … An ENUM can have a  maximum of 65535 distinct values
SET(value1,value2,…)	A string object that can have zero or more values, each of  which must be chosen from the list of values ‘value1’,  ‘value2’, … SET values can have a maximum of 64 members

Numeric Data Type

Data Type	Description
TINYINT()	-128 to 127 normal, 0 to 255 UNSIGNED
SMALLINT()	-32768 to 32767 normal, 0 to 65535 UNSIGNED
MEDIUMINT()	-8388608 to 8388607 normal, 0 to 16777215 UNSIGNED
INT()	-2147483648 to 2147483647 normal, 0 to 4294967295  UNSIGNED
BIGINT()	-9223372036854775808 to 9223372036854775807  normal, 0 to 18446744073709551615 UNSIGNED
FLOAT(M,D)	A small number with a floating decimal point
DOUBLE(M,D)	A large number with a floating decimal point
DECIMAL(M,D)	A fixed decimal point
BIT()	Bit values (0 or 1)

The numeric data types are FLOAT, DOUBLE, and DECIMAL. The value M indicates the total number of digits, while D shows the number of digits after the decimal point. For example, if any column is defined as FLOAT(7,4), it will look like 999.9999.

Date Data Type

Data Type	Description
DATE	YYYY-MM-DD (3 byte)
DATETIME	YYYY-MM-DD HH:MM:SS (8 byte)
TIMESTAMP	YYYYMMDDHHMMSS (4 byte)
TIME	HH:MM:SS (3 byte)
YEAR	YYYY (1 byte)

B. Constraints
Constraints are restrictions or rules that exist in a table. MySQL  provides several types of constraints, namely:

NOT NULL
A column defined with the NOT NULL constraint means that the column cannot contain NULL values (cannot be empty). The column that functions as an automatic primary key cannot be NULL.

Read also : MySQL Query

UNIQUE
A column defined with a UNIQUE constraint means that the column is unique. One data with other data cannot have the same name.

PRIMARY KEYS
PRIMARY KEY constraints establish a unique key for a table.

FOREIGN KEYS
The FOREIGN KEY constraint is defined in a column that is in a table, where that column also belongs to the table, which is a PRIMARY KEY. This constraint is usually used to link two tables.

C. Creating, Selecting, and Deleting Databases

Making a database name cannot contain spaces and cannot have the same name between databases. The command to create a database is as follows:

mysql> CREATE DATABASE databasename;

The database that has been successfully created can be viewed with the following command:

mysql> SHOW DATABASES;

Before creating a table, you must first select an active database that will be used to store the tables. To choose which database to use is as follows:

mysql> USE databasename

The USE command is the same as the QUIT command, without a semicolon (;) at the end. However, if you want to use a semicolon, it becomes no problem.

Read also : MySQL

To find out which database is currently being used, you can use the following command:

mysql> SELECT DATABASE();

If a database has been created and then deleted, then the data stored in the database will also be deleted. To delete the database, namely by using the command:

mysql> DROP DATABASE databasename;

D. Creating and Deleting Tables

The table is the main object that must exist in a database because all data will be stored in it. The table is located in a database, so table creation is done after a database has been created first. In a table, there are rows and columns. Rows are termed recordsets, and columns are termed fields.

To create a table, use the following command:

mysql> CREATE TABLE tablename(columnname1, datatype1, columnname2, datatype2, …);

To display whether the created table has been successfully added to the database, use the following command:

mysql> SHOW TABLES;

To display the table structure in the form of data type and recordset length that has been created, use the following command:

mysql> DESCRIBE tablename;

To delete the table that has been created, use the following command:

mysql> DROP TABLE tablename;

F. Creating and Deleting Primary Keys

In creating a database, a record whose data cannot be the same as other records will be found. To avoid duplicate data, a column must be made, which is declared as the primary key, whereas in a table, there can only be a maximum of one primary key. The command for creating the primary key is as follows:

mysql> CREATE TABLE tablename(columnname1 datatype1 PRIMARY KEY, columnname2 datatype2, …);

or

mysql> CREATE TABLE tablename(columnname1 datatype1, columnname2datatype2, …, PRIMARY KEY(columnname1);

or

mysql> ALTER TABLE tablename ADD CONSTRAINT nameconstraint  PRIMARY KEY (columnname);

In deleting a primary key there are two ways, namely:

1. If the primary key is created using CREATE TABLE

mysql> ALTER TABLE tablename DROP PRIMARY KEY;

2. If the primary key is created using ALTER TABLE

mysql> ALTER TABLE tablename DROP CONSTRAINT nameconstraint;

G. Create a Unique Column (Unique)

A unique column is a column form that does not allow twin data. If there is a data input process with twin data, then the process will be failed or rejected by the database. The command for creating a unique column is as follows:

mysql> CREATE TABLE tablename(columnname1 datatype1, columnname2 datatype2, …, UNIQUE(columnname1));

H. Modifications to the Table

Some changes to the table that can be made include the following:

Rename the table

mysql> RENAME TABLEold_tablename TO new_tablename; or
mysql> ALTER TABLE old_tablename RENAME new_tablename;

Add a new column to the table

mysql> ALTER TABLE tablename ADD new_columnname new_datatype;

or (put the new column at the beginning/left side of the table)

mysql> ALTER TABLE tablename ADD new_columnname new_datatype FIRST;

or (insert new column after specified column)

mysql> ALTER TABLE tablename ADD new_columnname new_datatype AFTER old_columnname;

Change the column names in the table

mysql> ALTER TABLE tablename CHANGE old_columnname new_columnname new_datatype;

Change the data type or column width in the table

mysql> ALTER TABLE tablename MODIFY columnname datatype;

Adding a primary key to an existing column

mysql> ALTER TABLE table name ADD PRIMARY KEY (column name);

Delete columns in the table

mysql> ALTER TABLE tablename DROP columnname;

Delete all data in the table

mysql> TRUNCATE TABLE tablename;