"Database System Overview"

Common databases are MySQL, Oracle, and so on. Different databases support SQL standards, and different databases have been expanded on the basis of SQL standards.
The study of the database includes:sql> process, trigger and other content, the importance of which is as follows:
Sql> processes, triggers, etc.
Oracle Database:
1. The development part of Oracle contains two parts: Sql+plsql programming
2. Oracle Management Section, database configuration and operation maintenance
"About Oracle"
Oracle has SYS and system two users by default, where
SYS: Super Administrator with all permissions to manipulate the database
System: General Administrator
Note: Multiple services will appear after you install Oracle and can be set to start manually
The two important services are:
1, database monitoring services, if you want to connect to the database through a remote client (such as SQL develop, etc.), or directly with the program to connect to the database, then this service must open
2, Database Strength services: Each database will have this service name such as: Oracleservicesid
"About Sqlplus"
Sqlplus first puts the SQL statement into the buffer and then submits the buffer's SQL statement to the database execution;
ORACLE12C The default data can not be found, you need to restore the data, find the Scott.sql file directory for the data
To modify the recovery data by modifying Scott.sql
The data configuration execution order is:
1. Open Sqlplus/nolog
2. Run C # #scott. sql
"Sqlplus Common Commands"
1. Format the command:
The data display has a newline problem and data paging occurs:
1, first to solve the screen width: set linesize 300
Set PageSize 30
2, convenient to write long database script, you can call Notepad: ed, you can edit the query command in technical this, and then you can use the @ tag to execute the database script
Sqlplus how to execute SQL scripts: using @+ scripts
3. Connection operation
Users can switch between each other
CONN username/password "as SYSDBA"
You can query the current user by show
Querying the tables in Scott in SYS requires that the user name be added before the
SELECT * from tab; query all tables
View the structure of the data table Desc:desc EMP;
Execute host command: host+ command
Host dir;

Questions about the original user:
Restore the original data:
1, Login SYS User: conn sys/oracle123456 as Sysdba
2. View the current container name: Show Con_name;
The return value is: Cdb$root is a CDB container
3, change the container for Pdb:alter session set CONTAINER=PDBORCL;
4. Open the PDB database:
ALTER DATABASE PDBORCL OPEN;
5. View Users
Open SH and Scott users
6. Switch back to CDB
Alter session Set Container=cdb$root

Grammar:
SELECT[DISTINCT] *| column name [as] column alias, column name [as] column alias .... From table name table alias
Use as to set aliases, alias best not to use Chinese
Two words in a simple query:
1. Select sentence
Distinct means to remove duplicate columns, all columns are the same content
2. FROM clause
On the order of execution of sentences:
1, from the sentence, determine the source of data
2. Select sentence to determine which columns to display
Arithmetic in select:
The result is returned as NULL when the numeric value that participates in the operation contains a null value
Check monthly salary, daily wage, etc.
Select Sal+comm msal from EMP;
To add a constant column:
Select ' Y ' as CL from EMP;
Use "| |" Making string connections
Select ' number is: ' | | empno| | ' Name is: ' | | Ename from EMP; strings are enclosed in single quotation marks.
"Qualifying Query"

1. Syntax:
SELECT[DISTINCT] *| column name [as] column alias, column name [as] column alias .... From table name table alias WHERE condition statement
Logical operators that connect multiple conditions: and or not

The qualifying query has three words, the following steps are:
1. Implement the FROM clause to control the source of the data
2. Execute the WHERE clause and filter the data rows using qualification
3. Execute select words to determine the data column

Common qualifying operators:
1. Relational operators, determining the comparison of size equality relationships
SELECT * from emp where Sal =2000
SELECT * from emp where ename= ' Smith '
When you use relational operators to judge a string, you need to pay attention to case, and the string with single quotation marks; string can be compared directly with "="
Not equal to the symbol "<>" and "! =" two forms of operation
SELECT * from emp where ename!= ' JAMES ';
SELECT * from emp where ename<> ' JAMES ';
Salary Range 1500 to 3000
SELECT * from emp where sal>1500 and sal<3000;
Sales staff base salary above 1200
SELECT * from emp where sal>1200 and job= ' Saleman ';

