An Introduction to Oracle database query basics

Simple table Operations

directive: Create a simple table: CreateTable Student (name Varchar2 (), age number (3));

Insert new record: INSERT INTO studentvalues (' stone ', 24);

View table structure: DESC student;

Query record: SELECT * from student;

Delete table: drop table student;

Delete data from table: Delete from studentwhere condition;

Description: Sqlplusworksheet with "--" as a single-line comment tag. VARCHAR2 (20) indicates a variable-length string with an upper limit of 20

Number (3) indicates that the age is a 3-bit integer that does not contain a decimal point. If only number is defined, the system defaults to a 10-bit integer

The single quoted character is the representation of a string constant in the SQL language. Keyword capitalization in the SQL language is not sensitive

Database developers prefer to use uppercase keywords. The majority of application developers use lowercase to achieve good readability

When you insert a record, you do not have a permanent immediate effect in the database, and you can use commit to submit the update action for the data

A simple SELECT statement

Syntax format: SELECT *| {< field name >,...} From < table name >;--fields are separated by commas. Adding spaces after commas can enhance readability

For example, select * from emp, or select Empno, ename, Sal from EMP;

Display effect: Sql*plus default display: Date and character data left-aligned, numeric data right-aligned, column headings default to uppercase

Isql*plus default display: Column headings are displayed in uppercase by default, column headings are centered by default

Use an arithmetic expression

Overview: In a SELECT statement, you can use arithmetic operators (+ 、-、 *,/) to create an expression on the number type

For example: select Empno, ename, Sal, sal*12 from emp;--query the employee's number name in the EMP, salary and yearly salary

Select Empno, ename, Sal, sal*12+1000 from EMP;

Select Empno, ename, SQL, sal* (12+1000) from emp;--can change the order of calculation using parentheses

Connection operators

Overview: That is, "| |" You can concatenate columns with characters or other expressions to get a new string that implements the functionality of the "compositing" column

Example: Select Ename | | ' is a ' | | Job from EMP;

Select Empno, ename | | s annual salary is ' | | Sal*12 from EMP;

Description: A delimited token that represents a string constant with a single quotation mark. Represents a single quote character that appears in a string constant with two single quotes

In the query, you do not need an escape character, directly using two single quotes to represent a single quotation mark in a string constant

Using field aliases

Overview: Rename fields in query results to enhance readability. Use double quotes when using special characters in aliases or forcing output capitalization

Format: SELECT < field name >|< expression > [[as]< field alias],... from< Table name >;

For example: select Empno, ename "ename", sal*12 "yearly Salary" from EMP;

Select Empno as employee number, ENAME employee name, sal*12 "yearly Salary" from EMP;

Description: As can not be added. Aliases can be raised in double quotes. If the alias does not contain special characters, double quotes can be omitted

If the alias contains a space, such as "annual salary", you must add double quotes, or it will be resolved into two components, resulting in grammatical problems

If ename does not enclose double quotes, the execution is displayed as ename. Only double quotes are given to it to show the following significant lowercase

Null value

Overview: Null values are invalid, unspecified, unknown, or unpredictable values. Null value is not equal to 0 or space

For example: select Empno, ename, Sal, comm from emp;--the Comm properties of some records will show a blank, i.e. null value

INSERT into student (age) VALUES (24)--If you do not specify a name value when inserting data, the default name is the null value

Add: If null values appear in arithmetic expressions, the entire expression results in null

Null values that appear in a join expression are treated as an empty (zero-length) string

such as select Ename, Sal, comm, ename | | ' - ' || Comm, sal+comm from EMP;

If a null value is involved in an arithmetic expression, you can use the corresponding function to set the default value for a field that might have a null value

After judgment, if it is null, return 0, if it is not NULL, return its true value, and then perform arithmetic operations

Remove Duplicate rows

Overview: By default, all record rows that match the criteria are displayed in the query results, including duplicate rows. You can use distinct to clear duplicate rows

The scope of the distinct is the combination of all subsequent fields, that is, the combination of the following fields does not appear to be repeated.

For example: The Select Deptno from emp;--is intended to query the type of department number, showing all numbers, including duplicate numbers

Select distinct Deptno from emp;--clear Duplicate department numbers in query results

Select DISTINCT Deptno, the job from emp;--as long as the results of deptno and job combinations are not duplicated, the output is displayed

Description: Usually very simple data processing, including the query, combination, filtering or conversion types, etc., are handed to the database for processing

such as grouping statistics, summing, averaging, filtering null values, filtering duplicate values, space filtering at the beginning and end of data, string-date type, and so on

