SQL Basic Learning Notes (ii)

Inserting a piece of data into a table

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>create table Emp1as Select employee_id, last_name, hire_date, salary from Employeeswhere 1 = 2</st Rong></span>

It is important to note that the data being inserted must correspond to the type of data in the table.

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>insert into Emp1values (1001, ' abc ', To_date (' 1998-12-11 ', ' yyyy-mm-dd '), 10000) </strong>< /SPAN>

If salary is null

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>insert into Emp1values (1002, ' C ', to_date (' 1999-01-11 ', ' yyyy-mm-dd '), NULL) Note that salary does not write, it is not possible </ Strong></span>

It can also be specified to specify the value to be entered, note (employee_id,last_name,hire_date), this order can be changed, but value,
must correspond to (employee_id,last_name,hire_date) one by one

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>insert into EMP1 (employee_id,last_name,hire_date) VALUES (1004, ' C1 ', to_date (' 2999-05-11 ', ' Yyyy-mm-dd ')) </strong></span>

So salary can not write

One thing to be aware of is that only nullable values can be assigned
Name        type         Nullable Default comments 
----------------- ----------------------------- 
employee_id number (6)    y             & nbsp           ,
last_name   VARCHAR2 (+)                          &NBSP
hire_date   DATE                                  &NBSP
SALARY      number (8,2) &NB Sp Y   

This table last_name, hire_date, cannot be empty

copy data from other tables

--Import the following information into the new table in section 80th of the Employees table

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>insert into Emp1 (employee_id, hire_date, last_name, salary) Select employee_id  , Hire_date, Last_ Name, salaryfrom employeeswhere department_id = 80</strong></span>

Create script (Learn)

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>insert into Emp1 (employee_id, last_name, salary, hire_date) VALUES (&id, ' &name ', &salary , ' &hire_date ') </strong></span>

Update data

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>SQL> update EMP1   2  set  3  salary = 12000  4  where employee_id = 1005;// Be careful not to write where will get rid of all the data (commit; save) </strong></span>

If you change your mind, you can use rollback, but the data after commit cannot be rolled back.

--Update number 114th employee's work and salary to make it same as 205 employees

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>create Table Empployees1asselect * from employeesupdate empployees1set job_id = (             select Job_id
   from empployees1             where employee_id = 205              ), salary = (                          Select salary from                          empployees1                         where employee_id = 205                         ) Where employee_id = 114;</strong></span>

--Adjust the department_id of employees who job_id the same staff as employee_id 200 to employee_id 100 of department_id

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>update empployees1set department_id = (                      select department_id from                      empployees1                      where employee_id = +                    ) where job_id = (                    select job_id from                    empployees1                    where employee_id = $               ) </ Strong></span>

Delete Data

Delete From ...
where ....

Remove Department ID from the EMPPPLOYEES1 table with the public character in the department name

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>delete from Empployees1where department_id = (                        select department_id from                        departments                        where department_name like '%public% '                      ) </strong></span>

Note the integrity error of the delete operation

Deleting a department_id that contains employees is not possible because the Employees table does not know to put these people
What to do

Increase:

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong> (1) Insert into...values (...) (2) Insert INTO ... select. From.. Where ... </strong></span>

Change:

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>update. Set: Where: </strong></span>

Delete:

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" ><strong>delete from.. Where: </strong></span>

Transactions:

A set of logical operations units that transform data from one state to another.
Database transactions are made up of the following sections:

One or more DML statements
A DDL (data definition Language language) statement
A DCL (Data Control Language language) statement

Start with execution of the first DML statement
End with one of the following:
COMMIT or ROLLBACK statement

DDL statements (auto-commit)

Rollback, rollback is the last commit.
The state of the data before committing or rolling back
The state of the data before the change can be restored.

Users performing DML operations can query for previous corrections through the SELECT statement
Other users cannot see changes made by the current user until the current user ends the transaction.
The rows involved in the DML statement are locked and cannot be manipulated by other users.
This is similar to multi-threaded operations to share data, when a thread in the operation to change data, if it does not release the lock, other threads are unable to manipulate the data

Scott user is in Update table, no commit
System User
SELECT * from Scott.employees for update;
Cannot query, wait, wait until the Scott user commits, you can query the
Scott users also want to continue to modify the table, it is not, because the system user commit

SQL Basic Learning Notes (ii)