SQL Foundation of Oracle Database (ii)

First, constraints? 1. Overview of Constraints

Binding effect:

(1) Define rules

(2) Ensure completeness: including the accuracy and reliability of the data. To ensure that data does not go wrong, or to minimize errors.

Type of constraint:

(1) Non-null constraints

(2) PRIMARY KEY constraint

(3) FOREIGN KEY constraint (right FOREIGN KEY constraint) (left FOREIGN KEY constraint)

(4) Unique constraint

(5) Check constraints

? 2, non-null constraints

(1) Set a non-null constraint when creating a table
Syntax:CREATE TABLE table_name (column_name datatype not NULL,...);
(2) Adding a non-null constraint when modifying a table
Syntax:ALTER TABLE table_name MODIFY column_name datatype not NULL;
Note: Fields that add non-null constraints cannot have null data present.
(3) Removing non-null constraints when modifying a table
Syntax:ALTER TABLE table_name MODIFY column_name datatype NULL;

When adding a non-null constraint when modifying a table, delete the original data first:
Delete from UserInfo;
And then modify the table:
ALTER TABLE UserInfo modify username varchar2 () not null;

? 3, PRIMARY KEY constraint

role : Ensure uniqueness of each row of data in the table.
The field where the primary KEY constraint is set, the field must be non-null and the value is unique;
Only one PRIMARY KEY constraint can be set in a table;
A PRIMARY KEY constraint can have multiple fields (this is called a federated primary key or a composite primary key).

to set a PRIMARY KEY constraint when creating a table :

(1) Setting the PRIMARY KEY constraint ( column level ) when creating a table
Syntax: CREATE TABLE table_name (column_name datatype PRIMARY KEY,...);

When a primary key constraint is created, it automatically adds a non-null constraint.

(2) Setting primary KEY constraints ( table-level ) when creating tables
Syntax: CREATE TABLE table_name (column_name datatype,...,CONSTRAINT pk_name PRIMARY KEY (column_name1,...));

to set a PRIMARY KEY constraint when modifying a table :

(1) Adding a PRIMARY KEY constraint when modifying a table
Syntax:ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column_name1,...);
Note: If there is content in the table, the data in the field where you want to add the primary key must be unique.

(2) Change the constraint name

Syntax: ALTER TABLE table_name RENAME CONSTRAINT old_pk_name to New_pk_name;

(3) Disabling primary KEY constraints

Syntax: ALTER TABLE table_name disable| ENABLE CONSTRAINT pk_name;

(4) Delete primary KEY constraint

Syntax: ALTER TABLE table_name DROP CONSTRAINT pk_name;
or alter TABLE table_name DROP PRIMARY KEY [CASCADE]; [CASCADE] is optional and is used in cascading operations.

? 4, FOREIGN KEY constraints

to add a foreign key constraint when creating a table : FOREIGN KEY constraints (representing relationships between master and slave tables)

(1) Setting foreign KEY constraints ( column level ) when creating tables
Syntax: CREATE TABLE table1 (column_name datatype REFERENCES table2 (column_name),...); --where table2 is the primary table and Table1 is the table

　　　　Note : 1) When setting a FOREIGN key constraint, the Main table field must be the primary key
2) The corresponding field in the master/slave table must be the same data type (the FOREIGN KEY constraint field and the primary key field of the main table)
3) The value of the foreign key field from the table must be from the value of the corresponding field in the primary table, or a null value

(2) Setting foreign KEY constraints ( table-level ) when creating tables
Syntax: CREATE TABLE table_name (column_name datatype,...,CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table_name2 (column_name)[on DELETE CASCADE]);
　　　　Note : 1) table_name2 is the main table name;
2) [on delete CASCADE] indicates the meaning of cascade deletion;
3) The name of the constraint is also unique.

To add a foreign key constraint when modifying a table:

Syntax: ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES table_name2 (column_name) C6>[on DELETE CASCADE];

　　Note : 1) table_name2 is the main table name;
2) [on delete CASCADE] indicates the meaning of cascade deletion;
3) The name of the constraint is also unique.

5. Remove the PRIMARY KEY constraint

(1) View constraints
Syntax: Select Constrain_name,constraint_type,status from user_constraints where table_name= ' XXXX ';
-the status P represents the primary key R for the foreign key

　　

(2) Disabling FOREIGN KEY constraints
Syntax: ALTER TABLE table_name disable| ENABLE CONSTRAINT fk_name;

(3) Delete foreign KEY constraint
Syntax: ALTER TABLE table_name DROP CONSTRAINT pk_name;

? 6. Unique Constraint

