Docstoc

Chapter 4-MySQL Second Step

Document Sample
Chapter 4-MySQL Second Step Powered By Docstoc
					      Chapter 4

Index, Join and View
    in MySQL 5
                 Understanding Index
Company name



 Indexes speed up data retrieval by             mapping
 interesting column to file-system locations.

 Naturally, DBMS by default, perform entire row-scans
 when attempting to locate data.

 In one table, the system allows only16 columns only
 to be indexed.
                 Understanding Index
Company name



 Syntax of creating index

 CREATE INDEX index_name ON tablename(column);

 Ex:

 CREATE INDEX id1 ON clients(id,name);

 To verify index on existing tables:

 Show index from clients;
                 Join
Company name



 Join is used in MySQL to make relational data
 between different tables and to reduce redundancy.

 In MySQL to create join, you just create a Primary key
 in one table and create one more field in other tables
 which refer to that Primary Key.

 There are 2 types of Join in MySQL:
 1. Equi Join
 2. Natural Join
                LAB 1
Company name

 Create following tables:
                LAB 1
Company name

 Key in following data
                 EQUI Join
Company name



 EQUI Join is a kind of join type which use ‘=‘ to
 compare between tables.

 Ex:
 Select employee.EID, employee.Ename,
 absent.AbsentDate, absent.Reason from
 employee,absent where employee.EID=absent.EID;

 Or Using Alias
 Select t1.EID, t1.Ename, t2.AbsentDate, t2.Reason
 from employee as t1,absent as t2 where
 t1.EID=t2.EID;
               EQUI Join
Company name



 OR

 Select t1.EID as ‘Employee ID’, t1.Ename as
 ‘Employee Name’, t2.AbsentDate, t2.Reason from
 employee as t1,absent as t2 where t1.EID=t2.EID;

 OR

 Select t1.EID ‘Employee ID’, t1.Ename ‘Employee
 Name’, t2.AbsentDate, t2.Reason from employee
 t1,absent t2 where t1.EID=t2.EID;
                Natural Join
Company name



 Natural Join is similar to EQUI Join but the
 comparison is using LEFT or RIGHT Join.

 Ex:
 Select t1.EID, t1.Ename, t2.AbsentDate, t2.Reason
 from (Employee as t1) left join (absent as t2) on
 (t1.EID=t2.EID);

 OR
 Select t1.EID, t1.Ename, t2.AbsentDate, t2.Reason
 from (Employee as t1) right join (absent as t2) on
 (t1.EID=t2.EID);
               Natural Join
Company name



 OR
 Select t1.EID, t1.Ename, t2.AbsentDate, t2.Reason
 from (absent as t2) left join (Employee as t1) on
 (t2.EID=t1.EID);
                View
Company name



 Views allow Database administrator to present sub-
 set of data to the user-community.

 Note:
 1. View must have unique names from tables and
    other views.
 2. View shares the same namespace as tables and
    other views.
 3. Views are dependent upon underlying tables.
                 View
Company name



 Syntax:

 CREATE       VIEW       employee_list(EmployeeID,
 EmployeeName, EmployeeAddress) as select EID,
 Ename,Eaddress from employee;

 Type show tables; to list all views in current databse.
 To run view type select * from employee_list;
                 View
Company name



 Create view with join:

 CREATE View VEmployeeAbsent(EmpID,EmpName,
 EmpabsentDate,EMPReason) as Select t1.EID,
 t1.Ename, t2.AbsentDate, t2.Reason from (Employee
 as t1) left join (absent as t2) on (t1.EID=t2.EID);
                View
Company name



 Alter View:

 CREATE OR REPLACE VIEW employee_list(EmpID) as
 select EID from employee;

 OR

 ALTER VIEW Employee_list(EmpName) as select
 Ename from employee;

 Note: Some views are updateable.

				
DOCUMENT INFO
Shared By:
Stats:
views:72
posted:3/6/2011
language:English
pages:15
Description: This is the Second step of the study PHP with My SQL.