MySQL commands--I- (DDL (DATA DEFINITION LANGUAGE)- COMMANDS/ STATEMENTS )

 DDL (DATA DEFINITION LANGUAGE)- COMMANDS/ STATEMENTS

Use for creating database structure or schema.

Following commands use in DDL.

1.    Create,

2.    Alter,

3.    Truncate,

4.    Rename,

5.    Copy,

6.    Drop.

Create Database:-

syntax:

1. CREATE DATABASE database_name;  

Example

create a database name "employeedb" :

1. mysql> CREATE DATABASE employeesdb;  

 Show databases :- display all databases.

mysql> SHOW DATABASES;    

 

SELECT Database

Syntax:

1. USE database_name;  

Example:

use a database name "customers".

1. USE customers;  

 Show/List Databases

list of all the databases available on the MySQL server:

mysql> SHOW DATABASES;  

 MySQL also allows us another command to list the databases

Mysql> SHOW SCHEMAS;

 

DROP Database

syntax:

1. DROP DATABASE [IF EXISTS] database_name;    

Example

1. DROP DATABASE mytestdb_copy;  

 ============================================================

CREATE TABLE

Syntax:

1.    CREATE TABLE table_name( column_name1 data_type (size) table_constraints , column_definition2, ……,  table_constraints  );  

Example:-

mysql> CREATE TABLE employee_table( id int NOT NULL AUTO_INCREMENT,  

name varchar(45) NOT NULL, occupation varchar(35) NOT NULL, age int NOT NULL,  PRIMARY KEY (id) );  

 

mysql> SHOW TABLES;  

 

See the table structure:

the information or structure of the newly created table:

mysql> DESCRIBE employee_table;  

--------------------------------------------------------------------------------------------------------------

ALTER Table

·         To change the name of your table or any table field. It is also used to add or delete an existing column in a table.

·         The alter statement is always used with "add", "drop" and "modify" commands according to the situation.

 

1) ADD a column in the table

Syntax:

1.    ALTER TABLE table_name  ADD new_column_name column_definition  

[ FIRST | AFTER column_name ];  

Parameters

table_name:  name of the table that wants to modify.

new_column_name:  name of the new column that wants to add to the table.

column_definition:  data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name:  optional. Define MySQL was in the table to create the column. If the parameter is not specified, then the new column will be added to the end of the table.

 

Example:

Use the following query to do this:

1.    ALTER TABLE cus_tbl  ADD cus_age varchar(40) NOT NULL;  

 

See the recently added column:

1.    SELECT* FROM cus_tbl;  

 

2) Add multiple columns in the table

Syntax:

1.    ALTER TABLE table_name   ADD new_column_name column_definition

 [ FIRST | AFTER column_name ],  ADD new_column_name column_definit

ion [ FIRST | AFTER column_name ],    ..;  

 

Example:

1.    ALTER TABLE cus_tbl  ADD cus_address varchar(100) NOT NULL  

2.    AFTER cus_surname,  ADD cus_salary int(100) NOT NULL  

3.    AFTER cus_age ;  

 

See the recently added columns:

1.    SELECT* FROM cus_tbl;  

 

3) MODIFY column in the table

The MODIFY command is used to change the column definition of the table.

Syntax:

1.    ALTER TABLE table_name  MODIFY column_name column_definition  

2.    [ FIRST | AFTER column_name ];  

Example:

1.    ALTER TABLE cus_tbl  MODIFY cus_surname varchar(50) NULL;  

 

4) DROP column in table

Syntax:

1.    ALTER TABLE table_name  DROP COLUMN column_name;  

 

Example:-

ALTER TABLE cus_tbl  DROP COLUMN cus_address;  

 

5) RENAME column in table

Syntax:

1.    ALTER TABLE table_name  CHANGE COLUMN old_name new_name   

column_definition  [ FIRST | AFTER column_name ]  

Example:

ALTER TABLE  cus_tbl  CHANGE COLUMN cus_surname cus_title  

varchar(20) NOT NULL;  

 

6) RENAME table

Syntax:

1.    ALTER TABLE table_name  RENAME TO new_table_name;  

Example:

For example, the table name cus_tbl is renamed as cus_table.

1.    ALTER TABLE cus_tbl  RENAME TO cus_table;  

Show/List Tables

mysql> USE mystudentdb;  

mysql>SHOW TABLES;  

-----------------------------------------------------------------------------------------------------

Rename Table

Syntax

The following are the syntax used to change the name of the table:

mysql> RENAME old_table TO new_table;  

Make sure that new_table_name must not exist, and old_table_name should be present in the database. Otherwise, it will throw an error message.

 

RENAME TABLE Example

mysql> RENAME employee TO customer;  

 

RENAME Multiple Tables

mysql> RENAME TABLE customer TO employee, shirts TO garments;  

 

Rename table using ALTER statement

ALTER TABLE old_table_name RENAME TO new_table_name;  

Example:-

mysql> ALTER TABLE garments RENAME TO shirts:  

 -----------------------------------------------------------------------

TRUNCATE Table

The TRUNCATE command works the same as a DELETE command without using a WHERE clause that deletes complete rows from a table.

The following points must be considered while using the TRUNCATE command:

• Not use the WHERE clause with this command so that filtering of records is not possible.
• Not roll back the deleted data after executing this command because the log is not maintained while performing this operation.
• Not use the truncate statement when a table is referenced by a foreign key or participates in an indexed view.
• Not fire DELETE triggers associated with the table that is being truncated because it does not operate on individual rows.

Syntax

The following syntax explains the TRUNCATE command to remove data from the table:

1.    TRUNCATE [TABLE] table_name;  

 

Example

mysql> TRUNCATE TABLE customer;  

--------------------------------------------------------------------------------------------------------

DESCRIBE TABLE

DESCRIBE command to show the structure of the table, such as column names, constraints on column names, etc.

The DESC command is a short form of the DESCRIBE command. Both DESCRIBE and DESC commands are equivalent and case-sensitive.

Syntax

The following are the syntax to display the table structure:

1.    {DESCRIBE | DESC} table_name;  

mysql> DESCRIBE customer;  

                    or

mysql> DESC customer;  

==========================================================

DROP Table

MYSQL uses a Drop Table statement to delete the existing table. This statement removes the complete data of a table along with the whole structure or definition permanently from the database.

Syntax

The following are the syntax to remove the table in MySQL:

mysql> DROP TABLE  table_name;  

OR,  

mysql> DROP TABLE  schema_name.table_name;  

 

Example

mysql> DROP TABLE  orders;    

It will remove the table permanently.

 

MySQL DROP Multiple Table

DROP TABLE IF EXISTS table_name1, table_name2, table, ......., table_nameN;  

---------------------------------------------------------------------------------------------------------------

Copy/Clone/Duplicate Table

MySQL copy or clone table is a feature that allows us to create a duplicate table of an existing table, including the table structure, indexes, constraints, default values, etc.

Copying data from an existing table into a new table is very useful in a situation like backing up data in table failure.

Copy an existing table to a new table using the CREATE TABLE and SELECT statement, as shown below:

1.    CREATE TABLE new_table_name  SELECT column1, column2, column3   

FROM existing_table_name;  

===================================================