Preliminary & oracle-18-select Statements Using the arithmetic expression & alias in SQL & join operator%distinct&where clause

The format of a general SELECT statement is as follows:

1. Querying all columns of a specified table

SELECT * from Table name [ where condition ] [group by Group column name ] [having? Aggregation Functions ] [order by sort column name? ] asc| DESC]

Ability to write multiple tables in the name of the table

?

2. Query some columns of the specified table:

Select {[DISTINCT] column name. Column name,...} from table name [where condition ] [group by Group column name ] [having? Aggregation Functions ] [order by sort column name? ] asc| DESC]

Keyworddistinct means to remove repeated data from some columns,

is simply removed at the time of the query, and the table's internal data does not budge

?

Second, query the specified column

Suppose you want to query one or more specified columns. Ability to write SQL statements similar to the following.

?

Example 1: Query the Student Table section column, such as school number, name

Select Sno,snamefrom Student

Among the sno,sname are the column names of the student tables. Student is the table name for the student table, for example:

Example 2: Query the Student table for all information, for example:

Third, you can use arithmetic expressions in SQL statements:

+ 、-、 *,/.

?

such as: selectempno,ename,sal+500 from EMP;

So the SAL column can do the arithmetic and then display it in the query results.

"Attention" Select after assuming that add * , then * And then you can't add anything else, for example .

SELECT *, sname from A is wrong.

?

Example 3: Query the Student Table Section column. And the Age column adds 20 to the data. For example, with:

"Note" Although the query results will Age column all data plus - , but the data in the actual table has not changed.

?

Iv. use of aliases in query statements

In a query statement, we can alias a table or column

1. Add aliases to the columns:

(1) The first form of writing

select Empno as " Employee Number ", ename as" Employee Name the From EMP

(2) Another way of writing

Select Empno Employee Number , ename Employee Name From EMP

(3) A third way of writing

select Empno " Employee Number ", ename" Employee Name the From EMP

Example 4: Add aliases for Sno and sname in the student table, for example:

2. Add aliases to the table

Select Empno, ename from Empa

The original table name EMP is changed to alias a

?

Example 5: According to the existing student table and SC table, query the student scores of the S001 school number.

Solution: Query the Student table and SC table information first. For example, with:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

Two more forms of joint investigation. This may be learned from:

For commands:

Sql>selecta.sno,sname,score from Student A, SC b

????? 2 where A.sno = ' s001 ' and a.sno = B.sno;

A and B are student tables and SC table aliases, respectively.

so the use of defining aliases for tables is to simplify code writing , and the original Student.sno is now simplified into a.sno.

?

Five, join operators

You can concatenate the results of a two-column query in a SQL query statement by using the ' | | ' Realize.

Sql>select Empno | | Ename from EMP;

Example 6: Merging Sno and sname two columns from the student table.

For beauty. Ability to add aliases to columns:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

expansion: The ability to combine new data for the data in each column. For example, the student table in Sname and Sno together and merge new data.

"Note" query statement Select doing so does not change the data in the table.

Vi. use of distinct

Assuming that the results of the query have repeated values, you can use distinct to filter the repeated values.

Syntax structure:sql>SELECT DISTINCT column name from table name ;

?

Example 7: Query the SC table for information, go back and forth

Solution: First query all data in SC table

Then query the SC table for CNO column data

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

Use distinct to repeat

VII. WHERE clause uses

Use the WHERE clause in the SELECT statement to filter the results of the query.

Syntax format:

( 1 ) where Column Name comparison operator value ;

( 2 ) where Column Name comparison operator An expression ;

( 3 ) where Column Name comparison operator Column Name ;

Oracle provides examples of the following commonly used comparison operators:

(1) >

(2) <

(3) =

(4) <=

(5) >=

(6) <> or! = (both indicate not equal)

(7) between ... and ...

(8) in

(9) Like

?

Example 8: Use the WHERE clause to filter out student information with a name of 3 characters in the student table.

Solution: Query Student Table all data

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

Use where to filter out student information with a name of 3 characters

In fact, for example, the SQL statement is also a WHERE clause, which may be explained in detail by the two-table search.

Example 9: Querying employee numbers and names for wages greater than $1500

Select Empno,ename from emp where sal > 1500;

Example : Query position is not "clerk" employee number and name

Select Empno,ename from emp where job <> ' clerk '

Example : Query employee information for payroll from 1500 to 2900

Select Empno,ename from emp where Sal between and 2900

Example : Query salary is 1500,3000. 2000 of Employee information

Select Empno,ename from emp where Sal in (1500,3000,2000);

Eight, and, or, not use

And, or, not are the 3 logical operators (operators) in Oracle. They can combine various parts of the search criteria.

?

For example: Query employee information with a salary greater than 1500 and a position of "salesman"

Sql>selectempno, ename from EMP

2 where Sal > and job = ' salesman ';

For example: Query "COMM" This column of non-empty employee information

Sql>selectempno, ename, comm from EMP

????? 2 where Comm is not NULL ';

?

Example : Querying the game_id column for non-empty data and empty data in the player table

Solution: First query the player table for all data

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

Then query the Player table game_id columns with non-empty data

Query the Player Table game_id column hollow data

Preliminary &amp; oracle-18-select Statements Using the arithmetic expression &amp; alias in SQL &amp; join operator%distinct&amp;where clause