tags: Oracle

preface

In the first part of the Oracle summary, we have summarized some common SQL related topics. So this article mainly summarizes some content about Oracle CE view, sequence, transaction…

In databases, we can classify various SQL statements into four broad categories…

  • (1) DML: SELECT, INSERT, update, delete
  • (2) DDL: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCate table
  • Grant select any table to Scott/REVOKE select any table from Scott
  • (4) TCL: COMMIT, rollback, savePoint to rollback point

The batch operation

What is the batch operation, is to insert a number of data…. In SQL, the data we query can be regarded as a table, so when we insert data, we can insert the data according to the query… This can be viewed as a batch operation…

Note that if you do not specify which fields to insert, all of the queried fields will be inserted into the table..

Copy all employees in xxX_EMP table from department 20 to EMP table and insert them in batches.insert intoThe name of the tableselect. grammarinsert into emp
select * 
from xxx_emp
where deptno=20;

Copy the code

Comparison of Deletion Operations

Our delete syntax has three types:

  • delete from
  • truncate from
  • drop from

Let’s compare the differences:

drop table

  • 1) belongs to the DDL
  • 2) Cannot be rolled back
  • 3) Do not bring where
  • 4) Delete table content and structure
  • 5) Fast deletion speed

truncate table

  • 1) belongs to the DDL
  • 2) Cannot be rolled back
  • 3) Do not bring where
  • 4) Delete table contents
  • 5) Fast deletion speed

delete from

  • 1) belongs to the DML
  • 2) can be rolled back
  • 3) can take the where
  • 4) Table structure, table content depends on where execution condition
  • 5) The deletion speed is slow and needs to be deleted line by line

The transaction

Transactions are actually covered in the JDBC section: blog.csdn.net/hon_3y/arti…

To be clear again: a transaction is a whole of indivisible sub-operations that either all execute successfully or all fail

It is worth noting that transactions in Oracle are somewhat different from transactions in Mysql:

Oracle transaction starts:

  • The first DML operation starts as a transaction [transaction does not need to be started manually]

Oracle commit transactions

  • (1) Display commit: commit
  • DDL/DCL/exit(sqlplus)

Oracle rollback transactions

  • (1) Rollback is displayed
  • (2) Hidden rollback: Close the window (SQLplus tool), crash, and power failure

Because Oracle has the concept of instance pools, Oracle supports rollback…

The default isolation level supported by Oracle is Read Commited

The isolation level supported by Mysql by default is reapatable read

Set transaction isolation level to SerialIZABLE in Oracleset transaction isolation level serializable;

Copy the code

Accessing objects of other users

As mentioned in the last blog post, Oracle treats tables/users as objects… How do we access the table under user Scott and user HR?

In fact, we only need to specify a specific user when accessing the table. Database table on the line, but also to see if the user has permission to query other users of the table, so you need to grant permission…

Practice:

Declaration: Scott or HR is called user name/schema name/space name Scott--tiger
      hr-----lionQuery who the current user isshow user; SQL > select all objects from Scott's tablespace with or without user nameselect * from emp;
select * fromemp; orselect * fromscott.emp; Unlock a common HR account as sysdbaalter user hr account unlock; Set the password of the hr common account as sysdbaalter user hr identified bylion; When Scott queries all tables in the HR tablespace, the user name must be addedselect * fromhr.jobs; By default, each user can query only the objects in his own space, but cannot query objects in other user Spaces. As sysdba, grant Scott the permission to query objects in all user Spacesgrant select any table toscott; Revoke user Scott's permission to query objects in all user Spaces as user sysdbarevoke select any table fromscott; Scott checks his own permissionsselect * fromuser_sys_privs; Conn/as sysdba; Navigate from sysdba to Scott user space CONN Scott/Tiger; User space conn HR/Lion; Query all objects in the HR user spaceselect * fromtab; Navigate from hr to Scott user space conn Scott/Tiger; SQL > select JOBS from hr; select jobs from Scottselect * from hr.jobs;


Copy the code

view

A view is a virtual table based on a data table

  • (1) A view is a virtual table
  • (2) Views are built on the basis of existing tables, which are called base tables
  • (3) The statement that provides the data content to the view is the SELECT statement, which can be understood as the stored SELECT statement
  • (4) The view provides the user with another representation of the base table data
  • (5) View does not store real data, real data is stored in the base table
  • (6) The programmer operates on a view, but eventually the view becomes the base table
  • (7) A base table can have zero or more views

