Database professionals use DDL, DML, and DCL commands to interact with a database. Each command has a specific purpose that helps shape the data within the database. In this blog post, we will give you a quick cheat sheet of these commands so you can get to the building.
Data Definition Language (DDL) commands:
CREATE
to create a new table or database.ALTER
for alteration.Truncate
to delete data from the table.DROP
to drop a table.RENAME
to rename a table.
Data Manipulation Language (DML) commands:
INSERT
to insert a new row.UPDATE
to update an existing row.DELETE
to delete a row.MERGE
for merging two rows or two tables.
Data Control Language (DCL) commands:
COMMIT
to permanently save.ROLLBACK
to undo the change.SAVEPOINT
to save temporarily.
Table of Contents
Example: Creating a MySQL Database using Command Line
SSH into your server using Terminal (Mac), Command Prompt (PC).
Open Terminal or Command Prompt, enter your username & IP address, then password when prompted.
ssh [email protected]
Password
This password is typically in your hosting’s admin console settings.
Log in to MySQL
mysql -u root -p
This command logs you into Root (administrative settings), you will need to find the password from your hosting provider.
Create the New Database
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Replace database_name in the example above with a name for your database.
You can confirm that the database was created by using the command below:
SHOW DATABASES;
Create a New Database User
You will need to create a user so WordPress can access the database
GRANT ALL ON database_name.* TO 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Replace database_name, database_user, and user_password with desired settings and record the values to use when you setup the new WordPress installation.
Flush Privileges
FLUSH PRIVILEGES;
Exit MySQL
EXIT;
Now that your database is set up, you can now use the database to create installations like WordPress blogs.