Oralce_DDL

--创建表
--使用create table语句建立学生信息表,该表共包括学号（id）、姓名（name）、年龄（age）、入学日期（sdate）、年级（grade）默认值为1、班号（class）、email等信息
create table student (
id number(6)  not null,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)
)
select * from student
insert into student (id) values (10012)
desc student
--使用create table语句建立班级信息表,该表共包括班级号(id)、班名(name)等信息
create table class(
id number(6),
name varchar2(50)
)
desc class
--创建一张和emp一样表结构的表
create table emp2 as select * from emp
select * from emp2
--创建一张包含雇员表中雇员名和部门表中部门名的表
create table emp_dept as select ename,dname from emp inner join dept on emp.deptno=dept.deptno
desc emp_dept
select * from emp_dept

drop table student
desc student
--创建表时指定约束条件
--非空约束(字段级)  姓名不可以为空值
create table student (
id number(6),
name varchar2(20) not null,
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)
)
desc student
insert into student (name,age)values ('AA',10)
select * from student
drop table student

create table student (
id number(6),
name varchar2(20) constraint not_null_name not null,
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)
)
--唯一性约束(字段级/表级)  email字段不可以重复
drop table student

create table student (
id number(6),
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)  unique
)

create table student1 (
id number(6),
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)  ,
constraint unique_sdate_email unique(sdate,email)
)

insert into student (email)  values ('admin@163.com')
insert into student (email)  values ('admin@163.com')
--主键约束(表级)   学号为主键 表级 not null ,unique
drop table student
create table student2 (
id number(6) primary key,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50) 
)

insert into student2 (id,name) values (1,'AA')
insert into student2 (id,name) values (1,'BB')
create table student3 (
id number(6) ,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) ,
email varchar2(50)  ,
constraint pk_student2 primary key (id)
)

--外键约束(字段级/表级)   班级的取值必须为class表中的id值
create table class2 (
id number(8) primary key,
name varchar2(20)
)
create table student4 (
id number(6) ,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6) references class2(id),
email varchar2(50)
)
insert into class2 (id,name) values (1,'class_one')
insert into student4(class) values (1)
delete from class2

create table student5 (
id number(6) ,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6),
email varchar2(50) ,
constraint fk foreign key (class) references class2(id)
)
--检查约束(表级)  年龄不可以为负值
create table student6 (
id number(6) ,
name varchar2(20),
age number(3),
sdate date,
grade number(2) default 1,
class number(6),
email varchar2(50) ,
constraint check_age check (age>0)
)
insert into student6 (age) values (37)
select * from student6

--查看约束
select t1.constraint_name,t1.table_name,t1.column_name,t2.constraint_type
from user_cons_columns t1
inner join  user_constraints t2 on t1.constraint_name=t2.constraint_name
and t1.table_name=t2.table_name
where t1.table_name='STUDENT'

select * from user_constraints
select * from user_cons_columns
/*
综合练习
使用create table语句建立学生信息表
该表共包括学号、姓名、年龄、入学日期、年级、班级、email等信息
并且其中的学号为主键
姓名不可以为空值
年级默认为1年级
email字段不可以重复
班级的取值必须为class表中的id值
年龄不可以为负值

创建 频道表 channel 两个字段频道的id ，频道的名字name，频道的id是主键
创建 视频表 video  视频的id，视频的名称，视频的上传日期，视频的播放次数，视频的评分 ，视频所属的频道Id
视频的id是主键，视频的名称必填，视频的上传日期默认当前的系统时间，视频的评分0-5分之间，视频所属的频道Id是外键
*/

create table channel (
id number(10) primary key,
name varchar2(10)
)
create table video (
id number(10) primary key,
name varchar2(10) not null,
udate date default sysdate,
num number(10),
score number(2,1),
cid number(10) references channel(id),
constraint check_score check(score>=0 and score<=5)
)
insert into channel (id,name) values (1,'happy')
insert into video (id,name,num,score,cid) values (1,'love',1,2,1)
insert into video (id,name,num,score,cid) values (2,'you',1,2.5,1)
select * from video
delete from video

