Oracle学习笔记（三）

create table classInfo(
  cid number(4) primary key, --班级编号
  cname varchar2(100) unique not null, --班级名称
  cyear number(4), --入学年份
  clen number(1) --学制
);
创建学生表
create table stuInfo(
  stuNo number(4) primary key,
   stuName varchar2(10),
   cid number(4) constraint FK_cid references classInfo(cid),–添加行级约束
  stuAge number(3),
   sex varchar2(3),
  stuTel varchar2(11),
   addr varchar2(50)
);
drop table stuInfo

alter table stuInfo add constraint Fk_cid1 foreign key(cid) references classInfo(cid)

检查约束
alter table stuInfo add constraint ck_sex check(sex=‘男’ or sex=‘女’)
insert into stuInfo values(1,‘张三’，1001,18,‘张’,123456789,‘衡阳’)；–ORA-02991
select * from stuInfo

年龄在18-30
alter table stuInfo add constraint ck_stuAge check(stuAge between 18 and 30)
alter table stuInfo add constraint ck_stuAge check(stu>=18 and stuAge<=30)

查看约束
select * from user_constraints where table_name=‘STUINFO’

删除约束
alter table stuInfo drop constraint SYS_C005465 --主键约束名

禁用约束
alter table stuInfo disable constraint ck_sex

启用约束
alter table stuInfo enable constraint ck_sex

删除指定行数据
delete from stuInfo where stuNo=2

修改表结构
修改表名
alter table stuInfo rename to studentInfo
修改表的列名
alter table studentInfo rename column sex to stuSex
修改列的数据类型
alter table studentInfo modify stuTel varchar2(18)
查看表结构
select * from user_tab_columns where table_name=‘STUDENTINFO’ --要大写
添加列
alter table studentInfo add stuPWD varchar2(10)
删除列
alter table studentInfo drop column stuPWD

classInfo添加数据
添加数据语法 insert into <表名>(列名1，列名2…) values (‘值1’,‘值2’)
insert into classInfo(cname,cid,cyear,clen) values (‘计算机21’,1009,2018,4)
insert into classInfo(cid,cname) values (1111,‘计算机212’)

插入多条数据 insert into <表名>(列名1，列名2…) select (列名1，列名2…) from(数据来源表名)
select * from dept
insert into classInfo(cid,cname) select deptno,dname from dept

插入多条数据
insert into classInfo
select 1007,‘技术4班’,2014,4 from dual union --union是连接符
select 1008,‘技术5班’,2014,4 from dual union
select 1010,‘技术6班’,2014,4 from dual

查询当前用户
select user from dual

查询系统当前时间
select sysdate from dual

select 1+2 from dual

查询序列
create sequence aab start with 1001 increment by 1
select aab.Nextval from dual