MySQL Creating and Deleting Database

In this section you will learn how to create and
delete the database in MySQL. MySQL provides the both commands. In this section
you can learn the following things :
- Creation and Selection of database
- Creation of table
- Load the data into the table
Once you enter the command prompts then ready to access
the database. Before creating a database firstly check the currently existing
database in the server by SHOW statement.
SHOW statement shows you a list of databases. This
statement does not show you those database for which you don't have the
privilege for.
Creating And Selecting a Database
mysql>CREATE DATABASE search; |
This statement is used for creating a database by the
name of search. But if the administrator is creating a database for you then you
have to setting up the permission before using it. Else create it yourself.
Creating a database does not mean that it is select for use, you have to select
it explicitly by the USE command.
You have to create the database only once but if you
want to access this database then you have to select it to each time by using
USE statement.
MySQL Creating Table
Creating a database is very easy part but at this point you want the list of
tables by SHOW TABLES statement it shows empty.
mysql> SHOW TABLES;
Empty set (0.00sec) |
The most important part is deciding the structure of
the database means what tables you are required and what type of column you
should have in each table. By the following example you can learn about the
creation of table :
| mysql>create table Emp (fname VARCHAR(20), lname VARCHAR(20),
city VARCHAR(20), ->sex CHAR(1), bod DATE); |
If you want to show the structure list of your table
then use DESCRIBE statement. like :
After creating the table you need to load the data in this table, you can do
this by using the INSERT statement. Following example is helps you to teach the
INSERT statement.
| mysql>INSERT INTO Emp VALUES('Amar', 'Patel', 'Delhi',
'M', '14-02-2004'); |
After inserting the data into the table, now we discuss about retrieving the
data from the table. For retrieving the data we used the SELECT statement. The
general syntax of SELECT statement is :
| SELECT column_names FROM table_name
WHERE condition_to_satisfy |
colum_names means what you want retrieve.
It can be column names or * that means all columns data. table_name
means table name from that you want to show the data But WHERE clause is
optional. It is used where you want to put some condition for retrieving the
data.
mysql> SELECT * FROM Emp;
mysql> SELECT fname,lname FROM Emp;
mysql> SELECT * FROM Emp WHERE fname='Amar'; |
In above table first statement is used to retrieve the
full list of table Emp. But from the second statement you get the only fname and
lname column of whole table. And by the third statement you will get the only
row that match with the fname equals to 'Amar'.
If you need to delete the database then you have to use
the DROP statement. Example -
| mysql> DROP DATABASE search; |
CREATE USER Syntax :
Create user syntax is :
CREATE USER user_name [IDENTIFIED BY[password]
'password']
Create User statement is used to create a new MySQL account. For using this
statement you must have the global CREATE USER privilege or the INSERT privilege
for the mysql database.
MySQL drop User syntax:
The DROP USER statement is used to delete the one or more MySQL accounts.
But you must have the global CREATE USER privilege or DELETE privilege for the mysql database. The
general syntax to drop user is :

|