Summary of simple SELECT query statements in SQL, and SQL SELECT query statements

Summary of simple SELECT query statements in SQL, and SQL SELECT query statements

-- Scott users cannot use it. Use system to log on
-- Modify scott account to unlock
Alter user scott account unlock;
-- Reset the password identified to be recognized
Alter user scott identified by tiger;

-- Select the scott user department table for all fields
SELECT * FROM scott. dept;
-- Employee table
SELECT * FROM scott. emp;

-- SELECT {*, column [alias],...} FROM table;
SELECT empno, ename, sal FROM scott. emp;

-- Arithmetic expression in SELECT statement
SELECT empno, ename, sal, sal * 12 FROM scott. emp;

-- Operator priority
SELECT empno, ename, sal, sal * 12 + 100 FROM scott. emp;
SELECT empno, ename, sal, sal * (12 + 100) FROM scott. emp;

-- String connection operator \ (equivalent to java ++)
-- Connect the data of two fields _
SELECT empno | '_' | ename FROM scott. emp;

/**
Key Point: NULL
A null value is an unallocated value that is unavailable.
The null value is not equal to zero or space.
NULL values are supported for any type.
Any arithmetic expression that includes null values is equal to null.
*/
-- Query all columns in scott's emp table
SELECT * FROM scott. emp;
-- Query all columns in scott's emp table. The condition is comm = 0.
SELECT * FROM scott. emp WHERE comm = 0;
-- Query all columns in scott's emp table. The condition is comm or null.
SELECT * FROM scott. emp WHERE comm = '';
-- Query all columns in scott's emp table. The condition is comm or null.
SELECT * FROM scott. emp WHERE comm = NULL;
SELECT * FROM scott. emp WHERE comm is null;
SELECT * FROM scott. emp WHERE comm is not null;
SELECT empno, ename, sal, comm, (sal + comm, sal) * 12 FROM scott. emp;

-- Nvl function (Java method, passing parameters to return results)
-- Nvl (first parameter, second parameter). If the first parameter is null, the second parameter is used.
SELECT empno, ename, sal, comm, NVL (sal + comm, sal) * 12 FROM scott. emp;

-- Defines the field alias. Note that the alias does not support single quotation marks.
SELECT empno, ename, sal, sal * 12 AS yearsal FROM scott. emp;
SELECT empno, ename, sal, sal * 12 yearsal FROM scott. emp;
SELECT empno, ename, sal, sal * 12 AS "yearsal" FROM scott. emp;
SELECT empno, ename, sal, sal * 12 "yearsal" FROM scott. emp;
-- Query rules: empno is the columnName column name, and eID is the columnLable column label (custom alias)
SELECT empno AS eID, ename, sal, sal * 12 AS yearsal FROM scott. emp;
-- GetInt (String columnLable) in JDBC is an alias if an alias exists. If no alias exists, columnLable is an alias.

-- The DISTINCT keyword is obviously different.
-- By default, the query displays all rows, including duplicate rows.
SELECT deptno FROM scott. emp;
-- DISTINCT keywords remove duplicate data
Select distinct deptno FROM scott. emp;
-- DISTINCT is a combination of all the following fields.
Select distinct deptno, ename FROM scott. emp;
SELECT * FROM scott. emp;
Select distinct deptno, job FROM scott. emp;
-- Why does DISTINCT apply to the combination of all the following fields?
Select distinct deptno, ename FROM scott. emp WHERE deptno = 30;
-- After DISTINCT deptno, for example, 30 has only one record, and 30 has six ename, so the complete data cannot be displayed.

-- The where clause restricts data filtering and must be followed by the from clause
SELECT * FROM scott. emp WHERE deptno = 20 OR deptno = 30;
SELECT * FROM scott. emp WHERE deptno = 30 AND mgr = 7698;

-- Comparison operator
SELECT * FROM scott. emp WHERE sal> = 800 AND sal <= 1600;
-- Between and
SELECT * FROM scott. emp WHERE sal BETWEEN 800 AND 1600;
-- In contains, which is split into a heap or
SELECT * FROM scott. emp WHERE deptno = 20 OR deptno = 30;
SELECT * FROM scott. emp WHERE deptno IN (20, 30 );
-- Like: case-sensitive fuzzy search
-- % Match all _ match one character
-- Query all employees whose names start with "s"
SELECT * FROM scott. emp WHERE ename LIKE's % ';
-- Query all employees ending with "s"
SELECT * FROM scott. emp WHERE ename LIKE '% s ';
-- Query employees whose names contain "S"
SELECT * FROM scott. emp WHERE ename LIKE '% S % ';
-- Query the employee whose second character is A in the name
SELECT * FROM scott. emp WHERE ename LIKE '_ A % ';

-- Priority 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 is sorted...
-- Descend in descending order
-- Ascend ascending
-- Sort the result set order by asc (default in ascending order) desc (descending order)
-- Note: order by can only appear in the last row 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;