Oracle's five architecture concepts, oracle Architecture

Oracle's five architecture concepts, oracle Architecture
Oracle's five architecture concepts

1. oracle architecture = [5 concepts] -- 1. database Oracle database only has one database name orcl -- 2. an instance consists of a series of processes and memory structures. The instance can have multiple, but 99.9% uses one instance for use -- 3. data file. dbf data storage file -- 4. tablespace ### is the logical ing of data files -- 5. user ### the first user to operate the tablespace is the system user. Run the following code to [Switch users ]. --------- Scott user's default password is tiger [Reset Password] use SQL to reset Scott's password alter user scott identified by tiger --- ###--- select * from scott. emp; -- access scott user's emp table ----------------------- use scott user --------------------------- select * from dept; -- department select * from emp; -- employee select * from salgrade; -- salary level 3. query -- 1. simple query 1. Alias usage --- garbled select empno as "employee ID", ename as employee name, job "position", mgr lead from emp select empno as "employee ID" from e Mp solves Chinese garbled characters/* 1. check the server-side code select userenv ('language ') from dual; the actual result I found is: AMERICAN_AMERICA.ZHS16GBK 2. run the select * from V $ NLS_PARAMETERS statement to check whether the VALUE corresponding to NLS_LANGUAGE in PARAMETER in the first line is the same as the VALUE obtained in the first step. If not, you need to set the environment variable. otherwise, the PLSQL client uses inconsistent encoding with the server encoding, and garbled characters will appear when Chinese characters are inserted. 3. set environment variables computer-> properties-> advanced system settings-> environment variables-> new settings variable name: NLS_LANG, variable value: the value found in step 1st, my name is AMERICAN_AMERICA.ZHS16GBK 4. restart PLSQL, insert data normally */2, four arithmetic operations select sal, sal * 12 from emp; 3. connector | -- dual pseudo table objective: to configure query statements, see "employee ID is XXX and name is XXX, position: XXX "select 'employee ID: XXX' | 'name: XXX' | 'position: XXX' info from dual select' employee ID: '| empno |' the name is '| ename | 'position is' | job from emp; 4. deduplicate distinct -- 2. entries Item query where 1. query employees with salaries greater than 1500 and less than 3000 select * from emp where sal> 1500 and sal <3000 2, .. and -- contains the critical value select * from emp where sal> = 1500 and sal <= 3000; select * from emp where sal between 1500 and 3000; 3. null is neither a null String nor 0, is an unknown data type and null are not equal, select * from emp where comm is null query: select * from emp where comm is not null 4. not query: the salary is not greater than 1500 select * from emp where not (sal> 1500)) 5. The in query employee number is 7788,7369, 7566 select * from emp where empno in (7788,7369, 7566) the employee name is smith jones scott select * from emp where ename in ('Smith ', 'Jones', 'Scott ') -- 3. note: in Oracle, the second letter in the case-sensitive query employee name is select * from emp where ename like '_ M %' select * from emp where ename like '% M % '. select * from emp where ename like '% 1 _ %' escape '1' select * from emp where ename like '% d in employee name _ % 'Escape d' select * from emp where ename like '% @ _ % 'escape' @ 'select * from emp where ename like '% # _ % 'escape '# 'select * from emp where ename like '% __% 'escape' _ 'incorrect select * from emp where ename like' % _ % 'escape '% 'select * from emp where ename like '% & _ %' escape '&' --- 4. sort order by desc asc select * from emp order by comm desc nulls last sort by bonus select * from emp or Der by comm nulls first 4. Functions -- 1. single-row function 1.1 string type lower -- convert lowercase select lower ('oracle ') from dual select ename, lower (ename) from emp upper -- turn size initcap -- uppercase substr -- When intercepting oracle, write 0 at the starting position and 1 at the same select substr ('qwertyui ',) from dual; -- qwer select substr ('qwertyui',) from dual; -- qwer select substr ('qwertyui',) from dual -- wert length -- length select length ('qwertyui ') from dual -- 8 replace -- replace select replac E ('qwertyui', 'qw', 'aaa') from dual -- aaertyui concat -- connect two string functions ==== recommended | select concat ('aaa ', 'bbb ') from dual select concat ('aaa', 'bbb'), 'ccc '), 'ddd ') from dual 1.2 numeric function round -- rounding into select round (12.456) from dual; -- 12 select round (12.656) from dual; -- 13 select round (12.456, 2) from dual; -- 12.46 select round (12.456,-1) from dual; -- 10 select round (16.456,-1) from dual; -- 20 trunc -- Truncation Select trunc (12.456) from dual; -- 12 select trunc (12.956) from dual; -- 12 select trunc (12.456, 2) from dual; -- 12.45 select trunc (12.456,-1) from dual; -- 10 select trunc (16.456,-1) from dual; -- 10 mod -- Obtain the remainder select mod () from dual; -- 1 select mod () from dual; -- 0 1.3 date type date 1-date 2 = value (unit: Day) Current Time-employment time = length of service select sysdate from dual get employee employment days select ename, hiredate, sysdate-hiredate from emp get employee number of weeks selec T ename, hiredate, round (sysdate-hiredate)/7) from emp get the number of months in which an employee is hired months_between -- select ename, hiredate, months_between (sysdate, hiredate), (sysdate-hiredate)/30 from emp add_months -- add month select sysdate from dual select add_months (sysdate, 1) from dual 1.4 Conversion Function -- ### Conversion Function (emphasis) -- convert to_char value to string select 12, to_char (12) from dual -- to_char date to string select * from emp where to_char (hiredate, 'yyyy/MM/dd ') = '1970/20' select to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual select to_char (sysdate, 'yyyy'), to_char (sysdate, 'mm'), to_char (sysdate, 'dd'), to_char (sysdate, 'day') from dual -- to_number string to select to_number ('12 ') + to_number ('34') from dual select '12' + '34' from dual -- to_date string to date select add_months (to_date ('2017-01-31 ', 'yyyy-MM-dd'), 1) from dual select * from emp where hiredate = _ Date ('1970-02-20 ', 'yyyy-MM-dd') 1981 general functions -- learn about -- nvl --- ##### Syntax: nvl (digit field name, 0) -- When the field is null, it is treated as 0. Calculation of employee annual salary -- null participates in the calculation result. The constant value is null select ename, sal, comm, sal * 12 + comm from emp -- nvl. It indicates that there is a problem with the running result as shown in the preceding figure: if comm does not exist, the annual salary is null. -- ### Null is involved in the computation. The result is always null select ename, sal, comm, sal * 12 + nvl (comm, 0) from emp -- nvl (comm, 0) meaning: when comm is null, it is treated as 0. ==== Emp table field values: CLERK -- employee SALESMAN -- salesman manager -- manager analyst -- analyst president -- decode -- is [unique to Oracle] -- Key decode (column name, 'table Data ', 'display value', 'table data', 'display value', 'table data', 'display value') select ename, job, decode (job, -- similar to Java: switch case -- different values are used to display different content such as 'cler', 'salesman', 'salesman', and 'others ') from emp -- condition expression -- any relational database supports -- end of the when value displayed in the case column -- same as the decode function: for a field, show different content select ename, job, case Job when 'wheel' then' employee 'when' SALESMAN 'then' SALESMAN 'else' others' end from emp -- 2. multiline function: Aggregate Function Group function 2.1 aggregate function sum avg count max min select avg (sal) from emp select max (sal)-min (sal) from emp select count (*) from emp select sum (sal) from emp 2.1 group functions: grouping group by queries the average salary of each department select deptno, max (sal) from emp group by deptno select deptno, avg (sal) from emp group by deptno, Avg (sal) from emp group by deptno having avg (sal)> 2000 -- difference between where and having [Pen question] ### where filters physical columns in the table (columns in the table) where appears before group, having appears after group by ---------------------- summary ------ 5. Summary of day01: oracle installation and configuration 1. loading an xp Virtual Machine 2. Installing an Oracle database on a virtual machine 3. configuring the network with a virtual network card 4. Testing the client tool instantclient_12_1 on the local machine to remotely connect to the virtual machine 5, install plsqlDev, and configure simple query and conditional query-must master-common key functions 1. string: replace length substr 2. number round 3. type Conversion -- ### to_char -- numeric and date conversion To_date -- string to date. 4. general functions (commonly used) nvl solution: -- ### null is involved in the computation. The result is always null select ename, sal, comm, sal * 12 + comm from emp -- nvl. Meaning: there is a problem with the running result in the above writing: comm does not have, resulting in a null annual salary. Select ename, sal, comm, sal * 12 + nvl (comm, 0) from emp Syntax: nvl (digit field name, 0) -- ### -- when the field is null, treat it as 0. 5. five basic Aggregate functions count sum max min avg