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
C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN
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
- Follow SQL standards
- Different manufacturers, different database products, but have their own dialects
- Using your own dialect can also complete the same function
- 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 --rounding 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 --truncation 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 1, if a=... then ......... end if; 2, if a=... then ...... else .... 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: DECODE(value,if1,then1,if2,then2,if3,then3,...,else) 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' else 'zzzzz' end as Note: 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;