The basic operation of Oracle database on tables and some common functions

First, create an employee table with the following SQL statement:

CREATE TABLE employee (  ID Number (4),  name VARCHAR2 () not NULL,  gender CHAR (1) DEFAULT ' M ',  birth DATE,  salary Number (6,2),  comn number (6,2),  job VARCHAR2 (+),  Manager number (4),  deptno number (2))

Here are some basic operations on this table:

RENAME employee to EMP//change table name to EMP

ALTER TABLE Employee Add hiredate Date default sysdate//Increment column for employee, default value is current date

ALTER TABLE Employee MODIFY (Job VARCHAR2 (max) default ' clerk ')//Modify the Job column length to 40 and set the default value

ALTER TABLE employee DROP (manager)/delete Manager column

INSERT into employee (id,name,job,salary) VALUES (1001, ' Jack ', ' PROGRAMMER ', 5500)//Insert data into table

INSERT into employee (Id,name,job,birth) VALUES (1002, ' LARRY ', ' ANALYST ', to_date (' 1994-10-22 ', ' yyyy-mm-dd '))// Insert a record using a custom date format, the default format is ' 22-sep-94 '

Update employee SET salary=10 WHERE name= ' Larry '//update Larry's salary, note that if the letter is uppercase when inserting the data, you should also capitalize the change

Delete from the employee WHERE birth is null//delete record of the birthday null

Char has a maximum value of 2000 bytes, it can not specify a length, the default is 1,VARCHAR2 maximum value is 4000 bytes, it must specify the length.

String functions:

Concat and | | : Returns the result of a two string concatenated. Select CONCAT (' A ', ' B ') from dual Result:ab select ' Lo ' | | ' ve ' from dual Result:love

Length: Returns the lengths of the strings, if VARCHAR2 returns the actual length of the string, if the character type is char, the length also includes the trailing space.

UPPER: Converts characters to uppercase.

LOWER: Converts the character to lowercase.

Intcap: Capitalize the first letter of each word, separating the words with spaces and non-alphabetic characters.

Trim:select TRIM (' e ' from ' elite ') from dual//cut ' e ' from the front and back of the elite, and if you do not write E, remove the spaces at both ends.

Ltrim:select LTRIM (' elite ', ' e ') from dual//cut ' e ' from the left of elite, and if you do not write E, remove the space at the right end.

Rtrim:select LTRIM (' elite ', ' e ') from dual//cut ' e ' from the right side of elite, and if you do not write E, remove the space at the left.

Lpad:select Lpad (' Three ', 5, ' $# ') from dual//put ' 12 ' with ' $# ' on the left to fill 5 bits.

Rpad:select Lpad (' Three ', 5, ' $# ') from dual//put ' 12 ' with ' $# ' on the right to fill 5 bits.

Substr:select SUBSTR (' ABCDEFG ', -2,2) from dual//The second parameter equals 0 or 1 is taken from the first character, if the second argument is negative, then ' G ' is-1, ' F ' is-2, and so on, Then take the third parameter length character to the right. If the third argument is not set or the length exceeds the length of the character, it is taken to the end of the string.

SELECT INSTR (' Thinking in Java ', ' I ', 4,2) from dual//returns the position of ' I ' in the first argument, the meaning of the third parameter is retrieved from the fourth character (' N '), and the meaning of 2 means ' I ' is the second occurrence of the position.

Null-valued Functions:

NVL (Expr1, EXPR2): If EXPR1 is null, the value EXPR2.

NVL2 (Expr1, Expr2, EXPR3): NVL2 is used to determine if EXPR1 is null, if not NULL, returns EXPR2, if NULL, returns EXPR3.

Date Conversion Functions:

To_date: Converts a string to a date type in a custom format. For Example:to_date (' 2002-01-01 ', ' yyyy-mm-dd ')

To_char: Converts data from other types (date, numeric) to character types, primarily on date types. For Example:to_char (hiredate, ' YYYY ' year "MM" month "DD" Day ")

Commonly used date formats such as:

Date Common functions:

Last--day (date): Returns the last day of the month in which the given date is located.

Add_months (Date,i): Returns the date value after the given date plus I month, and if I is a negative number, the date value minus I months is obtained.

Month_between (Date1, Date2): Calculates the number of months between date1-date2 two date values, and if date2 time is later than date1, a negative value is obtained. Unless the two date interval is an integer month, you get a result with a decimal place, such as calculating how many months between September 1, 2009 and October 10, 2009, you get 1.29 months.

Next_day (date, char): Returns the next week of the data for the given date date, and the weeks are determined by the parameter char. In the Chinese environment, the use of "Wednesday" in the form of direct, English environment, the need to use the "Wednesday" this English week. To avoid trouble, the number 1-7 can be used directly to denote Sunday-Saturday.

Greatest (expr1[, expr2[, EXPR3] ...)  LEAST (expr1[, expr2[, EXPR3] ...) Two functions can have more than one parameter value, but the parameter type must be the same, the return result is the largest or smallest value in the parameter list, before the comparison, the second argument in the parameter list is implicitly converted to the data type of the first parameter, so if you can convert, then continue to compare, if not the conversion will be an error.

The basic operation of Oracle database on tables and some common functions