Use of Oracle Database [Entry edition, including common SQL statements]

Use of Oracle Database [Entry edition, including common SQL statements]
Enter sqlplus in the command prompt
Or directly open sqlplus
Scott/tiger (User Name/password) {or enter the user name (scott)-press ENTER-enter the password (tiger )}
1. sqlplus
A client tool provided by Orcale, which can be used to send some SQL
Communicate with the database
Sqlplus command
<1> Local Logon
Sqlplus user name/Password
Sqlplus User Name
Sqlplus/nolog --> Start the sqlplus character interface, and then use the conn user


<2> view the current user: show user
<3> exit sqlplus: exit


<4> clear screen:
SQL> clear screen
Host cls.
<5> desc table name: displays the table structure.
<6> set pagesize 20: each page displays 20
<7> set linesize 300, 300 characters per page
Others
Database --"
1 relational database (sqldatabase) oracle. sqlserver, mysql, psql
<Table, record, column>
Database -- "2 nosql (not only SQL is not just a relational database)
In the Internet era, big data storage and unstructured data storage
The cluster is widely used.
Key/value structure -- redis/MemcachedM (memory database)
Document Database-Mongodb
Column database -- Hbase, Cassandra
Graph database -- neo4j
2. StructuredQuery Lanuage statement
Structured Query statements can be understood as a bridge between the client and the server.
By using SQL
Better access and operation of data in the database


Through SQL, we can operate the data in the table (add, delete, modify, and delete), create tables, databases, views, indexes,
Assign permissions and Roles
SQL Classification
<1> DML (database manipulation language) --> operates on data in database tables.
<2> DDL (Database Definition Language) --> operate on objects in the database
<3> DCL (Data Control Language) --> operates on Database permissions and roles.

Select 1
2 Column Selection
3 table join


SQL writing principles:
1 is case insensitive
2. One SQL statement can be written into one or multiple rows.
3. We recommend that you write different words in different branches to enhance readability.
Note:
1. When an SQL query statement is executed, the oracle server will parse
Query statement (obtained through some internal algorithms)
Optimal execution plan (the Oracle Server considers the best), and then execute
This process is the most time-consuming.
In the development process, SQL statements are generally written as follows:
2. oracle has a shared pool principle. An SQL query statement is stored after analysis.
If the same query statement is generated next time, it will not be parsed again.
, Exactly the same in case and space.
Principle only for simple queries (not multi-table queries, subqueries)

3. Basic SQL query statements
Syntax:
Select [all | distinct] <target list expression>
From <Table name or view Name>
[Where <conditional expression>]
[Group by <column name 1> having <conditional expression>]
[Order by <column name 2> asc | desc];


[1] If an order by clause exists, the result is in ascending or descending order of <column name 2>.
Sort in descending order (Ascending by default)






Oracle default instance Library (installed by default)


We can use the following tables:
EMP, DEPT, SALGRADE table


Select * from emp;
Note: We are not recommended to use it during development and usage *
* Lists all columns.
Queries data.
Instead, you can directly list the columns that need to be queried during time consumption.


Select deptno, dname, loc from dept;
<3> note:
During development, we recommend that you only list the columns you need so that I/O can be reduced.
Improve Efficiency

<4> remove duplicate columns (distinct)
Obtain the Department ID from the employee
Select deptno from emp; (many repeated deptno)
Select distinct deptno from emp;

<5> application of expressions
[1] view the names and salaries of all employees
Select ename, sal * 12 from emp;

[2] query the employee name and monthly income (assuming that the monthly bonus is 300)
Select ename, sal + 300 from emp;


<6> alias Application
Select ename, sal * 12 as nianshouru from emp;
As can be omitted
We recommend that you use aliases to improve search efficiency;






4. query Restricted Conditions

Select * | distinct column name, expression alias
From table name
Where clause
Order by clause;


[1] If an order by clause exists, the result is in ascending or descending order of <column name 2>.
Sort in descending order (Ascending by default)

[1] query the 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 salary from emp order by sal; (Ascending by default)
Select ename, sal salary from emp order by 2; (based on the second column)
[2] query the name and salary of an employee in ascending order of the employee name and sort the salary in descending order.

Select ename, sal salary from emp order by ename asc, sal desc;


[2] where
[1] query the salary of KING
Select ename, sal from emp where
Ename = 'King'; use single quotes

[2] query the names and salaries of employees whose salaries are not equal to 3000

Select ename, sal from emp where sal! = 3000;
Select ename, sal from emp where sal <> 3000;
Select ename, sal from emp where sal not in (3000 );


[3] query the name salary of employees with salaries greater than or equal to 1000 and 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 1000 and 2000;
(Not)
[4] query the names and salaries of employees with salaries greater than or less than 3000
Select ename, sal from emp where sal <= 1000 or sal> = 3000;


[5] query the names and salaries of employees with salaries equal to 1000 or 3000
Select ename, sal from emp where sal = 1000 or sal = 3000;
Note: here is "=" rather than "= ".
Or
Select ename, sal from emp where sal in (1000,3000 );

[6] query the names and salaries of employees who have been hired in
Fuzzy search
Select enamel, sal, hiredate from emp where hiredate
Like '% 100 ';
Where: % Represents 0 or multiple characters;

[7] query information about the person whose name is K
Select ename, sal from emp where ename like 'K % ';

[8] querying information about employees whose names contain letters
Select ename, sal from emp where ename like '% A % ';

[9] query employee information with the second letter of the name Z
Select ename, sal from emp where enem like '_ Z % ';
_ Represents any character.
[10] query the name and salary of an employee whose employment date is-
Default Date Format: '23-5-87 'indicates January 1, May 23, 1987.
Date formats can be modified in the future
Select ename, sal from emp where hiredate = '23-January 1, May-87 ';


[11] query the salaries of employees who joined the company from January 1, to January 1 ,.
(You can also query data in a certain interval)
Select ename, sal from emp
Where hiredate> = '23-August-87 'and hiredate <= '23-August 9 ';


[12] query the names and salaries of employees with null bonuses
Is null/is not null;
Select ename, sal salary from emp
Where comm is null;
[13] query information about employees numbered 20 by department in ascending order of salary
Select ename, sal deptno from emp
Where deptno = 20 order by sal;


[14] Application of escape characters

Select ename, sal from emp where ename


Like '\ _ S % ';


[15] format control output:
Output in the specified format on the right (SMITH is a CLERK)
| Stitch
Select ename | 'is a' | job
From emp
Where job = 'cler' and ename = 'Smith ';