Oracle Note 1-Database concepts

Database:

The basic concept:
Database management System (DB Management System,dbms): A software system for managing data
relational database management System (RDBMS)
Database: A disk that holds data, which is part of an RDBMS

What are the common database management systems on the market?
Oracle,mysql,sql Server, Sqlite (Mobile database), DB2, Access, sybase,h2 ...

SQL (Structure query Language) Structured Query Language,
It is a standard for operational database management systems: primarily CRUD operations on databases,
The operation of the database is done by SQL;
Create: Creating
Delete: Remove
Update: Modify
Retrieve: Retrieving

Mainstream databases on the market adhere to the SQL specification:

NOTE: SQL support for different databases is somewhat different;

SQL is divided into 5 parts depending on the command function:
1.DCL Data Control Language statement: Grant,revoke

The 2.DDL (data definition Language) data definition statement:
Create,alter,drop,truncate,comment On,rename to ...

Data manipulation statement for 3.DML (data manipulating Language): Insert,update,delete

4.DQL (Date query Language) data query statement: SELECT

5.DTL (data Transaction Language) transaction statement: Commit,rollback,savepoint
-----------------------------------------------------------------------------
Oraclewww.oracle.com

... 8i,9i,10g,11g,12c

Installation Note: The directory can not be in Chinese, the proposed firewall and 3,601 of security software to close;

The Oracle database has a server and client component:

Server: Consists of a bunch of database background processes and listeners and databases
The two most important services:
Oracleservicexe,oracle Database Master Service (XE represents the instance name of the current database, and if it is an Enterprise edition,
Instance name is ORCL)
Oraclexetnslistener,oracle database listener to listen to third-party software connections to the database

Client: There are multiple
1.Oracle comes with a client: sqlplus--command-line tools
Start: Run cmd open cmd Command Window input sqlplus
2.Navicat third party, graphical interface
Ctrl+q Open Query
Ctrl+r Run All statements
Ctrl+shift+r Run the selected statement

3.pl/sql developer Third party, graphical interface

4.SQL Developer, with the Java language developed by Oracle itself, requires JDK support

5. In oracle9i~10g, a browser-based access service is provided Isqlplus
In other words, no specialized client software is required. can be accessed through a browser,
has now been abolished.
For example: Open browser, type: Http://127.0.0.1:8080/apex

-----------------------------------------------------------------------------

To create a user 2 steps:
1. Create a (normal) User:
Syntax: Create user username identified by password;

For example:
Create user jsd1510 identified by jsd1510;

2. Authorization to a User:
Role: 3 roles are built into Oracle:
DBA: System administration permissions;
Connect: Create session connection permissions;
Resource: Permission to manipulate basic database objects;

Syntax: Grant permissions, permissions ... to user name;

For example: Give JSD1510 account to connect, basic operation permission
Grant Connect,resource to jsd1510;

Change Password: Alter user username identified by new password;

Delete users: Drop user user name; (attention to permissions issues when deleting)

Account unlock: Alter user username accounts unlock;

-----------------------------------------------------------------------------

Common commands in Sqlplus:

1. Switch Users:
Conn user name/password;

2. Display the currently logged in User name:
Show user;

3. Execute the database script:
Start or @ *.sql (you can drag the script directly into the command window)

For example: Start d:/oralce_cn.sql;

Database script: A collection of statements used to create database objects
Purpose: Create a table that stores data in a database and initialize the data in a table

4. View the structure of the table: This is the Sqlplus command, not the SQL command
The name of the DESC table;

5. Clear the screen:
Clear screen;

6. Recall Record:
Arrow keys up and down

7. Edit multi-line SQL statements:
Edit

-------------------------------------------------------------------------
Basic query:

Table (table): It is a two-dimensional space consisting of rows (rows) and columns (column)
It is the basic unit of database storage data, in the future our data is stored in the table;

The nature of the query:
1. What to look for
2. Where to check

Syntax: Select column name, column name,... from table name;

If you are querying all information, you can use * instead of all columns
SELECT * from table name;

For example:
--Find out all the information about the employee;
SELECT * from S_emp;

--Check the name, salary and position of the employee from the S_emp table;
Select First_name,salary,title from S_emp;

