Oracle (Getting started) ____oracle

i) Oracle Overview

(1) Understand some concepts about the database
Data: In the database field, data is the basic unit of storage, including text, pictures, video, audio
Database: The Data Warehouse, the place where the data is stored, especially the hard disk in the computer equipment, stored in the form of binary compressed text
This file cannot be directly manipulated and must be operated by the tools provided by each database company in a format that is within each database company
Defined, not a uniform rule
Database objects: In Oracle, for example: tables, views, indexes, functions, procedures, triggers ...
Relational database: Simply put, in the form of row and column structure, the object that the information in the database is expressed, that is, the two-dimensional table
Common popular relational database:oracle&mysql/oracle–>db2/ibm->sqlserver/microsoft–> ...

(2) Understand oracle11g background

(3) Oracle database server consists of two parts
(A) Examples: understood as objects, unseen
(B) Database: Understood as class, visible, e:\app\administrator\oradata\orcl*. Dbf

(4) The relationship between Oracle server and ORCL database
An Oracle database server includes multiple databases, such as: ORCL,ORM,OA,BBS,TAX,ERP, etc.
In the E:\oracleDB\oradata\ directory, how many folders are there, how many databases, for example: ORCL folder =ORCL database
All of the databases that we store in the database will eventually be stored in the corresponding library's *. DBF file, in binary compression form storage
To create a database on an Oracle server: see Creating a database on an Oracle server. JPG "
Note: When we install Oracle, we have created a database with the default name ORCL unless you changed the database name

(5) Sqlplus and SQLDEVELOPER,ORCL instances, ORCL the relationship between databases
Sqlplus is a oracle11g with a client-side black screen interface tool that can connect to an instance of a database to manipulate the database
Sqldeveloper is a oracle11g client-side Color interface tool that can be connected to an instance of a database to manipulate the database
If you feel that these two client tools do not like, you can download the Third-party client tools online

(6) Understanding the concept of failover and load balancing "look at the end Today"
Failover: An Oracle server in a cluster is broken and should have the user on that Oracle server be transferred to several other Oracle servers
This process does not need to be known to the user
Load balancing: When multiple users come to concurrent access, the Oracle servers in the cluster share the pressure of concurrent user access, but not necessarily the average distribution
These two concepts, not only appear in the database field, but also in the Web server domain

(7) with the SYS super username, dba role, Super admin identity unlock Scott scheme/user, and set a password for Scott for Tiger
Unlock User: Alter user scott/hr account unlock;
Set Password: Alter user scott/hr identified by tiger/lion;
Regular user Scott
Password Tiger

(8) Using the Client Sqlplus tool to enter and exit the ORCL database
--access to the Super Manager role
C:/>sqlplus/as SYSDBA
Sql>exit
--access to ordinary users
C:/>sqlplus Scott/tiger
Sql>exit
Statement: We enter as an ordinary user

II Java EE Engineers, oraclesql and Oracle Relationships

(1) Fourth generation language: SQL "Structured Query language, relationship-oriented"
First generation: Machine language
Second generation: Compilation
Third generation: c/c++/c#/java/vb/...
Fourth generation: SQL
Future...

(2) Four categories of sql92/"99" standard
(A) DML (Data Manipulation language): Select,insert,update,delete
(B) DDL (data definition Language): Create Table,alter table,drop table,truncate table ...
(C) DCL (Data Control Language): Grant permissions to Scott,revoke permissions from Scott ...
(D) TCL (Transaction Control Language): Commit,rollback,rollback to SavePoint ...

(3) The relationship between Oraclesql and sql92/99
SQL92/99 standard, standard for accessing any relational database
Oraclesql language, only access to the dedicated language of the Oracle database server

(4) The relationship between Java technology and Oraclesql
jdbc–> use oraclesql syntax –>oracle server->ORCL database –> table –> Records
hibernate–> use oraclesql Syntax –>oracle server
mybatis–> use oraclesql Syntax –>oracle server

(5) Statement: Our four-day time, mainly in the work of "Java EE Engineer" angle, the operation of the database "common" function

