So far we have been reading data from existing database. Its time to start how the data came into the tables.
INSERT
Insert, as the name indicates adds new records to a table. The most common and simple syntax is
INSERT INTO <table_names>
[(col1[,col2 [,col3..]])]
VALUES
(value1[,value2 [,value3..]])
[,(value1[,value2 [,value3..]])]
;
Lets looks a real example.
mysql> insert into students
(name, class, date_of_joining) values
("Prior", "8", now());
Query OK, 1 row affected (0.06 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 5 |
+------------------+
1 row in set (0.05 sec)
mysql> select * from students where id = 5;
OR
mysql> select * from students order by id desc limit 1;
+----+-------+-------+-----------+---------------------+
| id | name | class | parent_id | date_of_joining |
+----+-------+-------+-----------+---------------------+
| 5 | Prior | 8 | NULL | 2013-01-27 21:48:20 |
+----+-------+-------+-----------+---------------------+
1 row in set (0.00 sec)
So lets analyse it. First line says, insert into students so we are selecting the table. Now we specify the columns to which we are going to update the data. Then after the VALUES keyword, we specify all the column values. You can insert multiple rows also using a single insert query.
Note : You might have noticed now() being used for date_of_joining. It returns the current timestamp.
In the query we have not passed the id, and the MySQL has auto generated the value for id based on the current max id in the table. So to retrieve the that id, we use the special function last_insert_id().
Note: The function is not related to a table but to a connection, ie irrespective of the table and database, it returns the last id it has created.
Third query is quite obvious, verifying the data.
UPDATE
Now, Prior got promoted to 9th grade. So basically we have to the class for him.
mysql> update students
set class="9", date_of_joining=now()
where id = 5;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students where id = 5;
+----+-------+-------+-----------+---------------------+
| id | name | class | parent_id | date_of_joining |
+----+-------+-------+-----------+---------------------+
| 5 | Prior | 9 | NULL | 2013-01-27 21:48:20 |
+----+-------+-------+-----------+---------------------+
1 row in set (0.00 sec)
The UPDATE query is similar to SELECT query as it has table selection(can select multiple table using index) and has a where clause. An update cannot have GROUP by clause.
So here again I am breaking the query to 3 parts. UPDATE students, select the table to apply the update statement. Then comes the SET keyword, followed by columns to be updated. A comma separated list of "columns=value" can be given. Followed by optional WHERE clause.
You can use an update on multiple tables as well.
mysql> update students as s
left join parents as p on p.name = "Smith"
set class="9", date_of_joining=now(), s.parent_id = p.id
where s.id = 5;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Hope you can understand this query. We are updating the parent details of Prior with Smith.
Before running an update query, its always good to run a select query to identify the updating rows. Its simple to do.
Step 1: Replace "update" with "select * from"
Step 2: Remove text from "set" till where.
So the above query will look like
mysql> select * from students as s
left join parents as p on p.name = "Smith"
where s.id = 5;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Its even better, if you do an explain also before running the select query also.
DELETE
On any given day, I would say no deletion of data from your database. If you dont need the data, add a column status/active/enabled(or whatever u like) and set the value accordingly, effectively using only an update query.
For sake of information, deletion can be done as:
Prior has completed the course and leaving school now. So, we would like to remove his information.
mysql> delete from students where id = 5;
Query OK, 1 row affected (0.01 sec)
mysql> select * from students where id = 5;
Empty set (0.00 sec)
Its exactly same as SELECT, except that it cannot use GROUP BY.
We can also delete from only specific tables when you are using join.
mysql> delete s.*
from students as s
INNER join parents as p on s.parent_id = p.id
where s.id = 5;
This will select records from both students and parents but will delete only from s.*(students table).By default all tables will be cleared.
Right now we have seen only the basic INSERT, UPDATE and DELETE queries. More complex things are yet to come and we will explore it later.
No comments:
Post a Comment