Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 2.4Databases and tables

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:

SQL
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:

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

In this example, there are four fields:

Field name Field type
ID INT
Name VARCHAR(255)
Address VARCHAR(255)
Phone VARCHAR(255)

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:

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

SQL
DROP TABLE mydb.mytable
DROP DATABASE mydb
		
Feedback 👍
Comments are sent via email to me.