-The following code is to display the EMP table to do the setting

col empno for 9999;
Col ename for A10;
Col job for A10;
Col Mgr for 9999;   
Col hiredate for A12;
Col Sal for 9999;
Col Comm for 9999;
Col Deptno for;
Set pagesize;
Col tname for A20;
Set pagesize;

--Create a new table new_emp, copy the structure and data from the EMP table to the New_emp table

    copy_emp as 
select * from EMP;

CREATE TABLE Copy_copy_emp 
as 
select * from EMP;

The following two services are started: "ORACLESERVICEORCL (set to Manual startup) and Oracleoradb11g_home1tnslistener (set to autostart port 1521)"
Similar to MySQL: Use database name, that is, use ORCL;
Start the current database name: ORCL

Use the password command to change the new password for the Scott Username, beginning with a letter, but advocating tiger
Password
Old Password: Tiger
New Password: abc123
Enter new password again: abc123

Exit Sqlplus Tool
Exit

Query who the current user is
Show user;

Query all objects under Scott's user, using the tab table, each user has
SELECT * from tab;

Sets the column widths displayed (character varchar2, date-type dates), 10-width bits, a for character type, and case
column ename format A12;
Column HireDate format A10;

Sets the width of the display (numeric number), 9 for the numeric type, a 9 for a digit, four 9 for four digits, and only 9
column empno format 9999;
Column MGR format 9999;
Column SAL format 9999;
Column Comm format 9999;
Column DEPTNO format 9999;

Set a page to display the height of 80 records
Set pagesize 80;

Use/staves to execute the most recent SQL statement
/

Clear screen, belongs to the command in the Sql*plus tool
Host CLS;

Query the structure of the EMP table
DESC EMP;

Query all of the contents of the EMP table, the number represents a wildcard character, represents all the fields in the table, but the number cannot be used with the specific fields
SELECT * from EMP;
Or
Select Empno,ename,sal,deptno from EMP;

Query the EMP table employee number, name, payroll, Department number, column name, case insensitive, but advocate capitalization
Select Empno "Number", ename "name", Sal "salary", DeptNO "department number" from EMP;

Query the EMP table for work that does not duplicate
SELECT distinct job from EMP;

Check employee's number, name, monthly salary, yearly salary (salary *12)
Select empno,ename,sal,sal*12 "Yearly Salary" from EMP;

Check the employee's number, name, entry time, monthly salary, annual salary, yearly income (annual salary + bonus)
Select Empno "Number", ename "name", HireDate "entry Time", Sal "Monthly Salary", sal*12 "annual salary", Sal*12+comm "yearly Income" from EMP;
If the result is null, the value of NULL is not displayed in the Sqlplus client tool.

To solve the problem of NULL, use the NVL () function, NVL (A,B): If A is null, replace with B, if A is non-null, do not use B substitution, directly return a value \
Select NVL (null,10) from EMP; result has 14 lines of records
Select NVL (null,10) from dual; result has 1 lines of records
Select Empno "Number", ename "name", HireDate "entry Time", Sal "Monthly Salary", sal*12 "yearly Salary", SAL*12+NVL (comm,0) "Yearly Income"
from EMP;
Note: null is the result of NULL in the case of a specific numeric operation

Use column alias, query employee's number, name, monthly salary, yearly salary, yearly income (annual + bonus), as case can and can omit as, alias with double quotation mark
Select Empno as "number", ename as "name", Sal "Monthly Salary"
from EMP;
Or
Select Empno as number, ename as name, Sal monthly Salary
from EMP;
Difference
Select Empno as "number", ename as Name, sal "Monthly Salary"
from EMP;
Aliases without double quotes cannot have spaces; aliases with double quotes can have spaces
To add only double quotes, you cannot add single quotes, because single quotes in Oracle represent string types or date types
Column names cannot use single quotes because Oracle considers single quotes to be string or date-type

Use a dual or pseudo table, use string concatenation symbol | |, output "Hello World", in Oracle from must write
Select ' Hello ' | | ' World ' "results" from dual;

