Oracle Database 3

In the first two chapters, we learned some basic SQL language query statements, that is, the database query Language DQL, today we want to introduce the database operation language DML

Database, in addition to our query, the main thing is the daily increase, deletion, change, check.

Database Operation Language DML
Insert
Delete
Modify

1 、--Create a table

CREATE TABLE BJSXT as SELECT * from EMP; (creates a table named BJSXT in the format of the EMP expression)

Then we use SELECT * from BJSXT; we can query the table we created.

2, insert--must fully refer to the structure order of the table
INSERT into BJSXT VALUES (' Teacher Pei ', ' teacher ', 666,333);
INSERT into BJSXT VALUES (' Teacher ', ' Mr. Hu ', 666,333);

--The wrong way
INSERT into BJSXT VALUES (' Teacher ', ' Mr. Hu ', 666,333,10);
INSERT into BJSXT VALUES (' Teacher ', ' Mr. Hu ', 666);

--Custom Insert Order
INSERT into BONUS (ename,sal) VALUES (' Yan teacher ', 777);

3--Delete
DELETE from BONUS;
DELETE from BONUS WHERE sal>666;
DELETE from the EMP WHERE ename like '%a% ' and SAL >1500 and DEPTNO = 10;

4--modification
UPDATE BONUS SET SAL = 8888 WHERE ename= ' PEI teacher ';
UPDATE BONUS SET SAL = 8888,comm = 888 WHERE ename= ' PEI teacher ';

2--transactions for the database

--The transaction has been opened

Once the table of the database is manipulated, it represents the start of the transaction
DELETE from bjsxt WHERE EMPNO = 7788;
DELETE from bjsxt WHERE EMPNO = 7781;
DELETE from bjsxt WHERE EMPNO = 7782;
INSERT into
UPDATE
DELETE from bjsxt WHERE EMPNO = 7783;
DELETE from bjsxt WHERE EMPNO = 7784;
DELETE from bjsxt WHERE EMPNO = 7785;
DELETE from bjsxt WHERE EMPNO = 7786;

--End Transaction
/*
Manual end
COMMIT;
Submit this transaction operation
ROLLBACK;
Rollback this transaction operation
Auto End
When the user executes the DDL statement (CREATE TABLE, delete table, modify table structure)
Transaction autocommit
User exits
Transaction autocommit
User exit
Transaction rollback
system crashes or power outages
Transaction Auto-rollback
Four characteristics of a transaction
acid principle
Atomic
The operations in the transaction are either all executed or rolled back all the time before and after the execution of the
Consistency
transaction, the database is in a consistent state
quarantine
The execution of a transaction is not interfered with by other transactions after the
Persistence
Transaction is committed, it enters a new state, and all data cannot be changed
Transaction isolation LEVEL
READ UNCOMMITTED-to lock data to read Committed o--resolve dirty read
Repeatable READ-solve the virtual read, and Magic Read Delete
Serializable o--new to resolve Phantom read

Update missing
is because there is no lock
Dirty read
Read other people uncommitted data
Read
Read data from the first transaction, another transaction can modify
Phantom read
The first transaction reads more data than the second transaction inserts the data

Deadlock ORA-00060
Thread 1
DELETE from bjsxt WHERE EMPNO = 7499;
Thread 2
DELETE from bjsxt WHERE EMPNO = 7521;
Thread 1
DELETE from bjsxt WHERE EMPNO = 7521;
Thread 2
DELETE from bjsxt WHERE EMPNO = 7499;
*/

/*
Paradigm
First Paradigm
Each column is an indivisible atomic data item.
Second Paradigm (2NF)
Requires that the attributes of an entity depend entirely on the primary key. A full dependency is a property that cannot exist that relies only on the primary key
Each row of data must have a primary key, the primary key is unique,
Third Paradigm
Any non-primary attribute is not dependent on other non-primary properties [elimination of transitive dependencies on 2NF basis]
All attributes must be fully dependent on the primary key
The relationship between a table and a table
1:1
Feel free to hold each other's primary key
Have the same primary key
1:n
Foreign keys are maintained by multiple parties
N.
Establish intermediate table, maintain in intermediate table

/*
DDL: Database Definition language



*/

SELECT table_name from User_tables;

Design Requirements:
Create a table to store student information,
The fields in the table contain information such as student number, name, age, entry date, grade, class, email, etc.
and the default value of 1 is specified for grade.
If you do not specify a grade value when inserting data, you are the first grade student
--How to create a table
CREATE TABLE STUDENT (
SNO VARCHAR2 (12),
NAME VARCHAR2 (12),
Age Number (3),
Schooldate DATE,
GRADE Number (2) DEFAULT 1,
Clazz Number (2),
EMAIL VARCHAR2 (100)
);

--just created to represent an empty table, you need to insert the data artificially
INSERT into STUDENT VALUES (' BJSXT2016 ', ' Zhang Sanfeng ', 80,sysdate,null,10, ' [email protected] ');
INSERT into STUDENT (sno,name) VALUES (' BJSXT2015 ', ' Zhang Mowgli ');

--Add a column
ALTER TABLE STUDENT ADD BIRTHDAY DATE;
--Delete a column
ALTER TABLE STUDENT DROP COLUMN age;
--Modify a column
ALTER TABLE STUDENT MODIFY SNO VARCHAR2 (40);
--Modify Table name
RENAME STUDENT to STU;
--Delete Table
DROP TABLE STU;

Oracle Database 3