Oracle Learning Notes

Describe a table with DESC employees
Filter Duplicate department SELECT distinct DEPARTMENT_ID from Employees
Three ways to alias: 1, Space 2, plus as 3, "" (the alias of multiple words must be separated by a space, or by an underscore)
Condition matching Date: where To_char (date, ' yyyy-mm-dd ') = ' 1997-06-07 '
Default format: where date = ' July-June-1997 '
Like:where name like '%\_% ' escape ' \ ' (%: 0 or more characters, _ denotes any one character, escape means escape keyword)
ORDER BY salary ASC (Ascending) desc (descending)
Multi-level ordering: ORDER by salary asc,name ASC (in ascending order by name in the same salary)
To delete a duplicate record of a field in a table:
Delete from Job_grades J1
where rowID <> (select min (rowid) from Job_grades j2 where j1.grade_level = J2.grade_level);
If you delete the 15th row in the natural order of the table, the following statement can be implemented.
(rowID is a pseudo-column of the database, and the database automatically establishes a ROWID column for each table when the table is established.)
Used to uniquely identify a row of records. ROWID is the actual physical address where each record is stored, and access to the record is based on ROWID. ）
Delete from tab where rowid= (
Select II from (select ROWNUM Nn,rowid II from tab where rownum<=15) where nn=15);

Equivalent connection:
Select Employee_id,e.department_id,department_name,city
from Employees e,departments d,locations L
Where e.department_id = d.department_id and d.location_id = l.location_id
or
Select Employee_id,e.department _id,department_name,city
from Employees e
joins departments d on e.department_id = d.department_id
Join Locatio ns L on d.location_id = l.location_id
Equivalent connection alternate mode:
Select Last_name,department_id,department_name
from Employees join Departments
--using (DEPARTMENT_ID) (provided that the column names of the two tables and the data types of the columns are the same)

Non-equivalent connection
Select Employee_id,last_name,salary,grade_level
From Employees E,job_grades J
where e.salary between J.lowest_sal and J.highest_sal
Left outer connection (which table to which the "+" number is placed on the empty side)
Select E.employee_id,e.last_name,d.department_name
From Employees e,departments D
where e.department_id = d.department_id (+)
Left outer (right outer, full) connection
Select Employee_id,e.department_id,department_name
From Employees E
Left outer
--right outer
--full
Join Departments D on e.department_id = d.department_id
Self-connect

increase, deletion and modification of table data:
Adding data to a table
1. Insert into EMP1 ' sysdate ' or
VALUES (1002, ' BB ', to_date (' 1998-08-08 ', ' yyyy-mm-dd '), 20000)
2. Insert INTO EMP1
VALUES (1002, ' BB ', to_date (' 1998-08-08 ', ' yyyy-mm-dd '), NULL)

