This wikiHow teaches you how to create a database using MySQL. To create a database, you need to open the "mysql" command line interface and enter database commands while the server is running.
Step
Part 1 of 3: Opening the MySQL Command Line
Step 1. Make sure the MySQL server is connected
If serverMySQL is not on the network, you cannot create a database.
You can check the server status by opening MySQL Workbench, selecting the server, and looking at the "Server Status" indicator on the "Administration - Server Status" tab
Step 2. Copy the address (path) of the installation folder
The address will depend on the operating system used (eg Windows or Mac):
- Windows - Copy C:/Program Files/MySQL/MySQL Workbench 8.0 CE/ and make sure you replace the last folder name with the current MySQL folder name.
- Mac - Copy /usr/local/mysql-8.0.13-osx10.13-x86_64/ and make sure you replace the last folder name with the current MySQL folder name.
Step 3. Open a computer command line program
On a Windows computer, use the Command Prompt. Meanwhile, Mac computer users can use Terminal.
Step 4. Change the entry to the MySQL install folder directory
Type cd and insert a space, paste the address of the installation folder, and press Enter. For example, on a Windows computer you could type the following entry:
cd C:\Program Files\MySQL\MySQL Workbench 8.0 CE
Step 5. Open the MySQL login command
For example, to open the login prompt for the username "my", type the following entry and press Enter key:
mysql -u me -p
Step 6. Enter the account password
Type in your MySQL user account password, then press Enter. You will be logged into the account and the command line application will be connected with the MySQL commands.
- You can see the "MySQL>" marker in the command line application window. From this stage, any commands you enter will be processed via the MySQL command line application.
- Understand how to enter MySQL commands. MySQL commands must be entered using a semicolon (;) right after the last part of the command. You can also enter a command, type a semicolon, and press Enter again.
Part 2 of 3: Creating a Database
Step 1. Create a database file
You can create one by typing the command " create database " create database, adding the database name and inserting a semicolon, and pressing Enter. For a database named "Pet Data", for example, enter the following command:
create a database of Pet_Data_Animals;
- Database names cannot contain spaces. If you want to include spaces in your name, you need to use an underscore (eg "My Best Friend" becomes "My Best Friend").
- Every MySQL command must end with a semicolon. If you forget the first semicolon, you can type it next to “ … ” is displayed, then press Enter key again.
Step 2. Show currently saved database
You can display a list of saved databases by typing the following command and pressing Enter:
show databases;
Step 3. Select a database
You can select a database from the list by typing the use name command, with "name" as the database name. For example, for the "Pet Data" database, type the following command and press Enter:
use Data_Animal_Pet;
Step 4. Wait for the confirmation message to appear
Once you see the phrase " Database changed " under the last command entered, you can move on to database content creation.
Part 3 of 3: Creating Tables
Step 1. Understand the various table commands
There are a few key aspects to tables that you need to know about before creating a table:
- Title - The table title is added right after the " create table " command and must follow the same rules as the database name (eg no spaces).
- Column headers - You can specify the column headers by typing the header names in parentheses (see next step example).
- Square length - When specifying the length of the box, you can use " VARCHAR " (a variable character that allows you to type between one and the maximum number of characters " VARCHAR ") or " CHAR " (requires no more and no less than the specified number of characters; as for example, “CHAR(1)” requires one character, “CHAR(3)” requires three characters, and so on).
-
Dates - If you want to add dates to the chart, use the " DATE " command to indicate that the column contents need to be formatted as dates. In addition, the date needs to be entered in the year-month-date format (
XXXX-XX-XX
- ).
Step 2. Outline the table
Before entering data into a chart, you need to create a table structure by typing the following command and pressing the Enter key:
create table name (column1 varchar(20), column2 varchar(30), column3 char(1), column4 date);
- For example, to create a table titled "Pets" with two columns “VARCHAR”, one column “CHAR”, and a date column, you could type the following command:
create table Pet_Pet(Name varchar(20), Species varchar(30), Gender char(1), Birth_Date date);
Step 3. Add rows to the table
With the " insert " command, you can enter database information on a line by line basis:
insert into table_name values('column1 value', 'column2 value', 'column3 value', 'column4 value');
-
For the "Pets_Pet" table used earlier, your rows of data should look like this:
insert into Pet_Animal values('Fido', 'Husky', 'J', '2017-04-12');
- You can enter the word NULL as the column content if the column is empty.
Step 4. Enter. If you wish to do so, skip the next step.
Step 5. Upload a text file if necessary
If you have a database with more rows of information that would be a pain if you had to manually type them one by one, you can upload a text file containing the data using the following code:
load data local infile '/path/namaberkas.txt' into table nama_tabel lines terminated by '\r\n';
-
In the "Pet_Animal" table example, you can type the following code or command:
load local data infile 'C:/Users/username/Desktop/pets.txt' into table Pets_Pets lines terminated by '\r\n';
- On Mac computers, you need to use the command " lines terminated by " with '\r' instead of '\r\n'.
Step 6. Review the created table
Enter the show databases; command, then select the database by typing select * from name;, with "name" as the database name. For example, if you are using the "Pet_List" database, type the following command:
show databases; select * from Pet_List;
Tips
-
Here are some of the most commonly used data types:
- “ CHAR ”(length) – This variable has a set length (string) of characters.
- “ VARCHAR ”(length) – This variable has the maximum length of the character string (according to the length variable you entered).
- “ TEXT ” – This variable has a character set with a maximum text length equivalent to 64 kilobytes.
- “ INT ” (length) – This variable is a 32-bit integer with a maximum digit length (a minus sign or “-” is considered a “digit” for negative numbers).
- “ DECIMAL ”(length, decimal) – This variable is a decimal number with the value of length as the total number of characters displayed. Meanwhile, the decimal column indicates the maximum number of numbers that can be displayed after the comma.
- “ DATE ” – This variable contains the date in year-month-date format (####-##-##).
- “ TIME ” – This variable contains the time in hour-minute-second format.
- “ ENUM ”("value1 ", "value2 ", ….) – This variable contains a list of whole numbers or values.
-
Here are some additional parameters you can use:
- “ NOT NULL ” – With this parameter, you must enter a value. The column cannot be emptied.
- “ DEFAULT ” default-value – If no data or value is entered, the default-value will be added to the column automatically.
- “ UNSIGNED ” – In the numeric field, the parameter ensures that the entered number will not be a negative number.
- “ AUTO_INCREMENT ” – With this parameter, the value will be incremented automatically every time you add a new row to the table.
Warning
- If the MySQL server is not running when you try to access the "mysql" command line, you cannot continue this process.
- As with other encodings, make sure the commands you enter are typed with the correct spelling and spacing before you try to enter them.
