Oracle notes Collection (1)

Oracle notes Collection (1)

User: Username: scott password: tiger login: sqlplus scott/tiger exit: exit to view the tables of the current user: select * from tab; ===============================display the current user SQL> show userUSER as "SYS" switch user SQL> connect scott/tiger already connection. The command can use the abbreviation conn [ect] user to execute the previous SQL statement/set the row width set linesize 150 the default value is 80 to set the page size (the data displayed on one page) set pagesize 100 default is 14 display parameter value show parameter name such as: show linesize permanent save Configuration Modification glogin. SQL file. Note: single line comment: -- Multi-line comment: /**/view the table structure SQL> desc dept ============================== ====== clear screen: in Windows: host clsLinux: host clear sets the width of a column: column job format a15, col job for a15column SAL format 9999, or col salfor 9999: col [umn] column name for [mat] format for string: a20, 20 is a data, indicating the width of 20 characters. The number 9 indicates one digit. There are several values, that is, the width. ============================================================= The processing of null values is not A valid value. It is neither 0 nor a null string. Null! = Nullis not null the expression containing null is equivalent to null. Modify the previous SQL statement ed [it]. Note: Do not write a semicolon to filter the null function nvl (expression, value used when the expression is null) select empno, ename, sal, sal * 12 as annual salary, nvl (comm, 0), (sal * 12 + nvl (comm, 0 )) total income from emp ======================================== string is case sensitive, quotation marks are required for use. When an alias is specified, quotation marks can be added or not added. When spaces or special characters are contained, quotation marks are required. When no quotation marks are added, the display is in uppercase. After quotation marks are added, the display is based on what is written. Use single quotes when using strings. When writing an alias, double quotation marks are required. Remove duplicate rows -- apply to a column of SQL> select distinct job from emp; -- apply to multiple columns, and add the values of all columns to repeat the record SQL> select distinct job, deptno from emp; if only one expression is queried and no data is used in any table, you must write from... it can be written as from dualdual, which is a virtual table and can be directly used. For example, select 3 + 2 select 'hello' | 'World' from dual; string connector SQL> select ename | 'salary: '| sal from emp; ENAME | 'salary: '| SAL ----------------------------- --SMITH's salary is 800ALLEN's salary and 1600WARD's salary. it's 1250JONES's salary: 2975MARTIN's salary: 1250blke's salary: 2850CLARK's salary: 2450SCOTT's salary: 3000KING's salary: 5000TURNER's salary: 1500ADAMS's salary: 1100JAMES's salary: 950FORD's salary the salary of 1300 Miller is. You can also use the function concat (): SQL> select concat ('hello', 'World') from dual;

 

========================================== LIKE, you can use % and _ to represent any number of characters or any character. To express % or _ itself, use an escape character, for example, SQL> select * from emp where ename like 'Ki \ % 'escape '\'; BETWEEN contains two boundaries. A small value must be written to the front, and a sequence is written to the back. Otherwise, no result is returned. INwhere... in (...,...) if it contains null, no effect. For example, query all the employee SQL> select * from emp where empno in (select mgr from emp); where .. not in (..,..,...) if null exists, no results are returned. For example, query all SQL> select * from emp where empno not in (select mgr from emp where mgr is not null) for employees who are not managers ); ========================================================== = date type in MySQL: date, time, and datetimeOracle only have Date. processing of the date 1. query the employee information after the specified date SQL> select * from emp where hiredate> '31-December-81 '; 2, modify the date format SQL> select * from v $ nls_parameters; SQL> alter session set nls_date_format = 'yyyy-mm-dd'; // only valid for the current session 3, how to Use the date function: SQL> select * from emp where hiredate> to_date ('2017-12-31 ', 'yyyy-MM-dd '); or SQL> select * from emp where to_char (hiredate, 'yyyy-MM-dd')> '2017-12-31 '; Syntax: TO_CHAR (date, 'format _ model ') syntax: TO_DATE (str, 'format _ model') format String case-insensitive: Get current time: SQL> select to_char (sysdate, 'yyyy-MM-DD ') from dual; TO_CHAR (SY----------2013-07-30SQL> select to_char (sysdate, 'yyyy-mm-dd') from dual; TO_CHAR (SY----------2013-07-30SQL> select to_char (sysdate, 'yyyy-mm-dd hh: mi: ss ') from dual; TO_CHAR (SYSDATE, 'yy ------------------- 2013-07-30 10:23:31 ==================================== ======

 

Sort:
Order by COLUMN name ,...
It can be used in numbers, dates, and strings.
You can use the column name, expression, alias, and serial number (which indicates the columns in the select statement)
Ascending and descending
Sort by one column and by multiple columns
If the column where order by is located has null, it will:
In ascending order, null is listed below.
In descending order, null is above.
We hope that, whether in ascending or descending order, the null value is always below

Method 1: SQL> select * from emp order by comm desc nulls last; Method 2: select empno, ename, job, hiredate, sal, nvl (comm, 0) from emp order by 6 desc ====================================== ==============================

 

1. The group function select max (sal) highest wage, min (sal) Minimum Wage, avg (sal) average wage, sum (sal) total wage and count (sal) number of employees who receive the salary from emp; 2. The group function processes the null value and queries the average bonus of all employees (the bonus of some employees is null) select sum (comm) the/count (*) average bonus from emp group function automatically filters out null values. When using avg (), pay attention to processing null values: select avg (nvl (comm, 0) from emp; functions can be nested and used

 

3. Group
Group by, which is written after the FROM statement. If there is a WHERE statement, it is followed by the WHERE statement.
The queried columns must be:
Columns that appear in group by (not all columns are written in Select)
Or use group functions.
Group by one column
Group by multiple columns
One of the multiple columns involved in the group is different from the other.
4. Grouping result Filtering
Having is to filter the groups and only display the results that meet the conditions.
Aliases cannot be used in Group by and Having.
Differences from Where
Having is to divide the group before filtering.
Where is used to filter data before grouping.
If possible, try to write the Where condition without Having.

Select...From...Where...Group by ...Having...Order by...================================================

Subquery: when one step cannot be solved, subquery can be used. Divided into: single-row subquery multi-row subquery can be in the select, from, where, having can both be used for subqueries. It cannot be used for group by subqueries in the primary query. A single row operator corresponds to a single row subquery. A multi-row operator corresponds to a multi-row subquery in a select statement, only a single row subquery is required. IN: ANY: smaller than ANY value IN a set, that is, smaller than the maximum value IN the set. Greater than any value in a set, that is, greater than the minimum value. ALL: smaller than ALL values in a set, that is, smaller than the minimum value. Greater than all values in a set, that is, greater than the maximum value.