--To find out the employee's surname and name from the S_emp table;
Select First_name,last_name from S_emp;

--Find out all of the company's titles (title);

Row weight: Distinct
Select distinct title from S_emp;

SELECT * from S_title;

--Find out the name, salary, date of entry for all employees
Select First_name,salary,start_date from S_emp;

--Find out all the client's name and his phone number.
Select Name,phone from S_customer;

--Find out the employee's name and his job title
Select First_name,title from S_emp;

--Find out the cost, payment method and shipping date of each order
Select total,payment_type,date_shipped from S_ord;

--------------------------------------------------------------------------

The data type of the column:
1. Numeric type, can do all mathematical operations
Number Number (4) represents the maximum number of integers that can be stored 9999
Number (7,2) represents an integer of type double with a length of 5 decimal digits of 2.

2. The character type, can do the concatenation operation
Note: strings are used in the database ";
VARCHAR2 (20) represents the longest string of 20;
Char (20) represents a fixed length of 20;

3. Date type, can do add and subtract operation
Date Month Day seconds
Time seconds and minutes
Timestamp days and seconds, and decimal digits, such as 3.2 seconds

4. Big Data types
Clob character large Object large character type maximum possible 4G
Blob binary large object big binary objects can be saved up to 4G

Note: Large data types do not support viewing results

Such as:
SQL has the ability to operate
--the employee's name and his annual salary are queried from the employee table.
Select first_name,salary*12 from S_emp;
Select First_Name Name, salary*12 annual salary from S_emp;
Select First_Name "Name", salary*12 "annual salary" from S_emp;

Note: Oracle can alias the columns of the query, the aliases can be written directly, or they can be decorated with ""
"" can also control formatting and case sensitivity

SQL has string stitching capability
--Identifying the employee's name from the S_emp table;
Select First_name| | ' ' | | Last_Name full name from S_emp;

--String Splicing (* * * * * into the job, salary is * * *, title is * * *)
Select First_name| | ' ' | | last_name| | ' Is ' | | start_date| | ' The salary of the entry is ' | |
salary| | ', title is ' | | Title employee overview from S_emp;

Select First_name| | ' ' | | last_name| | ' Is ' | | To_char (start_date, ' yyyy-mm-dd ') | | ' The salary of the entry is ' | |
salary| | ', title is ' | | Title employee overview from S_emp;

-----------------------------------------------------------------------------

Null displacement function

--Query all employees ' names, annual salary (regardless of commission)
Select first_name,salary*12 from S_emp;

--Query all employee's name, annual salary (consider Commission)
--Error
Select First_name,salary *12 * (1 + commission_pct/100) from S_emp;

Reason:
Null and all values are evaluated at the end of NULL;

Null-value substitution function NVL (commission_pct/100,0)
If the value of commission_pct/100 is null, the value of the expression is 0
If the value of commission_pct/100 is not NULL, the value of the expression is commission_pct/100

-Correct
Select First_name,salary * (1 + NVL (commission_pct/100,0)) from S_emp;

Note: The two values in NVL () are the same data type

---------------------------------------------------------------------------

Conditional query:

The WHERE clause is used to filter the data of the query, which is sensitive to the literal size of the large lowercase
The operators that appear behind the Where are:

1. Comparison operators:
>, <, >=, <=, =,! = ^= <>

--Find out the employee's name and salary in the 41 department door;
Select First_name,salary from s_emp where dept_id=41;

--To find out the information of employees with wages above 1500;
SELECT * FROM S_emp where salary>1500;

--Find out the full name, salary and job title of the employee with salary greater than 1200 yuan
Select First_name| | ' ' | | Last_name,salary,title from S_emp
where salary>1200;

2. Logical operators:
And,or,not

--To find out 41 employees whose wages are above 1200;
Select First_name,salary from S_emp where dept_id=41 and salary>1200;

--Find out the employee's name and salary in 41,50,42 department;
Select First_name,salary from S_emp
where dept_id=41 or dept_id=42 or dept_id=50;

3. Other operators:
In (list) is a list with multiple values separated by commas
Not in ()
Between? Within the specified range, it is the fully closed space
Is null
is NOT NULL

Fuzzy matching: Like

Wildcard characters:
_ Pass with any single character
% pass with any number of characters