Why use views?

Sometimes, we may only relate certain fields in a table, while others may only relate certain fields in the same table…

It doesn’t make sense to show them all the fields. We should give them the data they want to see… On the one hand, they can only focus on their own data, on the other hand, we also make sure that some of the data table confidential data is not leaked…

There’s another reason:

When we query data, we often need to write very long SQL statements, almost every time to write very long…. As mentioned above, a view is a virtual table based on a query, that is, a view can encapsulate the queried data… Then it will be very convenient when we use it…

Summary:

  • (1) You can use views if you don’t want the user to see all the data (fields, records) and only want the user to see certain data
  • (2) When you need to reduce the writing of SQL queries, you can use views, but not improve the efficiency of the query

Practice # #

Create view EMP_view_1 based on all emP table columns,create viewView nameas selectA query against one or more base tablescreate view emp_view_1
as
select * fromemp; By default, regular users can't create views, so sysdBA assigns you creare View permissions and authorizes Scott as SysdBAcreate viewpermissionsgrant create view toscott; As sysdba, undo user Scottcreate viewpermissionsrevoke create view fromscott; Create a view emp_view_2 based on the emP table with the specified columns containing the number/name/salary/annual salary/annual income (column alias used in the query)create view emp_view_2
as
select empno "Number",ename "Name",sal "Wages",sal*12 "Salary",sal*12+NVL(comm,0) "Annual income"
fromemp; Create view EMP_view_3 (a, B, C, D,e) with number/name/salary/annual salary/annual income (use column names in view)create view emp_view_3(a,b,c,d,e)
as
select empno "Number",ename "Name",sal "Wages",sal*12 "Salary",sal*12+NVL(comm,0) "Annual income"
fromemp; Query the structure of the emp_view_3 view. Desc emp_view_3; Modified emp_view_3 (id, name, salary, annual income) view,create or replace viewView nameasThe subquerycreate or replace view emp_view_3(id.name,salary,annual,income)
as
select empno "Number",ename "Name",sal "Wages",sal*12 "Salary",sal*12+NVL(comm,0) "Annual income"
fromemp; Query the EMP table to find the minimum salary, maximum salary and average salary for each departmentselect min(sal),max(sal),round(avg(sal),0),deptno
from emp
group bydeptno; Create a view emp_view_4 that contains the minimum, maximum, and average salaries for each departmentcreate or replace view emp_view_4
as
select deptno "Department Number".min(sal) "Minimum wage".max(sal) "Maximum salary".round(avg(sal),0) "Average salary"
from emp
group bydeptno; Create a view emp_view_5 that contains the employee number, name, salary, department name, and salary levelcreate or replace view emp_view_5
as
select e.empno "Number",e.ename "Name",e.sal "Wages",d.dname "Department name",s.grade "Pay grade"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal ands.hisal); Delete the record for employee 7788 in view EMP_view_1, usingdeleteDoes the operation affect the base tabledelete from emp_view_1 where empno=7788; Emp_view_1 (with read only)deleteOperation, ok?create or replace view emp_view_1
as
select * from emp
with read only; Cannot bedeleteDoes deleting a record in a view affect the base table? Does deleting a view affect the base table affect the table? Does the base table delete view go to the recycle bin without affecting it? Does deleting the base table affect the view without going into the recycle bin? Does the view have an impact after flashback to the base table? The view is working againCopy the code

A synonym for

When we use a multi-table query, or when the meaning of the queried table field is not clear, we use an alias instead of…. Of course, aliases are for column or table names only

Now that we know about users/views/tables and other objects, Oracle also provides synonyms for us to use

The role of synonyms

  • (1) Shorten the object name
  • (2) Convenient access to other users’ objects

Practice:

Create a synonym for the salgrade table,create synonymA synonym forforTable name/view/other objectcreate synonym e for salgrade;
create synonym ev5 foremp_view_5; Grant to Scott as sys usercreate synonympermissionsgrant create synonym toscott; Removed from Scott as sys usercreate synonympermissionsrevoke create synonym fromscott; Use synonyms to manipulate the salgrade tableselect * froms; Delete synonymsdrop synonymev5; Does deleting a synonym affect the base table? Does deleting a base table affect synonyms? Will affect the synonymCopy the code

The sequence

Mysql > create table increament with auto increament Does Oracle have an automatic growth strategy?

