mysql slides
TRANSCRIPT
![Page 1: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/1.jpg)
Chapter 7/1 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
ADVANCED MYSQL
![Page 2: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/2.jpg)
Chapter 7/2 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Review Relational Model Terminology Relation is a two-dimensional table Attributes are single valued Each attribute belongs to a domain
– A domain is a physical and logical description of permittable values
No two rows are identical Order is unimportant The row is called a tuple
![Page 3: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/3.jpg)
Chapter 7/3 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Relational Algebra
Relational algebra defines a set of operators that may work on relations.
Recall that relations are simply data sets. As such, relational algebra deals with set theory.
The operators in relational algebra are very similar to traditional algebra except that they apply to sets.
![Page 4: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/4.jpg)
Chapter 7/4 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Relational Algebra Operators
Relational algebra provides several operators:– Union– Difference– Intersection– Product– Projection– Selection– Join
![Page 5: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/5.jpg)
Chapter 7/5 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Union Operator
The union operator adds tuples from one relation to another relation
A union operation will result in combined relation
This is similar to the logical operator ‘OR’
![Page 6: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/6.jpg)
Chapter 7/6 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Union Operator
JUNIOR and HONOR-STUDENT relations and their union:
(a)Example of JUNIOR relation
(b)Example HONOR-STUDENT relation
(c) Union of JUNIOR and HONOR-STUDENT relations
![Page 7: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/7.jpg)
Chapter 7/7 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Difference Operator
The difference operator produces a third relation that contains the tuples that appear in the first relation, but not the second
This is similar to a subtraction
![Page 8: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/8.jpg)
Chapter 7/8 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Difference Operator
JUNIOR relation
HONOR-STUDENT relation
JUNIOR minus HONOR-STUDENT relation
![Page 9: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/9.jpg)
Chapter 7/9 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Intersection Operator
An intersection operation will produce a third relation that contains the tuples that are common to the relations involved.
This is similar to the logical operator ‘AND’
![Page 10: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/10.jpg)
Chapter 7/10 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Intersection Operator
JUNIOR relation
HONOR-STUDENT relation
Intersection of JUNIOR and HONOR-STUDENT relations
![Page 11: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/11.jpg)
Chapter 7/11 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Product Operator
A product operator is a concatenation of every tuple in one relation with every tuple in a second relation
The resulting relation will have n x m tuples, where…
n = the number of tuples in the first relation andm = the number of tuples in the second relation
This is similar to multiplication
![Page 12: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/12.jpg)
Chapter 7/12 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Projection Operator
A projection operation produces a second relation that is a subset of the first.
The subset is in terms of columns, not tuples
The resulting relation will contain a limited number of columns. However, every tuple will be listed.
![Page 13: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/13.jpg)
Chapter 7/13 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Selection Operator
The selection operator is similar to the projection operator. It produces a second relation that is a subset of the first.
However, the selection operator produces a subset of tuples, not columns.
The resulting relation contains all columns, but only contains a portion of the tuples.
![Page 14: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/14.jpg)
Chapter 7/14 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Join Operator
The join operator is a combination of the product, selection, and projection operators. There are several variations of the join operator…– Equijoin– Natural join– Outer join
• Left outer join• Right outer join
![Page 15: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/15.jpg)
Chapter 7/15 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Data for Join Examples
SID Name Major GradeLevel
123 Jones History JR
158 Parks Math GR
271 Smith History JR
105 Anderson Management SN
StudentNumber ClassName PositionNumber
123 H350 1
105 BA490 3
123 B490 7
![Page 16: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/16.jpg)
Chapter 7/16 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Join Examples
Equijoin
Natural Join
Left OuterJoin
![Page 17: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/17.jpg)
Chapter 7/17 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Expressing Queries in Relational Algebra
1. What are the names of all students?
STUDENT [Name]
2. What are the student numbers of all students enrolled in a class?
ENROLLMENT [StudentNumber]
![Page 18: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/18.jpg)
Chapter 7/18 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Expressing Queries in Relational Algebra3. What are the student numbers of all
students not enrolled in a class?STUDENT [SID] – ENROLLMENT
[StudentNumber]
4. What are the numbers of students enrolled in the class ‘BD445’?
ENROLLMENT WHERE ClassName = ‘BD445’[StudentNumber]
![Page 19: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/19.jpg)
Chapter 7/19 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Expressing Queries in Relational Algebra
5. What are the names of the students enrolled in class ‘BD445’?
STUDENT JOIN (SID = StudentNumber) ENROLLMENT WHERE ClassName = ‘BD445’[STUDENT.Name]
![Page 20: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/20.jpg)
Chapter 7/20 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Expressing Queries in Relational Algebra
6. What are the names and meeting times of ‘PARKS’ classes?
STUDENT WHERE Name = ‘PARKS’ JOIN (SID=StudentNumber) ENROLLMENT JOIN (ClassName = Name) CLASS
[CLASS.Name, Time]
![Page 21: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/21.jpg)
Chapter 7/21 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Expressing Queries in Relational Algebra7. What are the grade levels and meeting
rooms of all students, including students not enrolled in a class?
STUDENT LEFT OUTER JOIN (SID = StudentNumber) ENROLLMENT JOIN (ClassName = Name) CLASS [GradeLevel, Room]
![Page 22: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/22.jpg)
Chapter 7/22 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Summary of Relational Algebra Operators
![Page 23: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/23.jpg)
Fundamentals, Design, and Implementation, 9/e
Using SQL in Applications
![Page 24: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/24.jpg)
Chapter 7/24 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
View Ridge Gallery
View Ridge Gallery is a small art gallery that has been in business for 30 years
It sells contemporary European and North American fine art
View Ridge has one owner, three salespeople, and two workers
View Ridge owns all of the art that it sells; it holds no items on a consignment basis
![Page 25: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/25.jpg)
Chapter 7/25 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Application Requirements
View Ridge application requirements– Track customers and their artist interests– Record gallery's purchases– Record customers' art purchases– List the artists and works that have
appeared in the gallery– Report how fast an artist's works have
sold and at what margin– Show current inventory in a Web page
![Page 26: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/26.jpg)
Chapter 7/26 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
View Ridge Data Model
![Page 27: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/27.jpg)
Chapter 7/27 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
View Ridge Data Model
Problems: the keys for WORK and TRANSACTION are huge and the key for CUSTOMER is doubtful as many customers may not have an email address
![Page 28: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/28.jpg)
Chapter 7/28 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Surrogate Key Database Design
![Page 29: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/29.jpg)
Chapter 7/29 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Sample Values
![Page 30: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/30.jpg)
Chapter 7/30 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Sample Values
![Page 31: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/31.jpg)
Chapter 7/31 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Sample Values
![Page 32: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/32.jpg)
Chapter 7/32 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Sample Values
![Page 33: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/33.jpg)
Chapter 7/33 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Sample Values
![Page 34: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/34.jpg)
Chapter 7/34 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CHECK CONSTRAINT
CHECK CONSTRAINT defines limits for column values
Two common uses– Specifying a range of allowed values– Specifying an enumerated list
CHECK constraints may be used – To compare the value of one column to another– To specify the format of column values– With subqueries
![Page 35: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/35.jpg)
Chapter 7/35 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SQL Views
SQL view is a virtual table that is constructed from other tables or views
It has no data of its own, but obtains data from tables or other views
SELECT statements are used to define views– A view definition may not include an ORDER BY clause
SQL views are a subset of the external views– They can be used only for external views that involve
one multi-valued path through the schema
![Page 36: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/36.jpg)
Chapter 7/36 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SQL Views
Views may be used to – Hide columns or rows– Show the results of computed columns– Hide complicated SQL statements – Provide a level of indirection between
application programs and tables– Assign different sets of processing
permissions to tables– Assign different sets of triggers
![Page 37: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/37.jpg)
Chapter 7/37 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE VIEW SYNTAX
CREATE [OR REPLACE]
ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
![Page 38: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/38.jpg)
Chapter 7/38 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Example: CREATE VIEW
CREATE VIEW CustomerNameView AS
SELECT Name AS CustomerName
FROM CUSTOMER;
SELECT *
FROM CustomerNameView
ORDER BY CustomerName;
![Page 39: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/39.jpg)
Chapter 7/39 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 40: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/40.jpg)
Chapter 7/40 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 41: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/41.jpg)
Chapter 7/41 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 42: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/42.jpg)
Chapter 7/42 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Updating Views
Views may or may not be updatable Rules for updating views are both
complicated and DBMS-specific
![Page 43: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/43.jpg)
Chapter 7/43 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Updating Views
Guidelines:
![Page 44: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/44.jpg)
Chapter 7/44 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 45: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/45.jpg)
Chapter 7/45 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 46: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/46.jpg)
Chapter 7/46 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 47: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/47.jpg)
Chapter 7/47 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 48: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/48.jpg)
Chapter 7/48 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
ALTER VIEW SYNTAX
ALTER [ALGORITHM = {UNDEFINED |
MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)]
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
![Page 49: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/49.jpg)
Chapter 7/49 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CHECK TABLE
CHECK TABLE view_name: to check the validation of views
![Page 50: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/50.jpg)
Chapter 7/50 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 51: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/51.jpg)
Chapter 7/51 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
DROP VIEW SYNTAX
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
![Page 52: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/52.jpg)
Chapter 7/52 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 53: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/53.jpg)
Chapter 7/53 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Importing and Exporting data
Using LOAD DATA INFILE statement to import data from files
Using SELECT … INTO OUTFILE statement to export data to files
Using mysqldump in command lines
![Page 54: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/54.jpg)
Chapter 7/54 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Importing data from files
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name,...)]
![Page 55: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/55.jpg)
Chapter 7/55 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLES
![Page 56: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/56.jpg)
Chapter 7/56 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXPORTING DATA
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
![Page 57: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/57.jpg)
Chapter 7/57 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLES
![Page 58: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/58.jpg)
Chapter 7/58 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 59: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/59.jpg)
Chapter 7/59 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXPORTING DATA WITH MYSQLDUMP
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases
![Page 60: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/60.jpg)
Chapter 7/60 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 61: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/61.jpg)
Chapter 7/61 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 62: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/62.jpg)
Chapter 7/62 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 63: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/63.jpg)
Chapter 7/63 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
RESTORE DATA
![Page 64: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/64.jpg)
Chapter 7/64 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Embedding SQL In Program Code SQL can be embedded in triggers, stored
procedures, and program code Problem: assigning SQL table columns with
program variables Solution: object-oriented programming, PL/SQL Problem: paradigm mismatch between SQL and
application programming language– SQL statements return sets of rows; an applications work
on one row at a time
Solution: process the SQL results as pseudo-files
![Page 65: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/65.jpg)
Chapter 7/65 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Triggers
A trigger is a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view
Three trigger types: BEFORE, INSTEAD OF, and AFTER– Each type can be declared for Insert, Update, and Delete– Resulting in a total of nine trigger types
Oracle supports all nine trigger types SQL Server supports six trigger types (only for
INSTEAD OF and AFTER triggers)
![Page 66: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/66.jpg)
Chapter 7/66 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE TRIGGERS
CREATE TRIGGER trigger_name
{BEFORE|AFTER}
{INSERT|UPDATE|DELETE}
ON table_name
FOR EACH ROW
trigger_statement
![Page 67: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/67.jpg)
Chapter 7/67 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Firing Triggers
When a trigger is fired, the DBMS supplies– Old and new values for the update– New values for inserts– Old values for deletions
The way the values are supplied depends on the DBMS product
Trigger applications:– Checking validity (Figure 7-14)– Providing default values (Figure 7-15)– Updating views (Figure 7-16)– Enforcing referential integrity actions (Figure 7-17, 7-18)
![Page 68: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/68.jpg)
Chapter 7/68 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLES
![Page 69: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/69.jpg)
Chapter 7/69 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 70: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/70.jpg)
Chapter 7/70 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 71: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/71.jpg)
Chapter 7/71 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 72: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/72.jpg)
Chapter 7/72 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 73: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/73.jpg)
Chapter 7/73 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
DROP TRIGGER
DROP TRIGGER trigger_name
![Page 74: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/74.jpg)
Chapter 7/74 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 75: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/75.jpg)
Chapter 7/75 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
User Variables and Prepared Statements
User varibles Using prepared statements with
mysql
![Page 76: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/76.jpg)
Chapter 7/76 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
User Variable Syntax
SET @var_name = expr [, @var_name = expr] ...
User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value
![Page 77: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/77.jpg)
Chapter 7/77 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLES
![Page 78: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/78.jpg)
Chapter 7/78 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
String operators
![Page 79: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/79.jpg)
Chapter 7/79 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Read a file of maximum_allowed_package bytes (1GB)
![Page 80: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/80.jpg)
Chapter 7/80 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 81: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/81.jpg)
Chapter 7/81 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 82: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/82.jpg)
Chapter 7/82 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Syntax
PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name
![Page 83: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/83.jpg)
Chapter 7/83 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Prepare statement
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hethuctamgiacvuong';
mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a,
@b;
![Page 84: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/84.jpg)
Chapter 7/84 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 85: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/85.jpg)
Chapter 7/85 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 86: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/86.jpg)
Chapter 7/86 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 87: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/87.jpg)
Chapter 7/87 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 88: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/88.jpg)
Chapter 7/88 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Drop Prepare
![Page 89: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/89.jpg)
Chapter 7/89 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
E-PROJECT
Nhom 1: Men (TL), Phu, Trong Nhom 2: Tu, Khai (TL), Quyet, Hien
![Page 90: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/90.jpg)
Chapter 7/90 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Nội dung
Lựa chọn và giới thiệu chủ đề (có khảo sát thực tế)
Thiết kế cơ sở dữ liệu, quan hệ giữa các bảng (quan hệ các bảng được biểu diễn bằng sơ đồ)
Dữ liệu >=50 records Sử dụng View cho các truy vấn trên
nhiều bảng
![Page 91: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/91.jpg)
Chapter 7/91 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Export kết quả ra file txt hoặc xls Sử dụng triggers kiểm soát nhập, xuất dữ
liệu Sử dụng các stored functions, routines Phân quyền người dùng (root,admin,users) Cho phép kết nối database server từ nhiều
ip khác nhau
![Page 92: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/92.jpg)
Chapter 7/92 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Báo cáo
Thứ 7 (3/12/2011) : trình bày trên lớp File export (.txt, .xls),
file .word, .ppt, .sql
![Page 93: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/93.jpg)
Chapter 7/93 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Stored Procedures
A stored procedure is a program that is stored within the database and is compiled when used– In Oracle, it can be written in PL/SQL or Java– In SQL Server, it can be written in TRANSACT-SQL
Stored procedures can receive input parameters and they can return results
Stored procedures can be called from– Programs written in standard languages, e.g., Java, C#– Scripting languages, e.g., JavaScript, VBScript– SQL command prompt, e.g., SQL Plus, Query Analyzer
![Page 94: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/94.jpg)
Chapter 7/94 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Stored Procedure Advantages
Greater security as store procedures are always stored on the database server
Decreased network traffic SQL can be optimized by the DBMS
compiler Code sharing resulting in
– Less work– Standardized processing– Specialization among developers
![Page 95: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/95.jpg)
Chapter 7/95 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Define Procedure Syntax
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
![Page 96: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/96.jpg)
Chapter 7/96 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Define Function Syntax
CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
![Page 97: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/97.jpg)
Chapter 7/97 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Examples
![Page 98: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/98.jpg)
Chapter 7/98 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 99: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/99.jpg)
Chapter 7/99 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 100: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/100.jpg)
Chapter 7/100 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 101: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/101.jpg)
Chapter 7/101 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 102: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/102.jpg)
Chapter 7/102 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 103: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/103.jpg)
Chapter 7/103 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
IN and OUT
![Page 104: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/104.jpg)
Chapter 7/104 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Declare statement
Used for declaring varibles, conditions …
Syntax:
DECLARE var_name data_type
![Page 105: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/105.jpg)
Chapter 7/105 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 106: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/106.jpg)
Chapter 7/106 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Conditions and Handlers
Conditions may arise during stored program execution that require special handling, such as exiting the current program block or continuing execution. Handlers can be defined for general conditions such as warnings or exceptions, or for specific conditions such as a particular error code. Specific conditions can be assigned names and referred to that way in handlers.
![Page 107: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/107.jpg)
Chapter 7/107 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Conditions Declare syntax
DECLARE condition_name CONDITION FOR condition_value condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value
![Page 108: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/108.jpg)
Chapter 7/108 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
EXAMPLE
![Page 109: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/109.jpg)
Chapter 7/109 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Handlers Declare syntax
DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action:
CONTINUE | EXIT | UNDO
condition_value:
mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
![Page 110: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/110.jpg)
Chapter 7/110 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 111: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/111.jpg)
Chapter 7/111 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 112: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/112.jpg)
Chapter 7/112 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CURSORS
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:
1. Asensitive: The server may or may not make a copy of its result table
2. Read only: Not updatable 3. Nonscrollable: Can be traversed
only in one direction and cannot skip rows Cursors must be declared before
declaring handlers and after declaring variables and conditions.
![Page 113: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/113.jpg)
Chapter 7/113 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name FETCH cursor_name INTO var_name
[, var_name] ... CLOSE cursor_name
![Page 114: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/114.jpg)
Chapter 7/114 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 115: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/115.jpg)
Chapter 7/115 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 116: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/116.jpg)
Chapter 7/116 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 117: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/117.jpg)
Chapter 7/117 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Flow control
IF and CASE: conditional testing LOOP, REPEAT, WHILE: loops
![Page 118: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/118.jpg)
Chapter 7/118 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
![Page 119: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/119.jpg)
Chapter 7/119 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 120: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/120.jpg)
Chapter 7/120 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CASE case_value
WHEN when_value
THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
![Page 121: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/121.jpg)
Chapter 7/121 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Attention: Transfer of Control
LEAVE label: transfer to the end of the named construct
ITERATE label: transfer to the beginning of the named construct
Transfer of Control can be used with blocks and loops
![Page 122: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/122.jpg)
Chapter 7/122 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 123: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/123.jpg)
Chapter 7/123 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
[begin_label:] LOOP statement_list END LOOP [end_label]
![Page 124: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/124.jpg)
Chapter 7/124 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 125: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/125.jpg)
Chapter 7/125 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
![Page 126: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/126.jpg)
Chapter 7/126 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
![Page 127: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/127.jpg)
Chapter 7/127 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Drop Procedure, Functions
DROP PROCEDURE proc_name DROP FUNCTION func_name
![Page 128: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/128.jpg)
Chapter 7/128 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Account Management Statements
CREATE user DROP user RENAME user GRANT syntax
![Page 129: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/129.jpg)
Chapter 7/129 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE USER syntax
CREATE USER user_specification [, user_specification] ... user_specification: user [IDENTIFIED BY [PASSWORD] 'password']
![Page 130: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/130.jpg)
Chapter 7/130 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
RENAME USER syntax
RENAME USER old_user TO new_user [, old_user TO new_user] ...
![Page 131: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/131.jpg)
Chapter 7/131 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
DROP USER syntax
DROP USER username
![Page 132: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/132.jpg)
Chapter 7/132 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 133: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/133.jpg)
Chapter 7/133 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
GRANT syntax
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...]
![Page 134: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/134.jpg)
Chapter 7/134 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
![Page 135: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/135.jpg)
Chapter 7/135 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Debugging MYSQL Applications
Interpret error messages Using SHOW WARNINGS and
SHOW ERRORS statements perror
![Page 136: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/136.jpg)
Chapter 7/136 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SHOW WARNINGS statement
![Page 137: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/137.jpg)
Chapter 7/137 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SHOW ERRORS statement
![Page 138: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/138.jpg)
Chapter 7/138 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Using SQL In Application Code
SQL can be embedded in application programs
Several SQL statements need to be executed to populate an external view
The application program causes the statements to be executed and then displays the results of the query in the form’s grid controls
![Page 139: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/139.jpg)
Chapter 7/139 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Using SQL In Application Code (cont.) The application program also processes
and coordinates user actions on a form, including– Populating a drop-down list box– Making the appropriate changes to foreign keys
to create record relationships The particulars by which SQL code is
inserted into applications depend on the language and data-manipulation methodology used
![Page 140: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/140.jpg)
Fundamentals, Design, and Implementation, 9/e
ReviewIntroduction to MySQL
![Page 141: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/141.jpg)
Chapter 7/141 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
More features
Cross-database joins Outer joins API: C/C++, Eiffel, Java, PHP, Perl,
Python, TCL Runs on Windows, UNIX, and Mac High performance
![Page 142: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/142.jpg)
Chapter 7/142 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SQL syntax
CREATE TABLE people (name CHAR(10)) INSERT INTO people VALUES (‘Joe’) SELECT name FROM people WHERE
name like ‘J%’
![Page 143: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/143.jpg)
Chapter 7/143 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SQL commands
SHOW DATABASES SHOW TABLES Data types: INT, REAL, CHAR(l),
VARCHAR(l), TEXT(l), DATE, TIME ALTER TABLE mytable MODIFY
mycolumn TEXT(100) ENUM(‘cat’,’dog’,’rabbit’,’pig’)
![Page 144: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/144.jpg)
Chapter 7/144 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SQL commands
CREATE DATABASE dbname CREATE TABLE tname (id NOT
NULL PRIMARY KEY AUTO_INCREMENT)
CREATE INDEX part_of_name ON customer (name(10))
INSERT INTO tname (c1, …, cn) values (v1, …, vn)
![Page 145: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/145.jpg)
Chapter 7/145 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
JOINs and ALIASing
SELECT book.title, author.nameFROM author, book
WHERE books.author = author.id
SELECT very_long_column_name AS col FROM tname WHERE col=‘5’
![Page 146: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/146.jpg)
Chapter 7/146 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Loading text files
Comma-separated files (*.csv) LOAD DATA LOCAL INFILE
"whatever.csv" INTO TABLE tname
![Page 147: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/147.jpg)
Chapter 7/147 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Aggregate queries
SELECT position FROM people GROUP by position
SELECT position, AVG (salary) FROM people GROUP BY position HAVING AVG (salary) > 50000.00
![Page 148: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/148.jpg)
Chapter 7/148 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Full text search
CREATE TABLE WebCache (url VARCHAR (255) NOT NULL PRIMARY KEY,ptext TEXT NOT NULL,FULLTEXT (ptext));
INSERT INTO WebCache (url, ptext) VALUES (‘index.html’, ‘Welcome to the University of Michigan’);
SELECT url from WebCache WHERE MATCH (ptext) against (‘Michigan’);
![Page 149: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/149.jpg)
Chapter 7/149 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Advanced features
Transactions Table locking Functions Unions Outer joins
![Page 150: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/150.jpg)
Chapter 7/150 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Installing MySQL on Windows
http://www.mysql.com/products/mysql/
http://www.webdevelopersnotes.com/tutorials/sql/index.php3
![Page 151: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/151.jpg)
Chapter 7/151 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
Useful pointers
Small example: http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm
MySQL documentation:http://www.mysql.com/doc/en/index.html
(official) MySQL tutorial:http://www.mysql.com/doc/en/Tutorial.html
Online, interactive tutorials:http://sqlzoo.net/http://sql.grussell.org/
![Page 152: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/152.jpg)
Chapter 7/152 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm
use test;
CREATE TABLE STATION
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
STATE CHAR(2),
LAT_N REAL,
LONG_W REAL);
DESCRIBE STATION;
INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112);
INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105);
INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68);
SELECT * FROM STATION;
SELECT * FROM STATION
WHERE LAT_N > 39.7;
![Page 153: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/153.jpg)
Chapter 7/153 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SELECT ID, CITY, STATE FROM STATION; ID CITY STATE ;
SELECT ID, CITY, STATE FROM STATION
WHERE LAT_N > 39.7;
CREATE TABLE STATS
(ID INTEGER REFERENCES STATION(ID),
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150),
RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100),
PRIMARY KEY (ID, MONTH));
INSERT INTO STATS VALUES (13, 1, 57.4, 0.31);
INSERT INTO STATS VALUES (13, 7, 91.7, 5.15);
INSERT INTO STATS VALUES (44, 1, 27.3, 0.18);
INSERT INTO STATS VALUES (44, 7, 74.8, 2.11);
INSERT INTO STATS VALUES (66, 1, 6.7, 2.10);
INSERT INTO STATS VALUES (66, 7, 65.8, 4.52);
SELECT * FROM STATS;
![Page 154: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/154.jpg)
Chapter 7/154 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SELECT * FROM STATION, STATS
WHERE STATION.ID = STATS.ID;
SELECT MONTH, ID, RAIN_I, TEMP_F
FROM STATS
ORDER BY MONTH, RAIN_I DESC;
SELECT LAT_N, CITY, TEMP_F
FROM STATS, STATION
WHERE MONTH = 7
AND STATS.ID = STATION.ID
ORDER BY TEMP_F;
SELECT MAX(TEMP_F), MIN(TEMP_F), AVG(RAIN_I), ID
FROM STATS
GROUP BY ID;
SELECT * FROM STATION
WHERE 50 < (SELECT AVG(TEMP_F) FROM STATS
WHERE STATION.ID = STATS.ID);
![Page 155: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/155.jpg)
Chapter 7/155 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE VIEW METRIC_STATS (ID, MONTH, TEMP_C, RAIN_C) AS
SELECT ID,
MONTH,
(TEMP_F - 32) * 5 /9,
RAIN_I * 0.3937
FROM STATS;
SELECT * FROM METRIC_STATS;
SELECT * FROM METRIC_STATS
WHERE TEMP_C < 0 AND MONTH = 1
ORDER BY RAIN_C;
UPDATE STATS SET RAIN_I = RAIN_I + 0.01;
SELECT * FROM STATS;
UPDATE STATS SET TEMP_F = 74.9
WHERE ID = 44
AND MONTH = 7;
![Page 156: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/156.jpg)
Chapter 7/156 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SELECT * FROM STATS;
COMMIT WORK;
UPDATE STATS SET RAIN_I = 4.50
WHERE ID = 44;
SELECT * FROM STATS;
ROLLBACK WORK;
SELECT * FROM STATS;
UPDATE STATS SET RAIN_I = 4.50
WHERE ID = 44
AND MONTH = 7;
COMMIT WORK;
SELECT * FROM STATS;
![Page 157: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/157.jpg)
Chapter 7/157 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
DELETE FROM STATS
WHERE MONTH = 7
OR ID IN (SELECT ID FROM STATION
WHERE LONG_W < 90);
DELETE FROM STATION WHERE LONG_W < 90;
COMMIT WORK;
SELECT * FROM STATION;
SELECT * FROM STATS;
SELECT * FROM METRIC_STATS;
![Page 158: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/158.jpg)
Chapter 7/158 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
http://www.mysql.com/doc/en/Tutorial.html
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
("lax"),("whale"),("ostrich");
SELECT * FROM animals;
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);
SELECT * FROM shop;
![Page 159: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/159.jpg)
Chapter 7/159 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
INSERT INTO articles VALUES
(NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
(NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
(NULL,'Optimizing MySQL','In this tutorial we will show ...'),
(NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
(NULL,'MySQL Security', 'When configured properly, MySQL ...');
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database');
![Page 160: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/160.jpg)
Chapter 7/160 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
# What's the highest item number?
SELECT MAX(article) AS article FROM shop;
# Find number, dealer, and price of the most expensive article.
SELECT MAX(price) FROM shop;
SELECT article, dealer, price
FROM shop
WHERE price=19.95;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
# What's the highest price per article?
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
![Page 161: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/161.jpg)
Chapter 7/161 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS
dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
![Page 162: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/162.jpg)
Chapter 7/162 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
# find the articles with the highest and lowest price
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
# foreign keys
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
![Page 163: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/163.jpg)
Chapter 7/163 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
INSERT INTO person VALUES (NULL, 'Antonio Paz');
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM person;
SELECT * FROM shirt;
![Page 164: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/164.jpg)
Chapter 7/164 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';
# unions
select id, style from shirt where color = 'blue' union select id,
style from shirt where color = 'orange'
# visits per day
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
![Page 165: MySQL Slides](https://reader033.vdocuments.us/reader033/viewer/2022061103/540a2141dab5ca2e688b493d/html5/thumbnails/165.jpg)
Chapter 7/165 Copyright © 2004
Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke
References
Bài giảng MySQL Ai-ti Aptech SQL applications Dragomir R. Radev
Fall 2005 http://dev.mysql.com/doc/refman/
5.5/en/