基本SQL语句练习之SELECT

一、SQL Plus连接
sqlplus:以命令行方式连接数据库
sqlplusw：以窗口登录方式连接数据库
conn sys/password as sysdba;
show user
select * from tab; 查看当前用户连接的实例的所有表
desc emp; 查看emp表的结构
DESC降序 ASC升序

二、基本SQL语句
--1.SELECT * FROM EMP

--2.SELECT ENAME,JOB,SAL FROM EMP

--3.SELECT ENAME 姓名,JOB 工作, SAL 工资 FROM EMP

--4.SELECT DISTINCT JOB FROM EMP

--5.SELECT ' THE EMPLOYEES NO: '|| EMPNO || ' NAME IS ' || ENAME || ' AND HIS SALARY IS: '||SAL FROM EMP;

--6.ELECT ENAME, SAL*12 FROM EMP;

--7.SELECT * FROM EMP WHERE SAL > 1500

--8.SELECT * FROM EMP WHERE COMM IS NOT NULL

--9.SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000

--10.SELECT * FROM EMP WHERE ENAME='SCOTT'

--11.SELECT * FROM EMP WHERE ENAME='SMITH' OR ENAME='WARD' OR ENAME='FORD'

--12.SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD')

--13.SELECT * FROM EMP WHERE ENAME NOT IN ('SMITH', 'WARD')

--14.SELECT * FROM EMP WHERE ENAME LIKE '%AM%'

--1S.SELECT * FROM EMP WHERE ENAME LIKE '%AM_S'

--16.SELECT * FROM EMP WHERE ENAME <> 'SCOTT'

--17.SELECT * FROM EMP WHERE ENAME !='SCOTT'

--18.SELECT * FROM EMP ORDER BY SAL

--19.SELECT * FROM EMP ORDER BY SAL DESC

--20.SELECT * FROM EMP WHERE DEPTNO=10 ORDER BY SAL DESC, HIREDATE ASC

--21.SELECT UPPER('rusky') FROM DUAL --字符串改大写

--22.SELECT LOWER('RUSKY') FROM DUAL --字符串改小写

--23.SELECT INITCAP ('rusky') FROM DUAL --首字母大写

--24.SELECT INITCAP(ENAME) FROM EMP

--25.SELECT CONCAT('HELLO ','RUSKY') FROM DUAL

--26.SELECT SUBSTR('rusky',1,3) FROM DUAL --oracle数据库从0开始和从1开始效果一样

--27.SELECT SUBSTR('rusky',0,3) FROM DUAL --从左边第1个字符开始，向右截取3个字符

--28.SELECT LENGTH('rusky') FROM DUAL --长度

--29.SELECT REPLACE('rusky','u','x') FROM DUAL --以x替换字符串中的u字符

--30.ROUND()四会五入 TRUNC()截断小数位 MOD()取模

--31.SELECT ROUND(195.6) FROM DUAL

--32.SELECT ROUND(195.46,1) FROM DUAL

--33.SELECT ROUND(145.467,-1) FROM DUAL --对整数部分个位进行四舍五入，结果150

--34.SELECT ROUND(145.567,-2) FROM DUAL --对整数部分十位进行四舍五入，结果100

--35.SELECT ROUND(145.567,-3) FROM DUAL --对整数部分百位进行四舍五入，结果0

--36.SELECT TRUNC(125.92) FROM DUAL --取整数，结果125

--37.SELECT TRUNC(125.98,1) FROM DUAL --指定小数点保留位数，不会四舍五入，结果125.9

--38.SELECT TRUNC(125.983,-1) FROM DUAL --结果120

--39.SELECT MOD(10,3) FROM DUAL;

--40.SELECT SYSDATE FROM DUAL; --2014/6/5 15:27:04

--41.SELECT ENAME, ROUND((SYSDATE-HIREDATE)/7) FROM EMP --求星期数

--42.SELECT SYSDATE+5 FROM DUAL

--43.SELECT SYSDATE-100 FROM DUAL

--44.SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP --给定日期范围内的月数

--45.SELECT ADD_MONTHS(SYSDATE,10) FROM DUAL --在指定日期上加上指定月数，求出之后的日期