Oracle uses the sequence object….

  • (1) It is similar to the auto_increment mechanism in MySQL, but there is no auto_increment mechanism in Oracle
  • (2) is a mechanism provided by Oracle to generate unique numeric values
  • (3) is usually used for the primary key of the table
  • (4) Sequences can only be guaranteed to be unique, not continuous 
    • ** In Oracle, only rownum always starts at 1 and continues **Copy the code
  • (5) Sequence value, can be placed in memory, get faster

What’s the difference between oracle sequences and automatic growth in Mysql?

  • Mysql maintains an auto-growing program for each table…
  • Oralce stores sequences in memory that can be used by several tables…

You may wonder why we use a pseudo column called Rownum for auto-growth when we use it for paging.

Rownum values, while unique and contiguous, do not always uniquely identify the record… That is, once the record is deleted, the rownum value will change.

Why sequence

  • (1) Before, we set the value of the main key, which needs to be set manually, which is prone to error
  • (2) The primary key of each table before is independent and cannot be shared

practice

Create the sequence EMP_EMPNO_seq for the EMPNO field of the EMP table,create sequenceThe sequence ofcreate sequenceemp_empno_seq; Delete the sequence emp_EMPno_seq,drop sequenceThe sequence ofdrop sequenceemp_empno_seq; Nextval = currval; nextval = currval; nextval = currvalselect emp_empno_seq.nextval from dual;
select emp_empno_seq.currval fromdual; Using sequences, insert records into the EMP table, empNO field using sequence valuesinsert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval); Change the increment BY attribute of emp_EMPNO_seq to 20 by defaultstart withis1.alter sequenceThe sequence ofalter sequence emp_empno_seq
increment by 20; Modify Change the increment BY attribute of the EMP_EMPNO_seq sequence to 5alter sequence emp_empno_seq
increment by 5; To modify the emp_empNO_seq sequencestart withProperties, okayalter sequence emp_empno_seq
start with 100; Once I have a sequence, can I set values manually for the main key?insert into emp(empno) values(9999);
insert into emp(empno) values(7900); Does deleting a table affect the sequence? You can't doinsertOperation, the table really die, sequence death delete sequence, will affect the table? Not in Hibernate if it is accessedoracleDatabase server, then user.hbm. XML mapping file about <idHow are the tags configured? <id name="id" column="id">
   <generator class="Increment /identity/ UUID / [sequence] / [native]"/>
</id>

Copy the code

The index

What is an index

What is an Index?

(1) it is a mechanism to quickly query the contents of the table, similar to the directory of Xinhua dictionary. (2) It is used in some fields of the table, but when stored, it is independent of the table

Why index

Why index

  • (1) Accelerate the query speed of Oracle server through Pointers
  • (2)Reduce disk I/O by quickly locating data using ROWID 
    • Rowid is the only id in Oracle that determines the different records of each tableCopy the code

Index tables turn data into ordered….

Quickly locate data files on hard disk…

The rowid characteristics

The characteristics of the rowid

  • (1) in each table, but not visible on the surface, e.g. Desc EMp is not visible
  • (2) Rowid is visible only in select
  • (3) It is bound to each table, the table dies, and the rowiD of that table dies. The rownum of two tables can be the same, but the ROwiD must be unique
  • (4) ROWID is an 18-bit mixture of case and number, which only represents the position of the record in the DBF file
  • (5) RoWId can participate in =/like comparison, with “” single quotes around the value of rowid, and is case-sensitive
  • (6) ROWID is a bridge between contact tables and DBF files

The index characteristics

Index features

  • (1) Once an index is created, it is automatically maintained by Oracle Management system, and it is up to Oracle management system to decide when to use the index
  • (2) The user does not specify which index to use in the query statement
  • (3) Create index on primary key or unique constraint
  • (4) Users can also according to their own needs, to specify a single field or multiple fields, add index

If you specify a primary key or unique constraint, the system automatically creates an index on the corresponding column.

When to create an index

  • SELECT (1)
  • (2) The table is very large (there are too many records), and the distribution of record content is very wide
  • (3) Column names often appear in WHERE clauses or join conditions

When do [not] create indexes

  • (1) INSERT/UPDATE/DELETE operations are frequently performed on tables
  • (2) Table is very small (record very few)
  • (3) Column names do not often appear as join conditions or in the WHERE clause

practice

