Oracle Note 1:-Installation-architecture-Basic Query

I. Installation of the Oracle database (installed in the XP system of the virtual machine)
1. Install the virtual machine
2. Install the XP system inside the virtual machine, the following steps are made within the virtual machine
3. Double-click Setup.exe, set the password in the interface that appears, and confirm the password (this is the password for the system account), click Next
4. Pre-check the "user has verified the previous check box tick, next
5. Overview interface, click Install
6. After the installation is complete, run the Configuration Wizard automatically
7. Password management interface, click "Password Management" to unlock Scott and HR two accounts
8. Go back to the Password Management screen, click OK, install complete, click Exit.

9. Set up virtual machine: Edit--Virtual network editor--Add a network and automatically assign IP when added (e.g., 192.168.9.0)
10. At this time, there will be a virtual network card (Network and Sharing Center), set the IP of the network card with the virtual machine IP in the same segment (such as 192.168.9.1)
11. Set the IP in the XP system to remain on the same network segment (e.g. 192.168.9.2)
12. Set the network adapter for the virtual machine, customize it to the NIC you just added; Virtual machine-Settings-Network adapter-Custom

13. Modify the Oracal two core profiles: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
Under this file, the IP in the Listener.ora file and the Tnsnames.ora file is changed to match the XP system IP, port=1521

14. Test Sqlplus Client Connection server, sqlplus the entire folder (instanclient_12_1), copy to a no Chinese, no spaces in the path
15. Open the DOS window, enter the folder, enter the command: sqlplus system/password @ remote IP:1521/ORCL; If PowerShell is used, enter. \sqlplus system/password @ remote IP:1521/ORCL
The IP of the remote IP:XP system; Password is the password entered during installation
16.quit, exit

17. Test and connect PL/SQL developer. Install PL/SQL, note that the installation path can not have Chinese, no spaces, the next until the installation is complete;
18. Click on the shortcut, the login screen appears, click "Cancel" to enter the interface
19.tools--preferences, modify Oracle home as: D:\instantclient_12_1;
Modify ocilibrary for, D:\instantclient_12_1/oci.dll;
20.copy Oracle's core file TNSNames to a path without Chinese and white space, then added to the system environment variable, variable name: tns_admin, variable value: D:\instantclient_12_1
21. Enter the system using the systems account;

22. After entering the database, reset and unlock the Scott account with the following command: Alter user Scott identified by Tiger account unlock; for HR accounts;

Note:
--Solving Chinese garbled problem
--1. Viewing server-side encoding
Select Userenv (' language ') from dual;
--2. See if the first parameter after execution of the following statement is consistent with the server-side encoding
SELECT * from V$nls_parameters;
--3. If not, add the environment variable: variable name: Nls_lang, Variable value: The 1th step to find the value
--4. Restarting PL/SQL Devloper

Two. Architecture of Oracle Database
1. Database: Oracle database is the physical storage of data, including data files Ora or DBF, control files, online files, log files, parameter files. Oracle database has only one library in an operating system, which distinguishes it from other databases;
2. Example: an instance consists of a series of background processes and memory structure, a database can have n instances, generally only set one;
3. Data file (DBF): The physical storage unit of the database;
4. Tablespace: Logical mapping of data files. A database is logically partitioned into one or more tablespaces, and a tablespace consists of one or more data files (datafile) on disk, and a data file can belong to only one tablespace.
5. User: The user is established under the instance, in different instances can be built with the same name of the user.

Three. Basic Query Statement (Scott account)
SELECT * from EMP;

1.--to heavy distinct
SELECT distinct job from EMP;
Select Job from EMP;

2.--String Connection | | The information of each employee is displayed in a sentence: "Employee number is XXXX, name is xxx, position is xxx"
Select ' Employee number is ' | | Empno | | ', name is ' | | ename | | ', position is ' | | Job from EMP;

3.--arithmetic
Select sal*12 from EMP;

