To Install :
yum install mysql
To Create the Database File:
mysql> create database sapna;
Query OK, 1 row affected (0.00 sec)
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>
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)
-> (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)
-> (msgID, name, email, msgFrom, msgDate, msgBody) values
-> (NULL, 'Test User1', 'testuser@test.com','
-> '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)
----------------+-------------
| 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