1 / 23

ORACLE SQLPlus Updating and Modifying Data In SQL

ORACLE SQLPlus Updating and Modifying Data In SQL. University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management. Getting started with ORACLE in SQL-- see assignment on website Migrating databases from ACCESS to ORACLE -- see handouts

evers
Télécharger la présentation

ORACLE SQLPlus Updating and Modifying Data In SQL

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. ORACLE SQLPlus Updating and Modifying DataIn SQL University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management Database Management -- R. Larson

  2. Getting started with ORACLE in SQL-- see assignment on website Migrating databases from ACCESS to ORACLE -- see handouts More on SQL and SQLPlus for data manipulation and modification Today Database Management -- R. Larson

  3. SELECT • Syntax: • SELECT [DISTINCT] attr1, attr2,…, attr3 as label, function(xxx), calculation, attr5, attr6 FROM relname1 r1, relname2 r2,… rel3 r3 WHERE condition1 {AND | OR} condition2 ORDER BY attr1 [DESC], attr3 [DESC] Database Management -- R. Larson

  4. CREATE SYNONYM • CREATE SYNONYM newname FOR oldname; • CREATE SYNONYM BIOLIFE for ray.BIOLIFE; Database Management -- R. Larson

  5. SELECT Conditions • = equal to a particular value • >= greater than or equal to a particular value • > greater than a particular value • <= less than or equal to a particular value • <> not equal to a particular value • LIKE‘%wom_n%’ (Note different wild card) • IN (‘opt1’, ‘opt2’,…,’optn’) Database Management -- R. Larson

  6. Aggregate Functions • COUNT(dataitem) • AVG(numbercolumn) • SUM(numbercolumn) • MAX(numbercolumn) • MIN(numbercolumn) • STDDEV(numbercolumn) • VARIANCE(numbercolumn) Database Management -- R. Larson

  7. ABS(n) ACOS(n) ASIN(n) ATAN(n) ATAN2(n, m) CEIL(n) COS(n) COSH(n) ROUND(n) SIGN(n) SIN(n) SINH(n) SQRT(n) TAN(n) TANH(n) TRUNC(n[,m]) Numeric Functions • EXP(n) • EXP(n) • FLOOR(n) • LN(n) • LOG(m,n) • MOD(n) • POWER(m,n) Database Management -- R. Larson

  8. CHR(n) CONCAT(char1,char2) INITCAP(char) LOWER(char) LPAD(char, n,char2), RPAD(char, n,char2) LTRIM(char, n, cset), RTRIM(char, n, cset) REPLACE(char, srch, repl) SOUNDEX(char) SUBSTR(char, m, n) SUBSTRB(char, m, n) TRANSLATE(char, from, to) UPPER(char) Character Functions returning character values Database Management -- R. Larson

  9. ASCII(char) INSTR(char1, char2[,m, n]) INSTRB(char1, char2[,m, n]) LENGTH(char) LENGTHB(char) Character Function returning numeric values Database Management -- R. Larson

  10. ADD_MONTHS(dt, n) LAST_DAY(d) MONTHS_BETWEEN(d1, d2) NEW_TIME(d, z1, z2) -- PST, AST, etc. NEXT_DAY(d, dayname) ROUND(d, fmt) -- century, year etc. SYSDATE TRUNC(d, fmt) -- century, year, etc. Date functions Database Management -- R. Larson

  11. CHARTOROWID(char) CONVERT(char, dchar, schar) HEXTORAW(char) RAWTOHEX(raw) ROWIDTOCHAR(rowid) TO_CHAR (date, fmt) TO_DATE(char, fmt) TO_NUMBER(char,fmt) TO_MULTIBYTE(char) TO_SINGLE_BYTE(char) Conversion Functions Database Management -- R. Larson

  12. Create Table • CREATE TABLE table-name (attr1 attr-type CONSTRAINT constr1 PRIMARY KEY, attr2 attr-type CONSTRAINT constr2 NOT NULL,…, attrM attr-type CONSTRAINT constref REFERENCES owner.tablename(attrname), attrN attr-type CONSTRAINT constrN CHECK (attrN = UPPER(attrN)); • Adds a new table with the specified attributes (and types) to the database. Database Management -- R. Larson

  13. Types • VARCHAR2(size) • NUMBER(p, s) • LONG -- long char data • DATE -- from 4712BC to 4714 AD • RAW(size) -- binary • LONG RAW -- large binary • ROWID -- row reference • CHAR(size) -- fixed length characters Database Management -- R. Larson

  14. Alter Table • ALTER TABLE table-name ADD COLUMN attr1 attr-type; • ALTER TABLE table-name ADD COLUMN attr1 CONSTRAINT xxx constrainvalue; • ALTER TABLE table-name MODIFY COLUMN attr1 optiontochange; • … DROP COLUMN attr1; • Adds a new column to an existing database table. Database Management -- R. Larson

  15. INSERT • INSERT INTO table-name (attr1, attr4, attr5,…, attrK) VALUES (“val1”, val4, val5,…, “valK”); • OR • INSERT INTO table-name SELECT col1, col2, col3as newcol2, col4 FROM xx, yy WHERE where-clause; • Adds a new row(s) to a table. Database Management -- R. Larson

  16. DELETE • DELETE FROM table-name WHERE<where clause>; • Removes rows from a table. Database Management -- R. Larson

  17. UPDATE • UPDATE tablename SET attr1=newval, attr2 = newval2 WHERE<where clause>; • changes values in existing rows in a table (those that match the WHERE clause). Database Management -- R. Larson

  18. DROP Table • DROP TABLE tablename; • Removes a table from the database. Database Management -- R. Larson

  19. CREATE INDEX • CREATE [ UNIQUE ] INDEX indexname ON tablename (attr1 [ASC|DESC][, attr2 [ASC|DESC], ...]) • Adds an index on the specified attributes to a table Database Management -- R. Larson

  20. System Information In ORACLE • Find all of the tables for a user • SELECT * FROM ALL_CATALOG WHERE OWNER = ‘userid’; • SELECT * FROM USER_CATALOG; (or CAT) • Show the attributes and types of data for a particular table • DESCRIBE tablename; Database Management -- R. Larson

  21. Running commands • Create file with SQL and SQLPlus commands in it. • Use a plain text editor and NOT a word processor (or save as text only) • Give the file the extension .sql • From inside SQLPlus type • START filename Database Management -- R. Larson

  22. Simple formatting in SQLPlus • SET PAGESIZE 500 • SET LINESIZE 79 • PROMPT stuff to put out to screen • TTITLE “title to put at top of results” • COLUMN col_name HEADING “New Name” Database Management -- R. Larson

  23. Outputting results as a file… • SPOOL filename • Commands • SPOOL STOP • File will be created with everything between the SPOOL commands Database Management -- R. Larson

More Related