--Find out the employee's name and salary in 41,42,50 department;
Select First_name,salary from S_emp where dept_id in (41,42,50);

--Find employee names from 1200 to 1500 (both ends, not including 2 answers)
Select First_name,salary from S_emp where salary between and 1500;
Select First_name,salary from S_emp where salary>1200 and salary<1500;

--Find employees with a salary greater than 1500 and no commission;
SELECT * from S_emp where salary>1500 and commission_pct are null;

Note: If the condition is null when querying data, we use the keyword is or is not null and must not use =/!=

--Identify the employee whose name starts with M;
SELECT * from s_emp where first_name like ' M% ';

--Identify the employee whose third letter is E;
SELECT * from s_emp where first_name like ' __e% ';

If we want to match the character in itself there is _ or%, you need to escape through the keyword escaped;
SELECT * from s_emp where first_name like ' \_t% ' escape ' \ ';

--Find employees without a royalty rate
SELECT * from s_emp where commission_pct is null;

--Identify employees with a royalty rate
SELECT * from s_emp where commission_pct are NOT null;

--Find out the order number and payment method that cost more than 10000 yuan
Select Id,payment_type from S_ord where total>10000;

--Find out the employee's name, title and salary at 950 (inclusive) to 1200 (inclusive)
Select First_name,title from S_emp where salary between 950 and 1200;

--Find the employee whose name contains the letter A
SELECT * from s_emp where first_name like '%a% ';

--Find the employee whose second letter is a in the name
SELECT * from s_emp where first_name like ' _a% ';

--Find out the employee's name, entry time and salary in February
Select First_name,start_date,salary from S_emp
Where start_date like '% February ';

Select First_name,start_date,salary from S_emp
where To_char (start_date, ' mm ') = 2;

--Query the job title is the employee's full name, salary, and in descending order of the stock clerk
Select First_name| | ' ' | | Last_name,salary from S_emp
Where title= ' Stock clerk ' ORDER by salary desc;

--------------------------------------------------------------------------

Sort words:
Order BY Column name ASC (ascending, default not written) | Desc (Descending)

--Find out the employee's full name, salary, and job title in June and sort by salary in descending order
Select First_name| | ' ' | | Last_name,salary,title from S_emp
where To_char (start_date, ' mm ') =6 order by salary desc;

--Query the name of the employee with VP in the job title, salary, and sort by the descending wage
Select First_name,salary,title from s_emp where title like '%vp% '
Order BY salary Desc;

---Check out the employee's name, title, and rank according to the annual salary of less than 25000
Select first_name,title,salary*12* (1+NVL (commission_pct/100,0)) annual salary
From S_emp
where salary*12* (1+NVL (commission_pct/100,0)) <25000
Order by 3;

Note: ORDER by 3 means sorting by the 3rd column of the query result.
That is, order by can be sorted by column name (alias) or column number.

--------------------------------------------------------------------------

Dual is a virtual table with no meaning, just to act as a complete SELECT statement

Such as: Query the current database system time
Select Sysdate from dual;

One-line functions (single function):
Multiline function (group function)

A number of functions are available in Oracle
The different processing methods are divided into:
1. Number function:
Round (column | value | expression, number of significant digits); Rounded
Trunc (column | value | expression, number of significant digits); Direct interception
MoD (column | value, column | value); Modulus (remainder)
ABS (column | value) to find absolute value

Select Round (3.1415926,3) from dual;
Select Trunc (3.1415926,3) from dual;
Select mod (10,3) from dual;

--Check out the total amount in the order form (rounding)
Select Id,round (total,0) from S_ord;

2. Character functions
Length (column | value | expression); Request length
--Get the last name length for each employee in the employee table
Select First_name,length (first_name) from S_emp;

--View the length of the ' HelloWorld ' string
Select Length (' HelloWorld ') from dual;

Upper (column | value | expression); Turn capital letters
Lower (column | value | expression); Turn lowercase letters
Initcap (column | value | expression); Capitalize the first letter of each word

Select Name,score from S_student where lower (name) = ' Tom ';

Select UPPER (' abc ') from dual;
Select lower (' ABC ') from dual;
Select Initcap (' Hello World ') from dual;

