Oracle | ddl& constraint &DCL&TCL

• DDL (data definition Language), which is used to create/modify/delete various objects of a database, such as tables, views, indexes, synonyms, sequences, and so on. DDL statements mainly include CREATE creation, ALTER modification, TRUNCATE truncation, DROP and deletion.
• Constraint constraint, which is used to restrict the type of data in a table, mainly including non-null constraints, NOT NULL UNIQUE constraints, UNIQUE primary KEY constraints PRIMARY KEY , check constraints CHECK , foreign KEY constraints FOREIGN KEY .
• DCL (Data Control Language), which is used to grant/reclaim access to the database. The DCL statement is GRANT granted and REVOKE recycled.
• TCL (Transaction control Language, transaction control Language) for commit/rollback of transactions. TCL statements include COMMIT commit, rollback ROLLBACK , SAVEPOINT savepoint.

Oracle Database Unlocking Account

alter user <账户名> account unlock;

Oracle Data types

• Number: integer
• Date: Day
• CLOB: Large text data, can save 4G text data
• BLOB: Binary data, can save picture, audio, video, text, Maximum 4G.
• VARCHAR: fixed-length string, standard SQL data type
• VARCHAR2: variable-length string, unique to Oracle.
  • String:‘abc‘
  • String connector:||

Data Pseudo-Columns

• ROWID: Each row in the database has a row address, and the ROWID pseudo-column returns the row address. The ROWID value uniquely identifies a row in the database.
• ROWNUM: Returns a numeric value that represents the order of the rows. By using RowNum, users can limit the number of rows returned by the query.

Ddl

Data definition Language DDL, which is used primarily to create/modify/delete various objects of a database, such as tables, views, indexes, synonyms, sequences, and so on. DDL statements mainly include CREATE creation, ALTER modification, and DROP deletion.

Table: Table creation Tables

CREATE TABLE <表名>(    <列名> <数据类型> [<DEFAULT 默认值>] [<约束>],    <列名> <数据类型> [<DEFAULT 默认值>] [<约束>],    ...    [CONSTRAINT <约束名> <约束类型>(约束列)])

Modify Table

modifying columns in a table

1. Add Columns:

ALTER TABLE <表名> ADD （    <列名> <数据类型> [<DEFAULT 默认值>] [<列级约束>],    <列名> <数据类型> [<DEFAULT 默认值>] [<列级约束>],    ...)

2. Modify the columns:

ALTER TABLE <表名> MODIFY （    <列名> <数据类型> [<DEFAULT 默认值>] [<列级约束>],    <列名> <数据类型> [<DEFAULT 默认值>] [<列级约束>],    ...)

3. Delete columns:

ALTER TABLE <表名> DROP COLUMN <列名>

4. Rename the column:

ALTER TABLE <表名> RENAME <旧名> TO <新名>

To modify a property in a table:

1. Add a constraint:

ALTER TABLE <表名> ADD CONSTRAINT <约束名> <约束类型>(约束列);

2. Delete the constraint:

ALTER TABLE <表名> DROP CONSTRAINT <约束名>;

TRUNCATE TABLE

TRUNCATE TABLE <表名>

Delete a table

1. Delete the table:

DROP TABLE <表名>

2. Truncate the table:

Copying tables

1. Copy the structure and data of the table:

CREATE TABLE <表名> AS     SELECT <目标列表达式>    FROM <表>    WHERE <查询条件>

2. Copy only the structure of the table:WHERE 1 = 2

CREATE TABLE <表名> AS     SELECT <目标列表达式>    FROM <表>    WHERE 1 = 2;

Renaming

RENAME <旧名称> TO <新名称>

Views: view

View: A virtual table that does not occupy physical space.

Create a View

CREATE [OR REPLACE] [FORCE] VIEW <视图名>[(列名1, 列2,...)]AS SELECT...[WITH CHECK OPTION][WITH READ ONLY];

Force: forcing creation of views
With check OPTION: Check constrained view
With Read only: read-only view

Delete a view

DROP VIEW <视图名>

Indexes: Index

Index: Speed up queries

Create an index

CREATE [UNIQUE] INDEX <索引名>ON <表名> (<列名> [ASC | DESC], ... )[INITRANS n][MAXTRANS n][PCTFREE n][TABLESPACE <表空间名>][STORAGE storage][NOSORT]

Unique: Single Index
Initrans: Number of initial transaction entries
Maxtrans: Maximum number of transaction entries
PCTFREE: Percentage of index chunk free space
Nosort: Not sorted

modifying indexes

Rebuilding indexes

ALTER INDEX <索引名> REBUILD;

Merging indexes

ALTER INDEX <索引名> COALESCE;

Delete Index

DROP INDEX <索引名>

Synonyms: Synonym

Synonyms: An alias for a database object that has no change in operation permissions and simplifies object access.

Create synonyms

CREATE [PUBLIC] SYNONYM <表名同义词>FOR <用户>.<表名>

Delete synonyms

DROP [PUBLIC] SYNONYM <同义词>

Sequence: SEQUENCE

Sequence (SEQUENCE): Serial number generator, which automatically generates serial numbers for rows in a table, producing a set of equal-interval values (type number). Takes up no disk space and consumes memory.

Its primary purpose is to generate a table's primary key value, which can be referenced in an INSERT statement, or a query to check the current value, or to increment the sequence to the next value.

Create a sequence

