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


Advertisement

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

Create MySQL Database. This guide below will show you how to Create a MySQL Database Via Command Line on Windows. It's easy to create 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 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
    xampp control panel
  4. Now, run Shell

2. Log In to MySQL server.

  1. For the first, we must log in to the MySQL server,
  2. To do this, we must enter a statements to the command line with following command.
    • mysql -uroot

  3. In this case we have specified the user is root with the 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 connect MySQL server to other 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.
  3. show databases result
    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 table inside customers database.
  2. Now we will create customers table. the table content data shown on image below.
    table content - create MySQL table
    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 table successfully created, 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 show 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 customers table.
  3. Now add a primary key at 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.
  8. alter table - create a primary key
    alter table - create a primary key
  9. You should see a PRI under key column.

9. Input Data Into Table.

  1. This sections will explain 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 with 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.
  5. show table data - mysql table
    show table data - mysql table
  6. How to show specified table data
  7. the statement is ;
    • select field1, field2...filedx from table name;
  8. example
    • select customerName, city, country from customers;
  9. SQL prompt should return with this result.
  10. show specified table data
    show specified table data
  11. Finish

Comments