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_nameWHERE 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; |