Create database in Windows OS using WampServer

Create database in Windows OS using WampServer

Webmasters create database very often. PHP projects or any other web scripting project requires you to create database for holding user information. MySQL is a very user friendly and preferred language for database interaction in web applications.

I use WAMP to setup my local projects and I did create database several times. Using phpmyadmin is very easy if you know the basics. I have already mentioned in my previous article about setting up PHP projects in local computers.

This article will just focus on steps to create database using phpmyadmin. We will understand both the methods that can be applied to create database –

  1. Writing SQL commands
  2. Using the phpmyadmin interface

Let’s jump into it without any delay!

Create database using the SQL commands – for the professionals in programming

Good news for all who like to rest upon their laurels. If you are looking to create your own database with custom tables and constraints; WAMP has the option for you.

Database creation syntax according to MySQL 5.7 is as follows –

CREATE { DATABASE | SCHEMA } {IF NOT EXISTS} db_name
 {create specifications}

Here in the above syntax to create database, create specifications are optional attributes to be set like COLLATE and CHARACTER SET information. For more information on this visit this link.

Where to write our SQL code? Just click on the SQL icon on the top left corner of the phpmyadmin page. This will open the query editing and compiling window.

The above SQL command has created your database, but it does not have any tables yet. So, we create tables to hold our data.

Similarly, we create tables just as we did to create database previously. This time, we select the SQL editing window form within the database dedicated webpage. This page will open up when you select the newly created database from the list of databases in the left pane.

Once you create database and select it from the list, the right pane automatically opens up a dedicated page. This page shows all tables currently present in the database. On top of the right pane, there are a number of options like structure, search, query, etc. One of them is the option for opening the SQL editor. Click on it and start writing your SQL commands.

Creating a table after you create database

Creating tables are also simple. In the SQL editor, simply type in the SQL syntax for creating tables –

CREATE TABLE {IF NOT EXISTS} table_name
{
Column_name variable_name constraint_type
…
}

Similarly you can update your table data using the SQL UPDATE statement. Also you can INSERT new rows and DELETE rows from your newly created table. All of this depends on how good you are at your SQL commands.

Dropping a table or dropping a database

Soon after you create database and start working with it, you might need to delete the database completely or delete and entire table.

Although this is the strangest of the suggestions, but at times you might need it. Normally you never drop a database or table; it’s a matter of huge data backup all gone.

create-database-in-windows-using-phpmyadmin-and-wamp-browse-data

However, I feel that I must make you understand all possible options.

So, this is nothing different. Whether you want to drop a database (not recommended at all) or a table; just open the respective SQL window and type in the command. Here’s the syntax for dropping database or table –

Dropping a table

Dropping a database

Dropping a table sometimes might not work for you. The table structure might be important.

So, if in case you need to keep the table and but empty it; just use TRUNCATE instead of DROP. Truncate statement however, requires the DROP privilege to be enabled for the DB user. What it does is it drops the table first and then creates it again. DROP statement completely deletes the table.

How to alter database or modify table structure after you create database?

Do not confuse this with changing data or values inside the database tables. This is dedicated purely to the structural changes.

This is very simple. Just open the SQL window and write down the syntax for altering a database or modifying the table structure.

Modifying table structure is often useful in following cases –

  1. Your column datatype length needs to be updated.
  2. You entered the wrong type of data for a constraint
  3. The number of constraints is more or less than what was planned.

There may be many more of such situations where the table must be altered in structure.

Take syntax help from altering database documentation and altering table structure documentation.

To create database using the UI

Using the UI is very simple. No need for SQL knowledge at all.

create-database-in-windows-using-phpmyadmin-and-wamp-export-table

To create database, just follow these steps –

  1. Open phpmyadmin
  2. You’ll see an empty field (text box) with a label over it saying “Create Database”
  3. That’s it. Name your database and click on Create.

Once you have created the database, just select it by its name from the left panel dropdown (as mentioned earlier).

Now you can create tables and do everything else that you can probably think of wit your database.

Creating table is easy. Just follow the below steps after you create database –

  1. Select the database
  2. You’ll see two text fields. One for name and the other for the number of columns. Enter both and click on Go.
  3. That’s it. It’s done. Next you’ll be asked to enter the column names, data types, and other details like attributes, length, collation, auto-increment and primary / foreign key configurations.
  4. After this your table is ready to feed in new data.

Once you have created the table, you can now use the top menu options to edit structure of the table, run custom queries, and insert data, search for data and much more.

You can also view the existing table data values from the Browse option.

For DROP table and database, you have the option right at the end of all the top menu options. Just click on it and you’ll be asked to confirm. Once confirmed, your table will be dropped.

That’s all on steps to create database and use them locally. In fact, the methods I have detailed here are not only for local servers. Online hosting servers also offer phpmyadmin to access database. This tutorial will help you there too.

Do let me know about your thoughts and suggestions.

  • Adolf aravind

    thanks dude it greate