Retrieval records

Retrieve all rows and columns

1. Code

   # All data in the query table
   select * from EMP;
   
   # Query specific columns
   select EMPNO, ENAME, JOB, SAL, MGR, HIREDATE, COMM, DEPTNO from EMP;
   

Screening rows

1. Code

   # Filter rows to see rows that satisfy the criteria
   select * from EMP where DEPTNO = 10;
   

Find rows that satisfy multiple query criteria

1. Code

   # Find rows that satisfy multiple criteria
   select *
   from EMP
   where DEPTNO = 10
      or COMM is not null
      or SAL <= 2000 and DEPTNO = 20;
   

Screening column

1. Code

   # Screening column
   select ENAME, DEPTNO, SAL
   from EMP;
   

Create meaningful column names

1. Code

   # Create meaningful column names and use AS to create aliases
   select SAL as salary, COMM as commission
   from EMP;
   

Reference to alias columns in where clauses

1. error code

   # Execution will report errors
   select SAL as salary, COMM as commission 
   from EMP
   where salary < 5000;
   

2. Correct code, using embedded views

   # aliases
   select *
   from (select SAL as salary, COMM as commission from EMP) x
   where salary < 5000;
   

3. Explain that the where clause will be executed before the select clause. In the case of failure, when the where clause is executed, salary and commission do not exist. The alias will not take effect until the where clause is executed. The from clause is queried before the where clause.

Values of tandem multiple columns

1. Find the value of the result

   ENAME	JOB
   CLARK	MANAGER
   KING	PRESIDENT
   MILLER	CLERK

2. The desired result

   CLARK WORKS AS AMANAGER
   KING WORKS AS APRESIDENT
   MILLER WORKS AS ACLERK
   

3. Code

   # Values of tandem multiple columns
   # CONCAT functions can concatenate values of multiple columns
   select concat(ENAME, ' WORKS AS A', JOB) as msg
   from EMP
   where DEPTNO = 10;
   

Use conditional logic in select statements

1. Code

   # Use conditional logic in select statements
   select ENAME,
          SAL,
          case
            when SAL <= 2000 then 'UNDERPAID'
            when SAL >= 4000 then 'OVERPAID'
            else 'OK'
              end as status
   from EMP;
   

2. Result

   ENAME	SAL	STATUS
   SMITH	800	UNDERPAID
   ALLEN	1600	UNDERPAID
   WARD	1250	UNDERPAID
   JONES	2975	OK
   MARTIN	1250	UNDERPAID
   BLAKE	2850	OK
   CLARK	2450	OK
   SCOTT	3000	OK
   KING	5000	OVERPAID
   TURNER	1500	UNDERPAID
   ADAMS	1100	UNDERPAID
   JAMES	950	UNDERPAID
   FORD	3000	OK
   MILLER	1300	UNDERPAID

Restrict the number of rows returned

1. Code

   # Limit the number of rows returned
   select *
   from EMP
   limit 5;
   

Random return of rows

1. Code

   # Random return of rows
   select ENAME, JOB
   from EMP
   order by rand()
   limit 5;
   

Find null values

1. Code

   # Find null values
   select *
   from EMP
   where COMM is null;
   

Converting null values to actual values

1. Code

   # Converting null values to actual values
   # The coalesce function returns the first non-null value in the parameter list.
   select coalesce(COMM, 0), ENAME
   from EMP;
   

   # Use case
   select case
            when COMM is not null then COMM
            else 0 end
   from EMP;
   

Find matches

1. Code

   # Query matches
   # '% I%'means that strings with I appearing anywhere will be retrieved, and'% ER' means that strings ending with ER will be retrieved.
   select ENAME, JOB
   from EMP
   where DEPTNO in (10, 20)
     and (ENAME like '%I%' or JOB like '%ER');
   

Ranking of query results

Returns query results in specified order

1. Code

   # Sals grow up
   select ENAME, JOB, SAL
   from EMP
   where DEPTNO = 10
   order by SAL asc;
   

Multi-field sorting

1. Code

   # First in DEPTNO ascending order, then in SAL descending order
   select EMPNO, DEPTNO, SAL, ENAME, JOB
   from EMP
   order by DEPTNO, SAL desc;
   

Sort by clause

1. Code

   # Result the search results according to the last two characters of the job field
   select ENAME, JOB
   from EMP
   order by substr(JOB, length(JOB) - 2);
   

null Value Processing in Sorting

1. Code

   # null Value Processing in Sorting
   # Add auxiliary columns for sorting
   select ENAME, SAL, COMM
   from (select ENAME, SAL, COMM, case when COMM is null then 0 else 1 end as is_null from EMP) x
   order by is_null desc, COMM;
   

Dynamic adjustment of sorting items according to conditional logic

1. Code

   # Dynamic adjustment of sorting items according to conditional logic
   select ENAME, SAL, JOB, COMM
   from EMP
   order by case when JOB = 'SALESMAN' then COMM else SAL end;