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> 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 ];  

 

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  

AFTER cus_surname,  ADD cus_salary int(100) NOT NULL  

 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  [ 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;  

 

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.

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;  

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