1 / 67

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

This lecture covers the basics of relational languages and SQL, including querying data, data manipulation and definition, the history and evolution of SQL, and practical examples.

bettse
Télécharger la présentation

Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications

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. Carnegie Mellon Univ.Dept. of Computer Science15-415/615 - DB Applications C. Faloutsos – A. Pavlo Lecture#6: Fun with SQL (Part 1)

  2. Administrivia • HW1 is due today. • HW2 is out. CMU SCS 15-415/615

  3. Homework #2: Bike-Share Data • For each question, generate a SQL query that computes the answer. • It will test automatically when you submit. • Column names are not important but order is • You can use Postgres on your laptop or on one of the Andrews machines. • Check the “Grade Center” on Blackboard for your machine and port number. CMU SCS 15-415/615

  4. Relational Languages • A major strength of the relational model: supports simple, powerful querying of data. • User only needs to specify the answer that they want, not how to compute it. • The DBMS is responsible for efficient evaluation of the query. • Query optimizer: re-orders operations and generates query plan CMU SCS 15-415/615

  5. Relational Languages • Standardized DML/DDL • DML → Data Manipulation Language • DDL → Data Definition Language • Also includes: • View definition • Integrity & Referential Constraints • Transactions CMU SCS 15-415/615

  6. History • Originally “SEQUEL” from IBM’sSystem R prototype. • Structured English Query Language • Adopted by Oracle in the 1970s. • ANSI Standard in 1986, ISO in 1987 • Structured Query Language CMU SCS 15-415/615

  7. History • Current standard is SQL:2011 • SQL:2011 →Temporal DBs, Pipelined DML • SQL:2008 →TRUNCATE, Fancy ORDER • SQL:2003 → XML, windows, sequences, auto-generated IDs. • SQL:1999 → Regex, triggers, OO • Most DBMSs at least support SQL-92 • System Comparison: • http://troels.arvin.dk/db/rdbms/ CMU SCS 15-415/615

  8. Today's Class: OLTP • Basic Queries • Table Definition (DDL) • NULLs • String/Date/Time/Set/Bag Operations • Output Redirection/Control CMU SCS 15-415/615

  9. Example Database STUDENT ENROLLED COURSE CMU SCS 15-415/615

  10. First SQL Example Find the course ids where students received a grade of ‘C’ in the course. SELECT cid FROM enrolled WHERE grade = ‘C’ Similar to… But not quite…. p cid(sgrade=‘C’ (enrolled)) Duplicates CMU SCS 15-415/615

  11. First SQL Example SELECT DISTINCTcid FROM enrolled WHERE grade = ‘C’ Now we get the same result as the relational algebra Why preserve duplicates? • Eliminating them is costly • Users often don’t care. CMU SCS 15-415/615

  12. Multi-Relation Queries SELECT name, cid FROM student, enrolled WHEREstudent.sid= enrolled.sid AND enrolled.grade = ‘C’ Get the name of the student and the corresponding course ids where they received a grade of ‘C’ in that course. Same as pname, cid(sgrade=‘C’ (student⋈enrolled))

  13. Basic SQL Query Grammar SELECT [DISTINCT|ALL] target-list FROMrelation-list [WHEREqualification] • Relation-List: A list of relation names • Target-List: A list of attributes from the tables referenced in relation-list • Qualification: Comparison of attributes or constants using operators =, ≠, <, >, ≤, and ≥. CMU SCS 15-415/615

  14. SELECT Clause • Use * to get all attributes • Use DISTINCT to eliminate dupes • Target list can include expressions SELECT*FROMstudent SELECTstudent.*FROMstudent SELECTDISTINCTcidFROMenrolled SELECTname, gpa*1.05FROM student CMU SCS 15-415/615

  15. FROM Clause • Binds tuples to variable names • Define what kind of join to use SELECT * FROMstudent, enrolledWHEREstudent.sid= enrolled.sid SELECTstudent.*, enrolled.gradeFROM student LEFT OUTER JOINenrolledWHEREstudent.sid= enrolled.sid CMU SCS 15-415/615

  16. WHERE Clause • Complex expressions using AND, OR, and NOT • Special operators BETWEEN, IN: SELECT * FROMenrolled WHEREgrade = ‘C’ AND(cid= ‘15-415’ ORNOTcid= ‘15-826’) SELECT * FROMenrolled WHERE(sidBETWEEN56000 AND57000) AND cidIN(‘15-415’, ‘15-721’) CMU SCS 15-415/615

  17. Renaming • The AS keyword can also be used to rename tables and columns in SELECT queries. • Allows you to target a specific table instance when you reference the same table multiple times. CMU SCS 15-415/615

  18. Renaming – Table Variables • Get the name of the students that took 15-415 and got an ‘A’ or ‘B’ in the course. SELECT student.name, enrolled.grade FROM student, enrolled WHERE student.sid= enrolled.sid ANDenrolled.cid= ‘15-415’ AND enrolled.grade IN (‘A’, ‘B’) CMU SCS 15-415/615

  19. Renaming – Table Variables • Get the name of the students that took 15-415 and got an ‘A’ or ‘B’ in the course. SELECT S.name, E.gradeASegrade FROM student ASS, enrolled ASE WHERE S.sid= E.sid ANDE.cid= ‘15-415’ AND E.grade IN (‘A’, ‘B’) CMU SCS 15-415/615

  20. Renaming – Self-Join • Find all unique studentsthat have taken more thanone course. SELECTDISTINCTe1.sid FROMenrolled ASe1, enrolled ASe2 WHEREe1.sid = e2.sid ANDe1.cid != e2.cid CMU SCS 15-415/615

  21. More SQL • INSERT • UPDATE • DELETE • TRUNCATE CMU SCS 15-415/615

  22. INSERT • Provide target table, columns, and values for new tuples: • Short-hand version: INSERT INTOstudent (sid, name, login, age, gpa) VALUES (53888, ‘Drake’, ‘drake@cs’, 29, 3.5) INSERT INTO studentVALUES (53888, ‘Drake’, ‘drake@cs’, 29, 3.5) CMU SCS 15-415/615

  23. UPDATE • UPDATE must list what columns to update and their new values (separated by commas). • Can only update one table at a time. • WHERE clause allows query to target multiple tuples at a time. UPDATE student SETlogin = ‘kwest@cs’, age = age + 1 WHEREname = ‘Kayne’ CMU SCS 15-415/615

  24. DELETE • Similar to single-table SELECT statements. • The WHERE clause specifies which tuples will deleted from the target table. • The delete may cascade to children tables. DELETE FROM enrolled WHERE grade = ‘F’ CMU SCS 15-415/615

  25. TRUNCATE • Remove all tuples from a table. • This is usually faster than DELETE, unless it needs to check foreign key constraints. TRUNCATE student CMU SCS 15-415/615

  26. Today's Party: OLTP • Basic Queries • Table Definition (DDL) • NULLs • String/Date/Time/Set/Bag Operations • Output Redirection/Control CMU SCS 15-415/615

  27. Table Definition (DDL) CREATE TABLE <table-name>( [column-definition]* [constraints]* ) [table-options]; • Column-Definition: Comma separated list of column names with types. • Constraints: Primary key, foreign key, and other meta-data attributes of columns. • Table-Options: DBMS-specific options for the table (not SQL-92).

  28. Table Definition Example CREATE TABLE student ( sidINT, name VARCHAR(16), login VARCHAR(32), age SMALLINT, gpaFLOAT ); CREATE TABLE enrolled ( sidINT, cid VARCHAR(32), grade CHAR(1) ); Integer Range Variable String Length Fixed String Length

  29. Common Data Types • CHAR(n), VARCHAR(n) • TINYINT, SMALLINT, INT, BIGINT • NUMERIC(p,d), FLOAT, DOUBLE, REAL • DATE, TIME • BINARY(n), VARBINARY(n), BLOB CMU SCS 15-415/615

  30. Useful Non-standard Types • TEXT • BOOLEAN • ARRAY • Geometric primitives • XML/JSON • Some systems also support user-defined types. CMU SCS 15-415/615

  31. Integrity Constraints CREATE TABLE student ( sidINT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpaFLOAT ); CREATE TABLE enrolled ( sidINT REFERENCESstudent (sid), cid VARCHAR(32) NOT NULL, grade CHAR(1), PRIMARY KEY (sid, cid) ); PKey Definition Type Attributes FKey Definition

  32. Primary Keys • Single-column primary key: • Multi-column primary key: CREATE TABLE student ( sid INT PRIMARY KEY, ⋮ CREATE TABLE enrolled ( ⋮ PRIMARY KEY (sid, cid) CMU SCS 15-415/615

  33. Foreign Key References • Single-column reference: • Multi-column reference: CREATE TABLE enrolled ( sid INT REFERENCES student (sid), ⋮ CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid, ...) REFERENCES student (sid, ...) CMU SCS 15-415/615

  34. Foreign Key References • You can define what happens when the parent table is modified: • CASCADE • RESTRICT • NO ACTION • SET NULL • SET DEFAULT CMU SCS 15-415/615

  35. Foreign Key References • Delete/update the enrollment information when a student is changed: CREATE TABLE enrolled ( ⋮ FOREIGN KEY (sid) REFERENCES student (sid) ONDELETE CASCADE ON UPDATE CASCADE CMU SCS 15-415/615

  36. Value Constraints • Ensure one-and-only-one value exists: • Make sure a value is not null: CREATE TABLE student ( login VARCHAR(32) UNIQUE, CREATE TABLE enrolled ( cid VARCHAR(32) NOT NULL, CMU SCS 15-415/615

  37. Value Constraints • Make sure that an expression evaluates to true for each row in the table: • Can be expensive to evaluate, so tread lightly… CREATE TABLE student ( age SMALLINT CHECK (age > 0), CMU SCS 15-415/615

  38. Auto-Generated Keys • Automatically create a unique integer id for whenever a row is inserted (last + 1). • Implementations vary wildly: • SQL:2003 →IDENTITY • MySQL →AUTO_INCREMENT • Postgres → SERIAL • SQL Server →SEQUENCE • DB2 → SEQUENCE • Oracle →SEQUENCE CMU SCS 15-415/615

  39. Auto-Generated Keys CREATE TABLE student ( sid INT PRIMARY KEY AUTO_INCREMENT, ⋮ MySQL INSERT INTO student (sid, name, login, age, gpa) VALUES (NULL, “Drake”, “drake@cs”, 29, 4.0); CMU SCS 15-415/615

  40. Conditional Table Creation • IFNOTEXISTS prevents the DBMS from trying to create a table twice. CREATE TABLE IF NOT EXISTSstudent ( sidINT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT CHECK (age > 0), gpaFLOAT ); CMU SCS 15-415/615

  41. Dropping Tables • Completely removes a table from the database. Deletes everything related to the table (e.g., indexes, views, triggers, etc): • Can also use IFEXISTS to avoid errors: DROP TABLE student; DROP TABLEIF EXISTS student; CMU SCS 15-415/615

  42. Modifying Tables • SQL lets you add/drop columns in a table after it is created: • This is really expensive!!! Tread lightly… ALTER TABLE student ADDCOLUMN phone VARCHAR(32) NOT NULL; ALTER TABLE student DROPCOLUMN login; CMU SCS 15-415/615

  43. Modifying Tables • You can also modify existing columns (rename, change type, change defaults, etc): ALTER TABLE student ALTER COLUMN loginTYPE VARCHAR(32); Postgres ALTER TABLE student CHANGE COLUMN login loginVARCHAR(32); MySQL CMU SCS 15-415/615

  44. Accessing Table Schema • You can query the DBMS’s internal INFORMATION_SCHEMA catalog to get info about the database. • ANSI standard set of read-only views that provide info about all of the tables, views, columns, and procedures in a database • Every DBMS also have non-standard shortcuts to do this. CMU SCS 15-415/615

  45. Accessing Table Schema • List all of the tables in the current database: SELECT * FROMINFORMATION_SCHEMA.TABLES WHEREtable_catalog = '<db name>' \d Postgres SHOW TABLES; MySQL .tables; SQLite CMU SCS 15-415/615

  46. Accessing Table Schema • List the column info for the student table: SELECT * FROMINFORMATION_SCHEMA.COLUMNS WHEREtable_name = 'student' \d student Postgres DESCRIBE student; MySQL .schema student; SQLite CMU SCS 15-415/615

  47. Today's Class: OLTP • Basic Queries • Table Definition (DDL) • NULLs • String/Date/Time/Set/Bag Operations • Output Redirection/Control CMU SCS 15-415/615

  48. NULLs • The “dirty little secret” of SQL, since it can be a value for any attribute. • What does this mean? • Is that we do not know Andy’s GPA? • Or does Andy not have a GPA? CMU SCS 15-415/615

  49. NULLs • Find all the students with a null GPA. X SELECT * FROMstudent WHEREgpa= NULL CMU SCS 15-415/615

  50. NULLs • Find all the students with a null GPA. SELECT * FROMstudent WHEREgpaISNULL CMU SCS 15-415/615

More Related