Oracle Study Notes (4)

Oracle study note (4) Foreign key-create table major_ning (mid number (2) primary key, mname varchar2 (30) referenced in the master table (parent table )); -- from the table (sub-table), refer to the create table student_ning (id number (5) primary key, name varchar2 (20), mid number (2 ), constraint stuning_mid_fk foreign key (mid) references major_ning (mid); insert into major_ning values (1, 'computer '); insert into major_ning values (2, 'music '); insert into major_ning values (3, 'movie '); insert into student_ning values (101, 'Peter', 1); insert into student_ning values (102, 'chris ', 3 ); insert into student_ning values (103, 'King', 2); select s. name, m. mname from student_ning s join major_ning m on s. mid = m. mid and s. name = 'Peter '; -- try to add a student record and specify a non-existent professional code 10 insert into student_ning values (110, 'dawson', 10); -- error: parent keyword not found -- indicates that there is no professional ORA-02291 encoded as 10 in the parent table: integrity constraint (OPENLAB. STUNING_MID_FK) violated-parent key not found -- tries to delete the major 1 in the major_ning table. This major is selected by some students (referred to) and cannot be deleted, delete from major_ning where mid = 1; -- ERROR: Found subrecord ERROR at line 1: ORA-02292: integrity constraint (NINGLJ. STUNING_MID_FK) violated-child record found -- when a child table is created, the foreign key constraint is added with the setting condition -- on delete cascade create table student_ning (id number (5) primary key, name varchar2 (20), mid number (2), constraint stuning_mid_fk foreign key (mid) references major_ning (mid) on delete cascade ); -- on delete set null create table student_ning (id number (5) primary key, name varchar2 (20), mid number (2), constraint stuning_mid_fk foreign key (mid) references major_ning (mid) on delete set null); select create/drop/insert/delete/update commit/rollback -- insert into dept (deptno, dname, loc) values (80, 'market', 'beijing'); create table mydept (id number (2) primary key, name varchar2 (20), location varchar2 (30); insert into mydept (id, name, location) select deptno, dname, loc from dept; create table emp_ning (id number (4) primary key, name varchar2 (20), deptno number (20 )); -- insert into emp_ning values (1234, 'Peter ', 10) is added. -- using subqueries, insert into emp_ning (id, name, deptno) select empno is added, ename, deptno from ninglj. emp where deptno = 10; -- Copy table: including structure and data create table emp_dup as select * from ninglj. emp; -- copy the table structure without copying data. create table emp_bak as select * from ninglj. emp where empno> 9999; -- update emp_ning set sal = 1000 where empno = 7369; update emp_ning set sal = 1500, deptno = 20, job = 'salesman' where empno = 7369; -- delete emp_ning where deptno = 10; DML: insert/update/delete transaction statement commit/rollback/savepoint create table temp_ning (id number primary key ); insert into temp_ning values (1); savepoint A; insert into temp_ning values (2); savepoint B; insert into temp_ning values (3); savepoint C; insert into temp_ning values (4); rollback to B; rollback to C; -- check the result rollback to A; commit; -- synonym for the sequence constraint condition of the view index sequence in the table view of the primary object of the database .... -- View view -- create or replace View v_emp_ning as select ename, sal from ninglj. emp; -- select * from v_emp_ning; desc v_emp_ning -- delete view drop view v_emp_ning; -- data dictionary -- data table under User name: user_tables; read-only select count (*) from user_tables; select table_name from user_tables where rownum <20; -- query the first 19 table names select * from user_table where table_name = 'emp '; -- Query emp table information -- View-related data dictionary user_views; -- view the total number of views select count (*) from user_views; -- view the data dictionary structure desc user_views select text from user_views where view_name = 'v _ EMP_NING '; -- create a complex view create or replace view emp_sum as select deptno, sum (sal) sum_sal from ninglj. emp group by deptno; Data Dictionary: user_tables: All tables under the current user name all_tables: All tables that the current user can access + tables that other users can access. dba_tables: select count (*) from user_objects; select count (*) from user_tables; select count (*) from all_objects; select count (*) from all_tables; -- index creation method: automatic/manual when creating unique/pk constraints, index creation create index emp_ename_idx on emp (ename );