Oracle Database The next day

1. Course Objectives

① Mastering the basic syntax of SQL queries

②sql the various operations for querying

2. Specific content

If you want to make a SQL query, then we need to use the knowledge of select in Dql, and for simple queries, the popular understanding is to query the data from all the records in the database, but you can control the display of the columns through such syntax.

-Syntax structure for simple queries

SELECT [DISTINCT] *| column name [alias], column name [alias], ...

from table name [alias];

① the concept of explicit clauses, which refer to the SELECT clause and the FROM clause.

② in this program, the FROM clause is the source of the data, as long as it is a table structure (a collection of rows and columns). The SELECT clause controls which columns of data need to be displayed.

③ the execution order of this program is to execute the FROM clause before executing the SELECT clause. This is a good understanding, want to query the data we want, first we have to find the source of the data, here is the first to find the table, and then determine the columns displayed. For example, to buy a bottle of water in the supermarket, we have to go to the supermarket, and then see what to buy water it, you are soda or mineral water and so on.

④ It is important to note that the SELECT clause and the FROM clause must be used at the same time to be valid and not be used alone.

-Control the columns that need to be displayed, which is actually the projection of the data

Example: Querying each employee's number, name, base salary

SELECT Empno,ename,sal

from EMP;

-Arithmetic

Arithmetic is also supported in our simple queries, and the contents of the columns can be used directly

Example: Query the Employee table for basic monthly salary and basic annual salary

SELECT sal,sal*12

from EMP;

-Column Aliases

We are not looking at the above query results in the display uncomfortable ah, sal*12, can not change, of course, this requires us to use column aliases: Two ways, and support the Chinese

SELECT sal,sal*12 as Incom

from EMP;

SELECT sal,sal*12 Income

from EMP;

SELECT sal,sal*12 as basic annual salary

from EMP;

-Connection operator (| | ) is used

SELECT Empno | | Ename

from EMP;

SELECT ename | | 1

from EMP;

SELECT ename | | ' Wages are: ' | | Sal

from EMP;

SELECT Sal | | Comm

from EMP;

In fact, this link operator is not recommended in development, but it is intended to illustrate the use of numbers and strings in the database, the numbers are used directly, the strings are wrapped with ' ', it is important to note that the difference between the column aliases, column aliases in the special display use "".

For example:

Select Sal,sal*12 as "My Salary"

from EMP;

-Remove duplicate rows (using DISTINCT keyword)

SELECT DISTINCT Deptno

from EMP;

① Note here the DISTINCT keyword can only be used immediately after the SELECT keyword

② If multiple columns are displayed, see if the result of the combination is duplicated

SELECT DISTINCT Deptno,job

from EMP;

Oracle Database The next day