How to Create a Better Database with MySQL

How to Create a Better Database with MySQL
MySQL is a very effective tool to provide data on your website. It can be used to store, organize and retrieve data on your website. To create a MySQL database could be little tricky so here are some tips and tutorial to create a better database with MySQL.

Creating a new MySQL database
MySQL stores information in databases which can hold data and tables with specific data. Creating a new database in MySQL is very easy. You simply need to type the given command
CREATE DATABASE database_name;
If for example you want to create a database named ‘employees’, you can give the following command
CREATE DATABASE employees;

How to be better:
Keep in mind to end each command with a semicolon (;) or else the command won’t execute. However, if you forget to do that, you can use a semicolon at the end of the next command for the execution of the first one.
Although MySQL command line is not case sensitive but the commands are written in uppercase and the usernames, database, tables and texts in lower case to clearly distinguish them.
There should be no space in the name of the database.
Retrieving a stored database
Type on the MySQL command line the following command
SHOW DATABASES;
Your computer screen will show you the list of all the databases available.

Deleting a stored database
You can delete a MySQL database using this command
DROP DATABASE employees;
Editing a database
To edit an already created database select it using the command
USE employees;

A message will be displayed on your screen
Database changed
This will mean that your database is now selected for you to commence with its editing.
Creating a table
You need to give the following command to create a table within the database ‘employees’

CREATE TABLE personal_information (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;
name VARCHAR(30),
address VARCHAR(70),
contact_number INT(10),
joining_date DATE);

This command will accomplish the following things:
Created a table ‘personal information’ within the database ‘employees’
It has 5 columns, viz., id, name, address, contact and joining date.
The INT command means the field can contain only numbers while CHAR command means only characters can be used in that field. And the number in the brackets next to it means how many integers or characters are allowed in that particular field
NOT NULL command means the field cannot be left blank
PRIMARY KEY command makes the id, the key field in the table
AUTO_INCREMENT command automatically increases number of rows
DATE) will require you to put a date in that field in the format yyyy-mm-dd

You can use DESCRIBE personal_information; command to recheck the table’s organization.
Entering data into the table
Use the following command to enter data into the table’s first row

INSERT INTO personal_information (‘name’, ‘address’, contact_number’, ‘joining_date’) VALUES (NULL “Andrew”, “45_West_Avenue_New_Jersey”, “9876543210”, “2011/04/18”);

Similarly, enter the other data using comma (,) after each row’s command and end with a semicolon (;).
Updating table
You can make any changes in your database table very easily. Suppose if any employee has changed his contact number, you can write the following command to update it.
UPDATE ‘personal_profile’
SET
‘contact_number’ = ‘9878685848’
WHERE ‘personal_information’.’name’ = ‘Andrew’;
Adding and deleting a column
You can easily add a column in your existing table. For example, if you want to add a column of email you can give the following command
ALTER TABLE personal_information ADD email VARCHAR(40)AFTER name;

This will put the column ‘email’ after the coloumn ‘name’.

Similarly it is also very easy to delete a column
ALTER TABLE personal_information DROP email;
Deleting a row
You can very well delete a row too. For example, if Andrew left the job and you want to remove him from your table, give the following command
DELETE from personal_information where name = ‘Andrew’;