How to create a database MySQL

What is MySQL

MySQL is a relational database management system, Database is organized data into more than one table. MySQL is a database system used for web application and its run on the server. The MySQL database is reliable, fast, easy to use. Mysql is a programming language used to create, insert, update, and extract data from the relational database (RDMS).

MySQL Create Database, Table, and Data Types

In this tutorial, we will learn how to create a database Mysql.
There are multiple ways to create a database MySQL but in this tutorial, we will learn the below 3 ways.

1. By executing a SQL script/Query
2. By using Command line
3. By using the phpMyAdmin user interface

Table of Contents

1. Create database MySQL
2. Create Table
3. Data Types
4. Removing Databases (DROP DATABASE [IF EXISTS])

1. Create database MySQL

We need to write the CREATE DATABASE command to create a database MySQL. CREATE DATABASE is a SQL command which is used to create a database.

Now we will create a database named “testdemo” by executing the below SQL command line.

CREATE DATABASE testdemo;

Or you can also create a database by using the phpMyAdmin below the user interface. In this interface, just you need to simply enter the database name in the text field and submit. After submitting your database has been created in MySQL.

IF NOT EXISTS

The IF NOT EXISTS is an optional clause of the SQL statement. The IF NOT EXISTS clause statement prevents to check the existence of a database by making a new database that already exists in the database server. You can’t create 2 databases with identical names in a MySQL database server.

CREATE DATABASE IF NOT EXISTS testdemo;

SHOW DATABASES

SHOW DATABASES statement will show the list of all the databases in the MySQL database server.
You can check the database that you are created on the MySQL server by executing the SHOW DATABASES statement on the MySQL database server.

DROP DATABASE (Removing Databases) [IF EXISTS]

If we want to delete the DATABASE so we need to execute the following command on the database server. The IF EXISTS is an optional clause of the statement prevents to check a database before removing a database that does not exist in the database server.

DROP DATABASE [IF EXISTS] testdemo

Collation

A collation is a set of rules that defines examine and sort character strings. Every collation in MySQL belongs to a single character set. Each character set has a minimum of one collation, and most have two or more collations.

SHOW CHARACTER SET

Default Collations of the character set as follows

Charset	Description	Default collation	Maxlen
big5	Big5 Traditional Chinese	big5_chinese_ci	2
dec8	DEC West European	dec8_swedish_ci	1
cp850	DOS West European	cp850_general_ci	1
hp8	HP West European	hp8_english_ci	1
koi8r	KOI8-R Relcom Russian	koi8r_general_ci	1
latin1	cp1252 West European	latin1_swedish_ci	1
latin2	ISO 8859-2 Central European	latin2_general_ci	1
swe7	7bit Swedish	swe7_swedish_ci	1
ascii	US ASCII	ascii_general_ci	1
ujis	EUC-JP Japanese	ujis_japanese_ci	3
sjis	Shift-JIS Japanese	sjis_japanese_ci	2
hebrew	ISO 8859-8 Hebrew	hebrew_general_ci	1
tis620	TIS620 Thai	tis620_thai_ci	1
euckr	EUC-KR Korean	euckr_korean_ci	2
koi8u	KOI8-U Ukrainian	koi8u_general_ci	1
gb2312	GB2312 Simplified Chinese	gb2312_chinese_ci	2
greek	ISO 8859-7 Greek	greek_general_ci	1
cp1250	Windows Central European	cp1250_general_ci	1
gbk	GBK Simplified Chinese	gbk_chinese_ci	2
latin5	ISO 8859-9 Turkish	latin5_turkish_ci	1
armscii8	ARMSCII-8 Armenian	armscii8_general_ci	1
utf8	UTF-8 Unicode	utf8_general_ci	3
ucs2	UCS-2 Unicode	ucs2_general_ci	2
cp866	DOS Russian	cp866_general_ci	1
keybcs2	DOS Kamenicky Czech-Slovak	keybcs2_general_ci	1
macce	Mac Central European	macce_general_ci	1
macroman	Mac West European	macroman_general_ci	1
cp852	DOS Central European	cp852_general_ci	1
latin7	ISO 8859-13 Baltic	latin7_general_ci	1
utf8mb4	UTF-8 Unicode	utf8mb4_general_ci	4
cp1251	Windows Cyrillic	cp1251_general_ci	1
utf16	UTF-16 Unicode	utf16_general_ci	4
utf16le	UTF-16LE Unicode	utf16le_general_ci	4
cp1256	Windows Arabic	cp1256_general_ci	1
cp1257	Windows Baltic	cp1257_general_ci	1
utf32	UTF-32 Unicode	utf32_general_ci	4
binary	Binary pseudo charset	binary	1
geostd8	GEOSTD8 Georgian	geostd8_general_ci	1
cp932	SJIS for Windows Japanese	cp932_japanese_ci	2
eucjpms	UJIS for Windows Japanese	eucjpms_japanese_ci	3
gb18030	China National Standard GB18030	gb18030_chinese_ci	4

2. Create Table

Create table statement is used to create a table in the MySQL database.

Following the below Create table statement.

DROP TABLE IF EXISTS `testrecords`;
CREATE TABLE IF NOT EXISTS `testrecords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

We can also create a table by using the PHPMYADMIN interface. The interface looks like.

The following way to create a table using PHP PDO.

<?php
class Database
{
     
    private $host = "localhost";
    private $db_name = "testdemo";
    private $username = "root";
    private $password = "";
    public $conn;
     
    public function dbConnection()
	{
     
	    $this->conn = null;    
        try
		{
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
			$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);	
			
// Mysql create table
  $sql = "CREATE TABLE IF NOT EXISTS `testrecords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullName` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)";
$conn->exec($sql);
echo "Table has been created successfully";
        }
		catch(PDOException $exception)
		{
            echo "Connection error: " . $exception->getMessage();
        }
         
        return $this->conn;
    }
}
?>

3. Data Types

MySQL uses many different data types its categories into 5 categories.

1. Numeric data types
2. Date and Time
3. String data types
4. Spatial data types
5. JSON

Numeric data types

The list of the following numeric data types below.

• INT 
• TINYINT 
• SMALLINT 
• MEDIUMINT 
• BIGINT 
• FLOAT(M,D)
• DOUBLE(M,D)
• DECIMAL(M,D)

Date and Time

1. DATE
2. DATETIME
3. TIMESTAMP
4. TIME
5. YEAR

String data types

• CHAR
• VARCHAR
• BLOB or TEXT
• TINYBLOB or TINYTEXT
• MEDIUMBLOB or MEDIUMTEXT
• LONGBLOB or LONGTEXT
• ENUM 

Spatial data types

• GEOMETRY
• POINTS
• LINESTRING
• POLYGON
• MULTIPOINT
• MULTILINESTRING
• MULTIPOLIGON
• GEOMETRY COLLECTION