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.
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
You can download the full source code of this example here: How to Create a Table in MySQL
