Data Definition Language

DDL is the abbreviation for “Data Definition Language”. And include the most common SQL commands such as CREATE, ALTER, DROP, TRUNCATE etc. And the SQL commands are listed below with short descriptions:

Command Description
CREATE Command CREATE commands is used to create new table, a view of a table, or other object in database.
ALTER Command ALTER commands is used to modifies an existing database object, such as a table.
DROP Command DROP commands is used to deletes an entire table, a view of a table or other object in the database.
TRUNCATE Command TRUNCATE commands is used to truncates the table values without delete table structure.

1) MySQL Create Query/Command:

MySQL Create query is used to reate a new table, User can delete one or multiple records at a time.

Syntax: Following are the SQL syntax of Create command to Create new table in database

CREATE TABLE table name (column1 datatype (size), column2 datatype (size), column3
datatype (size) … columnN datatype (size), PRIMARY KEY (one or more columns));

2) MySQL ALTER Query/Command:

MySQL ALTER query is used to add, delete or modify columns in an existing table .

Syntax: Following are the SQL syntax of ALTER command to add, delete or modify columns in database

i) ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

ii) ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

iii) ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

iv)ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

v) ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name ADD PRIMARY KEY (column1, column2…);`

3) MySQL TRUNCATE Query/Command:

MySQL TRUNCATE TABLE query is used to delete complete data from an existing table

Syntax: TRUNCATE TABLE is as follows:

TRUNCATE TABLE table name;

4) MySQL DROP TABLE Query/Command:

MySQL DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.

Syntax: DROP TABLE statement is as follows:

DROP TABLE table_name

MySQL Comments:

Comments can be used to document the purpose of an SQL statement and it can be placed within your SQL statements in MySQL. These comments can appear on a single line or multiple lines.MySQL Server supports three comment styles:

  • # Comment
  • — Comment
  • /* Comment */

Syntax The basic syntax using # symbol is as follows:

1)From a '#' to the end of the line.

# comment goes here

SELECT 
    first_name, last_name
FROM
    employees
WHERE
    emp_id= 1002; # get employee id

2) From a '-- ' to the end of the line. The double dash-comment style requires at least whitespace or control character (space, tab, newline, etc) after the second dash.

-- comment goes here

SELECT * FROM employees; -- This is a comment

3)C-style comment /**/ can span multiple lines. You use this comment style to document a block of SQL code.

/*
    Get employees id
    that salary is 10000
*/
 
SELECT 
     firstName, lastName
FROM
    employees
WHERE
    emp_id= 1001
        AND salary= 10000;

Notice that MySQL does not support nested comments.

5) MySQL RENAME Query/Command::

The RENAME command is used to change the name of an existing database Table or Column to a new name. To rename a table in MySQL you just need to run a command named RENAME TABLE.

The basic syntax using RENAME TABLE is as follows:

RENAME TABLE table_name1 TO table_name2;

You can also rename more than one table in one command and the syntax is as follows.

RENAME TABLE table_name1 TO table_name2, table_name3 TO table_name4;