Function: Uniqueness of the field values that guarantee the setting of unique constraints

　　the difference between a unique constraint and a PRIMARY KEY constraint :
(1) The field of the primary KEY constraint is non-empty, and the unique constraint only allows a value of NULL
(2) A table can have only one primary KEY constraint, and a unique constraint can have multiple

　　to set a unique constraint when creating a table :

(1) Set a unique constraint ( column level ) when creating a table
Syntax:CREATE TABLE table_name (column_name datatype UNIQUE,...);

　　

(2) Set a unique constraint ( table-level ) when creating a table
Syntax:CREATE TABLE table_name (column_name datatype,...,CONSTRAINT u_name UNIQUE (column_name));
Note: 1) The name of the unique constraint (u_name) must be unique;
2) If you want to set more than one unique constraint field, you need to write the clause (CONSTRAINT u_name unique (column_name)) repeatedly.

　　　　

　　To add a unique constraint when modifying a table:

Syntax:ALTER TABLE table_name ADD CONSTRAINT u_name UNIQUE (column_name);

　　

　　To disable a unique constraint:
Syntax: ALTER TABLE table_name disable| ENABLE CONSTRAINT u_name;

To delete a unique constraint:
Syntax: ALTER TABLE table_name DROP CONSTRAINT u_name;

? 7, check the constraints

effect : Makes the values in a table meaningful.
Note: Check constraints can also have multiple in a table.

set a CHECK constraint when creating a table :
(1) Set CHECK constraint ( column level ) when creating a table
Syntax: CREATE TABLE table_name (column_name datatype CHECK (expressions),...);
Note: Expressions is a conditional expression

　　

(2) Set CHECK constraints ( table level ) when creating tables
Syntax: CREATE TABLE table_name (column_name datatype,...,CONSTRAINT c_name CHECK (expressions));

to add a check constraint when modifying a table :

Syntax:ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (expressions);

　　

To disable CHECK constraints :

Syntax: ALTER TABLE table_name disable| ENABLE CONSTRAINT Ck_name;

To delete a check constraint :

Syntax: ALTER TABLE table_name DROP CONSTRAINT ck_name;

? 8. Constraint Summary

• Non-null constraint not NUL (disallow insertion of Word blank)
• PRIMARY KEY constraint PRIMARY key (only one per table can be composed of multiple fields)
• FOREIGN KEY constraint FOREIGN key (involves two tables, the constraint field matches the outer field, the type is the same, the data must be in {appearance data})
• Unique constraint unique (guaranteed data uniqueness, can be composed of multiple fields)
• Check the constraint check (to ensure that the data values are safe and reliable, and within the allowable range)

to set a constraint when creating a table :

Only non-null constraints are column-level settings and non-null constraints do not have names, and other constraints can be column-level settings or table-level settings.

to add a constraint when modifying a table :

(non-NULL) ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME datatype NOT null;

Only non-null constraints are added to the constraint in a way that modifies the field, and the other is identical.

change the name of the constraint :

A non-null constraint is not a name,

So other constraints can be modified by the following syntax: ALTER TABLE table_name RENAME CONSTRAINT old_name to new_name; data dictionary (user_constraints) is available for viewing.

To Disable a constraint :

Non-null constraints do not need to be disabled,

The disable syntax for other constraints is: ALTER TABLE table_name disable| Enable CONSTRAINT constraint_name; can be viewed through a data dictionary (user_constraints).

To delete a constraint :

The deletion of a non-null constraint is also removed by modifying the field, that is, adding a null after the field type,

Other can be through the syntax: ALTER TABLE table_name drop CONSTRAINT constraint_name;

The primary KEY constraint can also be removed by syntax: ALTER TABLE table_name DROP CONSTRAINT PRIMARY key;

Can be viewed through a data dictionary (user_constraints).

Practice Knowledge Points:

Oracle requires only one primary key in a table, so there can be only one PRIMARY KEY constraint.

A FOREIGN KEY constraint involves two tables, such as the Department table dept, which includes the department number (DEPTNO), and the Department in Employee table employee (Deptnum) as the foreign key reference in the Deptno field in the Department table.

A unique constraint requires that each field cannot have duplicate values and can have null values, but only one of the null values.

Second, the query statement

? Query overview

• Query the basic statement;
• Formatting in sql*plus (formatting the results of the query);
• Querying all fields in the table and specifying fields;
• Set the alias for the field (the name of the field is not changed for the result of the query);
• operators and expressions;
• Use the operator in the SELECT statement;
• a query with conditions;
• Fuzzy query;
• Scope query;
• Sort the results of the query;
• The use of Case...when statements (conditional statements);
• Use of the Decode function