3, insert into EMP1 (employee_id,last_name,hire_date) Note: Here is only a partial column assignment, there is a non-null constraint must be assigned
values (1004, ' DD ', to_date (' 1990-08-08 ', ' yyyy-mm-dd '))
4, note: Only want to assign the value of a column, the other must be allowed to empty the value of the columns, where the default salary is null (that is, have a non-null constraint must be assigned)
INSERT INTO EMP1 ( employee_id,last_name,hire_date)
VALUES (1005, ' EE ', to_date (' 1996-08-08 ', ' Yyyy-mm-dd ')
5, Windowed insert data
Insert into EMP1 (employee_id,last_name,hire_date,salary)
values (&id, ' &last_name ', ' &hire_date ', &salary)
Insert data based on records from an existing table
INSERT into EMP1 (employee_id,last_name,hire_date,salary)
Select employee_id, Last_name,hire_date,salary
from Employees
where department_id =

Update data:
Update Emp1
Set salary = 22000
where employee_id = 179
Update 114 employees work and pay the same as employee number NO. 206
1. Select Employee_id,job_id,salary
From Employees1
where employee_id in (114,205)
2. Update Employees1
Set job_id = (
Select job_id from employees1 where employee_id = 205
), salary = (
Select salary from employees1 where employee_id = 205
)
where employee_id = 114

Adjust the same as the job_id for EMPLOYEE_ID 200 employees
Employee's department_id is a department_id for employees with employee_id of 100
Update Employees1
Set department_id = (select department_id from employees1 where employee_id = 100)
where job_id = (select job_id from employees1 where employee_id = 200)
Errors in data integrity that are prone to occur are as follows:
Update Employees
Set department_id = 55
where department_id = 100; The problem appears in the table, unit 55th, it doesn't exist.
Remove the Department ID from the Employees table that contains the public character in the Departments department name
Delete from Employees1
where department_id = (select department_id from departments where department_name like '%public% ')

Increase:
Insert INTO ...
VALUES (...)

Insert INTO ...
Select...from...where ...
Change:
Update ...
Set ...
where ...
By deleting:
Delete From ...
where ...

Transaction:
Commit
SavePoint A;
Rollback to SavePoint A;
While the user is working on the table, no other user is able to manipulate the current table until the commit has been made.
Change information for employee number 108th: Turn its salary into the highest wage in your department, job becomes the lowest average wage in the company
Update Employees
Set salary = (select Max (Salary)
From Employees
where department_id = (
Select department_id
From Employees
where employee_id = 108)
Group by department_id),
job_id = (select job_id
From Employees
Having avg (Salary) = (
Select min (avg (Salary))
From Employees
Group by job_id)
Group by job_id)
where employee_id = 108
Delete the employee with the lowest wage in the Department of employee number 108th
Delete FROM Employees
where employee_id = (
Select employee_id
From Employees
where department_id = (select department_id
From Employees
where employee_id = 108)
and salary = (select min (Salary)
From Employees
where department_id = (select department_id
From Employees
where employee_id = 108)
)
)
Can be optimized into:
Delete from Employees E
where department_id = (select department_id
From Employees
where employee_id = 108)
and salary = (select min (Salary)
From Employees
where department_id = e.department_id
)
Create a table with constraints not NULL and unique: There is no conflict between multiple null,null that are assigned to it in a unique constraint
CREATE TABLE Emp3 (
--Column-level constraints:
ID number (TEN) constraint Emp3_id_uk unique,
Name VARCHAR2 (constraint) emp3_name_nn NOT NULL,
Email varchar2 (20),
Salary number (10),
--Table-level constraints:
Constraint emp3_email_uk unique (email)
)
PRIMARY KEY constraint: the ability to uniquely determine a record, as well as table-level constraints and column-level constraints, primary key is not only not null and unique
CREATE TABLE Emp4 (
ID number (TEN) constraint EMP4_ID_PK primary key,
Name VARCHAR2 (constraint) emp4_name_nn NOT NULL,
Email varchar2 (20),
Salary number (10),
Constraint emp4_email_uk unique (email)
)
Or
CREATE TABLE Emp4 (
ID Number (10),
Name VARCHAR2 (constraint) emp4_name_nn NOT NULL,
Email varchar2 (20),
Salary number (10),
Constraint emp4_email_uk Unique (email),
Constraint EMP4_ID_PK primary key (ID)
)
FOREIGN KEY constraint: (note: When inserting data in emp6, it is not possible to insert data records that are not department_id in the Departments table.) In addition, the column referenced by the foreign key must have at least one unique constraint)
CREATE TABLE EMP6 (
ID Number (10),
Name VARCHAR2 (constraint) emp6_name_nn NOT NULL,
Email varchar2 (20),
Salary number (10),
DEPARTMENT_ID Number (10),
Constraint emp6_email_uk Unique (email),
Constraint EMP6_ID_PK primary key (ID),
Constraint EMP6_DEPT_ID_FK foreign KEY (DEPARTMENT_ID) references departments (DEPARTMENT_ID)
)
Inserts the DEPARTMENT_ID (primary key) data that exists in the Departments table into the table
INSERT INTO EMP6
VALUES (1002, ' AA ', null,10000,20)

On delete set null: (Cascade NULL: The corresponding column in the child table is empty)
ON DELETE cascade: (Cascade Delete: When a column in the parent table is deleted, the corresponding column in the child table is also deleted)
CREATE TABLE EMP7 (
ID number,
name VARCHAR2 () constraint emp7_name_nn not null,
email varchar2 (20), Br>salary number,
department_id number (TEN),
constraint emp7_email_uk unique (email),
constraint emp7_id _PK primary key (ID),
Constraint EMP7_DEPT_ID_FK foreign key (DEPARTMENT_ID) references departments (DEPARTMENT_ID) on Delete Set null
)
Check constraint: For example, constrain the range of Wages
CREATE TABLE EMP8 (
ID number,
name VARCHAR2 () constraint EMP8 _name_nn NOT NULL,
email varchar2 (+),
Salary number (TEN) constraint emp8_salary check (salary>1500 and Salary <30000),
department_id number (TEN),
constraint emp8_email_uk unique (email),
constraint EMP8_ID_PK Primary key (ID),
Constraint EMP8_DEPT_ID_FK foreign key (DEPARTMENT_ID) references departments (DEPARTMENT_ID) on Delete Set NULL
)

To modify a constraint:
Add NOT NULL constraint
ALTER TABLE EMP5
Modify (Salary number (10,2) NOT NULL)
To delete a constraint:
ALTER TABLE EMP5
Drop constraint Emp5_name_nn
Add a UNIQUE Constraint
ALTER TABLE EMP5
Add constraint Emp5_name_uk unique (name)
Invalid constraint:
ALTER TABLE Emp3
Disable constraint Emp3_email_uk
Activation constraints:
ALTER TABLE Emp3
Enable constraint Emp3_email_uk
Query constraint: (Note: The table name in the condition is capitalized)
Select Constraint_name,constraint_type,search_condition
From User_constraints
WHERE table_name = ' EMPLOYEES '
The query defines which columns are constrained:
Select Constraint_name,column_name
From User_cons_columns
WHERE table_name = ' EMPLOYEES '

View:
It is actually a virtual table, it is dependent on the base table, and when the data in the view changes, the corresponding data in the base table is also changed
Why use views?
A: 1, you can control data access 2, simplify query 3, avoid repeated access to the same data

