[Oracle] Chapter 2 common operations, Chapter 2 of oracle

[Oracle] Chapter 2 common operations, Chapter 2 of oracle

Chapter 2 common ORACLE operations

Modify the column information in the table:

Alter table vendor_master modify (vencode varchar (20 ));

Add a default value constraint to the column in the table

Alter table mytable modify (name varchar2 (20) default 'abc ');

Add a primary key constraint to columns in an existing table:

Alter table testtable add (constraint "idkey" primary key ("ID "));

Add a column to an existing table:

Alter table vendor_master add (vid number (5 ));

Delete a column from an existing table:

Alter table vendor_master drop column vid;

Delete A table:

Drop table vendor_master;

Modify column Name:

Alter table mytable rename column name to newName

-- Unio returns all the rows that are not repeated in two queries. Use of unio

Select vencode from vendor_master

Union select vencode from order_master

-- Unio all: returns all rows selected by two queries, including duplicate rows.

Select vencode from vendor_master

Union all select vencode from order_master

-- Intersect returns the rows of both queries. Use of intersect

Select vencode from vendor_master

Intersect select vencode from order_master

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

-- Query the selected row, that is, the row exists in the second query result in the first query result.

Select vencode from vendor_master

Minus

Select vencode from order_master

--- | Use of Connector

Select ('vendor '| venname |' address: '| venadd1 | ''| venadd2 |'' | venadd3) as address from vendor_master

-- Use the DATE keyword to provide a date text string to the database. This DATE is required. The format yyyy-mm-dd must be used. The following Code demonstrates the date keyword;

Insert into MYTABLE (ID, NAME, ADDRESS, EMAIL, REGDATE)

Values (6, 'Rose ', 'tianjin', 'Rose @ sdi.com', date '2017-10-12 ')

-- The following code demonstrates adding the value returned by the to_date () function to the database.

Insert into MYTABLE

(ID, NAME, ADDRESS, EMAIL, REGDATE)
Values (6, 'Rose ', 'tianjin', 'Rose @ sdi.com', to_date ('2017-10-23 12:36:58 ', 'yyyy-mm-dd hh24: mi: ss '))

-- Add_month

Select del_date, add_months (del_date, 2) from order_master

 

-- Months_between: use this function to return the number of months between two dates. The format is MONTHS_BETWEEN (d1, d2)

Select to_date ('1970-11-30 ', 'yyyy-mm-dd') from dual

Select months_between (to_date ('1970-12-31 ', 'yyyy-mm-dd'), to_date ('1970-12-20', 'yyyy-mm-dd') from dual

 

-- Last_day is used to return the date value of the last day of the specified date month. The format is LAST_DAY (d)

Select sysdate, last_day (sysdate) from dual

 

-- ROUND: The format is ROUND (d, [fmt]), d is the date, and fmt is the format model.

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

-- If the format is "YEAR", it is rounded to the beginning of the YEAR, that is, January 1, January 1. If the format is "MONTH", it is rounded to the first day of the MONTH; if the format is week "DAY", it is rounded to the closest Sunday.

Select round (to_date ('2017-8-13 ', 'yyyy-mm-dd hh24: mi: ss'), 'day') as m from dual

 

-- NEXT_DAY, which returns the specified date of the next week. The format is

-- NEXT_DAY (d, day), d indicates the date, and day indicates any day of the week.

Select next_day (sysdate, 'Friday') from dual

-- Trunc function truncates a specified date from the date specified by the format model,

-- Unlike the ROUND function

Select trunc (to_date ('1970-12-12 ', 'yyyy-mm-dd'), 'day') from dual

-- Function extraction specific part of the date and time type, in the format

-- EXTRACT (fmt FROM d)

Select extract (year from sysdate) from dual

-- Returns the corresponding characters based on the ASCII code.

Select chr (97) from dual

-- The LPAD function consists of three parameters. The first parameter is a string,

-- Fill the left side of the string, and the second side is a number,

-- It indicates the total length of the returned value, and the third is a string,

-- Fill left with it when needed.

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

-- The TRIM function crops specified characters from the start or end of a string. spaces are cropped by default.

-- Leading indicates from left to right, and trailing indicates from right to left

-- When neither leading nor trailing is specified, specific characters before and after the cropping are specified.

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 ('abc') from dual

-- Decode function to replace values one by one

-- Select decode (expr, search1, result1, search2, result2... [default])

Select decode (2, 1, 'identical ') from dual

-- To_char: String Conversion 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 ('1970-10-10 13:12:12 ', 'yyyy-mm-dd hh24: mi: ss') from dual

-- Converts a null value to a specified value. NVL (expression1, expression2)

Select vencode, venname, nvl (venadd1, 'no') from vendor_master

-- Used by NVL2 (if the first parameter is not null, the second parameter is returned; otherwise, the third parameter is returned)

Select vencode, venname, nvl2 (venadd1, 'beijing', 'no address') from vendor_master

-- NULLIF is used to determine whether two parameters are equal. If the two parameters are equal, null is returned. Otherwise, the first parameter is returned)

Select nullif (1, 2) from dual

-- Use the rollup clause to return a subtotal for each group and return a total for all groups:

Select division_id, sum (salary), count (*) from employees2

Group by division_id

Use the oracle pseudo-column to delete duplicate records in the table:

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 <= 12) where r> 9