干货 | Oracle数据库操作命令大全，满满的案例供你理解，收藏！
干货 | Oracle数据库操作命令大全，满满的案例供你理解，收藏！
- ?数据库技术
- ?前言
- ?一、数据库发展史
- ?1.1 程序管理阶段
- ?1.2 文件系统阶段
- ?1.3 数据库系统阶段
- ?二、数据库专业术语
- ?2.1 关系
- ?2.2 元组
- ?2.3 属性
- ?三、数据库及连接工具介绍
- ?3.1 Oracle数据库介绍
- ?3.2 连接工具介绍
- ?3.3 Oracle数据库登录
- ?四、表空间
- ?五、数据类型
- ?5.1 字符类型
- ?5.3 日期类型
- ?六、数据库语句
- ?6.2 SQL语句分类
- ?6.3 创建表
- ?6.4 查看表结构
- ?6.5 插入语句
- ?6.6 修改(更新)语句
- ?6.7 删除表中的数据
- ?6.8 删除表
- ?6.9 查询语句(重点)
- ?6.9.15 事务控制语句
- ?七、约束(constraint)
- ?7.1 主键约束(primary key)
- ?7.2 唯一约束(unique)
- ?7.3 检查约束(check)
- ?7.4 默认值约束(default)
- ?7.5 非空约束(not null)
- ?八、索引(index)
- ?九、序列(sequence)
- ?十、视图(view)
- ?10.1 创建视图
- ?10.2 视图查询
- ?10.3 修改视图
- ?10.4 通过视图实现对表的操作
- ?10.5 只读视图(-重点)
- ?10.6 删除视图
数据库技术
前言
T1.什么是数据库?
存储数据的仓库
T2.常见的数据库有哪些?
- 微软-------SQLServer
- 微软-------Access
- IBM-------DB2
- 甲骨文-----Oracle
- 甲骨文-----MySQL
- 国产-------人大金仓
T3.生活中那些地方使用数据库?
- 超市商品管理系统---商品信息和价钱
- 银行管理系统----账户信息
- 医院挂号系统----用户信息
- 网上商城系统-商品信息和价钱和等级
- 12306---------账户信息和车次信息
一、数据库发展史
1.1 程序管理阶段
20世纪50年代中期
特点:不能长期存储数据
1.2 文件系统阶段
20世纪50年代后期---20世纪60年代后期
特点:数据缺乏独立性
1.3 数据库系统阶段
20世纪60年代后期--
特点:数据实现共享,减少冗余
二、数据库专业术语
2.1 关系
一个关系就是一张2维表(Excel)
2.2 元组
在2维表中,一行叫做元组,或者称为
“行”或者 “记录”
2.3 属性
在2为表中,一列叫做属性,或者称为
“列”或者“字段”
三、数据库及连接工具介绍
3.1 Oracle数据库介绍
Oracle是美国ORACLE公司研发的一款关系型数据库,主要特点有:速度快、安全性强、可跨平台,主要版本有:Oracle9i、Oracle10g、Oracle11g、Oracle12c
3.2 连接工具介绍
- SQL Developer--Oracle自主研发
- PL/SQL Developer
- Navicat
3.3 Oracle数据库登录
- 登录到DOS窗口
开始>搜索程序和文件>cmd>回车 2. 在DOS窗口中输入:sqlplus>回车 3. 请输入用户名:system>回车 4. 输入口令:123456>回车
说明:如果出现连接到:Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL>表示登录成功
四、表空间
表空间就是数据库中存储对象(2维表、视图、索引、序列)的容器,因为主要存储2维表(表),所以称为表空间
- 在数据库安装的时候,系统会自动创建表空间(system)
- 可以通过system表空间,创建其它表空间
- 一个数据库中,可以有若干个表空间,也可以只有一个system表空间。
五、数据类型
小说类型=>都市、玄幻、穿越、仙侠
5.1 字符类型
char、varchar、varchar2
char(n) 表示固定长度的字符串,n表示字符串的长度,当实际要保存的数据小于n时,在字符串的右侧,使用空格补齐。
例如: char(10) 存储zhangxs,会占用系统10个存储空间(7+3)
varchar(n) 表示可变长度的字符串,n表示字符串的最大长度,当要保存的数据小于n时,按照实际长度保存。
varchar2,是Oracle自定义的类型,比varchar数据类型兼容性更好,一般在企业开发中,常用varchar2代替varchar
5.2 数值类型
number
number(n) 表示整数类型,n表示能保存整数的最大位数
例如: number(3) 999 88 5
number(n,m) 表示整数或者小数,n表示有效数字的最大位数,m表示小数的最大位数,n-m表示整数的最大位数
例如: number(7,2) 12345.3 1000
5.3 日期类型
date 表示日期类型,包含:年月日时分秒
系统默认日期格式: dd-mon月-yy
- dd----表示几号
- mon---表示几月
- yy-----表示那年
六、数据库语句
6.1 SQL语句
结构化查询语句,通过SQL语句,可以对数据库进行增删改查。
6.2 SQL语句分类
DDL--数据定义语句,主要是对数据库中的表,创建、删除、修改
- 创建----create
- 修改----alter
- 删除----drop
DML--数据操纵语句,主要是对数据库表中的数据,插入、修改(更新)、删除
- 插入---insert
- 更新---update
- 删除---delete
TCL--事务控制语句,主要负责数据库中的事务。
- 提交事务---commit
- 回滚事务---rollback
DQL--数据查询语句,数据库中的重点
- 查询---select
6.3 创建表
格式:
create table 表名(
列名1 数据类型 primary key,
列名2 数据类型,
......
列名n 数据类型
);
create table per01(
id number(8) primary key,
name varchar2(30),
age number(3),
sex char(3)
);
create table per02(
id number(8) primary key,
name varchar(30)
);
练习:创建一张表,表名为per03,表中包含的字段有:
id number(8) primary key
name varchar2(30)
sex char(3)
address varchar2(50)
create table per03(
id number(8) primary key,
name varchar2(30),
sex char(3),
address varchar2(50)
);
练习:创建一张表,表名为ter01,表中包含的字段有:
id number(4) primary key
name varchar2(30)
age number(3)
sex char(3)
sal number(7,2)
address varchar(50)
ttime date
create table ter01(
id number(4) primary key,
name varchar2(30),
age number(3),
sex char(3),
sal number(7,2),
address varchar(50),
ttime date
);
6.4 查看表结构
格式: desc 表名;
案例:查看per01表的,表结构
desc per01;
练习:查看per03表的,表结构
desc per03;
6.5 插入语句
6.5.1 向全部列插入数据
格式:
insert into 表名 values(列值1,列值2,列值3.....列值n);
说明:values中的列值必须和表结构中的列名是一一对应的(数量、顺序、类型)
验证: select * from 表名;
案例:向per01表中,插入2条记录
desc per01;
insert into per01 values(1001,'张三',23,'男');
insert into per01 values(1002,'李四',30,'女');
select * from per01;
练习:向per02表中插入2条记录,并验证
(1)desc per02;
(2)insert into per02 values(101,'奥利根');
(3)insert into per02 values(102,'奥利奥');
(4)select * from per02;
练习:向per03表中,插入2条记录,并验证
desc per03;
insert into per03 values(1,'王五','男','北京');
insert into per03 values(2,'赵六','男','天津');
select * from per03;
6.5.2 向指定列插入数据
格式:
insert into 表名(列名1,列名2,列名3.....列名n) values(列值1,列值2,列值3.....列值n);
说明:表名中的列名必须和values中的列值是一一对应的(数量,顺序,类型)。
验证:select * from 表名;
案例:向per01表中插入2条记录
ID NAME AGE SEX
9001 tom 20
9002 rose 女
9003 25 男
insert into per01(id,name,age) values(9001,'tom',20);
insert into per01(id,name,sex) values(9002,'rose','女');
select * from per01;
insert into per01(id,age,sex) values(9003,25,'男');
练习:向per03表中,插入数据并验证
ID NAME SEX ADDRESS
801 jack 男
802 女 上海
803 lucy 广州
insert into per03(id,name,sex) values(801,'jack','男');
insert into per03(id,sex,address) values(802,'女','上海');
insert into per03(id,name,address) values(803,'lucy','广州');
select * from per03;
6.6 修改(更新)语句
格式:
update 表名 set 列名1=该列新值, 列名2=该列新值,.....列名n=该列新值 where 条件;
说明:如果没有where条件,修改全部数据。
验证:select * from 表名;
案例:修改per03表中,编号(id)是801的记录,将地址(address)修改为深圳
update per03 set address='深圳' where id=801;
select * from per03;
案例:修改per03表中,地址(address)是上海的记录,将姓名(name)修改为rose
update per03 set name='rose' where address='上海';
练习:修改per03表中,姓名(name)是lucy的记录,将性别(sex)修改为女
update per03 set sex='女'
where name='lucy';
练习:修改per01表中,编号是9003的记录,将姓名(name)修改为smith,年龄(age)修改为99岁
update per01 set name='smith',age=99 where id=9003;
练习:修改per01表中,将全部年龄(age)修改为18岁
update per01 set age=18;
6.7 删除表中的数据
格式:
delete from 表名 where 条件;
说明:如果没有where条件,删除全部数据。
案例:删除per01表中,将编号(id)是9001的记录删除
delete from per01 where id=9001;
练习:删除per01表中,将姓名(name)是rose的记录删除
delete from per01 where name='rose';
练习:删除per01表中全部数据
delete from per01;
6.8 删除表
格式:drop table 表名;
案例:删除per01、per02、per03表
drop table per01;
select * from per01;
复习:
- 创建表
create table 表名(
列名1 数据类型 primary key,
列名2 数据类型,
列名3 数据类型,
......
列名n 数据类型
);
- 查看表结构
desc 表名;
- 向全部列插入数据
insert into 表名 values(列值1,列 值2,........列值n);
- 向指定列插入数据
insert into 表名(列名1,列名2,...列名n) values(列值1,列值2,...列值n);
- 修改(更新)语句
update 表名 set 列名1=该列新值,列名2=该列新值,....列名n=该列新值 where 条件;
- 删除表中的数据
delete from 表名 where 条件;
- 删除表
drop table 表名;
6.9 查询语句(重点)
6.9.1 没带条件的查询语句
1)查询全部列数据
select * from 表名;
说明: *表示全部列
案例:查询emp表、dept表、salgrade表全部数据
select * from emp; ---员工表
select * from dept; ---部门表
select * from salgrade; -工资等级表
2)查询指定列数据
格式:
select 列名1,列名2,....列名n from 表名;
案例:查询emp表中,员工的编号(empno)、员工的姓名(ename)、员工的工资(sal)
select empno,ename,sal
from emp;
案例:查询dept表中,部门的编号(deptno),部门名称(dname),部门地址(loc)
select deptno,dname,loc
from dept;
练习:查询emp表中,员工的编号(empno),姓名(ename),职位(job),工资(sal),入职时间(hiredate)
select empno,ename,job,sal,hiredate
from emp;
练习:查询salgrade表中,工资的等级(grade),最低工资(losal)、最高工资(hisal)
select grade,losal,hisal
from salgrade;
6.9.2 给列起别名
格式1:
select 列名1 as 别名1,列名2 as 别名2,.....列名n as 别名n from 表名;
案例:查询emp表中,员工的编号(empno),员工姓名(ename),领导编号(mgr),入职时间(hiredate),并给每列起别名
select empno as 员工编号,ename as 员工姓名,mgr as 领导编号,hiredate as 入职时间
from emp;
格式2:
select 列名1 别名1,列名2 别名2,.....列名n 别名n from 表名;
练习:查询dept表中,部门编号(deptno),部门名称(dname),部门地址(loc),并给每列起别名
select deptno 部门编号,dname 部门名称,loc 部门地址
from dept;
6.9.3 去掉重复的列值(distinct)
格式:
select distinct 列名 from 表名;
案例:查询emp表中,员工的职位(job)信息(去重)
select distinct job from emp;
练习:查询emp表中,部门的编号(deptno)(-去重)
select distinct deptno from emp;
6.9.4 排序(order by)
格式:
select */列名 from 表名
order by 列名1 asc/desc,列名2 asc/desc;
说明:
- asc----升序排列(默认)
- desc---降序排列
案例:查询emp表中,员工的编号(empno),姓名(ename),工资(sal),根据员工的编号降序排列
select empno,ename,sal
from emp
order by empno desc;
练习:查询emp表中,员工的姓名(ename),职位(job),工资(sal),部门编号(deptno),根据工资升序排列
select ename,job,sal,deptno
from emp
order by sal asc;
练习:查询emp表中,员工的编号(empno),姓名(ename),工资(sal),部门编号(deptno),首先根据工资升序排列,再次根据员工编号降序排列。
select empno,ename,sal,deptno
from emp
order by sal asc,empno desc;
练习:查询emp表中,员工的编号(empno),姓名(ename),职位(job),部门编号(deptno),先根据部门编号降序排列,再根据员工编号升序排列
select empno,ename,job,deptno
from emp
order by deptno desc,empno asc;
说明:
- 如果根据2列排序,先根据前面的列排序,如果列值相同,那么在根据第2列排序
- 排序永远放在格式的最后面
6.9.5 条件查询语句
格式:
select */列名 from 表名 where 条件;
说明: 条件包含关系运算符、逻辑运算符、特殊情况
关系运算符: > < = >= <= <>/!=(不等于)
案例:查询emp表中,工资(sal)大于1500的,员工的编号,姓名,职位,工资
select empno,ename,job,sal
from emp
where sal>1500;
练习:查询emp表中,部门编号是30号部门的,员工的姓名,职位,工资,部门编号(deptno)
select ename,job,sal,deptno
from emp
where deptno=30;
练习:查询dept表中,部门地址(loc)是BOSTON的,部门的编号(deptno),部门名称(dname),部门地址
select deptno,dname,loc
from dept
where loc='BOSTON';
练习:查询emp表中,工资不等于1250的,员工的编号,姓名,工资,根据工资降序排列
select empno,ename,sal
from emp
where sal<>1250
order by sal desc;
逻辑运算符:
- and(与)
- or(或)
- not(非)
1)and 并且 连接2个或者多个条件
案例:查询emp表中,工资在1000~3000之间的,员工的编号,姓名，职位,工资
select empno,ename,job,sal
from emp
where sal>1000 and sal<3000;
练习:查询emp表中,部门编号是30号部门并且工资大于1000的,员工的编号,姓名,职位,工资,部门编号
select empno,ename,job,sal,deptno
from emp
where deptno=30 and sal>1000;
2) or 或者
案例:查询emp表中,职位是SALESMAN或者工资大于1000的,员工的编号,职位,工资
select empno,job,sal
from emp
where job='SALESMAN' or sal>1000;
练习:查询emp表中,工资大于等于3000或者部门编号是20号部门的,员工的编号,姓名,职位,工资,部门编号,根据工资升序排列
select empno,ename,job,sal,deptno
from emp
where sal>=3000 or deptno=20
order by sal asc;
3) not 非
案例:查询emp表中,工资不等于1250,员工的编号,姓名,职位,工资
select empno,ename,job,sal
from emp
where sal<>1250;
select empno,ename,job,sal
from emp
where not sal=1250;
6.9.5.1 查询列值为空的情况(is null)
案例:查询emp表中,奖金(comm)为空的,员工的编号,姓名,奖金
select empno,ename,comm
from emp
where comm is null;
练习:查询emp表中,领导编号(mgr)为空的,员工的编号、姓名、职位、领导编号
select empno,ename,job,mgr
from emp
where mgr is null;
6.9.5.2 查询列值不为空的情况(is not null)
案例:查询emp表中,奖金不为空的,员工的编号,姓名,职位,工资,奖金,根据工资升序排列
select empno,ename,job,sal,comm
from emp
where comm is not null
order by sal asc;
练习:查询emp表中,有领导编号的,员工的全部列信息
select * from emp where mgr is not null;
6.9.5.3 between....and
格式:
select */列名 from 表名 where 列名 between 初值 and 终值;
说明:使用between...and查询出来的数据,范围在[初值,终值]之间。
案例:查询emp表中,工资在1000~5000之间的,员工的编号,姓名工资
select empno,ename,sal
from emp
where sal>=1000 and sal<=5000;
select empno,ename,sal
from emp
where sal between 1000 and 5000;
6.9.5.4 in 比较一个列中的几个列值
格式:
select */列名 from 表名 where 列名 in(列值1,列值2,.....列值n);
练习:查询emp表中,员工编号是7521, 7698, 7844, 7902的员工信息
select * from emp where empno=7521 or empno=7698 or empno=7844 or empno=7902;
select * from emp where empno in(7521, 7698, 7844, 7902);
练习:查询emp表中,员工职位是MANAGER, SALESMAN, ANALYST的,员工的编号,姓名,职位,工资
select empno,ename,job,sal
from emp
where job in('MANAGER', ' SALESMAN',' ANALYST');
复习:
- 查询全部列数据
select * from 表名;
- 查询指定列数据
select 列名1,列名2,....列名n from 表名;
- 给列起别名
select 列名1 as 别名1,列名2 as 别名2,....列名n as 别名n from 表名;
select 列名1 别名1,列名2 别名2,....列名n 别名n from 表名;
- 去掉重复的列值
select distinct 列名 from 表名;
- 排序
select */列名 from 表名 order by 列名1 asc/desc,列名2 asc/desc;
- 条件查询
select */列名 from 表名 where 条件;
关系运算符:> < = >= <= <>/!=
sal>2000 job='MANAGER'
sal<>1250
逻辑运算符: and or not
sal>1000 and sal<5000
deptno=10 and job='MANAGER'
deptno=30 or sal>2000
not sal=1250
特殊情况: - 列值为空 comm is null - 列值不为空 mgr is not null - between...and和and相似
select */列名 from 表名 where 列名 between 初值 and 终值;
- in 比较一个列中的几个列值
select */列名 from 表名 where 列名 in(列值1,列值2,....列值n);
6.9.5.5 模糊查询(-重点)
格式:
select */列名 from 表名 where 列名 like 条件;
说明条件组成:
- %: 表示0个或者多个任意字符
- _: 表示任意一个字符
案例:查询emp表中,员工姓名的首字母(第1个字母)是M开头的,员工的编号,姓名,职位
Mfsdfdf M===>M%
select empno,ename,job
from emp
where ename like 'M%';
案例:查询emp表中,员工姓名尾字母是(最后一个字母)N的,员工的编号,姓名,职位
dsafdN N ==> %N
select empno,ename,job
from emp
where ename like '%N';
案例:查询emp表中,员工姓名中包含(含有)字母N的,员工的编号,姓名,工资
fdsN Newr gfNgfg==>%N%
select empno,ename,sal
from emp
where ename like '%N%';
练习:查询emp表中,员工姓名倒数第2个字母是N的,员工的编号,姓名,工资
dfsdNe Nw ==> %N_
select empno,ename,sal
from emp
where ename like '%N_';
练习:查询emp表中,员工姓名正数第3个字母是N的,员工的编号,姓名,工资
select empno,ename,sal
from emp
where ename like '__N%';
6.9.6 处理空值的函数
案例:查询emp表中,员工的编号,姓名,工资,年薪(工资*12)
select empno,ename,sal,sal*12 from emp;
案例:查询emp表中,员工的编号,姓名,工资,奖金(comm),年薪(工资*12+comm)
select empno,ename,sal,comm,sal*12+nvl(comm,0) from emp;
说明:任何数值类型通过“+”和null连接在一起,结果是null,对于这种情况需要使用nvl()函数处理
nvl(列名,数值) 表示如果列值为null,使用数值代替null。
6.9.7 聚合(分组)函数
- count()
- sum()
- avg()
- min()
- max()
1)count(列名/*)
*: 表示统计表中数据的总数量
列名: 表示统计该列中,列值不为空的总数量
案例:统计emp表中,员工的总数量
select count(*) from emp;
案例:统计emp表中,奖金不为空的,员工的总数量
select count(comm) from emp;
练习:统计emp表中,员工职位的总数
select count(distinct job) from emp;
2)sum(列名) 统计列值累加之和
案例:统计emp表中,员工的工资总和
select sum(sal) from emp;
练习:统计emp表中,员工的奖金总和
select sum(comm) from emp;
3)avg(列名) 求平均值
案例:求emp表中,奖金的平均值
select avg(comm) from emp;
练习:求emp表中,工资的平均值
select avg(sal) from emp;
4)min(列名) 求最小值
案例:查询emp表中,员工的最低工资
select min(sal) from emp;
练习:查询emp表中,员工编号的最小值
select min(empno) from emp;
5)max(列名) 求最大值
案例:查询emp表中,员工的最高工资
select max(sal) from emp;
练习:查询emp表中,员工总数,工资总和,平均工资,最低工资,最高工资,并给每列起别名
select count(*) 员工总数,sum(sal) 工资总和,avg(sal) 平均工资,min(sal)
最低工资,max(sal) 最高工资 from emp;
6.9.8 分组查询
解释分组查询:在数据库中的表中,对表中某一列的数据,根据列值分成几组(相同的分成一组),然后对每一组的数据,使用聚合函数,聚合函数经常和分组查询一起使用。
格式:
select 列名/聚合函数
from 表名
where 条件
group by 列名
order by 列名/聚合函数 asc/desc;
案例:查询emp表中,每个部门的编号,部门的人数,部门的最高工资
select deptno,count(*),max(sal)
from emp
group by deptno;
案例:查询emp表中,工资大于1000的,每个部门的编号,部门的工资总和,部门的平均工资
select deptno,sum(sal),avg(sal)
from emp
where sal>1000
group by deptno;
练习:查询emp表中,领导编号不为空的,每个部门的编号,部门的最高工资,部门的人数,根据部门编号升序排列
select deptno,max(sal),count(*)
from emp
where mgr is not null
group by deptno
order by deptno asc;
练习:查询emp表中,员工姓名中不包含C的,每个职位的名称,职位的最高工资,每个职位的人数,根据人数降序排列
select job,max(sal),count(*)
from emp
where not ename like '%C%'
group by job
order by count(*) desc;
6.9.9 having语句
解释:分组查询后,得到的结果,再次进行过滤,这个时候使用having语句
格式:
select 列名/聚合函数
from 表名
where 条件
group by 列名
having条件
order by 列名/聚合函数 asc/desc;
where和having的区别:
- where是对整张表的数据进行过滤,可以单独使用
- having是对分组后的数据进行过滤,
having必须和group by一起使用。
案例:查询emp表中,部门的平均工资大于2000的,每个部门的编号,部门人数,部门的平均工资
select deptno,count(*),avg(sal)
from emp
group by deptno
having avg(sal)>2000;
练习:查询emp表中,工资在1000~5000之间的,每个职位的名称,职位的人数,职位的平均工资,职位的最高工资,要求职位的最高工资小于3000,根据平均工资降序排列
select job,count(*),avg(sal),max(sal)
from emp
where sal>1000 and sal<5000
group by job
having max(sal)<3000
order by avg(sal) desc;
6.9.10 常用字符处理函数
1)length(字符串/列名) 统计字符串中字符/列值的个数
案例:查询emp表中,员工的姓名以及姓名的长度
select ename,length(ename) from emp;
案例:查询helloworld字符串的长度
说明:如果试题中没有表,可以使用系统自带表---dual可以验证各种函数的使用
select length('helloworld') from dual;
2)substr(参数1,参数2,参数3) 截取字符串---重点
- 参数1: 要截取的字符串/列名
- 参数2: 从哪里开始截取
如果是正数表示从正数第几位开始截取,如果是负数,表示从倒数第几位开始截取
- 参数3:截取的数量
select substr('helloworld',-3,2) from dual;
select substr('helloworld',5,3) from dual;
练习:查询emp表中,员工的姓名以及姓名最后2个字母
select ename,substr(ename,-2,2) from emp;
3)拼接字符串
- 使用 || 拼接字符串
select '窗前明月光' || '疑是地上霜' || '举头望明月' || '低头思故乡' from dual;
select ename || job from emp;
```sql
- 使用concat拼接字符串
```sql
concat(字符串/列名,字符串/列名)
select ename,job,concat(ename,job) from emp;
复习:
- 模糊查询
select 列名 from 表名
where 列名 like 条件;
条件:
%: 0个或者多个任意字符
_: 1个任意字符
- 处理空值的函数
nvl(列名,数值) 如果列值为null,使用数值代替null
- 聚合函数
count(列名/*)
*:表示统计表中数据总数量
列名:列值不为空的,数据总数量
sum(列名) 求和
avg(列名) 求平均值
min(列名) 求最小值
max(列名) 求最大值
- 分组查询
聚合函数经常和分组查询一起使用
select 列名/聚合函数
from 表名 ---1
where 条件 ---2
group by 列名 ----3
order by 列名/聚合函数 asc/desc;
- having语句
select 列名/聚合函数
from 表名 ---1
where 条件 ---2
group by 列名----3
having 条件
order by 列名/聚合函数 asc/desc;
6.9.11 常用数值处理函数
1)round(数值,位数) 四舍五入函数
如果位数>0,表示小数点后保留几位小数,如果位数=0,不保留小数,位数<0,小数点之前第几位进行四舍五入
select round(35.376,2) from dual;
select round(35.376,1) from dual;
select round(35.576,0) from dual;
select round(35.576,-1) from dual;
----40
select round(35.576,-2) from dual;
----0
2)trunc(数值,位数) 截取函数
如果位数>0,表示小数点后保留几位小数,如果位数=0,不保留小数,位数<0,舍掉小数点之前第几位
select trunc(35.376,2) from dual;
--35.37
select trunc(35.376,1) from dual;
--35.3
select trunc(35.576,0) from dual;
--35
select trunc(35.576,-1) from dual;
--30
select trunc(35.576,-2) from dual;
--0
6.9.12 常用日期处理函数
- 常见的日期格式:
系统默认格式: dd-mon月-yy
年月日格式: yyyy-mm-dd
年月日时分秒格式: yyyy-mm-dd hh24:mi:ss
1)sysdate 当前系统日期
案例:查询当前系统时间
select sysdate from dual;
练习:查询昨天、今天、明天的日期
select sysdate-1,sysdate,sysdate+1 from dual;
2)to_char(date,'日期格式的一部分')
案例:查询当前的月份
select to_char(sysdate,'mm') from dual;
练习:查询当前时间的分钟数
select to_char(sysdate,'mi') from dual;
练习:查询emp表中,12月份入职的,员工的编号,姓名,职位,入职时间(hiredate)--重点
select empno,ename,job,hiredate
from emp
where to_char(hiredate,'mm')='12';
3)to_date('特殊日期格式的字符串','日期格式')
将字符类型按照某种日期格式,转换为日期类型,经常用于插入语句操作
案例:向emp表中插入2条记录
empno ename hiredate
2345 ROSE 2019-5-9
2346 JACK 2000-10-11
2347 TOMS 2008-8-8
insert into emp(empno,ename,hiredate) values(2345,'ROSE',to_date('2019-5-9','yyyy-mm-dd'));
insert into emp(empno,ename,hiredate) values(2346,'JACK',to_date('2000-10-11','yyyy-mm-dd'));
insert into emp(empno,ename,hiredate) values(2347,'TOMS',to_date('2008-8-8','yyyy-mm-dd'));
6.9.13 多表连接查询
说明:多表连接查询就是,通过一条SQL语句,可以将分布在不同表中的数据,一次查询出来------多表连接查询
格式:
select 别名1.*/列名,别名2.*/列名,...
from 表1 别名1,表2 别名2,.....
where 关联条件;
案例:查询emp表中,员工的编号,姓名,职位以及dept表中,部门的编号,部门名称
select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
说明:如果2张表中的,关联条件是通过“=”相连接的,称为等值连接查询,特点是把2张表中有关联的数据全部查询出来。
案例:查询emp表中,工资在1000~5000之间的,员工的编号,姓名，职位,工资以及dept表中全部列的数据
select e.empno,e.ename,e.job,e.sal,d.*
from emp e,dept d
where e.deptno=d.deptno and e.sal>1000 and e.sal<5000;
练习:查询emp表中,工资大于1000的,员工的编号,姓名,职位,工资以及dept表中,部门的名称(dname),部门地址(loc),根据工资进行降序排列
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.sal>1000
order by e.sal desc;
练习:查询emp表中,员工姓名中不包含字母K的并且在10和20号部门下的,员工的编号,姓名,职位,以及dept表中部门名称,部门地址
select e.empno,e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and not e.ename like '%K%' and (e.deptno=10 or e.deptno=20);
select e.empno,e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and not e.ename like '%K%' and e.deptno in(10,20);
2)非等值连接查询
说明:如果2张表中,关联条件不是通过“=”相连接的,称为非等值连接查询
案例:查询emp表中,员工的编号,姓名,职位,工资以及salgrade表中,工资等级(grade),最低工资(losal),最高工资(hisal)
emp表中的sal,一定在salgrade表中的最低工资(losal)和最高工资(hisal)之间
e.sal between s.losal and s.hisal
select e.empno,e.ename,e.job,e.sal,
s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
3)自连接
自连接查询就是,列与列之间存在关联关系,我们将1张表看成2张表,使用等值连接方式进行查询。
练习:查询emp表中,员工的编号,姓名,职位,领导编号(mgr),领导姓名
select e.empno,e.ename,e.job,e.mgr,m.ename
from emp e,emp m
where m.empno=e.mgr;
6.9.14 子(嵌套)查询
子查询就是在一个查询语句的内部,又包含一个查询语句。
案例:查询emp表中,工资大于平均工资的,员工的编号,姓名,职位,工资
1)求出平均工资
select avg(sal) from emp;
2)合成
select empno,ename,job,sal
from emp
where sal>(select avg(sal) from emp);
练习:查询emp表中,工资大于30号部门最高工资的,员工的编号,姓名,工资
select empno,ename,sal
from emp
where sal>(select max(sal) from emp where deptno=30);
练习:查询dept表中,部门地址(loc)是DALLAS下的,员工的编号,姓名,职位，工资
select e.empno,e.ename,e.job,e.sal
from emp e,dept d
where e.deptno=d.deptno and d.loc='DALLAS';
select empno,ename,job,sal from emp where deptno=(select deptno from dept where loc='DALLAS');
练习:查询emp表中和JONES是同一个部门的,员工的编号,姓名,职位,部门编号
select empno,ename,job,deptno from emp where deptno=(select deptno from emp where ename='JONES');
复习:
- 常见字符处理函数
- length(字符串/列名) 统计字符/列值的个数
- substr(参数1,参数2,参数3) 截取字符串
参数1:被截取的字符串/列名
参数2:从哪里开始截取
参数3:截取个数
拼接字符串 || 或者 concat
- || 可以无限拼接
- concat(字符串/列名,字符串/列名)
- 常见数值处理函数
- round(数值,位数) 四舍五入函数
- trunc(数值,位数) 截取函数
- 常见日期处理函数
- sysdate 获取系统当前日期
- to_char(date,'日期格式的一部分')
- to_date('日期格式的字符串','日期格式')
- 多表连接查询
select 别名1.*/列名,别名2.*/列名...
from 表1 别名1,表2 别名2....
where 关联条件;
6.9.15 事务控制语句
1)提交事务---commit
create table test01(
id number(4) primary key,
name varchar2(30)
);
insert into test01 values(101,'乔峰');
insert into test01 values(102,'虚竹');
select * from test01;
commit;
2)回滚事务---rollback
说明:回滚事务只可以撤销未保存(没有提交事务的)的事务
insert into test01 values(103,'段誉');
insert into test01 values(104,'许仙');
select * from test01;
rollback;
七、约束(constraint)
说明:在创建表的时候,对表中的列设置一些规则,在插入数据的时候,只有满足这些规则才可以插入数据,我们把这些规则叫做约束。
约束的分类
- 主键约束(primary key)
- 唯一约束(unique)
- 检查约束(check)
- 默认值约束(default)
- 非空约束(not null)
7.1 主键约束(primary key)
说明:主键约束就是用来标识表中唯一的一条记录,被主键约束所修饰的列,其列值是唯一且非空的,一张表中,只可以有一个主键约束。
1)创建表时,添加主键约束
create table test02(
id number(4) primary key,
name varchar2(30),
age number(3)
);
insert into test02 values(101,'rose',22);
insert into test02 values(101,'jack',25);
insert into test02(name,age) values('toms',30);
2)联合主键(复合主键)---重点
使用主键约束修饰2个列或者多列叫做复合主键
create table test03(
id number(4),
name varchar2(30),
age number(3),
address varchar2(50),
constraint pk_id_name_test03 primary key(id,name)
);
insert into test03 values(101,'tom',20,'北京');
insert into test03 values(101,'toms',22,'上海');
insert into test03 values(101,'toms',25,'重庆');
3)修改表时,添加主键约束
格式:
alter table 表名 add constraint
约束名 primary key(列名1,列名2....列名n);
案例:创建一张表,表名为test04,表中的字段有: id number(4),name varchar2(30), sex char(3),email varchar2(30),修改表时,对id列添加主键约束,约束名为pk_id_test04
create table test04(
id number(4),
name varchar2(30),
sex char(3),
email varchar2(30)
);
alter table test04 add constraint pk_id_test04 primary key(id);
insert into test04(id,name) values(101,'金庸');
insert into test04(id,name) values(101,'古龙');
练习:创建一张表,表名为test05,表中包含的字段有:tid number(4)、tname varchar2(30)、passwd varchar2(30)
修改表时,对tid和tdname添主键约束,约束名为pk_tid_tname_test05
create table test05(
tid number(4),
tname varchar2(30),
passwd varchar2(30)
);
alter table test05 add constraint pk_tid_tname_test05 primary key(tid,tname);
4)删除主键约束
格式1:
alter table 表名 drop primary key;
格式2:
alter table 表名 drop constraint 约束名;
说明:可以使用此格式,删除主键约束、唯一约束、检查约束。
案例:删除test02表中的主键约束
alter table test02 drop primary key;
案例:删除test05表中的主键约束,约束名为pk_tid_tname_test05
alter table test05 drop constraint pk_tid_tname_test05;
练习:删除test04表中的主键约束,约束名为pk_id_test04
alter table test04 drop constraint pk_id_test04;
7.2 唯一约束(unique)
说明:使用唯一约束可以修饰1列或者多列的组合值,使其具有唯一性,防止用户输入重复数据
- 被唯一约束修饰的列,列值可以为null
- 一张表中,可以有多个唯一约束
1)创建表时,添加唯一约束
create table test06(
id number(4) primary key,
name varchar2(30) unique,
age number(3),
email varchar2(30)
);
insert into test06(id,name) values(101,'小花');
insert into test06(id,name) values(102,'小花');
2)修改表时,添加唯一约束
格式:
alter table 表名 add constraint 约束名 unique(列名1,列名2,.....列名n);
案例:对test06表中的,email列,添加唯一约束,约束名为uq_email_test06
alter table test06 add constraint uq_email_test06 unique(email);
练习:创建一张表,表名为test07,表中包含的字段有: id number(4) pk,
name varchar2(30), phone number(7),address varchar2(50)
修改表时,对name和address列添加唯一约束,约束名为uq_name_address_test07
create table test07(
id number(4) primary key,
name varchar2(30),
phone number(7),
address varchar2(50)
);
alter table test07 add constraint uq_name_address_test07 unique(name,address);
3)删除唯一约束
格式:
alter table 表名 drop constraint 约束名;
案例:删除test07表中,唯一约束,约束名为uq_name_address_test07
alter table test07 drop constraint uq_name_address_test07;
练习:删除test06表中,唯一约束,约束名为uq_email_test06
alter table test06 drop constraint
uq_email_test06;
复习:
- 主键约束(primary key)
1)联合主键
create table test(
id number(4),
name varchar2(30),
age number(3),
constraint pk_id_name_test priamry key(id,name)
);
- 唯一约束(unique)
- 主键约束一张表中只可以有一个,而唯一约束一张表中可以有多个
- 被主键约束所修饰的列,其列值是唯一且非空而唯一约束所修饰的列,列值是唯一的,可以为空
- 检查约束(check)
check(age between 1 and 150)
check(sex in('男','女'))
check(email like '%@%')
7.3 检查约束(check)
说明:检查约束就是用于限定某列,必须满足某种特定的条件,防止用户输入非法数据
1)创建表时,添加检查约束
create table test08(
id number(4) primary key,
name varchar2(30) unique,
sex char(3) check(sex in('男','女')),
age number(3),
email varchar2(30)
);
insert into test08(id,name,sex) values(101,'东方不败','中');
2)修改表时,添加检查约束
格式:
alter table 表名 add conctraint 约束名 check(条件);
练习:对test08表中,age列,添加检查约束,要求年龄在1~150之间,约束名chk_age_test08
alter table test08 add constraint chk_age_test08 check(age between 1 and 150);
insert into test08(id,name,sex,age) values(102,'陆小凤','男',200);
练习:对test08表中,email列添加检查约束,要求email中必须包含@符号,约束名为chk_email_test08,并验证
alter table test08 add constraint chk_email_test08 check(email like '%@%');
3)删除检查约束
格式:
alter table 表名 drop constraint 约束名;
案例:删除test08表中,email列上的检查约束,约束名为chk_email_test08
alter table test08 drop constraint chk_email_test08;
7.4 默认值约束(default)
当执行插入操作的时候,被默认约束所修饰的列,如果没有插入数据,系统会自动将默认值作为该列列值。
1)创建表时,添加默认值约束
create table test09(
id number(4) primary key,
name varchar2(30) unique,
sex char(3) check(sex in('男','女')),
hiredate date default sysdate,
age number(3) default 18,
address varchar2(50)
);
insert into test09(id,name,sex) values(101,'tom','男');
select * from test09;
2)修改表时,添加默认值约束
格式:
alter table 表名 modify 列名 数据类型 default 默认值;
案例:修改test09表中,age列上的默认值约束,将默认值修改为20
alter table test09 modify age number(3) default 20;
insert into test09(id,name,sex) values(111,'toms','男');
select * from test09;
练习:修改test09表,对address添加默认值约束,默认值设置为北京,并验证
alter table test09 modify address varchar2(50) default '北京';
insert into test09(id,name,sex) values(112,'rose','女');
select * from test09;
3)删除默认值约束
格式:
alter table 表名 modify 列名 数据类型 default null;
案例:删除test09表中,age列上的默认值约束
alter table test09 modify age number(3) default null;
insert into test09(id,name,sex) values(119,'jack','男');
select * from test09;
练习:删除test09表中,hiredate列上的默认值约束
alter table test09 modify hiredate date default null;
7.5 非空约束(not null)
当执行插入操作的时候,被非空约束所修饰的列,列值不能为空
1)创建表时,添加非空约束
create table test10(
id number(4) primary key,
name varchar2(30) unique,
sex char(3) check(sex in('男','女')),
hiredate date default sysdate,
age number(3) not null,
address varchar2(50)
);
insert into test10(id,name,sex) values(101,'西门吹雪','男');
2)修改表时,添加非空约束
格式:
alter table 表名
modify(列名1 not null)
modify(列名2 not null)
....
modify(列名n not null);
案例:修改test10表,对address列添加非空约束
alter table test10 modify(address not null);
insert into test10(id,name,sex,age) values(102,'叶孤城','男',20);
练习:创建一张表,表名test11,表中包含的字段有:
id number(4) pk
name varchar2(30)
sex char(3)
age number(3)
修改表时,对name和sex列添加非空约束
create table test11(
id number(4) primary key,
name varchar2(30),
sex char(3),
age number(3)
);
alter table test11
modify(name not null)
modify(sex not null);
3)删除非空约束
格式:
alter table 表名
modify(列名1 null)
modify(列名2 null)
....
modify(列名n null);
案例:删除test10表中,address列上的非空约束
alter table test10 modify(address null);
八、索引(index)
索引是建立在表中列上的,数据库对象,索引可以提高查询速度。
1)创建索引
格式:
create index 索引名称 on表名(列名);
create table test12(
id number(4) primary key,
name varchar2(30),
address varchar2(50)
);
create index index_name_test12 on test12(name);
select id,name from test12 where name='rose'; 3s
说明:
- 被主键约束所修饰的列,自带索引
- 被唯一约束所修饰的列,自带索引
2)删除索引
格式:
drop index 索引名称;
案例:删除test12表中的,索引,索引名称为index_name_test12
drop index index_name_test12;
九、序列(sequence)
序列是数据库中的一个对象,通过序列可以生成自动增长的数字,经常使用序列生成的数字,作为主键所修饰列的列值。
1)创建序列
create sequence 序列名称;
2)nextval
生成一个新的数字,第1次执行的时候,默认值为1
案例:创建一个序列,序列名称为seq_id01
create sequence seq_id01;
select seq_id01.nextval from dual;
案例:创建一张表,表名为test13,表中包含的字段有: id number(4) pk
name varchar2(30),使用序列生成自动增长的数字,作为主键所在的列值,序列名称为seq_n01
create table test13(
id number(4) primary key,
name varchar2(30)
);
create sequence seq_n01;
insert into test13 values(seq_n01.nextval,'天蚕土豆丝');
insert into test13 values(seq_n01.nextval,'我吃西红柿');
insert into test13 values(seq_n01.nextval,'辰东');
insert into test13 values(seq_n01.nextval,'鱼人二代');
3)序列的属性
- 序列的初始值
start with 初始值
- 序列的增长步长
increment by 步长
- 序列的最小值 minvalue 最小值
- 序列的最大值 maxvalue 最大值
案例:创建一个序列,序列名为seq_n02,初始值为:100,增长步长为:5,最小值为10,最大值为200
create sequence seq_n02
start with 100
increment by 5
minvalue 10
maxvalue 200;
select seq_n02.nextval from dual;
十、视图(view)
视图就是1张虚拟表,可以通过视图查询一张或者多张表的数据
10.1 创建视图
create view 视图名称
as
查询语句;
10.2 视图查询
案例:查询emp表中的全部数据,将结果给视图view_emp01
create view view_emp01
as
select * from emp;
select * from view_emp01;
练习:通过视图view_emp01,查询员工的编号,姓名,职位,工资,根据工资降序排列
select empno,ename,job,sal
from view_emp01
order by sal desc;
练习:通过视图view_emp01,查询工资大于1000的并且姓名中含有字母N的,员工的编号,姓名,职位,工资,根据编号升序排列
select empno,ename,job,sal
from view_emp01
where sal>1000 and ename like '%N%'
order by empno asc;
练习:创建一个视图,视图名称为:view_emp_dept,查询emp表中的
全部数据以及dept表中的,部门名称(dname)和部门地址(loc),作为视图view_emp_dept的结果
create view view_emp_dept
as
select e.*,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
select * from view_emp_dept;
练习:查询工资在1000~5000之间的并且上级领导编号不为空的,员工的编号,姓名,职位,工资,上级领导编号(mgr),部门地址,首先根据工资降序排列,再次根据编号升序排列
select e.empno,e.ename,e.job,e.sal,e.mgr,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.sal>1000 and e.sal<5000 and e.mgr is not null
order by e.sal desc,e.empno asc;
select empno,ename,job,sal,mgr,loc
from view_emp_dept
where sal>1000 and sal<5000 and mgr is not null
order by sal desc,empno asc;
练习:创建一个视图,视图名称为view_emp_sal,查询工资在1000~5000之间的,员工的编号,姓名,职位,工资,工资等级(grade),最低工资(losal),最高工资(hisal),作为视图view_emp_sal结果
emp<------->salgrade
create view view_emp_sal
as
select e.empno,e.ename,e.job,e.sal,s.*
from emp e,salgrade s
where e.sal between s.losal and s.hisal and e.sal>1000 and e.sal<5000;
10.3 修改视图
格式:
create or replace view 视图名称
as
查询语句;
案例:修改view_emp01视图,查询10,30号部门的,员工的编号,姓名,职位,工资,部门编号,作为视图结果
create or replace view view_emp01
as
select empno,ename,job,sal,deptno
from emp
where deptno in(10,30);
select * from view_emp01;
练习:修改视图view_emp_dept,查询dept表中全部数据,作为视图结果
create or replace view view_emp_dept
as
select * from dept;
select * from view_emp_dept;
10.4 通过视图实现对表的操作
create view view_dept01
as
select * from dept;
select * from view_dept01;
1)插入操作
insert into view_dept01 values(50,'TESTING','BEIJING');
insert into view_dept01 values(60,'DEVELOPER','SAHNHA');
insert into view_dept01 values(70,'UI','HANGZHOU');
select * from view_dept01;
select * from dept;
2)修改操作
案例:通过视图view_dept01,修改部门编号是60的,将地址(loc)修改为SHENZHENG
update view_dept01 set loc='SHENZHENG' where deptno=60;
select * from view_dept01;
select * from dept;
练习:通过视图view_dept01,将部门地址(loc)全部修改为BEIJING
update view_dept01 set loc='BEIJING';
3)删除操作
练习:删除view_dept01视图中,50,60,70号部门的记录
delete from view_dept01 where deptno in(50,60,70);
10.5 只读视图(-重点)
说明:只能查询,不能修改
格式:
create view 视图名称
as
查询语句
with read only;
案例:创建一个只读视图,视图名称为view_dept02,并验证
create view view_dept02
as
select * from dept
with read only;
insert into view_dept02 values(50,'TEST','BEIJING');
10.6 删除视图
格式:
drop view 视图名称;
案例:删除view_dept02视图
drop view view_dept02;
select * from view_dept02;