培训: 001 Lesson 1-15 -- 2001/11/20最新补充: Lesson 14-15

jhmaple

LESSON 2 中的问题：
无ORDER BY时ORACLE缺省用什么样的顺序显示数据？
是按原始存储的顺序？

偶然

应当是的

ctianh

不取出数据的顺序是随机的，同样的语句可能得到不同的顺序结果。

sun

thanks a lot

easyfree

最初由 jhmaple 发布
[B]LESSON 2 中的问题：
无ORDER BY时ORACLE缺省用什么样的顺序显示数据？
是按原始存储的顺序？ [/B]

个人不怎么认为, 不用order by 它的显示次序和rowid还是有关的,之所以同样的语句不同的显示结果,是由于进行了dml操作,比如insert了新的记录等等


个人想法, 看来还得查查资料,找理论依据

easyfree

[php]
Lession6        Subqueries

* You can place the subquery in a number of SQL command clauses:
        WHERE clause
        HAVING clause
        FROM clause of a SELECT or DELETE statement
* Additionally, subqueries can be placed in the
        CREATE VIEW statement,
        CREATE TABLE statement,
        CREATE SNAPSHOT statement,
        UPDATE clause,
        INTO clause of an INSERT statement,
        SET clause of an UPDATE statement.
* A subquery executes once before the main query, and may execute multiple times in correlated subqueries. --相关子查询中执行多次
* Comparison operators fall into two classes: single row (>, =, >=, <, <>, <=) and multiple row (IN, NOT IN) operators.
* The subquery generally executes first, and its output is used to complete the query condition for the main or outer query.
* Subqueries Guidelines
        Enclose a subquery within parentheses.
        Place the subquery after the comparison operator.
        Do not add an ORDER BY clause to a subquery. You can have only one ORDER BY clause for a SELECT statement, and if specified, it must be the last clause in the main SELECT statement.
        Must appear on the right side of the operator.
* Single row subquery: =, >, <, >=, or <=. One common error with subqueries is more than one row returned for a single row subquery.
* Multiple row subqueries: IN
* To produce a nonpairwise comparison in a multiple-column subquery, use a WHERE clause with multiple conditions.  A nonpairwise comparison produces a cross product.
-----------------------------------------------
Lession7        Specifying Variable at Runtime

* In SQL*PLUS: ACCEPT reads a line of user input and stores it in a variable. DEFINE creates and assigns a value to a variable.
* SQL*Plus does not support validation checks on user input.
* &user_variable: SQL*Plus discards a new variable once it is used. －如果使用&&，SQLPLUS将记住使用了&&之后同名变量的值
* With the single ampersand, the user is prompted every time the command is executed.
* Setting SET VERIFY to ON forces SQL*Plus to display the text of a command before and after it replaces substitution variables with values.
* ACCEPT variable [datatype][FORMAT][PROMPT text][HIDE]
        HIDE        suppresses what the user enters, for example a password.
* Both the ACCEPT and DEFINE commands will create a variable if the variable does not exist; these commands will automatically redefine a variable if it exists.
* Use the ACCEPT command to
        Give a customized prompt when accepting user input. Otherwise, you will see the default “Enter value for variable.”
        Explicitly define a NUMBER or DATE datatype variable.
        Hide user input for security reasons.
* The ECHO variable controls whether START and @ commands list each command in a command file as the command is executed.
* To define you wanted variables for every session, modify your login.sql file so that those variables are created at startup.
* Reports can accept a maximum of nine parameters that are named from &1 to &9.
* SQL*Plus retains report parameters and their values until you redefine them, undefine them, or terminate your SQL*Plus session.
* Without the single quotes enclosing the variable value an invalid column name error occurs.
[/php]

easyfree

[php]
Lession8        Overview of data Modeling and Database Design

* System Development Cycle
        1. Strategy and Analysis
        2. Design
        3. Build and Document
        4. Transition
        5. Production
* Entity relationship models are derived from business specifications or narratives.
* Component                 Description
-------------------------------------------------------------------------------------
        Entity                         A thing of significance about which information needs to be known.
        Attribute                 Something that describes or qualifies an entity.
        Relationship         A named association between entities showing optionality or degree.
