Querying the database

One, multi-table query

Syntax:select * from table name 1, table name 2 where conditional expression (primary key = foreign key)order by ordering

PRIMARY key: Must have a value (not null) and is not duplicated and can be used as the identity of the current row

primary Key = foreign key: The foreign key in the current table must be the primary key of another table

1.1 Inline Query ( content in the Where Condition: primary key = foreign key)

in the query results, the primary key is in, the foreign key also has the value will be queried, the primary key does not have the external key in the value does not check

To be consulted

Example: Query the employee's number, employee name, department number, department name and department location

Selecte.empno,e.ename,d.deptno,d.dname,d.loc from EMP e,dept D where

E.deptno = D.deptno;

or:selecte.empno,e.ename,d.deptno,d.dname,d.loc from emp e inner JOIN

Dept D on e.deptno = D.deptno;

that is , the two tables are connected by ainner join, where with on replace

1.2 outer query ( content in Condition: primary key = foreign key)

LEFT outer: Values in the left table appear in the query results, even if there is no corresponding data in the right table

Example:select * FROM Dept D left joins Empe on d.deptno = E.deptno;

Department can still query, the data in its EMP is replaced by null

1.3 Self-union query ( content in the Where Condition: a column in this table = primary key column in this table)

Example 1: The name of each employee, name, job, department and Supervisor

Select E.empno,e.ename,e.job,m.ename,d.dname from emp e,emp m,dept D

where e.mgr = M.empno and E.deptno =d.deptno;

Example 2: The name ofeach employee, salary, department name, salary level and the name and salary of the superior leader are queried .

Grade

Select E.ename,e.sal,m.ename,d.dname,s.grade,m.sal,sm.grade from emp

E,emp m,dept d,salgrade s,salgrade sm wheree.mgr = m.empno and E.deptno =

D.deptno and (E.sal between S.losal ands.hisal) and (M.sal between sm.losal

and Sm.hisal);

Second, group functions and grouping statistics

2.1 Group Functions

A,count (): Find out the total number of records

usage:selectcount (*) from EMP;

b,max (): Find the maximum value in a set of data

usage:selectmax (SAL) from EMP;

C,min (): Find the minimum value in a set of data

usage:selectmin (SAL) from EMP;

D,sum (): Sum

usage:select sum (SAL) from emp Wheredeptno = 20;

E,avg (): Averaging

usage:select AVG (SAL) from emp Wheredeptno = 20;

2.2 Grouping Statistics ( grouping by using GROUP by)

Syntax:select * from table name where conditional expression GROUP by grouping condition ORDER by ordering

Field

Example 1: Find out the number of employees in each department, divided by department number

Select Deptno,count (empno) from the EMP group by DEPTNO;

Example 2: Find out the average salary for each department

Select Deptno,avg (SAL) from the EMP group by DEPTNO;

Example 3: Grouping by department and showing the name of the department and the number of employees in each department

Select D.dname,count (ename) from EMP e,dept d where E.deptno = D.deptno

Group BY D.dname;

Having conditional directives:

Syntax:select * Form table name where conditional expression GROUP by grouping condition having condition

An expression order by sort field

Example 4: Show the department number and average salary of the average salary greater than

Select Deptno,avg (SAL) from EMP Group BY DEPTNO have avg (SAL) >2000;

Example 5: Displays the non-salesperson's job name and the sum of the monthly wages of the employee who is engaged in the same job, and satisfies

employees of the same job have a total monthly wage of more than one, and the output is sorted in ascending order of monthly wages

Select Job,sum (SAL) from EMP where Job <> ' salesman ' GROUP by job

Having a sum (SAL) >5000 ORDER by sum (SAL) ASC;

Example 6: Find the department with the highest average wage

Select Max (SAL) from the EMP Group by

Third, sub-query

Concept: Another query is included inside a query

Syntax:select * from table name where conditional expression (SELECT * from table name where conditional expression

GROUP BY group condition having conditional expression ORDER by sort field ) group by grouping condition

have conditional expression ORDER by sort field

Example 1: Find out All employee information that is higher than 7654 salary

SELECT * from emp where sal > (select sal from emp where empno =7654);

Example 2: Query for all employee information that has a higher salary than 7654 while working with 7788

SELECT * from emp where sal > (select sal from emp where empno = 7654) and

Job = (select Job from emp where Empno =7788);

in subqueries, there are three types of operational symbols for queries:in,any,all

3.1 In operator

role: Specify the scope of a query

Example 3: Finding employee information for the minimum wage for each department

SELECT * from emp where Sal in (select min (sal) from EMP Group BY

DEPTNO);

3.2 Any operator

=any ( exactly the same as the in operator )

> any ( larger than the smallest value inside )

< any ( smaller than the maximum value inside )

