第一章、简单查询
1、1简单查询语句
1、创建dept表、
emp表、
bonus表、
salgrade表
CREATE TABLE dept(
deptno NUMBER(2) CONSTRAINT PK_dept PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
SQL> CREATE TABLE emp(
2 empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
3 ename VARCHAR2(10),
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
10 );
SQL> CREATE TABLE bonus(
2 enamE VARCHAR2(10),
3 job VARCHAR2(9),
4 sal NUMBER(7,2),
5 comm NUMBER(7,2));
SQL> CREATE TABLE salgrade(
2 grade NUMBER,
3 losal NUMBER,
4 hisal NUMBER);
插入数据
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES(20,'RESENARCH','DALLAS');
INSERT INTO dept VALUES(30,'SALES','CHICAGO');
INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');
INSERT INTO emp VALUES(7369,'SMITH','CLERK',1902,to_date('12-12-1980','dd-mm-yyyy'),80,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-2-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES(7698,'BLANK','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',1839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);
语法1 简单查询语句语法
SELECT [DESTINCT] *|列名称 [AS] [列别名] ,
列名称 [AS] [列别名],.....FROM 表名称 [表别名];
(SELECT之后可以使用*将所有字段(列)的内容全部查询出来;如果数据列上面存在重复,则可以使用DISTINCT关键字取消重复元素)
范例:查询emp表中的数据
SELECT *FROM EMP;
在简单查询语句中,也可以使用各种四则运算“+、-、*、/”进行数学计算的操作
select empno,ename,sal*12,sal/30 from emp;
为查询结果设置别名
select empno 雇员编号,ename 雇员姓名,(sal+200)*12+500 as 年薪 from emp;
第二章、限定查询与排序显示
限定查询指的是在数据查询时设置一系列的过滤条件,只有满足制定条件后才可以进行显示。
查看emp表中的数据量
select count(*) from emp;
语法:限定查询
select [distinct] *| 列名称 [as] [列名称],
列名称 [as] [列名称],....
from 表名称 [表别名]
[where 条件(s)];
范例:统计基本工资高于1500元的全部雇员的信息
select * from emp where sal>1500;
1、常用限定运算符
NO | 运算符 | 符号 | 描述 |
1 | 关系运算符 | >,<,>=,<=,=,!=,<> | 进行大小或相等的比较,其中不等于有两种,!=或<> |
2 | 判断null | IS NULL,IS NOT NULL | 判断某一列的内容是否为null |
3 | 逻辑运算符 | AND,OR,NOT | AND表示多个条件必须同时满足,OR表示只需要有一个条件满足,NOT表示条件去反,即:真变假,假变真 |
4 | 范围查询 | BETWEEN最小值AND最大值,IN,NOT IN | 在一定范围内进行,查找结果为最小值=<内容<=最大值;IN为在指定数据范围内查找 |
5 | 模糊查询 | LIKE,NOT LIKE | 可以对指定的字段进行模糊查询。在LIKE语句中有两个通配符:百分号%、下划线_。百分号可以匹配任意类型和长度的字符,下划线匹配单个任意字符 |
2、对结果排序:ORDER BY
语法:对结果排序
SELECT [DISTINCT] *|列名称 [AS] 列别名 [AS] 列名称
FROM 表名称 表别名
[WHERE 条件(s)]
[ORDER BY 排序的字段 | 列索引序号 ASC|DES,
排序的字段2 | 列索引序号 ASC|DES .....]....;
(ASC 升序排序 ,默认排序;DES 降序排序)
第三章、单行函数
语法:单行函数语法:
funcation_name(列 | 表达式[参数1,参数2])
单行函数主要分为以下几种:
- 字符函数:接收数据返回具体字符信息
- 数值函数:对数字进行处理,例如四舍五入
- 日期函数:直接对日期进行相关的操作
- 转换函数:日期、字符、数字之间可以完成互相转换功能
- 通用函数:oracle自己提供的特色函数
3.1、字符函数
NO | 函数名称 | 描述 |
1 | UPPER(列 | 字符串) | 将字符串的内容全部转大写 |
2 | LOWER(列 | 字符串) | 将字符串的内容全部转小写 |
3 | INITCAP(列 | 字符串) | 将字符串的开头首字母大写 |
4 | REPLACE(列 | 字符串,新的字符串) | 使用新的字符串替换旧的字符串 |
5 | LENGTH(列 | 字符串) | 求出字符串的长度 |
6 | SUBSTR(列 | 字符串,开始点[,长度]) | 字符串截取 |
7 | ASCII(字符) | 返回与指定的字符对应的十进制数字 |
8 | CHR(数字) | 给出一个整数,并返回与之对应的字符 |
9 |
RPAD(列 | 字符串,长度,填充字符)
LPAD(列 | 字符串,长度,填充字符)
| 在右或左填充指定长度字符串 |
10 | LTRIM(字符串),RTRIM(字符串) | 去掉左、右空格 |
11 | TRIM(列 | 字符串) | 去掉左右空格 |
12 | INSTR(列 | 字符串,要查找的字符串,开始位置,出现位置) | 查找一个子字符串是否在指定位置上出现 |
3.2、数值函数
NO | 函数名称 | 描述 |
1 | ROUND(数字,[,保留位数]) | 对小数进行四舍五入,可以指定保留位数,如果不指定,则表示对小数之后数字全部进行四舍五入 |
2 | TRUNC(数字,[,截取位数]) | 保留指定位数的小数,如果不指定,则表示不保留小数 |
3 | MOD(数字,数字) | 取模 |
3.3、日期函数
范例:取得当前的系统时间(取得的数据只能是年、月、日、分、秒等数据)
SELECT SYSDATE FROM dual;
如果要精确到毫秒,则用select systimestamp from dual;
修改日期显示格式:ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
范例:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2016-11-07 15:39:26
除取得系统时间外,在oracle中也有日下三个日期操作公式:
日期 - 日期 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字(天数)
日期操作函数:
NO | 日期函数 | 描述 |
1 | ADD_MONTHS(日期,数字) | 指定的日期上加入指定的月数,求出新的日期(使用日期函数主要是为了考虑闰年的问题,如果用时期加减数字的方式则无法进行准确的日期操作) |
2 | MONTHS_BETWEEN(日期1,日期2) | 求出两个日期间的雇佣天数 |
3 | NEXT_DAY(日期,星期数) | 求出下一个星期X的具体天数 |
4 | LAST_DAY(日期) | 求出指定日期的最后一天日期 |
5 | EXTRACT(格式 FROM 数据) | 日期时间分割,或计算给定两个日期的间隔 |
范例:验证ADD_MONTHS()函数
SQL> SELECT SYSDATE,
2 ADD_MONTHS(SYSDATE,3) 三个月后的日期,
3 ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,
4 ADD_MONTHS(SYSDATE,60) 六十个月之后的日期
5 FROM dual;
SYSDATE 三个月后的日期 三个月之前的日期 六十个月之后的日期
------------------- -------------------
------------------- -------------------
------------------- --------
2016-11-07 15:52:34 2017-02-07 15:52:34 2016-08-07 15:52:34 2021-11-07 15:52:34
范例:验证NEXT_DAY()函数
SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期一') 下一个星期一,NEXT_DAY(SYSDATE,'星期日')下一个星期日 FROM dual;
SYSDATE 下一个星期一 下一个星期日
------------------- ------------------- -------------------
2016-11-07 16:13:43 2016-11-14 16:13:43 2016-11-13 16:13:43
范例:验证LAST_DAY()函数。使用LAST_DAY()函数可以求得指定日期所在月的最后一天日期,如果今天的日期是“2013年01月19日”,则使用LAST_DAY()函数求得的日期是“2013年01月31日”
SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;
SYSDATE LAST_DAY(SYSDATE)
------------------- -------------------
2016-11-07 16:18:03 2016-11-30 16:18:03
范例:验证EXTRACT()函数
此函数的主要功是可以从一个日期时间(DATE)或者是时间间隔(INTERVAL)中截取除特定的部分,此函数使用语法如下:
语法:EXTRACT()函数语法
EXTRACT([YEAR | MONTH | DAY | HOUR | MINUTE | SECOND]
| [TIMEZONE_HOUR | TIMEZONE_MINUTE]
| [TIMEZONE_REGION | TIMEZONE_ABBR]
FROM [ 日期 (date_value) | 时间间隔(interval_value) ])
范例:从日期时间中取出年、月、日数据
SQL> SELECT EXTRACT (YEAR FROM DATE'2001-09-19')years,
2 EXTRACT(MONTH FROM DATE'2001-09-19')months,
3 EXTRACT(DAY FROM DATE'2001-09-19')days
4 FROM dual;
YEARS MONTHS DAYS
---------- ---------- ----------
2001 9 19
范例:从时间戳中取得年、月、日、时、分、秒
SQL> SELECT EXTRACT(YEAR FROM SYSTIMESTAMP)years,
2 EXTRACT(MONTH FROM SYSTIMESTAMP)months,
3 EXTRACT(DAY FROM SYSTIMESTAMP)days,
4 EXTRACT(HOUR FROM SYSTIMESTAMP)hours,
5 EXTRACT(MINUTE FROM SYSTIMESTAMP)minutes,
6 EXTRACT(SECOND FROM SYSTIMESTAMP)seconds
7 FROM dual;
YEARS MONTHS DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ---------- ---------- ----------
2016 11 7 8 56 18.17
3.4转换函数
转换函数主要功能是将一个指定的数据类型变为另一种数据类型,常见的转换函数如下表:
NO | 函数名称 | 描述 |
1 | TO_CHAR(日期 | 数字 | 列 ,转换格式) | 将指定的数据按照指定的格式变为字符串型 |
2 | TO_DATE(字符串 | 列 ,转换格式) | 将指定的数据按照指定的格式变为DATE型 |
3 | TO_NUMBER(字符串 | 列) | 将指定的数据类型变为数字 |
3.4.1、TO_CHAR()函数
熟悉格式化日期的替代标记
NO | 转换格式 | 描述 |
1 | YYYY | 完整的年份数字表示,年有四位,所以用四个Y |
2 | Y,YYY | 带逗号的年 |
3 | YYY | 年的后三位 |
4 | YY | 年的后两位 |
5 | Y | 年的最后一位 |
6 | YEAR | 年份的文字表示,直接表示四位的年 |
7 | MONTH | 月份的文字表示,直接表示两位的月 |
8 | MM | 用两位数字表示月份,月有两位,所以用两个M表示 |
9 | DAY | 天数的文字表示 |
10 | DDD | 表示一年里的天数(01--366) |
11 | DD | 表示一月里的天数(01--31) |
12 | D | 表示一周里的天数 |
13 | DY | 用文字表示的星期几 |
14 | WW | 表示一年里的周数 |
15 | W | 表示一月里的周数 |
16 | HH | 表示12小时制,小时是两位数字,使用两个H |
17 | HH24 | 表示24小时制 |
18 | MI | 表示分钟 |
19 | SS | 表示秒,秒是两位数字,所以使用两个S |
20 | SSSSS | 午夜之后的秒数字表示(0---86399) |
21 | AM|PM(A.M.|P.M.) | 表示上午下午 |
22 | FM | 去掉查询后的前导0,改标记用于时间模板的后缀 |
范例:格式化当前的日期时间
3.5、通用函数
通用函数指的是oracle中具有一些基本特色的函数,
NO. | 函数名称 | 描述 |
1 | NVL(数字 | 列,默认值) | 如果显示的数字是null的话,则使用默认值表示 |
2 | NVL2(数字 | 列,返结果如果1(不为空现实),返回结果2(为空现实)) | 判断指定的列是否为null,如果不为null则返回结果为1,如果为空则返回结果为2 |
3 | NULLIF(表达式1,表达式2) | 比较表达式1和表达式2的结果是否相等,如果相等返回NULL,如果不等返回1 |
4 | DECODE(列 | 值,判断值1,显示结果1,判断值2,显示值2,... ,默认值) | 多指判断,如果某一个列(或某一个值)与判断值相同,则使用指定的显示结果输出,如果没有满足条件,则显示默认值 |
5 | CASE 列 | 数值 WHEN 表达式1 THEN 显示结果1 ..... ELSE 表达式n .....END | 用于实现多条件判断,在WHEN之后编写条件,而在THEN之后编写条件满足的显示操作,如果都不满足则使用ELSE之后的表达式处理 |
6 | COALESCE(表达式1,表达式2,..... ,表达式n) | 将表达式逐个判断,如果表达式1的内容为null,则显示表达式2,如果表达式2的内容为null,则显示表达式3的内容,以此类推,如果表达式n的结果还是为null,则返回null |
范例:验证NULLIF()函数
SELECT NULLIF(1,1),NULLIF(1,4)FROM dual;