1. 基本入门
1.1 创建表空间：
create tablespace pf_spc
datafile 'D:\A_Oracle课程\oracle\pf.dbf'--存储在自己下载的oracle
size 20m --初始大小
1.2 将表空间分配给用户
create user pf
identified by "pf123"
default tablespace pf_spc
1.3 授权用户来登录：connect（连接权限） resource（资源权限）
grant connect,resource to 用户名
1.4 收回权限
revoke resource from 用户名
1.5 上锁解锁
alter user pf account unlock--解锁
alter user pf account lock--锁住
2. 数据管理
2.1 数据类型
字符类型
char，定长字符串，内容不够空格填充来达到器最大长度varchar2，变长字符串，内容不够不会填充，空串等同于null处理
数字类型
number(p,s)：p是有效数字位数（<=38）,s是小数点位数integer：是number子类型，等同于number(38,0)，用来存储整数。若插入，更新的数值有小数，则会被四舍五入
浮点数
binary_float：单精度,32位浮点数，存储需要5个字节，包括长度字节binary_double：双精度,64位浮点数，存储需要9个字节，包括长度字节
日期类型
date：不含微秒级时间，一般占用7个字节timestamp：不含时区，含微妙时间timestamp with time zone：保持client的timezonetimestamp withlocal time zone：把client的timezone转换成DB的timezone对应的时间
lob类型
clob，二进制数据，存储单字节和多字节字符数据，最大长度4Gblob，存储非结构化的二进制数据大对象，是没有字符集义的比特流，存储一般图像、声音、视频等文件，最大4Gnclob，存储Unicode类型的数据，最大长度4G
long&raw&long raw类型
long：存储变长字符串（超长字符串），最多达2G（两千兆字节）的字符数据long raw：能存储2GB的原始二进制数据，可存放媒体图像声音等raw：用于存储二进制或字符类型数据，必须指定长度，不会发生字符集转换，可存放多媒体图像声音等
2.2 基本 SQL
--新建表
create table student(
stu_id number primary key,
stu_name varchar2(200) not null,
stu_sex char(3) not null,
stu_birthday date
);
--插入数据
insert into student values(1,'小明','男',to_date('2020/1/1','yyyy/mm/dd'))
insert into student values(2,'小黑','男',to_date('2020-1-1','yyyy-mm-dd'))
--日期不能直接使用字符串 需要用to_date进行转换 格式要一致
--插入数据(union)
insert into student
select 5,'小绿','男',to_date('2020/1/1','yyyy/mm/dd') from dual
union --联合
select 6,'小白','男',to_date('2020/1/1','yyyy/mm/dd') from dual;
--修改数据
UPDATE STUDENT SET STU_NAME='AA' WHERE STU_ID=6;
COMMIT;--提交
--删除数据
DELETE FROM STUDENT;
ROLLBACK;--回滚 事务
--删除表
DROP TABLE STUDENT;
--删除表中的数据
--复制表
CREATE TABLE STU_BAK
AS
SELECT * FROM STUDENT;
SELECT * FROM STU_BAK01;
--复制表(只复制结构)
CREATE TABLE STU_BAK01
AS
SELECT * FROM STUDENT WHERE 2=4;
--查看emp表
SELECT * FROM EMP;
--给emp表取别名
SELECT A.ENAME FROM EMP A;
--只查看emp表的员工名字
--给ename列取中文别名
SELECT A.ENAME AS 名字 FROM EMP A;
SELECT A.ENAME 名字 FROM EMP A;
--查看emp表中员工的职业信息
SELECT DISTINCT JOB FROM EMP;
--查询emp表中职业为CLERK的员工信息
SELECT * FROM EMP WHERE JOB ='CLERK';
--查询emp表中职业为CLERK或SALESMAN的员工信息
SELECT * FROM EMP WHERE JOB='CLERK' OR JOB='SALESMAN'
--查询emp表中名字带S的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%S%'
--查询emp表中名字以S开头的员工信息
SELECT * FROM EMP WHERE ENAME LIKE 'S%'
--查询emp表中名字以S开头且名字为五个字的员工信息
SELECT * FROM EMP WHERE ENAME LIKE 'S____'
--查询emp表中职业为CLERK且工资大于1000的员工信息
SELECT * FROM EMP WHERE JOB='CLERK' AND SAL>1000
--查询emp表中每个员工的收益(薪水+奖金)
-- ISNULL() SQLSERVER
-- NVL() ORACLE
SELECT SAL+NVL(COMM,0) FROM EMP
--将emp表中的员工名字和职业连接起来(查询成1列)
-- || 连接
SELECT ENAME||'-'||JOB 职业信息 FROM EMP
--查看emp表中没有奖金的人
SELECT * FROM EMP WHERE COMM IS NULL
--查看emp表中有奖金的人
SELECT * FROM EMP WHERE COMM IS NOT NULL
--按照emp表中的工资升序排列数据
SELECT * FROM EMP ORDER BY SAL ASC
--按照emp表中的工资降序排列数据
SELECT * FROM EMP ORDER BY SAL DESC
--按照emp表中的 部门降序 工资升序 排列数据
SELECT * FROM EMP ORDER BY DEPTNO DESC,SAL ASC
--求工资最高的人
SELECT B.*,ROWNUM FROM (
SELECT A.*,ROWNUM C FROM EMP A ORDER BY SAL DESC
) B WHERE ROWNUM=1
--求工资排列中(升序)的前三人
SELECT B.*,ROWNUM FROM (
SELECT * FROM EMP A ORDER BY SAL DESC
) B WHERE ROWNUM<4
-- ROWNUM可以用于小于
-- 还可以用于等于 但是等于只能为1
--求工资排列中(升序)的前三到六人
-- 伪列不好使用，将它变成实列
SELECT * FROM (
SELECT B.*,ROWNUM C FROM (
SELECT A.* FROM EMP A ORDER BY SAL DESC
) B
) D WHERE C BETWEEN 4 AND 6
3. 内置函数
3.1 转换函数
to_char()
作用：把一个非字符类型转换为字符类型
案例：调用当前的系统日期，日期类型转字符类型
- 转换当前系统时间为字符串类型，时间格式为：xxxx-xx-xx xx:xx:xx
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
- 转换当前系统时间为字符串类型，只显示年月日，时间格式为：xxxx-xx-xx
select to_char(sysdate,'yyyy-mm-dd ') from dual
- 转换当前系统时间为字符串类型，并且显示是24小时制，时间格式为：xxxx年xx月xx日 xx:xx:xx
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual
to_date()
作用：把字符类型的时间转换为日期类型
案例1：把字符类型时间 ‘2021-08-18’ 转换成日期类型，格式为：xxxx/xx/xx
select to_date('2021-08-18','yyyy/mm/dd') from dual
案例2：把字符类型时间 ‘2021-08-18 11:33:44’ 转换成日期类型，格式为：xxxx/xx/xx xx:xx:xx；
select to_date('2021-08-18 11:33:44','yyyy/mm/dd hh:mi:ss') from dual
to_number()
作用：把字符字符类型的数字转换为数值类型；
select 100+to_number('200') from dual
3.2 日期函数
add_months()
作用：在指定的日期上加上指定数量的月份，求当时的日期
注意点：只能作用于日期类型
案例1：求当前系统时间100个月之后的日期
select add_months(sysdate,100) from dual
案例2：求2020年8月8日，100个月之后的日期
select add_months(to_date('2020-08-08','yyyy-MM-dd'),100) from dual
案例3：求2020年8月8日，100个月之前的日期
select add_months(to_date('2020-08-08','yyyy-MM-dd'),-100) from dual
extract()
作用：单独取日期里面的年月日
注意点：只能作用于日期类型
案例1：取当前系统时间的年份
select extract(year from sysdate) from dual
案例2：取当前系统时间的月份
select extract(month from sysdate) from dual
案例3：取当前系统时间的日期
select extract(day from sysdate) from dual
案例4：求emp表里面2月份入职的人数
select count(*) from emp where extract(month from hiredate) = 2
案例5：求emp表里面每个月份入职的人数
select extract(month from hiredate),count(*) from emp group by extract(month from hiredate)
months_between()
作用：求两个时间之间相差的月份
注意点：只能作用于日期类型
案例：求2020-2-2和当前系统时间之间相差的月份
select months_between(sysdate,to_date('2017-2-2','yyyy/mm/dd')) from dual
last_day()
作用：求本月的最后一天
案例1：求当前月份的最后一天日期
select last_day(sysdate) from dual
案例2：求2021年的最后一天
select last_day(to_date('2021-12','yyyy-mm'))from dual
next_day()
作用：求下个指定星期的日期
注意点：只能作用于日期类型
案例：求下周六的日期
select next_day(sysdate,'星期六') from dual
3.3 字符函数
upper、lower、initcap、ltrim、rtrim…
select upper('abcdAsdfa') from dual; --全部转为大写；
select lower('abcdAsdfa') from dual; --全部转为小写；
select initcap('adad') from dual； --将首字母转换为大写；
select ltrim('abcd','a') from dual; --去掉左边的字符(第一个字符若是a则去 除，反之不去除)；
select rtrim('acccsdfdsccccbcd','c') from dual; --去掉右边的字符(第一个字符若是c则去除，反之不去除)；
select substr('abcdefghi',3,4) from dual; --从第三位开始截，截4个；
select instr('abcdefg','c') from dual; --判断c的位置，下标从1开始；
select concat('aa','bb') from dual; --这是拼接的意思，用的少，因为我们可以用||替代；
select ascii('a') from dual; --求a的ic码；
select char(77) from dual; --将ic码转换成对应的值字符；
select length('abcd张') from dual; --求字符的个数，不是字节的个数；
select lpad('abde',9,'x') from dual; --左填充，从左边开始用‘x’填充，直到字节的长度变为9为止；
select rpad('abde',9,'x') from dual; --右填充，从右边开始用‘x’填充，直到字节的长度变为9为止；
select decode('c','a',1,'b',2,'c',3) from dual; --结果为3，意思是给几个结果进行判断，如果前面的符是a就输出1，如果是b就输出2，如果是c就输出3；
3.4 数学函数
ceil、floor、round、mod、sqrt、sign
select ceil(57.12) from dual; --向上取整；
select floor(57.98) from dual; --向下取整；
select round(54.99) from dual; --结果是55，四舍五入；
select round(54.59,1) from dual; --四舍五入并且保留小数点后1位数；
select power(3,2) from dual； --3的2次方；
select mod(6,7) from dual; --取余/模；
select sqrt(4) from dual; --开根号；
select sign(0) from dual; --如果是正数结果就为1，如果是负数结果就为-1，如果是0，结果就为0；
综合案例：如果薪水小于3500就输出“要努力”，如果等于3500就输出“刚刚好”，如果大于3500就输出“要纳税”
select ename,decode(sign(sal-3500),1,'要纳税',0,'刚刚好',-1,'要努力') from emp
3.4 聚合函数
sum、max、min、avg、count
作用：基于一组行来返回结果，为每一组行返回一个值
sum、max、min、avg、count
3.5 分析函数
ROW_NUMBER、RANK、DENSE_RANK
ROW_NUMBER返回连续的排位，不论值是否相等RANK具有相等值的行排位相同，序数随后跳跃DENSE_RANK具有相等值的行排位相同，序号是连续的
3.6 练习
--分析函数
--求出emp表中的前三条记录
SELECT A.*,ROWNUM FROM EMP A WHERE ROWNUM<4;
--求出emp的四到六条数据
SELECT * FROM(
SELECT A.*,ROWNUM RUM FROM EMP A
)B WHERE RUM BETWEEN 4 AND 6;
--查询工资最高的前三人（ROW_NUMBER() OVER() 排序函数）
SELECT * FROM(
SELECT A.*,ROW_NUMBER() OVER(ORDER BY SAL DESC) Z FROM EMP A
)B WHERE Z<4
--查询工资排序中较高的3到6人（ROW_NUMBER() OVER() 排序函数）
SELECT * FROM(
SELECT E.*,ROW_NUMBER() OVER(ORDER BY SAL DESC) MYR FROM EMP E
)T WHERE MYR BETWEEN 3 AND 6;
SELECT * FROM (
SELECT B.*,ROWNUM MYR FROM(
SELECT A.* FROM EMP A ORDER BY SAL DESC
)B
)C WHERE MYR BETWEEN 3 AND 6;
--日期函数
--系统当前时间 sysdate
SELECT SYSDATE FROM DUAL;--DUAL伪表
--将字符串变为date类型 to_date(s1)
SELECT TO_DATE('2020/1/1','YYYY/MM/DD') FROM DUAL;
SELECT TO_DATE('2020-1-1','YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('2020年1月1日','YYYY"年"MM"月"DD"日"') FROM DUAL;
--将日期变为字符类型 to_char(d1) 'yyyy-mm-dd hh24:mi:ss'
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM DUAL;
--求两个月之后是多久 add_months(d1,d2)
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;
--求两个月之前是多久
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;
--求2020-1-1的两个月之后多久
SELECT ADD_MONTHS(TO_DATE('2020/1/1','YYYY/MM/DD'),2) FROM DUAL;
--求现在的年份 extract(year from d1)
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
--求现在的月份
SELECT extract(MONTH from SYSDATE) FROM DUAL;
--求现在的天数
SELECT extract(DAY from SYSDATE) FROM DUAL;
--统计每个月入职的人数（分组函数前面显示的只能是分组依据或者聚合函数）
SELECT EXTRACT(MONTH FROM HIREDATE),COUNT(1) FROM EMP GROUP BY EXTRACT(MONTH FROM HIREDATE)
--统计每年的每个月入职的人数
SELECT EXTRACT(YEAR FROM HIREDATE),EXTRACT(MONTH FROM HIREDATE),COUNT(1) FROM EMP GROUP BY EXTRACT(YEAR FROM HIREDATE),EXTRACT(MONTH FROM HIREDATE);
--求员工入职到现在所经历的年/月/天
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 FROM EMP;--相差的年份
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP;--相差的月份
SELECT SYSDATE-HIREDATE FROM EMP;--相差的天数
--求2020-1-1到现在所经历的月份
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2020-1-1','YYYY-MM-DD')) FROM EMP;
--取当前月份的最后一天 last_day(d1)
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--倒数第二天
SELECT LAST_DAY(SYSDATE)-1 FROM DUAL;
--日期计算
SELECT SYSDATE-1 FROM DUAL;
SELECT SYSDATE-20 FROM DUAL;
--取下一个周五的下个周五的时间
SELECT NEXT_DAY(NEXT_DAY(SYSDATE,'星期五'),'星期五') FROM DUAL;
--取下一个周日的时间
SELECT NEXT_DAY(SYSDATE,'星期日') FROM DUAL;
--日期中的四舍五入 round(d1)，过了今天十二点就是明天
SELECT ROUND(SYSDATE) FROM DUAL;
--日期中的取整 trunc(d1) 只留日期
SELECT TRUNC(SYSDATE) FROM DUAL;
--字符串函数
--截取 substr(s1,i1,i2)
SELECT SUBSTR('ABCD',1,2) FROM DUAL;--第一个开始截取两个
--SUBSTR 下标从1开始 substr(string string, int a, int b)/substr(string string, int a)
--注意1、string 需要截取的字符串
-- 2、a 截取字符串的开始位置（当a等于0或1时，都是从第一位开始截取）
-- 3、b 要截取的字符串的长度
-- 4、只有a时，是从第a个字符开始截取后面所有的字符串。
select substr(20211119,0,4) from dual;--2021
select substr(20211119,1,4) from dual;--2021
select substr(20211119,1) from dual;--20211119
select substr(20211119,4) from dual;--11119
--长度 length(s1)
SELECT LENTH('ABCD我') FROM DUAL;
--查找 instr(s1,s2,i1) INDEXOF
SELECT INSTR('ABCD','B') FROM DUAL;
SELECT INSTR('ABCDB','B',3) FROM DUAL;--从第三个数起
SELECT INSTR('ABCDB','B',INSTR('ABCDB','B')+1) FROM DUAL;--和上边一样
--连接 concat(s1,s2)
SELECT 'AA'||'BB' FROM DUAL;
--替换 replace(s1,s2,s3)，把BABY的A换成钝角
SELECT REPLACE('BABY','A','钝角') FROM DUAL;
--左填充 lpad(s1,i1,s2)
SELECT LPAD('1',5,'0') FROM DUAL;
--右填充 rpad(s1,i1,s2)
SELECT RPAD('1',5,'0') FROM DUAL;
-- 去除空格 trim(s1) or replace
SELECT TRIM(' AA BB CC ') FROM DUAL;--只去两边
SELECT REPLACE(' AA BB CC ',' ','') FROM DUAL;全部去掉
--数字函数
--四舍五入 round(i1,i2)
SELECT ROUND(1.564646,3) FROM DUAL;
--取整 trunc(i1,i2)
SELECT TRUNC(4.65665,3) FROM DUAL;
--取模 mod(i1,i2)，10%3 = 1
SELECT MOD(10,3) FROM DUAL;
--向下取整 floor(i1)
SELECT FLOOR(4.65757) FROM DUAL;-- 4
SELECT FLOOR(-1.9) FROM DUAL;-- -2
--幂 power(i1,i2)
SELECT POWER(2,3) FROM DUAL;
--平方根 sqrt(i1)
SELECT SQRT(9.9) FROM DUAL;
--正数 sign(i) i>0就是1 i=0就是0 i<0就是-1
SELECT SIGN(1) FROM DUAL;
--绝对值 abs(i1)
--decode 判断decode(c1,a1,a2,b1,b2) ，如果c1=a1 输出a2，c1=b1 输出b2
--NVL(a,b)如果a为空，则为b
SELECT NVL(DECODE(3-1,1,'AA',2,'BB',3,'CC'),'DD') FROM DUAL;
--输出对应的部门编号
SELECT ENAME,DECODE(DEPTNO,10,'青楼',20,'驿站',30,'停马场') FROM EMP;
--工资大于3500提示交税 ，等于3500的提示刚好，小于3500提示你要继续努力为国家贡献
SELECT ENAME,DECODE(SIGN(SAL-3500,0,'刚好',1,'交税',-1,'为国家贡献') FROM EMP;
189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
