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:
- CREATE DATABASE database_name;
Example
create a database name "employeedb" :
- mysql> CREATE DATABASE employeesdb;
Show databases :- display all databases.
mysql> SHOW DATABASES;
SELECT Database
Syntax:
- USE database_name;
Example:
use a database name "customers".
- 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:
- DROP DATABASE [IF EXISTS] database_name;
Example
- 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;
===================================================
0 Comments