--create table test as select * from T1 where 1=2;
--Copy table structure, not data
--select * from test;
--create table test2 as select * from T1;
--Copy the entire table to test2
--select * from test2;
--insert into test select * from T1;
--Insert T1 data into the test table;
--1、 Date Function
--Sysdate is system date, dual is virtual table
--2. last_day [Return the date of the last day of the month]
--select last_day(sysdate) from dual;
--3. months_between [return the number of months between dates]
--4. next_day (d, day): returns the date of the next week. Day is 1-7 or Sunday Saturday. 1 means Sunday
--5.,round Rounded to the nearest date
--6. truncate [Truncate to the nearest date]
--7. The latest date in the return date list
--Select greatest ('01-Jan-04 ',' 04-Jan-04 ',' 10-Feb-04 ') from dual;
--2、 Character function
--1. String interception
--select substr('abcdef',1,3)from dual;
--2. Find the substring position
--select instr('avcsab','ab')from dual;
--3. String connection
--select 'hi'||'hello world' from dual;
--4. Remove the space in the string,
--LTRIM cancels the preceding characters, RTRIM removes the following characters, and TRIM removes the preceding and following characters
--select ltrim(' abc')s1,rtrim('we ')s2,trim(' abc ')s3 from dual;
--5. Remove the leading and suffix
--Leading removes the leading and trail removes the suffix
--6. Return the Ascii value of the first letter of the string
--select ascii('a')from dual;
--7. Return the letter corresponding to the ascii value
--select chr(98)from dual;
--8. initcap, lower, upper
--9.Replace
--10.translate
--11. lpad [left recharging] rpad Right Fill
--12. decode [Implement if.. Then logic]
--Select deptno, decode (deptno, 10, '1', 20, '2', 30, '3', 'others') from dual;
--3、 Digital function
--1. Rounding function (ceil rounded up, floor rounded down)
--select ceil(66.23) N1,floor(66.56) N2 from dual;
--2. Power and square root
--select power(4,2) N1,sqrt(9) N2 from dual;
--3. Remaining
--select mod(9,7) from dual;
--4. Return fixed decimal places (round: round, truncate: directly truncate)
--select round(666.667,2)N1,trunc(666.667,2)N2 from dual;
--5. Sign The sign of the returned value (positive number is returned as 1, negative number is returned as - 1)
--select sign(-32),sign(23) from dual;
--4、 Conversion function
--1. to_char() [convert date and number types to character types]
--2. To_date() [convert character type to date type]
--3. to_number() is converted to numeric type
--select to_number(to_char(sysdate,'hh24')) from dual;
--5、 Other functions
--1. User returns the current login name
--select user from dual;
--2.vsize； Returns the number of bytes required by the expression
--select vsize('hello')from dual;
--3.nvl(ex1,ex2):
--If ex1 is empty, ex2 is returned; otherwise, ex1 (common) is returned
--4. nullif (ex1, ex2): if the value is equal, return null; otherwise, return the first value
--5. coalesce: return the first non empty expression in the list
--6. nvl2 (ex1, ex2, ex3) If ex1 is not empty, ex2 is displayed; otherwise, ex3 is displayed
--6、 Grouping function
--max min avg count sum
--1. The whole result set is a group
--2. Grouping with group by and having
--3. tddev returns the standard deviation of a set of values, and variance returns the variance difference of a set of values
--4. Group By with rollback and cube operators