-- 20181106讲课内容

-- DDL 语句

创建表

--创建年级表tb_grade
create table tb_grade(
gradeId number(6) primary key, --主键
gradeName varchar2(20) not null, -- 年级名称
addtime date
);
-- 创建班级表 tb_classs
create table tb_class(
classId number(6) primary key,
className varchar2(20) not null,
gradeId number(6) not null,
classNum number(3),
beginTime date,
endTime date,
constraint ck_classnum check (classNum>=20 and classNum<=50),

检查/外键约束

-- 检查约束，班级人数不能小于20不能大于50
constraint fk_gradeid_tbgradeid foreign key(gradeId) references tb_grade(gradeId)
--外键，本表的某一个列指向另外一个表列（被关联表的字段必须是唯一的）
);

字段操作

-- 为已经存在的表添加字段
alter table tb_class add remark varchar2(50);
-- 更新字段(如果该列有数据，变换类型或者是长度变小，都不行，需要先清空数据，再更新)
alter table tb_class modify classNum number(5);
-- 删除字段
alter table tb_class drop column remark;

---

-- DML语句

时间

-- sysdate oracle中取系统当前时间,相当于mysql中的now()
select sysdate from dual; -- dual oracle中的虚表，物理上不存在，为了让语法健全，虚拟出来的表

增（union）

-- 单条添加
insert into tb_grade values(1,'一年级',sysdate);
-- 批量添加 union用法 合并查询结果（合并重复内容），如果是union all 不合并重复内容
insert into tb_grade
select 2,'2年级',sysdate from dual union
select 3,'3年级',sysdate from dual union
select 4,'4年级',sysdate from dual union
select 5,'5年级',sysdate from dual union --all
select 5,'5年级',sysdate from dual;

查

-- 查询
select * from tb_grade;
select * from tb_class;

join on

-- 查询每个年级下的班级数量（即使年级下没有班级也显示0），显示年级名称和班级数量
select * from tb_grade a join tb_class b on a.gradeid=b.gradeid;
select * from tb_grade a left join tb_class b on a.gradeid=b.gradeid;

group by

-- 在分组查询时select里面的所有字段在group by后面出现（除非使用聚合函数）
-- count 为null不计数
select a.gradename,count(b.classid) from tb_grade a left join tb_class b on a.gradeid=b.gradeid
group by a.gradename;
select a.gradename,count(b.classid) as cnum from tb_grade a left join tb_class b on a.gradeid=b.gradeid
group by a.gradename order by cnum desc;
--统计各个年级的总人数 nvl 相当于mysql ifnull
select a.gradename,nvl(sum(b.classnum),0) as cnum from tb_grade a left join tb_class b on a.gradeid=b.gradeid
group by a.gradename order by cnum desc;
-- 添加班级表
insert into tb_class(classid,classname,gradeid,classnum) values(1,'11班',1,20);
insert into tb_class(classid,classname,gradeid,classnum) values(2,'12班',1,24);
insert into tb_class(classid,classname,gradeid,classnum) values(3,'13班',1,21);
insert into tb_class(classid,classname,gradeid,classnum) values(4,'21班',2,25);
insert into tb_class(classid,classname,gradeid,classnum) values(5,'22班',2,25);
insert into tb_class(classid,classname,gradeid,classnum) values(6,'41班',4,25);
insert into tb_class(classid,classname,gradeid,classnum) values(7,'42班',4,25);
insert into tb_class(classid,classname,gradeid,classnum) values(8,'31班',3,25);

删除

-- 删除
delete from tb_grade where gradeid=7;
truncate table tb_grade ; --删除数据
-- delete 和truncate区别 1，delete 删除可以加条件 truncate不可以（只能整表删除）
-- 2，delete 删除时速度慢，删除时记录日志，如果误删数据，在一定时间内可以找回
-- truncate 删除速度快，不记录日志，无法找回

---

---

序列

-- 为什么使用序列 oracle创建表时无法使用自增，oracle自增功能通过序列实现
-- 序列的创建
create sequence seq_tbgrade_id -- 定义序列名称
start with 10 -- 定义开始值
increment by 1 -- 定义增量
minvalue 1 --最小值 不能高于开始值
maxvalue 9999999 -- 最大值（最大为27个9）
-- 序列的使用
-- nextval 下一个值 currval 当前值
select seq_tbgrade_id.currval from dual;--取当前值（必须先取下一个值）
select seq_tbgrade_id.nextval from dual; --取下一个值
-- 插入时使用
insert into tb_grade values(seq_tbgrade_id.nextval,'n年级',sysdate);
select * from tb_grade;
-- union 里面不能使用序列
select 1,'n年级',sysdate from dual union
select 2,'n年级',sysdate from dual union
select 3,'n年级',sysdate from dual;
select seq_tbgrade_id.nextval,'n年级',sysdate from dual union
select seq_tbgrade_id.nextval,'n年级',sysdate from dual union
select seq_tbgrade_id.nextval,'n年级',sysdate from dual;