Lpad (Column | value, width, padding character); Fills the width of the padding character from the left
Rpad (Column | value, width, padding character); Fills the width of the fill character from the right
Select Rpad (' abc ', Ten, ' de ') from dual;//abcdededed
Select Lpad (' abc ', Ten, ' de ') from DUAL;//DEDEDEDABC

LTrim (column | value, intercept character); Intercept characters from the left
RTrim (column | value, intercept character); Intercept characters from the right
Select RTrim (' Abcdededededede ', ' de ') from DUAL;//ABC
Select RTrim (' abcdededeededeee ', ' de ') from DUAL;//ABC
Select RTrim (' Abcdededeadedede ', ' de ') from Dual;//abcdededea

Replace (column | value, substituted character, substituted character) to displace the specified character
Select replace (' Hello world! ', ' o ', ' * ') from dual;

Translate (parameter One, parameter two, parameter three) converts the specified character
Parameter one: the content to be processed, the column | value.
Parameter two: What needs to be retrieved
Parameter three: corresponding to the contents of the retrieved content one by one

Select Translate (' I l6o9ve y8o7u! ',
' abcdefghijklmnopqrstuvwxyz0123456789 ',
' ABCDEFGHIJKLMNOPQRSTUVWXYZ ')
from dual;

Select replace (' Abcdededeadedede ', ' de ', ') from DUAL;//ABCA
Or
Select Translate (' Abcdededeadedede ', ' ABCDE ', ' abc ') from DUAL;//ABCA

InStr () finds the location of the specified string in a long string
Select InStr (' go,go,quickly! ', ' Go ') from DUAL;//1
Select InStr (' go,go,quickly! ', ' Go ', 2) from DUAL;//4
Select InStr (' go,go,quickly! ', ' Go ', -1) from DUAL;//4
Select InStr (' go,go,quickly! ', ' Go ', -1,2) from DUAL;//1

Note: The database string subscript starts at 1 counting!!!

SUBSTR ()
Select substr (' Hello world! ', 3) from Dual;//llo world!
Select substr (' Hello world! ', 3,4) from Dual;//llo
Start cutting from subscript 3rd and keep 4 characters

Decode ()
A. Similar to a three-mesh operator
Compares the first and second arguments if equal takes the third argument, if not equal to take the fourth argument
Select Decode (' A ', ' a ', ' B ', ' C ') from dual;

B.if else if Else

Case when you can think of it as a switch function:
Select name "department name",
Case region_id
When 1 Then ' North America '
When 2 Then ' South America '
When 3 Then ' Middle East/Africa '
When 4 Then ' Asia '
When 5 Then ' Europe '
Else ' unknown region '
End "Zone Name"
From S_dept;

Practice:
--Find out the last part of the phone row in the Customer table (S_customer);
Select Phone,substr (PHONE,INSTR (Phone, '-', -1) +1) from S_customer;

--To find out the suffix of the file name (filename) column in the picture table (S_image);
SELECT DISTINCT substr (filename,instr (filename, '. ', -1) +1) from S_image;

--simulates showing only the first character of a name to the bank (Obama becomes Austrian * *):
Select First_name,rpad (substr (first_name,1,1), Length (first_name), ' * ')
From S_emp;

Select First_Name,
Replace (FIRST_NAME,SUBSTR (first_name,2), Rpad (' * ', Length (substr (first_name,2))-1, ' * '))
From S_emp;

--Find employees with names longer than 5
SELECT * from s_emp where length (first_name) >5;

--Find out that the job title is stock clerk employees
SELECT * from S_emp where lower (title) =lower (' Stock clerk ');

--Please divide the employee's salary into 3, more than 2000 yuan for the higher level between 1500 and 2000 for the medium
Lower than 1500 for lower
Select First_Name Employee Name,
Case
When the salary<1500 then ' low '
When salary>=1500 and salary<2000 then ' Medium '
When salary>=2000 and then ' higher '
End wage level
From S_emp;

Note: The column name appears after the case, depending on whether the comparison column appears in the condition after
If the column name appears after the when, the column name must not be written after the case.
If the When is followed by a value, you must specify the column name after the case, or the given value cannot be determined
The real meaning.

--Output each order number and payment method, payment method is either cash,
It's either a credit card or it's unknown.
Select ID,
Decode (Payment_type, ' CASH ', ' cash ', ' credits ', ' Credit card ', ' unknown ')
From S_ord;

--the corresponding Chinese in each region of the output Region table (s_region)
Select Name,
Decode (name, ' Northern America ', ' North America ', ' South America ', ' sa ', ' africa/middle East ', ' Africa/Middle East ', ' Asia ', ' Asian ', ' Europe ', ' Europe ', ' Unknown area ')
From S_region;

3. Date function

The default date format for Oracle is DD-MON-YY

DD represents a 2-digit day
Mon/month for the month, for example: June
MM stands for 2-digit months, such as: 06
YYYY represents a 4-digit year
HH24/HH Representative Hours
MI stands for Minutes
SS stands for seconds
FM means to remove the previous 0, such as: FM 06, after the value of 6
Day means day of the week

Date function:
Sysdate Current Date
Months_between (date1,date2) Number of months between 2 dates
Add_months (Date1,n) on the basis of date1 for a few months
Next_day (Date1, ' Day of the Week ') on the basis of date1 what time next week?
Last_day (date) when is the last day of the month on which the date is

For example:
--Query the number of months between November 1, 2014 and December 9, 2015
Select
Months_between
(
To_date (' 2015-12-9 ', ' yyyy-mm-dd '), to_date (' 2014-11-1 ', ' yyyy-mm-dd ')
) from dual;

-What is the date after six months?
Select Add_months (sysdate,6) from dual;

--what is the date of six months ago?
Select Add_months (sysdate,-6) from dual;

--Query Current date What's the next Friday?
Select Next_day (sysdate, ' Friday ') from dual;

--When is the last day of the current month queried?
Select Last_day (sysdate) from dual;

Practice:
--Find out the number of employees working months
Select Months_between (sysdate,start_date) from S_emp;

--Query employee's working days
Select First_name,sysdate-start_date from S_emp;

Attention:
The date type in Oracle can be subtracted, resulting in a number of days of two dates!!!

--Calculate a year ago, current, one year later time
Select Add_months (sysdate,-12), Sysdate,add_months (sysdate,12) from dual;

--the last day of six months before the current date
Select Last_day (add_months (sysdate,-6)) from dual;

Add:
To modify the current session locale:
Alter session Set Nls_language = ' American ';//Change to English
Alter session Set Nls_language = ' Simplified Chinese ';//change to Simplified Chinese

To modify the format of the current session date:
Alter session set nls_date_format= ' Yyyy-mm-dd HH24:mi:ss ';

4. Conversion functions

To_number (char), converting characters to numbers

To_date (Char[,fmt]),
Converts a string to a date, and if the FMT parameter is not passed, the default format (DD-MON-YY) is used to parse

To_char to format numbers or dates as strings
1.to_char (NUMBER,FMT); Formatting numbers
2.to_char (date, FMT); Formatted date

Some of the symbols you can use to format numbers as strings:
9 stands for arbitrary data
L represents the local currency symbol
$ on behalf of USD
0 Rep 0
. Represent.
Represent

--Convert 123 strings to 123 digits
Select To_number (' 123 ') from dual;

--Convert 1986-04-13 string to date by template Yyyy-mm-dd
Select To_date (' 1986-04-13 ', ' YYYY-MM-DD ') from dual;

--formatted output employee's salary ($1,500.00)
Select To_char (Salary, ' l999,999,999,999.99 ') from S_emp;

Practice:
--Format the employee's entry date as a year/month/day
Select First_name,to_char (start_date, ' Yyyy/mm/dd ') from S_emp;

--Find out the employees who entered the May
SELECT * from S_emp where To_char (start_date, ' fmmm ') = 5;

--Find the employee who entered the month
SELECT * from S_emp where To_char (start_date, ' fmmm ') =to_char (sysdate, ' fmmm ');

--Check out the employee's name, date of entry, and sort by day in ascending order
Select First_name,start_date from S_emp
Order BY To_number (To_char (start_date, ' FMDD '));

--Find out the name of each employee and its salary (eg: $2,500.00)
Select First_name,to_char (Salary, ' $999,999,999,999.99 ') from S_emp;

Oracle Note 1-Database concepts