4.--condition Query
4.1--query employees with a salary greater than or equal to 1500 and less than or equal to 3000
SELECT * from emp where Sal >= and Sal <= 3000;

4.2--between and with head with tail
SELECT * from EMP where Sal between and 3000;

4.3--query wages are not more than
SELECT * from emp where not sal > 1500;

4.4--Query Employee number is 7566,7788,7369 in
SELECT * from EMP where empno in (7566,7788,7369);

4.6--Query the employee information for which the bonus is empty
SELECT * FROM EMP where comm is null;

5.--Fuzzy query like
--Wildcard%, placeholder _
--Query the second character in the employee's name with M, at which point the Oracle database is case sensitive
SELECT * from emp where ename like ' _m% ';

6.--Query Employee name with _, need to escape escape Note:%/&/Chinese characters cannot be used as escape characters
SELECT * from emp where ename like '%a_% ' escape ' a ';

7.--sort ORDER BY ASC DESC
--Sort in descending order of bonuses
SELECT * FROM emp ORDER BY comm Desc nulls last;
Select from dual;

8.--single-line function
-character function:
        lower () to lowercase;
        Upper () turn capital;
        Initcap () capitalize the first letter;
        replace ();
        substr () interception;
        length ();
        concat () connection;
Select lower (' ABC ') from dual;
Select UPPER (' abc ') from dual;
Select Initcap (' ABCD ') from dual;
Select replace (' abcdef ', ' ab ', ' MM ') from dual;
Select substr (' abcdef ', 0,3) from dual;--starting from the 1th Fetch (inclusive), take 3;
Select substr (' abcdef ', 1,3) from dual;--starting from the 1th Fetch (inclusive), take 3;
Select substr (' abcdef ', 2,3) from dual;--starting from the 2nd fetch (inclusive), take 3;
Select Length (' ABCDEFG ') from dual;
Select Concat (' abc ', ' Def ') from dual;
Select ' abc ' | | ' def ' from dual;

9.--Numeric functions
Round () rounding
Trunc () go tail/truncate
MoD () modulo
Select Round (12.56,1) from dual;--12.6
Select Round (12,-1) from dual;--10
Select Round (16,-1) from dual;--20

Select Trunc (12.56,-1) from dual;--10
Select Trunc (16.5,-1) from dual;--10

Select mod (10,3) from dual;--1

10.--Date function
10.1--Current system Time Sysdate
Select Sysdate from dual;

10.2--month difference of two dates Months_between
Select Ename, Round (Months_between (sysdate,hiredate)) from EMP;

10.3--add_months N months in time
Select Add_months (sysdate,4) from dual;

11--conversion function
--Character to number
Select ' + ' + ' from dual;
Select To_number (' n ') +to_number (' a ') from dual;

12--search for ' 1980-12-17 ' entry-level employees
SELECT * from emp where TO_CHAR (hiredate, ' yyyy-mm-dd ') = ' 1980-12-17 ';
SELECT * from emp where hiredate = to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

13--General function Nvl
--null If you participate in the operation, the result is constant null and you need to use the NVL function to convert it to 0
Select EMPNO,ENAME,COMM,SAL*12+NVL (comm,0) from EMP;

14--translation function decode to display the positions in the employee table as Chinese

Select Ename,job,decode (Job,
' Clerk ', ' clerk ',
' Salesman ', ' The salesman ',
' Other ') from EMP;

Select Ename, Job,
When the case job is ' clerk ' then ' clerk '
When ' salesman ' and ' Then ' salesman '
Else ' other '
End
from EMP;

15.--Group functions
-Calculate the average salary for each department
Select Deptno, trunc (avg. sal) from the EMP GROUP by DEPTNO have avg (SAL) > 2000;

16.--counts the number of people in each department, and the unmanned sector shows 0

SELECT D.deptno, COUNT (E.deptno)
From EMP E
Right JOIN DEPT D
On e.deptno = D.deptno
GROUP by D.deptno;

Oracle Note 1:-Installation-architecture-Basic Query