Here is an introduction to the basic structure and simple query of select
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:
This data comes from: Click me to get the data in the above table
. The client I use is
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
文件-->新建-->SQL窗口open the command line interface
2. Simple query:
2.1, query all information:
select * from student;
*Indicates that all information is inquired, the
；ends, and it is easy to report errors if it is not written
2.2, query the information of a field in the table, such as
select age from student;
PLSQLrunning, select a certain line of sql code to run, otherwise
sqlthe syntax of the entire interface will be run directly .
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
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.
3. De-duplication, aliasing and sorting
Goal: Query all math scores [only show one duplicate score]:
select math from student;
The result is shown in the following figure:
you can find that there are duplicate data in the query result. To remove these duplicate data, you need to use keywords
select distinct math from student;and the result is as shown in the following figure:
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:
Here you can see the query to all the data, although single
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
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
method 2: Use the as keyword, followed by the alias name
select name as 姓名,math as 数学成绩 from student;result is shown in the following figure:
you can see that the effect is the same as just now, both of which can be achieved, the first is simpler.
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
order byfollowed by the standard ordering, where age-standard ordering. The result is shown in the following figure:
You can see that the age is sorted in descending order, and the ascending order only needs to
descreplace the keywords
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;
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:
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
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
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.
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.
（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,
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（math，res）, it means that if the
mathresult is not empty, return the
mathoriginal value, if it is a
resthis value, for example,
resset it to
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 will be at the top, and then sort in descending order, such as the following figure [network diagram example]
but we just
nullWhat should I do if I want the value to be at the bottom?
You can use
nulls lastto achieve control, as the
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
select name,name||'a' a名字 from student;
a名字column is actually a pseudo-column, and the result is shown in the following figure:
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
nameas shown in the following figure [Network image example]
4.4, virtual table:
oracleThere is a virtual table called
dual, which is used to construct
selectthe grammar rules to
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
select * from dual;
The result is shown in the following figure:
We can also use the virtual table to calculate some values, such as calculation
select 999*666 from dual;
The results are as follows:
But if we query the
dualdata in the table, will find that the value has not changed:
a simple query small sum:
a lookup table all records:
select * from 表名;
query record some of the fields of:
select 字段1，字段2... from 表名;
字段 别名 ,
字段 as 别名
pseudo-column: expression, e.g.
order by desc default is
order by asc
nulls first ,
Syntax analysis order:
select ... from ... order by
- First find the
fromfollowing table, know where to find the data
- Then execute
select, know what data to find from the table
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
. The above figure is the result. Think about it. If the
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:
as shown in the figure above, it is indeed possible to do this, but it also verifies the
sqlexecution order of the statements.