oracle database: select basic syntax [simple query]

Here is an introduction to the basic structure and simple query of select

1. selectStructure

select 字段名 from 表名  条件

The text description is to query which field information in which table by what conditions.
Before performing the query operation, take a look at the data table prepared in advance:

Insert picture description here


This data comes from: Click me to get the data in the above table
. The client I use is PLSQL. It cmdis also possible to directly use the interface without a client , but I can’t see the visual interface. , The actual effect is not affected, in PLSQLit:
文件-->新建-->SQL窗口open the command line interface

Insert picture description here

2. Simple query:

2.1, query all information:

select * from student;
*Indicates that all information is inquired, the sqlsentence ends, and it is easy to report errors if it is not written

Insert picture description here

2.2, query the information of a field in the table, such as age:

select age from student;

When PLSQLrunning, select a certain line of sql code to run, otherwise sqlthe syntax of the entire interface will be run directly .

Insert picture description here


As shown in the above figure, the query result is only displayed age, and the table ageis uppercase, because the oracledatabase syntax is not case sensitive, and the default is uppercase, but it should be noted that the data is case sensitive.

2.3, query the data of multiple fields in the table:

select name,math from student;

The query result is shown in the following figure. When querying multiple fields, the fields are separated by commas, and the query results are displayed according to the order of the fields. For example, here is the namefront and maththe back, then the query results are also in this order , You can adjust the display order of the query result fields according to this rule.

Insert picture description here

3. De-duplication, aliasing and sorting

3.1 Deduplication

Goal: Query all math scores [only show one duplicate score]:

select math from student;

The result is shown in the following figure:

Insert picture description here


you can find that there are duplicate data in the query result. To remove these duplicate data, you need to use keywords distinct, syntax:, select distinct math from student;and the result is as shown in the following figure:

Insert picture description here

You can see that there is no duplicate data in the queried data. Of course, if multiple fields are to be deduplicated, the multiple fields must be all the same to be deduplicated. select distinct math,english from student;Here queries in English and math and deduplication:

Insert picture description here


Here you can see the query to all the data, although single mathor englishhave the same results, but math+englishnot exactly the same, you can look out the re-recording is to take effect for exactly the same piece of data.

3.2. Create an alias

Method 1:
Add the name of the alias directly after the field. For example, query the name and math fields, and give the field the alias name, math score:, select name 姓名,math 数学成绩 from student;you can see the result shown in the figure below, and the data name of the query becomes the one we set Name

Insert picture description here


method 2: Use the as keyword, followed by the alias name
syntax:, the select name as 姓名,math as 数学成绩 from student;result is shown in the following figure:

Insert picture description here


you can see that the effect is the same as just now, both of which can be achieved, the first is simpler.

3.3, sort

Examples: all the content query and sorted according to descending age field: select * from student order by age desc;wherein descindicates a descending sort in ascending order of keyword asc, order byfollowed by the standard ordering, where age-standard ordering. The result is shown in the following figure:

Insert picture description here


You can see that the age is sorted in descending order, and the ascending order only needs to descreplace the keywords asc.

Insert picture description here


It can be found that people of the same age have different math scores. On this basis, you can sort again according to math scores :

select * from student order by age asc,math desc;

Phrase sqlmeans that all the contents of the lookup table, which agein ascending order of sorting criteria, if agethe same time, to mathscore in descending order, the results shown below:

Insert picture description here


Note here that the same only when the first field, Will execute the second field. If the sort key is not written, the default is ascascending sort.

4. Pseudo column and virtual table

4.1 Pseudo columns and expressions

Note: The column that does not exist in the query is a pseudo column. When the required result cannot be obtained directly from the table, it can be used 伪列+表达式to achieve
it if it needs to be calculated to display. Let’s look at an example :

select name,math,1 from student;

In this grammar, you need to query 1the content of the field name . In fact, there is no such field in the table.

Insert picture description here


As shown in the above figure, there is no 1such field in the table , but it is automatically spliced ​​behind the result set when querying. This kind of column that does not exist in the original table is called a pseudo column.
Next (math+english)/2, let’s make an example to query the student’s name, age, and average score, as the average score, because there is no average score in the table, so you need to use a pseudo column to achieve this.

