Oracale Data processing and constraints

Data processing other DML statements:

2. INSERT
   ①insert into table [(column [, Column ...])]
   VALUES (value [, value ...]);
   Use this syntax to insert only one piece of data into a table at a time.
   You cannot use this method to insert multiple data into a table at the same time
   ② How to create Scripts
   Example: INSERT into Departments
   (department_id, Department_name, location_id)
   VALUES (&department_id, ' &department_name ', &location);
   Similar to the address characters in the C language
   ③insert into Sales_reps (ID, name, salary, commission_pct)
   SELECT employee_id, last_name, salary, commission_pct
   From Employees
   WHERE job_id like '%rep% ';
   Copy data from other tables, depending on where clause to determine whether to insert data, and what data to insert

4. UPDATE
   UPDATE table
   SET column = value [, column = value, ...]
   [WHERE condition];
   You can update multiple data at once: the same use where clause to control
   You can also use subqueries in an UPDATE statement

6. DELETE
   DELETE from table
   [WHERE condition];
   Similarly, you can use subqueries in DELETE statements

8. Transaction control
   Transactions: A set of logical operations units that transform data from one state to another.
   Composition
   One or more DML statements
   A DDL (data definition language– language) statement
   Perform:
   Start with execution of the first DML statement
   End with one of the following:
   COMMIT or ROLLBACK statement
   DDL statements (auto-commit)
   User session ends normally
   System Abnormal termination
   Transaction control:
   COMMIT
   ROLLBACK
   SavePoint (ROLLBACK to SavePoint)

Constraints

Constraint is a rule
**not NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
check**
You can create a constraint while creating a table
You can modify the constraint after you create the table

Constraints can be defined at the table-level or column-level
You can view constraints through a data dictionary
Table-level constraints and column-level constraints:

1. Scope of Action:
   ① column-level constraints only work on one column
   column [CONSTRAINT constraint_name] Constraint_type,

   ② table-level constraints can be used on multiple columns (of course, table-level constraints Also
   Can function on a column)
   Column,...
   [CONSTRAINT Constraint_name] Constraint_type
   (column, ...),

2. Defined by: The column constraint must follow the definition of the column, and the table constraint is not associated with the column, but is defined separately.

3. A non-null (NOT NULL) constraint can only be defined on a column

UNIQUE:
A unique constraint that allows multiple null values to occur: null.

FOREIGN KEY:
FOREIGN KEY: Specify columns in a child table at the table level
REFERENCES: Columns that are marked in the parent table
On delete CASCADE (cascade delete): When a column in the parent table is deleted, the corresponding column in the child table is also deleted
On DELETE SET null (cascade NULL): The corresponding column in the child table is empty

CHECK:
Can be understood as a custom constraint

Adding and removing constraints

1. Add constraint
   ALTER Table Table
   ADD [CONSTRAINT CONSTRAINT] Type (column);
   Add or remove constraints, but cannot modify constraints
   Effective or invalid constraint
   Add not NULL constraint to use MODIFY statement
2. Delete Constraint
   ALTER TABLE Employees
   DROP CONSTRAINT EMP_MANAGER_FK;
3. Invalid constraint
   ALTER TABLE Employees
   DISABLE CONSTRAINT EMP_EMP_ID_PK;
   Table altered.
4. Activation constraints
   ALTER TABLE Employees
   ENABLE CONSTRAINT EMP_EMP_ID_PK;

Query Constraints

1. Query constraints:
   Querying the Data dictionary view user_constraints
   SELECT constraint_name, Constraint_type,
   Search_condition
   From User_constraints
   WHERE table_name = ' EMPLOYEES ';
2. columns for query definition constraints:
   Querying the Data dictionary view User_cons_columns
   SELECT Constraint_name, COLUMN_NAME
   From User_cons_columns
   WHERE table_name = ' EMPLOYEES ';

Practice, I will write a separate article. Because learning SQL is mainly the practice, the most difficult part is the sub-query, need more practice, here to you say I think more efficient contact method, is to pay attention to the writing format, for example:

--Query the department information with the lowest average wage and the average wage of the departmentSELECTD.*, (SELECT avg(Salary) fromEmployeesWHEREdepartment_id = d.department_id) fromDepartments DWHEREdepartment_id = (SELECTdepartment_id fromEmployeesGROUP  bydepartment_id having avg(Salary) = (SELECT min(avg(salary)) fromEmployeesGROUP  byDEPARTMENT_ID))

This writing format facilitates analysis.

Oracale Data processing and constraints