Oracle Database: Installation & function query & condition query

Keywords: Database Oracle SQL

1. Course introduction

first day:
1.Oracle concept and installation
2. Basic query
3. Condition query
4. Functions in Oracle
the second day:
1. Multi table query (mysql also has the same concept, different syntax and important aspect)
2. Sub query (it also exists in mysql, with the same concept, different syntax and important aspect)
on the third day:
1. Concept of tablespace
2. Users
3. View (mysql also has the same concept, different syntax and important aspect)
4. Index (it also exists in mysql. The concept is the same, the syntax is different, and the aspect is important)
5. Sequence (it also exists in mysql, with the same concept, different syntax and important aspect)
6. Synonyms
7.PLSQL programming
the forth day:
1.PLSQL programming
2. Cursor
3. Stored procedure
4. Storage function
5. Trigger
Master the level required by the enterprise in the first three days, master all the certificates that can directly obtain Oracle OCA certification or even higher, and be directly competent for Oracle work

Origin of Oracle & Ellison's life experience

Oracle: at first, four programmers wrote data for $2000 and $1200, 60% of the shares
Read data and process large amounts of data
Origin: an IBM company published a paper - R relational database model. Ellison saw and created the first version of Oracle, fooled the CIA and sold it. He reached the peak of his life. His latest wife is a model

Why Oracle

mysql: open source free database, small and medium-sized enterprises, Taobao, mysql Cluster
Oracle: charging database, state-owned enterprises, finance, banking, securities, insurance, JD | charging by CPU core, one core = permanent use fee of 180000

2. Oracle installation and configuration

Oracle virtual machine installation:
1. Install vm wareworkstation first
2. Double click XP in the pure version_ Oracle.vmx file
3. Turn off the firewall

Configure network:
1. Edit - > add virtual network
2. Set the virtual machine network as a fixed IP address
3. Test whether the network is ping ed

Oracle Software Installation:
1. Drag Oracle to the virtual machine
2. Install database software
3. Unlock Scott and HR accounts (after installation, you can log in to the page, which can directly manage and view the database)

4. Configure the address of Oracle as a fixed IP address
Modify the host of listener and tnsnames to the corresponding ip address
5. Restart Listener and ORCL services
6. Client test access ORACLE
Key - Installation of PLSQLDevelop: (Note: it is installed in the path without Chinese and spaces)
1. Install instantclient first
2. Configure tools -- > preferences -- > configure the path of instantclient
3. Copy the tnsnames.ora file in the previous steps to the local computer and configure TNS_ADMIN environment variable

4. Restart PLSQLDevelop
5. Enter the user name and password test (execute the command after creating a new SQL window)

3. Formal learning part

Oracle architecture

Database - > database instance - > tablespace (logical unit) (user) - > data file (physical unit)
Earth - > a country - > provinces (logical units) (citizens) - > mountains and rivers (physical units)

Usually, ORacle database has only one instance ORCL,

Create a new project:
MYSQL: create a database and create corresponding tables
Oracle: create a tablespace, create users, and users create tables

Differences between Oracle and MYSQL

Oracle is multi-user and MYSQL is multi database

  1. Follow SQL standards
  2. Different manufacturers, different database products, but have their own dialects
  3. Using your own dialect can also complete the same function
  4. Oracle security level should be high, and MYSQL is open source and free

A brief review of SQL

Basic query:
SQL: Structured Query Language
Please listen to the question: please talk about the classification of SQL and what are the common operators of each type
Four categories:
DDL: data definition language create alter drop truncate
DML: data manipulation language insert update delete
DCL: data control language security authorization grant revoke
DQL: data query language select from clause where clause
Structure of query statement:
select [column name] [*] from table name [where condition] [group by grouping condition] [having filter] [order by sort]
  select * from emp;
  select 1+1; -- An error is reported in Oracle, and the output result in MYSQL is 2

Concept of virtual table

     dual : oracle The virtual table and pseudo table in are mainly used to supplement the syntax structure
