?Oracle SQL
The Relational Database Concept
Dr. E.F.Codd proposed(建议,提议) the relational model for database systems in 1970.
The relational model contains the following components:
Collection of objects or relations.
Set of operations to act on the relations.
Data integrity for accuracy(精确度) and consistency(一致性).
A relational database is a collection of relations or a two-dimensional tables.
Each table is composed of(由…组成) rows and columns.
row --- tuple(元组)
column --- attribute(属性)
You can manipulate([m?'nipjuleit]操纵) data in the rows by executing Structured Query Language(SQL) commands.
Relational Database Terminology(术语)
Each row of data in a table is uniquely identified by a primary key(PK).
You can logically relate information from multiple tables using foreign keys(FK).
Database Objects
Object Description
------- ----------------------------
Table Basic unit of storage composed of rows and columns.
View Logically represents subsets of data from one or more tables.
Sequence Generates primary key values.
Index Improves the performance of some queries.
Synonym Alternate name for the an object.
Program unit Procedure, function, or package of SQL and PL/SQL statements.
What is Oracle?
Oracle is a database company that offers products and services for information technology needs.
Oralce's RDBMS is called the Oracle 10g Server.
G means gridding.
Designated storage areas
Fast data retrieval
Database security
Distributed dbs and integrity across networks
Client-Server configurations
SQL: A command language for communications withe Oracle 10g server.
SQL*PLUS: An Oracle tool that recognizes and executes SQL and PL/SQL statements.
PL/SQL: An Oracle procedural language that extends SQL by adding application logic.
SQL Commands
Data retrieval
SELECT
Data manipulate language(DML)
INSERT, UPDATE, DELETE
Data definition language(DDL)
CREATE, ALTER, DROP, RENAME, TRUNCATE
Transaction control language(TCL)
COMMIT, ROLLBACK, SAVEPOINT
Data control language(DCL)
GRANT, REVOKE(撤回)
--------------------------------------------------------------------------
Lesson 1 Selecting Rows
Writing SQL commands
1. Commands can be on one or many lines.
2. Tabs and indents(缩进) can be used for readability.
3. Abbreviations([?,bri:vi'ei??n]缩写) and splitting of words are not allowed.
4. Commands are not case sensitive.
5. Commands are entered into(进入) the SQL buffer.
Simplest SELECT statement contains the following two clauses(条款, 子句):
SELECT clause
Asterisk(*)(['?st?risk]) indicates(表明) all columns
FROM clause
SELECT dept_id, last_name, manager_id
FROM s_emp;
1. List the columns in the SELECT clause.
2. Separate columns by using a comma(逗号).
3. Specify columns in the order you want them ot appear.
Column Label Defaults
Label defualt justification:
Left: date and charact
er data
Right: numeric data
Label default display is uppercase.
Arithmetic([?'riθm?tik]算术) Expressions
Create expressions on NUMBER and DATE datatypes by using operators:
Add +
Subtract -
Multiply *
Divide /
Operator Precedence(['presid?ns]优先)
1. Multiplication and division take priority over addition and subtraction.
take precedence over比……重要；优先于
2. Operator of the same priority are evaluated([i'v?ljueit]估价；求…的值) from left to right.
3. Parentheses([p?'renθisi:z]圆括号) can be used to force prioritized evaluation and to clarity(清楚) statements.
Column Aliases(['eili?s])
1. A column alias renames a column heading.
2. Especially useful with calculations.
3. Immediately follows column name:
Optional AS keyword between column name and alias.
4. Double quotation([kw?u'tei??n]) marks are required if an alias contains spaces, special characters, or is case-sensitive.
Concatenation([k?n,k?ti'nei??n]连结) Operator
1. The concatenation operator is represented by two vertical bar(||).
2. Links columns or character strings to other columns.
3. Create a resultant column that is a character expressions.
Literal(['lit?r?l] 文字的) Character String
1. A literal is a character, expression, or number included in the SELECT list.
2. Date and character literal values must be enclosed within single quotations marks.
3. Each character string is output once for each row returned.
Managing NULL Values
1. NULL is a value that is unavailable, unassigned(未赋值的), unknown, or inapplicable.
2. NULL is not the same as zero or space.
3. Arimethic expressions containing a null value evaluate to NULL.
NVL Function
1. Convert NULL to an actual value with NVL.
2. Datatypes to use are date, character, and number.
3. Datatypes must match.
NVL(start_date, '01-JAN-95)
NVL(title, 'No Title Yet')
NVL(salary, 1000)
Duplicate Rows
1. The defaut display of queries is all rows including duplicate rows.
2. Eliminate duplicate rows by using DISTINCT in the SELECT clause.
3. DISTINCT applies to all columns in the SELECT list.
4. When DISTINCT is applied to multiple columns, the result represents the distinct combination of the columns.
Display Table Structure
1. The SQL*Plus DESCRIBE command displays the structure of a table(column names, NOT NULL columns, and datatypes)
2. NOT NULL columns must contain data.
3. Example cloumn datatype and length.
NUMBER(p,s)
VARCHAR2(s)
DATE
CHAR(s)
SQL*Plus Editing Commands
1. A[PPEND] text
2. C[HANGE] /old/new
3. CL[EAR] BUFF[ER]
4. DEL
5. I[NPUT] text
6. L[IST] n
7. n text
SQL*Plus File Commands
1. SAVE filename
2. GET filename
3. START filename
4. @ filename
5. EDIT filename
6. SPOOL filename
7. EXIT
Column Command: Syntax(['sint?ks])
1. CLE[AR]- clears any column foramts.
2. FOR[MAT] format - changes the display of the column using a format model.
3. HEA[DING] text - sets
the column heading.
4. JUS[TIFY] {align} - aligns the colunm heading to be left, center, or right.
Column Format Models
1. Character and date columns
An Sets the display width of n
2. Number columns
9 Single zero-supperssion digit
0 Enforces leading zero
$ Floating dollar sign
L Local currency
. Position of the decimal point
, Thousand separator
--------------------------------------------------------------------------
Lesson 2 Sorting & Limiting Selected Rows
The ORDER BY Clause
1. Sort rows with the ORDER BY clause.
2. ASC - ascending order, default.
3. DESC - descending order.
4. ORDER BY clause is last in SELECT command.
5. You can sort by expressions or aliases.
6. You can order by position to save time.
7. You can sort by multiple columns.
8. You can sort by a column that is not int the SELECT list.
Limiting Rows Selected
Restrict the rows returned by using the WHERE clause.
The WHERE clause follows the FROM clause
Conditions consist of the following:
Column name, expression, constant
Comparison operator
Literal
Character Strings and Dates
1. Character strings and dates are enclosed within single quotation marks.
2. Number values are not enclosed within quotation marks.
3. Character values are case-sensitive.
4. The default date format is 'DD-MON-YY'.
Negating Expressions
Logical Operators
!=, <>, ^=
BETWEEN and IN SQL Operators
Use the BETWEEN operator to test for values between, and inclusive of, a range of values.
LIKE SQL Operator
1. You can use the LIKE operator to perform wildcard searches of valid search string values.
2. Search conditions can contain either literal characters or numbers.
"%" denotes(表示) none or many characters.
"_" denotes one character.
3. You can use the ESCAPE identifier to search for "%" or "_"
where last_name LIKE '\_a%' escape '\'
--------------------------------------------------------------------------
Lesson 3 Single Row Functions
Character Functions
LOWER Converts to lowercase
UPPER Converts to uppercase
INICAP Converts to initial capitalization
CONCAP Concatenates values
SUBSTR Returns substring
LENGTH Returns number of characters
NVL Converts a null value
Number Functions
ROUND Rounds value to specified decimal
TRUNC Truncates value to specified decimal
MOD Retures remainder of division
SYSDATE is a function returning date and time.
DUAL is a dummy table used to view SYSDATE.
Date Function
MONTHS_BETWEEN Number of months between two dates
ADD_MONTHS Add calender months to date
NEXT_DAY Next day of the date specified
LAST_DAY Last day of the month
ROUND Round to date at midnight
TRUNC Remove time portion(部分) from date
Conversion Functions
TO_CHAR converts a number or date string to a character string.
TO_NUMBER converts a character string containing digits to a number.
TO_DATE converts a character string of a date to a date value.
TO_CHAR Function with D
ates
1. Must be enclosed in single quotation marks and is case-sensitive.
2. Can include any valid date format element.
3. Has an fm element to remove padded blanks or suppress([s?'pres]消零)leading zeros.
4. If you see a string of pound signs(#), the format model does not contain enough digits to the left of the decimal.
--------------------------------------------------------------------------
Lesson 4 Displaying Data from Multiple Tables
Join methods
Equijoin
Non-equijoin
Outer join
Self join
Set operator(union, minus, union all, intersect)
Create a Cartesian product if you omit(省略) the WHERE clause.
Speed up(加速) db access with table aliases.
Use aliases to set up self joins.
--------------------------------------------------------------------------
Lesson 5 Group Function
GROUP BY divides rows into smaller groups.
HAVING further restricts the result groups.
Group Functions
AVG()
COUNT()
MAX()
MIN()
STDDEV()--标准偏差
SUM()
VARIANCE()--方差
You can use MAX and MIN for any datatype.
You can use AVG and SUM against columns that can store numeric data.
COUNT(expression) returns the number of non-null rows.
--------------------------------------------------------------------------
Lesson 6 Subqueries
The subquery executes once before the main query.
The result of the subquery is used by the main outer query.
--------------------------------------------------------------------------
Lesson 7 Specifying Variables at Runtime
Single Ampersand(['?mp?s?nd]&) Substitution Variable
1. The user can restrict rows bynamically using substitution variable prefixed by single ampersand.
2. The user is prompted every time the command is executed.
3. Character and date values must be enclosed by single quotation marks.
4. Place single quotation marks around the ampersand and variable name so that the user does not have to enter the single quotation marks.
ACCEPT Command
1. Create a customized prompt when accepting user input.
2. Explicitly defines a NUMBER or DATE datatype variable.
3. Hides user input for security reasons.
UNDEFINE Command
1. A variable remains defined until
You use the UNDEFINE command to clear it.
You exit SQL*Plus.
2. Confirm undefined variables with the DEFINE command.
--------------------------------------------------------------------------
Lesson 8 Overview of data modeling and Database design
Model of system in client's mind-->Entity model of client's model-->Table model of entity model-->Tables on disk
Entity Relationship Modeling Concepts
1. Entity: A thing of significance about which information needs to be known
Examples: customers, sales representatives, orders
2. Attribute: Something that describes or qualifies an entity
Examples: name, phone, identification number
3. Relationship: An association between two entities.
Examples: orders and items, customers and sales representatives
Uni
que Identifier(UID)
Primary marked with(#)
Secondary marked with(#)
Mandatory marked with(*)--must be
Optional marked with(o) --may be
Recursive Relationships
1. Define a relationship between an entity and itself as a recursive relationship.
2. Represent that relationship with a "pig's ear".
UID bar - relationship is part of the entity's unique identifier.
Normalization(范式)
1. First normal form: All attributes must be single-valued.
2. Second normal form: An attribute must depend upon its entity's entrie UID.
3. Third noraml form: No non-UID attribute can be dependent upon another non-UID attribute
Primary Key
1. A primary key(PK) allows no duplicate values and connot be NULL.
2. Each row is uniquely identified by a column or set of columns(composite primary key)
3. A candidate(候选) key can also serve as PK.
Foreign Key
1. A foreign key(FK) is a column or combination of columns in one table that refers to a PK or unique key(UK) in the same table or in another table.
2. FKs are based upon data values and are purely logical.
3. The value must either match the value in the related column or be NULL.
4. If an FK is part of a PK, then it cannot be NULL.
--------------------------------------------------------------------------
Lesson 9 Creating tables
Constraint Guidelines
1. Name a constraint or the server can generate a name by using the sys_cn (n is number) format.
2. Create a constraint
At the same time as the table is created.
After the table has been created.
3. Define a constraint at the column or table level.
The NOT NULL Contraint
1. Ensures that null values are not permitted for the column
2. Is defined at the column-constraint level
The UNIQUE Constraint
1. Designates(指定) a column or combination of columns so that no two rows in the table can have the same value for this key.
2. Allows null values if the UNIQUE key is based on a single column.
3. Is defined at either the table or column-constraint level
4. Automatically creates a UNIQUE index
The PRIMARY KEY Constraint
1. Creates a primary key for the table; only one primary key is allowed for each table.
2. Enforces uniqueness of columns.
3. Dose not allow null values in any part of the primary key.
4. Is defiend at either the table or column constraint level.
5. Automatically creates a UNIQUE index.
The FOREIGN KEY Constraint
1. Designates a column or combination of columns as a foreign key.
2. Establishes a relationship between the primary or unique key in the same table or between tables.
3. Is defined at either the table or column constarint level.
4. Must match an existing value in the parent table or be NULL.
FOREIGN KEY Constraint Keywords
FOREIGN KEY
Defines the column in the child table at the table constraint level
REFERENCES
Identifies the table and column in the parent table
ON DELETE CASCADE
Allows deletion in the parent table and deletion of the dependent rows in the child table.
The CHECK Constraint
1. Defines a condition that each rows must satisfy
2. Expression not allowed
References to pseudo columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
Calls to SYSDATE, UID, USER, or USERENV functions
Queries that refer to other values in other rows
3. Is defined at either the table or column constraint level.
Do not forget that only the NOT NULL constraint is copied.
--------------------------------------------------------------------------
Lesson 10 Oracle Data Dictionary
Querying the Data Dictornary
1. Four classes of views(prefixes)
USER objects owned by user
ALL objects user has access rights
BDA all database objects
V$ server performance
2. Other views
DICTIONARY
TABLE_PRIVILEGES
IND
List all data dictionary views accessible to the user.
select *
from dictionary;
Display the structure of the user_objects view.
desc user_objects;
Display the names of tables that you own.
select object_name
from user_objects
where object_type='TABLE';
Search for data dictionary tables on specific topics in the COMMENTS column of the dictionary table.
select *
from dictionary
where lower(comments) like '%grant%';
Query the user_constraints table to view all constraint definitions and names.
example: verify(核实) the constraints on the s_em table
select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints
where table_name='S_EMP';
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'COUNTRY_ASIA';
--------------------------------------------------------------------------
Lesson 11 Manipulating Data
INSERT Adds a new row to the table
UPDATE Modifies existing rows in the table
DELETE Removes existing rows from the table
COMMIT Makes all pending changes permanent.
SAVEPONT Allows a rollback to that savepoint marker.
ROLLBACK Discards all pending data changes.
ACID
Atomicity, consistency, isolation and durability are together known as the ACID criteria.
1. Atomicity: Serveral operations are grouped together as a single indivisible unit.
2. Consistency: Any transaction works with a consistent set of data and leaves the data in a consistent state when the transaction completes.
3. Isolation: a particular transaction shouldn't be visible to and shouldn't influence other concurrently running transactions.
4. Durability: Once a transaction completes, all changes made during that transaction become persistent and aren't lost even if the system subsequently fails.
Implicit Transaction Processing
An automatic commit occurs under the following circumstances:
A DDL command is issued, such as CREATE.
A DCL command is issued, such as GRANT.
A normal exit from SQL*Plus, without explicity issuing COMMIT or ROLLBACK.
An automatic rollback occurs under an abnormal termination of SQL*Plus or a system failure.
State of the Data Before COMMIT or ROLLBACK
1. The previous state of
the data can be recovered because the database buffer is affected(影响).
2. The current user(session) can review the results of the DML operations by using the select statement.
3. Other users(session) cannot view the results of the DML statements by the current user.
4. The affacted rows are locked; other users cannot change the data within the affected rows.
State of the Data after COMMIT
1. Data changes are written to the db.
2. The previous data is permanently lost.
3. All users can view the results.
4. Locks on the affected rows are released; those rows are available for other users to manipulate.
5. All savepoint are erased.
State of the Data after ROLLBACK
1. Discard(放弃) all pending changes.
2. Data changes are undone.
3. Previous state of the data is restored.
4. Locks on the affected rows are released.
SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.
--------------------------------------------------------------------------
Lesson 12 Altering Tables and Constraints
create table test1(
id number(10),
name varchar2(20)
);
--add column,The new column becomes the last column.
alter table test1 add (age number(3));
--drop column
alter table test1 drop column age;
--modify column
alter table test1 modify (name varchar2(50));
--adding a primary key constraint, table level
alter table test1
add constraint test1_id_pk primary key(id);
--adding a unique key constraint, table level
alter table test1
add constraint test1_id_u unique(name);
--drop a constraint
alter table test1 drop constraint test1_id_u;
Dropping a Table:
1. All data in the table is deleted.
2. Any pending transactions are committed.
3. All indexes are dropped.
4. The CASCADE CONSTRAINTS option removes dependent integrity constraints.
5. You cannot rollback this command.
Truncating a Table
1. Removes all rows from a table.
2. Releases the storage space used by that table.
3. Is a DDL command
4. Cannot rollback row removal when using TRUNCATE.
5. Alternatively, remove rows by using the DELETE command.
Adding Comments to a Table
1. You can add comments to a table or column by using the COMMENT command.
comment on table s_emp
is 'Employee Information';
comment on column s_https://www.doczj.com/doc/be13969008.html,
is 'just name';
2. To clear the comment, use the empty string.
3. Comments can be viewed through the following data dictionary views:
all_col_comments
user_col_comments
all_tab_comments
user_tab_comments
--------------------------------------------------------------------------
Lesson 13 Creating Sequence
Create sequence mike_test_id
increment by 1
start with 10
maxvalue 9999999
nocache
nocycle;
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
NEXTVAL returns the next available sequence value.
CURRVAL obtains the current sequence value.
insert into mike_test(
id, name) values(mike_test_id.NEXTVAL, 'borjigen');
drop sequence mike_test_id
--------------------------------------------------------------------------
Lesson 14
Advantages of views
1. Restrict db access
2. Simplify queries
3. Data independence
4. Different appearances for the same data
The subquery can contain complex SELECT syntax.
The subquery cannot contain an ORDER BY clause.
create view country_asia
as select *
from countries
where region_id=3
Denying DML Operations
Ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.
create or replace view country_eu(country_id, country_name, region_id)
as select country_id, country_name, region_id
from countries
where region_id=1
with read only;
The USER_VIEWS data dictionary table contains the name of the view and the view definition.
select view_name, text
from user_views;
Removing a View:
Remove a view without losing data because a view is based on underlying tables in the db.
--------------------------------------------------------------------------
Lesson 15 Creating Indexes
How are indexes created?
1. Automatically
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
2. Manually
Users can create non-unique indexes on columns to speed up access time to the rows.
Create index mike_test_name_idx on mike_test(name);
Guidelines to create an index
1. The column is used frequently in the WHERE clause or in a join condition.
2. The column contains a wide range of values.
3. The column contains a large number of not null values.
4. Two or more columns are frequently used together in a WHERE clause or join condition.
5. The table is large and most queries are expected to retrieve less than 2-4% of the rows.
Do not create an index if
1. The table is small.
2. The columns are not often used as a condition in the query.
3. Most queries are expected to retrieve more than 2-4% of the rows.
4. The table is updated frequently.
--------------------------------------------------------------------------
Lesson 16 Controlling User Access
The DBA has high level system privileges.
Create new users
Remove users
Remove tables
Backup tables
The DBA creates users by using CREATE USER SQL command.
CREATE USER scott
IDENTIFIED BY tiger;
Once a user is created, the DBA can grant privileges to the user by executing the GRANT command.
CREATE SESSION
CREATE TABLE
CREATE SEQUENCE
CREATE VIEW
CREATE PROCEDURE
GRANT create table, create sequence, create view to scott;
Changing Your Password
1. When the user account is created, a password is initialized.
2. Users can change their password by using the ALTER USER command.
ALTER USER scott IDENTIFIED BY lion;
Grant QUERY privileges on your s_emp table;
GRANT select on s_emp to sue, rich;
Grant UPDATE privileges on specific columns to users and ro
les.
GRANT update(name, region_id) on s_dept to scott, manager;
Revoking Object Privileges
1. Use the REVOKE command to revoke privileges granted to other users.
2. Privileges granted to others through the WITH GRANT OPTION will also be revoked.
REVOKE select, insert
ON s_dept
FROM scott;
Creating a Synonym
1. Simplify access to objects by creating a synonym
2. another name for an object.
3. Refer to a table owned by another user.
4. Shorten lengthy objects names
Create a shortend name for the DEPT_SUM_VU view.
CREATE SYNONYM d_sum
FOR dept_sum_vu;
Public synonyms are created and dropped by the DBA only.
CREATE PUBLIC SYSNONYM s_dept
FOR alice.s_dept;
Remove a synonym
DROP SYNONYM s_dept;
Login sqlplus as dba
Michael@Michael$ sqlplus 'as sysdba'
Create user scott and grant privileges
create user scott identified by tiger;
grant resource.connect to scott;
Logout sqlplus then export file
Michael@Michael$ exp userid=Michael/Michael full=y file=inner_notify.dmp
Logout sqlplus then improt file
Michael@Michael$ imp userid=Michael/Michael full=y file=Michael.dmp