Queries for Oracle Tables

Source: Internet
Author: User
Tags expression

One, SELECT
1. Understanding | [] {} The meaning in the introduction of the command, as well as the data in the Basic Select query table, mainly using the Select command.
SELECT *| {[DISTINCT] column|expression [alias],...} from table;
In this way of describing the command: | : to express or, as above, the *|{... That is, you can use *, which means that all columns are displayed, or you can use the contents of curly braces.
{}: Indicates a required option. []: An option, that is, the contents of the brackets, but not. Of course, the choice of whether or not the command in parentheses indicates a different meaning.
{[DISTINCT] column|expression [alias],...} The meaning of this string is that [DISTINCT] is optional and can not be added. Column|expression [alias], as already mentioned, either use column, which is the name of the row, such as select Id,name from T1 (try it). Either use expression [alias],expression the word is usually the meaning of an expression. An expression usually refers to a number of addition and subtraction expressions: Select salary+1000 from T1 (try), and salary+1000 is an expression. To put it another way, the word expression, which we will use frequently in future orders. It can represent everything, data, arithmetic, and so on.

2. In a single command, we can use column somewhere and use expression [alias] in another place, for example:
Select name,salary+1000 from T1 (try); Name is to use the column name directly, salary+1000 is an expression. The results of this command appear as follows:

Gyj@ocm> select name,salary+1000 from T1;

NAME salary+1000
---------- -----------
GYJ2 9000
Gyj3 11000
Gyj4 16000

3, we can also specify an alias for the expression, expression [alias], the option [alias] is an alias. We have omitted the order above. Try the following alias:

Gyj@ocm> Select name,salary+1000 salary from T1;

NAME SALARY
---------- ----------
GYJ2 9000
Gyj3 11000
Gyj4 16000

Notice that the displayed header becomes an alias. Double quotation marks if the alias contains spaces or has special symbols:
gyj@ocm> Select name,salary+1000 ' My salary ' from T1;

NAME my Salary
---------- ----------
GYJ2 9000
Gyj3 11000
Gyj4 16000


Gyj@ocm> Select name,salary+1000 "Salary (bonus)" from T1;

NAME Salary (Bonus)
---------- -------------
GYJ2 9000
Gyj3 11000
Gyj4 16000

Two, commonly used operators
Understanding | | , and the difference between single and double quotes
In the expression, the commonly used operators have +,-,*,/and (), these people look at the PPT I will not say more. These are all for the digital type.
1, for the character, there is a common connector: | | 。 It can fonts two words together:

gyj@ocm> Select ' www. ' | | name| | '. com ' from T1;
' WWW. ' | | name| | '. CO
------------------
Www.gyj2.com
Www.gyj3.com
Www.gyj4.com

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

This example is mainly for demo | | The role of www. And the value of the Name column, and COM is connected together.
Note Here the use of single, double quotes if the www. Single quotes outside of. com are exchanged for double quotes, and the results are as follows:
gyj@ocm> select "www." | | name| | ". com "from T1;
Select "www." | | name| | ". com "from T1
*
ERROR at line 1:
ORA-00904: ". com": Invalid identifier
We've just used double quotes in the column alias "My Salary," If you change to single quotes:
gyj@ocm> Select name,salary+1000 ' My salary ' from T1;
Select name,salary+1000 ' My salary ' from T1
*
ERROR at line 1:
Ora-00923:from keyword not found where expected
The same error, which means that single, double quotes are not universal. When should I use single quotes, and when do we use double quotes? It's simple to distinguish. If your display results are for the header portion of the table, use double quotes. "My Salary" is only displayed in the header, and there is no "my Salary" in the column data below. and ' www. ', '. com ' is displayed in the following column data, which will be put in single quotes.

2, and a little more about | | , it does not only connect characters, it can also work with numbers and time:


Gyj@ocm> Select Name| | ' s salary is ' | | salary| | ' at ' | | To_date (sysdate, ' Yyyy-mm-dd ') from T1;

name| | "" Ssalaryis ' | | salary| | ' At ' | | To_date (sysdate, ' yyyy-mm-dd ')
-------------------------------------------------------------------------------
Gyj2 ' s salary is 8000 at 13-feb-20
Gyj3 ' s salary is 10000 at 13-feb-20
Gyj4 ' s salary is 15000 at 13-feb-20


Here are two knowledge points, one is the salary is a numeric column, Sysdate is the system time, they can also be connected with the character. And the single quote is already a special character, it is no longer a single quote in general sense, it is an Oracle character demarcation mark. But like Gyj2 ' s salary is 8000, it's a common quote. In single quotes, when single quotes appear, just like ' gyj2 ' salary is 8000, to avoid Oracle mistaking this single quote for the end of the string, you need to hit the single quote two times, that is, ' gyj2 ' salary are 8000 ', so it's OK. This is the meaning of ' s salary is ', the first single and last single quotation marks are the boundaries of the strings. The first single quote is two consecutive single quotes, just to represent a single quotation mark in the ordinary sense.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.