数据库基本开发
学习目标
- 掌握表空间、用户基本知识和创建方法。
- 掌握表的创建;修改;删除。
- 掌握数据的操作:增;删;改;查。
- 掌握函数的使用。
- 掌握条件查询。
- 掌握子查询。
一、表空间、用户
表空间
- system
- user
- temp
定义:表空间是数据库的逻辑划分,一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表, 所以称作表空间。
表空间的优势
1、控制用户所占用的表空间配额。
2、控制数据库所占用的磁盘空间。
3、灵活放置表空间,提高数据库的输入输出性能。
4、大表的排序操作。
5、日志文件与数据文件分开放,提高数据库安全性。
语法:CREATE TABLESPACE <tablespace name> DATAFILE '<directory path>\<file name>.dbf' SIZE <size>;
参数说明:
tablespace name:是您想要创建的表空间的名称,
directory path:是文件存储路径的文件夹,例如 C:\app\user\oracle\product\12.1.0\db_1\database,
file name:是表空间文件的名称,例如 sysaux.dbf,
size:是表空间的大小。
用户
用户定义:数据库用户即使用和共享数据库资源的人,有在当前数据库中创建数据库对象及进行数据库备份的权限、用户对数据库表的操作权限及执行存储过程的权限以及用户数据库中指定表字段的操作权限三种权限。
用户权限:
1、在当前数据库中创建数据库对象及进行数据库备份的权限。
2、用户对数据库表的操作权限及执行存储过程的权限。
3、用户数据库中指定表字段的操作权限。
创建用户语法:CREATE USER <username> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace name> QUOTA <size> ON <tablespace name>;
参数解释
username是您要创建的用户名。
password是用户的密码。
tablespace是用户的默认表空间。
size是表空间的大小。
用户授权:GRANT
参数解释:privilege是用户需要访问的权限(如SELECT、INSERT、UPDATE、DELETE等)。
username是您创建的用户名。
二、表–创建;修改;删除
1、创建表语法:
create table 表名(字段名1 类型 约束,字段名2 类型 约束,字段名3 类型 约 束,……);
2、表修改:主要是增加列或者删除列。
增加列语法:
2.1、使用ALTER TABLE语句增加表字段:ALTER TABLE 表名 ADD (字段名 字段类型);
2.2、使用CREATE TABLE AS语句增加表字段:
CREATE TABLE new_table AS SELECT column1,column2,column3,… FROM old_table;
删除列语法:
Alter TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;
3、删除表:Drop Table 表名
三、数据操作—–增;删;改;查
1.增加数据
使用INSERT语句
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);
使用INSERT ALL语句:它允许一次性向多个表中插入多条记录。
INSERT ALL
INTO table1 (column1, column2, column3, …) VALUES (value1, value2, value3, …)
INTO table2 (column1, column2, column3, …) VALUES (value1, value2, value3, …)
INTO table3 (column1, column2, column3, …) VALUES (value1, value2, value3, …)
使用INSERT INTO SELECT语句:它允许从一个表中选择数据,并将其插入到另一个表中。
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, … FROM table1;
2、数据删除
利用delete删除表数据:delete from 表名 where 条件语句。
利用truncate命令删除数据:truncate table 表名
truncate和delete的区别:
1)、条件删除:这个比较好理解,因为delete是可以带WHERE的,所以支持条件删除;而truncate只能删除整个表。
2). 事务回滚:由于delete是数据操作语言(DML - Data Manipulation Language),操作时原数据会被放到 rollback segment中,可以被回滚;而truncate是数据定义语言(DDL - Data Definition Language),操作时不会进行存储,不能进行回滚。
3). 清理速度:在数据量比较小的情况下,delete和truncate的清理速度差别不是很大。但是数据量很大的时候就能看出区别。由于第二项中说的,truncate不需要支持回滚,所以使用的系统和事务日志资源少。delete 语句每次删除一行,并在事务日志中为所删除的每行记录一项,固然会慢,但是相对来说也较安全。
4). 高水位重置:随着不断地进行表记录的DML操作,会不断提高表的高水位线(HWM),delete操作之后虽然表的数据删除了,但是并没有降低表的高水位,随着DML操作数据库容量也只会上升,不会下降。所以如果使用delete,就算将表中的数据减少了很多,在查询时还是和delete操作前速度一样。
3、数据修改
使用UPDATE语句
UPDATE table_name SET column1=value1,column2=value2,… WHERE some_column=some_value;
使用MERGE语句
MERGE INTO table1 USING table2 ON (condition)
WHEN MATCHED THEN UPDATE SET column1=value1,column2=value2,…
WHEN NOT MATCHED THEN INSERT (column1,column2,…) VALUES (value1,value2,…);
使用PL/SQL语句:对于复杂的更新操作,可以使用PL/SQL语句。PL/SQL是Oracle数据库的编程语言,可以编写复杂的存储过程、触发器和函数等。
DECLARE v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM users WHERE age
IF v_count>0 THEN
UPDATE users SET age=18 WHERE age
END IF;
END;
数据查询:基本查询语句
select * from table_name/select column1,column2,…from table_name
四、函数的使用
普通行级函数
1、字符串函数
upper/lower:列值的大小写转换。
initcap:对某一列首字母进行大写。
concat:字符拼接 只有两个参数。
||:字符拼接 可以连接多列。
SUBSTR(ch, pos, length)截取函数:参数ch 列名, 参数pos 从第几位开始截取,参数length 截取几位。
LENGTH(ch):得到字符长度的。
trim:去空格(去除前后空格)。
Ltrim:去除左边的空格/Rtrim:去除右边的空格
REPLACE(source, search_str, replace_str)替换
参数source–列名,可以是自定义的值。
参数search_str–要被替换的内容 参数一里面有的。
参数replace_str–用谁去替换 参数一里面没有的。
lpad , rpad填充
LPAD(expr1, n, expr2)
RPAD(expr1, n, expr2)
第一个参数可以是列名或者值,第二个参数:是一个数值,第三个参数:填充的内容。
2、数字函数
ROUND(date, fmt)四舍五入:参数一可以是列名,值。参数二要保留小数点后几位。
TRUNC(date, fmt)截断:参数一可以是列,可以是值。参数二,小数点后面保留几位,不考虑四舍五入。
MOD(n1, n2):求余数。
ABS(x):返回x的绝对值。
BIN(x):返回x的二进制(oct返回八进制,hex返回十六进制)。
CEILING(x):返回大于x的最小整数值。
EXP(x):返回值e(自然对数的底)的x次方。
FLOOR(x)—返回小于x的最大整数值。
GREATEST(x1,x2,…,xn)—返回集合中最大的值。
LEAST(x1,x2,…,xn)—返回集合中最小的值。
LN(x)—返回x的自然对数。
LOG(x,y)—返回x的以y为底的对数。
PI():返回pi的值(圆周率)。
RAND():返回0到1内的随机值,可以通过提供一个参数(种子)使。
rand()随机数生成器生成一个指定的值。
SIGN(x):返回代表数字x的符号的值。
SQRT(x):返回一个数的平方根。
3、日期函数
SYSDATE:返回系统时间。
MONTHS_BETWEEN(date1, date2):两个日期相差的月数 参数可以是列可以是值。
ADD_MONTHS(date, int):向指定日期中加上若干月数。
NEXT_DAY(date, ch):指定下个星期几的日期。
参数一:就是日期可以是列可以是值 ,参数二,数值(1-7)国外星期计算。
LAST_DAY(date):本月的最后一天。
ROUND(date,fmt):对日期进行四舍五入:第二个参数可以是year yyyy 年,month mm 月,day dd 天。
TRUNC(date, fmt):对日期进行截断。
4、转换函数
TO_CHAR(date, ‘format_model’):日期转换成字符。
TO_DATE:字符转换成日期。
TO_NUMBER(char [,fmt [,nlsparam]]):其中char是待转换的字符串,fmt指定待转换字符串的匹配规则。
5、通用函数
NVL(expr1, expr2):将空值转换成一个已知的值:可以使用的数据类型有日期、字符、数字。
expr1:列名 ,expr2 :自己定义的值
NVL2(expr1, expr2, expr3):根据参数1是否为null返回参数2或参数3的值。
COALESCE(expr1, … exprn):1为空返回2,1,2为空返回3,直到值不为空。
条件表达式:
case when 表达式 then … 当满足什么条件后 输出什么结果
when 表达式 then … 当满足什么条件后 输出什么结果
……………
else …. 最后输出其他结果
end 结束
EXTRACT():函数用于提取日期/时间的单独部分,比如年、月、日、小时、分钟等等。
INSTR:(‘源字符串’,‘目标字符串’,’开始位置’,’第几次出现’)。
SIGN(n):n 可以是列,可以是值 ,可以是表达式。
LPAD(string, padded_length, [ pad_string ])
参数说明:
string源字符串;
padded_length: 即最终结果返回的字符串的长度;如果最终返回的字符串的长度比源字符串的小,那么此函数实际上对源串进行截取处理,与substr(string,number1,number2)的作用完全相同,如果padded_length比源字符串的长度长,则用pad_string进行填充,确保返回的最终字符串的长度为padded_length;
pad_string:用于填充的字符,可以不填,默认为空字符。
NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(null),否则返回第一个值。
开窗函数
1、定义:开窗函数是一种强大的查询工具,它能够让开发人员使用类似EXCEL的窗口函数方式对数据库数据进行分析和聚合。
2、作用:开窗函数的主要作用是在对数据进行处理之前进行统计和聚合。
3、种类
1)排序函数:row_number()over()、rank()over()、dense_rank()over -必须掌握。
2)聚合函数:sum()over()、min()over()、max()over()、avg()over()、count()over()。
3)返回特定行函数:first_value()over()返回第一个值、last_value()over()返回最后一个值。
4)返回前N行数据和后N行数据:lag()over()、lead()over()–必须掌握。
分析子句:分组(PARTITION BY) 排序(ORDER BY) 窗口(ROWS) –窗口还有个极少用的RANGE。
4、开窗函数的应用:
4.1聚合类开窗函数应用:SUM() AVG() MAX() MIN() COUNT()
准备工作—-创建一个名字为BUSINESS,插入多条数据,语法如下
建表
CREATE TABLE BUSINESS(
DATE_DT VARCHAR2(20),
DAY VARCHAR2(20),
WEEK VARCHAR2(20),
AMT NUMBER
);
数据准备
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-04’,’星期一’,’第一周’,’3000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-05’,’星期二’,’第一周’,’2000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-06’,’星期三’,’第一周’,’1000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-07’,’星期四’,’第一周’,’4000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-08’,’星期五’,’第一周’,’6000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-09’,’星期六’,’第一周’,’2000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-10’,’星期日’,’第一周’,’3000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-11’,’星期一’,’第二周’,’1000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-12’,’星期二’,’第二周’,’4000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-13’,’星期三’,’第二周’,’8000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-14’,’星期四’,’第二周’,’2000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-15’,’星期五’,’第二周’,’5000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-16’,’星期六’,’第二周’,’3000’);
INSERT INTO BUSINESS(DATE_DT,DAY,WEEK,AMT) VALUES(‘2020-05-17’,’星期日’,’第二周’,’7000’);
用法(以SUM为例):SUM(COL_NAME)OVER([PARTITION] [ORDER] [ROWS])
SELECT A.,SUM(AMT) OVER() FROM BUSINESS A;
SELECT WEEK,SUM(AMT) FROM BUSINESS GROUP BY WEEK;
SELECT A.,SUM(AMT) OVER(PARTITION BY WEEK) FROM BUSINESS A;
SELECT A.*,SUM(AMT) OVER(ORDER BY DAY) FROM BUSINESS A;
–查询每天的营业额及整个月的营业额总额。
SELECT DATE_DT,AMT,(SELECT SUM(AMT) FROM BUSINESS) FROM BUSINESS;
–查询每天的营业额及每个周的营业额总额。
SELECT DATE_DT,AMT,WEEK,SUM(AMT) OVER(PARTITION BY WEEK) A FROM BUSINESS;
–查询每天的营业额及月每日累计营业额。
SELECT A.*,SUM(AMT) OVER(ORDER BY DATE_DT) FROM BUSINESS A; –所有数据从第一条到当前数据的和
–查询每天的营业额及周每日累计营业额。
SELECT A.*,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT) FROM BUSINESS A;–在组内,从第一条到当前数据的和。
聚合类开窗函数注意点:
1)分析函数名内必须包含需要分析的内容。
2)分析子句没有硬性要求 –出现ROWS时,必须跟随ORDER BY。
3)采用默认窗口范围时,下一个相同值(排序的值)会被一并算入。
4.2、排序类举例:ROW_NUMBER() RANK() DENSE_RANK()
–当天营业额与周营业额之差
SELECT DATE_DT,AMT,AMT-SUM(AMT) OVER(PARTITION BY WEEK) FROM BUSINESS;
–查询每天的营业额并在整月范围内升序排列
SELECT DATE_DT,AMT,ROW_NUMBER() OVER(ORDER BY AMT) FROM BUSINESS; –如果有相同的,也会按序号往下排(不并列,不跳跃)
SELECT DATE_DT,AMT,RANK() OVER(ORDER BY AMT) FROM BUSINESS; –如果有相同的,会把相同的变成同一个序号,按相同的数量的总数往下一位排(并列跳跃)
SELECT DATE_DT,AMT,DENSE_RANK() OVER(ORDER BY AMT) FROM BUSINESS; –如果有相同的,会把相同的变成同一个序号,下一个不相同的,按这个序号加1往下排(并列不跳跃
–查询每天的营业额并在每周范围内降序排列
SELECT DATE_DT,AMT,WEEK,ROW_NUMBER() OVER(PARTITION BY WEEK ORDER BY AMT DESC) FROM BUSINESS;
注意:用在去重时,一般用ROW_NUMBER(),因为如果用RANK()和DENSE_RANK()的话。
–碰到两条一样的数据时排序会一样,而在取出来时也会一并取出来,达不到去重的效果。
–写在开窗函数里的ORDER BY 和 写在开窗函数外面的ORDER BY 的区别。
写在开窗函数里的ORDER BY是对开窗函数里的数据进行排序。
写在开窗函数外面的ORDER BY是对最终的结果进行一个排序。
排序类开窗函数注意点:
1)分子函数名内不能包含任何内容 –ROW_NUMBER() 括号内不能包含任何东西。
2)分析子句内必须添加ORDER BY,且不能指定窗口 –排序类字句中必须加ORDER BY ,而且不能加ROWS()。
4.3、偏移类举例:LAG() LEAD()
LAG(COL_NAME,OFFSET,DEFVAL)OVER():向前偏移N行取数。
COL_NAME:要分析的字段。
OFFSET:偏移量 –默认偏移一行。
DEFVAL:默认返回值 –默认返回空null。
应用举例
–查询每天的营业额以及前一天的营业额
–方法1:
SELECT DATE_DT, AMT, LAG(AMT, 1, 0) OVER(ORDER BY DATE_DT) FROM BUSINESS;
–方法2:
SELECT DATE_DT, AMT,
SUM(AMT) OVER(ORDER BY DATE_DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) FROM BUSINESS;
–两天营业额之差
SELECT DATE_DT,AMT,AMT-LAG(AMT,1,0) OVER(ORDER BY DATE_DT) FROM BUSINESS; –偏移量不能为负值
–查询五月连续登录五天的用户
–准备工作:建表;插入数据
–建表
CREATE TABLE EXAM(
ID VARCHAR2(10),
TS VARCHAR2(15)
);
–插入数据
INSERT INTO EXAM VALUES(‘A0001’,’2021/01/04’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/04’);
INSERT INTO EXAM VALUES(‘A0001’,’2021/01/05’);
INSERT INTO EXAM VALUES(‘A0003’,’2021/01/05’);
INSERT INTO EXAM VALUES(‘A0001’,’2021/01/06’);
INSERT INTO EXAM VALUES(‘A0001’,’2021/01/07’);
INSERT INTO EXAM VALUES(‘A0001’,’2021/01/08’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/09’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/10’);
INSERT INTO EXAM VALUES(‘A0003’,’2021/01/10’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/11’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/12’);
INSERT INTO EXAM VALUES(‘A0002’,’2021/01/13’);
INSERT INTO EXAM VALUES(‘A0005’,’2021/01/13’);
INSERT INTO EXAM VALUES(‘A0003’,’2021/01/14’);
INSERT INTO EXAM VALUES(‘A0004’,’2021/01/15’);
INSERT INTO EXAM VALUES(‘A0004’,’2021/01/16’);
INSERT INTO EXAM VALUES(‘A0007’,’2021/01/17’);
INSERT INTO EXAM VALUES(‘A0008’,’2021/01/18’);
SELECT ID,TS,
TO_CHAR(TO_DATE(TS, ‘YYYY/MM/DD’) - 4, ‘YYYY/MM/DD’) A,
LAG(TS, 4) OVER(PARTITION BY ID ORDER BY TS) B
FROM EXAM;
–查询其向上偏移4天的登录时间
SELECT DISTINCT ID
FROM (SELECT ID,TS, –本次(当天)登录日期
TO_CHAR(TO_DATE(TS, ‘YYYY/MM/DD’) - 4, ‘YYYY/MM/DD’) A, –当前数四天的日期
LAG(TS, 4) OVER(PARTITION BY ID ORDER BY TS) B –上四次的登录日期
FROM EXAM) WHERE B IS NOT NULL; –ERROR(不能用非空来算)
SELECT DISTINCT ID
FROM (SELECT ID,TS, –本次(当天)登录日期
TO_CHAR(TO_DATE(TS, ‘YYYY/MM/DD’) - 4, ‘YYYY/MM/DD’) A, –当前数四天的日期
LAG(TS, 4) OVER(PARTITION BY ID ORDER BY TS) B –上四次的登录日期
FROM EXAM) WHERE A=B;
偏移类开窗函数注意点:
1)分析函数名内必须包含要分析的内容,其他两项参数可以默认。
2)分析子句内必须添加ORDER BY,且不能指定窗口。
3)若不再有可供偏移的行,则返回默认值。
4)偏移量不允许写负数。
5)分析的字段与默认返回值数据类型要保持一致。
4.4、开窗函数相关总结
4.4.1、各种窗口范围:PRECEDING:之前的;FOLLOWING:之后的;CURRENT:当前的;UNBOUNDED:不受限的;ROW:行。
举例:
ROWS BETWEEN N PRECEDING AND N FOLLOWING 前N位到后N位。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) B FROM BUSINESS;
ROWS BETWEEN CURRENT ROW AND N FOLLOWING 当前位和到后N位。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) B FROM BUSINESS;
ROWS BETWEEN N PRECEDING AND CURRENT ROW 前N位到当前位
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM BUSINESS;
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 从前面所有行到当前行。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM BUSINESS;
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 从当前行到后面所有行。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM BUSINESS;
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 全部。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM BUSINESS;
ROWS BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING 从前面所有到当前行。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM BUSINESS;
ROWS BETWEEN N PRECEDING AND UNBOUNDED FOLLOWING 从前面N行到后面所有。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) FROM BUSINESS;
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) B
FROM BUSINESS;
求的是当前一行前面三个,加上它后面两个的和;当前的和为六个数相加所得。
SELECT DATE_DT,AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) B FROM BUSINESS;
B的值为B所对应的值加上它前面1位的值和后面1位的值
SELECT DATE_DT, AMT,SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATE_DT ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) B FROM BUSINESS;
4.4.2、不同分析子句组合:–ROWS的出现,必须要伴随ORDER BY。
举例
1.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER() FROM EMP;
2.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;
3.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(ORDER BY SAL )
FROM EMP;
4.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM EMP;
–ERROR(窗口字句不能单独出现)
5.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;
6.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM EMP;
–ERROR (出现ROWS,必须跟随ORDER BY)
7.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM EMP;
8.SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM EMP ;
1.窗口子句不能单独出现,必须要有排序子句出现的情况下才能指定窗口范围。
2.若出现排序子句,同时未指定窗口范围,默认的窗口范围是第一行到当前行;若未出现排序子句,同时未指定窗口范围,默认的窗口范围是第一行到最后一行。
3.PARTITION BY 分组的范围,ROWS 统计分析的范围 ,分析范围不会超过分组范围。
4.5、聚合函数与开窗函数的差异
1.聚合函数每组数据返回一行值;开窗函数每条数据返回一行值。
2.开窗函数后会跟一个OVER(),聚合函数后没有。
3.开窗函数通过PARTITION BY 分组 ,聚合函数通过GROUP BY 分组o。
4.开窗函数做分析时,并不一定是拿整个分组的数据进行分析,而是通过窗口指定;聚合函数做分析时,一定是拿整个分组的数据进行分析。
五、条件查询
1.定义—用于判断符合业务或者实际运用场景要求的查询语句。
2.分类:
2.1基本的if else
DECLARE:
I INT :=1;
BEGIN
IF I=1 THEN
DBMS_OUTPUT.PUT_LINE(‘TRUE’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘FALSE’);
END IF;
END;
2.2这里用的是elsif 而不是 else if(注意区分一下就行了)
DECLARE:
I INT :=1; –如果I=1 满足第一个条件之后,就不会再走第二个条件
–如果I=5,则直接走最后的一个条件
BEGIN
IF I<4 THEN
DBMS_OUTPUT.PUT_LINE(‘4’);
ELSIF I<3 THEN
DBMS_OUTPUT.put_line(‘3’);
ELSIF I<2 THEN
DBMS_OUTPUT.PUT_LINE(‘2’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘0’);
END IF;
END;
2.3 decode语法–DECODE(‘条件’,值1,真,假)
实例一:SELECT DECODE((2-2),0,’TRUE’,’FALSE’) FROM DUAL; –结果返回true。
实例二;比较两个数的大小。先介绍一函数的使用sign() 它只会返回三个值:1 0 -1。
SELECT SIGN(2-1) FROM DUAL; –正数就返回1。
SELECT SIGN(2-2) FROM DUAL; –0 就返回0。
SELECT SIGN(2-3) FROM DUAL; –负数就返回-1。
然后配合它 我们可以比较两个数的大小。
DECLARE
I INT :=2;
J INT :=3;
RESULT INT :=0;
BEGIN
SELECT DECODE(SIGN(I-J),-1,I,J) INTO RESULT
FROM DUAL; –比较连个数,求最小或者最大的数
DBMS_OUTPUT.put_line(RESULT); –这样我们就求出了最小的一个数值滴呀
END;
实例三(字段)
DECLARE
NAME VARCHAR2(99) :=’DD’;
RESULT VARCHAR2(99) ;
BEGIN
SELECTD ECODE(NAME,’JACK’,’JACK’,’TOM’,’TOM’,’DEFAULUT’) INTO RESULT
FROM DUAL;
–第一个参数值 也可以是我们的字段;
–字段,比较,值1,比较,值2,默认值
–进行这样的基本模式比较低呀
DBMS_OUTPUT.put_line(RESULT);
END;
2.3.1DECODE的应用 ,下面我们看这样的一个需求;
2.3.1.1数据准备
CREATE TABLE STUINFO(
ID NUMBER,
NAME VARCHAR2(40),
SEX VARCHAR2(4)
)
INSERT INTO STUINFO VALUES(1,’JACK’,’男’);
INSERT INTO STUINFO VALUES(2,’TOM’,’男’);
INSERT INTO STUINFO VALUES(3,’ANDY’,’女’);
INSERT INTO STUINFO VALUES(4,’CANDY’,’女’);
COMMIT;
2.3.1.2具体应用
–现在我们来统计班上 男生多少人,女生多少人;
–方法一:SELECT COUNT() FROM STUINFO WHERE SEX=’男’;
SELECT COUNT() FROM STUINFO WHERE SEX=’女’;
–方法二:(如果id没有重复的话)
–我们再求一次和
–方法虽然有点绕弯,但是可以锻炼我们写sql语句的能力滴呀
SELECT SUM(MEN_COUNT) “男生总和”,SUM(FEMEL_COUNT) “女生总和” FROM (SELECT ID,
SUM(CASE SEX WHEN ‘男’ THEN 1 ELSE 0 END) “MEN_COUNT”,
SUM(CASE SEX WHEN ‘女’ THEN 1 ELSE 0 END) “FEMEL_COUNT”
FROM STUINFO
GROUP BY ID);
–方法三:自然就用到我们的DECODE
SELECT SUM(DECODE(SEX,’男’,1,0)) “MEN_TOTAL”, SUM(DECODE(SEX,’女’,1,0)) “女生总和”
FROM STUINFO
2.4 case when 的用法
–关于学生成绩 分等级的计算滴呀
–CASE WHEN 字段 THEN 的用法。
SELECT NAME,
CASE WHEN GREADE >85 THEN ‘优秀’
WHEN GREADE >70 THEN ‘良好’
WHEN GREADE >60 THEN ‘及格’
ELSE ‘不及格’
END “等级”
FROM STU
六、子查询
1.子查询简介:子查询是嵌套在另一个语句(如SELECT,INSERT,UPDATE或DELETE)中的SELECT语句。通常,可以在任何使用表达式的地方使用子查询。
子查询的例子。
以下查询使用MAX()函数从products表中返回产品的最高标价:SELECT MAX( list_price ) FROM products;
要查询最贵的产品的详细信息,可在以下查询中使用上面的查询出的标价(8867.99):
SELECT product_id, product_name, list_price FROM products WHERE list_price = 8867.99
我们需要分别执行两个查询来获取最贵的产品数据信息。
通过使用子查询可以将第一个查询嵌套到第二个查询中,如以下查询所示:
SELECT product_id,product_name, list_price
FROM products
WHERE list_price = (SELECT MAX( list_price ) FROM products);
在此示例中,检索最高价格的查询是子查询,选择详细产品数据的查询是外部查询。子查询嵌套在外部查询中。请注意,子查询必须出现在括号内 。
Oracle分两步评估(计算)上面的整个查询:
首先,执行子查询。
其次,在外部查询中使用子查询的结果作为条件查询数据。
嵌套在SELECT语句的FROM子句中的子查询称为内联视图。请注意,其他RDBMS(如MySQL和PostgreSQL)使用术语派生表 而不是内联视图。
嵌套在SELECT语句的WHERE子句中的子查询称为嵌套子查询。
子查询可以包含另一个子查询。Oracle允许在顶级查询的FROM子句中使用无限数量的子查询级别,并在WHERE子句中使用多达255个子查询级别。
子查询的主要优点:
提供一种替代方法来解决查询需要复杂联接和联合的数据。
使复杂的查询更具可读性。
允许以一种可以隔离每个部分的方式来构建复杂的查询。
最后编辑:严锋 更新时间:2024-07-01 11:15