? 1, basic query statements

Syntax: SELECT [DISTINCT] column_name1,... | * FROM table_name [WHERE conditions];

That is: Select [Distinct] column name. From table name (does not display duplicate data using distinct)

? 2. Formatting in Sql*plus

Function: Use with SELECT statement, set the format of the query results display, does not modify the database table fields.

(1) Setting the field display style
Syntax:COLUMN column_name HEADING new_name;
Note: COLUMN can be abbreviated as COL

　　

(2) Setting formatting styles for fields
Syntax: COLUMN column_name FORMAT DataFormat;
Note: If it is a character-type value, you can only set the length it displays. Where A is followed by a number indicating the length of the string, example: A10 represents a string that displays a 10-bit length, and a number with ' 9 ' for one, example: 999.9 means that the integer digit is three bits, and the decimal place is one bit. If the length of the setting exceeds the maximum length of the data, it is displayed with ' # '. You can also add ' $ ' to the front of the number, for example: $999.9 the results displayed after the query are preceded by a ' $ ' character.

　　

　　

　　

　　

(3) Clear Formatting
Syntax: COLUMN column_name CLEAR;

? 3. Querying all fields in the table and specifying fields

(1) Query all fields:

Syntax: SELECT * FROM table_name;

　　

Set Format:

　　

(2) Query the specified field:
Syntax: Select Column1, ... from table_name;

　　

? 4. Set the alias for the field

Syntax: SELECT column_name as New_name,... from table_name;
Where: As can be omitted, with a space to separate the original field name and the new field name.
Comments:
1) column (col) col_name heading new_name;//set the name of the query result for the field
2) column (col) col_name format datatype;//sets the type range of the query result for the field
3) column (col) col_name clear; Clears the set of cells

? 5. Operators and expressions

(1) expression = operand + operator
(2) operands in Oracle can be constants, variables, and fields in a table
(3) Operators in Oracle:

Arithmetic operator (+-*/);

Comparison operators (> < = >= <= <>); (If the 5>2 result is true, the Boolean type)

Logical operator (And,or, not)

　　　　　　　　

use the operator in the SELECT statement :

(1) Using arithmetic operators

　　

All wage results add 200, but do not affect the data in the table. (After the data in the table is arithmetic, the data values are not actually changed, the change requires the UPDATE statement)

(2) Using comparison operators

　　

Note: Not equal to use! = or <> indicates

? 6. Conditional query (where statement)

(1) Single condition query

(2) Multi-conditional queries (using logical operators to join expressions)

Example: Query employee whose name is AAA, or whose salary is greater than 2000

It is important to note that operators have precedence.

• Precedence of logical operators: Descending in order of not, and, or
• Comparison operators have higher precedence than logical operators

? 7. Fuzzy Query

(1) Use of wildcard characters (' _ ', '% '):

A ' _ ' represents a character, and a '% ' represents 0 to more than any character.

(2) Use the like query:

? 8. Scope Query

(1) Between......and

Between ~ and ~ Query interval contains two range limit values;
The not between ~and~ query does not include values in this range;

Range queries, such as finding wages from 800 to 2000 (including 800 and 2000):
SELECT * from users where salary between and 2000;
If you want to not include 800 and 2000 of these two numbers, you can write:
SELECT * from the users where salary not between and 2000;

(2) In/not in

SELECT * from the users where username in (' AAA ', ' BBB ');
SELECT * from the users where username not in (' AAA ', ' BBB ');

? 9. Sort the results of the query

Syntax: SELECT [DISTINCT] column_name1,... | * FROM table_name [WHERE conditions] ORDER by column_name1 desc| Asc...
Where Desc is sorted in descending order, ASC is ascending sort, by default is ascending sort

? 10. Case...when Statement

Basic syntax: ([when followed by the condition of the judgment, then the condition is really the thing shown])
Grammar 1:case column_name When value1 then Result1, ... [ELSE Resultn] END;
Grammar 2:case when column_name=value1 then Result1, ... [ELSE Resultn] END;
Note: value1, RESULT1 need to add single quotes, to represent strings, when multiple when...then ... When used, the middle is separated by a space, can not be separated by commas

Syntax 1 Example:

Syntax two examples:

? 11, the use of decode function

With the previous case ... when: Then (else) end similar to

Syntax: DECODE (COLUMN_NAME,VALUE1,RESULT1,..., defaultvalue);
(Decode and case ... when: Then (else) end similar, but the decode condition is specified as = =)
Explanation: Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value) from Talbename;

SQL Foundation of Oracle Database (ii)