DDL (Data Definition Language) Commands in MySQL
DDL statements or commands are used to define and modify the database structure of your tables or schema. When you execute a DDL statement, it takes effect immediately.
Some commands of DDL are:
- CREATE – to create table (objects) in the database
- ALTER – alters the structure of the database
- DROP – delete table from the database
- TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
- COMMENT – add comments to the data dictionary
- RENAME – rename a table
The create table statement (query) to create a table is given below:
CREATE TABLE <table name> ( <attribute name 1> <data type 1>, ... <attribute name n> <data type n>);
CREATE TABLE STUDENT ( StudID NUMBER, Name VARCHAR);
The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or NTEGER, which will usually specify a precision; and DATE or related types. Data types are differ according to the databases software whatever you are using to your system.
The alter table statement to make modifications to the table structure such as Key constraints, Column size, etc.
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY(<attribute list>);
ALTER TABLE STUDENT ADD CONSTRAINT NOT NULL PRIMARY KEY (StudID);
The drop table statement (query) to delete a table is given below:
DROP TABLE <table name>;
DROP TABLE STUDENT;