Oracle's use of the database "Getting started, including common SQL statements"

At the command prompt, enter Sqlplus.
or open Sqlplus directly
Scott/tiger (username/password) {or enter username (Scott)-Enter-password (Tiger)}
1.sqlplus
Orcale provides a customer-side tool that can be used to send some SQL
Communicating with a database
Sqlplus command
<1> Native Login
Sqlplus User name/password
Sqlplus User Name
Sqlplus/nolog--> starts the Sqlplus character interface and then conn the user


<2> View current users: Show user
<3> Exit Sqlplus:exit


<4> Clear Screen:
Sql>clear screen
Host CLS.
&LT;5&GT;DESC table name: Show Table structure
<6>set pagesize 20 per page show 20
<7>set Linesize 300, 300 characters per page
Other
Database--"
1 relational database (SQL database) Oracle. SQL Server, Mysql,psql
< tables, records, Columns >
Database--"2 NoSQL (not only SQL is not just a relational library)
The internet era, the storage of big data, the storage of unstructured data
The application of cluster is more extensive
Key/value structure--"REDIS/MEMCACHEDM (Memory Database)
Document Database--"Mongodb
Column database--"Hbase,cassandra
Graph database--"neo4j
2.SQL (structuredquery lanuage) statement
Structured query statement that can be understood as a bridge between client and server side
By using SQL
Better access to manipulate data in the database


With SQL we can manipulate the data in the table (add and remove), create tables, libraries, views, indexes,
Assigning permissions, roles, and so on
SQL classification
&LT;1&GT;DML (Database Manipulation language)--manipulating data in a database table
&LT;2&GT;DDL (Database Definition Language)--manipulating objects in a database
&LT;3&GT;DCL (Data Control Language)--operations are permissions in the database, roles

"1" line selection
"2" Column selection
"3" Table join


The writing principle of sql:
"1" is case insensitive
"2" a SQL statement can be written in one line or in multiple lines
"3" suggests different lines of writing to enhance readability
NOTES:
1. When executing an SQL query statement, the Oracle server resolves
Query statements (through some of the internal algorithms) are
The optimal execution plan (which Oracle server considers optimal), then executes the
Then this process is the most time-consuming
There are general rules for writing SQL statements during development:
2.oracle has a shared pool principle, a SQL query statement that will be stored after analysis.
Next time if there are identical query statements generated, will not be re-parsed
, exactly the same as the case, the space, and so on.
Principle only for simple query (cannot be multi-table query, subquery query)

3. Basic SQL query Statements
Grammar:
Select [all|distinct]< target-list expression >
from< table name or view name >
[where< conditional expression;]
[GROUP BY < list name 1> having < conditional expression;]
[ORDER by < column name 2>asc|desc];


"1" If there is an ORDER BY clause, the result is to follow the < column name 2> ascending or
Sort Descending (by default, in ascending order)






Oracle default instance library (default installed when installed)


Comes with some tables that we can take advantage of mainly:
EMP, DEPT, Salgrade table


SELECT * from EMP;
Description: We are not recommended for use in development use *
* refers to the list of all columns
will go to query the data
Digital dictionaries, instead time-consuming what columns should be queried, directly listed can be


Select Deptno, Dname,loc from dept;
<3> Note:
In development, it is recommended that you only list the columns you need to reduce the IO
Operation to improve efficiency

<4> Remove duplicate columns distinct
Department number from employee's department
Select Deptno from EMP; (many repetitions deptno)
SELECT DISTINCT deptno from EMP;

Application of <5> expressions
[1] View all employees ' names and annual salary
Select Ename, sal*12 from EMP;

[2] Query employee's name and monthly income (assuming bonus is 300 per month)
Select Ename, sal+300 from EMP;


Application of <6> aliases
Select ename,sal* Nianshouru from EMP;
As can be omitted
It is suggested to use aliases to improve retrieval efficiency.






4. Limitations of the query

Select *|distinct column name, expression alias
From table name
WHERE clause
ORDER BY clause;


"1" If there is an ORDER BY clause, the result is to follow the < column name 2> ascending or
Sort Descending (by default, in ascending order)

[1] Query employee's name, salary and sort by employee's salary (from low to high)
Select Ename, Sal from emp order by SAL ASC;
Select Ename, SAL payroll from EMP Order by Sal; (Ascending by default)
Select Ename, SAL payroll from EMP Order by 2; (according to the second column)
[2] Query the employee's name, salary, and follow the employee's name ascending, salary descending sort

Select Ename, SAL payroll from EMP ORDER by ename ASC, Sal Desc;


"2" where
[1] Inquiry name for King's salary
Select Ename, sal from EMP where
ename = ' KING '; To use single quotation marks

[2] The name and salary of an employee whose salary is not equal to 3000 is queried.

Select Ename, sal from EMP where Sal!=3000;
Select Ename, sal from EMP where sal<>3000;
Select Ename,sal from emp where Sal isn't in (3000);


[3] The salary is queried for employees whose salary is greater than or equal to 1000 less than or equal to 3000


Select Ename, sal from EMP where Sal >=1000 and Sal <=2000;


Or
Select Ename, sal from EMP where Sal between and 2000;
(Not between)
[4] Check the name and salary of an employee whose salary is greater than 3000 or less than 1000
Select Ename, sal from EMP where Sal <=1000 or Sal >= 3000;


[5] Check the name and salary of an employee whose salary equals 1000 or 3000
Select Ename, sal from EMP where Sal =1000 or sal = 3000;
Note: Here is the = sign instead of = =
Or
Select Ename,sal from emp where Sal in (1000,3000);

[6] Inquiry into the names and salaries of employees who entered the employment in 87
Fuzzy query
Select Enamel, sal,hiredate from EMP where hiredate
Like '%87% ';
Where:% represents 0 or more characters;

[7] Inquiries about people whose names begin with K
Select Ename,sal from emp where ename like ' K% ';

[8] Query the employee information whose name contains a letter
Select Ename, Sal from the EMP where ename like '%a% ';

[9] Query name The second letter is the employee information of Z
Select Ename,sal from emp where enem like ' _z% ';
where _ represents any one character
[10] The employee's name and salary are on the May 23, 87 of the entry date.
Default date format: ' 23-5-87 ' stands for May 1987 23
Date formats can be modified in the future
Select Ename, sal from emp where hiredate = ' 2 March-May-87 ';


[11] Employee salary for entry date from May 23, 87 to 89
(can also be queried at a certain interval)
Select Ename, sal from EMP
where hiredate>= ' 2 March-May -87 ' and hiredate<= ' 2 March-May 89 ';


[12] The name and salary of the employee who queried for the bonus null
is null/is not null;
Select Ename, sal payroll from EMP
where comm is null;
[13] Query employee information for department number 20 and sort by salary in ascending order
Select Ename, Sal Deptno from EMP
where Deptno = the order by Sal;


[14] Application of the escape character

Select Ename, sal from EMP where ename


Like ' \_s% ';


[15] Format control output:
Specify format output on the right (SMITH is a clerk)
|| For splicing operations
Select Ename | | ' is a ' | | Job
From EMP
where job = ' Clerk ' and ename = ' SMITH ';

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle's use of the database "Getting started, including common SQL statements"