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.

Example: Creating a MySQL Database using Command Line

  1. SSH into your server using Terminal (Mac), Command Prompt (PC).

    terminal

    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.

    vultr example

  2. 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.

  3. 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;

  4. 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.

  5. Flush Privileges

    FLUSH PRIVILEGES;

  6. Exit MySQL

    EXIT;

Now that your database is set up, you can now use the database to create installations like WordPress blogs.