Step By Step Create a MySQL Database Via Command Line on Windows.

Create a MySQL Database. This guide below will show you how to Create a MySQL Database Via Command Line on Windows. It's easy to create a database, we must know how to enter SQL statements. By creating a database on your computer, you will be able to collect, manage, add, find the data stored on your computer. Why we use MySQL on this tutorial to create a database. Because MySQL software is Open Source. And MySQL is the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation. Also, The MySQL Database Server is very fast, reliable, scalable, and easy to use. MySQL Server works in client/server or embedded systems. And many more reasons why we use MySQL to create a database. Now, let we start our guide.

Database Programming.

• These instructions below are intended for creating a MySQL database on a Windows operating system computer.
• Remember, each command must end with a semicolon ( ;)

1. XAMPP

1. Download and install Xampp server on your computer.
2. Now, open Xampp control panel.
3. Then start MySQL.

   

   XAMPP control panel

4. Now, run Shell

2. Log In to the MySQL server.

1. For the first, we must log in to the MySQL server,
2. To do this, we must enter a statement to the command line with the following command.

• mysql -uroot

3. In this case, we have specified the user is root with u flag.
4. If you have specified a password at your MySQL server, you must add p flag at the end of command line, example.

• mysql -uroot -p****

5. which **** character assign your password.
6. At the other case you want to connect MySQL server to another host, you must add h flag at the end of command line.
7. If you successfully log in to MySQL server, You should see MySQL prompt that looks very similar to this:

• mysql>

3. Create Database.

1. To create a database with the name customers type the following command:

• create database customers;

2. If your database is successfully created, you will see the following message at MySQL prompt.

• Query OK, 1 row affected (0.00 sec)

3. If a database of the same name already exists, then a new database will not be created and you’ll receive this error:

• ERROR 1007 (HY000): Can't create database 'customers'; database exists

4. Show Databases.

1. To view the database you’ve successfully created simply issue the following command:

• show databases;

2. The result will similar to this.



show databases result

5. Use Data Base.

1. The use command is used when you have more than one database on a MySQL server and need to switch between them. Statement : use [DatabaseName];  for example;

• use customers;

2. The result will similar to this.

• Database changed.

6. Create Table.

1. At this sections will show you how to create a table inside customers database.
2. Now we will create a customers table. the table contains data showed on the image below.

   

   table content - create MySQL table

3. Type the following command at MySQL prompt;

• create table customers (customerNumber int(11), customerName varchar(50), phone varchar(50), addressLine1 varchar(50), city varchar(50), state varchar(50), postalCode varchar(15), country varchar(50));

 
4. If the table successfully created, the SQL prompt should return with this result.

• Query OK, 0 rows affected (0.28 sec)

5. Do the same steps to create another table.

7. Desc Table.

1. DESC is short for DESCRIBE, this function shows the table structure.
2. the statement should see like this; ; desc table name;
3. Example ;

• desc customers;

4. SQL prompt should return with this result.
• +------------------+-------------+------+-----+---------+-------+
• | Field                    | Type            | Null   | Key  | Default   | Extra   |
• +------------------+-------------+------+-----+---------+-------+
• | customerNumber | int(11)           | YES  |          | NULL    |           |
• | customerName    | varchar(50)   | YES  |          | NULL    |           |
• | phone                  | varchar(50)   | YES  |          | NULL    |           |
• | addressLine1       | varchar(50)   | YES  |          | NULL    |           |
• | city                      | varchar(50)   | YES  |          | NULL    |           |
• | state                    | varchar(50)   | YES  |          | NULL    |           |
• | postalCode          | varchar(15)   | YES  |          | NULL   |           |
• | country                | varchar(50)   | YES  |          | NULL    |           |
• +------------------+-------------+------+-----+---------+-------+
• 8 rows in set (0.03 sec)

8. Alter Table.

1. Alter table used for change the table structure, there are many options to use alter command.
2. Now we will show add a primary key at the customers table.
3. Now add a primary key at the customerNumber field.
4. type the following command at MySQL Prompt;

• alter table customers add primary key (customerNumber);

5. SQL prompt should return with this result.

• Query OK, 0 rows affected (0.63 sec)

• Records: 0  Duplicates: 0  Warnings: 0

6. Now, back to the desc table command.
7. SQL prompt should return with this result.



alter table - create a primary key

8. You should see a PRI under the key column.

9. Input Data Into Table.

1. This sections will explain to you how to input data to the table.
2. The statements should look like this;

• insert into table name (field1, field2, field3, ....fieldx) values (values1, values2, values3... valuesx);

3. example.

• insert into customers (customerNumber, customerName, phone,  addressLine1, city, state, postalCode, country) values (1, 'Andy', '+62438999',  'royal street', 'kudus', 'central java', '59388', 'Indonesia'); 

4. SQL prompt should return with this result.

• Query OK, 1 row affected (0.04 sec)

5. Remember, to enter data must correspond to the data type, for example.
• data type: Varchar = data must be enclosed in quotation marks.
• data type: Int (numeric) = Data can be directly written.
• data type : date = type with the following format (yyyy-mm-dd)

10. Show Table Data.

1. How to show data which is inserted into the table.
2. the statement is ;
• select * from table;
3. example ;
•  select * from customers;
4. SQL prompt should return with this result.



show table data - MySQL table

5. How to show specified table data
6. the statement is ;

• select field1, field2...filedx from table name;

7. example

• select customerName, city, country from customers;

8. SQL prompt should return with this result.



show specified table data

9. Finish

Share this post