mysql ii continuation of previous dbms

Lesson VIII: DBMS

Joins and Views

DDL and DML Languages

• Data Definition Language deals with database schemas and descriptions of how data should be structured in the database,

• Data Modification Language deals with data manipulation.

DDL Statements

• CREATE - to create objects in the database• ALTER - alters the structure of the database• DROP - delete objects from the database• TRUNCATE - remove all records from a table,

including all spaces allocated for the records are removed

• COMMENT - add comments to the data dictionary• RENAME - rename an object

DML Statements

• SELECT - retrieve data from the a database• INSERT - insert data into a table• UPDATE - updates existing data within a table• DELETE - deletes all records from a table, the space

for the records remain• MERGE - UPSERT operation (insert or update)• CALL - call a PL/SQL or Java subprogram• EXPLAIN PLAN - explain access path to data• LOCK TABLE - control concurrency

Joins

• A join works through the use of keys, the primary key and the foreign key.

• Our example has the supplier table that contains a supplier_id column that’s configured as a primary key.

• We can use the SELECT statement to extract the corresponding information from the tables.

Suppliers Table

Products Table

Creating Cross joins

• We join two tables by combining rows. The most basic type of join is what we call a cross-join.

• The cross-join simply links one row from one table to every row of the second table. It’s not really used for real life situations.

• SELECT column_names FROM table1, table2;

Cross-Joins

Making Equi-Joins

• Equi-Joins (Inner Joins) join two rows from two or more tables through a specific comparison of two columns from their corresponding tables.

• SELECT column_names FROM table1, table2 WHERE (table1.column = table2.column);

Making Equi-Joins

• For example, to extract the product name and supplier name for each row in our product we say:

• SELECT prod_name, supplier_name, supplier_address FROM product, suppliers WHERE (product.supplier_id = suppliers.supplier_id);

Making Equi-Joins

Notes

• Note that we have to use the fully qualified name since both contain supplier_id.

• The fully qualified column name is defined by the following syntax:– <table_name>.column_name

Creating a Left or Right Join

• You can also join tables using the LEFT JOIN or the RIGHT JOIN statements. Using the LEFT JOIN joins the tables before the WHERE clause is used.

• SELECT column names FROM table1 LEFT JOIN table2 ON (table1.column = table2.column);

Creating a Left Join

Creating a Left Join

• The difference with LEFT JOIN is it will list first table rows that do not have matches on the second table.

• An example would be, if a product from our first table is mapped to a non existent supplier, when we run SELECT the row will still be displayed except the a NULL value will be placed.

Creating Right Joins

• Creating a RIGHT JOIN is the same with creating a LEFT JOIN except that a right join displays all the tables regardless of whether they have a matching column value or not.

Creating Right Joins

Creating Joins using WHERE and USING

• We can use the WHERE clause when we want to narrow down our results such as searching only for products created by Microsoft.

Creating Joins using WHERE and USING

Creating Joins using WHERE and USING

• Utilizing the USING clause prevents us from using fully qualified names that simplifies the usage of JOIN.

• So to perform the same commands from the previous slides using the values of product.supplier_id and supplier.supplier_id we say:

SELECT prod_name, supplier_name, supplier_address FROM product LEFT JOIN suppliers USING (supplier_id) WHERE supplier_name='Microsoft';

Creating Joins using WHERE and USING

Views

• MySQL views are basically a way to group a set of SELECT statements into re-useable tables that can easily be referenced without re-invoking the view.

• Usually JOINS are used in conjunction with the CREATE VIEW command.

Views

• To create a view in MySQL simply use the CREATE VIEW statement where the syntax is:

CREATE VIEW tablename AS select statement

• A basic view is composed of data from a single table. We might have a normal SELECT statement that we can use to format data in a certain way.

Views

Views

The command to create the view from the previous slide is:

CREATE VIEW suppformat AS SELECT CONCAT(UPPER(supplier_name), ' ', supplier_address) FROM suppliers;

ViewsWhen we want to create a table based on that SELECT statement without reinvoking the statement, we can create a view.

Creating Views

Creating Vies

• Remember how to create a Join? Remember that previously we’ve created a join between two tables?

• This allowed us to display values from both the supplier and product table.

Creating Views

• We can easily create a view based on the join that we’ve just created. To create this join, simply input the following command:

CREATE VIEW prodsupp AS SELECT prod_code, prod_name, supplier_name, supplier_addressFROM product, suppliers WHERE (product.supplier_id = suppliers.supplier_id);

Creating Views

Creating ViewsWe can easily be able to filter our data from our data the same way we do with any table.

Sources

• http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views

• http://www.techotopia.com/index.php/An_Introduction_to_MySQL_Views