1. 关系型数据库与非关系型数据库
基于关系模型的数据库
关系模型:以二维表形式(行和列)存储数据的模型。
Oracle mysql sqlServer.. db2
非关系型数据库: nosql not only sql
Redis(key-value) mongdb …
2.数据库认证
OCA,OCP,OCM
3.数据库数据类型
number 数值类型
number 范围-10的38次方 到 10的38次方
number(3) 能存3位整数,如果添加了小数时,自动四舍五入成整数插入
number(3,2) 表示三位有效数字,其中有两位小数,一位整数
char 字符类型 最大能保存2000字节
char(3) 能保存长度为3字节的字符(一个字母或数字占一个长度)中文占3个字节(utf-8编码时)
char(10) 如果插入的字符小于10字节,剩下的会用空格填满
缺点:浪费空间
优点:查询速度更快
varchar2(10) 字符类型 最大能保存4000字节
如果插入的字符长度为2字节,实际上占用的空间也为2
优点:节省空间
date 时间类型
to_date(‘2018-08-20 11:11:20‘,‘yyyy-mm-dd HH:mi:ss‘)
--把字符串日期转化为特定格式的date日期
yyyy --年
mm 月
dd 日
HH 时(12小时制) HH24(24小时制)
mi 分
ss秒
timestamp这是oracle9i对date数据类型的扩展。可以精确到毫秒。
clob(character large object)字符型大对象 最大4G
blob二进制数据 可以存放图片/声音4G一般来讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。
4.增删改查+创建表格
Create table 表名(
字段1数据类型,
字段2数据类型,
。。。。。
字段2数据类型
);
注意:最后一个字段不要,
查询
Select 字段1,字段2…. from 表名;
Select 字段1,字段2…. from 表名where条件;
Select * from 表名;(*代表所有字段)
插入
Insert into 表名values(值1,值2…);
注意:插入数据时,数据与表的字段顺序对应,数据类型也要对应
--指定字段插入
insert into test3(birth ,a1) values(sysdate,‘aaa‘);
insert into 表名(字段x ,字段y…) values(值x,值y);
修改
update test3 set a1=‘pig2‘ where a1=‘egg‘ ;
update 表名 set 字段1=值1 ,字段2=值2 where 字段x=值x ;
删除
delete from test3 where a1=‘pig2‘;
delete from 表名 where 条件;
drop
Drop table 表名;
删除表结构和数据
注意:只有当字母作为字符内容时,才区分大小写
表Student
Sno(学号)
sname
sage
sgender
number
Varchar2(20)
number
char(3)
插入数据
1001
egg
12
男
1002
狗蛋
15
男
1003
翠花
16
女
1004
花花
12
女
查询表中所有数据
Select * from student;
查询性别为男的学生信息(学号,名字,性别)
Select sno,sname,sgender from student where sgender=’男’;
查询学号为1002 的学生信息(学号,名字,性别)
Select sno,sname,sgender from student where sno=1002;
修改名字为“花花”的年龄,改为20岁
Update student set sage = 20 where sname=’花花’;
修改学号为1003的学生 性别为男
Update student set sgender=’男’ where sno=1003;
删除学号为1001的学生
Delete from student where sno=1001;
5.命名规范
必须以字母开头
·长度不能超过30个字符
·不能使用oracle的保留字
·只能使用如下字符 A-Z,a-z,0-9,$,#,_等
6.字段操作
--给表添加一个字段username varchar2(20)
alter table sc1 add(username varchar2(20));
--修改字段的数据类型
alter table sc1 modify(username varchar2(50));
--删除某个字段
alter table sc1 drop column username;
--修改字段名字(重命名)
alter table sc1 rename column age to scAge;
7. 操作符
算术运算符
+ - * / mod(x,y)
比较运算符
< > = != <> (不等于)
逻辑运算符
and(与) or (或) not
8. Null
Null和空格和0是不一样的null表示空,没有赋值
--7.查询奖金为null的员工信息
select * from emp where comm is null;
--8.查询奖金不为null的员工信息
select * from emp where comm is not null;
当插入数据时,不插入值,用null
insert into test3 values(‘111‘,null);
9. Nvl()
--10.查询所有员工的奖金(如果奖金没有的用0表示)
select empno, nvl(comm,0) from emp;
--nvl(a,b)如果a的值为null ,取b ,否则取a
10.别名
select * from emp A where A.ENAME=‘SCOTT‘;
select empno "员工编号" from emp;
select empno as "员工编号" from emp;
select empno noo from emp;
select empno as noo from emp;
select empno as "Noo" from emp;
注意:别名不要用单引号
11.in
用in 查询指定散点集合,可用于子查询
--查询上级编号为7369,7698,7839的员工信息
select * from emp A where A.Mgr in(7369,7698,7839);
--查询和SCOTT同一部门的员工信息(子查询)
select * from emp where deptno
in(select deptno from emp where ename=‘SCOTT‘ );
12.模糊查询like
-- % 代表0个或多个字符
-- _代表一个字符
--查询emp中名字带有A的员工信息
select * from emp where ename like ‘%A%‘;
--查询emp中名字第二个字母为M的员工信息
select * from emp where ename like ‘_A%‘;
--查询emp中名字首字母为S的员工信息
select * from emp where ename like ‘S%‘;
--查询emp中名字以R结尾的员工信息
select * from emp where ename like ‘%R‘;
--查询名字中包含%的学生信息(转义)
select * from sc2 where scname like‘%\%%‘ escape ‘\‘;
--把\作为转义的符号 要把% 转义 —> \%
13.连接符||
select 6||‘asd‘ as "连接后的结果" from dual;
14. 排序 order by + 要排序的字段
desc 降序
asc 升序(不写默认)
select * from emp order by empno asc;
select * from emp order by hiredate,sal;
--按多个字段排序,先排前面的字段,再后面的字段
15. 聚合函数
count() max() min() sum() avg()
16. 子查询
内部查询的结果作为外部查询的条件
17. 分组 group by
按照一定的规则进行分组,分组后的数据会聚合,需要使用聚合函数,使用聚合函数不一定要分组。
Select 的字段只能是分组的字段或聚合函数。
where和having的区别:
-- where是一次筛选,使用在分组之前,筛选的字段可以是任意的(不能是聚合函数)
-- having是二次筛选,使用在分组查询之后,紧跟着group by ,
有having,一定有group by ,有group by 不一定有having
--筛选的字段只能是分组字段或聚合函数
18. 去重distinct
去除指定字段的重复值
select distinct scname from sc2 ;
--去除scname重复的数据(如果有多个重复,只会留一条)
select distinct * from sc2 ;
--查询能作为上级的员工信息
select * from emp where empno
in (select distinct mgr from emp where mgr is not null );
19. nvl2()
nvl2(a,b,c)
如果a是null,取c,否则取b
20. 表连接
内连接,左外连接,右外连接,全外连接,交叉连接
内连接: inner join …. on
两张表互相匹配到的数据显示出来
select * from t_stu A inner join t_sco B on A.TNO=B.TNO;
select * from t_stu A , t_sco B where A.TNO=B.TNO;(自然连接)
左外连接
以左表为主表,主表的数据全部显示出来,从表的数据匹配到的显示出来
select * from t_stu A left join t_sco B on A.TNO=B.TNO;
select * from t_stu A , t_sco B where A.TNO=B.TNO(+);
右外连接
以右表为主表,同上
select * from t_stu A right join t_sco B on A.TNO=B.TNO;
select * from t_stu A , t_sco B where A.TNO(+)=B.TNO;
全外连接
两张表的数据全部显示出来
select * from t_stu A full join t_sco B on A.TNO=B.TNO;
交叉连接(笛卡尔积)
两张表的乘积
select * from t_stu A cross join t_sco B ;
select * from t_stu A ,t_sco B ;
自连接
自己连自己(当作是内连接的一种)
21. 常用函数
to_date
to_char()
集合运算(交集并集差集),序列,主键,外键,约束,索引, 分页,行转列,列转行,视图,三范式(递归查询)
22. 集合运算
--集合运算
--交集,并集{2,3,4} {2,5,9},差集
select * from t_student for update;
select * from t_teacher;
--1.查询所有学生和老师的姓名、性别、生日
--union【all】并集
select tname ,tsex,tbirthday from t_teacher
union
select sname ,ssex,sbirthday from t_student;
--union公共部分只会显示一次(会去重)
--union all公共部分会显示多次(不会去掉重复的)
--交集:求公共部分
--查询emp工资大于2000,并且在20部门的员工姓名,工种
--intersect
select ename,job from emp where sal > 2000
intersect
select ename,job from emp where deptno=20;
select ename,job from emp where sal > 2000
and deptno=20;
--差集
--minus
--查询工资大于2000,但是部门不在20
select * from emp where sal > 2000
minus
select * from emp where deptno=20;
select * from emp where sal>2000 and deptno !=20;
注意:做集合运算的两个集合的字段个数,字段类型要一致(兼容),顺序也要一致,比如:ename ,job,hiredate 和ename hiredate job 是不一样的。
23. 约束
概念:加在表上的规则或条件
约束分类:非空约束,主键约束,外键约束,唯一约束,检查约束
(1)非空约束
create table tt(
t_id number,
t_name varchar2(50) not null,
t_sex char(3)
);
t_name添加了非空约束,该字段不能插入空的数据(null)
(2)唯一约束unique
create table tt(
t_id number unique,
t_name varchar2(50) not null ,
t_sex char(3) check(t_sex in(‘男‘,‘女‘))
);
alter table tt add constraint t1 unique(t_name);
--表名约束名字段
(3)检查约束
t_sex char(3) check(t_sex in(‘男‘,‘女‘))
Alter table tt add constraint my check(length(t_name)<6);
(4)主键约束(主键)primarykey
唯一的,不为空的(唯一标识),每张表都会有主键
alter table tt add constraint my_key primary key (t_id);
主键和唯一键的区别
主键是唯一不为空的,唯一键 可以为空,但是只有一个空
(5)外键约束(外键)foreignkey
进行两张表的强制关联,外键一般是另外一张表的主键或者是唯一键
物理外键:
逻辑外键:
3. 序列 (掌握)
用来提供主键值
--怎么创建序列?
create sequence seq_tt;--简单的创建方式
select seq_tt.nextval from dual;--得到序列的下一个值
select seq_tt.currval from dual;--得到序列的当前值
注意:创建的新序列,第一次不能执行currval(获取不到当前值)
--自定义序列
create sequence seq_my
start with 100 --起始值
maxvalue 120 --最大值
increment by 5 --增量
nocycle --是否循环cycle循环;nocycle不循环
; ·
drop sequence seq_my;--删除序列
4. 索引 (sql优化之一)
是一种的特殊的查询表
提高查询的速度
当做是书的目录
索引本身会占空间,索引不是越多越好
会减慢数据录入和删除的速度,做修改也要更新索引表
创建
create index myIn on t_student (sno);
删除
drop index myIn;
24.视图
是一种虚拟的表 ,是一种逻辑的概念,视图并不保存数据
是从表中抽取出来逻辑上相关的数据集合。
视图是建立在已有的表的基础上。视图赖以建立的这些表叫做基表。
可以理解为存储起来的sql 语句
优点:简化复杂查询
视图不能提高性能
--创建视图
create view myview as
select A.empno,B.dname from emp A ,dept B where A.Deptno=B.DEPTNO
with read only ;
drop view myview;--删除视图
select * from myview;--查询视图
25.三大范式
是设计数据库表应该遵循的规则
1nf 2nf 3nf bcnf 4nf 5nf dknf …
第一范式(1nf):原子性,将列(字段)分到不可再分为止
个人信息表: 姓名,年龄,地址
江西省南昌市南昌县..
要查看某个人的所在省份或所在市?
优化成:省,市,县。。详细地址
第二范式(2nf):满足第一范式的前提,非主键完全依赖于主键。(不存在部分依赖)
学生表:学号(主键),姓名,课程号,成绩
优化成:
学生表:学号 ,姓名
课程表:课程号 。。
成绩表:学号,课程号,成绩(中间关系表)(学号和课程号作为联合主键)
第三范式(3nf):满足第二范式的基础上,非主键列必须直接依赖于主键,不能存在传递依赖,减少冗余
举个例子:
学生表:学号,性别,系号,系主任
传递依赖:学号—系号—系主任
优化成:
学生表:学号,性别,系号
院系表:系号,系主任
注意:第三范式不一定要满足,反而有时特意添加冗余字段,减少表连接,提高查询效率
原文:https://www.cnblogs.com/lin02/p/11104159.html