基本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>
