How to Use DDL Commands to Manage Databases and Tables

DDL Commands

Users and applications may store, manage, and retrieve data in an organized manner with MySQL, a popular relational database management system (RDBMS). One or more tables are usually used to organize the data in a relational database. A group of rows and columns is called a table. Using the supplied response will describe basic MySQL database and table management procedures using the command-line client and standard SQL syntax.

Creating a Database

Building a database You need a database to hold the data before you can store it. The CREATE DATABASE statement is used in MySQL to create a new database.

The basic syntax is:

CREATE DATABASE db_name;

For example, to create a database named mydb:

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.05 sec)

To build a database, you often need specific build rights. MySQL will give you an error if you attempt to create an existing database (Error 1007). The IF NOT EXISTS clause can be used to prevent this error:

CREATE DATABASE IF NOT EXISTS Baseball;

Keep in mind that SCHEMA, the abbreviated form of the word, is a synonym for DATABASE.

Opening/Selecting a Database

Choosing or opening a database After connecting to the MySQL server, you frequently have to choose which database to use. This instructs MySQL on which database to run the next instructions on.

The statement for this is USE:

USE db_name;

For example, to select the mydb database:

mysql> USE mydb;
Database Changed

Even if adding a semicolon terminator doesn’t hurt anything, the USE statement is unique because it doesn’t need one. Additionally, it must be written on a single line.

SELECT DATABASE() can be used at any moment to determine which database is currently selected. If no database is chosen, NULL will be returned.

Getting a Listing of Databases

Obtaining a database listing The SHOW DATABASES query can be used to view the databases that are present on the MySQL server that you have access to.

The syntax is simply:

SHOW DATABASES;

This command provides a list of the databases that the MySQL DBMS can access. As an illustration: mysql> SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pets               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

You can view the information_schema and mysql databases, which MySQL uses as system databases, but you should usually avoid writing to them.

Creating a Table

Making a table Tables are used to store data in databases. The primary component of any database is a table. The CREATE TABLE statement is used to create a new table.

The general syntax is:

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

To create a table, define its columns, data types, and other criteria. Create a three-column table called mytable:

CREATE TABLE mytable (
    id              int unsigned NOT NULL auto_increment,
    username        varchar(100) NOT NULL,
    email           varchar(100) NOT NULL,
    PRIMARY KEY     (id)
); 

The IF NOT EXISTS clause can also be used with CREATE TABLE. Although they are not case sensitive, SQL keywords are typically capitalized. In general, it is best to avoid using reserved terms or column or table names that contain spaces; if they are used, they should be enclosed in back-tick “ delimiters.

Column definition using data types Columns specify the kind of information they hold. Numerous data kinds, such as string, date and time, and numeric types, are supported by MySQL.

Defining columns with data types

Data types include, for example:

• Numerical: DECIMAL (or NUMERIC), FLOAT, SMALLINT, INT (or INTEGER). BOOLEAN or BOOL is synonymous with TINYINT(1). AUTO_INCREMENT can be used to define integer types; it is commonly used for primary key fields to automatically generate a unique number for every new row.
  Date and Time: Date, time, year, and datetime. Calendar dates should be ‘YYYY-MM-DD’.
• String: TEXT, ENUM, VARCHAR, CHAR. You need to specify a length for VARCHAR. A list of acceptable values is defined by ENUM.

Constraints such as NOT NULL, which states that the column must contain a value, can also be included in column definitions. A field, or set of fields, that has a distinct value for every row is called a primary key. It can be specified at the table or column level.

Viewing Table Structure

Viewing table structure Table columns’ names, data types, nullability, and keys are obtained with the DESCRIBE statement.

The syntax is:

DESCRIBE table_name;

DESCRIBE is abbreviated as DESC. For instance, mysql> DESCRIBE pet; describes the pet table.

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+ 

The output displays the data type (Type), column name (Field), if it can be NULL, whether it is a key (PRI for the primary key, for example), its default value, and any other information such as auto_increment. You can use SHOW CREATE TABLE to get even more information, such as the engine and character set of the table.

Getting a Listing of Tables

Obtaining a table listing The SHOW TABLES statement can be used to list the tables in a database that has been chosen using the USE command.

The syntax is:

SHOW TABLES;

This command displays the selected database’s tables. Using the pets database, for instance:

mysql> SHOW TABLES;
+----------------+
| Tables_in_pets |
+----------------+
| cats           |
+----------------+
1 row in set (0.00 sec)

Tables_in_db_name will be the column name in the output, where db_name is the name of the database you are using at the moment.

Deleting a Database

Taking a database down The DROP DATABASE statement is used to delete a database along with all of its tables and data.

The syntax is:

DROP DATABASE db_name;

For example:

DROP DATABASE TUTORIALS;

To drop a database, certain privileges are required. All database data will be permanently erased by this operation. If the database does not exist, you can use IF EXISTS to prevent an error, just like with CREATE DATABASE:

DROP DATABASE IF EXISTS Baseball;

You may be prompted for confirmation when removing a database using the mysqladmin command-line tool, but using a script (such as PHP) usually does not, so you should proceed with caution.

Deleting a Table

Taking a table away The DROP TABLE statement is used when a table needs to be deleted from a database.

The syntax is:

DROP TABLE table_name;

For example, to delete the tutorials_tbl table:

mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)

A table’s structure and data are permanently deleted from the database. DROP TABLE’s IF EXISTS clause prevents errors if the table doesn’t exist. Don’t confuse DROP TABLE with TRUNCATE TABLE, which deletes all records but preserves the table structure. DELETE uses DML to remove rows, while TRUNCATE TABLE uses DDL.

The basic functions for organizing your data storage in a MySQL database are provided by these commands.