How to Create a Database in MySQL

This is an article on how to create a database in MySQL. To create a database, you must open the “mysql” command line interface and enter database commands while the server is running.

Table of Contents

Open the MySQL command line program

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/50/258108-1.jpg/v4-728px-258108-1.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/5/50/258108-1.jpg/v4-728px-258108-1.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Make sure the MySQL server is connected. If the MySQL server does not have a network connection, you cannot create the database.

You can check the status of your server by opening MySQL Workbench, selecting your server, and viewing the information under “Server Status” under the “Administration – Server Status” tab. server state).

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6a/258108-2.jpg/v4-728px-258108-2.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/6/6a/258108-2.jpg/v4-728px-258108-2.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Copy the path of the installation directory. This path will be different depending on whether you use a Windows or Mac computer:

Windows — Copy C:/Program Files/MySQL/MySQL Workbench 8.0 CE/ and replace the name of the last directory with the name of the current MySQL.
Mac — Copy /usr/local/mysql-8.0.13-osx10.13-x86_64/ and replace the name of the last directory with the name of the current MySQL.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/57/258108-3.jpg/v4-728px-258108-3.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/5/57/258108-3.jpg/v4-728px-258108-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open your computer’s command line program. You will use the Command Prompt on a Windows computer, and use the Terminal on a Mac computer.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/92/258108-4.jpg/v4-728px-258108-4.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/9/92/258108-4.jpg/v4-728px-258108-4.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the path of the MySQL installation directory. Type cd with a space, paste the path to the installation directory, and press ↵ Enter . For example, you would do the following on most Windows computers:

 cd C:Program FilesMySQLMySQL Workbench 8.0 CE

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/cd/258108-5.jpg/v4-728px-258108-5.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/c/cd/258108-5.jpg/v4-728px-258108-5.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the MySQL login command. For example, to open the login command for a user named “me”, you would type the following and press ↵ Enter :

 mysql - u me - p

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ee/258108-6.jpg/v4-728px-258108-6.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/e/ee/258108-6.jpg/v4-728px-258108-6.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the account’s password. You need to enter the password for the MySQL user account, and then press ↵ Enter . This is the operation of logging in and linking the command line program to MySQL.

You should see a “MySQL>” tab showing the command line program. From now on, every command line that you enter is handled by the MySQL command line program.
Learn how to enter MySQL commands. MySQL commands must have a semicolon (;) right after the end of the command line, but you can also type the command, type a semicolon, and press ↵ Enter again.

Create a database

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1a/258108-7.jpg/v4-728px-258108-7.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/1/1a/258108-7.jpg/v4-728px-258108-7.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Create database file. Do this by typing the command “create database” create database , entering the name of the database with a semicolon, and pressing ↵ Enter . For example, for a database named “Pet Records”, you would enter the following:

 create database Pet_Records ;

The name of the database cannot contain spaces; if you want to add a space to the name, enter an underscore (for example, “Friends of Mine” becomes “Friends_of_Mine”).
Each MySQL command must end with a semicolon. If you forget to enter a semicolon, you can type it right next to the currently displayed … and press ↵ Enter again.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/22/258108-8.jpg/v4-728px-258108-8.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/2/22/258108-8.jpg/v4-728px-258108-8.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Displays the current database. You can open the current list of databases by typing the following command and pressing ↵ Enter :

 show databases ;

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/70/258108-9.jpg/v4-728px-258108-9.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/7/70/258108-9.jpg/v4-728px-258108-9.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select your database. You can select a database from the list by entering use name with “name” being the name of your database. For example, for the “Pet Records” database, you would type the following and press ↵ Enter :

 use Pet_Records ;

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/ef/258108-10.jpg/v4-728px-258108-10.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/e/ef/258108-10.jpg/v4-728px-258108-10.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Wait for the confirmation message. When you see the “Database changed” message displayed below the command you just entered, you can move on to the step of creating the database’s content.

Create Table

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/27/258108-11.jpg/v4-728px-258108-11.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/2/27/258108-11.jpg/v4-728px-258108-11.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Learn the commands to create tables. Before creating a table, you need to understand a few key elements of the table as follows:

