20201029学习笔记:
oracle-1:
三范式(3NF):
第一范式(1NF):每一列都是不可分割的基本数据。
第二范式(2NF):在第一范式的基础上,表中的每个实例(行)必须可以被唯一的区分。这个唯一的属性列被称为主关键字或主键、主码。
第三范式(3NF):在第二范式的基础上,属性不依赖于其他表的非主属性。
**简而言之,最终的目的避免数据重复冗余,1NF–>列不可再分最小原子 (避免重复);2NF–>主键依赖(确定唯一);**3NF–>消除传递依赖(建立主外键关联 拆分表);
SELECT [DISTINCT] {*,column alias,..}
FROM table alias
WHERE 条件表达式
ORDER BY 排序字段列表 [asc|desc]
解析步骤: from -> where -> select -> order by
常见操作:
--查询所有列1, *通配符
select * from dept
/*distinct关键词去重 */
select distinct deptno from emp
--取别名 as 可省略 ""里面的原样输出,不然英文小写会转成大写
select ename as 姓名 from emp;
select ename 姓名 from emp;
select ename "姓 名" from emp;
--字符串拼接 ||
select 'sxt-'||ename as 姓名 from emp;
--伪列,不存在的列,构建虚拟的列
select ename,2*8 as num from emp;
--虚表
select 1+1 rom dual;
--null,null 遇到数字参与运算的结果为 null,遇到字符串为空串
nvl(comm,0) --表示如果comm为null就赋0,不然就按它原本的值
--where过滤行记录条件,
a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
-- <>相当于!=
between 800 and 1000 指800到1000范围内
b)、and 、or、 not、 union、 union all、 intersect 、minus
union:并集(去重) union all 全集(不去重) intersect 交集 minus 差集
c)、null :is null、 is not null、 not is null
comm is not null not comm is null
d)、like :模糊查询 % _ escape('单个字符')
使用escape('单个字符')指定转义符
select * from emp where ename like '%e%%' escape('e') --e代表转义,查找ename中包含%号的
f)、in 、 exists(难点) 及子查询
select * from emp where sal in(2000,1000,1500); --把需要的枚举出来
exists条件为true,存在记录则返回结果,后续不再继续比较查询,与查询的字段无关,与记录有关
排序:
使用 ORDER BY 排序,排序不是真实改变存储结构的顺序,而是获取的集合的顺序。
顺序 :asc(默认) desc
多字段: 在前面字段相等时,使用后面的字段排序
空排序: 降序为 desc,注意 null 为最后
select * from emp order by comm; --默认升序(asc),默认null放最后
select * from emp order by sal ,comm desc; --按照sal升序,相同时,按照comm降序
select * from emp order by comm nulls first; --按照comm升序,null值放前面进行排序
select * from emp order by comm nulls first,sal desc nulls last;
20201030学习笔记:
oracle学习第二天:
理解SQL语句运行的顺序,多练习sql语句。
函数:
系统内置函数:to_date , to_char
自定义函数:
根据函数的返回结果,也可将函数分为单行函数,和多行函数。
单行函数:处理一行,返回一行。
多行函数:处理多行,返回一行。
日期函数:
sysdate/current_date --以date类型返回当前的日期
add_months(d,x) --返回加上x月后的日期d的值
last_day(d) --返回所在月份的最后一天
months_between(date1,date2) --返回date1和date2之间月的数目
next_day(sysdate,'星期五') --返回下一个星期五(如果今天是星期四,那就是明天)
select sysdate+2 from dual; --两天后的时刻
select add_months(sysdate,5) --五个月后的时间,如果当前为31号,而5个月后没有31号,就会返回五个月后的月份的最后一天
months_between(sysdate,current_date) --月份之差
to_date(c,'yyyy-mm-dd hh24:mi:ss') --字符串以指定格式转换为日期。
to_char(date,'yyyy-mm-dd') --日期以指定的格式转换为字符串
--注意中文问题
to_char(sysdate, 'yyyy"年"mm"月"dd"日"')
组函数:
avg 、sum、min、max、count
avg() --求平均值
sum() --求值的合
min() --找最小值
max() --找最大值
count() --统计记录数
注意:
1.组函数仅在选择列表和Having子句中有效
2.出现组函数,select只能有组函数或分组字段
说明:
- 组信息与单条记录不能同时查询
- 组函数不能用在where中,能使用的地方select,having
- null不参与运算
分组:
分组:group by 字段名 将记录以字段名进行分组操作
过滤组:having,过滤组信息,
select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc
解析步骤:
form —> where —> group by —> having —> select —> order by
group by : 分组
- select出现分组函数,就不能使用非分组信息,可以使用group by字段
- group by字段可以不出现select中,反之select除组函数外的,其他字段必须出现group by中。
having:过滤组
- where:过滤行记录,不能使用组函数
- having:过滤组 可以使用组函数
行转列:
select name,
min(decode(course, '语文', score)) 语文, --null值不参与运算
min(decode(course, '数学', score)) 数学,
min(decode(course, '英语', score)) 英语
from tb_student
group by name;
表连接(92):
笛卡尔积:
非*且存在同名必须区分,使用表名.或别名区分
select * from emp,dept;
等值连接(在笛卡尔积的基础上取条件列相同的值)
select * from emp,dept where emp.deptno=dept.deptno;
非等值连接:
select ename, grade, sal
from salgrade s, emp e
where sal between losal and hisal;
自连接:特殊的等值连接(来自于同一张表):
select e.ename, m.ename from emp e, emp m
where e.mgr = m.empno;
外连接
看from后面表之间的逗号,主表在逗号的左边就叫左外连接,主表在逗号的右边叫右外连接。主表的对面需要写上(+)
select *
from emp e1, emp e2
where e1.mgr = e2.empno(+)
表连接(99):
- 交叉连接 cross join —>笛卡尔积
- 自然连接(外主键、同名列)natural join —>等值连接
- join using 连接(同名列) —>等值连接
- [inner] join on 连接 —> 等值连接 非等值 自连接(解决一切)关系列必须区分
- left|right [oyter] join on |using —>外连接
- full join on|using -->全连接 满足直接匹配,不满足相互补充null,确保所有的表的记录都至少出现一次
--交叉连接
select * from emp e1 cross join emp e2
--自然连接
select * from emp e1 natural join dept d
--join using连接() 等值连接
select * from emp e1 join dept d using (deptno)
--on连接
select * from emp e1 join dept d on (e1.deptno=d.deptno)
--外连接 左外连接
select * from emp e1 left outer join dept d on (e1.deptno=d.deptno)
--外连接 右外连接
select * from emp e1 right outer join dept d on (e1.deptno=d.deptno)
--全连接
select * from emp e1 full join dept d on (e1.deptno=d.deptno)
表复制:
create table 表名 as select 字段列表 from 已有表 where 1!=1;
--拷贝结构 emp
create table emp_his as select ename,sal from emp where 1!=1;
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from emp where sal>2000;
20201031学习笔记:
约束:
primary key 主键
unique 唯一
check( 字段名 in('男','女'))
check(age>0 and age <120)
not null
default()
rowid:
用于定位数据库中的一条记录的一个相对唯一的地址值,是一个伪列。
rownum:
是一种伪列,会根据返回记录生成一个序列画的数字,如果和排序同时用的话。
SELECT ROWNUM,EMPNO,ENAME,JOB FROM EMP ORDER BY EMPNO
--如果order by的是主键则rownum是按顺序的,否则顺序是打乱的,但可以用select语句来消除
视图view:
视图:建立在表|结果集|视图上的虚拟表,
-
简化select查询语句,相当于建了一个需要的表
-
重用:封装select语句 命名
-
隐藏:内部细节
-
区分:相同数据不同查询
不是所有的用户都有创建视图的权限:
-
授权: -->sqlplus /nolog
a)、sys登录 conn sys/123456@orcl as sysdba
b)、授权: grant dba to scott;
回收: revoke dba from scott;
c)、重新登录
-
create or replace view 视图名 as select语句 [with read only];
要求:所有列必须存在名称。
索引:
提高查询速度的一种手段 -->目录
1、唯一性较好字段适合建立索引
2、大数据量才有效果
3、主键|唯一: 唯一索引
//创建索引
CREATE INDEX 索引名 ON 表名(列名);
//删除索引
DROP INDEX 索引名;
创建表的同时添加约束:
create table sxt_student(
--主键约束 (唯一+非空) primary key
sid number(4) primary key,
--非空约束
sname varchar2(30) not null,
--检查约束 0~120
sage number(3) check(sage>=0 and sage<=120),
--检查约束
sgender char(3) check(sgender in('男','女')),
--唯一约束
phonenum number(11) unique,
--默认值
hiredate date default(sysdate),
cid number(4) --所在班级编号
);
--创建表的同时添加约束 指定约束名
--1) 在字段后面指定约束名字添加约束
--2)在表结构结束之前为字段添加约束
create table sxt_student(
--主键约束 (唯一+非空) primary key
sid number(4) constraints pk_student primary key,
--非空约束
sname varchar2(30) constraints stu_name_not_null not null,
--检查约束 0~120
sage number(3) constraints check_age check(sage>=0 and sage<=120),
--检查约束
sgender char(3), --check(sgender in('男','女')),
--唯一约束
phonenum number(11) unique ,
hiredate date default(sysdate),
cid number(4), --所在班级编号
--统一添加约束
constraint haha check(phonenum is not null)
-- .....
);
--在表结构结束之后追加约束
alter table sxt_student add constraint ck_student_gender check(sgender ='男' or sgender = '女');
--约束的禁用与启用
ALTER TABLE tb_user disable constraint nn_user_name;
ALTER TABLE tb_user enable constraint nn_user_name;
--外键约束
create table sxt_class(
cid number(4) primary key,
cname varchar2(30)
);
create table sxt_student(
sid number(4) constraints pk_student primary key,
sname varchar2(30),
--外键约束 关联班级表的主键
cid number(4) , --constraints fk_stu_cid references sxt_class(cid)
constraints fk_stu_cid foreign key(cid) references sxt_class(cid) on delete cascade
);
删除 (先删除从表 再删除主表 ;同时删除约束)
--主外键关系下删除表
--删除从表,直接删除,没有影响
--删除主表 : 默认先删除 从表在删除主表 ,否则就级联删除主表+主外键约束
drop table sxt_student cascade constraints;
drop table sxt_class cascade constraints;
主外键关系下,删除数据:
ORACLE 数据库支持级联删除,但要看你的约束是怎么建的
1.ON DELETE CASCADE (级联删除)
2.ON DELETE SET NULL (删除主表,子表列设置NULL)
没有设置上面两种方式,缺省是不允许删除,必须遵循下面的方式
删除主表的记录时,你必先删除子表的记录才能够删主表的记录
删除子表的记录时,你可以直接删除
--主外键关系下,删除数据:
--从表数据可以直接删除
--删除主表数据:
--没有被从表引用的数据,可以直接删除
--删除主表中已经被从表引用的数据: 3种解决方案
--先删除引用了当前主表数据的那些从表数据,然后再删除当前主表数据 默认
--删除主表数据的同时,删除从表中引用了当前主表数据的那些从表数据 on delete cascade
--删除主表数据的同时,从表中,引用了当前主表数据的那些从表数据的外键字段设置为null
–事务的作用:使用事务是为了保证数据的安全有效
–事务: 单个逻辑单位 执行的一组操作,以事务为单位,在一个事务声明周期之间额的一组 操作要么都成功,要么都失败
–事务的开启: 执行增删改操作自动开启
–事务的结束:
–提交 : commit 执行DDL DCL 正常关闭客户端 自动提交
–回滚: rollback 以外退出非法断电
–DDL: 数据定义语言 表创建create 表修改alter 表删除drop
–DML: 数据控制语言 增删改数据 insert update delete select
–delete 与 数据截断的区别
–共同点: 都能作为删除数据
–不同点: delete执行会开启事务,truncate不会开启事务
–数据截断删除主表中数据,会从表结构上检查 ,如果有被引用不允许使用截断,delete可以,三种情况
--删除主表数据的同时,从表中,引用了当前主表数据的那些从表数据的外键字段设置为null
--事务的作用:使用事务是为了保证数据的安全有效
--事务: 单个逻辑单位 执行的一组操作,以事务为单位,在一个事务声明周期之间额的一组 操作要么都成功,要么都失败
--事务的开启: 执行增删改操作自动开启
--事务的结束:
--提交 : commit 执行DDL DCL 正常关闭客户端 自动提交
--回滚: rollback 以外退出非法断电
--DDL: 数据定义语言 表创建create 表修改alter 表删除drop
--DML: 数据控制语言 增删改数据 insert update delete select
--delete 与 数据截断的区别
--共同点: 都能作为删除数据
--不同点: delete执行会开启事务,truncate不会开启事务
--数据截断删除主表中数据,会从表结构上检查 ,如果有被引用不允许使用截断,delete可以,三种情况