sql

How to Create a Table in MySQL

In this article, we explain how to create a table in MySQL.

1. Introduction

MySQL database is used by developers as a database. Data can be read by different applications from the MySQL database. MySQL database is an open-source relational database.

mysql create table

2. MySQL Database – Table Creation

Mysql database is used for data creation and storing the data.

2.1 Prerequisites

MySQL for the specific operating system is required for this example.

2.2 Download

You can download MySQL can be downloaded from the MySQL website

2.3 Setup

You can unzip the download file into the directory. If your operating system is MacOS, you can use the command below:

Install MySQL

brew install mysql

2.4 MySQL table creation

To avoid, you can skip access issues by running the skip grant tables flag with mysqld-safe as shown below:

Avoid the access issues

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

Now you can start the MySQL server with this command mentioned below:

Start the MySQL Server

sudo /usr/local/mysql/support-files/mysql.server start.

You can log into the MySQL shell with the command below:

Log into MySQL Shell

sudo /usr/local/mysql/bin/mysql -u root

You can enter the password and MySQL shell opens as shown below:

MySQL Shell

apples-MacBook-Air:mysql bhagvan.kommadi$ sudo /usr/local/mysql/bin/mysql -u root
Password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Next, you can create a database with the command below:

Create EmployeeDatabase

create database EmployeeDataBase;

The output when the above command is executed is shown as below:

Create EmployeeDatabase Output

mysql> create database EmployeeDataBase;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| EmployeeDataBase    |              |
+---------------------+
1 row in set (0.08 sec)

Now you can create the table Employee using the database EmployeeDatabase with the command below:

Create EmployeeTable

use  EmployeeDataBase;
CREATE TABLE employee (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups 
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255)); 

The output when the above command is executed is shown as below:

Create EmployeeTable Output

mysql> use  EmployeeDataBase;
Database changed
mysql> CREATE TABLE employee (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
    -> officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups 
    -> VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255)); 
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+----------------------------+
| Tables_in_employeedatabase |
+----------------------------+
| employee                   |
+----------------------------+
1 row in set (0.01 sec)

You can also create a table using a file script. The file script is shown below:

Create TABLE Script

CREATE DATABASE GlobalDB;
USE GlobalDB;
CREATE TABLE MEDIA_ASSET (title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,creator VARCHAR(60) NOT NULL,created_year INT NOT NULL,PRIMARY KEY(title));
INSERT INTO MEDIA_ASSET VALUE ("Phoenix", "documentary", "Thomas Smith", 2011);

Now you can run the script with the command below:

Create TABLE Script

sudo /usr/local/mysql/bin/mysql -u root -p < script.txt

The output when the above command executed is shown as below:

Create TABLE Script – Output

apples-MacBook-Air:mysqlcode bhagvan.kommadi$ sudo /usr/local/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 466
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| GlobalDB            |
+---------------------+
1 row in set (0.03 sec)

mysql> use GlobalDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_globaldb |
+--------------------+
| MEDIA_ASSET        |
+--------------------+
1 row in set (0.00 sec)

mysql> describe MEDIA_ASSET;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| title        | varchar(50) | NO   | PRI | NULL    |       |
| genre        | varchar(30) | NO   |     | NULL    |       |
| creator      | varchar(60) | NO   |     | NULL    |       |
| created_year | int(11)     | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

You can query the database with the command shown below:

Query TABLE command

SELECT * FROM MEDIA_ASSET;

The output when the above command is executed is shown as below:

Query TABLE command-Output

mysql> SELECT * FROM MEDIA_ASSET;
+---------+-------------+--------------+--------------+
| title   | genre       | creator      | created_year |
+---------+-------------+--------------+--------------+
| Phoenix | documentary | Thomas Smith |         2011 |
+---------+-------------+--------------+--------------+
1 row in set (0.02 sec)

3. Download the Source Code

Download
You can download the full source code of this example here: How to Create a Table in MySQL

Bhagvan Kommadi

Bhagvan Kommadi is the Founder of Architect Corner & has around 20 years’ experience in the industry, ranging from large scale enterprise development to helping incubate software product start-ups. He has done Masters in Industrial Systems Engineering at Georgia Institute of Technology (1997) and Bachelors in Aerospace Engineering from Indian Institute of Technology, Madras (1993). He is member of IFX forum,Oracle JCP and participant in Java Community Process. He founded Quantica Computacao, the first quantum computing startup in India. Markets and Markets have positioned Quantica Computacao in ‘Emerging Companies’ section of Quantum Computing quadrants. Bhagvan has engineered and developed simulators and tools in the area of quantum technology using IBM Q, Microsoft Q# and Google QScript. He has reviewed the Manning book titled : "Machine Learning with TensorFlow”. He is also the author of Packt Publishing book - "Hands-On Data Structures and Algorithms with Go".He is member of IFX forum,Oracle JCP and participant in Java Community Process. He is member of the MIT Technology Review Global Panel.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button