180 likes | 327 Views
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
E N D
CpSc 3220The 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 (Rockoffsays 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 fully-standard 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 fieldmade up of column-names, 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 aliasNameFROM table; The alias will be used in column headings • Tables can also be given aliases SELECT colexp FROM tableAS 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)
SQL Select Examples • We can write SQL statements for this database SELECT “Hello, World”; SELECT * FROM orders; SELECT firstName FROM customers; SELECT 2*OrderAmount FROM orders; SELECT “Amt doubled =”,2*OrderAmount FROM orders; SELECT concat(“Dr. ”, LastName) FROM customers; SELECT 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