Oracle study Note 1

Select distinct --, -- from --; // query the specified row, which is not repeated.
Set timing on; // display the start time
Select count (*) from --; // count
Select -- ''-- from --; // alias of the name displayed
Nvl (comm, 0) // If comm is not empty, use 0 instead.
Select -- from -- where hiredate> '2017-01-01 '; // filter date
Select * from -- where-name like's % '; // The name starting
Select * from -- where-name like '_ O %'; // the third letter is the name of O.
Select * from -- where-name in ('hangsan', 'lisi', 'hangzhou ');//
Order by // sort
Order by -- desc; // descending order
Select * from -- order by a1, a2 desc; // first sort by a1 in ascending order and then by a2 in descending order
Select name sal * 12 as 'annual salary 'from -- order by 'annual salary ';
Subquery
Select min (sal), max (sal) from --; // maximum, minimum, grouping function (avg, count)
Select max (sal) avg (sal) deptno from emp group by deptno; // query by group
Select max (sal) avg (sal) deptno from emp group by deptno having avg (sal)> 2000; // query the departments whose average salary is greater than 2000.
The order is group by, having, order.
Select a1.ename, a1.sal, a2.deptname from emp, dept where a1.deptno = a2.deptno; // multi-Table query
Multi-Table query principle: the query result must be at least less than the number of tables-1
Select a1.ename, a1.sal, a2.grade from emp a1, salgrade a2 where a1.sal between a2.losal and a2.hisal; // employee salary level
// Self-connection
Select worker. ename, boss. ename from emp worker, emp boss where worker. mgr = boss. empno; // you can use a table as two tables to query the employee's boss name.
The filtering condition is from right to left, so we should write the condition as much as possible _____
// Multi-row subquery
Select ename, job form emp where job in (select dinstinct job from emp); // same job type
Select ename, sal, deptno from emp where sal> all (select sal from emp where deptno = 20); // The salary is higher
Select ename, sal, deptno from emp where sal> any (select sal from emp where deptno = 20); // The salary is higher than any
Select deptno, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'Smith '); // multi-condition Query

// Query the employees whose salaries are higher than the average salaries of the Department. The embedded view must specify an alias for the subquery.
Select a1.ename a1.sal a2.mysal from emp a1, (select deptno avg (sal) mysal from emp group by deptno) a2 where a1.deptno = a2.deptno and a1.sal> a2.mysal;
The column can be added with an alias, but the table cannot be an alias,
// There are three paging methods for querying oracle by PAGE: 1. rowid 2. rownum 3. Analysis Function
Select a1. *, rownum rn from (select * from emp) a1;
Select a1. *, rownum rn from (select * from emp) a1 where rownum> = 10; // query the first 10 rows
Select * from (select a1. *, rownum rn from (select * from emp) a1 where rownum> = 10) where rownum> = 6; // query 6-10 rows, subquery required
Select * from (select a1. *, rownum rn from (select * from emp order by sal) a1 where rownum> = 10) where rownum> = 6;
// All changes only need to be changed to the innermost part, sorted by salary

Create table myemp (id, ename, sal) as select empno, ename, sal from emp; // use the old table to quickly create a new table for copying and using experiment data

// Merge query union, union all, intersect, minus
Union // merge two tables and remove duplicate records automatically
Union all // merge two tables without removing duplicate records
Intersect // obtain the intersection of two sets
Minus // gets the difference set of the two sets. The result is null if the relationship is not inclusive.

Database creation tool: database configuration assistant

Insert into -- values ('','');
Alter session set nls_date_format = 'yyyy-mm-dd'; // change the default date format.
Select * from -- where -- is null;
Select * from -- where -- is null; // judge the null value.
Update -- set -- = ''where -- = ''; // update data
Delete from --; // delete data and record logs
Savepoint --; rollback to --; // save + rollback log
Drop table --; // delete data and table structure to record logs
Truncate table --; // delete data and table structure, without logging, fast

Desc table // view the table
Alter table -- add (-- number (2); // modify a table
Alter talbe -- modify (-- number (3 ));
Alter talbe -- drop colomn --; // delete a field

Image Type: blob
Date type: date/timestamp
Number Type: number
Character Type: char (2000) (High Efficiency)/vchar2 (4000)/clob (4G)

Disc/passw/show user/start *. SQL/edit/spool/conn
User: system/sys/scott
System Permissions
-- Database-related permissions (more than 140)
Object permission
-- Data object permissions (25 types)
Role connect/dba/resource

Create profile -- limit password_life_time 10 password_grace_time 2 password_reuse_time 10
You can use the old password again after 10 days.
Drop profile -- cascade // Delete the profile file