* One-to-one         Degree of one and only one in both directions. These types are rare, and may really be the same entity, or an attribute
                                of the entity.
  Many-to-one         Degree of one or more in one direction and a degree of one and only one in the other direction. Very common.
  Many-to-many         Degree of one or more in both directions. Very common. Resolve them with an intersection entity.
* First normal form (1NF)                 All attributes must be single-valued and not repeating.
  Second normal form (2NF)                 An attribute must depend upon its entity’s entire unique identifier.
  Third normal form (3NF)                 No non-UID attribute can be dependent upon another non-UID attribute.
* Integrity Constraint                 Type Description
  --------------------------------------------------------------------------------
  Entity                                         No part of a primary key can be NULL and the value must be unique.
  Referential                                 Foreign key values must match a primary key or be NULL.
  Column                                         Values in the column must match the defined datatype.
  User-defined                                 Values must comply with the business rules.
* A primary key consisting of multiple columns is called a composite primary key or a compound primary key.
* A foreign key (FK) is a column or combination of columns in one table that refers to a primary key or unique key in the same table or in another table.
* Database Design Steps
        1. Map the Entities to Tables
        2. Map the Attributes to Columns: Map mandatory attributes, tagged with an asterisk (*), to columns defined as NOT NULL (NN).
        3. Map the Unique Identifiers to Primary Keys: symbol (#) to primary key columns and label the key type as PK.
        4. Map relationship to foreign keys.
-----------------------------------------------
Lession9        Creating Tables

* When create table, you can use $, _, # , number for table name and column name, must start with character (重点)
* Tables can have up to 254 columns and must conform to standard database object naming conventions. -- 8i中没有限制
* A schema is a collection of objects. Schema objects are the logical structures that directly refer to the data in a database. Schema objects include tables, views, synonyms, sequences, stored procedures, indexes, clusters, and database links.
* The default value can be a literal, an expression, or SQL function, such as SYSDATE and USER, but the value cannot be the name of another column or a pseudocolumn, such as NEXTVAL or CURRVAL. The default expression must match the datatype of the column.
* The basic datatypes are character, number, date, and RAW.  --8i中增加了????
* If not refered, default values are available (38 for NUMBER and 1 for CHAR).
* If you do not name your constraint, Oracle7 generates a name with the format SYS_Cn, where n is an integer to create a unique constraint name.
* Null values are allowed if the unique key is based on a single column.
* UNIQUE indexes are created automatically when you create PRIMARY KEY and UNIQUE constraints.
* A foreign key that is part of a primary key cannot be a null value because no part of a primary key can be NULL.
* FOREIGN KEY: Without the ON DELETE CASCADE option, the row in the parent table cannot be deleted if it is referenced in the chil department.
* The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as query conditions, with the following exceptions:
        References to the CURRVAL, NEXTVAL, LEVEL, or ROWNUM pseudocolumns
        Calls to SYSDATE, UID, USER, or USERENV functions
        Queries that refer to other values in other rows
* CHECK constraints can be defined at the column-constraint level or table-constraint level. The constraint syntax can apply to any column in the table, not only on the column on which it is defined.  --表级定义CHECK时，可以对多列组合定义，如：CHECK (A+B=10)
* CTAS = CREATE TABLE AS SELECT
        The column definition can contain only the column name, default value, and integrity constraints, not the datatype or referential integrity constraint.
        Only the NOT NULL constraint is inherited from the subquery table to corresponding columns in the new table.
* 注意定义CONSTRAINTS的语法，在字段级定义时放在“，”前面，在表级定义时，放在最后一个字段定义结束的“，”后面和表定义“）”前面，每个CONSTRAINTS以“，”分开.
* The ENABLE clause can be used in the ALTER TABLE and CREATE TABLE commands.
* The keywords PRIVATE and OR REPLACE are invalid when creating a synonym. Triggers may be enabled using the ENABLE clause in the ALTER TABLE command.
* By creating a public synonym for the table owned by user, you have eliminated the need to qualify an object name with its schema name.  The CREATE PUBLIC SYNONYM statement makes a synonym accessible to all users.  -- 对用户自身拥有的表建立同义词时，需要去除SCHEMA前缀
* In CTAS: The datatypes of the new table need not be defined as Oracle knows what the datatypes of the original table are.  The column names do not have to be specified unless they are to be given a different name than is used in the original table. -- 在CTAS中，不要指定列类型，除非需要更改列名才需要指定列名. The only constraint that is inherited is the NOT NULL constraint. -- 在CTAS中，只有NOT NULL约束被继承
* The size of a table does NOT need to be specified.
[/php]

easyfree

[php]
Lession10        Oracle Data Dictionary

* Whenever the database is in operation, the data dictionary is updated and maintained by the Oracle7 Server. All data dictionary tables are owned by the SYS user.
* The V$ views are initially accessible to the DBA. They are also special in that they are not stored in disk files, but are based on information constantly held in memory. In order for users to view the V$ tables, the DBA must assign them to the MONITORER role. -- V$视图是每次实例启动后驻留内存实事刷新的，只有赋予monitorer角色的用户才能访问V$视图；
* Prefix         Description
  --------------------------------------------------------------------------------------------------------------------------
  USER_         Contains objects owned by the user. For example, views with this prefix allow the user to display information
                          about tables created by the user and privileges granted by the user.
  ALL_                 Accesses objects to which the user has been granted access rights, in addition to objects owned by the user.
  DBA_                 Allows users with the DBA privilege to access any object in the database.
  V$                 Displays database server performance and locking. Initially available only to the DBA.
* Several data dictionary views do not use the prefixes listed above. These include synonyms for views with long names.
  View Name                 Description
  --------------------------------------------------------------------------------------------------------------------------
  DICTIONARY                 Lists all data dictionary tables, views, and synonyms.
  TABLE_PRIVILEGES         Grants on objects for which the user is the grantor, grantee, or owner.
                                  When role or a PUBLIC is grantee
                                The user has granted.
                                That have been granted to the user.
                                That the user owns.
  IND                         Is a synonym for USER_INDEXES.
* To view a description of each column in data dictionary tables and views, query the DICT_COLUMNS view.
* To view all constraints on your table, you can check the USER_CONSTRAINTS table.
* The DICTIONARY view lists all data dictionary views accessible to the user with a brief description of the object in a comment column. You an also reference the synonym for the view, DICT.
* Frequently Accessed Data Dictionary Views
        DICTIONARY
        DICT_COLUMNS
        USER_OBJECTS
        USER_CONSTRAINTS
        USER_CONS_COLUMNS
*
ALL_OBJECTS                 displays all of the objects to which the user has access to.
ALL_COL_PRIVS_MADE         displays all of the grants on columns that the user owns or that the user has granted.
USER_OBJECTS                 displays only the objects owned by the user.
USER_TABLES                 displays only the tables owned by the user.
USER_VIEWS                displays only the views owned by the user.
DICTIONARY                 this view provides descriptions of the data dictionary tables and views that are accessible to the user.

[/php]

880583

SQL buffer 有多大的限制 ?

NVL 的使用若要用字串取代原null 的值,得用
    TO_CHAR 轉型NVL ( TO_CHAR(price),' NOT DISPLAY')

ruby_lili

1、Lession6    Subqueries:
* To produce a nonpairwise comparison in a multiple-column subquery, use a WHERE clause with multiple conditions.  A nonpairwise comparison produces a cross product.

对于nonpairwise comparison和multiple-column subquery，我还是不太理解，版主能不能详细的讲讲A nonpairwise comparison produces a cross product？

2、Lession7    Specifying Variable at Runtime
* In SQL*PLUS: ACCEPT reads a line of user input and stores it in a variable. DEFINE creates and assigns a value to a variable.

我觉得其中的DEFINE的定义应更正如下：（参考oracle8英文教材）
DEFINE:create a CHAR datatype user variable

3、* Without the single quotes enclosing the variable value an invalid column name error occurs.
我觉得不应该是所有的variable value都要be enclosed by single quote吧？应该是character and date values must be enclosed by single quotation marks.