oracle(三)

/****************************表空间 start****************************/

--表空间的作用
/**
1.决定数据库实体的空间分配
2.设置数据库用户的空间份额
3.控制我们数据库部分数据的可用性
4.备份和恢复数据
**/
-- 如何创建表空间 对应的数据库的文件
/*create tablespace 表空间名称
datafile 路径名称
size 大小（默认值）
autoextend on next 大小 （递增的大小）
maxsize 大小（最大值）*/
/****创建一个表空间 jame_tablespace***/
create tablespace jame--数据库的名称
datafile 'E:\oracle\oradata\orcl\jame01.DBF' --路径名称 针对项目的数据库单独放一个路径
SIZE 20M;

/***创建一个用户并且指定表空间 Array ***/
create user u_array
identified by u_array
default tablespace jame;

/**将 connect resource 角色授权给u_array**/
grant connect,resource to u_array;
/*注意：在工作中，设计数据库的时候，表一定要跟用户放到一个表空间*/

/***查看表空间的名称及大小***/
/**
dba_tablespaces 表空间的信息视图
dba_data_files 表空间对应的文件信息
关系型数据库
**/
select t1.TABLESPACE_NAME,t2.BYTES
from dba_tablespaces t1, dba_data_files t2
where t1.TABLESPACE_NAME = t2.tablespace_name;

/***查看物理文件的名称及大小***/
select * from dba_data_files

/***查看表空间的使用情况***/
select * from dba_free_space;

/**调整表空间**/
alter tablespace jame
add datafile 'E:\oracle\oradata\orcl\jame02.DBF'
size 20m
autoextend on next 2m
maxsize 50m;
--修改数据文件的大小
alter database datafile 'E:\oracle\oradata\orcl\jame02.DBF'
resize 50m;

--删除数据文件
alter tablespace jame
drop datafile 'E:\oracle\oradata\orcl\jame02.DBF';
/****************************表空间 end****************************/
/****************************约束 start****************************/
--实现自动化部署自动化上线 自动化升级
/***
约束类型
1.主键约束(Primary)
2.唯一约束(unique)
3.检查约束(check)
4.非空约束(not null) --属于检查约束
5.外键约束(foreign key)

***/
create table t_student_con(
f_id int,
f_name varchar2(30),
f_sex char(2),
f_age int,
f_birth date default sysdate
);
--添加一个字段
alter table t_student_con
add f_email varchar2(30);

--添加一个主键约束 不能为null 会自动添加唯一约束
alter table t_student_con
add constraint pk_std_id primary key (f_id);

insert into t_student_con values(1,'Jaydy','M',23,to_date('1992-06-01','yyyy-mm-dd'),'jaydy@qq.com')

insert into t_student_con values(1,'Jaydy','M',23,to_date('1992-06-01','yyyy-mm-dd'),'jaydy@qq.com')
--添加一个唯一约束
alter table t_student_con 
add constraint uq_std_name unique(f_name);
insert into t_student_con values(2,'Jaydy','M',23,to_date('1992-06-01','yyyy-mm-dd'),'jaydy@qq.com')
--添加检查约束 可以自定义数据的完整性，保证字段的数据不存在约束条件之外的数据
alter table t_student_con 
add constraint ck_std_sex check(f_sex='M' or f_sex='F')

insert into t_student_con values(2,'Jaydy','S',23,to_date('1992-06-01','yyyy-mm-dd'),'jaydy@qq.com')

--添加非空约束
/*不是很常用
alter table t_student_con

add constraint nnull_std_email check( f_email is not null);
*/
--判断字段是否为null is null 判断是否不为null is not null
alter table t_student_con modify(f_email varchar2(30));
insert into t_student_con values(3,'思程','M',23,to_date('1992-06-01','yyyy-mm-dd'),null)


---外键约束
--考试信息表 100.00 95 95.5 95.55
create table t_student_exam(
e_id int,--主键
e_subject varchar2(30),--科目
e_score number(5,2),--分数
f_id int--学员信息的外键
);
--添加主键
alter table t_student_exam add constraint pk_exam_id primary key(e_id);
--添加外键
alter table t_student_exam add constraint fk_exam_student_id foreign key (f_id)
references t_student_con(f_id)
on delete cascade --在删除学生信息的时候，级联删除从表的成绩信息
;

