Part 2.4Databases and tables

SQL can execute a query against a database, retrieve information from a database, insert new information into a database, remove old information from a database, update current information, create new databases and more.

The next step is to create a database.

Creating a database

The first thing that this tutorial is going to focus on is creation of databases.

A database is a collection of items, in the case of SQL, tables .

A database may consist of many tables. A table consists of records and fields .

The very first thing to do when working with MySQL after connecting successful is to create a database. This is done with the syntax as below:

CREATE DATABASE my_db
		

This will create a new database with the name my_db.

Creating a table

Creating a table is not much different, except that fields need to be added when it is created.

A table is created in the same way with brackets following which contain the names of the fields:

CREATE TABLE People (
	ID INT,
	Name VARCHAR(255),
	Address VARCHAR(255),
	Phone VARCHAR(255)
)
		

In this example, there are four fields:

It's common when creating a table to use multiple lines for a single query with each row on a new line and the closing bracket on a new line.

Getting a list of all databases and tables

Getting a list of all of the databases and tables is done using the SHOW keyword:

SHOW DATABASES
SHOW TABLES
		

Deleting (dropping) a database or table

Deleting a database or table is called dropping it. It uses the DROP keyword to do precisely this.

DROP TABLE mydb.mytable
DROP DATABASE mydb