Transcript Part I

CpSc 3220
The Language of SQL
Chapters 1-4
SQL Background
• SQL was created at IBM as a language for
SYSTEM-R, an early RDBM based on Codd’s
work (Rockoff says RDBMs are called relational to indicate
their tables are related. Not true.)
• Later used in other RDBMs
• Standardized by ANSI and ISO in various
versions; SQL92, SQL99, … ,SQL2011
• Commercial RDBMs rarely implement a fullystandard version of SQL
Textbook Covers Three RDBMs
• SQL Server from Microsoft
• Oracle from Oracle
• MySQL from Oracle
We Will Concentrate on MySQL
• A link to an online MySQL reference manual
http://dev.mysql.com/doc/refman/5.5/en/index.html
SQL is a Declarative Language
•
•
•
•
•
Statements say WHAT we want, not HOW to get it
Has some procedural elements
Three ‘sub-languages’: DML, DDL, DCL
We will spend most of our time on the DML part
There are several statement types in the DML
part of SQL
• We will spend most of our time on the SELECT
statement
But First . . .
• Rockoff discusses SQL datatypes
• There are a bunch – we concentrate on a few:
–
–
–
–
–
–
–
–
int
decimal
float
char
varchar
date
datetime
time
Back to the SELECT Statement
•
•
•
•
We review the full SELECT syntax
We will only study part of it
We start with a very small part
First, we review the syntax notation used for
describing SQL statements
Syntax of the SELECT statement
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'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Syntax Notation
• Information in upper case is ‘reserved’, must be
spelled as given (but upper case isn’t required in
actual SQL statements)
• Information in lower case must be further defined
• The information inside [ ] is optional
• The | symbol is used to indicate alternatives
• The . . . symbol indicates (zero or more) repetitions
• All other symbols are used as is
Some Syntax Notes
• SQL statements are generally case-insensitive
• SQL is generally free-format
• SQL identifier names are made up of letters,
numbers, special symbols, and spaces. (names
with special symbols and spaces may require
delimiters.) Fully defined in Reference Manual.
• Some DBMS (including MySQL) require
statement terminators (;)
select_expr
• Made be either
*
a comma-separated list of fields
fields
• A field may be
a column-name field
a literal-value field
a calculated or generated field made up of columnnames, literal-values, operators, and/or functions
• Fields may have aliases
SQL allows Aliases DB elements
• Any select_expr can be given a new name:
SELECT colexp AS aliasName FROM table;
The alias will be used in column headings
• Tables can also be given aliases
SELECT colexp FROM table AS tableAlias;
Samples
• Suppose we have a database with two tables;
orders and customers
• We use this notation to describe this database
orders(orderID, customerID, OrderAmount)
customer(customerID, FirstName, LastName)
The Customer Table from Ch 1
CustomerID
FirstName
LastName
1
William
Smith
2
Natalie
Lopez
3
Brenda
Harper
The Orders Table from Ch 1
OrderId
CustomerID
OrderAmount
1
1
50.00
2
1
60.00
3
2
33.50
4
3
20.00
SQL Select Examples
• We can write SQL statements for this database
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
“Hello, World”;
* FROM orders;
firstName FROM customers;
2*OrderAmount FROM orders;
“Amt doubled =”,2*OrderAmount FROM orders;
concat(“Dr. ”, LastName) FROM customers;
concat(“Dr. ”, LastName) AS Name FROM customers;
The DBs from the Rockoff book are
available online
• Download the appropriate (MySQL) file from the site
given in the CourseMaterials link on BB
• Use a text editor, open a blank file, cut and paste the
script for a specific chapter to your open text file.
• Modify this file by adding the statements below at
the beginning of the script
DROP DATABASE IF EXISTS yourDBname;
CREATE DATABASE yourDBname;
USE yourDBname;
• Save it with any name you choose with a suffix .sql
• Use PHPMyAdmin to import and run the saved script