Create index emp_EMPNO_IDx for empNO single field in emP tablecreate indexIndex nameonTable name (field,...)create index emp_empno_idx
onemp(empno); Create index EMP_ENAME_job_IDx for ENAME and job of emP tablecreate index emp_ename_job 
onemp(ename,job); If the job is not only used in the where the index If only appear in the where ename appears at the same time use the index we advocate ename and job note: after the index creation, only the lookup table, and the other (insert/update/deleteEmp_ename_job_idx; emp_ename_job_idx;drop indexIndex namedrop index emp_empno_idx;
drop index emp_ename_job_idx;


Copy the code

Permissions and Users

practice

The users in the Oracle database are classified into two types: 1) System users, such as sys users, are automatically created by the system when the Oracle database server is created. 2) user created by system user, called ordinary user, such as Scott,hr, c##tiger,zhaojun... " Log in to the Oracle database server as sys, and query the names and status of existing users on the Oracle database server. Username Indicates login names expired&locked Indicates that the account is expired or locked. Open Indicates that the account is available. col username for a30; col account_status for a30; set pagesize 100; select username,account_status from dba_users; Select * from all_users; To create a common user in Oracle database, run the create user command. When creating a common user, allocate a specific tablespace for it, usually called users. " Select * from V $TABLESPACE; select * from V $TABLESPACE; " Mysql > create user #tiger as user sys, password as ABC, default to users storage, sqlplus/as sysdba; create user c##tiger identified by abc default tablespace users; " Sqlplus/as sysdba; sqlplus/as sysdba; sqlplus/as sysdba; alter user c##tiger quota unlimited on users; " Can I access the orcl database using c##tiger? SQL > select * from sysdba; SQL > select * from sysdba; SQL > select * from sysdba; SQL > select * from sysdba; drop user c##tiger cascade; Sys is an important system user in Oracle. Sys is the highest authorized user in Oracle. Its role is SYSDBA (system administrator) sqlplus/as SYSDBA. That is, all users in the database operation objects and can perform actions are limited. Oracle database permissions are classified into two categories: 1) system permissions 2) object permissions 5) system permissions permissions for specific operations on the database, for example, c##tiger can log in to the orcl database, can create tables in the orcl database. Select any table Indicates the select permission on all tables. Sqlplus/as sysdba; select distinct privilege from dba_sys_privs; " SQL > create session sqlplus/as sysdba create session sqlplus/as sysdba grant create session to c##tiger; " Can I access the orcl database using c##tiger? SQL > create table tiger as SQL > create table tiger as SQL > create table tiger as SQL > create table tiger as SQL > create table tiger as SQL > create table tiger sqlplus c##tiger/abc create table tiger( name varchar2(20) ); SQL > create table as sysdba; SQL > create table as sysdba; grant create table to c##tiger; " Create a table in tiger using c##tiger. sqlplus c##tiger/abc create table tiger( name varchar2(20) ); Sqlplus/as sysdba; sqlplus/as sysdba; select grantee,privilege from dba_sys_privs where lower(grantee) = 'c##tiger'; SQL > create table sqlplus/as sysdba; SQL > create table sqlplus/as sysdba revoke create table from c##tiger; Object permission User operation permission on existing objects, including: 1) select from table, view, and sequence; 2) insert into table or view; 3) update; 4) delete; 5) execute; Execution of the process of 6) index for the table index creation 7) references to table creation health 8) attributes of the alter table or modified with sys login, query object privilege c # # tiger have up sqlplus/as sysdba; col grantee for a10; col table_name for a10; col privilege for a20; select grantee,table_name,privilege from dba_tab_privs where lower(grantee) = 'c##tiger'; " SQL > alter table tiger sqlplus/as sysdba; grant all on c##tiger.tiger to c##tiger; Note: C## tiger indicates the name of the table in the space c##tiger tiger FLASHBACK c##tiger tiger DEBUG c##tiger tiger QUERY REWRITE c##tiger tiger ON COMMIT REFRESH C##TIGER TIGER REFERENCES C##TIGER TIGER UPDATE C##TIGER TIGER SELECT C##TIGER TIGER INSERT C##TIGER SQL > ALTER table TIGER; SQL > ALTER table TIGER; SQL > ALTER table TIGER; insert into tiger(name) values('AA'); update tiger set name = 'BB'; delete from tiger where rownum = 1; select * from tiger;Copy the code

If you find this article helpful, give the author a little encouragement