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.
| mysql> SHOW DATABASES; |
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.
| mysql> USE search |
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 :
| mysql>DESCRIBE Emp; |
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 :
| DROP USER user_name; |