Oracle数据库学习笔记3

select * from stuent;
alter table student add stu_card char(18)

--主键约束：非空，唯一 primary key
alter table student add constraint
pk_stuid primary key(stu_id)

--检查约束 check ck_xxx
alter table student add constraint
ck_stusex check(stu_sex='男' or stu_sex='女')

--唯一约束：不重复(只能允许一个为空) unique
alter table student add constraint
up_stucard unique(stu_card)

--默认约束 default
alter table student modify
stu_birth default sysdate

--非空约束 not all
alter table student modify(stu_name not null)

-主外键约束

alter table stu_score add constanint fk_stuid
foreign key(stu_id) references student(stu_id);

create table stu_score(
stu_id number,
stu_score number(5,1)
);

--删除表 （先删除外键表，再删除主键表）
drop table student;

1.每张表必须有主键，且为原子列（每个字段不可再分）
2.非主键列完全依赖与主键列
3.非主键列不能依赖与非主键列

DML语句：
--增加数据 insert into table_name
insert into student
(stu_id,stu_name,stu_birth,stu_card)
values
(1,'张三丰','1-10月-1990','123');

insert into student
values(2,'梅超风',default,'123456','男')；

select * from student;
commit;

--修改数据 update table_name set 字段名=值；
update student set stu_sex = '女' where stu_id = 2;

--删除数据 delete 表明
delete from student where stu_id = 1;

--查询数据 select
select
from
group by
having
order by
select * from emp
select * from dept

--复制表
create table emp_temp as select * from emp;
select * from emp_temp

--查询员工的编号，姓名，工作岗位
select empno,ename,job from emp_temp;
--查询20部门的员工信息
select * from emp_temp where deptno = 20;

select empno as 员工编号，ename 员工姓名
from emp_temp;

--查询员工工资大于3000的员工信息
select * from emp_temp where sal > 3000;
--查询不是30部门的员工信息
select * from emp_temp where deptno <> 30;

--查询20部门的员工信息，以下列格式显示
XXX的薪水是XXX
selsect ename || ‘的薪水是;’|| sal
from emp_temp;

--查询所有员工的月收入
select empno,ename,sal,comm,
sal+nvl(comm,0) 月收入
from emp_temp;

--模糊查询
select * from emp_temp
where ename like '%A%'; %是可出现可不出现的，可出现多个

--查询第二个字符是A的，_是占位符，一定出现的
select * from emp_temp
where ename like '_A%';

--查询员工的薪水在3000-5000之间的[]
select * from emp_temp
where sal between 3000 and 5000;

不在3000和5000的
select * from emp_temp
where sal not between 3000 and 5000;

--in
selsect * from emp_temp
where deptno not in (20,30);

--查询那些员工没有奖金
select * from emp_temp
where comm is null;

select * from emp_temp
where comm is not null