Mysql07 (data update DML)

In the actual project , The most used is the read operation , But inserting data is as important as deleting data , There are relatively few modifications

/*
 The insert ：
   Insertion of tuple values 
   Insertion of query results 
*/
-- The most basic way to insert 
--insert into tablename values(val1,val2,....)  If there is no column after the table name , Then you can only insert all columns 
--insert into tablename(col1,col2,...) values(val1,val2,...)  You can specify which columns to insert data into 

insert into emp values(2222,'haha','clerk',7902,to_date('2019-11-2','YYYY-MM-dd'),1000,500,10);
select * from emp;
-- When inserting data into some columns , You don't just insert into which column you want to insert , Follow the specifications defined when creating the table 
insert into emp(empno,ename) values(3333,'wangwu')

-- Other ways to create tables 
-- Copy table and copy table data at the same time , Constraints are not copied 
create table emp2 as select * from emp;
-- Copy table structure but not table data , Constraints are not copied 
create table emp3 as select * from emp where 1=2;
-- If there is a set of data , If you insert all the data in the set one by one , How efficient ？ Generally in actual operation , Rarely insert... One by one , It's more of a batch insert 

/*
 Delete operation ：
 delete from tablename where condition
*/
-- Delete data that meets the criteria 
delete from emp2 where deptno = 10;
-- Clear the data of the whole table 
delete from emp2;
--truncate , Follow delete Somewhat different ,delete In the process of deletion, the transaction , and truncate No business , Once deleted, it is permanently deleted , No rollback operation 
-- High efficiency , However, it is prone to misoperation , So it's not recommended 
truncate table emp2

/*
 Modify the operating ：
   update tablename set col = val1,col2 = val2 where condition;
    One or more columns can be modified 
*/
-- Update single column 
update emp set ename = 'heihei' where ename = 'hehe';
-- Update the values of multiple columns 
update emp set job='teacher',mgr=7902 where empno = 15;


/*
 Adding, deleting and modifying is a common operation of database , When operating, you need 《 Business 》 Guarantee ,  That is to say, every time in pl/sql In the implementation of sql After the statement, you need to complete commit The operation of 
 Affairs become very critical ：
     The main purpose is to ensure data consistency 
     If the same data , Only one person can visit at a time , There will be no data confusion , But in the current project , More concurrent access 
     Concurrent access brings insecurity of data , That is, inconsistency 
     If we want to ensure the security of data , The main way is to lock ,MVCC
    
     Extension of business ：
         The most basic database transactions 
         Declarative transactions 
         Distributed transactions 
     In order to improve efficiency , It is possible that multiple operations will be performed in the same transaction , Then it may be partially successful , Department failure , Based on this situation, transaction control is needed .
    select * from emp where id = 7902 for update
    select * from emp where id = 7902 lock in share mode.
    
     If you don't guarantee things , It will cause dirty reading , It can't be read repeatedly , Fantasy reading .
*/