/* ROWNUM is a only when actual data returned an additional series of */
Select rownum, deptno, dname from dept.
Insert into dept values (30, 'jack', 'New York');
Insert into dept values (40, 'AAA', 'New York');
Insert into dept values (50, 'VVV', 'New York');
Insert into dept values (60, 'CCC', 'New York');



Create a table/* */

The create table student (
StuName varchar2 (10) NOT NULL,
StuNo char (6) not null,
StuAddress varchar2 (50)
)

Change the database field properties */*/
The alter table student modify (stuName varchar2 (50));

/* field is added in the data table */
The alter table student add (stuPhone varchar (11))

/* delete data in the table a field */
The alter table student drop (stuPhone);

/* delete table */
Drop table student;

/* * insert data/
Insert into student values (' zhang ', '000001', 'China');
Insert into student values (' bill ', '000002', 'China');
Insert into student values (' Cathy ', '000003', 'China');
Insert into student values (' zhang ', '000004', 'China');
Commit;

Select * from student;

/* remove return duplicate data as to name the fields one alias */
Select distinct stuname as "student's name from" student

/* the order by according to a certain field sorted */
Select * from student order by stuName desc

/* create a new table, but the data of this table from student */
The create table student2 as select * from student;

/* choose specific columns to create new tables */
The create table student3 as select stuname, stuaddress from student2

The create table student4 as select * from student where 1=2

Select * from student4

Select count (*) from student;
Select count (1) the from student;
Select count (stuname) from student

Select the rowid, stuname, stuno from student group by rowid, stuname, stuno having (count (stuname | | stuno) & lt; 2)


Drop the from student where rowid not in
The select Max (rowid) from student group by stuname, stuno having (count (stuname | | stuno)=1)

The select Max (rowid) from student group by stuname, stuno having (count (stuname | | stuno) & gt;
=1)
/* screening repetitive data */
Select the rowid, stuname from student where rowid not in (
The select Max (rowid) from student group by stuname, stuno having (count (stuname | | stuno) & gt;
=1))

/* delete duplicate data, (student's name and student number is the same as repeat) */
The delete from student where rowid not in (
The select Max (rowid) from student group by stuname, stuno having (count (stuname | | stuno) & gt;
=1));
Commit;

Select * from student




The create table tTest (
STR char (5) the not null
)

Insert into tTest values (' a ');
Insert into tTest values (" b ");
Insert into tTest values (" c ");
Insert into tTest values (' d ')

Select * from ttest

/* * to create an archive point/
The savepoint a;
Insert into tTest values (' e ')

/* back to the point of a certain file */
The rollback to a

/* back all uncommitted transactions */
The rollback.

Commit;



Select * from ttest
Insert into tTest values (" f ");
Insert into tTest values (' g ')
Commit;


Select rownum, STR from ttest order by STR desc

Select the STR, rownum rn from (select STR from ttest order by STR desc) where rownum> 1 and rownum<7

/* Orcale paging */
Select * from (select STR, rownum rn from (select STR from ttest order by STR desc)) where rn & gt;=2 and rn
=6

Set operators/* */
/* create table */
The create table tablea (
/* the not null can't be empty */
STR char (2) not null
)

/* create table */
The create table tableb (
STR char (2) not null
)

/* * insert data/
Insert into tablea values (' a ');
Insert into tablea values (" b ");
Insert into tablea values (" c ");
Insert into tablea values (' d ');
Insert into tablea values (' a ');
/* to commit the transaction */
Commit;
Select * from tablea;

Insert into tableb values (' a ');
Insert into tableb values (" b ");
Insert into tableb values (" b ");
Insert into tableb values (' d ');
Insert into tableb values (' e ');
Commit;

Set operators/* */
/* the union joint query, the data returned is not repeated data (automatically remove duplicate data, only keep a) */
Select the STR from tablea union select STR from tableb;
/* union all will all the data returned from the two tables, including the duplicate data */
Select the STR from tablea union all select STR from tableb;

Select the STR from tablea;

/* minus returns in the first table but not found in the second table data (the first table refers to the left of the minus table!!! ) */
Select the STR from tablea minus the select STR from tableb;

/* */intersects return a list of two common data
Select the STR from tablea intersects the select STR from tableb;

CodePudding user response:

Thanks for sharing,

Record in a blog post,

CodePudding user response:

Minus will go to the heavy, if a table is 112, three data table b is 12 two data, and can also make for the same

CodePudding user response:

A good share blog next time