select 1+1 from dual;
select * from dual;
select 1 from emp;
--Writing a constant directly is better than writing * Be efficient
select count(1) from emp;
select count(*) from emp;

Alias query

       Alias query: use the as keyword, which can be omitted
       No special characters or keywords are allowed in the alias. If any, use double quotation marks
select ename full name, sal wages from emp;
select ename "lastname       name", sal wages from emp;

Remove duplicate data

      Remove duplicate data distinct
      Multiple columns remove duplicates: only when each column is the same can it be counted as duplicates
--Single column de duplication
select distinct job from emp;
--Multiple columns remove duplicate
select distinct job,deptno from emp;

Four arithmetic

select 1+1 from dual;
--Query employee annual salary  = a monthly salary* 12
select sal*12 from emp;

--Query employee annual salary+bonus
select sal*12 + comm from emp;

--nvl function : If parameter 1 is null  Parameter 2 is returned
select sal*12 + nvl(comm,0) from emp;
be careful: null value , Represents uncertain and unpredictable content , You can't do four operations

String splicing

String splicing:
    java : + No. splicing
    Oracle Unique connector: | splicing
    In Oracle, double quotation marks are mainly used when aliases are used, and single quotation marks are used values and characters
    concat(str1,str2) Functions are available in both mysql and Oracle
--Query employee name :  full name:SCOTT
select ename from emp;
--Use splices
select 'full name:' || ename from emp;
--Use function splicing
select concat('full name:',ename) from emp;

Condition query

    Conditional query: [write after where]   
        Relational operators: > = = < < =! = < >
        Logical operator: and or not
        Other operators:
               like Fuzzy query
               in(set) Within a collection
               between..and.. Within a certain interval
               is null  Judged to be empty
               is not null Judgment is not empty
--Query the information of employees who can get bonus every month
select * from emp where comm is not null;

--Query salary at 1500--3000 Employee information between
select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <= 3000;

--Query employee information whose name is in a certain range ('JONES','SCOTT','FORD') in
select * from emp where ename in ('JONES','SCOTT','FORD');

        _   Match single character
        If there are special characters, Need to use escape Escape

Fuzzy query

    Fuzzy query: like
        %   Match multiple characters
--The third character of employee name is O Employee information for
select * from emp where ename like '__O%';

--Query employee name,contain%Employee information for
select * from emp where ename like '%\%%' escape '\';
select * from emp where ename like '%#%%' escape '#';

Arrange query

       Sorting: order by 
          Ascending: ASC ascend
          Descending order: desc descend
          Note: null s first | last
          Arrange multiple columns at the same time, separated by commas
--Query employee information,Sort the bonus from high to low
select * from emp order by comm desc nulls last;

--Query department number and sort by salary in ascending order by Department, Salary descending sort
select deptno, sal from emp order by deptno asc, sal desc;

Multiline (aggregate) function

     Function: must have return value
     Single line function: process a value in a line
         Numerical function
         Character function
         Date function
         Conversion function
         General function
     Multiline function: process all rows of a column
           max()  min count sum avg
           //Ignore null values directly 
--Statistics of total wages of employees
select sum(sal) from emp;

--Statistics of total employee bonus two thousand and two hundred
select sum(comm) from emp;

--Count the number of employees 14
select count(1) from emp;

--Error in statistics of average bonus of employees    2200/14 =
select avg(comm) from emp;

--Statistics of average bonus of employees 157.
select sum(comm)/count(1) from emp;
select ceil(sum(comm)/count(1)) from emp;

update emp set ename = 'TUR%NER' where ename = 'TURNER';

select * from emp;

Numerical function

--Numerical function
select ceil(45.926) from dual;  --46
select floor(45.926) from dual; --45
select round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100

select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0

--Seeking remainder
select mod(9,3) from dual; --0
select mod(9,4) from dual; --1

Character function