create table channel(
id number(4) primary key,
name varchar2(10)
);
create table video(
 id number(4) primary key,
channel_id number(4),
 name varchar2(10) not null,
 shangchuan_date date default sysdate,
 time number(3),
 score number(2) check(score>=0 and score<=5),
constraint video_channel foreign key(channel_id)references channel(id)
);

create table channel(
   id number(2),
   name varchar2(10),
   constraint pk_channel primary key(id)
)
create table video(
   id number(4) primary key,
   name varchar2(20) not null,
   upload_date date default sysdate,
   display_number number(2),
   score number(2) check(score>0 and score<6),
   channelId number(2) references channel(id)
)
insert into channel values(1,'娱乐')
insert into video(id,name,displaynumber,score,channelId) values(3,'嘻嘻',2,1,1)

--更新一行数据：update
update student set name='zhangyu' where id = 10012
update student set id = 10014 where id = 10011
update student set grade = 2 where name = 'zhangyu'
update student set name = '张哥哥' where id = 10011
select * from student

--删除一行数据：delete
delete from student where id = 10012
delete from student where id = 10011
insert into student (id,name) values (10011,'zhangfei')
insert into student (id,name) values (10013,'zhangyu')
insert into student (id,name) values (10014,'zhanggege')
insert into student (id,name) values (10012,'zhangmm')
delete from student where id = 10014
delete from student where name = 'zhangmm'
select * from student
--修改表结构或其约束条件

--添加/修改/删除字段
--增加一个家庭住址的字段

alter table student add(addr varchar2(100) )

--修改新增的addr字段的精度
alter table student modify(addr varchar2(200))
--修改addr字段名称：
alter table student rename column addr to zhang

--新增的addr字段进行删除
alter table student drop column zhang

--添加/删除约束
--添加class_id外键约束
alter table student add constraint unique_email unique(email)
alter table student add constraint sys_110 primary key(class)
--删除class_id外键约束
alter table student drop constraint SYS_C005319
alter table student drop constraint SYS_C005268
alter table student drop constraint sys_110
--在name字段上添加非空约束
alter table student modify(name varchar2(20) not null ) --前提是字段中还没有写入空值。
--将name字段上的非空约束去除
alter table student modify(name varchar2(20))
alter table student drop constraint unique_email
alter table student drop constraint SYS_C005261
desc student

--删除表
delete from student

--创建索引
--索引可以加快排序数据，加快搜索。
--要点：能改善检索操作的性能，一般用于数据过滤和数据排序。
--对stu表中的name字段建立索引
create index index_name on student(name)
select name from student
--对stu表中email、class两个字段的组合上建立索引
create index index_email_class on student(email,class)
--删除索引
drop index index_name

--视图
--创建视图
--平均薪水的等级最低的部门名称

--每个部门的平均薪水的等级 3条
--select * from salgrade
select deptno,avg_sal,grade
from
(select deptno ,avg(sal) avg_sal from emp group by deptno)
inner join salgrade on avg_sal between losal and hisal
--最低等级
select min(grade)
from
(select deptno ,avg(sal) avg_sal from emp group by deptno)
inner join salgrade on avg_sal between losal and hisal
--如果这个部门平均薪水的等级=最低等级

--部门no -》 部门名称
select dname from dept
where deptno=(
  select deptno
  from
  (select deptno ,avg(sal) avg_sal from emp group by deptno)
  inner join salgrade on avg_sal between losal and hisal
where grade=(
      select min(grade)
      from
      (select deptno ,avg(sal) avg_sal from emp group by deptno)
      inner join salgrade on avg_sal between losal and hisal
      )
)

--创建视图
create view dept_grade as
  select deptno,grade
  from
  (select deptno ,avg(sal) avg_sal from emp group by deptno)
  inner join salgrade on avg_sal between losal and hisal

select dname from dept
where deptno=(
  select deptno
  from 
     dept_grade
  where grade=(
      select min(grade)
      from
      dept_grade
)
)
--删除视图

--序列
--创建序列
--使得student表中的id自增
create sequence seq_student_xx start with 1 increment by 1

--使用序列

insert into student (id) values (seq_student_xx.NEXTVAL)
select * from student
select seq_student_id.CURRVAL from  dual
--删除序列
drop sequence seq_student_xx

版权声明：QQ:597507041