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)主键约束(主键)primary key
唯一的,不为空的(唯一标识),每张表都会有主键
alter table tt add constraint my_key primary key (t_id); |
主键和唯一键的区别
主键是唯一不为空的,唯一键 可以为空,但是只有一个空
(5)外键约束(外键) foreign key
进行两张表的强制关联,外键一般是另外一张表的主键或者是唯一键
物理外键:
逻辑外键:
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):满足第二范式的基础上,非主键列必须直接依赖于主键,不能存在传递依赖,减少冗余
举个例子:
学生表:学号,性别,系号,系主任
传递依赖:学号—系号—系主任
优化成:
学生表:学号,性别,系号
院系表:系号,系主任
注意:第三范式不一定要满足,反而有时特意添加冗余字段,减少表连接,提高查询效率