一 数据库部分
-
数据库实例: 是一个非固定的、基于内存的基本进程与内存结构。当服务器关闭后,实例也就不存在了。jdbc plsqldeveloper
数据库(Database)指的是固定的、基于磁盘的数据文件、控制文件、日志文件、参数文件和归档日志文件等
一般情况下, 一个数据库对应一个实例, 但是在特殊情况下我们也可以创建多个数据库 和多个实例
例如: 集群 多个实例 对应一个数据库, 实现负载均衡 ,或者失败迁移
- Oracle数据库的数据类型
字符串: char 固定大小, 如不满足则自动补齐
varchar2 大小可变
数字类型:
number 最多24位,其中小数最多4位
number(最大位数,小数位数)
日期类型
date 存储日期和时间,精确到秒。时间值
默认存放格式:“DD-MON-YYYY”
默认显示格式:“DD-MON-YY"
TIMESTAMP 存储日期、时间和时区信息,带小数位的秒。时间戳 TIMESTAMP(3) 秒后面小数点为3 位。(最多可9位)
大对象
CLOB:Character LOB,用于存储字符数据。
BLOB:Binary LOB,用于存储二进制数据。如图形、视频剪辑和声音文件。
BFILE:用于存储二进制文件指针
-
SQL语法:
关键字不区分大小写。
表名和列名不区分大小写。 SQL中涉及到的值是区分大小写的.
语句以分号;结束
注释使用– -
SQL分类
DQL—数据查询语言:定义查询表的操作。–select
SELECT查询语句:检索数据库表中存储的行。 DML—数据操纵语言:定义修改表的内容的操作。
INSERT、DELETE、UPDATE等:修改语句 DDL—数据定义语言:定义对数据库对象的操作。
CREATE、ALTER、DROP、RENAME、TRUNCATE等
DCL—数据控制语言:定义修改数据库结构的操作权限。
GRANT、REVOKE等。
TCL—事务控制语言:用于将对行所做的修改永久性地存储到表中,或取消这些修改操作。
COMMIT、ROLLBACK、SAVEPOINT等。 -
常用的sql语句
1. 查询指定表的所有列数据: SELECT * FROM 表名; 查询指定表的指定列数据: SELECT 列名, 列名2… FROM 表名;
select 肯定会与from相连接 , 确定 查询哪个表中的数据
-
条件查询 where
WHERE子句中的条件表达式:
可以包括运算符(算术、比较、逻辑;
可以使用()。
可以使用常量、列、函数。
select * from emp where job = 'saleman' and sal <4000 ;
select * from emp where job in('saleman','mananger');
- 运算符的优先级 从高到地
算术 、连接{||}、比较、逻辑(NOT、AND、OR)
-
模糊查询
like 关键字 后面可以跟 _ 表示一个字符 %表示任意多个字符
select * from emp where ename like ‘%a%’; 姓名中包含a的
-
order by 排序
select * from emp order by sal desc; 根据工资降序排序
select * from emp order by empno asc; 根据员工编号升序排序
select * from emp where job='MANAGER' order by sal desc;
-
GROUP BY 分组
GROUP BY必须得配合聚合函数来用,分组之后你可以计数(COUNT),求和(SUM),求平均数(AVG)等。
select count(*) from emp group by job; 根据工作进行分组
SELECT COUNT(SAL) FROM EMP GROUP BY DEPTNO; 获取每个部门中工资的个数
select job, count(*) as con from emp where sal>2000 group by job;
having 针对于分组后的数据 再一次进行筛选 过滤(过滤分组条件)
select job, count(*) as con from emp where sal>2000 group by job having count(*) <3 ;
-
多个关键字书写顺序和 执行顺序
书写顺序: select --> from --> where --> group by–> having -->order by
执行顺序: from -->where -->group by -->having–> select --> order by
-
去重复 distinct
select distinct sal from emp;
-
limit 分页查询 – oracle中不支持这种写法 但是在sql语句中的方法 是从第2条开始 ,查10条
select * from emp limit 2,10;
-
在select语句的where子句中, regex 使用正则表达式过滤数据的
SELECT prod_name FROM products
WHERE prod_name REGEXP ‘.000’
ORDER BY prod_name;
-
伪列
伪列就像表的一个列,但是它并没有存储在表中。
常用的伪列有ROWID和ROWNUM
ROWID 是表中每一条记录的唯一标识符,数据库内部使用它来存储行的物理地址。ROWNUM 伪列 是SQL查询返回的结果集中每一行的行号
-
聚合函数
count 个数 sum 求和 avg 平均数 max最大值 min最小值
-
多表查询
1.多表查询的方式
JOIN_TYPE 连接查询的类型
有内连接:[INNER] JOIN 从表名 别名 ON 连接条件
等值连接:
非等值连接:
外连接
SELECT [DISTINCT] *|[列名 别名,…]
FROM 主表名 别名
(LEFT|RIGHT|FULL) [OUTER] JOIN 从表名 别名 ON 连接条件
[WHERE 条件];
左表(左外连接)
右表(右外连接)
两个表(全外连接)
交叉连接 :SELECT … FROM join_table CROSS JOIN join_table2;2.sql92和SQL99的区别
SQL92和SQL99都是用来表示多表的联合查询使用的,两者在开发中,具体使用哪一种都是可以的,但是在书写和阅读的过程中,具体表现在以下:
1、交叉连接产生 会产生笛卡尔积中的区别①SQL92中的笛卡尔积:select * from emp,dept
②SQL99中的笛卡尔积:select * from emp cross join dept
2、等值连接方面的区别①SQL92表示:select * from emp,dept where emp.deptno=dept.deptno
②SQL99表示:select * from emp innner join dept on emp.deptno=dept.deptno
3、不等值连接方面①SQL92表示:select * from emp ,salgrade where emp.sal<=salgrade.hisal and emp.sal>=salgrade.losal
②SQL99表示:select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
4、左外连接①SQL表示:select * from emp ,dept where emp.deptno=dept.depetno(+)
②SQL99表示:select * from emp left outer join dept on emp.deptno=dept.deptno
5、右外连接①SQL92实现:select * from emp ,depet where emp.deptno = dept.deptno;
②SQL99实现:select * from emp righ outer join dept on emp.deptno=dept.deptno
6、全完连接①SQL92实现:select * from emp,dept where emp.deptno(+) = dept.dpetno(+)
②SQL99实现:select * from emp full outer join dept on emp.deptno=dept.deptno;
7、内连接①SQL92实现:select e1.,e2.ename from emp e1,emp e2 where e1.deptno=e2.deptno
②SQL99实现:select e1.,e2.ename from emp e1 inner join emp e2 on e1.deptno=e2.deptno -
子查询
当进行查询的时候,需要的条件是另外一个select语句的结果,这个时候,就要用到子查询。
嵌入到另一个查询语句之中的查询语句
为了给主查询(外部查询)提供数据而首先执行的查询(内部查询)被叫做子查询。
子查询可以出现在任意的位置。
包括:SELECT子句、FROM子句、WHERE子句、GROUP BY、HAVING、ORDER BY等。
子查询的分类
单行单列子查询:
单行比较操作符:= 、!= 、>、>=、<、<=
多行单列子查询:
多行比较操作符:IN、NOT IN、ANY、ALL
IN:检查指定值是否包含在一个值列表中。
ANY:用指定值与一个列表中的任意一个值进行比较。
ALL:用指定值与一个列表中的所有值进行比较。
ANY和ALL必须组合=、!=、>、>=、<、<=来使用。
=ANY:与IN操作符相同
ANY:比列表中最小值更大
<ANY:比列表中最大值更小
ALL:比列表中最大值还要大
<ALL:比列表中最小值还要小
多列子查询:
可以把子查询返回的结果当作一张新表的数据来使用。
-
集合操作符
集合操作符:合并多个查询结果
UNION ALL:将多个查询结果合并到一个结果中,有重复行
UNION:将多个查询结果合并到一个结果中,没有重复行(并集) -
DML 数据操作语言
insert into emp (列,列.列)values(值,值,值);
update emp set 列= ‘值’ where 条件
delete from emp where 条件
特殊: truncate table tbl_name 不能指定某一行, 用于清空表中所有的行
执行后无法回滚,只能能作用于表,会清空表中的所有行,但表结构及其约束、索引等保持不变,会重置表的自增值,不会激活与表有关的删除触发器,后会使表和索引所占用的空间会恢复到初始大小
-
事务是用户定义的一个数据库操作序列,这些操作要么全做
要么全不做,是一个不可分割的工作单位。
事务具有四个特性:原子性、一致性、隔离性 和持久性。
-
表管理
创建表
CREATE TABLE [方案名.]表名(
列名 列类型 [default 默认值] [列级约束类型],
…
[CONSTAINT 表级约束]
); 修改表结构:
添加列:ALTER TABLE 表名 ADD(列名 类型 [DEFAULT 默认值]…);
修改列:ALTER TABLE 表名 MODIFY(列名 类型 [DEFAULT 值]…);
修改列名: alter table student rename column birthday to birth;
删除列:ALTER TABLE 表名 DROP COLUMN 列名;
为表重命名:
RENAME 表名 TO 新表名; 删除表:DROP TABLE 表名;
-
约束
NOT NULL | 指定列不允许存储空值 | 列级 |
---|---|---|
UNIQUE | 指定列只能存储唯一的值 | 表级,列级 |
ChECK | 指定列的值必须满足某种条件 | 表级,列级 |
PRIMARY KEY | 指定表的主键.由一列或多列组成,唯一标识表中的一行 | 表级,列级 |
FOREIGN KEY | 指定表的外键. 外键引用另外一个表的一列. 有朱外键关系的表在删除时:先删除子表,再删除父表 | 表级,列级 |
创建表时指定约束
CREATE TABLE [方案名.]表名(
列名 列类型 [default 默认值] [列级约束类型], …
[CONSTAINT 表级约束类型]
);
使用单独的SQL语句添加约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(要约束的列名);
ALTER TABLE 表名 ADD [CONSTRAINT 约束名]
FOREIGN KEY (要添加外键约束的列名) REFERENCES 主表名(主表的列名);
删除约束:
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
**unique唯一约束表示值不允许重复**
- 序列: sequence
序列(sequence)是一种用于生成唯一数字的数据库对象,序列生成器会自动生成顺序递增的序列号。
CREATE SEQUENCE 序列名
[INCREMENT BY 递增量值]
[START WITH 开始值]
[MAXVALUE 最大值]
[MINVALUE 最小值]
[CYCLE | NOCYCLE] -- 达到最大值或最小值时是否循环
[CACHE 预生成数量 | NOCHE]; -- 默认缓存20个
使用序列:通过序列的伪列来引用序列值
NEXTVAL:返回下一个序列值。可用做某个表的主键值
CURRVAL:返回当前序列值。仅用于查看
-
视图(VIEW):一个或多个基表上的预定义查询。
视图是虚拟表,它在存储时只存储视图的定义,而没有存储对应的数据
视图只在使用时才从基表中搜集数据,再展现给用户使用视图优点:
安全:防止未经许可的用户访问敏感数据
数据独立:降低数据库的复杂程度
视图可以间接对表进行更新,视图中数据的更新就是表数据的更新CREATE [OR REPLACE] VIEW 视图名
AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];查询视图中内容
select * from 视图名字
更新视图
replace view 视图名字 AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];
在视图上定义新的视图
create view 新视图名字
as 查询语句 (查询语句中可以存在 原来视图)
-
数据库设计范式
第一范式的目标:确保每列的原子性。
第二范式的目标:确保每列都和主键相关 。
第三范式的目标:确保每列都和主键列直接相关,而非间接相关。