Use Sysdate to display the system's current time, by default, Oracle displays only dates, not time, format: 2 June-April-15
Select Sysdate from dual;

Use string concatenation symbol | |, display the following format information:* The salary is * dollars
Select Ename | | ' The salary is ' | | Sal | | ' Dollar '
from EMP;

Using the spool command, save the SQL statement to the hard disk file E:/oracle-day01.sql and create the SQL file
Spool E:/oracle-day01.sql;

Using the spool off command, save the SQL statement to the hard disk file E:/oracle-day01.sql, and create the SQL file, concluding sentence
Spool off;

Use the @ command to e:/crm.sql the hard disk file, read to the ORCL instance, and execute the SQL statement in the file
@ e:/crm.sql;

Use – symbol, set Single-line comment
–select * from EMP;

Use/*//sign to set multiline comment
/*
Select
*
From
Emp
*/

Features of SQL statements
1) is the ANSI official standard of SQL92/99, as long as according to this standard to write, in any relational database can directly execute
2 The key word of SQL statement cannot be abbreviated, for example: Select,where,from
3 case insensitive, advocate capitalization
4) to be able to the table data to be added to check the operation
5) must end with a semicolon
6) usually called the statement

Features of the Sqlplus command
1 is Oracle's own tool, the command executed in the tool called Sqlplus command
2 Sqlplus tools in the command of the key words can be abbreviated, or can not be abbreviated, for example: Col ename for A10;
3 case insensitive, advocate capitalization
4 The table data can not be added to the search operation, can only complete the display format control, such as: Set the Display column width, clear screen, record the execution results
5 You can end with a semicolon, or end with a semicolon, the individual advocates regardless of SQL or sqlplus, end with a semicolon
6) usually called the command, is the command in the Sqlplus tool
Note: The Sqlplus command is a specific statement in the Sqlplus tool

Single quotes appear in the following places:
1 string type, for example: ' Hello ' | | ' World '
2 date type, e.g. ' 2 May-April-15 '

Double quotes appear in the following places:
1 column alias, for example: sal*12 "annual salary", or sal*12 annual salary, the individual advocates to use "" double quotation mark as column alias

————————————————————————————-where

Query the employee information in unit 20th of the EMP table
SELECT * from emp where deptno = 20;

Query name is Smith's employee, string usage ", content case sensitive
SELECT * from emp where ename = ' SMITH ';
Summary: Which of the techniques you have studied, which are case sensitive and which are case insensitive

For employees who are on the December 17, 1980, note the Oracle default date format (DD-MON-RR represents 2-bit years)
SELECT * from emp where hiredate = ' 1 July-December-80 ';

Query employees with a salary greater than 1500
SELECT * from emp where sal > 1500;

Check employee "!= or <>" with wages not equal to 1500
SELECT * from emp where Sal <> 1500;

Check employees with salaries between 1300 and 1600, including 1300 and 1600
SELECT * from emp where (sal>=1300) and (sal<=1600);
Or
SELECT * from emp where Sal between 1300 and 1600;

Query employees who are not paid between 1300 and 1600, excluding 1300 and 1600
SELECT * from emp where Sal not between 1300 and 1600;

Enquiries from employees in the "198 January-February-20" to "198 February-January-23" Entry time
SELECT * from emp where hiredate between ' 20月-February -81 ' and ' 2 March-January-82 ';
Attention:
1 for the numerical type, the decimal value in the front, the large value in the back
2 for the date type, the older value in the previous, the year small value after

Query 20th or 30th employees, such as: According to the ID number, selected employees, bulk delete
SELECT * from emp where (deptno=20) or (deptno=30);
Or
SELECT * from EMP where deptno in (30,20);

Inquiries are not employees in department 20th or 30th
SELECT * from EMP where deptno not in (30,20);

Employee whose name begins with the capital letter S, with% representing 0, 1 or more characters
SELECT * from emp where ename like ' S ';
Equivalent
SELECT * from emp where ename = ' S ';
SELECT * from emp where ename like ' s% ';

Attention:
The exact query used = symbol
All imprecise queries use like symbols, which we usually call fuzzy queries

