1 / 90

SQL Data Definition Language (DDL)

SQL Data Definition Language (DDL). Defining (Oracle10 g ) database structures, integrity, etc. Structured Query Language. What is it, and where did it come from?. Structured Query Language (Kifer p. 46). Standard language for relational databases: database definition (DDL)

abie
Télécharger la présentation

SQL Data Definition Language (DDL)

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. SQL Data DefinitionLanguage (DDL) Defining (Oracle10g) database structures, integrity, etc. SFDV3002

  2. Structured Query Language What is it, and wheredid it come from? SFDV3002

  3. Structured Query Language(Kifer p. 46) • Standard language for relational databases: • database definition (DDL) • data manipulation (DML) • Security control (see SFDV3002 chapter 3) SFDV3002

  4. Structured Query Language(Kifer p. 46) • Used by: • database administrator (DBA) • programmers • SQL literate end-users • application programs & middleware(see SFDV3002 ch. 4) SFDV3002

  5. History(Date & Darwen, A Guide to the SQL Standard) 1970: Relational model (Codd, IBM) ⇒ relational algebra & calculus 1974: SEQUEL (Chamberlin & Boyce) 1975: IBM System R 1981: SQL/DS (IBM) 1986–2003: Various standards (see next slide) Today: “Standardised”, but still varies across products. SFDV3002

  6. We are here… • Structured Query Language • Aside: Model translation • Data Definition Language • Data Manipulation Language • SQL Query Design SFDV3002

  7. Relational concepts in SQL columns • Table, row column vs. relation, tuple attribute • PK, FK, domain, etc. rows SFDV3002

  8. How does all this fit together? Data & metadata “DATABASE” DBMS Integrity constraints SFDV3002

  9. Oracle architecture (simplified)(see also Oracle10g Concepts manual, ch. 1 ▷ “Oracle Database Architecture”) An Oracle installation Instance Database Contains several logical “databases” What we would normally think of as a “database” — stuff gets defined here User/schema SFDV3002

  10. Instance, Database and Schema: The distinction between these three is probably one of the most confusing concepts across different RDBMS since different RDBMS have different architectures and different terminologies.  To put it simply in Oracle lingo, a database is a set of files (data files, redo log files, control files and so on).  An instance is a set of processes (SMON, PMON, MMON, DBWR, etc) and a shared memory segment (SGA).  A database may be mounted and opened by many instances (RAC installation) concurrently (this is to do with the shared everything architecture of Oracle RAC).  An instance may mount and open any database — however it may only open a single database at any time.  So, in ERD terms: Database:Instance :: 1:N (a one to many relationship) in Oracle.  A schema is the container of the schema objects so all the tables, views, procedures, packages, views etc. reside in the schema.  There can be multiple schemas per database. SFDV3002

  11. The catalog(Kifer §3.3.2) • User data (INSERT) vs. metadata (CREATE <xxx>) • Stored in predefined tables: catalog or data dictionary. • Read with SQL. SFDV3002

  12. SFDV3002

  13. The Oracle10g catalog(Oracle10g Concepts manual ch. 7; Oracle10g Referencemanual chs. 2–3; Table 2–3; Example 2–4) Data dictionary views • All_xxx: details of all <xxx>accessibleby current user. • User_xxx: details of all <xxx>created by current user. • DBA_xxx: details of all <xxx> in entire Oracle10g database (restricted access). • DESCRIBE <tablename> SFDV3002

  14. Oracle10g schema objects(SQL Reference ch. 2 ▷“Database Objects”) • Tables & views • Stored functions & procedures • Packages • Triggers • Indexes & some other physical structures • Object tables, types & views etc. SFDV3002

  15. SQL data definition • Structure: tables, columns, data types, views • Integrity constraints • Stored code • Physical-level structures etc. SFDV3002

  16. CREATE TABLE(Kifer §3.3.1; Example 2–5; SQL Reference ch. 16▷ “CREATE TABLE”) CREATE TABLE <name> ( <column-name> <data-type> [<default>] [<column-constraint>], <column-name> <data-type> [<default>] [<column-constraint>], ..., [<table-constraint>, ...] ); SFDV3002

  17. Core SQL:2003 data types(see also SQL Reference ch. 2 ▷“Datatypes” ▷“ANSI, DB2 and SQL/DS datatypes”) • CHARACTER(n): fixed-length char (CHAR) • CHARACTER VARYING(n): variable-length char (VARCHAR) • SMALLINT, INTEGER, BIGINT: integers • NUMERIC/DECIMAL: fixed point decimal • FLOAT/REAL/DOUBLE PRECISION: floating point decimal • DATE, TIME, TIMESTAMP, INTERVAL: dates and times SFDV3002

  18. Additional SQL:2003 data types • BINARY LARGE OBJECT: arbitrary binary data (BLOB) • NATIONAL CHARACTER: international character sets (NCHAR) • BOOLEAN (only since SQL:1999!) • Constructed data types: REF, ROW, ARRAY • XML • User-defined data types (UDTs) SFDV3002

  19. Oracle10g character types(SQL Reference ch. 2 ▷ “Datatypes” ▷ “CHAR/VARCHAR2 Datatype”) CHAR(n) • Fixed-length character (default 1) • Exactlyn characters, padded with blanks • Max. 2000 bytes VARCHAR2(n) • Variable-length character • Up ton characters, no padding • Max. 4000 bytes SFDV3002

  20. Oracle10g character types: some tips(SQL Reference ch. 2 ▷ “Datatypes” ▷ “CHAR/VARCHAR2 Datatype”) • String concatenation operator: || • String functions: see Oracle10g docs. • Single quotes (' '). • Use sensible lengths. • Use CHAR if length never changes. • String comparisons: • case sensitive ('A' ≠ 'a') • trailing blanks stripped ('a ' = 'a') SFDV3002

  21. Oracle10g numeric types(SQL Reference ch. 2 ▷ “Datatypes” ▷ “NUMBER Datatype”) NUMBER(p,s) • Number p digits long (precision) including s decimal places (scale). • Both floating point and integers. • Usual operators (+, –, *, /). • INTEGER/FLOAT/etc. ⇒NUMBER Some tips • Always use NUMBER(p,2) for money (why?) • Get the size right! SFDV3002

  22. Oracle10g dates(SQL Reference ch. 2 ▷ “Datatypes” ▷ “DATE/TIMESTAMP/… Datatype”) DATE • Date + time accurate to seconds, range 1/1/4712 B.C. ⇒ 31/12/4712 A.D. (!) • Default format 'dd-mmm-yy' (e.g., '12-MAY-90') • Current date function: SYSDATE. • Date arithmetic: subtract dates ⇒ number of days. (see also INTERVAL data type) SFDV3002

  23. Other Oracle10g data types(SQL Reference ch. 2 ▷ “Datatypes”) • LONG: up to 2 GiB of text • RAW(n): n bytes of binary data (n ≤ 2000) • LONG RAW: up of 2 GiB binary data • ROWID: a physical row address • BLOB: binary large object up to 8 TiB • CLOB: character large object up to 8 TiB • BFILE: pointer to an external binary file (max. 4 GiB) • UDTs (objects), arrays, REF, row types, … SFDV3002

  24. Oracle10g data type conversion(SQL Reference ch. 2 ▷ “Datatypes” ▷ “Datatype Comparison Rules” ▷ “Data Conversion”) Explicit • TO_CHAR, TO_NUMBER, TO_DATE, CAST functions Implicit • Automatic where necessary and possible: • '10' = 10 converts '10' to number • 'abc' = 10 makes no sense • explicit conversion safer SFDV3002

  25. Deleting a table: DROP TABLE(Kifer p. 61; SQL Reference ch. 18▷ “DROP TABLE”) DROP TABLE <name> [CASCADE CONSTRAINTS]; • CASCADE CONSTRAINTS deletes referencing FK constraints • DROP commands for most schema objects: tables, views, constraints, indexes, sequences, … SFDV3002

  26. Defining integrity in SQL(Kifer §3.3.3–3.3.7) Constraints enforce rules on tables. Simple constraints • DEFAULT, NOT NULL, UNIQUE • Name VARCHAR2(15) NOT NULL More complex constraints • CHECK allowable values Arbitrary business rules • e.g. adjust customers’ credit ratings according to sales volume and payment reliability • usually very complex (procedural code) SFDV3002

  27. Types of integrity available in SQL(see also Kifer §3.3.8) Declarative constraints • Domain: sort of in Oracle (CHECK) • Entity: PRIMARY KEY • Referential: FOREIGN KEY, cascade deletion • User-defined: • DEFAULT, NOT NULL, UNIQUE • CHECK • triggers (reactive) • stored code Procedural constraints SFDV3002

  28. Defining integrity constraints in SQL(see also Kifer §3.3.3–3.3.7) Column constraints (single column only) • salary ≥ $12,000 • department in {INFO, ACCT} Table constraints (single table only) • start date ≤ finish date • composite primary key Database constraints (single schema only) • sum of customer’s sales ≤ their credit limit • not in Oracle (except FKs) SFDV3002

  29. Oracle10g integrity constraints(SQL Reference ch. 8▷ “constraint”; Example 2–6) [CONSTRAINT <name>] <constraint-body> [[NOT] DEFERRABLE] • Column or table constraints. • Automatic vs. explicit names. • Checked immediately (NOT DEFERRABLE) vs. at end of transaction (DEFERRABLE). SFDV3002

  30. Oracle10g entity integrity Column constraint form (single-column PK) <column-name> <data-type> PRIMARY KEY DeptId NUMBER (2) CONSTRAINT dept_deptid_pk PRIMARY KEY, Table constraint form (composite PK) PRIMARY KEY (<column(s)>)← preferred CONSTRAINT dept_deptid_pk PRIMARY KEY(DeptId), SFDV3002

  31. Oracle10g referential integrity(see also Kifer §3.3.7) FOREIGN KEY (<column(s)>) REFERENCES <table> [(<column(s)>)] [ON DELETE CASCADE] • Table constraint only. • ON DELETE CASCADE: delete “child” rows when “parent” row is deleted (no “dangling” FKs). (see also triggers in SFDV3002 ch. 4) SFDV3002

  32. DEFAULT values <column> <type>DEFAULT<value> • City varchar(255) DEFAULT 'Sandnes' Single column, technically not a constraint. SFDV3002

  33. NOT NULL constraint <column> <type> NOT NULL • Name VARCHAR2(15) NOT NULL • Column constraint, <column> cannot be null. SFDV3002

  34. UNIQUE constraint Column constraint form (single column only) <column> <type> UNIQUE • no duplicate values in <column>. • CONSTRAINT dept_dname_uk UNIQUE(DeptName), Table constraint form (one or more columns) UNIQUE (<column(s)>) • Combination of values in <column(s)> is unique. SFDV3002

  35. CHECK constraint Column constraint form <column> <type> CHECK (<condition>) • <condition> can only directly refer to <column> DeptId NUMBER(2) CONSTRAINT dept_deptid_cc CHECK((DeptId >= 10) and (DeptId <= 99)), Table constraint form CHECK (<condition>) • <condition> can directly refer to any column in the table CONSTRAINT dept_deptid_cc CHECK((DeptId >= 10) and (DeptId <= 99)), SFDV3002

  36. A quick recap • We can now: • Define tables with various data types and various declarative integrity constraints. • Delete entire tables. What about changing existing table structures? SFDV3002

  37. Changing table structures(Kifer §3.3.10) The simple approach • DROP table and re-create. • Not always an option. (why not?) An alternative • Change in place using ALTER TABLE. • ADD: columns, table constraints. • MODIFY: columns only (data type, DEFAULT, NOT NULL). • REMOVE: constraints. SFDV3002

  38. ALTER TABLE: ADD form(SQL Reference ch. 12▷ “ALTER TABLE”; Example 2–7) ALTER TABLE <name> ADD ( <new-column-name> <data-type> [<default>] [<column-constraint>] [, ...] ); ALTER TABLE student ADD SocialSecurity CHAR(9); SFDV3002

  39. ALTER TABLE: MODIFY form(Example 2–8) ALTER TABLE <name> MODIFY ( <column-name> [<data-type>] [<default>] [NOT NULL] ); ALTER TABLE student MODIFY SocialSecurity VARCHAR2(11); • Can only add NOT NULL if column contains no nulls! SFDV3002

  40. ALTER TABLE: DROP form(Example 2–9) ALTER TABLE <name> DROP PRIMARY KEY [CASCADE]; ALTER TABLE major DROP PRIMARY KEY CASCADE; ALTER TABLE <name> DROP UNIQUE (<column> [, ...]) [CASCADE]; ALTER TABLE <name> DROP CONSTRAINT <constraint-name>; ALTER TABLE employee DROP CONSTRAINT employee_deptid_fk; • CASCADE drops all dependent constraints. SFDV3002

  41. ALTER TABLE: Caveats ADD • New columns contain nulls if no DEFAULT MODIFY • Limitations changing data types • Changing default affects only new rows SFDV3002

  42. Sequences(SQL Reference ch. 15▷ “CREATE SEQUENCE”; Example 2–10) Automatically generate unique numeric values CREATE SEQUENCE <name> [INCREMENT BY <num>] [START WITH <num>] [MAXVALUE <num>] [MINVALUE <num>] [[NO]CYCLE]; SFDV3002

  43. Using sequences • Defaults: start 1, increment +1, no max. • Seq.CURRVAL returns current value of Seq. • Seq.NEXTVAL increments Seq and returns value. • Note: • CURRVAL for current user only • NEXTVAL affected by other users SFDV3002

  44. Summary of section 2.3 • Oracle architecture • Metadata and the catalog • CREATE and DROP TABLE • SQL & Oracle10g data types in more detail • Integrity constraints (CHECK, PK, FK, etc.) • ALTER TABLE • Sequences Next: SQL data manipulation language (DML) SFDV3002

  45. SQL Data ManipulationLanguage (DML) 2.4 Getting data into and out of(Oracle10g) databases SFDV3002

  46. We are here… • Structured Query Language • Aside: Model translation • Data Definition Language • Data Manipulation Language • SQL Query Design SFDV3002

  47. Creating rows: INSERT(Kifer §5.3.1; SQL Reference ch. 18▷ “INSERT”; Example 2–11) Either:← preferred form INSERT INTO Employee (Employee_ID, Last_name, First_name, Middle_initial, ...) VALUES (12345, 'Anderson', 'Thomas', 'A', ...); or just: INSERT INTO Employee VALUES (12345, 'Anderson', 'Thomas', 'A', ...); SFDV3002

  48. Updating rows: UPDATE(Kifer §5.3.3; SQL Reference ch. 19▷ “UPDATE”; Example 2–12) UPDATE Employee SET Department_ID = 13 WHERE Department_ID = 5; UPDATE Employee SET First_name = 'Janet', Salary = Salary * 1.5 WHERE Employee_ID = 12345; SFDV3002

  49. Deleting rows: DELETE(Kifer §5.3.2; SQL Reference ch. 17▷ “DELETE”; Example 2–13) DELETE FROM Employee WHERE Employee_ID = 12345; DELETE FROM Employee WHERE Salary >= 75400; DELETE FROM Employee WHERE Department_ID = 13; What will DELETE FROM Employee; do? SFDV3002

  50. The importance of WHERE • Irrelevant for INSERT, usually essential for DELETE, UPDATE, SELECT. • WHERE expression: • always evaluates to true/false • multiple conditions using AND/OR (Boolean logic) (see Tutorial 2) SFDV3002

More Related