For complex business logic, or content related to business logic, it is recommended that you handle it in your application. This also allows for a loose coupling

Sorting query Results

Overview: Query results are arranged by default in the order in which records are inserted. Query results can be sorted by using the ORDER BY clause

The sort methods include ascending (ASC, default) and Descending (DESC). You can also sort by using field aliases or by multiple fields

For example: select Empno, ename, Sal from EMP ordered by sal;--sorted by Sal in ascending order, ASC can omit not write

Select Empno, ename, Sal from EMP ordered by Sal desc;--Sorted by Sal

Select Empno, ename, sal*12 annsal from emp order by Annsal;

Select Deptno, Empno, ename, Sal from emp order by Deptno, Sal;

Description: When sorting by multiple fields, first sort by deptno, if there are duplicate Deptno records in the resulting permutation.

The duplicate Deptno records are then sorted by Sal. As for the exact sort method, you need to specify two fields individually

such as deptno,sal means that both are in ascending order. Deptno,sal desc says Deptno in ascending order, Sal in descending order

As Deptno desc,sal indicates deptno in descending order, Sal is sorted in ascending order. So

This logic is appropriate for the database to be processed during the query phase. It can be difficult to implement in your application through code.

Conditional query

Note: string and date values are enclosed in single quotes when using strings or dates in query statements or query conditions

String case sensitive. Date value format is sensitive and the default date format is DD-MON-RR

Note: The case is sensitive to the comparison of the contents of the string

Format: SELECT *| {[distinct]< field name >|< expression >[< alias],...} from< table name > [WHERE < query conditions >];

Example: SELECT * from emp where ename= ' Smith ';--The Smith query here is strictly case-sensitive

SELECT * from emp where hiredate= ' February-April-81 ';--the system automatically converts strings that conform to the Convention format to the date type

Select SysData from dual;--to obtain the current system time in the default date format

Description: Table dual only one row, it does not save the system time, Sysdate always represents the current system time

Usually in a simple query or operation, the dual table acts as a medium, making the form look like a query

The advantage of dual is that there is only one line of records, which typically tests certain instructions while practicing. Generally not in the real development use it

For example, select 3+2 from dual; return 5. Another example is a select 3+2 from EMP, which returns 5 of rows

It will query each line of the EMP, but not the actual saved content, but the line-by-row calculation 3+2

And then the resulting 5 as a unique result, is also the only one field returned, so the result is 10 more lines of 5

For an Oracle database, even if you want it to do a simple constant expression of the operation, you need to take the form of questions and answers

Even when all the data is in the user's hands, pretend to be a query, give it an instruction to return the result

Relational operators

Symbol: BETWEEN ... And ... : Bounded between two values, including boundaries, and must put small values in the front

In (set): Appears in the collection. A record that determines whether a field is equal to a value in the collection

Like: Fuzzy query, also called a wildcard query

Is null: determining null value

Example: SELECT * from emp where Sal between 1600 and 2900;--query for information about employees between 1600 and 2900

SELECT * from EMP where ename in (' SMITH ', ' King ', ' King ');--Returns a record of the value of the element whose name is set

SELECT * FROM emp where comm is null;--returns records with null values for all COMM fields

The SELECT * from EMP where comm are not null;--returns NON-EMPTY. Note that 0 and null are different.

Note: The capitalization is sensitive to the comparison of the contents of the string. The value in the in () needs to be fully matched to the Ename value to query out the record

like:% represents 0 or more characters. _ Represents a character. You can use escape to identify special symbols

SELECT * from emp where ename like ' s% ';--queries the records of names that begin with S. Here s is still case sensitive

SELECT * from emp where ename like ' _a% ';--a record that queries the name of a for the second letter

SELECT * from emp where ename like '%_% ' escape ';--Returns a record with underlined names

The escape here indicates the function of the escape character. In real-world development, this escape character is not used much

The escape character in Oracle can be defined arbitrarily, so "" in the example can also be replaced by other symbols, such as "K"

Description: A delimited token that represents a string constant with a single quotation mark. Represents a single quote character that appears in a string constant with two single quotes

In a fuzzy query, you don't need an escape character, you can use two single quotes to represent a single quotation mark that appears in a string constant.

logical operators

Overview: Priority from high to Low: Not→and→or

Example: SELECT * from emp where deptno=20 and sal>2000;--returns a record with a department number of 20 and a salary greater than 2000

SELECT * from emp where deptno=20 or sal>2000;--returns a person with a department number of 20 or a record with a salary greater than 2000

SELECT * from EMP where deptno not in (20,30,40);--Returns information about the person that the department number does not appear in the collection