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

Oracle Series III: Use of Tables,(Constraints) Indexes, Sequences, and Views

1. Create, delete and modify tables

Create tables: Oracle main data types VARCHAR2, NUMBER, DATE, CLOB(large text), BLOB(picture, music, movie)

If you can only copy the structure of a table, but not the contents, you can add an impossible condition, such as

SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1>2)

For example, create the table Person as follows:

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(30),

age NUMBER(3),

birthday DATE,

sex VARCHAR(2) DEFAULT 'M'

);

If you find that you need to add a specific column, such as the address column, after creating the table, you can use the ALTER TABLE command

SQL> ALTER TABLE person ADD(address VARCHAR2(20));

Modify column properties in a table

SQL> ALTER TABLE person MODIFY(address VARCHAR2(30));

In database program development, table structure is rarely modified, and the ALTER TABLE command is not provided in IBM DB2.

Provide the RENAME command in Oracle to rename tables

SQL> RENAME person to personer;

In Oracle, if you want to empty the data of a table, but you can't roll it back, release the resource immediately.

SQL> TRUNCATE TABLE personer;

(Difference from Deleted: The former cannot be rolled back, the latter can be rolled back)

(Different from DROP TABLE: the former deletes the content, the latter deletes the table)

II. Table constraints

Constraint classification: primary key constraint, unique constraint, check constraint, non-null constraint, foreign key constraint

Syntax: CONSTRAINT Constraint Name Constraint Type (Field Name)

Add constraints as follows:

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(30) NOT NULL,

age NUMBER(3) CHECK(age BETWEEN 0 AND 150),

birthday DATE,

sex VARCHAR(2) DEFAULT 'M' ,

CONSTRAINT person_pid_pk PRIMARY KEY(pid),

CONSTRAINT person_name_uk UNIQUE(name),

CONSTRAINT person_sex_ck CHECK(sex IN('M','F'))

);

The above constraints can be named automatically or manually

Now I'm going to add another table, using a master-foreign key constraint

CREATE TABLE book(

bid NUMBER PRIMARY KEY,

bname VARCHAR(20),

bprice NUMBER(5,2),

pid VARCHAR2(18),

CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)

);

At this time, if you want to delete the person table, an error will occur. At this time, you can use mandatory deletion means.

DROP TABLE person CASCADE CONSTRAINT; however, this practice is generally not used

If a record is added to the person and book tables and a record is deleted from the person table, assume that the pid of the record is

Book table reference, then there will be a delete error. If you want to delete data in a table automatically when deleting records in the corresponding word table, you can use cascading deletion operations.

(Summary: The main table data is deleted, and the slave table data is deleted, under the condition of primary external constraints)

CREATE TABLE book(

bid NUMBER PRIMARY KEY,

bname VARCHAR(20),

bprice NUMBER(5,2),

pid VARCHAR2(18),

CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE

);

Add constraint syntax as follows:

ALTER TABLE table name ADD CONSTRAINT constraint name constraint type (constraint field);

Constraint type naming convention:

PRIMARY KEY: Primary key field_PK

UNIQUE: Field_UK

CHECK: Field_CK

FOREIGN KEY: Parent-Child Segment_Subfield_FK

SQL> ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);

SQL> ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);

SQL> ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);

SQL> ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;

Delete constraint syntax is as follows:

ALTER TABLE table name DROP CONSTRAINT constraint name;

SQL> ALTER TABLE person DROP CONSTRAINT person_age_CK;

SQL> ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;

Case:

Primary Key Constraint Add Delete

1. Create a primary key constraint while creating a table

create table accounts ( accounts_number primary key, accounts_balance number );

create table accounts ( accounts_number , accounts_balance number, constraint yy primary key(accounts_number) );

2. Delete existing primary key constraints in the table

SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='ACCOUNTS';

Find the primary key name ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;

ALTER TABLE ACCOUNTS DROP CONTRAINT yy;

ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER)

*****ROWNUM EXAMPLES ******

SQL> SELECT ROWNUM,empno,ename FROM emp;

ROWNUM appears in auto-numbered form

ROWNUM SELECT ROWNUM,empno, NAME FROM emp WHERE ROWNUM SELECT * FROM (

SELECT ROWNUM m,empno,ename FROM emp

WHERE ROWNUM=5

Set operations: Oracle provides three types of set operations: union, intersection and difference.

UNION: Combines multiple query results into one query result without duplicate values

UNION ALL: contains duplicate values unlike UNICON

INTERSECT: Returns the same part of multiple query results

MINUS: Returns the difference of two query results (the different parts)

Verify UNION and UNION ALL

SQL> CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5 records)

SQL> SELECT * FROM emp UNION SELECT * FROM emp20; (14 records)

SQL> SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19 records)

Verify MINUS and INTERSECT

SQL> SELECT * FROM emp MINUS SELECT * FROM emp20; (9 records)

SQL> SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5 records)

View operations in Oracle

1. Create a view

CREATE VIEW view name AS subquery This subquery is a very complex statement

SQL> CREATE VIEW empv20 AS

SELECT empno,ename,job,hiredate FROM emp

WHERE deptno=20;

2. Query View

SQL> SELECT * FROM empv20;

3. Delete View

SQL> DROP VIEW empv20;

If you want to modify the view, you must delete the view first. Oracle provides a replacement command for users to modify the view conveniently.

CREATE OR REPLACE View Name AS Subquery

Views can encapsulate complex queries such as department name, department headcount, average wage, and minimum wage employees

SQL> CREATE OR REPLACE VIEW empv20 AS

SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(

SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) min FROM emp

GROUP BY deptno) ed,emp e

WHERE d.deptno=ed.deptno AND e.sal=ed.min;

It is inconvenient to write such a long SQL statement every time in the development, so you can build it into a view, above the red department.

4. If you are updating a view, which should not contain real data, do the following

SQL> UPDATE empv20 SET deptno=30 WHERE empno=7369;

It is found that the view has been updated normally, because the number 7369 in the emp table has been modified to 30, so it is conditional to create the view.

SQL provides two important parameters

WITH CHECK OPTION: Cannot update view creation conditions

SQL> CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH CHECK OPTION;

Creation criteria cannot be updated, but other fields can still be updated

SQL> UPDATE empv20 SET ename='wilson' WHERE empno=7369;

So you can use the second condition of the view: create a read-only view.

SQL> CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY;

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.