-- substr(str1,Start index,length) 
--be careful: The starting index is intercepted from the first character no matter whether it is written as 0 or 1
select substr('abcdefg',0,3) from dual; --abc
select substr('abcdefg',1,3) from dual; --abc
select substr('abcdefg',2,3) from dual; --bcd

--Get string length 24 28
select length('abcdefg') from dual;

--Remove the spaces on the left and right sides of the character
select trim('  hello  ') from dual;

--Replace string
Select replace('hello','l','a') from dual;

Date function

--Query today's date
select sysdate from dual;
--Query the date of today after 3 months
select add_months(sysdate,3) from dual;
--Query the date after 3 days
select sysdate + 3 from dual;
--Query employee enrollment days
select sysdate - hiredate from  emp;
select ceil(sysdate - hiredate) from  emp;

--Query the number of weeks of employee enrollment
select (sysdate - hiredate)/7 from emp;

--Query the months of employee employment
select months_between(sysdate,hiredate) from emp;

--Query employee's year of employment
select months_between(sysdate,hiredate)/12 from emp;

Conversion function

 (Numeric to character (character to numeric date)
--Character to value to_number(str) chicken ribs
select 100+'10' from dual;  --110  The default has helped us convert
select 100 + to_number('10') from dual; --110

--Numeric to character
select to_char(sal,'$9,999.99') from emp;
select to_char(sal,'L9,999.99') from emp;
	to_char(1210.73, '9999.9') Return to '1210.7' 
	to_char(1210.73, '9,999.99') Return to '1210.73' 
	to_char(1210.73, '$9,999.00') Return '$1210.73' 
	to_char(21, '000099') Return to '000021' 
	to_char(852,'xxxx') Return to '354'

--Date to character to_char()  
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--Just want years
select to_char(sysdate,'yyyy') from dual;  --2017

--Just want the day
select to_char(sysdate,'d') from dual; --2  Represents the day of the week
select to_char(sysdate,'dd') from dual;  --10  Represents the day of the month
select to_char(sysdate,'ddd') from dual; --100 Represents the day of the year

select to_char(sysdate,'day') from dual;  --monday
select to_char(sysdate,'dy') from dual;   --mon  Abbreviation for week

--Character to date
select to_date('2017-04-10','yyyy-mm-dd') from dual;

--Query 1981 -- 1985 Employee information in
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

Judgment functions: if and case

1, Single IF
	if a=...  then
	end if;

	if a=... then
	end if;

2, Multiple IF
	if a=..  then
	elsif a=..  then
	end if;     
In the middle here is“ ELSIF",instead of ELSE IF . Special attention needs to be paid here
2,decode function
DECODE Syntax:
Indicates if value be equal to if1 When, DECODE The result of the function returns then1,...,If not equal to any one if Value, returns else. 

3,case when
	case when a='1'then 'xxxx'
	     when a='2' then 'ssss'
	end as
1,with CASE Start with END ending 
2,In branch WHEN Heel condition, THEN Show results for 
3,ELSE Otherwise, it is the default, similar to that in high-level language programs switch case of default,Can not add 
4,END Followed by alias  

General function

      General functions:
       nvl(Parameter 1, parameter 2) if parameter 1 = null, parameter 2 is returned
       nvl2(Parameter 1, parameter 2, parameter 3) if parameter 1 = null, parameter 3 is returned; otherwise, parameter 2 is returned
       nullif(Parameter 1, parameter 2) if parameter 1 = parameter 2, null is returned; otherwise, parameter 1 is returned
       coalesce: Returns the first non null value
select nvl2(null,5,6) from dual; --6;
select nvl2(1,5,6) from dual; --5;

select nullif(5,6) from dual; --5
select nullif(6,6) from dual; --null

select coalesce(null,null,3,5,6) from dual;  --3

select ceil(-12.5) from dual; --12
select floor(12.5) from dual; --12

select '  hello  ' from dual;
select * from emp;

Posted by troublemaker on Fri, 29 Oct 2021 17:42:01 -0700