Title — Your title should appear immediately after the “create table” command, and be set according to the same principles as the database name (i.e. no spaces) .
Cpumn Heading — You can set column headings by entering various names within parentheses (see examples in the next step).
Cell Length — When setting the character length, you will use “VARCHAR” (flexible character count, which means you can enter from one to a limited number of VARCHAR characters) or “CHAR” (cannot enter more or less than the specified number of characters; for example, CHAR(1) allows only 1 character, CHAR(3) requires 3 characters, etc.).
Date — If you want to add dates to the table, use the “DATE” command to have the contents of the column formatted with the date. Dates must be entered in the format YYYY-MM-DD (year-month-day).

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/49/258108-12.jpg/v4-728px-258108-12.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/4/49/258108-12.jpg/v4-728px-258108-12.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Create table elements. Before entering data into the table, you need to create the table structure by typing the following command and pressing ↵ Enter :

 create table name ( cpumn1 varchar ( 20 ), cpumn2 varchar ( 30 ), cpumn3 char ( 1 ), cpumn4 date );

For example, to create a “Pets” table with 2 VARCHAR columns, 1 CHAR column, and 1 Date column, you would write the following command:

 create table Pets ( Name varchar ( 20 ), Breed varchar ( 30 ), Sex char ( 1 ), DOB date );

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9c/258108-13.jpg/v4-728px-258108-13.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/9/9c/258108-13.jpg/v4-728px-258108-13.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,” bigHeight”:545,”licensing”:”<div class=”mw-parser-output”></div>”}

Add rows to the table. Using the “insert” command, you can enter the database’s information line by line:

 insert into name values ( 'cpumn1 value' , 'cpumn2 value' , 'cpumn3 value' , 'cpumn4 value' );

With the “Pets” table example used earlier, your line of information is entered as follows:
```
 insert into Pets values ( 'Fido' , 'Husky' , 'M' , '2017-04-12' );
```
You can enter the content of the column as NULL if it is an empty column.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/7b/258108-14.jpg/v4-728px-258108-14.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/7/7b/258108-14.jpg/v4-728px-258108-14.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Add remaining content (if possible). If your database is relatively small, you can add the rest of the content line by line using the “insert” command. If you decide to do this, skip to the next step.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/da/258108-15.jpg/v4-728px-258108-15.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/d/da/258108-15.jpg/v4-728px-258108-15.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,” bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Upload text files (if needed). If you have a database that requires multiple lines of information and cannot be entered all by hand, you can add a text file containing the data using the following command: ^{[1] X Research Source}

 load data local infile '/path/name.txt' into table name lines terminated by 'rn' ;

With the “Pets” example, you can write the following command:

 load data local infile 'C:/Users/name/Desktop/pets.txt' into table Pets lines terminated by 'rn' ;

On a Mac, you need to use the “lines terminated by” command with 'r' instead of 'rn' .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/71/258108-16.jpg/v4-728px-258108-16.jpg”,”bigUrl”:”https:// www.wikihow.com/images/thumb/7/71/258108-16.jpg/v4-728px-258108-16.jpg”,”smallWidth”:460,”smallHeight”:344,”bigWidth”:728,” bigHeight”:544,”licensing”:”<div class=”mw-parser-output”></div>”}

See table. Enter the command show databases; , and then select the database by typing select * from name; , with “name” being the name of the database. For example, if using the “Pet Records” database, you would enter the following command:

 show databases ; select * from Pet_Records ;

Advice

Here are some commonly used data types:
- CHAR ( length ) – fixed length character string
- VARCHAR ( length_length ) – character string whose maximum length is length
- TEXT – flexible character string with a maximum length of no more than 64KB
- INT ( length ) – 32-bit integer with a maximum number of digits of length (for negative numbers, the sign ‘-‘ counts as a ‘digit’)
- DECIMAL ( length , decimal ) – Decimal number of characters displayed by length ; the decimal part specifies the maximum number of digits after the comma
- DATE – Date value (year, month, day)
- TIME – Time value (hour, minute, second)
- ENUM (” value1 “, ” value2 “, ….) – List of count values
Here are some optional parameters:
- NOT NULL – A value must be entered. This field cannot be left blank.
- DEFAULT default_value – the default value that will be entered when you do not enter another value.
- UNSIGNED – With numeric fields you need to make sure the numbers are not negative
- AUTO_INCREMENT – The value will be automatically incremented every time a new line is added to the data table.

Warning

If the MySQL server is down when you try to log in to the command line program “mysql”, you cannot proceed further.
When writing code, you need to make sure the lines are spelled correctly and with spaces before pressing the Enter key.

Steps

Open the MySQL command line program

Create a database

Create Table

Advice

Warning

Related Posts