Sunday, July 25, 2010

Install Mysql and perform actions like create database, create tables, grant privileges, select particular data from table, insert data into a tables, update data from a table field etc .



To Install :
yum install mysql

 

 

To Create the Database File:

mysql> create database sapna;
Query OK, 1 row affected (0.00 sec)
The command creates the the database, sapna. If successful, you should receive a response like the one shown above.
The command creates the the database, sapna. If successful, you should receive a response like the one shown above.
After the database is created, we must select it to continue with our database building.
mysql> use sapna;
Database changed
mysql>
Now with our database selected, we are ready for the next step.

 

 

To Create a Table:

Next, you need to create a database table to hold your data. The SQL here defines a table. Below is the command and the output from the command. The database created below will be used in a web guestbook application.
mysql> create table guestTbl
-> (msgID int not null primary key auto_increment,
-> name varchar(30),
-> email varchar(30),
-> msgFrom varchar(30),
-> msgDate timestamp(14),
-> msgBody text);

Query OK, 0 rows affected (0.11 sec)
mysql>
In this example, the create command creates a table named guestTbl. The table has six fields described below.
msgID - a unique number assigned to each entry. The field hold an integer value that is automatically incremented each time an entry is added.
name - a 30 character field holding the name of the guest.
email - a 30 character field holding the email address of the guest.
msgFrom - a 30 character field holding the location of the guest.
msgDate - the date and time the entry is added to the database.
msgBody - a text field holding a text message entered by the guest.
Our next step is to add data to the table.

 

To Insert data to the Table:

Now we need to add some sample data to the table, so we can actually run some queries against it.
mysql> insert into guestTbl
-> (msgID, name, email, msgFrom, msgDate, msgBody) values
-> (NULL, 'Test User1', 'testuser@test.com','Smallville USA',now(),
-> 'This is the body for message 1.');

Query OK, 1 row affected (0.27 sec)
mysql>
The above command adds one row to the database. If we repeat this process for three other users, we have enough data to perform a query on the table.

 

 

To Query the Database:

Now we can query our database. To retrieve all the data in the table, perform the following command.


mysql> select * from guestTbl;

+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| msgID | name | email |
msgFrom | msgDate | msgBody |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| 1 | Test User1 | testuser@test.com |
Smallville USA | 20001117154626 | This is the body for message 1. |
| 2 | Test User2 | testuser2@test.com |
Smalltown USA | 20001117155043 | This is the body for message 2. |
| 3 | Test User3 | testuser3@test.com |
Smallcity CA | 20001117155201 | This is the body for message 3. |
| 4 | Test User4 | testuser4@test.com |
LittleTown CO | 20001117155319 | This is the body for message 4. |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
4 rows in set (0.06 sec)
mysql>
First off, you'll notice that if the data does not fit on a line, it is wrapped. All the data entered for the four test users is there. We can get cleaner looking output by selecting a couple of fields.


For example:
mysql> select name,email,msgFrom from guestTbl;
+------------+--------------------+----------------+
| name       | email              | msgFrom        |
+------------+--------------------+----------------+
| Test User1 | testuser@test.com  | Smallville USA |
| Test User2 | testuser2@test.com | Smalltown USA |
| Test User3 | testuser3@test.com | Smallcity CA |
| Test User4 | testuser4@test.com | LittleTown CO |
+------------+--------------------+----------------+

4 rows in set (0.05 sec)

mysql>
To Grant Privileges:
mysql> grant all privileges on neethu.* to sapna@localhost identified by 'sapna';
Query OK, 0 rows affected (0.00 sec)

No comments:

Post a Comment