(Oracle Base article) Select to retrieve Data ____oracle

SELECT statement syntax:

A SELECT statement is the most frequently used statement that begins with a select. The most important part of this is the select and from keywords, which are necessary parts of the query, and other clauses that can be changed according to actual requirements.

Get the specified field data code:

query specifies field data instance code:
Select VEHICLE.ID,VEHICLE.EMPID,VEHICLE.DEVICEID,VEHICLE.IPADDRESS,VEHICLE.CPH from vehicle

Get all field data codes:

Query all field information
SELECT * from vehicle

Use aliases instead of field names in tables

Alias replaces the field name in the table
select Vehicle.id as number, Vehicle.deviceid as device number, vehicle.ipaddress as table name, VEHICLE.CPH as name from vehicle

Using functions to manipulate the fields of a query

Using the SUBSTR function provided by the Oracle database
select VEHICLE.ID,SUBSTR (vehicle.deviceid,1,6) as the cell phone number intercepted, Vehicle.ipaddress, VEHICLE.CPH from vehicle

Get rid of duplicate records in retrieved data

Remove duplicate records from a datasheet using the DISTINCT keyword
select DISTINCT (VEHICLE.COMPID) as Department ID from vehicle

Reduce the correlation sort of data

Working with data in ascending and descending order

You can sort the query's data in ascending and descending order, and if you use an ORDER BY clause for a field instead of the specified sort, it arranges the data for the specified field in ascending order

Sort work related by field of ID (not specifying sort, default is ascending sort)
select * FROM vehicle ORDER by ID

Rehearse related data in descending order

Descending query (ID) field
      SELECT * FROM vehicle ORDER BY ID DESC

Ascending is to place null values first and descending to null values at the end of the house

The null value is placed first (ascending)
select *
from vehicle the order by ID nulls a value in the end (descending)
select * from vehicle D desc nulls Last

To set criteria for a query by using a WHERE clause

The operators that the WHERE CONDITION clause can use are mainly relational operators, comparison operators, and logical operators

1, the relational operators include:<, <=, >, >=, =,!=, <>

2. The comparison operators include:

Is null: Returns True if the operand is null;

Like: Fuzzy query

Between ... and: Verify within range

In: Among a series of values

3. Logical operator

and: And operator

Or: Or operator

Not: Inverse operator

Fuzzy query data:

_: Can replace a character

%: can replace multiple characters

Fuzzy query + descending sort
select * from vehicle  where cph like ' King% ' ORDER by id DESC

Queries specifically for null values

Null value query + descending sort
select * from vehicle where empid are null ORDER BY ID DESC

Group by and a HAVING clause (note: Query that is the result of the function)

The GROUP BY clause and the HAVING clause are not the same as where they are both used for group queries.

Vehicle Table Use COUNT (*) for related group queries

Subquery: A subquery is a nested query, which is a SELECT statement nested within another statement.

If the subquery returns a multiline value, the In keyword is required.

Simple connection Query

SELECT * from User_vehgroup where UserID = (select UserID from Usermain where username= ' Ceshi ')

Subquery in key sub instance Code
//query How many departments does the user have
select User_vehgroup.vehgroupid from User_vehgroup where UserID in (select Usermain.userid from Usermain where usermain.username= ' Ceshi ')

Subquery any and some critical subqueries (showing any one that satisfies the results of the subquery)
 SELECT * from User_vehgroup where Userid=some (select UserID from Usermain wher E username= ' Ceshi ')

Connection Query

Relational data allows tables and tables to exist in a relationship that can link data from two or more tables. Using this relationship, you can query for data that meets the criteria.

The simplest connection query

The simplest connection query is done with commas, and he uses commas to separate the name from the table, which forms the simplest connection query, but does not make much sense in doing so.

SELECT * FROM Usermain,user_vehgroup

INNER JOIN

An inner join, also known as a simple connection, connects two or more tables and can only query for matching records, and unmatched records will not be detected.

1, the equivalent connection (join conditions using the "= =" equal to connect two conditions list)

Equivalent connection
Select User_vehgroup.vehgroupid from Usermain,user_vehgroup where Usermain.userid=user_vehgroup.userid and Usermain.username= ' Ceshi '

2. Non-equivalent connection (use "<", "<=", ">", ">=", "!=", "Between and", "in", etc.) in the connection condition

Non-equivalent connection
select User_vehgroup.vehgroupid from Usermain,user_vehgroup where Usermain.userid!=user_vehgroup.userid and Usermain.username= ' Ceshi '

Outer Joins

The outer joins are divided into left connection, right connection, and whole outer connection.

Left join: Also known as the left-outward connection. A query that uses a LEFT outer join that returns not only the row records that meet the join criteria, but also the entire record of the table on the left

Right connection: Also known as the right outward connection. A query that uses a right outer join that returns not only the row records that meet the join criteria, but also all records from the right table

Full outer join: Also known as full outer join. Returns a record that matches successfully, returns a record that does not match successfully on the left table, and returns a record that matches the success of the right table.