Detailed description of SQL details, multi-table queries, grouped queries, and paging operations in Oracle databases, oraclesql

Detailed description of SQL details, multi-table queries, grouped queries, and paging operations in Oracle databases, oraclesql

Preface

I have probably learned about Mysql databases and related Oracle knowledge before, but I haven't used Oracle for a long time, so I forgot about it... There are only basic SQL statements and related concepts .... The reason for writing this blog post is to record some knowledge points that Oracle has not noticed before... It may be useful in the future...

Instance and database concepts

The Oracle database server consists of two parts:

Instance: understood as an object, invisible Database: understood as a class, visible

We have already filled in the name of our database when installing Oracle. Generally, the name of the instance is the same as that of the database...

So what is the relationship between our built-in sqlplus black window, instance, and database? Let's look at it:

Oracle databases regard tables and views as objects:

Null Value in Oracle

If the field in Oracle is null, it will not be displayed in sqlplus .... If we use null data to perform operations with other data... The final result is null.

Therefore, Oracle provides the NVL (expression 1, expression 2) function for us to use. If the value of expression 1 is null, take the value of expression 2... Of course, if expression 1 is not null, the value of expression 1 is used.

It is worth noting that the null value cannot be a parameter = number operation, and the null value can be a number/date/varchar2 operation.

Oracle provides the is null keyword to replace the = Operator.

Alias in Oracle

We know that 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 addition, we generally use double quotation marks (") to enclose aliases. Oracle also supports writing aliases directly. However, if we do not write double quotation marks, therefore, our alias cannot contain spaces.

Another point is that Oracle aliases cannot be enclosed by single quotes. Oracle considers single quotes as string and date by default.

IO Input and Output SQL statement

You can use the spool command in sqlplus to save the SQL statement to the hard disk. For example:

Spool e:/oracle-day01. SQL;

Use the spool off command to save the SQL statement to the hard disk file e:/oracle-day01. SQL, and create the SQL file.

Spool off;

Of course, we can also execute the SQL file in the hard disk in sqlplus, as long as the following command is enough:

@ E:/crm. SQL;

Escape characters

Sometimes, we may fuzzy query some data, but there are some special characters in the name. So we need to escape .... Of course, if you follow Java, it will be very simple, just write.

In Oracle, how can we escape it? Let's look at the following example:

Query the employees whose names contain '_' and use the \ escape character to return the subsequent characters to the Original Meaning [like '% \ _ % 'escape '\']

Select * from emp where ename like '% \ _ %' escape '\';

If the name is 'single quotes, then two single quotes represent a single quotation mark.

Insert an employee named''

Insert into emp (empno, ename) values (2222 ,'''''');

Single-row and multi-row Functions

First, we need to clarify a concept:

Single-line function: enter a parameter and return a result multiple-line function: scan multiple parameters and return a result .... Generally, multiline functions and grouping functions are similar in concept...

Oracle ** provides string functions and date functions for us to perform corresponding operations on data **. We will not go into details here. We will just check the document when necessary.

The single quotes are as follows:

1) string, for example: 'hello' 2) date type, for example: '17-December-80' 3) to_char/to_date (date, 'yyyy-MM-DD HH24: MI: ss ')

The double quotation marks are as follows:

1) column alias, for example: select ename "name" from emp 2) to_char/to_date (date, 'yyyy "year" MM "month" DD "day" HH24: MI: ss ')

Group by details

Details of the group by clause:

1) All columns of non-multi-row functions appearing in the select clause [must] appear in the group by clause. 2) All columns appearing in the group by clause, [available but not available] In the select clause

For example, the following code is incorrect !!!

