Oracle-别名、关键字、排序、函数

员工表：

CREATE TABLE "EMP" (  
	 "EMPNO" NUMBER(4,0) PRIMARY KEY, 
	 "ENAME" VARCHAR2(10BYTE),
	 "JOB" VARCHAR2(9BYTE),
	 "MGR" NUMBER(4,0),
     "HIREDATE" DATE, 
     "SAL" NUMBER(7,2), 
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0)
);

INSERT INTO "EMP" VALUES ('7369', 'SMITH', 'CLERK', '7902', TO_DATE('1980-12-17 00:00:00','YYYY-MM-DD HH24:MI:SS'), '800', null, '20');
INSERT INTO "EMP" VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', TO_DATE('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1600', '300', '30');
INSERT INTO "EMP" VALUES ('7521', 'WARD', 'SALESMAN', '7698', TO_DATE('1981-02-22 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1250', '500', '30');
INSERT INTO "EMP" VALUES ('7566', 'JONES', 'MANAGER', '7839', TO_DATE('1981-04-02 00:00:00','YYYY-MM-DD HH24:MI:SS'), '2975', null, '20');
INSERT INTO "EMP" VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', TO_DATE('1981-09-28 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1250', '1400', '30');
INSERT INTO "EMP" VALUES ('7698', 'BLAKE', 'MANAGER', '7839', TO_DATE('1981-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), '2850', null, '30');
INSERT INTO "EMP" VALUES ('7782', 'CLARK', 'MANAGER', '7839', TO_DATE('1981-06-09 00:00:00','YYYY-MM-DD HH24:MI:SS'), '2450', null, '10');
INSERT INTO "EMP" VALUES ('7788', 'SCOTT', 'ANALYST', '7566', TO_DATE('1987-04-19 00:00:00','YYYY-MM-DD HH24:MI:SS'), '3000', null, '20');
INSERT INTO "EMP" VALUES ('7839', 'KING', 'PRESIDENT', null, TO_DATE('1981-11-17 00:00:00','YYYY-MM-DD HH24:MI:SS'), '5000', null, '10');
INSERT INTO "EMP" VALUES ('7844', 'TURNER', 'SALESMAN', '7698', TO_DATE('1981-09-08 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1500', '0', '30');
INSERT INTO "EMP" VALUES ('7876', 'ADAMS', 'CLERK', '7788', TO_DATE('1987-05-23 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1100', null, '20');
INSERT INTO "EMP" VALUES ('7900', 'JAMES', 'CLERK', '7698', TO_DATE('1981-12-03 00:00:00','YYYY-MM-DD HH24:MI:SS'), '950', null, '30');
INSERT INTO "EMP" VALUES ('7902', 'FORD', 'ANALYST', '7566', TO_DATE('1981-12-03 00:00:00','YYYY-MM-DD HH24:MI:SS'), '3000', null, '20');
INSERT INTO "EMP" VALUES ('7934', 'MILLER', 'CLERK', '7782', TO_DATE('1982-01-23 00:00:00','YYYY-MM-DD HH24:MI:SS'), '1300', null, '10');
INSERT INTO "EMP" VALUES ('1111', '张三', '项目经理', '7839', TO_DATE('2019-01-12 00:00:00','YYYY-MM-DD HH24:MI:SS'), '600', '500', null);
COMMIT;

部门表：

CREATE TABLE "DEPT" (
	"DEPTNO" NUMBER(2,0) PRIMARY KEY, 
	"DNAME" VARCHAR2(14BYTE), 
	"LOC" VARCHAR2(13BYTE)
);

INSERT INTO "DEPT" VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO "DEPT" VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO "DEPT" VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO "DEPT" VALUES ('40', 'OPERATIONS', 'BOSTON');
COMMIT;

1.select

①在sql语句中用数学表达式：

SELECT ename, sal, sal+300 FROM emp;
 
SELECT ename, sal, 12*(sal+300) FROM emp;

②.列的别名

a.用AS,或者空格

  SELECT　ename AS name, sal salary FROM emp;

查询结果：

注意：默认是大写，如果想要小写，就要用“”
b.双引号

SELECT ename "Name" ,sal*12 "全年工资" FROM emp;

查询结果：

c.并置连接符

SELECT '亲爱的' ||ename ,',薪水是：' ||sal FROM emp ;

d.去重：

SELECT distinct deptno FROM emp;

2.where

①.字符串、日期应该用单引号；
②.字符串值大小写敏感。
③缺省日期格式：日日-月月-年年
23-JAN-19 就是 2019年1月23日

3.比较

①.between and

SELECT ename， sal FROM emp WHERE sal BETWEEN 1000 AND 2000；

②.in

SELECT ename, sal ,mgr FROM emp WHERE mgr IN (7698,7789,7566);

③.like
%:0个或多个字符
_:一个字符

SELECT * FROM emp WHERE ename LIKE '_I%';

特殊：如果ename是‘张_三’，语句应该写成：

SELECT * FROM emp WHERE ename LIKE '%\_%' escape '\';

4.逻辑

and、or、not
and的优先级高于or：会先匹配and，在匹配or

5.排序order by

asc升序【默认】，desc降序
①.按照列名：
SELECT ename, job FROM emp ORDER BY hiredate DESC;

②.按照列的别名：
SELECT ename， job， sal*12 annsal FROM emp ORDER BY annsal；

③.多列名排序：
SELECT ename, job, sal FROM emp ORDER BY deptno, sal DESC;

6.函数

①.大小写转化函数：

函数	结果	说明
LOWER(‘SQL Course’)	sql course	全小写
UPPER(‘SQL Course’)	SQL COURSE	全大写
INITCAP(‘SQL Course’)	Sql Course	驼峰格式

SELECT ename, job, sal FROM emp WHERE ename = UPPER('blank');

②.操作字符串

函数	结果	说明
CONCAT(‘Good’,‘String’)	GoodString	连接
SUBSTR(‘String’,1,3)	Str	截取
LENGTH(‘String’)	6	求长度
INSTR(‘String’, ‘r’)	3	指定字符在字符串中的位置
LPAD(sal, 10 ,’*’)	******5000	用*补全10位
TRIM( ‘x’ from ‘xday’)	day	从后面的去掉前面的

③数学函数

ROUND(四舍五入)

函数	结果
ROUND(45.923,2)	45.92
ROUND(45.923,0)	46
ROUND(45.923,-1)	50

TRUNC(舍去)

函数	结果
trunc(45.923,2)	45.92
trunc(45.923)	45
trunc(45.923,-1)	40

MOD(取余数)

MOD(2,3) 结果：2

④日期函数

函数	结果	说明
sysdate	15-MAY-19	系统时间
months_between(‘01-SEP-95’,‘11-SEP-94’)	19.6774194	两个日期之间相差多少个月
add_months(‘11-SEP-19’,6)	11-MAR-20	2019年9月11日加上6个月为2020年5月11日
next_day(‘14-MAY-19’,‘FRIDAY’)	17-MAY-19	2019年5月14日的下一个周五是2019年5月17日
last_day(‘14-MAY-19’)	31-MAY-19	2019年5月14日的这个月最后一天是5月31日
round(to_date(‘2019-5-14’,‘yyyy-mm-dd’),‘month’)	01-JUN-14	2019-5-14按月四舍五入为2019-6-1
round(to_date(‘2019-5-14’,‘yyyy-mm-dd’),‘year’)	01-JAN-19	2019-5-14按年四舍五入为2019-1-1
trunc（to_date(‘2019-5-14’,‘yyyy-mm-dd’),‘month’）	01-MAY-19	2019-5-14按月舍为2019-5-1
trunc（to_date(‘2019-5-14’,‘yyyy-mm-dd’),‘year’）	01-JAN-19	2019-5-14按年舍为2019-1-1

⑤数字格式转化

select ename, to_char(sal,'L99,999.00') from emp;

结果：TOM $3,000.00

⑥nvl函数

NVL(expr1,expr2) 如果expr1为null，返回expr2，否则返回expr1

⑦decode函数

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)