Database Knowledge points

? 1, Oracle Connection, external connection, internal connection query differences

?  2. Oracle, MySQL auto-increment sequence, primary key, table rename, field name modification, table field type modification

? 3. Oracle , MySQL triggers, functions, stored procedures,

? 4 , Oracle, MySQL paged query

? 5 , understand the following Oracle statements

SELECT * FROM (select row_.*, RowNum recordnum from (select tt.*, (select SUM (st.now_count) from Consumable_in_detail s T INNER join consumable_in_list icil on st.in_id = icil.id where St.consumable_type = Tt.consumabletype and st.consumab  Le_name = tt.consumablename and St.consumable_code = Tt.consumablecode and St.brand = Tt.brand and St.model = Tt.model and tt.now_count>0) Nowcount from (select Row_number () over (partition by t.consumable_code,t.consumable_name,t.brand,t . Model,t.consumable_type ORDER by T.id) RN, T.id, T.consumable_type consumabletype,t.consumable_name consumableName, T.consumable_code Consumablecode,t.use_way Useway, decode (t.use_way,1, ' Test ', 2, ' Office ') Usewayname, decode (t.source,1, ' Buy ', 2, ' retreat ') sourceName, Eac.class_name consumabletypename, T.source, t.shelf_id shelfid,t.depostary_id Depostaryid, Spd.shelf_position_name shelfname, Dl.depositary_name depositaryname,t.brand,t.model,t.fee_unit feeUnit,ul.unit_ Name Feeunitname,t.price_excluding_tax priceexcludingTax, T.price_including_tax priceincludingtax,t.fa_account_id faaccountid,t.config,t.project,t.suppliers, T.suppliers_contact Supplierscontact, T.maintenance_time maintenancetime,t.maintain_fulfill_date Maintainfulfilldate,t.maintain_suppliers maintainsuppliers, T.maintain_contact_person MaintainContactPerson, T.maintain_tel maintaintel,t.city,t.building,t.floor,cil.in_time indate, El.employee_name inPerson,cil.in_no inNo, T.now_count Now_count from Consumable_in_detail t inner join consumable_in_list CIL on t.in_id = cil.id INNER JOIN shelf_p Osition_dic SPD on spd.shelf_position_id=t.shelf_id inner join depositary_list dl on dl.depositary_id = t.depostary_id in NER join employee_list el on el.employee_id = Cil.in_person INNER join expend_article_class eac on eac.class_id = T.consu Mable_type left JOIN Unit_list ul on ul.unit_id = T.fee_unit and t.now_count>0) tt where TT.RN = 1) row_) where re Cordnum > 0 and Recordnum <= 20

？ 6.

Select Tt.*, (select SUM (st.now_count) from Consumable_in_detail st                         inner join consumable_in_list icil on st.in_id = icil.id        &NBS P                where st.consumable_type = tt.consumabletype      &N Bsp                    and st.consumable_name = tt.consumablename  &nbs P                        and St.consumable_code = Tt.consumablec ode                           and St.brand = Tt.bran d                           and St.model = tt.model& nbsp                          and tt.now_count > 0) Nowcoun Tfrom (Select Row_number () over (partition by T.consumable_code, T.consumable_name, T.brand, T.model, T.consumable_type ORDER BY t.id) rn,                               t. id,                             t.consum Able_type consumabletype,                             t.consumable_name consumablename,                    &NBSP ;          t.consumable_code consumablecode,                               t.use_way useway,                               decode (T.use_way, 1, ' Test ', 2, ' office ') usewayname,          &nbsp                    decode (T.source, 1, ' buy ', 2, ' withdraw ') sourcename,                               eac.class_name con sumabletypename,                           / / nbsp;t.source,                            &NB sp;t.shelf_id shelfid,                            &N Bsp  t.depostary_id depostaryid,                        &NBSP ;      spd.shelf_position_name shelfname,                  &NBSP ;            dl.depositary_name depositaryname,            &NBSP ;                 t.brand,                &NB Sp              t.model,                  &NB Sp            t.fee_unit feeunit,                               ul.unit_name feeunitname,                               t.price_excluding_tax priceexcludingtax,    &NBSP ;                          t.price_including_tax PRICEINCLU dingtax,                             T.F a_account_id faaccountid,                             t.config,                             t. project,                             T.S uppliers,                             t. Suppliers_contact supplierscontact,                        &NB Sp      t.maintenance_time maintenancetime,                  &NBS P            t.maintain_fulfill_date maintainfulfilldate,          & nbsp                    t.maintain_suppliers maintainsuppliers,                               t.maintain_contact_perso N Maintaincontactperson,                             /  T.maintain_tel maintaintel,                          &NBS P    t.city,                            &NBS P  t.building,                               t.floor,                              &NB Sp;cil.in_time indate,                            &N Bsp  el.employee_name inperson,                         .      cil.in_no inno,                        &N Bsp   &nbsp  t.now_count now_count                          from Consumable_in_detail t                         inner Joi cil                            Consumable_in_list On t.in_id = cil.id                         inner join SH Elf_position_dic spd                            on S pd.shelf_position_id = t.shelf_id                        &NBSP ; inner join Depositary_list dl                         /-------- nbsp On dl.depositary_id = t.depostary_id                        &N Bsp;inner JoIn Employee_list el                            on El . employee_id = cil.in_person                         inne R Join Expend_article_class eac                            on eac.class_id = t.consumable_type                      &NB Sp   LEFT join Unit_list ul                          &N Bsp On ul.unit_id = t.fee_unit and t.now_count > 0) tt                 where t T.rn = 1;

Database Knowledge points