CREATE DATABASE, CREATE TABLE, DROP, ALTER notes| DDL

Here are concise notes on Data Definition Language (DDL) commands in MySQL, specifically focusing on CREATE DATABASE, CREATE TABLE, DROP, and ALTER commands.

Data Definition Language (DDL) in MySQL

1. CREATE DATABASE

The CREATE DATABASE command is used to create a new database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE SchoolDB;

2. CREATE TABLE

The CREATE TABLE command is used to create a new table within a database. You define the table's structure, including the columns and their data types.

Syntax:

CREATE TABLE table_name (column1 datatype constraints,column2 datatype constraints,...);

Example:CREATE TABLE Students (StudentID INT PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(100) NOT NULL,Age INT,EnrollmentDate DATE);

3. DROP

The DROP command is used to delete a database or a table permanently. Be cautious when using this command, as it removes all data and the structure itself.Syntax to Drop a Database:sqlDROP DATABASE database_name;

Syntax to Drop a Table:

DROP TABLE table_name;

Example:

DROP TABLE Students;

DROP DATABASE SchoolDB;

4. ALTER

The ALTER command is used to modify an existing database object, such as a table. You can add, modify, or delete columns, as well as rename tables and columns.

4.1 Alter Table - Add Column

Syntax:ALTER TABLE table_name ADD column_name datatype;

Example:ALTER TABLE Students ADD Email VARCHAR(100);

4.2 Alter Table - Modify Column

Syntax:ALTER TABLE table_name MODIFY column_name new_datatype;

Example:ALTER TABLE Students MODIFY Age TINYINT;

4.3 Alter Table - Drop Column

Syntax:ALTER TABLE table_name DROP COLUMN column_name;

Example:ALTER TABLE Students DROP COLUMN EnrollmentDate;

4.4 Rename Table

Syntax:ALTER TABLE old_table_name RENAME TO new_table_name;

Example:ALTER TABLE Students RENAME TO Pupils;