Oracle 基础学习笔记（一）增删改查

Oracle基础基本操作（一）增删改查（一）数据库数据库：是一个软件产品，用于存放数据管理数据的存储仓库；（二）常见数据库大型数据库：Oracle；甲骨文公司的数据库产品，商品化的关系型数据库OraclellgDB2; IBM公司的数据库产品；Sysbase;美国的Sysbase公司的数据库产品，关系型数据库；中小型数据库：Sql serverMySQL甲骨文收购，关系型数据库，开源；小型数据库

洛心尘8

873人浏览 · 2020-09-27 21:15:50

洛心尘8 · 2020-09-27 21:15:50 发布

Oracle 基础基本操作（一）增删改查

（一）数据库

数据库：是一个软件产品，用于存放数据管理数据的存储仓库；

（二）常见数据库

大型数据库：
1. Oracle；甲骨文公司的数据库产品，商品化的关系型数据库 Oraclellg
2. DB2; IBM公司的数据库产品；
3. Sysbase; 美国的Sysbase公司的数据库产品，关系型数据库；
中小型数据库：
1. Sql server
2. MySQL 甲骨文收购，关系型数据库，开源；
小型数据库：
1. Access ;微软发布的关联式数据库；
RDBMS 关系型数据库管理系统；
SQL：结构化标准语言；
DBA：数据库管理员；
数据库语言分类：

分类
1. 数据定义语言 DDL ： create，drop，alter，rename，truncate ————不能回滚对表结构起作用；
2. 数据操作语言 DML : insert，update，delete ————可以回滚；要commit才更新到数据库对数据起作用；
3. 数据控制语言 DCL ： grant（授权），revoke（回收）
4. 数据查询语言 DQL： select
5. 事务控制语言 TCL ： commit（提交），rollback（回滚），savepoint（保存点）

rollback：
没有commit的数据删除后无法rollback
commit了的数据删除后可以使用rollback恢复
删除数据后commit则无法使用rollback恢复。
drop：
放到回收站里面

Oracle数据库对象：
1. 表（基本单元）；
2. 视图；
3. 索引；
4. 序列（MySQL中没有，Oracle中有）；
5. 存储过程；
6. 触发器；
7. 游标。

（三）Oracle数据库的基本操作

1. 创建用户

1. 创建子用户并授权

创建子用户：

create user  用户名 IDENTIFIED BY 用户密码;

授权
```
grant resource,connect to 用户名;
```

角色/权限

//授予角色（权限的集合）
grant connect,resource to 用户名;
    - CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。
    - RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。
    - DBA角色，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。
    一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。

//授予某一项权限
grant create view to 用户名;

//撤销角色/权限
revoke 角色|权限 from 用户名;

//查看自身有哪些角色
select * from user_role_privs;

//查看自身的角色和权限
select * from role_sys_privs;

//修改用户处于锁定（非锁定）状态，锁定状态是不能登录的
alter user 用户名 account lock|unlock;

查询用户
```
select * from all_users;
```

用户登录

sqlplus 用户名/密码 [@host_string] [用户身份];

//普通用户
sqlplus 用户名/密码；

//系统管理员登录，必须指定身份，as sysdba或者as sysoper，可以不输入密码也能正常登陆
sqlplus sys/ as sysdba;

用户登录失败原因：

被锁定；
没有登录权限；
忘记密码；
用户不存在；

切换用户
```
connect 用户名/密码;
```

2. Oracle中常见的数据类型

数字类型：
1. number（n):数字最长n位；
2. number（n，m) :浮点数，n表示总长度，m表示小数点后位数；
字符串类型：
1. char(n) :固定长度；不够空格补齐；
2. varchar(n)：变长；
3. varchar2（n) :变长，Oracle特有类型；
日期：
1. date；

3.表操作

创建一个表（格式快捷键 Ctrl +F7）

//方式一：
create table [用户名.]表名(
    列名 数据类型 [default 默认值][, ...]
)

create table 表名(
    列名1 char(10), --注释
    列名2 varchar2(20),
    列名3 date,
    列名3 integer,
    列名3 number（6,2） --整数最多4位，小数最多2位
)；

//方式二：利用子查询创建表，相当于复制操作
create table 表名[列名1，列名2...] as 子查询;

create table B as select * from A;

CREATE TABLE emp
  (
    empno    NUMBER(5),//员工编号
    enname   VARCHAR(20),//员工姓名
    job      VARCHAR(20),//职位
    salary   NUMBER(5),//薪金
    bonus    NUMBER(5),//奖金
    deptno   NUMBER(5),//部门编号
    hiredate DATE,//入职日期
    mgr      VARCHAR(20)//上级
  );
CREATE TABLE dept
  (
    deotno   NUMBER(5),//部门编号
    dname    VARCHAR(20),//部门名称
    location VARCHAR(20)//地点
  );

查询表
```
desc 表名
```
截断表
- 删除表数据，保留表结构，数据无法恢复。
```
truncate table 表名
```

删除表

//1、闪回删除————10g新特性
drop table 表名

//查看回收站
show recyclebin;

//从回收站恢复表
flashback table 表名 to before drop;

//清空回收站
purge recyclebin;

//2、彻底删除(级联删除 + 不放回收站)
drop table 表名 [cascade contraints] [purge]

修改表

插入新列 add

alter table 表名 1
add 列名1 数据类型 [default 默认值] [，列名n...]

alter table A
add age number(2) default 23;

删除列 drop；

alter table 表名 
drop column 列名;

（四）常见数据操作

1.清屏

CLEAR scr

2. 插入

插入

insert into 表名 values(值1,值2...);
insert into 表名 (列名1,列名2...) values(值1,值2...);

插入date型:

insert into 表名 (列名1) values(to_date('1999-09-09','yyyy-MM-dd'))：

3.更新

更新数据

一般更新、基于一个表来更新、利用多列子查询来更新。

update 表名 set 列名=数值[,列名=数值] [where 条件];

update A set age=13,name='Tom' where id=1;

4. 删除

删除数据

语句用于删除表中的行。

delete from 表名 [where 条件]

//删除所有行
delete from 表名  
delete * from 表名

5. 虚表

伪表
1. Oracle提供的用于查询一个不存在于任何表中的数据。属于系统用户；
2. 单行单列的表，存放常量
3. SQL的标准语法规定，查询语句必须至少有2个子句，即SELECT子句和FROM子句， Oracle数据库中就提供了一个表以便从语法上支持查询语句的完成,即dual表。
```
desc dual;
select sysdate from dual;
select length('abc') from dual;
```
2、伪列

Oracle数据库为了增强其功能，提供了一组数据列，这些列是由Oracle数据库自动创建的，从形式上看这些列与表的普通列没有什么区别，但实际上它们并不存储在表中，可以使用查询语句从这些列中查询到数据，但是不能对其进行插入、更新或删除操作，因为这些列不是真实的存在于表中，因此称之为伪列。

6. 约束CONSTRAINT

约束CONSTRAINT
- 注：外键的值允许为空，如果插入值，则必须能在父表中找到。先建父表再建子表。

7. Oracle数据库中的NULL

Oracle数据库中的NULL
1. 任何数据类型都可以取值为null；
2. 空值与任何数据运算，结果都为null；
3. 空值与顺风车进行拼接操作时，相当于不存在；
- 处理空值函数：nvl( );
  - nvl(d1,d2) :如果d1为null；那就显示d2;
  - nvl函数的两个参数可以是数组，字符，日期，但是两个参数数据类型必须一致；

8. SQL查询

SQL查询语句

SELECT [ ALL | DISTINCT ] [ * | [table.* | expr[alias] | view.*] [, [ table.* | expr[alias]]]...]
   FROM table [ alias ][ ,table[ alias ] ]...
   [ WHERE condition]
   [ GROUP BY expr [, expr] ...] 
   [ HAVING condition]
   [ ORDER BY expression  [ ASC | DESC ] ]

使用算术表达式 ±*/
```
select 列名+20 from 表名;
```

取别名 as

//为列取别名
select 列名 as 别名 from 表名;
select 列名 别名 from 表名;
select 列名 "别名" from 表名;  

