Oracle's previous Oracle Learning notes (8-15)

First, Database modeling

Ii. Establishment of the table

Third, Database dictionary

Iv. DML statements

V. views

Vi. Index

Seven, sequence

VIII. DDL statements

Lesson 8 Overview of Data Modeling and Database Design
-----------------------------------------------------------------------------
Database modeling

E-r Chart

Entity entities
Object entity
Business Entities

Relationship relationship (this refers to the entity to the entity)

In the ER diagram:
# unique
* Non-empty

#* only non-empty

O without any constraints

One-to-one
One-to-many

May is

Must be

The paradigm to follow when building a table

First paradigm: A field in the table that can be the master key (non-null and unique)

Second paradigm: The values of each column in the table are indivisible

Third paradigm: A column in a table cannot depend on other non-primary key columns (that is, the value of a non-primary key column does not affect values on other columns)

Lesson 9 Creating Tables
------------------------------------

Build table

CREATE TABLE Table name (
Column-Name 1 data Type column-level constraints,
Column-Name 2 data type column-level constraints,
Column-Name 3 data Type column-level constraints,
Column name 4 data type column-level constraint
);

CREATE TABLE Table name (
Column-Name 1 data Type column-level constraints,
Column-Name 2 data type column-level constraints,
Column-Name 3 data Type column-level constraints,
Column-Name 4 data Type column-level constraints,
Table-level constraint 1,
Table-level Constraints 2
);

Note: column-level constraints and table-level constraints can be non-writable.
CREATE TABLE Table name (
Column Name 1 data type,
Column Name 2 data type,
Column name 3 data type,
Column name 4 data type
);

A constraint is what the value above the column should follow.

PRIMARY KEY constraint
FOREIGN KEY constraints
Unique constraint
Non-null constraint
Check Constraint

Common Types of data:

Number
Varchar2
Char
Date

Build table
CREATE TABLE Student (
ID number primary Key,
Name VARCHAR2 ($) is not NULL,
Age number,
Birthday Date
);

Delete a table
drop table name;
drop table student;

CREATE TABLE Customer (
ID number primary Key,
Name VARCHAR2 () NOT NULL,
Age number,
Email varchar2 (+) Unique,
Gender Char Check (gender in (' F ', ' m '))
);

drop table customer;

Constraint keywords
-------------------------
When you build a table, you can give the constraint a name, the rule of the name is usually: Table name _ column name _ Constraint name

If the constraint does not have a name, then the system will give the constraint a default name, but the default name we do not know what the table in which column is the constraint.

CREATE TABLE Customer (
ID number constraint CUS_ID_PK primary key,
Name VARCHAR2 () NOT NULL,
Age number,
Emile Varchar2 (+) Unique,
Gender Char Check (gender in (' F ', ' m '))
);

View constraints created by the current user
Select Constraint_name
From User_constraints;

References keywords
-----------------------
A foreign key constraint that refers to the primary key of a table.

CREATE TABLE T_order (
ID number primary Key,
Price number is not NULL,
customer_id number references customer (ID)
);

This time directly delete the Customer table will be an error
Because the primary key ID for this table has been referenced in the past by the T_order table, the foreign key
drop table customer;

1. You can delete the order table before deleting the Customer table
2. Use the Cascade constraints command:
DROP TABLE customer cascade constraints;
This allows the customer table to be deleted directly, and also removes the foreign key constraint declared by the order table.

To use table-level constraints:
CREATE TABLE Customer (
ID number,
Name VARCHAR2 () NOT NULL,
Age number,
Email varchar2 (100),
Gender Char,
Constraint CUS_ID_PK primary key (ID),
Constraint Cus_email_un Unique (email),
Constraint cus_gender_ck Check (Gender in (' F ', ' m '))
);

CREATE TABLE T_order (
ID number,
Price number is not NULL,
CUSTOMER_ID number,
Constraint ORDER_ID_PK primary key (ID),
Constraint ORDER_CUS_ID_FK foreign KEY (customer_id) references customer (ID)
);

NOTE: NOT NULL constraints can only be declared as column-level constraints

1. Table-level constraints and column-level constraints are not written in the same location
2.not NULL constraints cannot be declared with table-level constraints
3. Table-level constraints and column-level constraint declaration syntax are slightly different
4. Be sure to use table-level constraints if the constraint you want to declare is a federated primary key, Union foreign key, Union unique

A primary key for a declaration
Union foreign keys and federated unique constraints are similar.
CREATE TABLE T_order (
ID number,
Price number is not NULL,
CUSTOMER_ID number,
Constraint ORDER_ID_PK primary KEY (Id,price),
Constraint ORDER_CUS_ID_FK foreign KEY (customer_id) references customer (ID)
);

Lesson Orcale Data ditionary
-------------------------------------
Database dictionary
Role: To help users understand some of the current database information or object information and user information

View views

User-owned objects are stored in the view at the beginning
The view at the beginning of all holds objects that the user has permission to view
The DBA starts with a view that holds all the objects in the database
V$ The starting view holds some performance property data for the database to run

DICTIONARY
Table_privileges
IND

Use the DESC command to get information about the columns in these views, and then go to query the mix view to find the information you want

Lesson11 manipulating Data
------------------------------------
DML statements

Insert
Update
Delete
Commit
SavePoint
Rollback

DML statement: This statement modifies data in a database table
Insert Update Delelte

CREATE TABLE Student (
ID number primary Key,
Name VARCHAR2 () NOT NULL,
Age Number
);

This means that all data is inserted into the student table
The order in which the data is inserted is the order of the columns when the table is built
INSERT into student values (1, ' Tom ', 20);

This allows you to control the insertion of data into those columns in the table, and the order in which the data is inserted
INSERT into student (Id,name) VALUES (2, ' Jerry ');

When DML statements are executed, transactions are generated and transactions are not committed, so the changes I make to the data are not valid.
Transaction commits, the previous modifications to the data are in effect (saved to the hard disk)
Transaction rollback, all previous modifications to the data were undone.

For example:
1 Modifying Data 1
2 Modifying Data 2
3 Modifying Data 3
4 Modifying Data 4
5 Committing a transaction

When the first line of DML statements is executed, the transaction is generated, and then the DML executed by the 2nd 3 4 line is placed in the transaction resulting from the first row, and then the transaction is committed, and all operations in the operation are in effect.
If line fifth is a rollback of the transaction, then all operations inside the transaction are undone.

Each transaction is independent and does not affect each other, and all operations within a transaction are either successful at the same time or fail (undone) at the same time.

Note: When committing a transaction, the command-ROLLBACK,DDL statement that rolls back the transaction also commits the transaction, which is equivalent to the effect of the commit.

For example:
1 Modifying Data 1
2 Modifying Data 2
3 Modifying Data 3
4 Modifying Data 4
5 Executing DDL statements
6 rollback

It is useless to roll back the sixth line here because line fifth executes the DDL statement and the transaction has been committed.

CREATE TABLE Student (
ID number primary Key,
Name VARCHAR2 () NOT NULL,
Age Number
);

Insert statement
--------------------------
This means that all data is inserted into the student table
The order in which the data is inserted is the order of the columns when the table is built
INSERT into student values (1, ' Tom ', 20);

This allows you to control the insertion of data into those columns in the table, and the order in which the data is inserted
INSERT into student (Id,name) VALUES (2, ' Jerry ');

INSERT into student (Id,name,age) VALUES (&id, ' &name ', &age);

UPDATE statement
-----------------
Modify all data in a table
Update student
Set name = ' Terry ';

Modify some data
Update student
Set name = ' Terry '
where id = 2;

If the transaction is not committed, then this data will be locked, the other terminal can not modify it, if modified, then the card is there, unless the transaction commits, the other terminal can be modified normally.

Delete statement
-----------------
Delete all data in a table
Delete from student;

Add a Where condition limit to delete some data
Delete from student
where id=2;

SavePoint rollback Point
------------------

DML Statement 1
SavePoint A
DML Statement 2
SavePoint B
DML Statement 3

Rollback to A/b

At this time, the transaction can be rolled back to the specified location through this rollback point, and if the rollback point is not specified, the transaction will be rolled back (rollback the entire transaction).

Lesson View
-----------------------
Views View:
Role 1: Hide important data from a table
Role 2: Instead of some long SQL statements

Create a View
CREATE VIEW V_student
As
SELECT *
From student
where id = 2;

View the contents of a view
SELECT *
From V_student;

Views are divided into two categories:
Simple view:
If there is no GROUP BY statement in the SQL represented by the view, and there is no set of functions, only one table is queried, then such a view is a simple view.
Complex views
A view is a complex view if there is a group BY statement in the SQL represented by the view, or if there are groups of functions, or if you are querying more than one table.

