MyLO-Lecture 06_02_MySQL.pdf - DATABASE - MYSQL Soonja Yeom...

DATABASE - MYSQL
Soonja Yeom

MVC
<code/>
HTML5 / CSS/ Bootstrap
JavaScript, jQuery, AJAX
INTERNET
World Wide Web (WWW)
Protocol, HTTP Message
Web Security
PHP
Database
MySQL
Framework
MVC, Laravel
DOM

SQL FOR DATA RETRIEVAL
• SQL allows a user or an application program to
retrieve stored data from the database and to
manipulate it
• SQL operates on one or more tables at a time,
not on one record at a time
• No loops

RELATIONAL
ALGEBRA

RELATIONAL ALGEBRA (RA) OPERATIONS
• Operations in the Relational Model
• Theory behind operations is formally defined (in mathematics)
• Relational operators operate on operands (either a simple relation or a pair of relations)
to produce a result that is a relation
• The result relation can be used as an operand in later operations

RA OPERATORS –
CARTESIAN PRODUCT AND JOIN
• Cartesian product joins each row of the
first table to every row of the second
table
• There are several types of joins
• EQUIJOIN
• INNERJOIN
• OUTERJOIN
• etc
X è
Cartesian Product
Join
*

RA OPERATORS – SELECT AND PROJECT
• Selecting row(s) from a table
• Selecting column(s) from a table
Select
Project

PROJECTIONS
• A projection is a subset of the columns in a table used when only some
columns are required or being updated
SELECT `firstname`, `lastname`
FROM `customer`

SQL FOR RETRIEVING : PROJECTIONS
• Retrieve data
• projections (selected columns)
• display all columns and rows
• display specified columns
SELECT * FROM table_name ;
SELECT column_name1 , column_name2 ,… FROM table_name ;

SELECTIONS
• A selection is a subset of the rows in a table
• typically, those rows that match some selection criteria
SELECT * FROM `customer` WHERE
`custID` = 995501

SQL FOR RETRIEVING : SELECTIONS
• retrieve data
• selections (selected rows(record) )
• display specific rows with all columns
• display specific rows with specific columns
• condition examples
SELECT * FROM table WHERE condition ;
SELECT column_name1 , column_name2 , … FROM table_name
WHERE condition ;
column_name="value"
column_name<"value"
column_name>="value"

SIMPLIFIED
MAPPING OF
BASIC SQL QUERY
TO RA
• FROM <table list>
• Joins the tables
• WHERE <boolean expression>
• Selects the rows
• SELECT <column list>
• Projects the columns

SELECT -
NOTES
• Each statement contains one or more clauses
• Examples:
• SELECT clause
• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• ORDER BY clause

SELECT -
NOTES
• Clauses are not evaluated in the order in which they
are written
• Standard order of evaluation:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

SINGLE TABLE QUERY
– EXAMPLE USING * AS TARGET LIST
• Select all columns from all rows
• SELECT specifies which columns
• * indicates all columns
• FROM specifies which table
• There is no WHERE clause,
so all the rows in the table are selected
SELECT * FROM Cat;

SINGLE TABLE QUERY
– EXAMPLE USING COLUMN LIST
• Select two columns from all rows
• Name, Spayed specifies the columns
to be displayed (the column list)
• There is no WHERE clause,
so all the rows in the table are selected
SELECT Name, Spayed
FROM Cat;