tags: Oracle

preface

I have had a general understanding of Mysql database and related Oracle knowledge before, but I have not used Oracle for a long time, so I almost forgot… I only remember basic SQL statements and related concepts…. The reason for writing this blog post is to document some knowledge that Oracle did not notice before… It may come in handy later…

Instance and database concepts

An Oracle database server consists of two parts:

  • Example: Understood as an object, invisible
  • Database: Understood as a class, visible

The name of the Oracle database is the same as the name of the Oracle database.

If you haven’t already installed Oracle, check out my other blog posts at….

So our own SQLplus black window and instances, the relationship between the database is what? We can look at the following image:

Oracle database treats tables, views, and so on as objects:

Null value in Oracle

If there is a null field in Oracle, it will not be displayed in SQLPlus…. If we use null data to perform operations with other data… The result is always null

Therefore, Oracle provides NVL(expression 1, expression 2) functions that take the value of expression 2 if the value of expression 1 is null… Of course, if expression 1 is not null, the value of expression 1 is taken

Note that null does not operate on the = sign argument, but on the number/date/varchar2 argument

Oracle provides the is null keyword to replace the = problem

Aliases in Oracle

If you want to use an alias in Mysql, you need to use the as keyword, followed by the alias…. Oracle can omit the as keyword…

In general, aliases are enclosed in double quotation marks (“”). Oracle also allows us to write aliases directly, but **, if we do not write double quotation marks, then our aliases are blank **

One more thing: Oracle aliases cannot be enclosed in single quotes. Oracle defaults to single quotes as string and date.

IO input and output SQL statements

You can use the spool command in SQLplus to save SQL statements to hard disk.


	spool e:/oracle-day01.sql;
Copy the code

Run the spool off command to save the SQL statement to the hard disk file E :/oracle-day01.sql and create an SQL file to end the statement

spool off;

Copy the code

You can also run SQL files from your hard disk in SQLPlus using the following command:


	@ e:/crm.sql; 
Copy the code

Escape character

Sometimes, we might obfuscate some data, but there are special characters in the name. So we have to go through the escape…. Of course, if you follow Java, it’s very simple, just write a “”.

How w does it escape in Oracle? Let’s look at the following example:

Select * from employees whose names contain '_', use \ escape to return the following characters to their original meaning [like '%\_%' escape ']select * from emp where ename like '% \ _ %' escape '\';
Copy the code

What if the name is’ single quote ‘?? So two single quotes represent one quote

Insert an employee named ''insert into emp(empno,ename) values(2222.' ' ' ' ' ');

Copy the code

Single-line and multi-line functions

First, let’s clarify a concept:

  • Single-line function: takes one argument and returns a result
  • Multi-line function: scans multiple arguments and returns a result…. In general, the concepts of multi-line functions and grouping functions are similar…

Oracle provides the string function, date function for us to operate on the corresponding data, here is not a repeat, we have to refer to the documentation when necessary.

Single quotes appear as follows:

  • 1) String, e.g. ‘hello’
  • 2) Date type, e.g. ’17-December-80′
  • 3) to_char/to_date(‘YYYY-MM-DD HH24:MI:SS’)

Double quotation marks appear as follows:

  • 1) select ename, ename from emp
  • 2) to_char/to_date (date, ‘YYYY “in” MM “month” DD “, “HH24: MI: SS’)

GROUP BY details

Details of the group by clause:

  • 1) All columns of a non-multi-line function that appears in the SELECT clause must appear in the group by clause
  • 2) select columns from group by group

For example: the following code is wrong!!


	select max(avg(sal)) "Maximum departmental average salary.",deptno "Department Number"
	from emp
	group by deptno;
Copy the code

Why is it wrong?? Select * from deptno; select * from deptno; select * from deptno If we use multi-line nesting when grouping queries, then we can only follow this column after the select field, not more. Max (AVg (Sal)) is grouped again

Of course, there’s no problem if we just figure out the average salary for each department, which is this code:

	select avg(sal) "Maximum departmental average salary.",deptno "Department Number"
	from emp
	group by deptno;

Copy the code

Multi-table query, sub-query

When we cannot query the data out of a table, we need to join other tables together to query….

