Shulou(Shulou.com)06/01 Report--

Video course: Li Xinghua Oracle from introduction to proficiency in video courses

Learner: sunny Rono

Video source: 51CTO College

If you want to query, definitely use the query part of the DML support, compared to a simple query, the popular understanding is that all records are queried, but the syntax can control whether the column is displayed or not.

The SQL syntax structure of a simple query is as follows:

② SELECT [DISTINCT] * | column [alias], don't [alias], … .① FROM table name [alias]

In the order of execution, they start with ①, and then execute ②.

For example: if you use "*" in the select clause to query all the data columns in a table.

Example: query all records in the emp table

SELECT * FROM emp

In this procedure, the from clause determines the data source, as long as the source is a table structure (a collection of rows and columns), while the select clause controls the required data columns.

Example: projection of data-controls the columns of data that need to be displayed.

Query the number, name and basic salary of each employee

Syntax format:

SELECT empno,ename,sal,job FROM emp

In addition to the basic query columns, four operations are also supported in a simple query, and four operations can be performed directly using the contents of the column.

Request to find out the serial number, name and basic annual salary of each employee (monthly salary sal, annual salary * 12)

Syntax format:

SELECT empno,ename,sal*12 FROM emp

Found that at this time part of the list name is not good-looking, in order to achieve an aesthetic effect, you can set aliases.

We can write like this. However, under normal circumstances, the use of Chinese is not recommended.

SELECT empno employee number, ename employee name, sal*12 Annual salary FROM emp

In fact, it is also supported in Chinese when defining table names or column names later, but you want you to develop a normal point and try to use English as much as possible.

In fact, in the process of making a simple query, the data connection operation is also supported, using "| |" to connect.

Example: observe the connection.

SELECT empno | | ename FROM emp

The result is as follows:

Believe in this result picture, many people must have read it. What is it? In order to make this connection look better. We can use some written descriptions in the middle.

For example, we now want the final format to be: "number: XXX, name: XXX". For our numbers and names, both the data columns are queried through the data table. For some fixed output content must be processed, for this part of the processing only consider using two types of data:

Ordinary numbers: then write it directly. (SELECT ename | | 1 FROM emp;)

String: described in single quotation marks. (SELECT empno | | 'hello' FROM emp;)

Example: implement formatted output

Syntax format:

SELECT'No.:'| | empno | | 'name:' ename FROM emp

In a simple query, there is a DISTINCT keyword whose main purpose is to eliminate duplicates.

Example: query the position information of all employees.

In the figure, we will find that there are a lot of people working together, so now if you don't want to have duplicate content in it, you can use DISTINCT in the SELECT clause to eliminate duplicate content.

Syntax format:

SELECT DISTINCT Job FROM emp

The result is shown in the figure:

Note: if duplicates are eliminated, it refers to the results of repeated queries in all columns of the queried data.

Example: observe the results of the following query

SELECT DISTINCT ename,Job FROM emp

The results are as follows:

I'm sure you'll see another 14 lines of data. At this time

Because there is no repetition of the name and position at the same time, it will not be possible to delete the duplicate content.

Summary: the biggest feature of a simple query is that it controls the data column, but it cannot control the data column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.