MySQL Tutorial: Create Database, Tables and Data Types

In this tutorial, you will learn about MySQL, how to create SQL databases, tables and various data types.

Prerequisites

You need to have MySQL server installed on your machine along with the MySQL client.

Create DATABASE | Create schema in MySQL

You can create a database in MySQL using the SQL instruction CREATE DATABASE .

Open a new terminal and invoke the mysql client using the following command:

$ mysql -u root -p

Enter the password for your MySQL server when prompted.

You can now execute SQL statments. Let's see an example of creating a database named mydb:

mysql> create database mydb;

Note: You can also use create schema for creating a database.

You can also add other parameters.

CREATE DATABASE IF NOT EXISTS

You can create multiple databases in your MySQL server. When using the IF NOT EXISTS parameter, you tell MySQL to create the database if no database with the same name is alreay created. This is will only prevent MySQL for displaying an error and aborting the opeartion but if a database with the same name exists It will not be overwritten:

mysql> create database if not exists mydb;

This will create a database named mydb and fail silently if a database with the name already exists.

SHOW DATABASES

You can get the list of created databases in your MySQL server using the SHOW DATABASES SQL instruction. In your terminal, simply run:

mysql> show databases;

Create a MySQL Table and Columns

After creating a database, the next thing that you would need is creating the database tables and their fields.

In your MySQL client, run the following SQL insruction to create a table and columns:

mysql> CREATE  TABLE IF NOT EXISTS `Contacts` (
  `id` INT  AUTOINCREMENT ,
  `first_name` VARCHAR(150) NOT NULL ,
  `gender` VARCHAR(6) ,
  `date_of_birth` DATE ,
  `address` VARCHAR(255) ,
  `postal_address` VARCHAR(255) ,
  `phone` VARCHAR(75) ,
  `email` VARCHAR(255) ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

Here is the format of the instruction we used:

CREATE TABLE [IF NOT EXISTS] TableName (columnName dataType [optional parameters]) ENGINE = storage Engine;

The CREATE TABLE part instructs MySQL to create a SQL table with the specified name in the database.

The optional IF NOT EXISTS part insturcts MySQL to create the table only if no table with the same name exists in the database.

columnName refers to the name of the column and data Type refers to the type of data that can be stored in the corresponding column.

The optional parameters section contains options about a specific column such as PRIMARY KEY, AUTO_INCREMENT or NOT NULL, etc.

MySQL DATA TYPES

Let's now see the available types that can be used for table columns in MySQL.

Simply put, a data types defines the nature of the data that can be stored in a particular column of a table.

MySQL data types can be categorized in three categories:

  • Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL,
  • Text: CHAR, VARCHAR, TINYTEXT, TEXT, BLOB, MEDIUMTEXT, MEDIUMBLOB, LONGTEXT and LONGBLOB,
  • Date and time: DATE, DATETIME, TIMESTAMP and TIME.

Apart from above there are some other data types in MySQL:

  • ENUM To store text value chosen from a list of predefined text values
  • SET This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
  • BOOL Synonym for TINYINT(1), used to store Boolean values
  • BINARY Similar to CHAR, difference is texts are stored in binary format.
  • VARBINARY Similar to VARCHAR, difference is texts are stor

Conclusion

In this post, we've seen how to create MySQL database and tables with columns and data types.

Note: We also publish our tutorials on Medium and DEV.to. If you prefer reading in these platforms, you can follow us there to get our newest articles.

You can reach the author via Twitter:

About the author

Ahmed Bouchefra
is a web developer with 5+ years of experience and technical author with an engineering degree on software development. You can hire him with a click on the link above or contact him via his LinkedIn account. He authored technical content for the industry-leading websites such as SitePoint, Smashing, DigitalOcean, RealPython, freeCodeCamp, JScrambler, Pusher, and Auth0. He also co-authored various books about modern web development that you can find from Amazon or Leanpub


Get our Learn Angular 8 in 15 Easy Steps ebook in pdf, epub and mobi formats, plus a new Angular 8 tutorial every 3 days.


Online Courses (Affiliate)

If you prefer learning with videos. Check out one of the best Angular courses online
Angular 8 - The Complete Guide (2019+ Edition)

Angular Crash Course for Busy Developers

comments powered by Disqus
DMCA.com Protection Status