Oracle 11g SQL Statement Supplemental Learning

To add a column:

ALTER TABLE TableName add ColumnName datatype (not NULL); -------One thing to note is that when adding a column that is not empty, the table must be an empty table.

To delete a column:

ALTER TABLE tablename DROP column ColumnName

To change the data type of a column:

ALTER TABLE TableName Modify COLUMNNAME datatype

Change Column Name:

ALTER TABLE tablename Rename column columnName to New_columnname

Delete data:

Delete tablename;

TRUNCATE TABLE tablename;

drop TABLE TableName; --------Delete a table

Common aggregation functions:

Take integer function

Floor ()-----Rounding up

Ceil ()-----Rounding down

Trunc ()-----Rounding

Round ()-----rounded rounding

You can use this function when querying your age

Select Trunc (Months_between (*,*)/12) from table-----------The former is the date of the future

Or

Select Trunc (sysdate-datadate)/365 from table

Months_between () returns the difference in how many months, two date data subtraction is the difference in how many days

Union and UNION ALL------------one remove duplicates a full display

Use of case

The first two syntaxes:

case when field name = ... then ... (infinite access) else ... end

Case field name when ... then ... (infinite access) else ... end

Don't write End is a pig, forget n+1 times

Decode () function

The decode () function is somewhat similar to the use of Case...when

Decode (field name, ' Value 1 ', ' corresponding value of value 1 ', ' Value 2 ', ' value 2 corresponding value ' ... (Infinite Connection) ..., ' Default ')

Vm_concat () function

Select Sname,student.sno,wm_concat (CNO) from student join score on STUDENT.SNO=SCORE.SNO GROUP by Sname,student.sno Order by Student.sno

Student table:

Score Table:

Query Result:

concat function:

Connect two query results to a maximum of two

Select Concat (sname,sbirthday) from student

Replace () function:

Replace (field name, ' Value 1 ', value 2)

Query the contents of a field, if he is ' value 1 ', replace it with ' value 2 '

NVL () and NVL2 ():

NVL (field name, ' value ')

If the value of this field of the query is null (NULL), return ' value '

NVL2 (field name, ' Value 1 ', ' Value 2 ')

Returns ' Value 1 ' If the query result is NOT NULL, if NULL returns ' Value 2 '

Oracle 11g SQL Statement Supplemental Learning