//为表取别名
select * from 表名 别名;

通常情况下，不需要用双引号（" "）将列别名括起来，但是以下三种情况，列别名需要使用双引号引起来：
① 列别名中含有空格时。
② 想让别名原样显示时（不用双引号则英文字符全部大写）。
③ 列别名中含有特殊字符时。

连接运算符 ||

select 列名 || 'abcdefg' from 表名;

--6：从学生表中查询学生的学号和姓名，并在学号和姓名的前面分别加上字符常量“学生学号”和“学生姓名”。
select '学生学号:'||s_id,'学生姓名:'||s_name from student;

（4）去重 distinct ，distinct 必须跟在select后面；

select distinct 列名 from 表名;

--查询员工表中有哪些职位?
select distinct job from emp;
--查询员工分布在哪些部门?
select distinct deptno from emp;
--查询每个部门不重复的职位有哪些？
select distinct deptno,job from emp;

（5）过滤 where

select * from 表名 where 列名 比较操作符 数值|表达式|列名;

WHERE Customer='Bush' OR Customer='Adams'
WHERE Address IS NULL
WHERE Address IS NOT NULL
WHERE Year>1965
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
WHERE City LIKE '%lon%'
WHERE City NOT LIKE '%lon%'

（6）通配符 % _

% ：0个或多个字符；
_ ：1个字符
[charlist]：字符列中的任何单一字符

[^charlist]或者[!charlist] ：不在字符列中的任何单一字符；

select * from 表名 where 列名 like 's_a%y';
select * from 表名 where 列名 not like 's_a%y'; 


//以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons WHERE City LIKE '[ALN]%'

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

//转义escape,可以将一个字符当成是转义字符
select * from 表名 where 列名 like 's\_a\%y' escape '\';
select * from 表名 where 列名 like 's$_a$%y' escape '$';

（7）排序 order by

select * from 表名 order by 列名 ASC|DESC;
select 列名 别名 from 表名 order by 别名;
select * from 表名 order by 列名1 ASC, 列名2 DESC,列名3 DESC;
select 列名1 列名2 from 表名 order by 2;              //根据列名2进行排序

（8）分组 group by

将行分组为具有相同列值的多个部分，大多数与聚合函数一起出现。如果查询中包含一个聚合函数，而同时有查询的列并不在聚合函数中，那么这个列必须在group by子句中。

select gender,count(gender) from A group by gender
select job,avg(sal) from A group by job 

//语法错误
select gender,count(gender) from A

select 后面出现的字段，如果没有被组函数应用，则必须出现在group by 后面；

--按部门计算，每个部门的最高最低薪金分别是多少？
select deptno,max(salary),min(salary) from emp group by deptno;

（9）聚合比较 having

聚合函数作比较要放在having中，而不能放在where中。分组->计算聚合->聚合比较。

select job,avg(sal) from A where gender='man' group by job having avg(sal)>1500

（五）SQL函数

需要select或者having才能返回结果，在where中是不返回结果的。

1. 单行函数

1. 字符函数

    //将第一个字母转成大写
    initcap(n)

    //从m位置开始在x中查找字符串y出现的位置，n是出现次数
    instr(x,y,m,n)

    //求字符串长度
    length(n)
    
    //将字符串各字符转换成小写
    lower(x)

    //大写......
    upper(x)

    //在字符串x左边补齐字符y（缺省则补空格），得到总长为n的字符串
    lpad(x,n,y)

    //在字符串x右边......
    rpad(x,n,y)

    //去掉左边\右边\两边去掉指定字符
    select trim(leading '*' from '**sbash**') from dual;

    //x左边去掉指定字符y，默认去空格
    ltrim(x,y)
    //x右边......
    rtrim(x,y)

    //如果x不是null，则返回x，否则返回y
    nvl(x,y)
    //如果x不是null，则返回y，否则返回z
    nvl2(x,y,z)
    //如果x是数字，则返回x，否则返回y
    nanvl(x,y)

    //x中替换y为z
    replace(x,y,z)
    //从字符串x中的m开始取长度为n的子串，n缺省时取到结尾
    substr(x,m,n)
    //连接字符串
    concat(m,n)

