(iii) Oracle Learning NOTE--SQL statement

0. The Scott User default table describes

Scott user tables directory contains four tables

1. Insert (INSERT) statement

To insert data into a specified column:

Insert  into Values ("xx");

Insert all column data:

Insert  into Values (x,'xx','lll');

2. Update (UPDATED) statement

UpdateDeptSetDname='Ministry of Justice' whereDeptno= -;UpdateDeptSetDname='Ministry of Justice', loc=' China' whereDeptno= -;

3. Delete (delete) statement

To delete the specified data:

Delete  from where deptno=;

Delete data for the specified condition:

Delete  from where deptno>;

4. Select (query) statements

Check all:

Select *  from EMP;

To specify a field query:

Select  from EMP;

Shorthand

Insert  into Values (+,'xxx','llll');

Add where condition:

Select *  from where sal>=; Select *  from where sal>= and job='salesman ';

Distinct to repeat the record;

Group by Group query:select Job,count (ename) as Num from EMP T GROUP by Job;

having Filter Group:Select Job,count (ename) as Num from EMP T GROUP by Job have count (ename) >=2;

Order by Sort:select * from emp order BY Sal Desc;

subquery: Query for employees with base pay greater than average salary: SELECT * from emp where sal> (select AVG (SAL) from EMP)

Federated query:

and set (to repeat):

Select *  from T_user1 Union Select *  from T_user2;

and set:

Select *  from T_user1 Union  All Select *  from T_user2;

Intersection:

Select *  from T_user1 intersect Select *  from T_user2;

Subtraction

Select *  from T_user1minus Select *  from T_user2;

Internal connection:

SELECT * from emp t,dept d where T.deptno=d.deptno;

Similar to: SELECT * from EMP e inner joins dept D on E.deptno=d.deptno; Inner can be omitted;

External connection:

Left outer connection: SELECT * from emp e ieft join dept D on E.deptno=d.deptno;

Right outer connection: SELECT * from emp e starboard Join Dept D on E.deptno=d.deptno;

(iii) Oracle Learning NOTE--SQL statement