Oracle 基础基本操作(一) 增删改查
(一)数据库
- 数据库:是一个软件产品,用于存放数据管理数据的存储仓库;
(二)常见数据库
-
大型数据库:
- Oracle;甲骨文公司的数据库产品,商品化的关系型数据库 Oraclellg
- DB2; IBM公司的数据库产品;
- Sysbase; 美国的Sysbase公司的数据库产品,关系型数据库;
-
中小型数据库:
- Sql server
- MySQL 甲骨文收购,关系型数据库,开源;
-
小型数据库:
- Access ;微软发布的关联式数据库;
-
RDBMS 关系型数据库管理系统;
-
SQL:结构化标准语言;
-
DBA:数据库管理员;
-
数据库语言分类:
分类
- 数据定义语言 DDL : create,drop,alter,rename,truncate ————不能回滚 对表结构起作用;
- 数据操作语言 DML : insert,update,delete ————可以回滚;要commit才更新到数据库 对数据起作用;
- 数据控制语言 DCL : grant(授权),revoke(回收)
- 数据查询语言 DQL: select
- 事务控制语言 TCL : commit(提交),rollback(回滚),savepoint(保存点)
rollback:
没有commit的数据删除后无法rollback
commit了的数据删除后可以使用rollback恢复
删除数据后commit则无法使用rollback恢复。
drop:
放到回收站里面
-
Oracle数据库对象:
- 表(基本单元);
- 视图;
- 索引;
- 序列(MySQL中没有,Oracle中有);
- 存储过程;
- 触发器;
- 游标。
(三)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中常见的数据类型
- 数字类型:
- number(n):数字最长n位;
- number(n,m) :浮点数,n表示总长度,m表示小数点后位数;
- 字符串类型:
- char(n) :固定长度;不够空格补齐;
- varchar(n):变长;
- varchar2(n) :变长,Oracle特有类型;
- 日期:
- date;
- 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. 虚表
-
伪表
- Oracle提供的用于查询一个不存在于任何表中的数据。属于系统用户;
- 单行单列的表,存放常量
-
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
- 任何数据类型都可以取值为null;
- 空值与任何数据运算,结果都为null;
- 空值与顺风车进行拼接操作时,相当于不存在;
- 处理空值 函数: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()
- 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…,默认值)
-
通过匹配,满足条件句执行,如果都不匹配就执行默认;
-
计算员工的薪金
- 若职位是sales,则薪金涨3%;
- 若职位是clerker,则薪金涨5%;
- 若职位是developer,则薪金涨6%;
- 其他不变;
select enname,salary,job, decode( job , 'sales' , salary*1.03,'clecker',salary*1.05,'develpoer' , salary*1.06 ,salary ) new_sal from emp;
-