Oracle table Operations (iii)

Additions and deletions to check the orthogonal connection to create a table:

　　　　CREATE TABLE table_name (
num int identified (+) not NULL,--identified self-increment
Name VARCHAR2 () not NULL,--not null is not empty
Age VARCHAR2 (20),
Sex VARCHAR2 (2)
)

New columns:

ALTER TABLE TABLE_NAME

Add Col_name data_type

To delete a column:

ALTER TABEL TABLE_NAME

Drop Column Col_name

To modify a column:

ALTER TABLE TABLE_NAME

Alter COLUMN Col_name data_type

Inserting INSERT statements

To insert data into a specified column:

INSERT INTO table_name (DEPTNO,DNAME) VALUES (x, ' xx ');
Insert all column data:

INSERT INTO Dept (DEPTNO,DNAME,LOC) VALUES (x, ' xx ', ' lll ');

INSERT INTO Dept values (+, ' xxx ', ' llll '); Insert value contains all fields can be omitted field name

BULK INSERT: (new table needs to be created beforehand)

Insert into TABLE_NAME1 (col_name1,col_name2)-Table 1 column names

Select Column1,column2--Table 2 column names

From Table_name2 where search_condition--constraints

Using Insert into BULK INSERT: (new table does not need to be created beforehand)

Select Col_name1,col_name2

Into new_table

From table_name

where search_condition

Updating the UPDATE statement

Update specified data: UPDATE table_name set col_name= ' New_data ' where deptno=50;

Deleting a DELETE statement

Delete the specified data: Delete from table_name where condition=70;

Delete Deletes the data in the database, drop deletes the table structure

Delete Deletes row data, drop Delete column

Querying the SELECT statement

Query all: SELECT * from EMP;
Specify field query: SELECT COL_NAME1,COL_NAME2 from table_name;
Add WHERE Condition: SELECT * from EMP where sal>=800; SELECT * FROM table_name where sal>=1500 and job= ' salesman ';

Distinct to repeat records: SELECT Distinct col_name from table_name;
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 ordering: SELECT * from emp order BY Sal Desc;

The Order by clause must be placed at the end of all sentences (no matter how many clauses are included)
Subquery: Query for employees with base pay greater than average salary: SELECT * from emp where sal> (select AVG (SAL) from EMP)

ASC Sequential Query desc Inverse query, only the most recent columns, multi-column reverse order requires multiple DESC
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_user1
Minus
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;

Oracle table Operations (iii)