Oracle学习笔记
发布时间：2020-12-12 14:26:29 所属栏目：百科 来源：网络整理
导读：简介 Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大
|
简介 Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的 适应高吞吐量的数据库解决方案。 Sql分类 DQL(数据查询语言) select DDL(数据定义语言) create,alter,drop， truncate DML(数据操控语言) insert,update,delete DCL(数据控制语言) grant(授权),revoke(回收权限) TCL(事务控制语言) commit(提交),rollback(回滚),savepoint(保存点) 常用数据类型 varchar2 最大值：4000字节 char 最大值：2000字节 number(最大长度) number(最大长度，小数位数) date 年月日时分秒 timestamp(时间戳) 精确到纳秒 clob(字符大对象) 4GB * 块大小 blob(二进制大对象) 4GB * 块大小 约束：用于保证数据的有效性和合法性 [constraint 约束名](可选) primary key 主键约束，表示唯一非空，且一张表只能有一个主键 not null 非空约束 unique 唯一约束 check(条件) 检查约束 references 表名(列名) 外键约束(该列必须唯一) primary key[unique](列1，列2) 复合约束，即两列合在一起唯一，但分别不唯一 默认值 default '男' 处于数据类型后，约束前 复制表：除了not null其它约束都不会复制 create table emp_my as select * from emp; 实例1：建表语句 create table users( username varchar2(20) primary key,password varchar2(20) not null,name varchar2(20) not null,zip number(6) check (length(zip) = 6),email varchar2(20) unique,married number(1) default 1 check (married = 1 or married = 0),birthday date ); user_tables(系统表) 保存当前用户创建的表的信息 user_constraints、user_cons_columns(约束表) 保存当前用户创建的约束信息 column(clo) 控制列的宽度 col 列名 format 9999 col 列名 format a12 / 可以重复执行当前命令 list(l) 可以查看当前命令 change(c)/oldstr/newstr 修改列名(针对查询出来的记录) start(@) xx.sql 可以批量执行sql语句 desc 表名 可以查看表的详细信息 save 路径/文件名 可以保存最后一条sql命令 help index 可以查看所有的sqlplus命令 set pagesize 2000 设置分页显示记录数量 set timing on 显示某条sql语句所耗时间 对于字符串的比较 1、用单引号表示字符串 2、严格区分大小写 like(模糊查询) select * from emp where ename like 'S%'; S%:S开头 %A%:包含A %A:A结尾 _A%:一个_代表一个字符 dual:Oracle特殊表，只有一行一列 通过特殊表做数学运算(不能取余) select 100+200 from dual; dbms_random.random() 产生随机数 sysdate(是一个变量) 得到当前时间 to_char(日期，格式化字符串) 将日期转换成字符串 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day q(季度) am') from dual; to_date(字符串，'格式化字符串') 将字符串转换为日期 to_date('1981-12-31','yyyy-mm-dd') add_months(日期，整数） 月分加减 select add_months(sysdate,1) from dual; trunc(日期，'year|month|day') 截断日期 select trunc(sysdate,'year')from dual; 实例2：求本月第一天是星期几 select to_char(trunc(sysdate,'month'),'day') from dual; NULL值：NULL+数字=NULL、NULL||'abc'='abc' 判断列值是否为空用is (not) null nvl(null,0) 做null值转换 计算年薪 select ename,sal,(sal+nvl(comm,0))*12 as anuasal from emp; case语句： select empno,ename,case when sal<1000 then 'low' when sal>=1000 and sal<=2500 then 'middle' else 'high' end as grad from emp; group by规则： 1、只有在group by中出现的列才能出现在select子句中。 2、没有出现在group by中的列，要想出现在select中必须配合组函数使用 select max(sal),deptno from emp group by deptno; order by：用于按某列排序 asc 升序排列 desc 降序排列 实例3：查询1981年各个月份入职的员工个数 select count(*)，to_char(hiredate,'mm') from emp where to_char(hiredate,'yyyy')='1981' group by to_char(hiredate,'mm'); 执行顺序 from > where > group > having > select > order by while与having比较 while效率比having高，但是当分组之后的数据需要条件比较时只能用having select avg(sal),deptno from emp group by deptno having(avg(sal)>2000); 伪列 rowid:唯一标识一条记录 rownum:对查询记录进行编号 rownum条件比较只能用于< 、<=比较 子查询 1、查询结果是一个单个值 2、查询结果是一行多列 3、查询结果是多行多列,可以将其看成临时表 实例4：求工资排名6-10的员工信息 select * from(select e.*,rownum r from (select empno,sal from emp order by sal desc) e where rownum <=10) where r > 5; 连接查询 select empno,dname,e.deptno from emp e,dept d where e.deptno = d.deptno; 等价于select empno,e.deptno from emp e inner join dept d on (e.deptno = d.deptno); 内连接：两张表中能够匹配的上的记录才会出现在查询结果中 外连接：跟表的连接顺序有关 分两种：left outer join（左外连接，左边的表中所有记录不管与右边表是否匹配都会出现在最终结果中） right outer join（右外连接） select empno,e.deptno from emp e left outer join dept d on (e.deptno = d.deptno); 等价于select empno,dept d while e.deptno = d.deptno(+);(oracle特殊写法，+指连接右边的表，只做了解) 笛卡尔连接 select empno,dname from emp e,dept d; 等价于select empno,dname from emp e cross join dept d; 多表连接 select o.orderid,o.orderdate,o.totalprice,i.qty,p.productname,p.price from orders o inner join orderitem i on (o.orderid = i.orderid) inner join product p on (i.productid = p.productid) where o.username = 'xxx'; 实例5：求每个部门最高工资的员工信息 select e.* from (select max(sal) msal,deptno from emp group by deptno) d inner join emp e on (d.deptno = e.deptno and d.sal = e.sal); 或 select * from emp e where sal = (select max(sal) msal from emp where deptno = e.deptno); 实例6：查询工资相同的员工的详细信息 select * from emp where sal in(select sal from emp group by sal having(count(*)>1)); 实例7：按工资进行排名 select empno,(select count(*)+1 from emp where sal > e.sal) from emp e; 实例8：求每个部门工资最高的前两名信息 select * from (select empno,deptno,(select count(*)+1 from emp where sal > e.sal and deptno > e.deptno) rank from emp e order by deptno,rank)where rank <=2; 等价于(oracle特有解法) select empno,rank() over(partition by deptno order by sal desc) r from emp; rank():松散排名 dense_rank():紧密排名 第一范式：表中的列的取值应是原子的，不可再分 第二范式：表的设计中不应当存在部分依赖于主属性的属性 stuid,courseid 主属性 stuname依赖于stuid 部分依赖 coursename依赖于courseid 部分依赖 score依赖于stuid,courseid 全部依赖 stuid stuname courseid coursename score 1001 zhangsan 2001 java 90 1001 zhangsan 2002 oracle 80 1002 lisi 2001 java 70 1002 lisi 2002 oracle 60 第三范式：表的设计中不能存在间接依赖的属性 事务：表示一个原子的不可分割的操作 将一条到多条DMLSQL(insert,delete)看做一个整体，如果操作都成功，则提交(commit)，有一条执行失败则撤销(rollback) DDL、DCL语句(create,drop,grant,revoke)，如果之前没有事务，开始，如果有，加入。并且执行成功会自动提交事务。 事务的4大特性 原子性 A 一致性 C：事务开始前后数据库的状态是一致的 隔离性 I：避免脏数据的出现 set transaction isolation level serializable设置事务隔离级别 持久性 D：事务结束后，数据的状态应当永久保存 添加列：如果表中已有记录，该列如果需要not null约束，则可以添加默认值 alter table 表名 add (列名1 数据类型 约束,列名2...) alter table users add (address varchar(20),telephone number(11)); 删除列 alter table 表名 drop column 列名 alter table users drop column address; 列重命名 alter table users rename column telephone to phone; 修改列类型 alter table users modify mail varchar2(30);//原本是20 修改约束 alter table users modify mail not null; 添加约束 alter table users add [constraint 约束名] 约束类型 添加主键约束 alter table orders_my add constraint orders_my_username_fk foreign key(username) references users_my(username); 禁用/启用约束 alter table users disable[enable] constraint 约束名 删除约束 alter table users drop constraint 约束名 删除数据时可能发生违反外键约束的情况 1、先删从表再删主表 2、删除约束 3、级联删除 级联删除：如果在建立外键时加上on delete cascade选项，则删除主表记录时会自动删除从表的记录 alter table orders_my add constraint orders_my_username_fk foreign key(username) references users_my(username) on delete cascade; truncate table 表名 删除表中所有的记录(不开启事务，不能恢复) delete from 表名(开启事务删除，可恢复) 删除表的同时先级联删除与此表有关的外键约束 drop table users_my cascade constraint; 授权语法 grant select on orders_my to xxx; 回收权限 revoke select on orders_my from xxx; 创建用户 创建用户后不能直接登录 create user 用户名 identified by 密码 授权登录权限，可以登录但是不能创建表 grant create session to 用户名 授予创建表权限，但没有表空间权限 grant create table to 用户名 角色：包含了多个常用的权限 connect、resource grant connect to 用户名 grant resource to 用户名 修改密码 alter user 用户名identified by 新密码; 每个用户(一个模式schema) 表、约束、函数、序列(sequence)、视图、索引 序列：保证主键值唯一(类似于mysql主键自增) create sequence 序列名；默认从1开始 获得序列的下一个值 select 序列名.nextval from dual; currval取到当前值 create sequence 序列名 start with 100001 increment by 5; 默认值为100001，递增值为5 关于sequence的数据字典 select sequence_name from user_sequences; 创建视图：创建出的是一个虚拟的表，当查询视图时实际上相当于查询的表 create view emp_view as select * from emp; 作用：简化复杂的sql语句的书写 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |