1 / 109

Chapter 4

Chapter 4. An Introduction to SQL. History of SQL. Developed by IBM in 1970s Early prototype of DB2 called System R Current version is SQL:1999 All major database vendors support SQL All support a superset of a subset of SQL Both relvar and relation = table in SQL Tuple = row in SQL

javan
Télécharger la présentation

Chapter 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 4 An Introduction to SQL

  2. History of SQL • Developed by IBM in 1970s • Early prototype of DB2 called System R • Current version is SQL:1999 • All major database vendors support SQL • All support a superset of a subset of SQL • Both relvar and relation = table in SQL • Tuple = row in SQL • Attribute = column in SQL

  3. Overview of SQL • It is the standard language for relational systems, although imperfect • Supports data definition (DDL) and data manipulation (DML) • DDL: CREATE TABLE, CREATE TYPE, ALTER TABLE • DML: SELECT, INSERT, DELETE, UPDATE

  4. DDL CREATE TABLE S ( S# CHAR(5) NOT NULL, SNAME CHAR(20) NOT NULL, STATUS SMALLINT NOT NULL, CITY CHAR(15) NOT NULL, PRIMARY KEY (S#) ) ; Creates (an empty) table with column headings Creates an entry in the data dictionary (catalog) Data can be placed in the table by: 1) Interactive SQL INSERT statements 2) A “host” language program with embedded SQL INSERT statements   3) A utility “load” program that loads tables from files

  5. DDL CREATE TABLE Is an executable statement Can be executed interactively through an SQL interface Can be executed from within a host language program 

  6. create table S (snum char(5) not null, sname char(20) not null, status numeric(5) not null, city char(15) not null, primary key (snum)); create table P (pnum char(6) not null, pname char(20) not null, color char(6) not null, weight numeric (5,1) not null, city char(15) not null, primary key (pnum)); The suppliers and parts database (SQL definition)

  7. create table SP (snum char(5) not null, pnum char(6) not null, qty numeric(9), primary key (snum, pnum), foreign key (snum) references S, foreign key (pnum) references P); The suppliers and parts database (SQL definition, cont.)

  8. S +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ SP +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P1 | 300 | | S1 | P2 | 200 | | S1 | P3 | 400 | | S1 | P4 | 200 | | S1 | P5 | 100 | | S1 | P6 | 100 | | S2 | P1 | 300 | | S2 | P2 | 400 | | S3 | P2 | 200 | | S4 | P2 | 200 | | S4 | P4 | 300 | | S4 | P5 | 400 | +------+------+------+ P +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ The suppliers and parts database (sample values)

  9. Data Types Integer Smallint Decimal (p, q) p decimal digits with q digits to the right of the decimal point Numeric (p, q) Number (p, q) Float [(p)] p binary digits of precision Real [(p)] (default precision if not specified) Char[acter] (n) character string of n characters (fixed length) (8 bit) Varchar (n) character string of up to n characters (varying length) (8 bit)

  10. DDL ALTER TABLE < base table name> ADD < column name > <type> ;   (the column values will be NULL or a default value can be specified)   (change to definition only until values supplied) DROP TABLE < base table name > ;   (deletes the table and any “associated” views or indexes) CREATE INDEX < index name > ON < base table name > ( <column name> ) ;   CREATE UNIQUE INDEX < index name > ON < base table name > ( <column name> ) ; no two entries in the index have the same value   i.e., no two records in the table can have the same value in the indexed field   “required” for the primary key

  11. SQL DDL: CREATE TABLE CREATE VIEW CREATE INDEX DROP TABLE DROP VIEW DROP INDEX ALTER TABLE DML: SELECT  INSERT DELETE UPDATE

  12. Relational operators in SQL (DML) • Restrict is implemented by SELECT • Subset of rows • Uses WHERE clause to narrow result • Uses SELECT * to copy entire table • Project is also implemented by SELECT • Subset of columns

  13. Copy entire table: mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec)

  14. Restrict: mysql> select snum, pnum, qty -> from SP -> where qty < 150; +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P5 | 100 | | S1 | P6 | 100 | +------+------+------+ 2 rows in set (0.02 sec)

  15. Restrict with compound conditions: mysql> select * -> from S -> where status > 10 -> and city = 'Paris'; +------+-------+--------+-------+ | snum | sname | status | city | +------+-------+--------+-------+ | S3 | Blake | 30 | Paris | +------+-------+--------+-------+ 1 row in set (0.01 sec)

  16. Project: mysql> select snum, city -> from S; +------+--------+ | snum | city | +------+--------+ | S1 | London | | S2 | Paris | | S3 | Paris | | S4 | London | | S5 | Athens | +------+--------+ 5 rows in set (0.00 sec)

  17. Relational operators in SQL (DML) • Join is also implemented by SELECT • Result is a single table matched by values in the join columns of each source table • Uses WHERE clause to specify source tables • Uses dot operator to qualify like column names • S.snum: a qualified column name

  18. Join: mysql> select S.snum, sname, status, city, pnum, qty -> from S, SP -> where S.snum = SP.snum; +------+-------+--------+--------+------+------+ | snum | sname | status | city | pnum | qty | +------+-------+--------+--------+------+------+ | S1 | Smith | 20 | London | P1 | 300 | | S1 | Smith | 20 | London | P2 | 200 | | S1 | Smith | 20 | London | P3 | 400 | | S1 | Smith | 20 | London | P4 | 200 | | S1 | Smith | 20 | London | P5 | 100 | | S1 | Smith | 20 | London | P6 | 100 | | S2 | Jones | 10 | Paris | P1 | 300 | | S2 | Jones | 10 | Paris | P2 | 400 | | S3 | Blake | 30 | Paris | P2 | 200 | | S4 | Clark | 20 | London | P2 | 200 | | S4 | Clark | 20 | London | P4 | 300 | | S4 | Clark | 20 | London | P5 | 400 | +------+-------+--------+--------+------+------+ 12 rows in set (0.00 sec)

  19. Update operators in SQL - Insert Insert is implemented by INSERT INTO insert into S (snum,sname,status,city) values ('S1','Smith',20,'London'), ('S2','Jones',10,'Paris'), ('S3','Blake',30,'Paris'), ('S4','Clark',20,'London'), ('S5','Adams',30,'Athens');

  20. Update operators in SQL - Insert Insert is implemented by INSERT INTO INSERT INTO TEMP (P#, Weight) SELECT P#, Weight FROM P WHERE COLOR = COLOR (‘Red’); Inserts part number and weight from P into part number and weight in TEMP for all red parts

  21. Update operators in SQL - Delete Delete is implemented by DELETE DELETE FROM SP WHERE P# = P# (‘P2’); Deletes all shipments for part P2

  22. Update operators in SQL - Update Update is implemented by UPDATE UPDATE S SET STATUS = 2 * STATUS, CITY = ‘Rome’ WHERE CITY = ‘Paris’; Doubles the status of the Parisian suppliers and moves them to Rome.

  23. MySQL sqlsh: /usr/local/opt/mysql-3.23.37/bin/mysql -h jacaranda.intranet.csupomona.edu -u hnriley -pmypassword hnriley_db dbkeys: hnriley mypassword hnriley_db jacaranda.intranet.csupomona.edu one line four lines

  24. MySQL intranet (104) % sqlsh Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> the prompt from the client program--the “monitor,” the “query processor”

  25. MySQL mysql command word mysql> show tables; +----------------------+ | Tables_in_hnriley_db | +----------------------+ | Cellar | | EMP | | P | | Producer | | S | | SP | | Wines | | tempP | +----------------------+ 8 rows in set (0.00 sec)

  26. MySQL mysql> select * from P where color = 'red'; +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P4 | Screw | Red | 14.0 | London | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ 3 rows in set (0.00 sec) SQL statement (a RESTRICT) entered on one line

  27. MySQL mysql> select snum, sname -> from S; +------+-------+ | snum | sname | +------+-------+ | S1 | Smith | | S2 | Jones | | S3 | Blake | | S4 | Clark | | S5 | Adams | +------+-------+ 5 rows in set (0.00 sec) SQL statement (a PROJECT) entered on two lines

  28. MySQL mysql> select pnum, pname -> from P -> where color = 'red' -> and city = 'London' -> ; +------+-------+ | pnum | pname | +------+-------+ | P1 | Nut | | P4 | Screw | | P6 | Cog | +------+-------+ 3 rows in set (0.00 sec) must always end with a semicolon monitor will continue prompting until semicolon entered SQL statement (a RESTRICT and a PROJECT) entered on multiple lines

  29. MySQL project join restrict mysql> select snum, sname, pnum, pname -> from S, P -> where S.city = P.city; +------+-------+------+-------+ | snum | sname | pnum | pname | +------+-------+------+-------+ | S1 | Smith | P1 | Nut | | S4 | Clark | P1 | Nut | | S2 | Jones | P2 | Bolt | | S3 | Blake | P2 | Bolt | | S1 | Smith | P4 | Screw | | S4 | Clark | P4 | Screw | | S2 | Jones | P5 | Cam | | S3 | Blake | P5 | Cam | | S1 | Smith | P6 | Cog | | S4 | Clark | P6 | Cog | +------+-------+------+-------+ 10 rows in set (0.00 sec) SQL statement with a JOIN

  30. SQL “JOIN” mysql> select * from S, P; +------+-------+--------+--------+------+-------+-------+--------+--------+ | snum | sname | status | city | pnum | pname | color | weight | city | +------+-------+--------+--------+------+-------+-------+--------+--------+ | S1 | Smith | 20 | London | P1 | Nut | Red | 12.0 | London | | S2 | Jones | 10 | Paris | P1 | Nut | Red | 12.0 | London | | S3 | Blake | 30 | Paris | P1 | Nut | Red | 12.0 | London | | S4 | Clark | 20 | London | P1 | Nut | Red | 12.0 | London | | S5 | Adams | 30 | Athens | P1 | Nut | Red | 12.0 | London | | S1 | Smith | 20 | London | P2 | Bolt | Green | 17.0 | Paris | | S2 | Jones | 10 | Paris | P2 | Bolt | Green | 17.0 | Paris | | S3 | Blake | 30 | Paris | P2 | Bolt | Green | 17.0 | Paris | | S4 | Clark | 20 | London | P2 | Bolt | Green | 17.0 | Paris | | S5 | Adams | 30 | Athens | P2 | Bolt | Green | 17.0 | Paris | | S1 | Smith | 20 | London | P3 | Screw | Blue | 17.0 | Rome | | S2 | Jones | 10 | Paris | P3 | Screw | Blue | 17.0 | Rome | | S3 | Blake | 30 | Paris | P3 | Screw | Blue | 17.0 | Rome | | S4 | Clark | 20 | London | P3 | Screw | Blue | 17.0 | Rome | | S5 | Adams | 30 | Athens | P3 | Screw | Blue | 17.0 | Rome | | S1 | Smith | 20 | London | P4 | Screw | Red | 14.0 | London | | S2 | Jones | 10 | Paris | P4 | Screw | Red | 14.0 | London | | S3 | Blake | 30 | Paris | P4 | Screw | Red | 14.0 | London | | S4 | Clark | 20 | London | P4 | Screw | Red | 14.0 | London | | S5 | Adams | 30 | Athens | P4 | Screw | Red | 14.0 | London | | S1 | Smith | 20 | London | P5 | Cam | Blue | 12.0 | Paris | | S2 | Jones | 10 | Paris | P5 | Cam | Blue | 12.0 | Paris | | S3 | Blake | 30 | Paris | P5 | Cam | Blue | 12.0 | Paris | | S4 | Clark | 20 | London | P5 | Cam | Blue | 12.0 | Paris | | S5 | Adams | 30 | Athens | P5 | Cam | Blue | 12.0 | Paris | | S1 | Smith | 20 | London | P6 | Cog | Red | 19.0 | London | | S2 | Jones | 10 | Paris | P6 | Cog | Red | 19.0 | London | | S3 | Blake | 30 | Paris | P6 | Cog | Red | 19.0 | London | | S4 | Clark | 20 | London | P6 | Cog | Red | 19.0 | London | | S5 | Adams | 30 | Athens | P6 | Cog | Red | 19.0 | London | +------+-------+--------+--------+------+-------+-------+--------+--------+ 30 rows in set (0.01 sec) the “join” part of the statement produces all possible combinations of rows--the “cartesian product of the two tables

  31. SQL “JOIN” project join restrict mysql> select snum, sname, pnum, pname -> from S, P -> where S.city = P.city; +------+-------+------+-------+ | snum | sname | pnum | pname | +------+-------+------+-------+ | S1 | Smith | P1 | Nut | | S4 | Clark | P1 | Nut | | S2 | Jones | P2 | Bolt | | S3 | Blake | P2 | Bolt | | S1 | Smith | P4 | Screw | | S4 | Clark | P4 | Screw | | S2 | Jones | P5 | Cam | | S3 | Blake | P5 | Cam | | S1 | Smith | P6 | Cog | | S4 | Clark | P6 | Cog | +------+-------+------+-------+ 10 rows in set (0.00 sec) the project and restrict parts of the statement produce the wanted columns from the meaningful rows SQL statement with a JOIN

  32. MySQL Scripts # # file tableBuilder.sql # script file to build table S # drop table S; create table S (snum char(5) not null, sname char(20) not null, status numeric(5) not null, city char(15) not null, primary key (snum)); insert into S (snum,sname,status,city) values ('S1','Smith',20,'London'), ('S2','Jones',10,'Paris'), ('S3','Blake',30,'Paris'), ('S4','Clark',20,'London'), ('S5','Adams',30,'Athens'); SQL statements

  33. MySQL Scripts file containing SQL statements mysql command word note the .sql extension mysql> source tableBuilder.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 deleted old table created new table inserted six rows

  34. MySQL Output to a File mysql command word file to receive copy of terminal output mysql> tee SuppliersList Logging to file 'SuppliersList' mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec) mysql> notee Outfile disabled. mysql> turn off the file output

  35. MySQL Output to a File intranet (103) % cat SuppliersList Logging to file 'SuppliersList' mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec) mysql> notee intranet (104) % copy of everything between “tee” and “notee”

  36. Nulls The absence of any value. Not a value. P +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ P4 color? (don’t know) not red not not red

  37. Nulls If acceptable, then physically: P +------+-------+-------+ --------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+ --------+--------+ | P1 | Nut | Red | 0 12.0 | London | | P2 | Bolt | Green | 0 17.0 | Paris | | P3 | Screw | Blue | 0 17.0 | Rome | | P4 | Screw | | 1 14.0 | London | | P5 | Cam | Blue | 0 12.0 | Paris | | P6 | Cog | Red | 0 19.0 | London | +------+-------+-------+ --------+--------+ P4 color marked as “null” (no value entered) extra hidden column with “null marker”

  38. Nulls create table Nullbiz (pnum char(6) not null, pname char(20), color char(6), weight numeric (5,1), city char(15), primary key (pnum)); insert into Nullbiz values ('NP1','Nut',null, 12.0, 'London'), ('NP2','Bolt','Green',null,'Paris'), ('NP3','Screw','Blue',17.0,null), ('NP4','Screw','Red',14.0,'London'), ('NP5','Cam','Blue',12.0,'Paris'), ('NP6','Cog','Red',19.0,'London'); primary key must be not null nulls are allowed

  39. insert into Nullbiz values ('NP1','Nut',null, 12.0, 'London'), ('NP2','Bolt','Green',null,'Paris'), ('NP3','Screw','Blue',17.0,null), ('NP4','Screw','Red',14.0,'London'), ('NP5','Cam','Blue',12.0,'Paris'), ('NP6','Cog','Red',19.0,'London'); not a value, not a string mysql> select * from Nullbiz; +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | NP1 | Nut | NULL | 12.0 | London | | NP2 | Bolt | Green | NULL | Paris | | NP3 | Screw | Blue | 17.0 | NULL | | NP4 | Screw | Red | 14.0 | London | | NP5 | Cam | Blue | 12.0 | Paris | | NP6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ 6 rows in set (0.00 sec)

  40. mysql command word Nulls allowed? mysql> describe Nullbiz; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | pnum | char(6) | | PRI | | | | pname | char(20) | YES | | NULL | | | color | char(6) | YES | | NULL | | | weight | decimal(5,1) | YES | | NULL | | | city | char(15) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) Bad choice. “Column Names”

  41. mysql> alter table tempP add size char(5); Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from tempP; +------+-------+-------+--------+--------+------+ | pnum | pname | color | weight | city | size | +------+-------+-------+--------+--------+------+ | P1 | Nut | Red | 12.0 | London | NULL | | P2 | Bolt | Green | 17.0 | Paris | NULL | | P3 | Screw | Blue | 17.0 | Rome | NULL | | P4 | Screw | Red | 14.0 | London | NULL | | P5 | Cam | Blue | 12.0 | Paris | NULL | | P6 | Cog | Red | 19.0 | London | NULL | +------+-------+-------+--------+--------+------+ 6 rows in set (0.01 sec)

  42. The Catalog in SQL • Every database has a catalog • A catalog has many schemas, one per user • Each catalog includes one Information Schema • The Information Schema is a collection of all other schemas as views • Represents a hypothetical Definition Schema for all users

  43. The Information Schema - Examples • Tables (includes views) • Views (does not include tables) • Table constraints • Referential constraints • Column constraints • Privileges • (Because these objects are themselves tables, they can be queried)

  44. Views CREATE VIEW GOOD_SUPPLIER AS SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15; You can now operate on the view, for example: SELECT S#, STATUS FROM GOOD_SUPPLIER WHERE CITY = ‘London’;

  45. Views - Example Creating a view, and then selecting from the view is equivalent to a select sub-query SELECT GOOD_SUPPLIER .S#, GOOD_SUPPLIER .STATUS FROM (SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15 ) AS GOOD_SUPPLIER; WHERE GOOD_SUPPLIER.CITY = ‘London’;

  46. SQL DDL DML CREATE ALTER DROP CREATE DROP CREATE DROP INSERT DELETE UPDATE SELECT TABLE “update” VIEW “query” “inquiry” “retrieval” INDEX

  47. Select SQL Select provides for all of the “query” functionality of the DML: restrict, project, join. SELECT <column(s)> [ or * ] FROM <table(s)> WHERE <condition> ORDER BY <column> ; always sometimes always

  48. Select mysql> select sname -> from S; +-------+ | sname | +-------+ | Smith | | Jones | | Blake | | Clark | | Adams | +-------+ 5 rows in set (0.00 sec) Result is a table with one column

  49. Select mysql> select city -> from S; +--------+ | city | +--------+ | London | | Paris | | Paris | | London | | Athens | +--------+ 5 rows in set (0.00 sec) Result is a table. What’s wrong?

  50. Select mysql> select distinct city -> from S; +--------+ | city | +--------+ | London | | Paris | | Athens | +--------+ 3 rows in set (0.02 sec) SQL keyword “distinct” to remove duplicates and make the result a true projection.

More Related