Basic syntax for Oracle database operations

Create a table

Sql>create Table Classes (

ClassId Number (2),

CNAME varchar2 (+) ,

Birthday Date

);

Add a field

sql>alter Table Student Add (classId number (2));

Modify field length

Sql>alter Table Student Modify (XM VARCHAR2 (30));

Modify the type of the field/ or a name (no data)

SQL >alter Table Student Modify (XM char ());

Delete a field

Sql>alter table student Drop column Sal;

Modify the name of the table

Sql>rename student to Stu;

Delete a table

Sql>drop table student;

Insert All field data

Sql>insert into student values (' 001 ', ' Salina ', ' female ', ' 01-5 month -05 ', ten) ;

Modify Date Input format

Sql>alter Session Set Nls_date_format = ' yyyy-mm-dd ';        // temporary effect, no wrong use after reboot

Sql>insert into student values (' 001 ', ' Salina ', ' female ', to_date (' 01-5 -05 ', ' yyyy-mm-dd '), 10) ;

Sql>insert into student values (' 001 ', ' Salina ', ' female ', to_date (' 01/5 -05 ', ' yyyy/mm/dd '), 10) ;

Insert a partial field

Sql>insert into student (xh,xm,sex) VALUES (' 001 ', ' Lison ', ' women ');

Insert Null value

Sql>insert into student (xh,xm,sex,birthday) VALUES (' 021 ', ' Blyk ', ' male ', null);

An INSERT statement can insert multiple rows of data

Sql> INSERT INTO KKK (myid,myname,mydept) select Empno, Ename,deptno from EMP where deptno=10;

query Null/( non-null) of Data

Sql>select * FROM student where brithday is null (/not null );

Modify (update) data

SQL >update student set sal=sql/2 where sex = ' Male ' ;

Change multiple data

sql> update emp Set (JOB,SAL,COMM) = (select Job,sal,comm from emp where ename= ' SMITH ') where ename= ' SCOTT ';

Delete data

1. 1. Save restore points

Sql>savepoint AA;

1. 2. Delete Data

"1" sql>delete from student;// Delete data from a table

"2"  sql>drop table student; Delete the structure and data of a table

"3" Sql>delete from student where xh= ' 001 '; Delete a record

"4"  sql>truncate table student; Delete all the records in the table, the table structure is still in, do not write logs, can not get back records, Fast

View table Structure

Sql>desc student;

Querying a specified column

Sql>select SEX,XH,XM from student;

How to cancel Duplicates

Sql>select distinct deptno,job from student;

Turn on switch to show operation time

Sql>set timing on;

Add large rows of data to the table (for test reaction time)

Sql>insert to Users (USERID,USERNAME,USERPSS) select * from users;

How many records are in the statistics table?

Sql>select Count (*) from users;

Block the same data in a column

Sql>select distinct deptno,job from EMP;

Querying data about a specific column of data

SQL >select deptno,job,sal from emp where ename= ' Smith ';

Using an arithmetic expression

Sql>select sal*12 from EMP;

Using Aliases for classes

sql>select ename " name " , sal*12 as " annual income "from EMP ;

handling null ( null ) value

sql>select SAL*13+NVL (comm,0) *13 " Annual Salary " , Ename,comm from EMP;

Connection string (| | )

SQL >select ename | | ' is a ' | | Job from EMP;

Where use of clauses

"1"sql>select ename,sal from emp where sal>3000; //number the range determines

"2 "Sql>select ename,hiredate from emp where hiredate> ' 1-1 month -1982 '; // Range determination for date formats

"3 "Sql>select ename,sal from EMP where sal>=2000 and sal<=2500;       // Combination Conditions

like operator: '% ' , ' _ '

sql>select ename,sal from emp where ename like ' s% ';               // first character "first character is S" Employee's information (salary) "

sql>select ename,sal from emp where ename like ' __o% ';           // other Characters "the third character of the first name is O Employee's information (salary) "

Bulk Query

Sql>select * from EMP where in (123,456,789);        // query batch processing for multiple cases of a condition

Querying data about a column of a data row that is empty

SQL >select * from EMP where Mgr is null;

Conditional combination query (with, or)

Sql>select * from EMP where (sal>500 or job= ' MANAGER ') and ename like ' j% ';

Order by Sort

"1 "Sql>select * from emp order by Sal (ASC );        // from low to high [ default]

"2 "Sql>select * from emp order by sal Desc;       // from high to low

"3 "Sql>select * from emp ORDER by DEPTNO (ASC), Sal Desc;      // Combined Sort

"4 "Sql>select ename,sal*12" annual salary "from EMP order by "Annual Salary" (ASC);

sql> Select Ename, (SAL+NVL (comm,0)) *12 as " annual salary "from emp order BY" annual salary ";