2. 数字函数

在这里插入图片描述

round（d1,d2)：四舍五入

d1:要处理（四舍五入）的数据；可以是表达式，函数；
d2:（正整数表示保留的位数）；

round(5.89,-1)       // 10 保留十位数
round(5.89)          // 6 保留个位数
round(5.89,1)        // 5.9
round(15.89,-1)      // 20
round(15.89,-2)      // 0

trunc：截断

trunc(5.89,-1)       // 0
trunc(5.89)          // 5
trunc(5.89,1)        // 5.8
123

3. 转换函数

to_char(日期数据，格式) ：日期----->字符串；
to_date( 字符数据,格式) : 字符串---->日期：

//转换为日期
select to_date('2018-06-18','yyyy-MM-dd') from dual

//转为为二进制数
bin_to_num(n)

//2018-06-18 09:35:45
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual

coalesce(): 返回列表中第一个非空参数，参数列表中的最后一个是常量；

4. 日期函数

//获取系统时间
select sysdate from dual

--求这个月最后一天；
select last_day(sysdate） from dual; //2020-09-30

--求下个月最后一天
select last_day(add_months(sysdate,1)) from dual;//2020-10-31

--从x开始，下一个第n天的日期（从星期天开始算）
select next_day(sysdate,n) from dual

select months_between(sysdate,sysdate+1) from dual

--2020-09-22	2021-01-01	2020-10-01
select round(sysdate),round(sysdate,'yyyy'),round(sysdate,'MM') from dual;

--2020-09-22	2020-01-01	2020-09-01
select trunc(sysdate),trunc(sysdate,'yyyy'),trunc(sysdate,'MM') from dual

--2020-09-22 11:13:10
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual

计算日期：

日期计算是以天为单位的，不足一天用小数表示；

select enname,hiredate,round(sysdate-hiredate) days from emp;

2. 聚合函数

//count，返回非空数据的条数
select count(*) from A

//min、max求最值
select min(列名),max(列名) from A

//sum求和，avg求均值
select sum(列名),avg(列名) from A

3.其他

coalesce()
1. coalesce(): 返回列表中第一个非空参数，参数列表中的最后一个是常量；
case语句(字段)when 表达式1 then 值1
when 表达式2 then 值2

else 值n

end+类别名;

    
    select nname,salary,job,
              case job when 'sales' then salary*1.03
              when 'clecker' then salary*1.05
              when 'develpoer' then salary*1.06
              else salary 
              end new_sal
              from emp;

decode(判断表达式，匹配1，值1，匹配2，值2…，默认值)
1. 通过匹配，满足条件句执行，如果都不匹配就执行默认；
2. 计算员工的薪金
  1. 若职位是sales，则薪金涨3%；
  2. 若职位是clerker，则薪金涨5%；
  3. 若职位是developer，则薪金涨6%；
  4. 其他不变；
```
select enname,salary,job,
 decode( job , 'sales' , salary*1.03,'clecker',salary*1.05,'develpoer' 
 , salary*1.06 ,salary ) new_sal from emp;
```

参考博客

AtomGit 开源协作平台测评赛

瓜分20万奖金获得内推名额丰厚实物奖励易参与易上手

更多推荐

Spring面试题整理

Spring 概述1. 什么是spring?spring 是个Java企业级应用的开源开发框架。Spring主要用来开发Java应用，但是有些扩展是针对构建J2EE平台的web应用。Spring 框架目标是简化Java企业级应用开发，并通过POJO为基础的编程模型促进良好的编程习惯。2. 使用Spring框架的好处是什么？轻量：Spring 是轻量的，基本的版本大约2MB。控制反转：Spring通

开放原子开发者工作坊

Macaca自动化测试框架

介绍Macaca是一套完整的自动化测试解决方案，基于node.js开发。由阿里巴巴公司开源：地址：https://github.com/macacajs/特点：同时支持PC端和移动端（Android、iOS）自动化测试。支持JavaScript（Node.js）、Java、Python。前置准备安装JavaJava下载地址：(http