CREATE SEQUENCE <序列名>[INCREMENT BY n][START WITH n][MAXVALUE n | NOMAXVALUE][MINVALUE n | NOMINVALUE][CYCLE | NOCYCLE][CACHE n | NOCACHE];

INCREMENT by: Define Step size
START with: Define sequence initial values
Maxvalue/minvalue: Defines the maximum/minimum value of a sequence
Nomaxvalue: Default Maximum Value
Cycle/nocycle: Indicates whether the sequence is looped after it reaches a qualified value
Cache N: Memory buffer for sequence, generate n ordinal cache in advance, default n=20
NOCACHE: No memory buffering of sequences

two pseudo-columns of a sequence

• Nextval: The next valid value in the sequence
• Currval: Sequence Current value

Modifying a sequence

ALTER SEQUENCE [<用户>.]<序列名>[INCREMENT BY n][START WITH n][MAXVALUE n | NOMAXVALUE][MINVALUE n | NOMINVALUE][CYCLE | NOCYCLE][CACHE n | NOCACHE];

Delete a sequence

DROP SEQUENCE <序列名>

Constraint-constraint

Constraint constraint, which restricts the type of data for a table.

CONSTRAINT <约束名> <约束类型>(约束列)

Non-empty constraint: NOT NULL

Non-empty constraint: NOT NULL

<列名> <数据类型> NOT NULL

Unique constraint: Unique

Unique constraint: Unique

<列名> <数据类型> UNIQUE

CONSTRAINT uk_cloumn UNIQUE(<列名>);

PRIMARY KEY constraint: PRIMARY key

PRIMARY KEY constraint: PRIMARY key, non-null + unique

CONSTRAINT pk_cloumn PRIMARY KEY(<列名>);

Federated PRIMARY Key:

CONSTRAINT pk_cloumn PRIMARY KEY(<列名1>,<列名2>,...);

CHECK constraints: Check

CONSTRAINT ck_cloumn CHECK(<列名> IN (值1,值2...))

CONSTRAINT ck_cloumn CHECK(<列名> BETWEEN 值1 AND 值2)

FOREIGN KEY constraint: FOREIGN key

Database Security-DCL

Data Control Language DCL, used to grant/Reclaim user/role access to the database, the DCL statement is GRANT granted, REVOKE recycled.

Grant Permission: Grant

GRANT <权限>ON <对象类型> <对象名>TO <用户>[WITH GRANT OPTION]

With Grant OPTION: A permission is propagated that will be granted to another user.

Revoke Permission: REVOKE

REVOKE <权限>ON <对象类型> <对象名>FROM <用户> [CASCADE|RESTRICT]

CASCADE: Cascade
RESTRICT: Limited

Roles: Role

Role: A collection of permissions for a set of database operations.

1. Create a role :

CREATE ROLE <角色名>

2. Authorize the role :

GRANT <权限>ON <对象类型> <对象名>TO <角色>

3. Delegate a role to another role or user :

GRANT <角色1>ON <对象类型> <对象名>TO <角色2>,<用户>

Transaction-TCL

transactions : Used to guarantee data integrity . Consists of a set of DML statements that either all succeed or fail altogether.

Transaction characteristics: ACID

• atomicity (atomicity): A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.
• Consistency (consistency): A transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.
• Isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.
• persistence (Durability): When a transaction is committed, its change to the data in the database should be permanent. The next operation or failure should not have any effect on it.

Transaction lock

Issues caused by transaction concurrency inconsistencies :

• Phantom Read : Transaction T1 Reads a statement that specifies the where condition and returns the result set. At this point the transaction T2 inserts a new row of records and commits, exactly satisfying the T1 's where condition. Then the T1 uses the same criteria to query again, the result set can see T2 inserted records, this new record is fantasy.
• non-repeatable reads : The transaction T1 reads a row of records, and the transaction T2 modifies the record that the T1 just read and commits, and then T1 the query again, which is called non-repeatable reading, unlike the record that was read for the first time.
• Dirty Read : Transaction T1 updated a row of records, did not commit the changes, the T2 read the updated data, and then T1 perform a rollback operation, cancel just the changes, so T2 read the row is invalid, that is, dirty data.

--The transaction lock is introduced to ensure the integrity of the data.

four levels of isolation :

• Serialization
• REPEATABLE READ
• Read Committed
• Read not submitted

Transaction processing-TCL

Transaction control Language Tcl, for commit/rollback of transactions. The TCL statement COMMIT commits, ROLLBACK rolls back, and SAVEPOINT saves points.

Commit: Commit

Transaction commit COMMIT : Permanently saves modifications to the database in the transaction.

• Explicit commit: The SQL statement needs to be actively submitted for database modifications, which can be rollback before being committed. such as DML operations.
• Implicit commit: The SQL statement execution ends Autocommit and cannot be rollback. such as DDL,DCL.

Rollback: ROLLBACK

Rollback ROLLBACK : Cancels changes made to the database in the transaction.

Save point: SavePoint

Rollback to save point

rollback to savepoint <savepoint_name>

Database design Paradigm

The actual design principle: As far as possible to reduce the number of table query , do not have to fully follow the design paradigm.

First paradigm : Make sure that every field in the table is not split.

The second paradigm : Eliminates partial function dependencies, ensuring that each column in the table is related to the primary key (primarily for the Federated primary key).

The third Paradigm : elimination of transfer function dependencies, ensuring that each column is directly related to the primary key, not indirectly.

Oracle | ddl& constraint &DCL&TCL