Oracle Learning (1): basic syntax
After logging on to and installing Oracle with the basic Oracle syntax, enable the Oracle service in services, run cmd, and enter sqlplus scott/Your scott PasswordConnection successful. Screen recording and cleaning
SQL> -- screen recording is a txt file
SQL> spool off
SQL> -- clear screen
SQL> host cls
Select basic query SQL> -- tables under the current user
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-----------------------------------------------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE 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)
SQL> desc dept
Is the name empty? Type
-----------------------------------------------------------------------------
Deptno not null number (2)
DNAME VARCHAR2 (14)
LOC VARCHAR2 (13)
SQL> -- Query Information of all employees
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------------------------------------------------------------------
DEPTNO
----------
7369 smith clerk 7902-12-80 800
20
7499 allen salesman 7698 20-2 months-81 1600 300
30
7521 ward salesman 7698 22-2 month-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------------------------------------------------------------------
DEPTNO
----------
7566 jones manager 7839 2975-81
20
7654 martin salesman 7698 28-9 month-81 1250 1400
30
7698 blake manager 7839 01-5 months-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------------------------------------------------------------------
DEPTNO
----------
7782 clark manager 7839-81 2450
10
7788 scott analyst 7566 13-7 month-87 3000
20
7839 king president 17-11-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------------------------------------------------------------------
DEPTNO
----------
7844 turner salesman 7698 month-81 1500 0
30
7876 adams clerk 7788 13-7 month-87 1100
20
7900 james clerk 7698-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
-------------------------------------------------------------------------
DEPTNO
----------
7902 ford analyst 7566 03-12 months-81 3000
20
7934 miller clerk 7782 23-1 month-82 1300
10
14 rows have been selected.
Display table settings SQL> -- set row width
SQL> set linesize 150
SQL> -- set the column width
SQL> col ename for a8 (a represents a string a8 represents an 8-Bit String)
SQL> col sal for 9999 (each 9 represents a digit from 0 to 9)
SQL>/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7499 allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 jones manager 7839 2975-81 20
7654 martin salesman 7698 28-9 month-81 1250 1400 30
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566 13-7 month-87 3000 20
7839 king president 17-11 month-81 5000 10
7844 turner salesman 7698 month-81 1500 0 30
7876 adams clerk 7788 13-7 month-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10
14 rows have been selected.
SQL> set pagesize 20 (set the number of entries displayed on each page)
SQL>/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7499 allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 jones manager 7839 2975-81 20
7654 martin salesman 7698 28-9 month-81 1250 1400 30
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566 13-7 month-87 3000 20
7839 king president 17-11 month-81 5000 10
7844 turner salesman 7698 month-81 1500 0 30
7876 adams clerk 7788 13-7 month-87 1100 20
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10
14 rows have been selected.
SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7499 allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 jones manager 7839 2975-81 20
7654 martin salesman 7698 28-9 month-81 1250 1400 30
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566 13-7 month-87 3000 20
7839 king president 17-11 month-81 5000 10
7844 turner salesman 7698 month-81 1500 0 30
7876 adams clerk 7788 13-7 month-87 1100 20
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10
14 rows have been selected.
SQL> -- * And all columns. Which one is better?
SQL> -- the column name is good, but it can be considered the same after oracle 9i
SQL> host cls
Query Multiple SQL statements> -- Query employee numbers, names, and monthly salary
SQL> select empno, ename, sal
2 from emp;
EMPNO ENAME SAL
-----------------------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows have been selected.
Statement error: how to modify SQL> -- Query employee ID, name, monthly salary, and annual salary
SQL> select empno, ename, sal, sal * 12
2 form emp;
Form emp
*
Row 3 has an error:
ORA-00923: the requested FROM keyword not found
SQL> -- use the c command to modify the previous SQL
SQL> 2
2 * form emp
SQL> c/form/from
2 * from emp
SQL>/
Empno ename sal * 12
---------------------------------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
14 rows have been selected.
SQL> -- use the ed command
SQL> ed
File afiedt. buf written
1 select empno as "employee number", ename "name", sal monthly salary, sal * 12 annual salary
2 * from emp
SQL>/
Employee ID name monthly salary
----------------------------------------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
14 rows have been selected.
SQL> ed
File afiedt. buf written
1 select empno as "employee number", ename "name", sal monthly salary, sal * 12 annual salary
2 * from emp
SQL>/
Select empno as "employee number", ename "name", sal monthly salary, sal * 12 annual salary
*
Row 3 has an error:
ORA-00923: the requested FROM keyword not found
SQL> ed
File afiedt. buf written
1 select empno as "employee number", ename "name", sal "monthly salary", sal * 12 annual salary
2 * from emp
SQL>/
Employee ID name monthly salary
----------------------------------------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
7566 JONES 2975 35700
7654 MARTIN 1250 15000
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
14 rows have been selected.
SQL> hostlcs
SP2-0042: Unknown command "hostlcs"-ignore for other lines.
SQL> host cls
SQL> -- Query employee ID, name, monthly salary, annual salary, and annual income
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7499 allen salesman 7698 20-2 month-81 1600 300 30
7521 ward salesman 7698 22-2 month-81 1250 500 30
7566 jones manager 7839 2975-81 20
7654 martin salesman 7698 28-9 month-81 1250 1400 30
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566 13-7 month-87 3000 20
7839 king president 17-11 month-81 5000 10
7844 turner salesman 7698 month-81 1500 0 30
7876 adams clerk 7788 13-7 month-87 1100 20
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10
14 rows have been selected.
Query of null values
SQL> ed
File afiedt. buf written
1 * select * from emp
SQL> select empno, ename, sal monthly salary, sal * 12 annual salary, comm bonus, sal * 12 + comm annual income
2 from emp;
Empno ename monthly salary, annual salary, bonus, and annual income
----------------------------------------------------------
7369 SMITH 800 9600
7499 ALLEN 1600 19200 300 19500
7521 WARD 1250 15000 500 15500
7566 JONES 2975 35700
7654 MARTIN 1250 15000 1400 16400
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000 0 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
14 rows have been selected.
SQL> /*
SQL> null values:
SQL> 1. Expression indicating null is null
SQL> 2. in SQL, null! = Null
SQL> */
SQL> ed
File afiedt. buf written
1 select empno, ename, sal monthly salary, sal * 12 annual salary, comm bonus, sal * 12 + nvl (comm, 0) annual income
2 * from emp
SQL>/
Empno ename monthly salary, annual salary, bonus, and annual income
----------------------------------------------------------
7369 SMITH 800 9600 9600
7499 ALLEN 1600 19200 300 19500
7521 WARD 1250 15000 500 15500
7566 JONES 2975 35700 35700
7654 MARTIN 1250 15000 1400 16400
7698 BLAKE 2850 34200 34200
7782 CLARK 2450 29400 29400
7788 SCOTT 3000 36000 36000
7839 KING 5000 60000 60000
7844 TURNER 1500 18000 0 18000
7876 ADAMS 1100 13200 13200
7900 JAMES 950 11400 11400
7902 FORD 3000 36000 36000
7934 MILLER 1300 15600 15600
14 rows have been selected.
SQL> -- 2. in SQL, null! = Null
SQL> query employees with blank bonuses
SP2-0734: Unknown command starting with "query bonus is..."-ignore the remaining lines.
SQL> -- Query employees with blank bonuses
SQL> select *
2 from emp
3 where comm = null;
Unselected row
SQL> ed
File afiedt. buf written
1 select *
2 from emp
3 * where comm is null
SQL>/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------------------
7369 smith clerk 7902-12-80 800 20
7566 jones manager 7839 2975-81 20
7698 blake manager 7839 01-5 months-81 2850 30
7782 clark manager 7839-81 2450 10
7788 scott analyst 7566 13-7 month-87 3000 20
7839 king president 17-11 month-81 5000 10
7876 adams clerk 7788 13-7 month-87 1100 20
7900 james clerk 7698 03-12 months-81 950 30
7902 ford analyst 7566 03-12 months-81 3000 20
7934 miller clerk 7782 23-1 month-82 1300 10
10 rows have been selected.
SQL> 1. Expression indicating null is null
SP2-0734: Unknown command starting with "1. Meaning empty..."-ignore the remaining lines.
SQL> host cls
Searching duplicate values
SQL> -- DISTINCT: Repeat only once
SQL> select deptno from emp;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
14 rows have been selected.
SQL> ed
File afiedt. buf written
1 * select DISTINCT deptno from emp
SQL>/
DEPTNO
----------
30
20
10
SQL> select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
14 rows have been selected.
SQL> ed
File afiedt. buf written
1 * select distinct deptno, job from emp
SQL>/
DEPTNO JOB
-------------------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
Nine rows have been selected.
SQL> -- if multiple columns are followed by distinct, all columns are followed.
SQL> host cls
Concatenate a string SQL> -- concat: concatenate a string
SQL> select concat ('hello', 'World') from dual;
CONCAT ('El
-----------
Hello World
Dual pseudo table SQL> -- dual table: pseudo table, which is convenient for testing and meets syntax requirements
SQL> select 3 + 2 from dual;
3 + 2
----------
5
SQL> select sysdate from dual;
SYSDATE
--------------
25-3 months-12
Connector SQL> -- Connector
SQL> select 'hello' | 'World' from dual;
'Hello' | 'W
-----------
Hello World
SQL> -- Query employee information: the job is ****
SQL> select ename | 'job is' | job
2 from emp;
ENAME | 'JOB is' | JOB
---------------------------
SMITH's job is CLERK.
ALLEN's position is SALESMAN.
WARD is a SALESMAN.
JONES is a MANAGER.
MARTIN's position is SALESMAN.
The job of BLAKE is MANAGER.
CLARK is a MANAGER.
SCOTT is in ANALYST.
KING is a PRESIDENT.
TURNER's position is SALESMAN.
ADAMS is named CLERK.
JAMES's position is CLERK.
FORD is in ANALYST.
MILLER's position is CLERK.
14 rows have been selected.
SQL> /*
SQL> master:
SQL> 1. Null Value
SQL> 2. distinct
SQL> 3. Connector
SQL> 4. dual table
SQL> */
SQL> desc emp
Is the name empty? Type
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------
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)
SQL> select ename | 'job is' | job
2 from emp;
ENAME | 'JOB is' | JOB
---------------------------
SMITH's job is CLERK.
ALLEN's position is SALESMAN.
WARD is a SALESMAN.
JONES is a MANAGER.
MARTIN's position is SALESMAN.
The job of BLAKE is MANAGER.
CLARK is a MANAGER.
SCOTT is in ANALYST.
KING is a PRESIDENT.
TURNER's position is SALESMAN.
ADAMS is named CLERK.
JAMES's position is CLERK.
FORD is in ANALYST.
MILLER's position is CLERK.
14 rows have been selected.
SQL Export Import SQL> save c: \ aa. SQL
File c: \ aa. SQL has been created.
SQL> @ c: \ aa. SQL
ENAME | 'JOB is' | JOB
---------------------------
SMITH's job is CLERK.
ALLEN's position is SALESMAN.
WARD is a SALESMAN.
JONES is a MANAGER.
MARTIN's position is SALESMAN.
The job of BLAKE is MANAGER.
CLARK is a MANAGER.
SCOTT is in ANALYST.
KING is a PRESIDENT.
TURNER's position is SALESMAN.
ADAMS is named CLERK.
JAMES's position is CLERK.
FORD is in ANALYST.
MILLER's position is CLERK.
14 rows have been selected.
SQL> spool off
Note: The SQL language is case insensitive. SQL statements can be written in one or more rows. Keywords cannot be abbreviated or the sub-statements of different branches are generally written in different branches. Use indentation to improve statement readability.
NULL values are invalid and unspecified. Unknown or unpredictable values are not blank or 0.
The values of all mathematical expressions containing null values are null.
Column alias: rename a column. Easy to calculate. Keep up with the column name. You can also add the keyword 'as' between the column name and the alias. the alias uses double quotation marks, so that the alias contains spaces or special characters and is case sensitive. AS can omit the connector: concatenate the column and column, and the column and character. '|. It can be used to 'composite 'columns. A string can be a character, number, or date in the SELECT list. The date and character can only appear in single quotes. Each time a row is returned, the string is output once.
Duplicate rows by default, the query returns all rows, including duplicate rows.
Use the keyword 'distinct 'in the SELECT clause to delete duplicate rows.
SQL and SQL * plusSQL? ANSI standard? Cannot be abbreviated as a keyword? Use statements to control the definition information of tables in the database and the data SQL * Plus environment in the table? One of Oracle's features? Can the keyword be abbreviated? Command cannot change the value of data in the database to run in a centralized manner
