DML statement (add, delete, modify) and change table of Oracle Foundation (5)

Time:2020-10-28

1、 DML statement (data operation statement)

1. Add data to the table

Insert into table name (field name) values (value)

Note: 1) only one piece of data can be inserted using the values keyword

2) Pay attention to integrity constraints

3) The inserted values must be in the same order as the fields

4) You can insert values for all fields in a table, or you can insert data from a subtable into a table

5) Insert the parent table first

6) You can insert multiple data at the same time with the subquery

//To s_ Stu inserts a piece of data
insert into s_stu
values(1,’tom’,null,null,null,null);
insert into s_stu
values(2,’tom’,null,null,’f’,null);
insert into s_stu
values(3,’lucky’,18,’18625034564′,’m’,41);
insert into s_stu(id,name,age)
values(4,’jack’,18);

insert into s_stu(id,name)
select id,last_name
from s_emp
where id>5;

2. Update the data in the new table
Update table name
Set field name 1 = value 1, field name 2 = value 2
[where condition]
1) Without where, the information of all the current fields in the student table will be updated
Update age = 20 in student table
  update s_stu
  set age=20;
 
2) Add where to update some data in the table
 
  Update data that meets the where condition
Update the age of students whose ID is greater than 20 to 99
  
update s_stu
  set age=99
  where id>20;
 
3) Pay attention to integrity constraints
Update the age of id = 10 to 50 and phone to 1111
  update s_stu
  set age=50,phone=’1111′
  where id=10; 
3. Delete data
Delete [from] table name
[where condition]
1) add where to delete some data in the table
Delete data that meets the where condition

/ / delete the student information with id = 10
      delete from s_stu
      where id=10;

2) delete all the data in the table without where, and the table structure is still there
3) delete the data of the child table first, and then delete the data of the parent table

4. Affairs
A series of operations in the same unit of operation that either succeed or fail at the same time
And these operations are atomic units and cannot be subdivided

Rollback transaction: rollback
Commit transaction: commit

(1) when the business begins:
1) the end of one thing means the beginning of another
2) open the terminal and a new transaction begins
(2) when will the business end
1) end of normal condition
A. execute commit
B. perform rollback
C. execute DDL and DCL command will be submitted automatically
D. execution of DML statements will not automatically commit (manual commit or rollback is required)
E: exit will be submitted automatically
2) abnormal ending
A. click the exit button
B. system error

(3) Four characteristics of transaction: Acid
Atomicity: a transaction is an atom and cannot be subdivided
Consistency: the data before and after the transaction operation should be consistent
Isolation: one transaction cannot see the data of another
Persistence: save persistent messages of transaction data to disk
  
       insert into…..
       savepoint a
       update….
       savepoint b
       insert into….
       delete
   
       rollback to a/b
/ / set a and B savepoints to roll back to the desired operation
2、 Alter table, rename
1. Add a new field
Alter table name
Add (field name data type default value constraint
, field name……… )
  
To s_ Stu add comments
    alter table s_stu
    add comments varchar2(100);
2. Delete the fields in the table
Alter table name
Drop column field name;
Delete comments
   alter table s_stu
   drop column comments;
3. Modify the fields in the table
Alter table name
Modify (field name data type default value constraint);
Modify s_ Stu’s Dept_ The ID constraint is changed to not null
    alter table s_stu
    modify dept_id number(7) not null;
Note: there are some things to note about the value (constraint data type)
Modification: data type (value is null), constraint (whether the constraint is met), length of data type (whether the length can drop the data in the table)
4. Add constraints (table level constraints, so non null constraints cannot be added)
Alter table name
Add takes the constraint name and constraint type (field);
  
5. Delete constraints
Alter table name
Drop constraint constraint constraint name
 
6. Make the constraint invalid
Alter table name
Disable constraint constraint name;
 
7. Make the constraint effective? > judge whether the existing value conforms to the constraint
Alter table name
Enable constraint constraint name;
8. Delete table
Drop table name
A. delete the data and table structure in the table;
B. It is DDL and cannot be rolled back
C. drop can delete any object
Delete from table name
A. deleting the data in the table will not delete the table structure;
B. DML can be rolled back
C. the efficiency of deleting data is low
Truncate table name;
A. deleting the data in the table will not delete the table structure;
B. It is DDL and cannot be rolled back
C. truncate can only delete tables
D. high efficiency of deletion
9. Rename
Rename old name to new name;
10. Rename (field name)
Alter table name
Rename column old field name to new field name
 
11. Add notes to the table
Comment on table name
Is’ the content of the note ‘;
    comment on table s_stu
    is ‘This is a Student info’;
/ / view the table’s comments
    select comments
    from all_tab_comments
    where table_name=’TEST’;
  
/ / annotate the columns in the table
    comment on column  test.id
Is’ serial number ‘;
/ / view column comments
    select comments
    from user_col_comments
    where table_name=’TEST’;
User table: a table created by the user through create table
Users are required to maintain their own addition, deletion and modification operations
Data dictionary table: the table created by the database, maintained by the database itself, and added, deleted and modified by the database itself.
       eg:user_constraint
 
/ / query s_ Comments on stu table
     select comment
     from all_tab_comments
     where table_ name=’S_ Stu ‘; / / table name capitalized