Data grouping (max , Min , Avg , Sum , Count )

Sql>select Max (sal), Min (sal) from EMP;

sql>select ename,sal from emp where sal= (select Max (SAL) from EMP); subquery, combined query

sql> SELECT * from emp where sal> (select AVG (SAL) from EMP); subquery, combined query

sql> update emp set sal=sal*1.1 where sal< (select AVG (SAL) from EMP) and hiredate< ' 1-1 month -1982 ';       // pay less than the average wage and enter a career earlier than 1982-1-1 People's wages increased 10%

Group by and having clauses

//group by used to group statistics on the queried data

//having use to restrict grouping display results

sql>select avg (SAL), Max (SAL), Deptno from EMP Group by DEPTNO;         // show average and minimum wages for each department

sql>select avg (SAL), Max (SAL), Deptno from EMP Group by DEPTNO;         // show average and minimum wages for each department

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

Sql> Select AVG (SAL), Max (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) >2000 ORDER by AVG (SAL);

Multi-Table Query

Descartes Set : specify that the condition of a multiple-table query is at least not less than: the number of tables -1

Sql> Select A1.ename,a1.sal,a2.dname from emp a1,dept A2 where A1.deptno=a2.deptno;

sql> Select A1.dname,a2.ename,a2.sal from dept a1,emp A2 where A1.deptno=a2.deptno and a1.deptno=10; Show Department name, employee name, and salary for department number

Sql> Select A1.ename,a1.sal,a2.grade from emp a1,salgrade A2 where a1.sal between A2.losal and A2.hisal;

sql> Select A1.ename,a1.sal,a2.dname from emp a1,dept A2 where A1.deptno=a2.deptno order by A1.deptno;                    // Multi-table sorting

sql> Select Worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;        // Self-connect (special case for multi-table queries)

Sql> Select Worker.ename,boss.ename from emp worker,emp boss where Worker.mgr=boss.empno and Worker.ename= ' FORD ';

Sub-query

Sql> SELECT * from emp where deptno= (select Deptno from emp where ename= ' SMITH ');

Sql> SELECT DISTINCT job from EMP where deptno=10;

sql> SELECT * from emp where job in (select distinct job from EMP where deptno=10);    

// How to inquire and Department ten The name, position, salary, department number of the employee who works the same.

sql> Select ename, Sal,deptno from EMP where Sal>all (select Sal from EMP where deptno=30);// How to inquire about payroll than Department The names, wages, and department numbers of all employees with high wages

Sql> Select ename, Sal,deptno from EMP where sal> (select Max (SAL) from EMP where deptno=30);

Sql> SELECT * from emp where (deptno,job) = (select Deptno,job from emp where ename= ' SMITH ');

Inline view

// when in the from when using a subquery in a clause, you must specify an alias for the subquery

Sql>select a2.ename,a2.sal,a2.deptno,a1.mysal from EMP A2, (select Deptno,avg (SAL) (AS) Mysal from EMP Group by DEPTNO) A1 where A2.deptno=a1.deptno and a2.sal>a1.mysal;

Page out

SQL >select a1.*,rownum rn from (SELECT * from emp) a1;//orcle the row number assigned to the table

Sql> SELECT * FROM (select A1.*,rownum rn from (SELECT * from EMP) A1 where rownum<=10) where rn>=6;

// changes in query content

1. All changes (specify query columns) only need to change the innermost subquery
2. (sort) Just change the innermost subquery

Subquery (Create a new table with query results)

Sql> CREATE TABLE MyTable (Id,name,sal,job,deptno) as select Empno,ename,sal,job,deptno from EMP;

Merging queries

Union ( Set), union ALL, intersect (take intersection), minus (difference set)

Sql> Select Ename,sal,job from emp where sal>2500;

Sql> Select Ename,sal,job from emp where job= ' MANAGER ';

sql> Select Ename,sal,job from EMP where sal>2500 Union select ename,sal,job from emp where job= ' MANAGER '; Union ( Seek and set)

Java Connection Database

Transaction

sql>commit; Transaction (first created, second commit) when you exit the database, the system commits the transaction automatically

Sql>savepoint A1; Create a savepoint (there is no limit to the number of Save points)

sql>rollback to AA; Roll back to AA with SavePoint

Sql>rollback ;// Rollback to transaction creation start

Read-Only transactions

Sql>set transaction Read Only

Java the transactions in

Ct.setautocommit (FALSE); SET Transaction autocommit to No

Ct.commit (); commit a transaction

Character functions

Lower (char) to convert a string to a lowercase format

Upper (char) format a string in uppercase

Length (char) returns the length of a string

substr (char,m,n) take a substring of a string

Sql>select Lower (ename) from EMP;

sql>select ename from emp where length (ename) = 5;

Basic syntax for Oracle database operations