Course Hero Logo

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

Course Hero uses AI to attempt to automatically extract content from documents to surface to you and others so you can study better, e.g., in search results, to enrich docs, and more. This preview shows page 1 - 17 out of 60 pages.

DATABASE - MYSQLSoonja Yeom
MVC<code/>HTML5 / CSS/ BootstrapJavaScript, jQuery, AJAXINTERNETWorld Wide Web (WWW)Protocol, HTTP MessageWeb SecurityPHPDatabaseMySQLFrameworkMVC, LaravelDOM
SQL FOR DATA RETRIEVALSQL allows a user or an application program toretrieve stored data from the database and tomanipulate itSQL operates on one or more tables at a time,not on one record at a timeNo loops
RELATIONALALGEBRA
RELATIONAL ALGEBRA (RA) OPERATIONSOperations in the Relational ModelTheory 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 relationThe result relation can be used as an operand in later operations
RA OPERATORS –CARTESIAN PRODUCT AND JOINCartesian product joins each row of thefirst table to every row of the secondtableThere are several types of joinsEQUIJOININNERJOINOUTERJOINetcXèCartesian ProductJoin*
RA OPERATORS – SELECT AND PROJECTSelecting row(s) from a tableSelecting column(s) from a tableSelectProject
PROJECTIONSAprojectionis a subset of the columns in a table used when only somecolumns are required or being updatedSELECT `firstname`, `lastname`FROM `customer`
SQL FOR RETRIEVING : PROJECTIONSRetrieve dataprojections (selected columns)display all columns and rowsdisplay specified columnsSELECT * FROMtable_name;SELECTcolumn_name1,column_name2,… FROMtable_name;
SELECTIONSAselectionis a subset of the rows in a tabletypically, those rows that match some selection criteriaSELECT * FROM `customer` WHERE`custID` = 995501
SQL FOR RETRIEVING : SELECTIONSretrieve dataselections(selected rows(record))display specific rows with all columnsdisplay specific rows with specific columnscondition examplesSELECT * FROM tableWHEREcondition;SELECTcolumn_name1,column_name2, … FROM table_nameWHEREcondition;column_name="value"column_name<"value"column_name>="value"
SIMPLIFIEDMAPPING OFBASIC SQL QUERYTO RAFROM <table list>Joins the tablesWHERE <boolean expression>Selects the rowsSELECT <column list>Projects the columns
SELECT -NOTESEach statement contains one or more clausesExamples:SELECT clauseFROM clauseWHERE clauseGROUP BY clauseHAVING clauseORDER BY clause
SELECT -NOTESClauses are not evaluated in the order in which theyare writtenStandard order of evaluation:1.FROM clause2.WHERE clause3.GROUP BY clause4.HAVING clause5.SELECT clause6.ORDER BY clause
SINGLE TABLE QUERY– EXAMPLE USING * AS TARGET LISTSelect all columns from all rowsSELECT specifies which columns* indicates all columnsFROM specifies which tableThere is no WHERE clause,so all the rows in the table are selectedSELECT * FROM Cat;
SINGLE TABLE QUERY– EXAMPLE USING COLUMN LISTSelect two columns from all rowsName, Spayed specifies the columnsto be displayed (the column list)There is no WHERE clause,so all the rows in the table are selectedSELECT Name, SpayedFROM Cat;

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 60 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Fall
Professor
NoProfessor
Tags
MYSQL, Relational model, relation

Newly uploaded documents

Show More

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture