随记常用SQL（基于oracle）

夏莹然

1.Alter user scott account unlock; this is the command line to unlock the user.
2.Varchar2 support interionalization.
3.NUMBER<7, 2> 7 digits, 2 decimal digits.
4.Dual is the default table to do the calculation.
5.Sysdate is used to show the current date.
6.Double quote is used to keep the character in database.
7.Any null value in math statement will result null result.
8.|| is used to connect the string in database.
9.<> means not equal in database.
10.select ename, sal from emp where sal in (800, 1500);
11.data format could be seen as a string in database.
12.% means zero or more, _ means one.
13.\ is like a escape symbol in sql. Or you could define your escape symbol.
select * from emp where ename like '%$%%' escape '$';
14.Order by: select * from dept order by deptno desc, ename desc;
15.select lower(ename) from emp;
16.select ename from emp where lower(ename) like '_a%';
17.select substr(ename, 2, 3) from emp;
18.select round(23.643, 1) from dual;
19.select to_char(sal, '$99,999.9999') from emp; to define the format of data.
20.select to_char(sal, 'L00000.0000') from emp; will fill with 0;
21.select to_char(hiredate, 'YYYY-MM-DD HH(24):MI:SS') from emp;
22.select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:23:23', 'YYYY-MM-DD HH24:MI:SS');
23.select sal from emp where sal > to_number('$1,250.00','$9,999.99');
24.select sum(sal) from emp; count, min, max, ave, sum.
25.Select count(distinct deptno) from emp;
26.select to_char(avg(sal), '$99,999.9999') from emp group by deptno;
27.select max(sal), deptno from emp group by deptno;
28.in group by , something after select must be unique. The must be after group by or arragrate function.
29.select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
30.select avg(sal), deptno from emp where sal > 1500 group by deptno having avg(sal) > 2000;
31.select ename, sal from emp where sal = (select max(sal) from emp);
32.select ename, sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
33.rollback after DDL is no use.