Karteikarten
Introduction to mySQL
Karten in diesem Stapel (18)
- 1Introduction to Structured Query Language (SQL)# Similar to programming, database structure and manipulation will have to be coded in certain language in order to be executed by computers.
#For relational database, the language is called Structured Query Language (SQL).
#SQL is supported by many relational database software (with slight variation) such as Oracle, MySQL, SQL server, Microsoft Access etc. - 2S.Q.L* SQL is relatively easy to learn Basic command set has vocabulary of less than 100 words.
*Nonprocedural language American National Standards Institute (ANSI) prescribes a standard SQL.
*Several SQL dialects exist. - 3SQL Data Definition Commands
- 4SQL Data Definition Commands (continued)
- 5SQL Data Definition Commands (continued)
- 6SQL Statements
- 7SELECT Statement** The purpose of SELECT statement is to retrieve and display data from one or more tables
- 8Example : SELECT Statement
- 9Projection (π)**Example 5.1 All Columns, All Rows
-List full details of all staff
** πstaffNo, fName, lName, address, position, sex, DOB, salary, branchNo (STAFF).
**SELECT staffNo, fName, lName, address, position, sex, DOB, salary, branchNo
FROM Staff;
-Can use * as an abbreviation for ‘all columns’:
**SELECT * FROM Staff; - 10Projection (π) continued..
- 11Selection (σ)
- 12Selection (σ) continued...
- 13SELECT Statement - Aggregates- ISO standard defines five aggregate functions:
i) COUNT returns number of values in specified column.
ii) SUM returns sum of values in specified column.
iii) AVG returns average of values in specified column.
iv) MIN returns smallest value in specified column.
iiv) MAX returns largest value in specified column.
**Each operates on a single column of a table and returns a single value. - 14SELECT Statement - Aggregates (continued)a) COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only.
b) Apart from COUNT(*), each function eliminates nulls first and operates only on remaining non-null values.
c) COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur.
d) Can use DISTINCT before column name to eliminate duplicates.
e) DISTINCT has no effect with MIN/MAX, but may have with SUM/AVG. - 15Example: Use of COUNT(*)
- 16Example : Use of COUNT and SUM
- 17Example : Use of MIN, MAX, AVG
- 18Example : Use of HAVING
