Orcale entry command

[Java]
Orcale entry command
Basic commands:
1. the spool command is used to screen recording in sqlplus.
Spool c: \ 1.txt automatically creates 1.txt, screen recording
 
Spool c: \ 1.txt append
Add to 1.txt
 
2. conn system/orcl switch user connection
3. -- meaning of the comment
4. exit
5. Clear screen: host cls;
6. Change the password:
// Change the password of the system user to system
Alter user system identified by system;
Alter user identified by is a keyword.
 
You can use
Sqlplus sys/aaa as sysdba; -- Log On as the database administrator.
You can enter the password at will.
 
7. Modify the SQL statement:
(1) run the c command
C/alert/alter (incorrect statement/pair Statement)
>/----/Run the previous command
User modified
(2) Modify through ed --- professional modification method
> Ed
Pop up Notepad to modify
8. Unlock: hr is the user name
Alter user hr account unlock;
9. show all display all commands
10. distinct is an important part of repeated rows.
11. set timing on; set the execution time
12. set timing off; time when the execution is canceled
13. set feedback off. When you cancel execution, the number of rows selected is displayed.
14. set feedback on; displays the number of rows selected during execution.
15. set heading off; cancel the header information of the field
16. set heading on; displays the header information of the field.
17. set trimout on;
18. set trimout off;
 
Ii. query table:
1. select * from tabs; query system table
2. select table_name from user_tables; Name of the table under the current user
3. desc table name query table structure
4. set linesize 120 to set the Row Height
5. col name for 9999 characters --- set the length of the stack bit
--- Col sal for 9999 (stack bit length)
6. The default length of set pagesize is 14.
7. set pagesize 0 indicates that there is no limit on the current display. pagination is prohibited.
8. show all to view some default oracle configurations
9. select * from tab to query all tables
10. select ename, job from emp; select the specified row
11. select sal * 12 from emp; operator operation
12. select sal * 12 + nvl (comm, 0) from emp; replace null with 0
13. filter conditions
-The use of the where clause, coupled with filtering conditions, must be followed by the from Table Name
SQL> -- where syntax select * | {distinct | express | clomn} from table name where filter Condition
Select * from emp where deptno = 20;
Select empno from emp where Deton = 20
Select * from emp where job = 'cler ';
Select * from emp where hiredate = '17-August-80'
14. Comparison operators> <=
Select * from e where sal> 1000;
Select * from emp where sal> = 3000;
Select * from emp where sal <= 3000;
Select * from e where sal <> 3000;
Select * from emp where sal! = 3000;
Select * from emp where sal between 1600 and 300;
Select * from emp where job in ('salesman', 'mmanager ');
Select * from emp where sal in (1600,3000 );
Select * from emp where sal = 1600 or sal = 3000;
15. Fuzzy search
Select * from emp where ename like '% SN % ';
Select * from emp where ename like 'sm % ';
Select * from emp where ename like '% SM ';
 
SQL> -- In orcal, % _ indicates % 0 with multiple meanings-multiple characters-one character
SQL> -- the field content % _ does not have any meaning combined with \ escape.
Select * from jobs where job_id like 'it \ _ % 'escape '\';
 
Select * from emp where comm is not null;
Select * from emp where sal> 1600 and job = 'manager ';
Select * from emp where sal> 1600 or job = 'manager ';
Select * from emp where job in ('manager', 'salesman ')
Select * from emp where job not in ('manager', 'salesman ')
16. Sort
Select * from emp order by sal;
Select * from emp order by sal desc;
Select * from emp order by comm;
Select * from emp order by comm desc;
Select * from emp order by nvl (comm, 0) desc;
Select * from emp order by hiredate;
 
SQL> -- sort by alias
SQL> select sal * 12 + nvl (comm, 0) from emp order by sal * 12 + nvl (comm, 0 );
 
Select sal * 12 + nvl (comm, 0) as salary from emp order by salary;
 
SQL> -- Sort multiple columns
SQL> select * from emp order by deptno, sal;

17. You can adjust the format that you can find. Sometimes col value for 9999 cannot be changed, so use the following SQL statement:
SQL> set linesize 120
SQL> col value for a20
SQL> col parameter for a40

 
Note: The SQL language is case insensitive.
SQL statements can be written in one or more rows.
Keywords cannot be abbreviated or branch
Each clause is generally written by a branch.
Use indentation to improve statement readability.


From Han Xiaoshuang's column