Learn Basic Data Definition Language DDL Commands in MySQL With CMD Query

Summary

In this detailed tutorial, the presenter walks through the basics of Data Definition Language (DDL) in MySQL, covering essential commands like CREATE, ALTER, RENAME, and DROP. The video demonstrates how to create and modify databases and tables, add or remove columns, rename tables, and delete them entirely. Practical examples are provided for each operation, helping viewers understand how to work with MySQL queries through the command prompt. This tutorial is perfect for those looking to learn and practice fundamental database management tasks in MySQL.

Takeaways

• 😀 DDL (Data Definition Language) in MySQL is used to create, modify, or delete database structures.
• 😀 The main DDL commands in MySQL are CREATE, ALTER, RENAME, and DROP.
• 😀 The CREATE DATABASE command is used to create a new database in MySQL. Example: 'CREATE DATABASE kuliah;'
• 😀 The CREATE TABLE command is used to define the structure of a table, including column names and data types.
• 😀 Data types like INTEGER and VARCHAR are used to define the data stored in the table's columns.
• 😀 The ALTER TABLE command is used to modify an existing table structure, such as adding, modifying, or deleting columns.
• 😀 The RENAME TABLE command is used to change the name of an existing table.
• 😀 The DROP command is used to delete a database or table from MySQL, which cannot be undone.
• 😀 When creating a table, you can assign a PRIMARY KEY to ensure unique entries, such as 'name' for a student ID.
• 😀 In the ALTER TABLE command, the MODIFY keyword is used to change the data type or properties of a column, such as increasing VARCHAR length.
• 😀 The DROP DATABASE command is used to remove an entire database from MySQL, which also deletes all its tables.

Q & A

What is Data Definition Language (DDL) in MySQL?

DDL in MySQL refers to a set of SQL commands used to define, modify, and delete database structures. It includes commands such as CREATE, ALTER, RENAME, and DROP.

What does the CREATE DATABASE command do?

The CREATE DATABASE command is used to create a new database in MySQL. The syntax is `CREATE DATABASE database_name;`.

Can database names in MySQL contain spaces?

No, database names cannot contain spaces in MySQL. Instead, use an underscore (_) to separate words, like `kuliah_perdana`.

How do you create a table in MySQL?

To create a table, use the `CREATE TABLE` command followed by the table name and column definitions. For example: `CREATE TABLE mahasiswa (name INT, address VARCHAR(100), PRIMARY KEY (name));`.

What is the purpose of a PRIMARY KEY in a table?

The PRIMARY KEY ensures that the values in a column (or a combination of columns) are unique and not null, making it the identifier for each record in the table.

What does the ALTER TABLE command do?

The ALTER TABLE command is used to modify the structure of an existing table. It can be used to add, modify, or delete columns in a table.

How do you add a column to an existing table in MySQL?

To add a column, use the `ALTER TABLE` command with the `ADD` keyword followed by the column name and data type. For example: `ALTER TABLE mahasiswa ADD umur INT;`.

How can you modify the data type of an existing column?

To modify the data type of a column, use the `ALTER TABLE` command with the `MODIFY` keyword followed by the column name and new data type. For example: `ALTER TABLE mahasiswa MODIFY COLUMN address VARCHAR(150);`.

What is the syntax for renaming a table in MySQL?

To rename a table, use the `RENAME TABLE` command followed by the old table name and the new table name. For example: `RENAME TABLE mahasiswa TO siswa;`.

What does the DROP command do in MySQL?

The DROP command is used to delete a table or database in MySQL. For example, `DROP TABLE table_name;` deletes a table, and `DROP DATABASE database_name;` deletes a database.