Summary of Select Simple query statements in SQL

--scott user not available, use System login
--Modify the user Scott account to unlock
ALTER USER SCOTT account UNLOCK;
--Reset the password identified is recognized
Alter user Scott identified by Tiger;

--Select all Fields Scott User Department table
SELECT * from Scott.dept;
--Employee table
SELECT * from Scott.emp;

--select{*, column [alias],...} from table;
SELECT empno,ename,sal from Scott.emp;

Arithmetic Expressions in--select statements
SELECT empno,ename,sal,sal * from Scott.emp;

--Precedence of operators
SELECT Empno,ename,sal,sal * + from scott.emp;
SELECT Empno,ename,sal,sal * (+ +) from Scott.emp;

--string's connection operator \ \ (equivalent to Java + +)
--Connect two fields of data using _.
SELECT Empno | | ' _ ' | | Ename from Scott.emp;

/**
Focus: null Empty value
A null value is an unavailable, unassigned value
Null value is not equal to zero or space
Any type can support null values
Any arithmetic expression that includes a null value is equal to an empty
*/
--Query all columns of the EMP table for Scott users
SELECT * from Scott.emp;
--Query all columns of the EMP table of the Scott user, with a condition of comm equals 0
SELECT * from scott.emp WHERE comm = 0;
--Query all columns of the EMP table for the Scott user, conditional comm equals an empty string
SELECT * from scott.emp WHERE comm = ';
--Query all columns of the EMP table of the Scott user, conditional comm equals null
SELECT * from scott.emp WHERE comm = NULL;
SELECT * FROM Scott.emp WHERE comm is NULL;
SELECT * FROM Scott.emp WHERE comm are not NULL;
SELECT Empno,ename,sal,comm, (sal + comm,sal) * from Scott.emp;

--nvl function (Java method, pass parameters in return to the result)
--NVL (first argument, second argument), if the first argument is null, take the second argument
SELECT EMPNO,ENAME,SAL,COMM,NVL (sal + comm,sal) * from Scott.emp;

--Define the alias of the field, note that the alias does not support the use of single quotation marks
SELECT empno,ename,sal,sal * Yearsal from Scott.emp;
SELECT empno,ename,sal,sal * Yearsal from Scott.emp;
Select Empno,ename,sal,sal * as "yearsal" from Scott.emp;
Select Empno,ename,sal,sal * "Yearsal" from Scott.emp;
--Query rules: EMPNO is called columnname column name, Eid is called columnlable column label (own defined alias)
SELECT Empno as Eid,ename,sal,sal * as yearsal from scott.emp;
--jdbc getInt (String columnlable) is an alias if there is an alias, and Columnlable is an alias if there is no alias

The--DISTINCT keyword is distinctly different.
--The query displays all rows by default, including duplicate rows
SELECT Deptno from Scott.emp;
--distinct keyword removal of duplicate data
SELECT DISTINCT deptno from Scott.emp;
The scope of the--distinct is the combination of all subsequent fields
SELECT DISTINCT deptno,ename from Scott.emp;
SELECT * from Scott.emp;
SELECT DISTINCT deptno,job from Scott.emp;
--why distinct's scope is a combination of all subsequent fields
SELECT DISTINCT deptno,ename from scott.emp WHERE deptno = 30;
--distinct Deptno After example 30 has only one record, and 30 has 6 ename, so the complete data cannot be displayed.

The--where clause restricts filtering data and must follow the From
SELECT * from scott.emp WHERE deptno = OR Deptno = 30;
SELECT * from scott.emp WHERE deptno = mgr = 7698;

--Comparison operators
SELECT * from Scott.emp WHERE sal >= and Sal <= 1600;
--between and
SELECT * from Scott.emp WHERE sal between and 1600;
--in contains, in execution, split into a bunch of or
SELECT * from scott.emp WHERE deptno = OR Deptno = 30;
SELECT * from Scott.emp WHERE deptno in (20,30);
--like Fuzzy queries are case-sensitive
--% Match all _ match one character
--Query all employees who start with "s"
SELECT * from Scott.emp WHERE ename like ' s% ';
--Query all employees who end with "s"
SELECT * from Scott.emp WHERE ename like '%s ';
--Query the employee whose name contains "S"
SELECT * from Scott.emp WHERE ename like '%s% ';
--the second character in the query name is the employee of a
SELECT * from Scott.emp WHERE ename like ' _a% ';

--precedence rules first and then or
SELECT Ename,job,sal
From Scott.emp
WHERE job= ' persident '
OR job= ' salesman '
and sal>1500;

SELECT Ename,job,sal
From Scott.emp
WHERE job= ' salesman '
OR (job= ' persident '
and sal>1500);

--order by ... Sort
--descend Descending
--ascend Ascending
--Sort the result set order by ASC (ascending default) desc (Descending)
--Note: The order by can only appear in the last line of the SQL statement
--Sort by salary from low to high
SELECT * from Scott.emp ORDER by Sal;
SELECT * from Scott.emp ORDER by Sal ASC;
SELECT * from Scott.emp ORDER by Sal Desc;

Summary of Select Simple query statements in SQL