"Oracle" chapter II Common operations

ORACLE Chapter II Common operations

To modify column information in a table:

ALTER TABLE Vendor_master Modify (vencode varchar);

Add a default value constraint to a column in a table

ALTER TABLE mytable Modify (name VARCHAR2 () default ' abc ');

To add a primary KEY constraint to a column in an existing table:

ALTER TABLE testtable Add (constraint "Idkey" PRIMARY KEY ("ID"));

To add a column to an existing table:

ALTER TABLE Vendor_master Add (vid number (5));

To delete a column for an existing table:

ALTER TABLE vendor_master drop column vid;

Delete a table:

drop table Vendor_master;

To modify a column name:

ALTER TABLE mytable rename column name to NewName

--Unio returns all the non-repeating rows selected for two queries. use of Unio

Select Vencode from Vendor_master

Union Select Vencode from Order_master

--unio All returns two queries for all rows selected , including duplicate rows , Unio all use

Select Vencode from Vendor_master

UNION ALL select Vencode from Order_master

--intersect Returns a row for all two queries,intersect use

Select Vencode from Vendor_master

Intersect select Vencode from Order_master

the use operator of--minus only returns selected by the first query but not by the second one.

--Queries the selected row, that is, the row that appears in the second query result is excluded from the first query result

Select Vencode from Vendor_master

Minus

Select Vencode from Order_master

--- || use of connectors

Select (' supplier ' | | venname| | ' address is : ' | |  venadd1| | ' ' | | venadd2| | ' ' | | VENADD3) as address from vendor_master

-- Use the date keyword to provide a datetime text string to the database, which must be used yyyy-mm-dd format, the following code demonstrates Date Key Words ;

Insert into MYTABLE (ID, NAME, ADDRESS, Email,regdate)

VALUES (6, ' Rose ', ' Tianjin ', ' [email protected] ', date ' 2007-10-12 ')

--The following code demonstrates the addition of the values returned by the to_date () function to the database

INSERT INTO MYTABLE

(ID, NAME, ADDRESS, Email,regdate)
VALUES (6, ' Rose ', ' Tianjin ', ' [email protected] ', to_date (' 2007-10-23 12:36:58 ', ' yyyy-mm-dd hh24:mi:ss '))

-- The use of Add_month

Select Del_date, Add_months (del_date,2) from Order_master

--months_between uses this function to return the number of months between two dates , in the form of Months_between(d1, D2)

Select To_date (' 2009-11-30 ', ' YYYY-MM-DD ') from dual

Select Months_between (to_date (' 2009-12-31 ', ' yyyy-mm-dd '), to_date (' 2008-12-20 ', ' Yyyy-mm-dd ')) from dual

--The use of last_day , which returns the date value of the last day of the month for the specified date , in the format last_day (d)

Select Sysdate,last_day (sysdate) from dual

--ROUND, formatted as ROUND (d,[FMT]), D is date,fmt is a format model.

--fmt is an option, and the date is rounded to the nearest day by default.

-- If the specified format is year, then rounds to the beginning of the year, i.e. 1 months 1 days If the format is month, the first day of the month is rounded, if the format is week , is rounded to the nearest Sunday.

Select round (to_date (' 2009-8-13 ', ' yyyy-mm-dd hh24:mi:ss '), ' Day ') as M from dual

--Next_day, which returns the date specified for the next one weeks, in the format

--Next_day (D,day),D is the date, and Day refers to any of the days of the week.

Select Next_day (sysdate,' Friday ') from dual

The--trunc function truncates the specified date to the date of the unit specified by the format model .

- -unlike the ROUND function, it only does not enter

Select Trunc (to_date (' 2009-12-12 ', ' yyyy-mm-dd '), ' Day ') from dual

--The function extracts a specific part of a datetime type in the form

--extract (FMT from D)

Select Extract (year from sysdate) from dual

-- returns the corresponding character according to the ASCII code

Select CHR from dual

The--lpad function consists of three parameters, the first parameter is a string,

-- The string to the left needs padding, the second is a number,

-- It represents the total length of the return value, the third is a string,

- use it for left padding when needed.

Select lpad (' function ', ten, ' = ') from dual;

The--trim function crops a specific character from the beginning or end of a string, clipping a space by default

--leading means from left to right,trailing for right to left

--leading and trailing do not specify the case before and after a specific character is cropped

Select Trim (leading 9 from 99977589678999) from dual

Select Trim (9 from 99977589678999) from dual

--length function returns the length of a string

Select Length (' ABCD ') from dual

--decode function to replace values by value

--Select decode (EXPR,SEARCH1,RESULT1,SEARCH2,RESULT2 ... [Default])

Select Decode (2,1, ' same ') from dual

--To_char convert string function

Select To_char (sysdate, ' YYYY ' year "Fmmm" month "FMDD" day "HH24:MI:SS ') from Dual;

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

--to_date date conversion function

Select To_date (' 2009-10-10 13:12:12 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

-- converts the null value to the specified value,NVL (expression1,expression2)

Select VENCODE,VENNAME,NVL (venadd1, ' no ') from Vendor_master

--nvl2 ( the third argument is returned if the first parameter is not empty and the second argument is returned )

Select VENCODE,VENNAME,NVL2 (venadd1, ' Beijing ', ' no address ') from Vendor_master

--nullif use ( determine if two parameters are equal , return null if equal , otherwise the first argument is returned )

Select Nullif from dual

-- Use the rollup clause to return a subtotal for each grouping and return totals for all groups:

Select Division_id,sum (Salary), COUNT (*) from Employees2

Group by division_id

To Delete a duplicate record in a table using an Oracle Pseudo-column:

Delete from TestTable t where t.rowid!= (select Max (t1.rowid) from testtable t1 where T1.name=t.name)

Oracle Paging Code:

SELECT * FROM (select RowNum r,o.* from Order_master o where rownum <=) where r>9

"Oracle" chapter II Common operations