SELECT * from emp where sal = any (select min (sal) from EMP Group BY

DEPTNO);

3.3 All operator

>all ( larger than the maximum value )

<all ( smaller than the smallest value )

SELECT * from emp where Sal > All (the Select min (sal) from EMP Group BY

DEPTNO);

Iv. Database Update operations

The main operations of the database are divided into two types:

1, the database query operation:Select

2.Update operation of database:insert,update,delete

to save The information of the original EMP table, copy the table before adding, modifying, deleting, i.e.:

CREATE TABLE Myemp as SELECT * from EMP;

4.1 Add Data Insertinto

syntax:insert into table name [( field name 1, field name 2 ...)] VALUES ( value 1, value 2 ...);

Example 1: Add a new record for the Myemp table, complete according to standard practice

Insert into Myemp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values

(7899, ' Zhang San ', ' cleaners ', 7369, ' 14-2 month -85 ', 9000,300,40);

Example 2: Inserting a new employee, but at this time the employee has no lead and no bonus

1), do not explicitly write out the field name to insert, the field without data is written as null

INSERT into myemp values (8881, ' John Doe ', ' cleaner ', NULL, ' 17-5 month -

(9000,null,40);

2), explicitly write out the name of the field to insert

Insert into Myemp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values

(8882, ' Harry ', ' cleaners ',null, ' 23-6 month -87 ', 9000,null,40);

3), change the data of a string type to date type

Insertinto myemp (Empno,ename,job,mgr,hiredate,sal,comm,deptno) values

(8883, ' Zhao Liu ', ' cleaners ', 7369,to_date (' 1988-12-23 ', ' yyyy-mm-dd '), 9000,300,40);

4.2 Modifying Data update

Syntax:

1), modify all:update table name set to modify the field = new Value , the field to be modified = New Value ...

2), modify the Local:update table name set to modify the field = new Value , the field to be modified = New Value ...

Where to modify conditions

Example 3: Change the bonuses of all employees in the Myemp table to ---> all modifications

Update Myemp Set comm = 1000;

Example 4: Change the salary of an employee numbered 7899 to---> local modifications

Update myemp Set sal = Empno = 7899;

Example 5: cancellation of 7369,8899,7788 leaders and bonuses

Update myemp Set mgr = Null,comm = null where empno in (7369,8899,7788);

4.3 deleting data Delete

Syntax:

1), delete all: deletefrom table name

2), delete Local: deletefrom table name where delete condition

Example 6: Deleting an employee information that is a number 7899

Delete from myemp where empno = 7899;

Example 7: Delete all employees who collect bonuses

Delete from Myemp where comm are not null;

V. Transaction processing

concept: To ensure data integrity, all operations are either successful at the same time or fail at the same time. each connected to a database

user indicates that a session has been created, and that a session's changes to the database are not immediately reflected in the database's true

real data is allowed to roll back, when a session commits all operations, the database is actually modified.

The following two main commands are provided in the operation of the database to complete the processing of things:

---> commit things:commit

---> Rollback things:rollback

Example 1: Create a temporary table that contains only the employee information for the department

CREATE TABLE EMP10 as SELECT * from emp where empno = 10;

Example 2: Remove employee information from 7782 in the EMP10 table

Delete from emp10 where empno = 7782;

transaction Operations : first define the start of a transaction , and then modify the data , if committed , these changes will never

long time to save , if fallback (ROLLBACK), the database management system will discard all of your modifications and return to the beginning of the transaction

The state.

properties of the transaction:

1, atomicity: Refers to the transaction is an inseparable unit of work, the operation of the transaction either occurs, or does not occur.

2, consistency: The transaction must transform the database from one consistent state to another consistent state. ( data is not broken

Bad )

3, isolation: Refers to the execution of a transaction can not be disturbed by other transactions, that is, a transaction inside the operation and use of data

The other transactions that are concurrent are isolated, and the transactions that are executed concurrently cannot interfere with each other.

4, Persistence: Refers to a transaction once committed, it changes the data in the database is permanent, the next other

Operations and database failures should not have any effect on them

concurrency problems with multiple transactions concurrently running:

1.Dirty read : one transaction reads the data that another transaction overwrites but has not yet committed , and if the data is rolled back, the read

The data is not valid.

2, non-repeatable read: In the same transaction, multiple reads of the same data returned by different results. In other words, after

The continued read can read the updated data that was committed by another transaction.

3, Repeatable read : in the same transaction to read data multiple times, can guarantee the same read data, that is, the subsequent read does not

Can read the updated data that has been committed by another transaction.

4. Magic read: After a transaction has read several rows of records, another transaction inserts some records, and the phantom reads occur. and later.

Query, the first transaction will find some records that were not originally found.

Querying the database