Oracle-18-select Statement &sql use of arithmetic expressions & aliases & join Operators%distinct&where clauses

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 aggregate function ] [ORDER BY Sort Column name asc| DESC]

Multiple tables can be written in the table name area

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 aggregate function ] [order by sort column name asc| DESC]

Where the keyword distinct means to remove duplicate data from some columns,

Just remove it at query time, and the table's internal data doesn't budge

Second, query the specified column

If you want to query one or more of the specified columns, you can write an SQL statement similar to the following.

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

Select Sno,snamefrom Student

Among them, Sno,sname is the column name of the student table, student is the table name of the student table, such as:

Example 2: Query All information about student tables, such as:

Three, an arithmetic expression can be used in an SQL statement:

+ 、-、 *,/.

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

So SAL columns can do operations and then display them in the query results.

"Attention" Select If you add * , then * And then you can't add anything else, like

SELECT *, sname from A is wrong.

Example 3: Query the Student Table section column, and the Age column data plus 20, such as:

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

Iv. use of aliases in query statements

In a query statement, we can add aliases to 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) The second kind of wording

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, such as:

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: First query the student table and SC table information, such as:

Two more tables, this follow-up will learn:

For commands:

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

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

Where 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 aesthetics, you can add aliases to columns:

Expand: You can combine new data for the data in each column. For example, merge the sname and Sno columns in the Student table and merge the new data.

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

Vi. use of distinct

If there are duplicate values in the results of the query, you can use distinct to filter the duplicate values.

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

Example 7: Querying the SC table for information, to repeat

Solution: First query all data in SC table

Then query the SC table for CNO column data

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 Operators value ;

( 2 ) where Column Name comparison Operators An expression ;

( 3 ) where Column Name comparison Operators Column Name ;

Oracle provides the following common 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

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

In fact, if the SQL statement is also a WHERE clause, the two tables follow-up will be detailed.

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 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 employees who have 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

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

Query the Player Table game_id column hollow data

Oracle-18-select Statement &sql use of arithmetic expressions & aliases & join Operators%distinct&where clauses