[mysql] index, view, stored procedure

index

mysql> select * from indextbl where first_name='Mary';
+------------+-----------+------------+
| first_name | last_name | hire_date  |
+------------+-----------+------------+
| Mary       | Sluis     | 1990-01-22 |
+------------+-----------+------------+
1 row in set (0.00 sec)

mysql> create index idx_indextbl_firstname on indextbl(first_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from indextbl where first_name='Mary';
+------------+-----------+------------+
| first_name | last_name | hire_date  |
+------------+-----------+------------+
| Mary       | Sluis     | 1990-01-22 |
+------------+-----------+------------+
1 row in set (0.00 sec)

mysql>

view

mysql> create view uv_membertbl
    -> as
    -> select membername,memberAddress from membertbl;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from uv_membertbl;
+------------+-----------------+
| membername | memberAddress   |
+------------+-----------------+
| DangTangYi | Bucheon, Korea  |
| JuYeon     | Incheon, Korea  |
| JiWoon     | Seoul, Korea    |
| SangGil    | SeongNam, Korea |
+------------+-----------------+
4 rows in set (0.00 sec)

(stored) procedure

mysql> delimiter //
mysql> create procedure myproc()
    -> begin
    ->     select * from membertbl where membername='DangTangYi';
    ->     select * from producttbl where productname='Refregiator';
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call myproc();
+----------+------------+----------------+
| memberid | membername | memberaddress  |
+----------+------------+----------------+
| Dang     | DangTangYi | Bucheon, Korea |
+----------+------------+----------------+
1 row in set (0.00 sec)

+-------------+------+------------+---------+--------+
| productname | cost | makedate   | company | amount |
+-------------+------+------------+---------+--------+
| Refregiator |    5 | 2014-02-01 | DaeWoo  |     22 |
+-------------+------+------------+---------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)