Creating view:
Create View Empview
as
Select Employee_id,last_name,salary
from Employees
where Department_ ID = +
Creates a view based on multiple tables:
CREATE VIEW empview3
as
Select employee_id id,last_name name,salary,e.department_name
From Employees e,departments D
where e.department_id = d.department_id
Modify view: Create or replace
Create or rep Lace View empview2
as
Select employee_id id,last_name name,department_name
from Employees e,departments D
where e.department_id = d.department_id
Masking DML operations: with Read only (other users can only view, not add, delete, change)
Create or replace view empview2< Br>as
Select employee_id id,last_name name,department_name
from Employees e,departments D
where e.department _id = d.department_id
with Read only

The difference between a simple view and a complex view: a simple view has no grouping function, and if it is created using a group function in a complex view, it cannot use DML (increment, delete, change) operations, because some columns are not originally present in the base table.
Create a complex view: (Note: A column that does not exist in the base table, give it an individual name when creating the view, such as the average wage below.)
Create or Replace view EMPVIEW3
as
Select Department_name dept_name,avg (Salary) Avg_sal
from Employees E, Departments d
Where e.department_id = d.department_id
Group by Department_name

RowNum is a pseudo-column, a bit related to the ID, and has its own sort.
For example, if you want to find the top 10 salaried employees on the table, the following is not possible with rownum as it has its own default order
Select Rownum,employee_id,last_name,salary
From Employees
where RowNum <= 10
ORDER BY salary Desc
If you really want to use rownum to reach the top 10 of the query maximum wage, the following is true: (Note: rownum can only use < or <=, and =,>,>= will not return any data)
Select Rownum,employee_id,last_name,salary
From (select Employee_id,last_name,salary
From Employees
ORDER BY salary Desc)
where RowNum <=10
So what do you do if you want to query the data for the top wage ranking of 40-50 people? as follows: (the outermost RN is not a pseudo-column at this time)
Select Rn,employee_id,last_name,salary
From (select RowNum rn,employee_id,last_name,salary
From (select Employee_id,last_name,salary
From Employees
ORDER BY salary Desc))
where RN <40 and rn<=50

Sequence: Primarily used to provide primary key values
To create a sequence:
Create sequence Empseq
Increment by 10
Start with 10
MaxValue 100
Cycle
NoCache
The role of the sequence in the primary key position when the data is inserted into the table:
First:
CREATE TABLE Emp01
As
Select Employee_id,last_name,salary
From Employees
where 1=2
And then:
INSERT INTO EMP01
VALUES (empseq.nextval, ' BB ', 3300)
Modify the sequence: (can modify the increment, maximum, minimum, loop, and whether to load memory, if you want to change the initial value to re-create the sequence by deleting the sequence, because the change may conflict with the previous data, because the sequence is unique)
Alter sequence EMPSEQ
Increment by 1
Nocycle
A crack can occur in a sequence where the following occurs:
1. Rollback
2, the system appears abnormal
3. Multiple tables using the same sequence at the same time
Query sequence: (If the NoCache option is specified, Last_number returns the next valid value in the sequence)
Select Sequence_name,min_value,max_value,increment_by,last_number
From User_sequences
Delete a sequence
Drop sequence Empseq
The function of the index can speed up the query speed of the Oracle server, the system defaults to the column creation that it is constrained by by default in the primary key and the unique constraint, or it can be manually created for non-unique columns, and the system automatically calls the index after it is created, without you having to manually encode the call
To create an index:
CREATE INDEX Emp01_id_ix
On EMP01 (employee_id)
To delete an index:
Drop INDEX Emp01_id_ix
When do I create an index?
1, the data value distribution in the column is very wide
2. Columns often appear in the WHERE clause or join condition
3, the table is often accessed and the volume of data is very large, access to the data accounted for about 2% to 4% of the total data
When do I not create an index?
1, the table is very small
2, the table is updated frequently
3, the query data is greater than 2% to 4%
4. The table is not often present as a WHERE clause or join condition

Synonyms:
Create synonyms
Create synonym E for employees
Delete synonyms
Drop synonym E

To create a user and password:
Create User Atguigu01
Identified atguigu01;
Permissions to the user to log in to the database (create Table,create sequence,create view,create procedure)
Grant Create session
to Atguigu01;
Create a user table space
Alter user atguigu01 Quota Unlimited (or 5M)
On users
Change the user's own password:
Alter user atguigu01
identified by Atguigu;

Roles: Use roles to assign permissions to users faster, what permissions the role has, and what permissions the user has
1. Create a role
Create role My_role
2. Assigning permissions to Roles
Grant Create session,create table,create view
To My_role
3. Assigning roles to users
Grant My_role to Atguigu02

Object:
1, to the ATGUIGU01 Allocation Table employees query, modify the permissions
Grant Select,update
On Scott.employees
To Atguigu01
To verify the action in the previous step:
Update Scott.employees
Set last_name = ' ABCD '
where employee_id = 206 (at this point the table in the Scott user is changed, and Atguigu just calls its table, and it is not copied over)