select name,age,(math+english)/2 平均分 from student;

Add a line of pseudo-column here, and give the pseudo-column an ​​alias name 平均分. As shown in the figure below,

Insert picture description here


if you can’t afford the alias, the default pseudo-column name looks like the field, here is (math+english)/2, and it is in English capital style. , Not shown here.

4.2, null handling

When performing data processing, such as addition, if one party is null, the result is null, according to normal logic, if one party is empty, the value of addition should be defaulted to 0, for example 88+null=88, this is what we want. Because my data is all set not null, I will not show the specific picture, just record the processing method: nvl():处理null值的方法,可以传两个参数。
for example nvl(math,res), it means that if the mathresult is not empty, return the mathoriginal value, if it is a nullvalue, return resthis value, for example, resset it to 0.

Insert picture description here


The picture above is an example picture found on the Internet.
When sorting in ascending or descending order, you will also encounter nullproblems. For example, when sorting in descending order, if there is a nullvalue, the nullvalue will be at the top, and then sort in descending order, such as the following figure [network diagram example]

Insert picture description here


but we just nullWhat should I do if I want the value to be at the bottom?
You can use nulls first/ nulls lastto achieve control, as the

Insert picture description here


name suggests, nulls firstthat is, the nullvalue is ranked first, nulls lastthat is , the value is ranked behind.

4.3, string splicing

The content to be spliced ​​here can be numbers or characters, and the splicing symbols are: ||
Example: query the student’s name and splice one after the name a. This alias is called a名字:

select name,name||'a' a名字 from student;

This a名字column is actually a pseudo-column, and the result is shown in the following figure:

Insert picture description here


Here is a small note, the sqlcharacters inside should use single quotation marks, and double quotation marks will report an error.
Of course, other splicing can be performed. I will not show them one by one here. It is necessary to say that if one of the spliced ​​objects has a nullvalue, it will automatically become an empty content. For example, the name||nullresult is nameas shown in the following figure [Network image example]

Insert picture description here

4.4, virtual table:

oracleThere is a virtual table called dual, which is used to construct selectthe grammar rules to oracleensure that dualthere is always only one record in it. This table has only one row and one column. Like other tables, it can perform insert, update, delete operations, and also perform dropoperations, but do not just use the droptable, otherwise it will make the system unusable and the database cannot be used [this can only be reinstalled ]. dualMainly used to select system variables or to find the value of an expression. If we don't need to get the data in the table from a specific table, but simply get some information we want, and selectwhen we want to complete it, we must use an object, this object is dual.

select * from dual;

The result is shown in the following figure:

Insert picture description here


We can also use the virtual table to calculate some values, such as calculation 999*666:

select 999*666 from dual;

The results are as follows:

Insert picture description here


But if we query the dualdata in the table, will find that the value has not changed:

Insert picture description here


a simple query small sum:
a lookup table all records: select * from 表名;
query record some of the fields of: select 字段1,字段2... from 表名;
de-duplication: distinct
Alias:字段 别名 , 字段 as 别名
pseudo-column: expression, e.g. (math+english)/2
Sort:order by desc default is order by asc
null processing: nvl(exp,res)
string concatenation: name||age
NULLS:nulls first ,nulls last

Syntax analysis order:

select  ...  from ... order by 
  1. First find the fromfollowing table, know where to find the data
  2. Then execute select, know what data to find from the table
  3. Then order by, sort the result set.

According to this principle, let's finally look at an example:

select name,age,(math+english)/2 平均分 from student order by (math+english)/2 desc;

This syntax is the average score of the query and sorted in descending order

Insert picture description here


. The above figure is the result. Think about it. If the from studentAND select ...statement is executed first , then the alias is already in effect. At this time order by, can the alias be connected later:

select name,age,(math+english)/2 平均分 from student order by 平均分 desc;

Take a look at the result:

Insert picture description here


as shown in the figure above, it is indeed possible to do this, but it also verifies the sqlexecution order of the statements.