Shulou(Shulou.com)06/01 Report--

Data manipulation

Mainly insert (insert), modify (update), delete (delete) operation to the table or view.

Grouping statistical query

Points to note when using aggregate functions:

1.count (*) counts all records. The column does not count when the count (field name) field value is null.

two。 The occurrence order of aggregate functions

3. Statistics of non-duplicated line information distinct

[* character function *]

1. String concatenation: concat (ch2,ch3)

Returns the string ch2 concatenated with the string ch3

Such as:

Select concat ('hello', 'Worldwide') From dual; / / hello world!

Or

Select 'hello' | | 'Worldwide' From dual; / / hello world!

two。 The first letter of a string is capitalized

Initcap (char): capitalize the first letter of each word in the string char and lowercase the other letters

For example, select initcap ('hello,world') from dual; / / Hello,World can be separated by any separator

3. Character index position: instr (ch2,ch3, [m [, n]])

Returns the position of the specified string ch3 in the string ch2, where m starts the search, and n represents the number of times ch3 appears in ch2

Such as:

Select * from emp where instr (ename,'C') > 0; / / start with 1

Select * from emp where instr (ename,'c') = 2; / / query the employees whose ename subscript 2 is'C'.

4. Calculate the length of the string: length (char)

5. Case conversion of characters:

Lower (char): converts the string char to lowercase

Upper (char): converts the string char to uppercase

6. Replacement string: replace (ch2,ch3,ch4)

Replace the string ch3 in string ch2 with the string ch4

For example: select replace (sal,sal,'*') from emp; / / replace the salary of the employee in the emp table with *

7. Intercept string: substr (ch, starting position, how many bits to intercept)

For example: select substr ('hello world',6,5) from dual; / / world

Note: the subscript starts with 1.

8. Remove the space in the string

Trim (char): remove spaces on both sides

Ltrim (char, [ch]): remove the left space of the string or remove the string containing ch on the left

Select ltrim ('abcdef','abc') from dual;-- > def, remove the letters on the left side of char that contain abc

Rtrim (char, [ch]): remove the right space of the string or the string containing ch on the right

Select rtrim ('abcdef','abc') from dual;-- > abc, remove the letters on the left side of char that contain def

9.instr (char1,char2, [m [, n]]): returns the position of char2 in char1, m represents the starting index position, and n represents the number of times cha2 appears in char1

Select instr ('abcde','d') from dual; / / the subscript starts at 1, returns the position of the string d, and returns 4

Instr (char,char,n) [search one string for another, n > 0 front-to-back, you 3

Select instr ('abcabc','c',-1) from dual;-- > 6

10.chr (n): returns a character with an ASCII code value of n

Select CHR ('65') from dual; / / A

ASCII (char): returns the ASCII code of the specified character

Select ascii ('A') from dual;-- > 65-returns the ASCII code of the A character

11.lpad (char1,n [, char2]): if the length of char1 is greater than n, then return n characters to the left of char1, if n is greater than the length of char1, use

Char2 fills the left side of char1 to make it length n

Select lpad ('abc',2,'dd') from dual; / / ab-- if char1 is less than the length of n, directly output n corresponds to the characters in char1

Select lpad ('abc',5,'dd') from dual; / / ddabc

12.RPad (char1,n [, char2]): add char2 to the right of the char1 to make the length of the char1 n. If n is less than the length of the char1, truncate the rest.

Select rpad ('abc',5,'a') from dual;-- > abcaa

Select rpad ('abc',2) from dual;-- > ab

Select length (rpad ('abc',5)) from dual;-- > 5 [2 extra spaces on the right]

13.Translate (char1,form,to): replace form with to, and then replace matching content in char1 with form

Select translate ('abc','ab','a') from dual;--- > ac

[date function]

1.dbtimezone: returns the time zone where the database is located

Select dbtimezone from dual

2.extract (depart from date): get the content of the corresponding part of depart from the date date. The values of depart can be as follows:

Year,month,day,hour,minute,second,timezone_hour,timezone_minute

Timezone_region,timezone_abbr

Select extract (year from sysdate) from dual; / / 2016

3.add_months (dline n): returns the date and time corresponding to the addition of n months. A positive number indicates the date after d, and a negative number indicates a date before d.

Select add_months (sysdate,2) from dual; / / Today is 2016-7-31, plus two months, that's 2016-9-30.

Select add_months (sysdate,-1) from dual; / / Today is 2016-8-1, and-1 represents the previous month, that is, 2016-7-1

4.next_day (date, day of the week):

Parameter description:

What day of the week: available, Sunday-Saturday

You can use 1-7 for Sunday.

Select next_day (sysdate,' Monday') from dual

Select next_day (sysdate,' Tuesday') from dual

Select next_day (sysdate,' Wednesday') from dual

Select next_day (sysdate,' Thursday') from dual

Select next_day (sysdate,' Friday') from dual

Select next_day (sysdate,' Saturday') from dual

Select next_day (sysdate,' Sunday') from dual

Select next_day (sysdate,1) from dual

Select next_day (sysdate,2) from dual

Select next_day (sysdate,3) from dual

Select next_day (sysdate,4) from dual

Select next_day (sysdate,5) from dual

Select next_day (sysdate,6) from dual

Select next_day (sysdate,7) from dual

5.last_day (d): returns the last day of the month where d is

Select last_day (sysdate) from dual; / / get the last day of the current month

6.trunc (d, [fmt]): returns truncated date-time data

Select trunc (sysdate,'yy') from dual; / / returns the first day of the current year

Select trunc (sysdate,'mm') from dual; / / returns the first day of the current month

7.months_between (D1 and D2): returns the number of months between D1 and D2

Select abs (months_between (sysdate,add_months (sysdate,3) from dual

If today is 2016-8-1, plus 3 months, it becomes 2016-11-1, so the difference is 3 months.

8.round (d [, fmt]): returns the round result of date d

Select round (sysdate) from dual; / / for example, today is 2016-8-1, it is half way past today, and it has been 2016-8-2 for so long.

9.to_date (string format, date date format): converts a date in a string format to a date date in a specified format

Select to_date ('2016-8-1) from dual

10.to_char (date,' string format'): converts a date in date format to a string date in a specified format

Select to_char (sysdate,'yyyy-mm-dd') from dual

11.systimestamp: returns the system date and time of type timesamp with time zone

Select to_char (systimestamp,'yyyy-mm-dd hh34:mi:ssxff6') from dual

Note: 6 in xff6 indicates how many bits are reserved

12.soundex (char): used to compare strings with the same pronunciation

[numeric function]

1.trunc (m [, n]): intercept m without rounding

Select trunc (108.123) from dual; / /-- > 108, when omitted, indicates the integer portion of the truncated value

Select trunc (108.123) from dual; / /-- > 108.12. When n > 0, it is truncated to the nth place to the right of the decimal point.

Select trunc (1082.123) from dual; / /-- > 1080, when n 0 returns 1 position, 0 returns 0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.