When our query criteria are not known, we can use the sub-query….

In general, subqueries and multi-table queries function similarly….

Subquery out of the data is single row single column, generally we are equal to, greater than or equal to, less than and other operators to limit the query conditions…

We use the IN, ANY, ALL operators to filter conditions for single columns and multiple rows.

If there are multiple rows and columns, we treat the query result as a table.

Note that the mathematical basis of multi-table queries is the Cartesian product, that is, if two real tables are joined, it will form a Cartesian product table… In other words, there is only one Cartesian product table

The connection

In a multi-table query, cartesian product is generated, so there will be a lot of irrelevant data in the Cartesian product table… To cull this data, we will use the WHERE clause to filter the Cartesian product table into useful tables

In general, we have several types of connections:

  • In the connection
    • Equivalent connection [filter out the conditions using the = sign]
    • Non-equivalent join [filter conditions by means of between and, etc.]
  • Outer join
  • Since the connection

Now the question comes, in Oracle some functions we can use multi-table query to complete, sometimes we can use sub-query to complete, so we generally choose which ？？？？

Let’s take a look at the following chart to compare their strengths and weaknesses:

Index is a concept of space for time.. When there is a large amount of data, Oracle will create an index for our data. When scanning the data, we can directly obtain the value…. based on the index There are also several indexing algorithms [binary tree, sparse index, bitmap index…., etc.]

To sum up: Multi-table queries in Oracle may perform better than subqueries

Oracle paging

In JDBC, we have already covered the pagination problem between Oracle and Mysql…. See my blog: blog.csdn.net/hon_3y/arti…

But let’s just give you an impression:

Pagination in Oracle relies on the rownum pseudo-column. Since rownum can only use <= or < to fetch data… Because the value of rownum can change frequently (add a single piece of data, then rownum +1, it is reasonable to say that rownum can be infinite, so it cannot be operated with >)….

So the idea of Oracle pagination looks like this:

  • The first n records are obtained in a subquery
  • Since we return multiple rows and columns, we can look at a table
  • Place the query data after the from sentence
  • Coat queries can filter data from subqueries using where clauses
  • Then we can query the data we want…

Formula:

  • *Mysql from (currentPage – 1)LineSize starts fetching data, lineSize bar data
  • *CurrentPage *lineSize; currentPage*lineSize;LineSize starts fetching data

Small interview questions

Pen test: there are [100 billion] member records, how to use the most efficient way to clear the salary field zero, other fields unchanged?

First: Remove the SAL field from the EMP table

  •   alter table emp 
Copy the code
  •   drop column sal;      
Copy the code

Add a sal field to the EMP table with the default content 0

  •   alter table emp
Copy the code
  •   add sal number(6) default 0;
Copy the code

Operation table details

Go to the recycle bindrop table users; Query objects in the recycle binshowrecyclebin; Before: Restores the flashback table name to beforedrop; Flashback table Table name to beforedrop rename toThe new name of the table; Delete the users table completelydrop table users purge; Empty the recycle binpurgerecyclebin; Add the image column to the EMP tablealter tableThe name of the tableaddColumn name type (width)alter table emp
add image blob; Change the length of the ename column to 20 bytesalter tableThe name of the tablemodifyColumn name type (width)alter table emp
modify ename varchar2(20); Delete the image column,alter tableThe name of the tabledrop columnThe column namealter table emp
drop columnimage; Ename = username;alter tableThe name of the tablerename columnThe original nametoThe new columnalter table emp
rename column ename tousername; Rename emP table emps,renameThe original name of the tabletoThe new name of the tablerename emp to emps;
Copy the code
  • Number (5) :
    • A maximum of five digits
  • Number (6, 2) :
    • Where 2 indicates that at most 2 decimal digits are displayed, which is rounded to 0. Col… for …
    • 6 indicates that the number of decimal and integer digits is not more than six
    • The number of integer digits cannot exceed 4 digits and can be equal to 4 digits
  • Varchar2 (8) :
    • 8 bytes

Note that when modifying a table, it cannot be rolled back!

Cascading operations in Oracle:

  • 【on delete cascade】 Cascade deletion
  • 【on delete set null】 Set the foreign key to NULL

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