Scope Query
The Between and operator contains the maximum and minimum values
Find out all information about employees for 1981 years; the scope is 1981-1-1--1987-12-31
SELECT * from emp where hiredate between ' January-January -81 ' and ' 3 January-December-81 ';

Determine if the content is Null:is null/is not NULL (this is the only way to judge)
Note: null cannot be judged by an equal sign
SELECT * from emp where empno=7369 and comm are null;

List Range Lookup: In/not in
The so-called list scope refers to a number of values given to the user, which must be within the range of these values
SELECT * from EMP where empno in (7369,7788);
SELECT * from EMP where empno not in (7369,7788);
Attention:
With regard to NULL, if NULL is included in the in operator, it does not affect the final query result, and if it contains null in the not, the direct consequence is that no data is displayed
SELECT * from EMP where empno is not in (7369,null), database system limitations, No in NULL does not return any value

"Fuzzy Query"
Like/not like
You can use a wildcard character in a like sentence:
Percent percent: can match any type and length of the character, if it is Chinese use two percent percentile; (appears 0 or more times)
Underline _: Matches a single arbitrary character, which is commonly used to limit the length of an expression (occurs once)

Starting with J:
SELECT * from emp where ename like ' j% ';
Query the letter anywhere containing J, before and after the problem with percent sign
SELECT * from emp where ename like '%j% ';
The name is longer than six characters:
SELECT * from emp where ename like ' _____% ';
Like can be used for numbers or time types above, the keyword is null to indicate query all

Data Sort display
Order by default ascending
Traditional data query will only be set gradually, if you want to sort the specified column, you need to use ORDER by ordering
Syntax:
select[distinct] *| column name [ As] column alias, column name [as] column alias .... From table name table alias where condition statement order by asc| Desc
ASC: Ascending
Desc: Descending
The order by in all SQL words is placed on the last line of the query statement, the last sentence executed,
Select * from EMP where sal>1000 order BY Sal ASC;
Example: Sort by salary high, pay the same wage, follow the hire date early to late
SELECT * from emp order by Sal Desc,hiredate;

Single-line function
Syntax:
function_name[column] expression [parameter 1, parameter 2]
The single-line function is divided into the following types:
1, character function
with character data (string)
UPPER () and lower () function conversion case; ABCD
SELECT UPPER (' ABCD ') from dual;
Select LOWER (ename) from EMP;
SUBSTR () function
Select SUBSTR (' abc ', 2) from dual;--returns BC
Select SUBSTR (' abc ', -1) from dual;--returns C; The negative numbers are specific to Oracle from the back number, and the subscript starts with 1
ASCII:
Select ASCII (' A ') from EMP; +
Select CHR from dual; D
Trim function:
Select LTrim (' ADF ') from dual; Remove left space
Select RTrim (' ADF ') from dual; Remove right space
Select trim (' ADF ') from dual; remove Space
padding:
Lpad
Select Lpad (' Ad ', 4, ' * ') from dual; **ad
Lookup function:
InStr
Select InStr (' Floor ', ' OO ') from dual; 3
Select InStr (' Floor ', ' QQ ') from dual; 0
2, numeric function
Round rounding
Select Round (123.456) from dual; 123 do not keep decimals
Select Round (123.456,2) from dual;123.46 reserved two-bit decimal
Select round (123.456,-2) from dual; 100 integer part rounding
trunc intercept number of bits
Select Trunc (123.456,2) from dual; 123.45
MoD modulo
Select mod (10,3) from dual; 1

3. Date function
Date calculation operations and use of date functions
1, get the current date: Use Sysdate pseudo-column to get the current time
Select Sysdate from dual;20-8 month-16
By default, only three items are included, and you can modify the date format by modifying the default language
Arithmetic Operations for dates:
Date several days ago: date-Number = Date
Select sysdate-3 from dual; 1 July-August-16