Select max (avg (sal) "maximum average salary of a department", deptno "department no"

From emp

Group by deptno;

Why is it wrong? We already have the deptno field in the group, and the select field is followed by the multiline function and the field, why is it wrong? If we use multi-row function nesting during grouping query, then we can only follow this column behind the select field, and no more. Max (avg (sal) is equivalent to grouping again

Of course, if we only find the average salary of each department, that is, the following code, there is no problem at all:

Select avg (sal) "maximum average department salary", deptno "department ID"

From emp

Group by deptno;

Multi-Table query and subquery

When we cannot query data in a table, we need to join other tables for query ....

When we do not know the query conditions, we can use subqueries ....

Generally, the sub-query and multi-Table query functions are similar ....

When the data in the subquery is a single row and a single column, we usually use operators like equals, greater than or equal to, and less than to limit the query conditions...

For a single column and multiple rows, we usually use the IN, ANY, and ALL operators to filter conditions...

If it is multi-row, multi-column, we will regard the returned query result as a table [Oracle paging is the principle]

It is worth noting that the mathematical basis of Multi-Table query is Cartesian product. That is to say, if two physical tables are connected, it will constitute a Cartesian Product table... That is to say, there is only one Cartesian Product table.

Connection

In multi-table queries, we generate cartesian products, so there will be a lot of irrelevant data in the Cartesian Product table... To remove the data, we use the where clause to filter the Cartesian Product table into useful data tables.

Generally, we have several connections:

Internal Connection

Equijoin [filter out the conditions by using the = Sign] non-equijoin [use between and other means to filter out the conditions] external join self-join

Now the problem arises. in Oracle, we can use multi-table queries to complete some functions, and sometimes we can use subqueries to complete them. Which one do we usually choose?

We can compare their advantages and disadvantages:

The index is a concept of changing the space for time .. when the data volume is large, 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, and bitmap index .... And so on]

To sum up, the performance of Multi-table queries in Oracle may be better than that in subqueries.

Oracle Paging

Here we will make a better impression:

In Oracle, pagination relies on the pseudo column rownum, because rownum can only use <= or <to obtain data... Because the value of rownum may change frequently [Add a piece of data, then rownum is + 1. In principle, rownum can be infinite, so you cannot use> for operations ]....

So the idea of Oracle paging is like this:

Obtain the first n records in the subquery. Because multiple rows and multiple columns are returned, therefore, we can see that the data to be queried is placed behind the coat of the from clause. We can use the where clause to filter the data to be queried. Then we can query the expected data. the data...

Formula:

Mysql get data from (currentPage-1) * lineSize, get lineSize data Oracle first get currentPage * lineSize data, get data from (currentPage-1) * lineSize

Interview Questions

Exam: There are [100 billion] member records. How can I reset the salary field in the most efficient way without changing the content of other fields?

1. Delete the sal field from the emp table

Alter table emp drop column sal;

Second, add the sal field to the emp table, and the content defaults to 0.

Alter table emp add sal number (6) default 0;

Operation table details

Go to the recycle bin

Drop table users;

Query objects in the recycle bin

Show recyclebin;

Flash back to restore the recycle bin

Flashback table name to before drop;

Flashback table name to before drop rename to new table name;

Permanently delete the users table

Drop table users purge;

Clear Recycle Bin

Purge recyclebin;

Add an image column to the emp table. alter table name add column name type (width)

Alter table emp

Add image blob;

Modify the length of the ename column to 20 bytes. alter table name modify column name type (width)

Alter table emp

Modify ename varchar2 (20 );

Delete the image column. alter table name: drop column name

Alter table emp

Drop column image;

Duplicate name column name: username, alter table name rename column original column name to new column name

Alter table emp

Rename column ename to username;

Rename the emp table and rename the original table name to the new table name.

Rename emp to emps;

Number (5 ):

A maximum of five digits number (6, 2 ):

2 indicates that a maximum of two decimal places can be displayed. The number is rounded to the nearest decimal point. If the number of digits is not enough, add 0. Set col... For... 6 indicates that the number of decimal + integer must not exceed 6 digits, and the number of digits of the integer must not exceed 4 digits. It can be equal to 4 digits varchar2 (8 ):

8 bytes

It is worth noting that the table cannot be rolled back when it is modified!

Cascade operations in Oracle:

[On delete cascade] cascade delete [on delete set null] set the foreign key side to null