Oracle Database-Basic SELECT statement

1. Basic SELECT statement

1) View Current user
Show User
USER is "SCOTT"
2) Query the table under the current user
Sql> select * from tab;

Tname Tabtype Clusterid
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
Salgrade TABLE

3) View the structure of the employee table
Sql> desc EMP
is the name empty? Type
----------------------------------------- -------- ----------------------------
EMPNO not NULL number (4)
Ename VARCHAR2 (10)
JOB VARCHAR2 (9)
MGR Number (4)
HireDate DATE
SAL Number (7,2)
COMM Number (7,2)
DEPTNO Number (2)

4) Clear Screen
Sql> Host CLS

5) Check all employee information
Sql> select * from EMP;

6) Set the line width
Sql> Show Linesize
Linesize 80
Sql> Set Linesize 120
7) Set the column width
Sql> Col ename for A8
Sql> Col Sal for 9999
Sql>/

　8) By Column name
Sql> Select Empno,ename,job,mgr,hiredate,sal,comm,deptno
2 from EMP;

　　

　　Principles of SQL Optimization:
1. Use column names as much as possible

9) Query Employee information: Employee Number name monthly salary
Sql> Select Empno,ename,sal

C command change
　　　

　11) Query Employee Information: Employee Number name monthly salary
Sql> Select Empno,ename,sal,sal*12
2 from EMP;

　　12) Query Employee information: Employee Number name salary annual salary bonus annual income
　　Sql> Select Empno,ename,sal,sal*12,comm,sal*12+comm
2 from EMP;

　　

　　NULL in SQL
1. An expression that contains null is NULL
2, NULL forever! =null

NVL (A, b) nvl2 when A is null and the value is
　　

　NULL in SQL

2, NULL forever! =null
　　15) search for employees with bonus null
　　Sql> SELECT *
2 from EMP
3 where comm=null;

　　　

　Sql> SELECT *
2 from EMP
3 where comm is null;

　　

　　16) Alias of column

　　Select Empno as "employee number", ename "name", Sal "Monthly Salary", SAL*12,COMM,SAL*12+NVL (comm,0)
From EMP
　　

　　Distinct remove duplicate records

Sql> select Deptno from emp;

　　Sql> SELECT DISTINCT deptno from EMP;

　　Sql> Select Job from EMP;

　　Sql> SELECT distinct job from EMP;

sql> SELECT distinct deptno,job from EMP;

　　Distinct acts on all subsequent columns

18) connector | |

concat function

　　Sql> Select Concat (' Hello ', ' World ') from EMP;

　　

　　Sql> Select Concat (' Hello ', ' world ') from dual;

Dual table: Pseudo table
Pseudo-Column
Select ' Hello ' | | ' World ' string from dual;

　　

　　Query Employee Information: * * * Salary is * * * *
Sql> Select Ename| | ' The salary is ' | | Sal information from EMP;

　　

Oracle Database-Basic SELECT statement