The difference between a simple view and a complex view:
1. Simple views can be modified, and complex views cannot be modified.
2. The data in the original table can be modified through a simple view, and the original data cannot be modified through a complex view.

Lesson Index
-----------------------------
Role: Improve the efficiency of querying data in tables

Index is built on top of the column

Conditions:
1. The data in this column is often used as a condition for querying data
2. The data on this column is not changed frequently

Attention:
1. Columns with a PRIMARY KEY constraint, or columns with unique constraints, are indexed by default.
2. In a table, the index is not the more the better
3. By adding a suitable index to one of the columns in the table, the efficiency of the query can be reflected only when the amount of data is large.
4. Indexing is a success, then the index has a system to manage, we can not control.

Types of indexes:
B-tree
Bitmap
Reverse order
Function

To format an index:

CREATE INDEX Index name
On table name (column name);

Delete Index
Drop index name;

Lesson Creating sequences sequence
-------------------------------
Role: Help us generate a value for the primary key column (non-null unique)

To create a sequence:
Generally do not need to set the properties of sequence, use the default way to create it.
Create sequence sequence name;

If you need to set a property, then add the following statement.
[INCREMENT by n] Each time you take out the value plus how much
[Start with n] Initial value starting from several
[{MAXVALUE n | Nomaxvalue}] Maximum value
[{MINVALUE n | Nominvalue}] Minimum value
[{CYCLE | Nocycle}] Whether to loop after the maximum value
[{CACHE n | NOCACHE}] The number of values to put in the cache each time.

Example:
Create sequence my_seq;

Get the next value in the sequence
This value corresponds to the current sequence, which is definitely non-null and unique
Select My_seq.nextval
from dual;

What is the current value in the query sequence?
Select My_seq.currval
from dual;

The sequence is often used when inserting a primary key column in a table
INSERT into student (Id,name,age) VALUES (My_seq.nextval, ' Tom ', 20);
INSERT into student (Id,name,age) VALUES (my_seq.nextval, ' &name ', &age);

Drop sequence sequence name;

Synonym synonyms
Synonym

Student

My_stu

Create a synonym for table student
Create synonym My_stu
for student;

Because the general user does not create a public synonym
permissions, all we need to log in to the database as a DBA to create
Sqlplus "/as sysdba"
Create public synonym My_stu
For tom.student;

Grant SELECT on My_stu to public;//authorization

Does a new user have at least one permission to log in to the database?
Create session

Lesson altering Tables modifying table structure
------------------------------------------------
Changes to the table structure:

Alter

Add a new column to the table
ALTER TABLE student
Add birthday date;

Delete a column of a table
ALTER TABLE student
Drop column birthday;

Modify the name of the table:
Rename student to Mystudent;

To modify the type of a column in a table
ALTER tablestudent
MODIFY (name VARCHAR2 (500));

To invalidate a constraint:
Must know the name of the constraint
ALTER TABLE S_emp
DISABLE CONSTRAINT S_EMP_ID_PK CASCADE;

Let the expiration constraint take effect again
ALTER TABLE S_emp
ENABLE CONSTRAINT S_EMP_ID_PK;

RowNum Pseudo-Column
-----------------------------------
RowNum Features:
1.rownum is a hidden line number
2.oracle is unique, other databases are not
3. Within each table or query results are present

RowNum Operating Features:
1. Ability to < any number,
2. Can only >0, greater than the number is not 0 words cannot get any data
3. Only = 1, equals the number is not 1 words can not get any data

Select Rownum,last_name
From S_emp
where rownum>0;

Select Rownum,last_name
From S_emp;

I have hundreds of thousands of data address, see is hundreds of thousands of data, must be able to batch query
I would like to provide a sample of data for customer inquiries, but for the time being can not show him specific digital grades
I want to be able to hide these numbers or replace them with * numbers.

Data table format: 123 Lane No. 456, Nanjing Road, Room 7890
Hope to display after the query: Nanjing Road * * * * * * * * * * room

How do I write the SELECT statement?

Select Replace replace (replace (replace (replace (replace (0 ', ' * '), ' 1 ', ' * '), ' 2 ' , ' * '), ' 3 ', ' * '), ' 4 ', ' * '), ' 5 ', ' * '), ' 6 ', ' * '), ' 7 ', ' * '), ' 8 ', ' * '), ' 9 ', ' * ')

Oracle's previous Oracle Learning notes (8-15)