Shulou(Shulou.com)06/01 Report--

Oracle Series "four": design and Analysis of Database

I. the use of sequences

There is an auto-growing column in many database systems. If you want to achieve the auto-growing function in Oracle, you can only rely on the sequence.

1. Create sequence or create any sequence must have the permission to create a sequence.

2. Syntax for creating sequences

CREATE SEQUENCE sequence / / create sequence name

[INCREMENT BY n] / / the sequence value increasing is n. If n is positive, it is incremented. If it is negative, the default is 1.

[START WITH n] / / starting value. Incrementing defaults to minvalue decreasing is maxvalue.

[{MAXVALUE n | NOMAXVALUE}] / / maximum

[{MINVALUE n | NOMINVALUE}] / / minimum

[{CYCLE | NOCYCLE}] / / Loop / No Loop

[{CACHE n | NOCACHE}]; / / allocate and store it in memory

NEXTVAL returns the next valid value in the sequence, which any user can refer to

The current value of the sequence is stored in CURRVAL

NEXTVAL should be specified before CURRVAL, and both should be valid

/ / explain

{

Create creation

Sequence sequence seqEmop sequence name

Increment by step size

Stat with 1 start value

Maxvalue maximum

Minvalue minimum

Cycle cycle nocycle does not cycle

Cache cache Cache SELECT SYSDATE FROM dual

Dual is a virtual table, which is defined by SYS users. You can query SQL > SELECT * FROM tab WHERE TNAME='DUAL' using the following statement

This table is under SYS, but SCOTT users can access it directly through the table name. Normally, we need to use "user name. Table name". This is the role of synonyms.

Create synonyms:

CREATE SYSNONYM synonym name FOR user name. Table name'

For example, define scott.emp as a synonym for emp

SQL > CREATE SYSNONYM emp FOR scott.emp

Delete synonyms

SQL > DROP SYSNONYM emp

Synonyms are only suitable for Oracle databases.

III. User management (*)

Create user: CREATE USER username IDENTIFIED BY password

SQL > CREATE USER test IDENTIFIED BY test123

SQL > GRANT CREATE SESSION TO test

The SESSION right will be created to the test user, after which the user can log in normally, but he does not have the right to create the table

In Oracle, multiple permissions can be defined as a set of roles, which can be assigned to users.

Two main roles are provided in Oracle: CONNECT and RESOURCE, which are assigned to test users.

SQL > GRANT CONNECT,RESOURCE TO test

The administrator changes the user's password:

SQL > ALTER USER test IDENTIFIED BY hello

In a general system, the password can be changed when the user logs in for the first time, using the following ways

ALTER USER user name PASSWORD EXPIRE

SQL > ALTER USER test PASSWORD EXPIRE

At this point, the user will be prompted for the old password and the new password

Lock and unlock the user

SQL > ALTER USER test ACCOUNT LOCK

SQL > ALTER USER test ACCOUNT UNLOCK

At this point, if you want to query the table EMP under the SCOTT user and find that you do not have permission, execute the following command

SQL > GRANT SELECT,DELETE ON scott.emp TO test

Command to revoke permissions:

SQL > REVOKE SELECT,DELETE ON scott.emp FROM test

The concept of nested tables (understanding)

Nested table: contains another child table in one table

First specify a type for the nested table, which needs to be defined separately

SQL > CREATE TYPE project_ty AS OBJECT (priod NUMBER (4), proname VARCHAR2 (30), prodate DATE)

SQL > CREATE TYPE project_nt AS TABLE OF project_ty /

This allows you to use project_nt to represent the project_ty type, and now create the department table based on this type

SQL > CREATE TABLE department (

Deptno NUMBER (2) PRIMARY KEY

Dname VARCHAR2 (50) NOT NULL

Projects project_nt

)

NESTED TABLE projects STORE AS project_nt_tab_temp

If you want to insert data

SQL > INSERT INTO department (deptno,dname,projects)

VALUES (1) technology, project_nt (

Project_ty (1001 recording ERPPhoneSYSDATE)

Project_ty (1002)))

Query department table, you can return multiple items

SQL > SELECT * FROM department

If you need to see all the items in a department, query the nested table

SQL > SELECT * FROM TABLE

(SELECT projects FROM department WHERE deptno=1)

Update the project name with number 1001

SQL > UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro

SET VALUES (pro) = project_ty ('1001) WHERE pro.proid=1001

The concept of variable arrays: an upgraded version of nested tables. (useful enough to understand)

The concept of database paradigm

The first paradigm: all the information is concentrated on one table, such as

CREATE TABLE person (

Pid NUMBER (4) PRIMARY KEY

Name VARCHAR2 (50)

Info VARCHAR (200))

There will be problems with the first paradigm, such as creating a student selection schedule.

CREATE TABLE selectCourse (stuno VARCHAR2 (50))

Stuname VARCHAR2 (50)

Stuage NUMBER

Cname VARCHAR2 (50)

Grade NUMBER

Credit NUMBER)

Not only is all the course information redundant, but there are also the following problems:

1. If no students take the course, the course will disappear.

2. The course itself is numbered. According to the above design, the course number must be repeated.

3. To change the course information, you need to modify many records

Modify using the second normal form

CREATE TABLE student (

Stuno VARCHAR2 (10) PRIMARY KEY

Stuname VARCHAR2 (20)

Stuage NUMBER)

CREATE TABLE course (

Cid NUMBER (5) PRIMARY KEY

Cname VARCHAR2 (20)

Credit NUMBER)

CREATE TABLE selectCourse (

Stuno VARCHAR2 (50)

Cid NUMBER (5)

Grade NUMBER, set the primary-foreign key relationship)

The above design solves the following problems:

1. If students do not take courses, the courses will not disappear.

2. Update the course schedule directly when updating the course

3. All the relationships are reflected in the relation table.

Here is the completion of the many-to-many relationship

Use the third paradigm:

Design a student table according to the second paradigm, including student number, name, age, university, college address, college telephone number, etc.

CREATE TABLE collage (

Cid NUMBER (40) PRIMARY KEY

Cname VARCHAR2 (50)

Caddress VARCHAR2 (20)

Ctel VARCHAR2 (20))

CREATE TABLE student (

Stuno VARCHAR2 (50) PRIMARY KEY

Stuname VARCHAR2 (50)

Stuage NUMBER

Cid NUMBER (4), establish primary-foreign key association)

The above is a clear one-to-many relationship.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.