/*46.NEXT_DAY下一次给定的日期数
指定时间的下一个星期几（由char指定）所在的日期，
char也可用1～7替代，1表示星期日，2代表星期一。。。。
还可以是星期一、星期二。。。星期日或是MONDAY TUESDAY WENSDAY THIRSDAY FRIDAY SATAUDAY SUNDAY*/
--SELECT NEXT_DAY(SYSDATE,5) FROM DUAL
--SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM DUAL
--SELECT NEXT_DAY(SYSDATE,'星期一'）FROM DUAL

--47.SELECT LAST_DAY(SYSDATE) FROM DUAL --返回某个时间值当月最后一天

--48.SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL
--SELECT TO_CHAR(SYSDATE,'YYYY')FROM DUAL
--SELECT TO_CHAR(SAL,'99,999') FROM EMP
--SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM DUAL

--49.SELECT TO_NUMBER('123')+TO_NUMBER('456') FROM DUAL

--50.SELECT TO_DATE('2014-06-05','YYYY-MM-DD') FROM DUAL

--51.SELECT ENAME,SAL,COMM,SAL+COMM SUM FROM EMP;

--52.SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) SUM FROM EMP --NVL(COMM,0)指定，如果comm值为null时，则取0

--53.SELECT EMPNO,ENAME,HIREDATE, DECODE(JOB,'CLERK','A','SALESMAN','B','MANAGER','C','ANALYST','D','PRESIDENT','E') FROM EMP

============replace,translate,concat,lpad,rpad,substr,instr===============================

SQL> SELECT REPLACE('abcdefg','abcd','1234') from dual;

REPLACE
-------
1234efg

SQL> select translate('abcdefgabc','abc','123') from dual;

TRANSLATE(
----------
123defg123

SQL> select translate('abcdefgahca','abc','123') from dual;

TRANSLATE('
-----------
123defg1h31

SQL> select replace('abc','a','') from dual;

RE
--
bc

SQL> select replace('abc','','ef') from dual;

REP
---
abc

SQL> select replace('abd','','') from dual;

REP
---
abd

SQL>

SQL> SELECT CONCAT('HELLO',' WORLD') FROM DUAL;

CONCAT('HEL
-----------
HELLO WORLD

SQL> SELECT SUBSTR('HelloWorld',1,5) FROM DUAL;

SUBST
-----
Hello

SQL> SELECT INSTR('HelloWorld','W') from dual;

INSTR('HELLOWORLD','W')
-----------------------
6

SQL> SELECT LPAD(sal,10,'*') from emp;

LPAD(SAL,10,'*')
--------------------
*******800
******1600
******1250
******2975
******1250
******2850
******2450
******3000
******5000
******1500
******1100
*******950
******3000
******1300

已选择14行。

SQL> SELECT RPAD(sal,10,'*') FROM EMP;

RPAD(SAL,10,'*')
--------------------
800*******
1600******
1250******
2975******
1250******
2850******
2450******
3000******
5000******
1500******
1100******
950*******
3000******
1300******

已选择14行。

trim一般都是用在删除字符串两边的空格。实际上，trim也可以用来删除字符串两边的指定字符。并且trim指定删除的字符串只能是单个字符。如 trim('字符1' from '字符串2') ，字符1只能是单个字符。
1. trim()删除字符串两边的空格。
2. ltrim()删除字符串左边的空格。
3. rtrim()删除字符串右边的空格。
4. trim('字符1' from '字符串2')  分别从字符2串的两边开始，删除指定的字符1。
5. trim([leading | trailing | both] trim_char from string)　从字符串String中删除指定的字符trim_char。
  leading:从字符串的头开始删除。
  trailing:从字符串的尾部开始删除。
  borth:从字符串的两边删除。
6. tim()只能删除半角空格。

SQL> select trim(leading '1' from '1231') from dual;

TRI
---
231

SQL> select trim(trailing '1' from '1231') from dual;

TRI
---
123

SQL> select trim(both '1
  2
SQL> select trim(both '1' from '1231') from dual;

TR
--
23

SQL> select trim('     123    ') || 'rusky' from dual;

TRIM('12
--------
123rusky

SQL> select ltrim('    123    ')|| 'rusky' from dual;

LTRIM('123')
------------
123    rusky

SQL> select rtrim('    123   ') || 'rusky' from dual;

RTRIM('123')
------------
    123rusky
SQL> select trim('字符1' from '字符串2') from dual;
select trim('字符1' from '字符串2') from dual
       *
第 1 行出现错误:
ORA-30001: 截取集仅能有一个字符


SQL> select trim('字' from '字符串2') from dual;

TRIM(
-----
符串2

SQL>