Employee whose name ends with capital Letter N
SELECT * from emp where ename like '%N ';

Query name first letter is T, the last letter is R employees
SELECT * from emp where ename like ' t%r ';

The query name is 4-character employees, and the second character is I, use _ can only represent 1 characters, can not represent 0 or more characters
SELECT * from emp where ename like ' i_ ';

Insert an employee whose name is ' T_im ', salary 1200
INSERT into EMP (empno,ename) VALUES (1111, ' t_im ');

Query Employee name contains ' _ ' employees, use the \ escape character, so that the characters back to the original meaning of "like '%_% ' escape '"
SELECT * from emp where ename like '%_% ' escape ' \ ';

Insert an employee whose name is called '
INSERT into EMP (empno,ename) VALUES (2222, "");

Insert an employee whose name is "
INSERT into EMP (empno,ename) VALUES (2222, "" ");

Query all employee information, using% or% percent
SELECT * from EMP;
SELECT * from emp where ename like '% ';
SELECT * from emp where ename like '%_% ';

Query for employees with a commission of NULL
SELECT * FROM EMP where comm is null;
Note: null cannot be an argument = operation
Null energy parameter NUMBER/DATE/VARCHAR2 type operation

Query for employees with a non-null commission
SELECT * FROM EMP where Comm was not null;

Query employees who have no commission and pay more than 1500
SELECT *
From EMP
WHERE (comm is null) and (SAL>1500);

Enquiries about employees with a salary of 1500 or 3000 or 5000
SELECT *
From EMP
where Sal in (4000,10000,1500,3,300,3000,5000);

Query position is "MANAGER" or employee Not "ANALYST" (mode one, use!= or <>)
SELECT *
From EMP
Where (job= ' MANAGER ') or (job<> ' ANALYST ');

The query position is "MANAGER" or an employee with a position other than "ANALYST" (mode two, using not)
SELECT *
From EMP
Where (job= ' MANAGER ') or (not (job= ' ANALYST '));

————————————————————————————-ORDER BY

Query Employee information (number, name, monthly salary, yearly salary), sorted in ascending monthly salary, default ascending order, if monthly salary is the same, sorted by Oracle built-in check rule
Select Empno,ename,sal,sal*12
From EMP
ORDER BY Sal ASC;

Query Employee information (number, name, monthly salary, yearly salary), sorted by descending monthly salary
Select Empno,ename,sal,sal*12
From EMP
ORDER BY Sal Desc;

Query employee information, sorted by entry date in descending order, using column name
Select empno,ename,sal,hiredate,sal*12 "Annual Salary"
From EMP
ORDER BY hiredate Desc;

Can be followed by the column name, alias, expression, column number (starting from 1, the column number in the SELECT clause)
Column Name:
Select empno,ename,sal,hiredate,sal*12 "Annual Salary"
From EMP
ORDER BY hiredate Desc;

Alias:
Select empno,ename,sal,hiredate,sal*12 "Annual Salary"
From EMP
ORDER BY "annual salary" DESC;

An expression:
Select empno,ename,sal,hiredate,sal*12 "Annual Salary"
From EMP
ORDER BY sal*12 Desc;

Column number, starting from 1:
Select empno,ename,sal,hiredate,sal*12 "Annual Salary"
From EMP
ORDER BY 5 Desc;

Query employee information, sorted by commission ascending or descending order, null value as maximum
SELECT * FROM EMP comm DESC;

Query employee information, for employees with commissions, in descending order of commission, and when the ordering by and where appear simultaneously
SELECT *
From EMP
Where Comm is not NULL
ORDER BY comm Desc;

Query employee information, in descending order of wages, and employees of the same salary in descending order of entry time
SELECT *
From EMP
ORDER by Sal Desc,hiredate Desc;

SELECT *
From EMP
ORDER by Sal Desc,hiredate ASC;
Note: HireDate sort only works when Sal is the same

Check number 20th and pay more than 1500, in descending order of entry time
SELECT *
From EMP
where (deptno=20) and (sal>1500)
ORDER BY hiredate Desc;

SELECT * from EMP where deptno in (10,20,30,50, ' a ');

————————————————————————————-single-line function

Single-line function: Only one parameter input, only one result output
Multiline function or grouping function: can have multiple parameter input, only one result output

Test the LOWER/UPPER/INITCAP function and use the Dual dummy table
Select lower (' Www.BAIdu.COM ') from dual;
Select Upper (' Www.BAIdu.COM ') from dual;
Select Initcap (' Www.BAIdu.COM ') from dual;

Test the CONCAT/SUBSTR function, starting at 1, representing characters, both Chinese and English
Select concat (' Hello ', ' Hi ') from dual;
Select concat (' Hello ', ' Howdy ', ' world ') from dual; error
Select ' Hello ' | | ' Hello ' | | ' World ' from dual; right
Select concat (' Hello ', concat (' Hi ', ' world ') from dual; right
Select substr (' Hello Hi ', 5,3) from dual;
5 from the beginning of a few characters, the first character is 1, Chinese and English unified processing
3 means to take several characters consecutively

Test the LENGTH/LENGTHB function, encoded in UTF8/GBK (Zhaojun), one Chinese for 3/2 byte length, one byte in English
Select Length (' Hello Hi ') from dual;
Select LENGTHB (' Hello Hi ') from dual;

Test the Instr/lpad/rpad function, from left to right to find the first occurrence of the position, starting from 1
Select InStr (' HelloWorld ', ' O ') from dual;
Note: The return 0 is not found
Case sensitive
Select Lpad (' Hello ', ten, ' # ') from dual;
Select Rpad (' Hello ', ten, ' # ') from dual;

Testing the Trim/replace function
Select Trim (' From ' and LL ') from dual;
Select replace (' Hello ', ' l ', ' l ') from dual;

Test round/trunc/mod function on numeric type
Select Round (3.1415,3) from dual;
Select Trunc (3.1415,3) from dual;
Select mod (10,3) from dual;

Current date: Sysdate = 2 June-April-15

Test round action on date type (month)
Select round (sysdate, ' month ') from dual;

Test round effect on date type (year)
Select round (Sysdate, ' year ') from dual;

Test trunc Action on date type (month)
Select Trunc (sysdate, ' month ') from dual;

Test trunc effect on date type (year)
Select Trunc (Sysdate, ' year ') from dual;

Show yesterday, today, tomorrow's date, date type +-value = Date type
Select Sysdate-1 "Yesterday", sysdate "Today", sysdate+1 "tomorrow" from dual;

Show employee approximate seniority in year and month, date-date = value, assuming: 365 days a year, 30 days in January
Select Ename "Name", round (sysdate-hiredate,0)/365 "Days" from EMP;

Use the Months_between function to calculate exactly how many months until the end of the year
Select Months_between (' 3 January-December -15 ', sysdate) from dual;

Use the Months_between function to display employee seniority in exact month form
Select Ename "Name", Months_between (sysdate,hiredate) "Exact monthly seniority" from EMP;

Test the Add_months function, what's the number of the next month today?
Select Add_months (sysdate,1) from dual;

Test the Add_months function, what's the number today?
Select Add_months (sysdate,-1) from dual;

Test the Next_day function, starting from today, the next one weeks three is the number of "Chinese platform"
Select Next_day (sysdate, ' Wednesday ') from dual;

Test Next_day function, starting from today, the next one weeks, the number of "Chinese platform"
Select Next_day (Next_day (sysdate, ' Wednesday '), ' Wednesday ') from dual;

To test the Next_day function, starting today, the next Sunday in Wednesday is the number of "Chinese platform"
Select Next_day (Next_day (sysdate, ' Wednesday '), ' Sunday ') from dual;

Test Last_day function, number of the last day of this month
Select Last_day (sysdate) from dual;

Test the Last_day function, the number of the second day of this month
Select Last_day (sysdate)-1 from dual;

Test Last_day function, number of the last day of the next one months
Select Last_day (add_months (sysdate,1)) from dual;

To test the Last_day function, what is the last day of one months?
Select Last_day (add_months (sysdate,-1)) from dual;

Attention:
1) Date-date = Days
2 date +-days = Date

————————————————————————————-three major types of conversions

Three major types and implicit data type conversions in Oracle
(1) varchar2 variable length/char fixed length –>number, for example: ' 123 '->123
(2) Varchar2/char–>date, for example: ' 2 May-April -15 '-> ' 2 May-April-15 '
(3) Number-->varchar2/char, such as:123-> ' 123 '
(4) Date-->varchar2/char, for example: ' 2 May-April -15 '-> ' 2 May-April-15 '

How Oracle Converts implicitly:
1 = number is the same type of two sides
2 If the type of the two sides of = number is different, try to do the conversion
3 in the conversion, to ensure that the legitimate reasonable, otherwise the conversion will fail, for example: December will not be 32 days, a year will not have 1 March

Query for December 17, 1980 Employees (Mode I: Date implicit conversion)
SELECT * from emp where hiredate = ' 1 July-December-80 ';

Use the To_char (date, ' lattice "constant") function to convert a date to a string that displays the following format: Saturday, April 25, 2015
Select To_char (sysdate, ' yyyy ' year "MM" month "dd" th "Day") from dual;

Use the To_char (date, ' format ') function to convert the date to a string, as shown in the format: 2015-04-25 today is Saturday 15:15:15
Select To_char (sysdate, ' yyyy-mm-dd ' is Today "Day Hh24:mi:ss ') from dual;
Or
Select To_char (sysdate, ' yyyy-mm-dd ' is today "day HH12:MI:SS AM ') from dual;

Use the To_char (value, ' format ') function to convert a value to a string that displays the following format: 1,234selecttochar (1234,′1,234 Select To_char (1234, ' 9,999 ') from dual;

Use the To_char (value, ' format ') function to convert a value to a string, displaying the following format: ￥1,234select to_char (1234, ' $9,999 ') from dual;
Select To_char (1234, ' l9,999 ') from dual;

Use the To_date (' string ', ' format ') function to inquire about the employee (mode two: date explicit conversion) of December 17, 1980
SELECT * from emp where hiredate = To_date (' December 17, 1980 ', ' yyyy ', ' mm ' month ' DD ' Day ');
Or
SELECT * from emp where hiredate = to_date (' 1980#12#17 ', ' yyyy ' # "MM" # "DD ');"
Or
SELECT * from emp where hiredate = to_date (' 1980-12-17 ', ' yyyy-mm-dd ');

Use the To_number (' string ') function to convert the string ' 123 ' to the number 123
Select To_number (' 123 ') from dual;

Attention:
Select ' 123 ' + 123 from dual;246
Select ' 123 ' | | 123 from Dual;123123

Use NVL (A,B) Common functions to count employee annual income, NVL () for any type, i.e. (number/varchar2/date)
Common functions: Parameter types can be number or varchar2 or date type

Use NVL2 (a,b,c) Common function, if a is not NULL, take B value, otherwise take C value, statistic employee yearly Income

Use the Nullif (a,b) Common function, in the case of a type, if a is the same as B, return null, otherwise return a, compare 10 and 10.0 are the same

Using the case expression in the SQL99 Standard General syntax, the position is the analyst, the salary is +1000, the position is the manager, the salary is +800, the position is other, the salary is +400.
Case Field
When condition then expression 1
When condition then expression 2
else Expression n
End

Using the Decode () function in the Oracle private syntax, the position is an analyst, the salary is +1000, the position is the manager, the salary is +800, the position is other, the salary is +400.
Decode (field, condition 1, expression 1, condition 2, expression 2,... Expression N)

Single quotes appear in the following places:
1) string, for example: ' Hello '
2 date type, for example: ' 1 July-December-80 '
3) to_char/to_date (date, ' Yyyy-mm-dd HH24:MI:SS ')

Double quotes appear in the following places:
1 column alias, for example: select Ename "Name" from EMP
2) to_char/to_date (date, ' YYYY ' year "MM" month "DD" Day "HH24:MI:SS")