Date after several days: Date + number = Date
Select sysdate+3 from dual;23-8 month-16

Two days of the day interval: date-date
Select Trunc (sysdate-hiredate) from EMP; Days employed

Calculation function for Date:
Add_months () function
Select Add_months (Sysdate, 3) from dual;20-11 month -16 three months after date
Select Add_months (Sysdate,-3) from dual;20-5 month-163 month ago Date
Next_day () function
Select Next_day (sysdate, ' Sunday ') from dual; Query next Sunday
Last_day () function
Select Last_day (sysdate) from dual; Last day of the month 31-8-16
Search for the third day of the month
Select ename from emp where Hiredate=last_day (sysdate)-2;
Month_between () Number of months
Select Trunc (Months_between (sysdate, HireDate)/12) years of employment from EMP;
Example: Calculates the number of years, months, and days that an employee has employed so far * *

1. Table to query: EMP
Select Ename,trunc (Months_between (sysdate,hiredate)/12) years,
Trunc (mod (Months_between ( sysdate,hiredate) months,sysdate-(Last_day (Add_months (sysdate,-1)) +1) days from EMP;
Select Ename,month_between (sysdate,hiredate)/12 years

4, conversion function
Primary used data type: character, number, and date (timestamp)
To_char () The
converts the date type into a string:
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual; 2016-08-20 20:26:25
Select To_char (Sysdate, ' Fmyyyy-mm-dd hh24:mi:ss ') from dual; 2016-8-20 20:30:14 Remove the leading 0
query for employee information that is hired every February
select * from emp where TO_CHAR (hiredate, ' MM ') =2;
The date of the split;
Select Ename, Empno,to_char (hiredate, ' yyyy ') year, To_char (HireDate, ' MM ') month, To_char (hiredate, ' DD ') Day from EMP
where To_char (hiredate, ' MM ') = 2; ALLEN749919810220
To_char () format number:
To_date () function; string--Date less
To_timestamp ()
To_number () Basic No
Select To_number (' the ') from dual; Automatic type conversions are supported in Oracle, select from dual; The result is the same

5. General functions: Characteristic functions provided by Oracle
The NVL () function is used to process null values
Example: query for yearly salary; Null to participate in the result of a null operation
Select NVL (sysdate-null,sysdate) from dual 20月-August-16 is null for sysdate
NVL2 () function
Select NVL2 (comm, sal+comm,sal) from EMP
Nullif, the same result returns an empty

DECODE () function
One of the most distinctive functions of oracle, similar to if else, but the judged content is a specific value
Select DECODE (2,1, ' Ground ', 2, ' Air ', ' default value ') from dual; If the value is 1, return ground, if the value is 2, return air, there is no matching return "default value"
Note: Use the decode () function to determine that all possible values are to be judged, the contents of which are not judged to be null,

After oracle9i, the case expression is introduced, and the given column or field is followed to determine
Select Ename,sal,
Case Job
When ' clerk ' and then sal*1.1
When ' salesman ' and then sal*1.2
Else
sal*1.5
End New Salary
from EMP;

"Multi-Table query" complex query
Multi-table Query first use Cartesian product to detect all records, and then filter by criteria
Grammar:
SELECT [DISTINCT] *| list as list from table name, indicating 2 ... where condition statement
Cartesian product problem:
SELECT * from EMP; 14 Records
SELECT * FROM dept; 4 Records
SELECT * FROM dept, EMP;56 Records 4*16
Hide the Cartesian product column: Use associated fields
SELECT * FROM dept T, EMP E where t.deptno=e.deptno; Show 14 rows
Note: The data volume is very large when generally do not use multiple table query, because the Cartesian product is definitely there, related fields just hide the Cartesian product records, and did not eliminate the Cartesian product
Example:
Query each employee's number, name, position, base salary, department name, department position information
1. Determine the required table:
EMP Table: Employee number, name, position, base salary
Dept Table: Department name, Department position information
2. Determine the associated field
Emp.deptno=dept.deptno
Select E.empno,e.ename,e.job,e.sal,d.dname,d.loc
From EMP e,dept D
where E.deptno=d.deptno;
Follow the steps in the SQL statement to write: from WHERE SELECT
Example: Identify each employee's number, name, hire date, base salary, salary level
1. Determine the required table:
EMP: Employee's number, name, hire date, base salary
Salgrade: Salary Level
2. Determine the associated field
Sal
Select E.ename,e.hiredate,e.sal,s.grade
From EMP E,salgrade s
Where Sal between Losal and Hisal;
Example: Querying each employee's name, position, base salary, department name, salary level
1. Determine the required table:
2. Determine the associated field
Step One: Identify employee information
Step two: Find out the Department table (eliminate Cartesian product)
Step three: Find out the salary scale table
Step four: Change level to Chinese
Select E.ename,e.job,e.sal,d.dname,s.grade,
Case S.grade
When 1 Then ' fifth '
When 2 Then ' fourth '
When 3 Then ' third '
When 4 Then ' second '
When 5 Then ' first wait '
End SG
From EMP e,dept D,salgrade s
where E.deptno=d.deptno and Sal between Losal and Hisal;

Join operations for the Multiple Table query table
Goal: Clear Table connection differences: internal links and outer joins
Inner connection: To hide the Cartesian product phenomenon by connecting with the equivalent of the associated field and eliminating the unequal connection of the associated fields.
Example: the difference between internal and external links:
1. Add an employee with no department information
2. Execute the following query statement
SELECT * from emp,dept where emp.deptno=dept.deptno;
Employees without departmental information are not shown, and if you want the data in the EMP or Dept table to display intact, you can take advantage of the outer join
Example: Using an outer join to hope that the EMP information is all displayed:

Outer joins: If you want the fields of a table to show all, you can use an outer join to control it using (+), only in Oracle (+)
This symbol can only be left outer join or right outer join
Left outer connection: SELECT * from emp e ieft outer JOIN Dept D on E.deptno=d.deptno;
Right outer connection: SELECT * from emp e r outer JOIN Dept D on E.deptno=d.deptno;
Full Outer connection: SELECT * from emp e full outer JOIN Dept D on E.deptno=d.deptno;
Note: External connections can only be used in Oracle (+)

Self-correlating:
The Mgr field in the EMP represents the employee's leadership information:
If you want to display leader information, you need to take advantage of the employee table and the employee table's own connection operation completion
Example: Query the employee name, number, and leader's number and name in the employee table

For employees without leadership information, the corresponding leader information is all connected using null
King has no MGR information, no display; workaround outside connection

Example: query for all employee numbers, names, employment dates (year and month) employed in 1981, name of work leader, monthly salary, annual salary (base salary + commission) wage level, department number, name, location, and the monthly salary of these employees at 1500-3500,
Finally, according to the annual salary in descending order, the same wage, according to the work sort
1. Determine the required data sheet
2. Identify the known associated fields
Set operations for data
A set operation is a binary operator with a total of four operators and a difference in the Cartesian product:
The syntax is as follows:
Query statements
[Union|union all |intersect|minus]
Query statements
SELECT * FROM dept; 4 results
SELECT * from dept where deptno=10; a result
Two query results return the same result structure
Union returns the entire contents of a number of query results, but the repeating meta-ancestor does not display
SELECT * FROM Dept Union
SELECT * from dept where deptno=10; 4 results
UNION ALL returns the entire contents of a number of query results, and the repeating meta-ancestor also displays
SELECT * FROM Dept UNION ALL
SELECT * from dept where deptno=10; 5 results
Example: Querying information for all sales and clerks
SELECT * from emp where job in (' Clerk ', ' salesman ');
SELECT * from emp where job= ' clerk ' or job = ' salesman ';
Note: Try to use Union or union all instead of or, the result structure of each query must be the same when set operation
SELECT * from emp where job= ' clerk '
Union
SELECT * from emp where job= ' salesman ';

Minus (difference set) returns the different parts of a number of query results
Intersect (intersection) returns the same part in several query results

Group Statistics Query:
1. Statistical functions
Mastering the use of standard statistical functions:
COUNT (*|distinct column) to find out the total number of records
The parameters in count can be used * or fields and dinstinct can be used
Select COUNT (*), COUNT (empno) from EMP; EMPNO has no null value, as a result
Select COUNT (*), COUNT (Mgr) from emp;15,14
Example: What is the difference between count (*), Count (field), Count (dinstinct)
1. All statistics
2. Do not count null values
3, do not count duplicate values
Try not to use *, all functions return NULL when there is no data, but count returns 0 when there is no data, so there is no need to judge the result in Java.

SUM () sum
AVG () Average
Max () max value
Min () Min value
Median () middle value
STDDEV () Standard deviation

The earliest hired and late hires of the sample statistics office.
The hire date is of type date, but functions in Oracle can be converted from one data type to the other, and the earliest employed hiredate value must be minimal.
Select min (hiredate) Zuizao,max (hiredate) Zuiwan from EMP;

Single field group query
Mastering the use of GROUP by
Requirement One: The company requires a group of each department to carry out tug-of-war competitions
Need to repeat the contents of the Department column
SELECT * FROM emp
Job and Deptno have duplicate content, it's best to group columns with duplicate content
Requirement two: A group of men and women in a class to debate competitions
Grammar:
Select Group Field | Statistics function from indicates GROUP by Group Field
GROUP BY clauses are used when grouping, but the SELECT clause allows the grouping fields and statistics functions to appear. * *
Example: Number of units per department in the Census and Statistics Department
Select Deptno, COUNT (empno)
From EMP
Group BY Deptno;
Example: Statistics on minimum and maximum wages for each position
Select Job,min (SAL), Max (SAL)
From EMP
Group by Job;
Master the use limit of group queries (the most troublesome place to limit this)
Precautions One:
If a GROUP BY clause does not exist in a query, only statistical functions are allowed in the SELECT clause, and no other fields are allowed to appear
Select Deptno, COUNT (*) from EMP; Prompt for "not a single group of grouping functions" error
Note two:
In a statistical query (a GROUP BY clause exists) only the grouping fields (the fields after group by) and the statistical functions are allowed in the SELECT clause, no other fields are allowed
Three things to note:
All statistical functions allow nesting, but once nested statistical functions are used, no fields are allowed in the select sentence, including grouped fields
Example:
Find the highest average wages per department
According to the division of the Department, and then the average value of each department of the Department, then for these statistical results to find a maximum
Example:
Query each department's name, department number, average salary, service life
1. Determine the required data sheet
2. Determine known field associations

Sentence execution order
From the where group by select order BY
Example: Find out the number and average salary of employees in each salary level of the company
1. Determine the required table
2. Determine the associated field
Example: Average wage of the Census and Statistics Department for employees who do not receive Commission and the median length of service, number of employees
1.
2.
Multi-field grouping:
Since multiple grouping fields can appear in the GROUP BY clause, multiple fields can also appear in the SELECT clause
Example: asking for detailed information about each department
Contains fields: Department number, name, location, average wage, total wage, maximum wage, minimum wage, number of departments. (Use multi-field grouping)
1. Determine the known data sheet
2. Determine the associated field
HAVING clause
Mastering the use of the HAVING clause
When you need to filter the data after the group by group, you can only do it through the HAVING clause
NOTE: The HAVING clause must be used in conjunction with the GROUP BY clause
Find out all job information, average salary and number of employees with average salary greater than 2000
Select Job,round (avg (SAL)), COUNT (empno)
From EMP
GROUP BY Job has avg (SAL) >2000
Execution order of statements from, where, group by, have, order by
Example:
List all department numbers and names of at least one employee and count the average wage, minimum wage, maximum wage for these departments
1. Determine the required data sheet
2. Determine the known associated fields:
Sub-query
The syntax format in a subquery does not have any new technology, similar to the inner class of Java, and in development, the use of subqueries is definitely more
Complex query = Limited query + multi-table Query + statistical Query + sub-query, there are more parts in the written examination.
Example: Find complete information about the lowest-paid employee in the company
SELECT * from emp where sal= (select min (sal) from EMP);
There are four types of data returned according to the type:
Single row
Single-row multi-column
Multi-row Multi-column
Multiline single row
Multi-row Multi-column
Where the subquery appears:
1. Where
Single row
Example: Querying employees with lower base pay than Allen

Example: Querying an employee with a base salary higher than the company's average salary

Example: Find all employees who work in the same way as Allen and receive your salary above employee number 7521
Example: Querying an employee with the same salary as Scott (returns two rows in one row)
SELECT * from emp where (job,sal) = (select Job,sal from emp where ename= ' SCOTT ');
SELECT * from emp where (job,sal) = (select Job J,sal s from emp where ename= ' SCOTT '); column does not correspond to
Example: Querying for the same employee who works the same as 7566 and leads the same
Example: Querying employee information for the same job as Allen and employed in the same year
Multi-line Single column:
If the subquery returns a multi-line column, there are three main operators used: In,any,all,not in
Example: Querying all employee information that is the same as the minimum wage for each department
Example: Querying all employee information that is not the same as the minimum wage in each department
Note: If the result of the in-neutron query is again in, the return data in the not-in subquery is null to indicate that no data will be returned
Any operator
=any: The function and in are the same, but the <>any is not equivalent to not in;
>any is larger than the maximum value
<any is smaller than the minimum value
Example:
All operator
Null data judgment
exists is used to determine if there is data return
SELECT * from emp where exists (SELECT * from EMP where empno=9999); subquery has no content and does not return
SELECT * from emp where exists (SELECT * from EMP); Returned with the result, the data will all return
2, having, must indicate that the operation will perform the grouping
A subquery in the having typically returns a single-row column, which is returned as a numeric value
Example: Querying the Department number, number of employees, average wage, and requiring the department's average salary to be higher than the company's average
Example: Query the department name with the highest average wage for each department and the average salary (no fields are allowed in the select sentence when the statistical function is nested, including group fields)
3, from the main function is to determine the source of data, the source is the data table (row + column collection), so it is generally multi-row multi-column subquery
Example: Query the number, name, location, number of departments, average salary for each department (you can use multiple table queries and subqueries two methods)
Use subqueries instead of multi-table queries to avoid Cartesian product, so prioritize subqueries
Example: Find out all employee numbers, names, base pay, bonuses, positions, hire dates, department's maximum and minimum wages in the Department ' sales '
1. Determine the required data sheet

For usage restrictions on statistical functions:
Use alone: No fields are allowed
Used with GROUP by: Allows grouping fields to appear
Example: Find out all the new employee numbers, name, base salary, position, employment date, department name, location, leader's name, company level, department number, average salary and service life.
1. Determine the required data sheet
2. Identify the known associated fields
Example: List the manager's name, salary, department name, number of departments, average salary of department in each department of the company
1. Determine the data sheet
2. Determine the associated field
4, select with a relatively small
Example: Check out department number, department name, department number, department average salary
With clause
You can use the with to create a temporary table query
Example: Querying each department's number, name, location, department average salary, number of people (using with)
Example: query for each department's highest-paid employee number, name, position, hire date, salary, department number, department name, and the final display results sorted by department number

Analytic functions:
Understand the main syntax of analytic functions:
Understanding the use of Windows
Delete syntax:
Delect from indicates where
Update Syntax:
Update indicates set a=b where
"Transaction Processing"
All SQL statements in the same session are executed as a whole
Server through session to distinguish between different users, each session corresponding to a user
Atomicity, consistency, isolation, and durability
Session---Cache
The update operation will not take effect until it is commit
Rollback rollback, savepoint+ save point Name
Basic concepts of Locks
A lock refers to a different session that simultaneously operates on the same resource.
Two sessions perform the same update operation statement:
Two types of locks:
Row-level Locks:
Features: When a transaction performs a corresponding data operation, if the transaction is not committed, the data for these operations is locked in an exclusive manner until the transaction is released.
Table-Level Locks:

"Database System Overview"