Oracle 数据库
--了解Oracle的编码
select userenv('language') from dual
1、两种数据存储形式
1)文件
---清楚数据在文件中的存储格式
---对数据的处理过程全部需要代码实现
---对文件的并发操作也要通过代码实现
2)数据库
---表(table)是数据库中存储数据的基本单位
2、数据库标准语言:SQL
----结构化查询语言(Structured Query Language)
------数据定义语言(DDL Data Definition Language)
------数据操作语言(DML Data Manipulation Language)
------事务控制语言(TCL Transaction Control Language)
------数据查询语言(DQL Data Query Language)
------数据控制语言(DCL Data Control Language)
3、DB 和 DBMS
1)数据库(DATABASE)
---关系数据库使用关系或二维表存储信息。
2)关系型数据库管理系统(RDBMS)
-----关系型数据库管理系统(Relationship Database Management System)是一套软件,用在数据库中存储数据,维护数据,查询数据等等;
4、DDL 语句
1)create
----create table 表名(字段名 类型(范围),.....);
-----create table 表名 as select 语句;//基于一个表创建另一个表
2)drop
----drop table 表名;//放回回收站
----drop table 表名 purge;//不放回回收站
----flashback table 表名 to before drop;//从回收站恢复表
3)desc
----desc 表名;//查看表结构
4)添加一列字段
alter table 表名 add 字段名 类型;
5)修改字段类型和大小,是否允许为空
alter table 表名 modify (字段 类型);
提示:如果修改类型;或将存储空间大改成小的;修改成非空;可能会产生错误,原因是可表中现有数据冲突导致改表结构失败.
6)删除一列字段
alter table 表名 drop column 字段名;
7)修改表名字
rename 表名 to 新表名;
提示:表名最大长度30,一个中文占2个的话,最大用15个中文字符.避免名称与关键字冲突.不要以数字和_开始.
8)字段设置default默认值
在insert操作时,不为该字段指定值时,采用默认值写入.显式指定值时,指定什么写入什么,null也可以写入.
....类型 default 数值,。。。
default可以用于insert语句和update语句
//删除主键约束
alter table foo_2 drop constraint foo2_pk;
/创建一个序列,从1开始计数,递增加1
create sequence 序列名 start with 值;
创建索引
create index 索引名 on 表名(字段名)
//创建或更新某个视图
create or replace view 视图名 as select语句;
===========================================================
//查看约束,使用user_constraints系统表
desc user_constraints;
select table_name,constraint_name,
constraint_type from user_constraints;
//查看用户表,使用user_tables系统表
select table_name from user_tables;
======================================================
5、DML 语句
a.添加
insert into 表名 (字段名1,字段名2...)values (值1,值2...);
b.更新
update 表名 set 字段名=值,字段名=值 [where 字段名=条件值]
c.删除
delete from 表名 [where 字段名=条件值]
6、TCL语句
a、commit 事物提交
b、rollback 事物回滚
7、DQL 语句
select *或字段名... from 表名 [where 字段名=条件值]
8、DCL 语句
负责权限控制.(由DBA使用)
create user...创建用户
grant 授权
revoke 收回权限
9、字符串函数
--concat(s1,s2)或者s1 || s2
--大小写转换
upper(s):将s转成大写
lower(s):将s转成小写
initcap(s):将s中单词首字母大写
--过滤空格
trim(s):过滤s中前后(左右)空格
ltrim(s):过滤s中前面(左)空格
rtrim(s):过滤s中后面(右)空格
--补位函数
lpad(s,n,char) : 当s不够n位时,在s左边补充char字符.
rpad(s,n,char):当s不够n位时,在s右边补充char字符.
注意:如果s超出n位,采取截取方式保留n位.
--截取函数
substr(s,begin[,size]):对s进行截取;begin表示从
第几个字符开始截取;size表示截取多少个字符
substr(s,begin):
如果begin为负数,表示从后向前取begin位字符
如果begin为正数,表示从begin位开始取到最后
--字符查找
instr(s,s1[,begin,n]):在s中查找s1第一次出现的位置.
begin表示从哪个字符开始查找;n表示第几次
匹配.
--计算长度
length(s):计算s字符个数.(一个中文算一个长度)
10、数值函数
-- round():四舍五入
--trunc():截取
trunc(n)一个参数取整,将小数截掉
trunc(n,s)保留s位小数,将多余的截掉
--mod():求余数
--ceil(n):向上取整(取大于或等于n的整数)
--floor(n):向下取整(取小于或等于n的整数)
11、日期操作
*1)日期类型
date:存储年月日小时分钟秒
timestamp:存储年月日小时分钟秒毫秒时区
上述类型定义时不需要指定大小.
*2)日期关键字
sysdate : 获取Oracle服务器系统当前时间
systimestamp : 获取Oracle服务器系统当前时间
to_date(s,format):将符合format格式的s字符串转成date
to_char(date,format):将date值按format转换成字符串
to_timetamp(s,format):将s字符串转成timestamp类型
format格式元素为yyyy-mm-dd hh:mi:ss
3)其它函数
--last_day(d):返回d日期中月份的最后一天的日期
--add_months(d,n):对d日期值的月份加n.n值可以为负数.
--next_day(d,星期几):从d日期开计算下一个星期几的时间日期.
--least(n1,n2):返回两个数值中小的那一个
--greatest(n1,n2):返回两个数值中大的那一个
--round(d):对日期的小时分钟秒部分进行舍入操作(12小时前的舍弃;
12小时后的日期加1)
--trunc(d):对日期的小时分钟秒部分截取掉.
--extract函数
extract(year from 日期值)//返回日期值的年
extract(month from 日期值)//返回日期值的月
extract(day from 日期值)//返回日期值的日
extract(hour from timestamp类型值)//返回小时
extract(minute from timestamp类型值)//返回分钟
extract(second from timestamp类型值)//返回秒
select extract(hour from systimestamp) from dual;
12、空值操作
--nvl(字段,值1):当字段值为null时返回值1;
字段值不为null返回字段值;
--nvl2(字段,值1,值2):当字段值不为null返回值1;
当字段值为null返回值2;
-------------面试题----------------
drop table xxx;//删除表结构和数据,释放空间
delete from xxx;//删除表数据,结构还在.可以回滚
truncate table xxx;//删除表数据,结构还在,
//效率高,数据不能回滚
-------------------------------------
13、主键
a.主键特点
--非空,不允许为null
--唯一,不允许重复
--企业中一个表肯定会定义一个主键
--主键其实就是字段(可以由一到多个字段
承担主键职责)
--主键用于定位要操作的某一条记录
(利用主键值查询,最多返回一条)
--主键值一般不参与业务操作,不允许更新
b.3种定义方法
create table 表名(字段 类型 primary key,。。。。)
create table 表名(字段 类型,..........,constraint 约束名 primary key(字段));
//建完表之后添加主键
alter table 表名 add constraint 约束名 primary key(字段);
提示:如果字段数据违反主键规则,会创建失败.
14、查询语句
1).基本语句(最简单的结构)
select *或字段名... from 表名;
--*代表表中所有字段名
--select 决定返回哪些列的值,结果集
--from决定对哪些表查询
2).where子句
select * from 表名 where 条件....;
默认是对表中所有记录查询.如果需要提取一部分记录,需要用where指定过滤条件.满足指定条件的记录返回.
3).使用like条件(模糊查询)
用于模糊条件值的查询;适用于字符串类型字段
--%表示0到多个任意字符.
--_下划线表示1个任意字符
4)使用in和not in
//字段值等于值1或者等于值2或者等于值3...
字段名 in (值1,值2,值3...)
//字段值不等于给出的值列表中
字段名 not in (值1,值2,值3...)
5)使用between...and...
字段名 between 最小值 and 最大值
判断某个字段值在指定范围区间.即某个字段值大于等于?并且小于等于?
6)使用is null,is not null
判断某个字段值是否为null;
7)使用any和all条件
--字段名 >any(值1,值2,值3...)
含义:字段值大于指定集合中任意一个值,就算满足条件.(即工资大于最小的值)
--字段名>all(值1,值2,值3...)
含义:字段值大于指定集合中所有值,才算满足条件.(即工资大于最大的值)
8).select子句过滤重复记录功能
select [distinct] *或字段名...from 表名;
distinct作用是过滤重复的记录.两行记录如果各个字段值都相同才算重复;有一个字段不同都不算重复.
9).order by子句
作用是将查询结果进行排序.
select ...from ... order by 字段名... [asc或desc];
--asc表示升序,默认值
--desc表示降序
--排序字段名可以写一个或多个
提示:desc或asc只对前面一个字段有效.
10).聚合函数(分组函数)
max():取一组中的最大值
min():取一组中的最小值
avg():求一组的平均值
sum():求一组的合计值
count():统计一组中非空值的个数
--空值不参与分组统计和计算.
--一组执行一次返回一个结果.
默认所有记录算一组
11).group by子句
前面5个聚合函数,默认将所有结果当作一组进行计算.可以利用group by将结果分成若干小组,之后聚合函数会以小组为单位执行计算.
提示:SQL中使用了聚合函数,返回记录数由分组决定,有几组就会返回几行记录.
注意:当select后面使用了聚合函数,此外还有其他查询字段,其他查询字段必须在group by后面存在.聚合函数使用的字段可以不用写在group by后面.(Oracle必须按此规则编写,其他数据库例如MySQL有时可省略一两个字段)
12).having子句
having用于使用聚合函数条件过滤.
where子句中不能使用聚合函数.
----------完整SQL的SELECT结构-------------
select ...//显示哪些列信息
from ...//从哪些表查询
where ...//限定条件,不符合的过滤掉
group by ...//按指定字段分组
having ...//指定聚合函数过滤条件
order by ...//按指定字段排序
--------------------------------------------
13).使用别名
在select,from,where ,order by子句中经常会使用
别名.
-----from后面使用别名
from 表名 别名;
注意:在遇到多表关联查询时才使用表别名.
----order by后面使用别名
-----having使用别名
14).使用子查询
子查询可以用在where,having,from,select子句中
注意:子查询语句需要用()括起来.
1)where部分
a.单行子查询
例如使用>,<,>=,<=,=,<>条件时,子查询语句只能返回一个值.
b.多行子查询
例如使用in,not in,>any,>all等条件,子查询可以返回一列多行值.
2)having部分
3)from部分 //from后面子查询充当临时数据源
4)select部分
=================== day five========================
1.关联查询
在查询时,有时候需要提取两个或两个以上数据表的字段值,就需要采用关联查询.
1)笛卡尔积连接
select * from 表名1,表名2;
--*代表from后面所有表的所有列
--查询结果数量等于表1记录数*表2记录数
(表1中每行记录都要和表2记录结合一次形成一行结果)
*2)等值连接
两个表连接,有等值关联的条件字段.
--两个表必须有关联条件的字段
--返回记录有两个表决定,条件是关联字段值相等
//返回满足ta.id和tb.id字段值相等的记录
--------新等值连接写法-------------
语法: ...from 表a [INNER] JOIN 表b ON(关联条件)
3)外连接
*a.左外连接
--外连接返回记录由主表决定,主表记录至少返回一次.另一张表没有对等记录补null值
--left outer join关键字左边的表为主表
.....from 表a left outer join 表b on(关联条件)
-----------旧的外连接语法-----------------
注意:带(+)方是补充表,另一方为主表
b.右外连接
与左外连接相似,采用right outer join ... on(...)结构.
该关键字右边表为主表,以改表记录显示为主.
c.全外连接
--返回结果等于左外连接+右外连接,将重复记录去掉
4)join...using,left outer join ... using语法
作用与join...on一致. 当两个表的关联字段名字和类型都一致时,可以使用该语法结构.
注意:关联条件字段前面不能使用别名.
5)自连接
2.Oracle特有操作
1)rownum列
rownum通常被称为伪列.在表结构和select查询结果中Oracle会自动追加该列值.
该值为1,2,3,4,5....连续序列.
提示: Oracle中需要借助rownum列进行分页查询,取出指定部分的记录.
注意:利用rownum当过滤条件时,只能使用<和<=条件符号.不能使用>,>=,=(可以等于1,其他值不 支持)等条件不好.
--先写最内部select,按要求字段排序
--再写中间的select,将rownum列提取出来给个别名rn
--最后写最外部的select和where条件
1.高级操作
*1)分支判断函数
a.decode函数
格式:decode(字段或表达式,
值1,结果1,
值2,结果2,
... ...
默认结果)
当字段或表达式值等于值1返回结果1,等于值2返回结果2,都不满足返回默认结果.
b.case when...then...else...end
更适合做一些>,<或区间条件判断的函数处理.
2.集合操作
可以将多个select结果集进行合并,求差集等操作.
*a.union和union all(求并集)用于将两个select结果集合并.
union all:合并之后允许重复记录
union:合并之后过滤重复记录
b.intersect(求交集)
用于返回两个select结果集相同的记录.
c.minus(求差集)
用于返回第一个select有的,而第二个select没有的记录.
集合操作使用注意事项:
--两个select结果集列(字段)数必须相同
--两个select结果集列(字段)类型必须一致
--需要排序时,将order by写在后面的select语句中.
*3.排序(排列)函数(分析函数)
row_number(),rank(),dense_rank()排序函数.
使用格式如下:
row_number() over(partition by 字段1 order by 字段2 [asc|desc])
--将查询结果先按字段1进行分组
--小组记录按字段2进行排序
--然后利用排序函数生成编号.
1)row_number()
对记录进行排名,编号顺序且唯一.
---------------------------------
row_number()和rownum区别
--row_number()是Oracle10g开始提供的高级函数
--row_number()可以进行组内排序.而rownum是
对所有记录排序.
-------------------------------
2)rank()
对记录进行排名,编号允许重复,跳跃.
3)dense_rank()
对记录进行排名,编号允许重复,连续不跳跃.
4.高级分组函数(聚合函数)
使用格式:group by rollup(分组字段)
1)rollup
a.使用方法1
group by rollup(字段1);
--在原查询统计结果后面添加一行合计
--合计行记录字段1的值为null
--结果会默认按select后面字段顺序排序
b.使用方法2
group by rollup(字段1,字段2)
--在原查询结果上新增小计和合计记录
--按字段1生成小计记录
--在结果集最后生成合计记录
--结果集按默认规则排序,根据select后面字段升序排列.
2)cube
--cube(字段),单个参数功能与rollup一样,都可以追加合计行记录
--cube(字段1,字段2),多个参数,会分别按字段1和字段2进行小计.最后添加一行
合计记录
=================day07===========================
-----------------------------------
1.请你介绍下Oracle数据库主要的对象有哪些
表,约束,视图,序列,索引,同义词
特点:独立存储和管理,可以单独创建,修改和删除.
函数:分为单行函数和分组函数(多行函数)
单行函数:一行记录调用一次.例如字符函数,
数值函数,日期函数.
分组函数:一组记录调用一次.例如max,min,avg
count,sum聚合函数
2.约束
1)约束的作用
负责对表中数据进行验证,通过验证才能进入表中,可以保证表中数据完整性和正确性.
2)约束的类型
**主键约束:非空且唯一
*外键约束: 值必须符合在参考字段中存在
*唯一约束:唯一性,不能重复
检查约束:符合指定的检查条件
-**非空约束:不能为空
3)非空约束
a.建表时添加
create table 表名(字段 类型 not null,。。。。)
b.后期添加,通过修改实现
alter table 表名 modify 字段 类型 not null;
c.取消非空约束
alter table 表名 modify 字段 类型 Null;
4)主键约束
--非空且唯一
--可以作用在一个字段,也可以多个
--一个表只能创建一次主键(数量只能1个)
a.建表时添加(系统会自动分配一个约束名)
create table 表名(字段 类型 primary key,......);
create table 表名(字段 类型,。。。。。,constraint 约束名 primary key(字段));
b.后期添加
alter table 表名 add constraint 约束名 primary key(字段名)
c.删出主键
alter table 表名 frop constraint 约束名;
5)外键约束
--可以为null
--所参考的字段必须是主键或唯一约束的字段
EMP--deptno(外键,参考DEPT的deptno)
DEPT--deptno(主键或唯一约束)
--外键字段值必须在参考字段列存在
--外键约束数量可以使用多个
--一个外键约束作用在一个字段上
contraint name foreign key(value)
6)唯一性约束
--允许为null
--有值时,不允许重复
--作用在单个字段上
contraint name unique(name)
7)检查约束
--允许为null
--有值,必须符合指定的检查条件
--作用在一个字段上
--可以为不同字段指定多个约束
=====约束总结=======
--检查,外键,唯一性约束,允许写入null值,
写入null时,不会触发检测.
--主键一个表只能有一个主键约束
--外键约束必须参照一个主键或唯一约束列
===================
3.视图
1)什么是视图
--视图没有数据存储空间
--视图只存储了一个select语句
--对视图查询时,它需要去表中提取数据
2)视图的好处
将一个select查询语句存储,可以对视图查询.
--简化复杂的查询操作
--将重要和敏感信息隐藏
3)视图的操作
a.创建
//创建或更新某个视图
create or replace view 视图名
as
select语句;
4.序列操作
1)什么是序列,有什么作用
Oracle特有对象.相当于一个计数器.在Oracle中经常利用序列生成表的主键ID值.
2)序列操作
a.创建
//创建一个序列,从1开始计数,递增加1
create sequence 序列名;
完整语法格式如下:
create sequence 序列名
[start with 1|integer] //指定计数起始点,默认1
[increment by 1|integer] //指定递增量,默认1
[maxvalue integer|nomaxvalue]//指定最大值
[minvalue integer|nominvalue]//指定最小值
[cycle|nocycle]//是否循环计数,默认不循环
[cache size|nocache]//指定一次生成几个值放入缓存,默认nocache.
b.使用
//序列名.nextval将计数器当前值加1并返回
insert into foo_1 (id,name)
values (序列名.nextval,'scott');
//查看foo_seq序列计数器的当前值
select foo_seq.currval from dual;
c.删除
drop sequence 序列名;
5.索引
1)什么是索引,有什么用?
可以为某些字段创建索引,好处是可以提升
查询操作效率.
select empno,ename
from emp where ename='scott';
当在ename上创建索引后,会提高查询效率.
2)操作
a.创建
create index 索引名 on 表名(字段名);
---------示例-------
create index emp_ename on emp(ename);
b.删除
drop index 索引名;
3)索引使用注意事项
索引虽然可以提升查询效率,但不是越多越好,
因为它会耗费存储空间.
a.适合建立索引的字段
--主键,唯一性约束的字段
--常出现在where部分当检索条件的字段
--外键字段
--两个表的关联字段
--group by后面的字段
b.不适合建立索引的字段
--条件字段使用了函数
where upper(ename) = 'SCOTT';
--条件字段使用了表达式
where sal+100>1200;
--条件字段进行is null,is not null条件
where comm is null;
--条件字段使用了like
where ename like '%A%';
--条件使用了not或<>否定的判断条件
where ename <> 'scott';
where deptno not in(10,20)
--了解Oracle的编码
select userenv('language') from dual
1、两种数据存储形式
1)文件
---清楚数据在文件中的存储格式
---对数据的处理过程全部需要代码实现
---对文件的并发操作也要通过代码实现
2)数据库
---表(table)是数据库中存储数据的基本单位
2、数据库标准语言:SQL
----结构化查询语言(Structured Query Language)
------数据定义语言(DDL Data Definition Language)
------数据操作语言(DML Data Manipulation Language)
------事务控制语言(TCL Transaction Control Language)
------数据查询语言(DQL Data Query Language)
------数据控制语言(DCL Data Control Language)
3、DB 和 DBMS
1)数据库(DATABASE)
---关系数据库使用关系或二维表存储信息。
2)关系型数据库管理系统(RDBMS)
-----关系型数据库管理系统(Relationship Database Management System)是一套软件,用在数据库中存储数据,维护数据,查询数据等等;
4、DDL 语句
1)create
----create table 表名(字段名 类型(范围),.....);
-----create table 表名 as select 语句;//基于一个表创建另一个表
2)drop
----drop table 表名;//放回回收站
----drop table 表名 purge;//不放回回收站
----flashback table 表名 to before drop;//从回收站恢复表
3)desc
----desc 表名;//查看表结构
4)添加一列字段
alter table 表名 add 字段名 类型;
5)修改字段类型和大小,是否允许为空
alter table 表名 modify (字段 类型);
提示:如果修改类型;或将存储空间大改成小的;修改成非空;可能会产生错误,原因是可表中现有数据冲突导致改表结构失败.
6)删除一列字段
alter table 表名 drop column 字段名;
7)修改表名字
rename 表名 to 新表名;
提示:表名最大长度30,一个中文占2个的话,最大用15个中文字符.避免名称与关键字冲突.不要以数字和_开始.
8)字段设置default默认值
在insert操作时,不为该字段指定值时,采用默认值写入.显式指定值时,指定什么写入什么,null也可以写入.
....类型 default 数值,。。。
default可以用于insert语句和update语句
//删除主键约束
alter table foo_2 drop constraint foo2_pk;
/创建一个序列,从1开始计数,递增加1
create sequence 序列名 start with 值;
创建索引
create index 索引名 on 表名(字段名)
//创建或更新某个视图
create or replace view 视图名 as select语句;
===========================================================
//查看约束,使用user_constraints系统表
desc user_constraints;
select table_name,constraint_name,
constraint_type from user_constraints;
//查看用户表,使用user_tables系统表
select table_name from user_tables;
======================================================
5、DML 语句
a.添加
insert into 表名 (字段名1,字段名2...)values (值1,值2...);
b.更新
update 表名 set 字段名=值,字段名=值 [where 字段名=条件值]
c.删除
delete from 表名 [where 字段名=条件值]
6、TCL语句
a、commit 事物提交
b、rollback 事物回滚
7、DQL 语句
select *或字段名... from 表名 [where 字段名=条件值]
8、DCL 语句
负责权限控制.(由DBA使用)
create user...创建用户
grant 授权
revoke 收回权限
9、字符串函数
--concat(s1,s2)或者s1 || s2
--大小写转换
upper(s):将s转成大写
lower(s):将s转成小写
initcap(s):将s中单词首字母大写
--过滤空格
trim(s):过滤s中前后(左右)空格
ltrim(s):过滤s中前面(左)空格
rtrim(s):过滤s中后面(右)空格
--补位函数
lpad(s,n,char) : 当s不够n位时,在s左边补充char字符.
rpad(s,n,char):当s不够n位时,在s右边补充char字符.
注意:如果s超出n位,采取截取方式保留n位.
--截取函数
substr(s,begin[,size]):对s进行截取;begin表示从
第几个字符开始截取;size表示截取多少个字符
substr(s,begin):
如果begin为负数,表示从后向前取begin位字符
如果begin为正数,表示从begin位开始取到最后
--字符查找
instr(s,s1[,begin,n]):在s中查找s1第一次出现的位置.
begin表示从哪个字符开始查找;n表示第几次
匹配.
--计算长度
length(s):计算s字符个数.(一个中文算一个长度)
10、数值函数
-- round():四舍五入
--trunc():截取
trunc(n)一个参数取整,将小数截掉
trunc(n,s)保留s位小数,将多余的截掉
--mod():求余数
--ceil(n):向上取整(取大于或等于n的整数)
--floor(n):向下取整(取小于或等于n的整数)
11、日期操作
*1)日期类型
date:存储年月日小时分钟秒
timestamp:存储年月日小时分钟秒毫秒时区
上述类型定义时不需要指定大小.
*2)日期关键字
sysdate : 获取Oracle服务器系统当前时间
systimestamp : 获取Oracle服务器系统当前时间
to_date(s,format):将符合format格式的s字符串转成date
to_char(date,format):将date值按format转换成字符串
to_timetamp(s,format):将s字符串转成timestamp类型
format格式元素为yyyy-mm-dd hh:mi:ss
3)其它函数
--last_day(d):返回d日期中月份的最后一天的日期
--add_months(d,n):对d日期值的月份加n.n值可以为负数.
--next_day(d,星期几):从d日期开计算下一个星期几的时间日期.
--least(n1,n2):返回两个数值中小的那一个
--greatest(n1,n2):返回两个数值中大的那一个
--round(d):对日期的小时分钟秒部分进行舍入操作(12小时前的舍弃;
12小时后的日期加1)
--trunc(d):对日期的小时分钟秒部分截取掉.
--extract函数
extract(year from 日期值)//返回日期值的年
extract(month from 日期值)//返回日期值的月
extract(day from 日期值)//返回日期值的日
extract(hour from timestamp类型值)//返回小时
extract(minute from timestamp类型值)//返回分钟
extract(second from timestamp类型值)//返回秒
select extract(hour from systimestamp) from dual;
12、空值操作
--nvl(字段,值1):当字段值为null时返回值1;
字段值不为null返回字段值;
--nvl2(字段,值1,值2):当字段值不为null返回值1;
当字段值为null返回值2;
-------------面试题----------------
drop table xxx;//删除表结构和数据,释放空间
delete from xxx;//删除表数据,结构还在.可以回滚
truncate table xxx;//删除表数据,结构还在,
//效率高,数据不能回滚
-------------------------------------
13、主键
a.主键特点
--非空,不允许为null
--唯一,不允许重复
--企业中一个表肯定会定义一个主键
--主键其实就是字段(可以由一到多个字段
承担主键职责)
--主键用于定位要操作的某一条记录
(利用主键值查询,最多返回一条)
--主键值一般不参与业务操作,不允许更新
b.3种定义方法
create table 表名(字段 类型 primary key,。。。。)
create table 表名(字段 类型,..........,constraint 约束名 primary key(字段));
//建完表之后添加主键
alter table 表名 add constraint 约束名 primary key(字段);
提示:如果字段数据违反主键规则,会创建失败.
14、查询语句
1).基本语句(最简单的结构)
select *或字段名... from 表名;
--*代表表中所有字段名
--select 决定返回哪些列的值,结果集
--from决定对哪些表查询
2).where子句
select * from 表名 where 条件....;
默认是对表中所有记录查询.如果需要提取一部分记录,需要用where指定过滤条件.满足指定条件的记录返回.
3).使用like条件(模糊查询)
用于模糊条件值的查询;适用于字符串类型字段
--%表示0到多个任意字符.
--_下划线表示1个任意字符
4)使用in和not in
//字段值等于值1或者等于值2或者等于值3...
字段名 in (值1,值2,值3...)
//字段值不等于给出的值列表中
字段名 not in (值1,值2,值3...)
5)使用between...and...
字段名 between 最小值 and 最大值
判断某个字段值在指定范围区间.即某个字段值大于等于?并且小于等于?
6)使用is null,is not null
判断某个字段值是否为null;
7)使用any和all条件
--字段名 >any(值1,值2,值3...)
含义:字段值大于指定集合中任意一个值,就算满足条件.(即工资大于最小的值)
--字段名>all(值1,值2,值3...)
含义:字段值大于指定集合中所有值,才算满足条件.(即工资大于最大的值)
8).select子句过滤重复记录功能
select [distinct] *或字段名...from 表名;
distinct作用是过滤重复的记录.两行记录如果各个字段值都相同才算重复;有一个字段不同都不算重复.
9).order by子句
作用是将查询结果进行排序.
select ...from ... order by 字段名... [asc或desc];
--asc表示升序,默认值
--desc表示降序
--排序字段名可以写一个或多个
提示:desc或asc只对前面一个字段有效.
10).聚合函数(分组函数)
max():取一组中的最大值
min():取一组中的最小值
avg():求一组的平均值
sum():求一组的合计值
count():统计一组中非空值的个数
--空值不参与分组统计和计算.
--一组执行一次返回一个结果.
默认所有记录算一组
11).group by子句
前面5个聚合函数,默认将所有结果当作一组进行计算.可以利用group by将结果分成若干小组,之后聚合函数会以小组为单位执行计算.
提示:SQL中使用了聚合函数,返回记录数由分组决定,有几组就会返回几行记录.
注意:当select后面使用了聚合函数,此外还有其他查询字段,其他查询字段必须在group by后面存在.聚合函数使用的字段可以不用写在group by后面.(Oracle必须按此规则编写,其他数据库例如MySQL有时可省略一两个字段)
12).having子句
having用于使用聚合函数条件过滤.
where子句中不能使用聚合函数.
----------完整SQL的SELECT结构-------------
select ...//显示哪些列信息
from ...//从哪些表查询
where ...//限定条件,不符合的过滤掉
group by ...//按指定字段分组
having ...//指定聚合函数过滤条件
order by ...//按指定字段排序
--------------------------------------------
13).使用别名
在select,from,where ,order by子句中经常会使用
别名.
-----from后面使用别名
from 表名 别名;
注意:在遇到多表关联查询时才使用表别名.
----order by后面使用别名
-----having使用别名
14).使用子查询
子查询可以用在where,having,from,select子句中
注意:子查询语句需要用()括起来.
1)where部分
a.单行子查询
例如使用>,<,>=,<=,=,<>条件时,子查询语句只能返回一个值.
b.多行子查询
例如使用in,not in,>any,>all等条件,子查询可以返回一列多行值.
2)having部分
3)from部分 //from后面子查询充当临时数据源
4)select部分
=================== day five========================
1.关联查询
在查询时,有时候需要提取两个或两个以上数据表的字段值,就需要采用关联查询.
1)笛卡尔积连接
select * from 表名1,表名2;
--*代表from后面所有表的所有列
--查询结果数量等于表1记录数*表2记录数
(表1中每行记录都要和表2记录结合一次形成一行结果)
*2)等值连接
两个表连接,有等值关联的条件字段.
--两个表必须有关联条件的字段
--返回记录有两个表决定,条件是关联字段值相等
//返回满足ta.id和tb.id字段值相等的记录
--------新等值连接写法-------------
语法: ...from 表a [INNER] JOIN 表b ON(关联条件)
3)外连接
*a.左外连接
--外连接返回记录由主表决定,主表记录至少返回一次.另一张表没有对等记录补null值
--left outer join关键字左边的表为主表
.....from 表a left outer join 表b on(关联条件)
-----------旧的外连接语法-----------------
注意:带(+)方是补充表,另一方为主表
b.右外连接
与左外连接相似,采用right outer join ... on(...)结构.
该关键字右边表为主表,以改表记录显示为主.
c.全外连接
--返回结果等于左外连接+右外连接,将重复记录去掉
4)join...using,left outer join ... using语法
作用与join...on一致. 当两个表的关联字段名字和类型都一致时,可以使用该语法结构.
注意:关联条件字段前面不能使用别名.
5)自连接
2.Oracle特有操作
1)rownum列
rownum通常被称为伪列.在表结构和select查询结果中Oracle会自动追加该列值.
该值为1,2,3,4,5....连续序列.
提示: Oracle中需要借助rownum列进行分页查询,取出指定部分的记录.
注意:利用rownum当过滤条件时,只能使用<和<=条件符号.不能使用>,>=,=(可以等于1,其他值不 支持)等条件不好.
--先写最内部select,按要求字段排序
--再写中间的select,将rownum列提取出来给个别名rn
--最后写最外部的select和where条件
1.高级操作
*1)分支判断函数
a.decode函数
格式:decode(字段或表达式,
值1,结果1,
值2,结果2,
... ...
默认结果)
当字段或表达式值等于值1返回结果1,等于值2返回结果2,都不满足返回默认结果.
b.case when...then...else...end
更适合做一些>,<或区间条件判断的函数处理.
2.集合操作
可以将多个select结果集进行合并,求差集等操作.
*a.union和union all(求并集)用于将两个select结果集合并.
union all:合并之后允许重复记录
union:合并之后过滤重复记录
b.intersect(求交集)
用于返回两个select结果集相同的记录.
c.minus(求差集)
用于返回第一个select有的,而第二个select没有的记录.
集合操作使用注意事项:
--两个select结果集列(字段)数必须相同
--两个select结果集列(字段)类型必须一致
--需要排序时,将order by写在后面的select语句中.
*3.排序(排列)函数(分析函数)
row_number(),rank(),dense_rank()排序函数.
使用格式如下:
row_number() over(partition by 字段1 order by 字段2 [asc|desc])
--将查询结果先按字段1进行分组
--小组记录按字段2进行排序
--然后利用排序函数生成编号.
1)row_number()
对记录进行排名,编号顺序且唯一.
---------------------------------
row_number()和rownum区别
--row_number()是Oracle10g开始提供的高级函数
--row_number()可以进行组内排序.而rownum是
对所有记录排序.
-------------------------------
2)rank()
对记录进行排名,编号允许重复,跳跃.
3)dense_rank()
对记录进行排名,编号允许重复,连续不跳跃.
4.高级分组函数(聚合函数)
使用格式:group by rollup(分组字段)
1)rollup
a.使用方法1
group by rollup(字段1);
--在原查询统计结果后面添加一行合计
--合计行记录字段1的值为null
--结果会默认按select后面字段顺序排序
b.使用方法2
group by rollup(字段1,字段2)
--在原查询结果上新增小计和合计记录
--按字段1生成小计记录
--在结果集最后生成合计记录
--结果集按默认规则排序,根据select后面字段升序排列.
2)cube
--cube(字段),单个参数功能与rollup一样,都可以追加合计行记录
--cube(字段1,字段2),多个参数,会分别按字段1和字段2进行小计.最后添加一行
合计记录
=================day07===========================
-----------------------------------
1.请你介绍下Oracle数据库主要的对象有哪些
表,约束,视图,序列,索引,同义词
特点:独立存储和管理,可以单独创建,修改和删除.
函数:分为单行函数和分组函数(多行函数)
单行函数:一行记录调用一次.例如字符函数,
数值函数,日期函数.
分组函数:一组记录调用一次.例如max,min,avg
count,sum聚合函数
2.约束
1)约束的作用
负责对表中数据进行验证,通过验证才能进入表中,可以保证表中数据完整性和正确性.
2)约束的类型
**主键约束:非空且唯一
*外键约束: 值必须符合在参考字段中存在
*唯一约束:唯一性,不能重复
检查约束:符合指定的检查条件
-**非空约束:不能为空
3)非空约束
a.建表时添加
create table 表名(字段 类型 not null,。。。。)
b.后期添加,通过修改实现
alter table 表名 modify 字段 类型 not null;
c.取消非空约束
alter table 表名 modify 字段 类型 Null;
4)主键约束
--非空且唯一
--可以作用在一个字段,也可以多个
--一个表只能创建一次主键(数量只能1个)
a.建表时添加(系统会自动分配一个约束名)
create table 表名(字段 类型 primary key,......);
create table 表名(字段 类型,。。。。。,constraint 约束名 primary key(字段));
b.后期添加
alter table 表名 add constraint 约束名 primary key(字段名)
c.删出主键
alter table 表名 frop constraint 约束名;
5)外键约束
--可以为null
--所参考的字段必须是主键或唯一约束的字段
EMP--deptno(外键,参考DEPT的deptno)
DEPT--deptno(主键或唯一约束)
--外键字段值必须在参考字段列存在
--外键约束数量可以使用多个
--一个外键约束作用在一个字段上
contraint name foreign key(value)
6)唯一性约束
--允许为null
--有值时,不允许重复
--作用在单个字段上
contraint name unique(name)
7)检查约束
--允许为null
--有值,必须符合指定的检查条件
--作用在一个字段上
--可以为不同字段指定多个约束
=====约束总结=======
--检查,外键,唯一性约束,允许写入null值,
写入null时,不会触发检测.
--主键一个表只能有一个主键约束
--外键约束必须参照一个主键或唯一约束列
===================
3.视图
1)什么是视图
--视图没有数据存储空间
--视图只存储了一个select语句
--对视图查询时,它需要去表中提取数据
2)视图的好处
将一个select查询语句存储,可以对视图查询.
--简化复杂的查询操作
--将重要和敏感信息隐藏
3)视图的操作
a.创建
//创建或更新某个视图
create or replace view 视图名
as
select语句;
4.序列操作
1)什么是序列,有什么作用
Oracle特有对象.相当于一个计数器.在Oracle中经常利用序列生成表的主键ID值.
2)序列操作
a.创建
//创建一个序列,从1开始计数,递增加1
create sequence 序列名;
完整语法格式如下:
create sequence 序列名
[start with 1|integer] //指定计数起始点,默认1
[increment by 1|integer] //指定递增量,默认1
[maxvalue integer|nomaxvalue]//指定最大值
[minvalue integer|nominvalue]//指定最小值
[cycle|nocycle]//是否循环计数,默认不循环
[cache size|nocache]//指定一次生成几个值放入缓存,默认nocache.
b.使用
//序列名.nextval将计数器当前值加1并返回
insert into foo_1 (id,name)
values (序列名.nextval,'scott');
//查看foo_seq序列计数器的当前值
select foo_seq.currval from dual;
c.删除
drop sequence 序列名;
5.索引
1)什么是索引,有什么用?
可以为某些字段创建索引,好处是可以提升
查询操作效率.
select empno,ename
from emp where ename='scott';
当在ename上创建索引后,会提高查询效率.
2)操作
a.创建
create index 索引名 on 表名(字段名);
---------示例-------
create index emp_ename on emp(ename);
b.删除
drop index 索引名;
3)索引使用注意事项
索引虽然可以提升查询效率,但不是越多越好,
因为它会耗费存储空间.
a.适合建立索引的字段
--主键,唯一性约束的字段
--常出现在where部分当检索条件的字段
--外键字段
--两个表的关联字段
--group by后面的字段
b.不适合建立索引的字段
--条件字段使用了函数
where upper(ename) = 'SCOTT';
--条件字段使用了表达式
where sal+100>1200;
--条件字段进行is null,is not null条件
where comm is null;
--条件字段使用了like
where ename like '%A%';
--条件使用了not或<>否定的判断条件
where ename <> 'scott';
where deptno not in(10,20)