insert into t_student_exam values(2,'JavaSe',100,1);
select * from t_student_exam
-- 
delete t_student_con where f_id=1

--删除约束
alter table t_student_exam drop constraint fk_exam_student_id;

/**在建表时同步加上**/
drop table t_student_con1;
create table t_student_con1(
f_id int primary key,
f_name varchar2(30),
f_sex char(2) check(f_sex='M' OR f_SEX='F'),
f_age int,
f_birth date default sysdate,
constraint ck_std_age check(f_age>=1 and f_age<=60)
);
DROP TABLE T_STUDENT_EXAM1;
create table t_student_exam1(
e_id int,--主键
e_subject varchar2(30),--科目
e_score number(5,2),--分数
f_id int REFERENCES T_STUDENT_CON(F_ID) ON DELETE CASCADE--学员信息的外键
);

/****************************约束 end****************************/
alter user scott account unlock;

/*简单查询 start*/
--1.查询姓名为SMITH的员工信息
--注意：在Oracle中内容区分大小写。 关键字、表名、字段名不区分大小写
select * from EMP WHERE ENAME='SMITH';
--2.查询20部门的所有员工信息
select * from emp where deptno=20
--3.查询所有JOB为CLERK的员工的员工号、员工姓名和部门号。
select empno,ename,deptno from emp where job='CLERK';
--4.查询奖金（COMM）高于工资（SAL）的员工信息。
select * from emp where comm>sal
--5.查询奖金高于工资的20%的员工信息。
select * from emp where comm>sal*0.2
--6.查询10号部门中JOB为MANAGER和 20部门中JOB为CLERK的员工的信息。
-- 组合条件
select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' AND DEPTNO=20)
--7.查询所有工种(job)不是MANAGER和CLERK且工资大于或等于2000的员工详细信息。
SELECT * FROM EMP WHERE JOB!='MANAGER' AND JOB!='CLERK' AND SAL>=2000
SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER','CLERK') AND SAL>=2000
--8.查询所有工种(job)是MANAGER和CLERK且工资大于或等于2000的员工的详细信息。
SELECT * FROM EMP WHERE JOB IN ('MANAGER','CLERK') AND SAL>=2000
--10.查询员工名字中包含字母"S"的员工。
SELECT * FROM EMP WHERE ENAME LIKE '%S%';
--11.查询员工名字中不包含字母"Ｓ"的员工。
select * from emp where ename not like '%S%';
--12.查询员工姓名的第二字母为"M"的员工信息。
--%代表匹配任何数量的任何字符
--_代表任意单个字符
select * from emp where ename like '_M%'
--13.接收用户输入的员工姓名并且根据姓名查询该员工信息。
select * from emp where ename ='&员工姓名' AND SAL='&工资';
--统计表中所有的job类型
select distinct job from emp
--14.查询员工的姓名和薪水，并且按照薪水从高到低排序 order by 字段名 [desc|asc]
select ename,sal from emp order by sal desc
--15.查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
select ename,hiredate from emp order by hiredate asc
--16.显示所有员工的姓名，工种，工资，和奖金按JOB降序排序，若JOB相同则按工资升序排序。
select ename,job,sal,comm from emp order by job desc,sal asc
/*使用的是scott用户下的emp 和dept表进行操作
1.查询姓名为SMITH的员工信息(注意大小写)
2.查询20部门的所有员工信息
3.查询所有JOB为CLERK的员工的员工号、员工姓名和部门号。
4.查询奖金（COMM）高于工资（SAL）的员工信息。
5.查询奖金高于工资的20%的员工信息。
6.查询10号部门中JOB为MANAGER和 20部门中JOB为CLERK的员工的信息。
7.查询所有工种(job)不是MANAGER和CLERK且工资大于或等于2000的员工详细信息。
8.查询所有工种(job)是MANAGER和CLERK且工资大于或等于2000的员工的详细信息。
9.查询有奖金的员工的不同工种(job)。
10.查询员工名字中包含字母"S"的员工。
11.查询员工名字中不包含字母"Ｓ"的员工。
12.查询员工姓名的第二字母为"M"的员工信息。
13.接收用户输入的员工姓名并且根据姓名查询该员工信息。
14.查询员工的姓名和薪水，并且按照薪水从高到低排序
15.查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
16.显示所有员工的姓名，工种，工资，和奖金按JOB降序排序，若JOB相同则按工资升序排序。
*/
/*简单查询 end*/