oracle数据库学习记录（持续更新中...）

--------------------------------------------day1-------------------------------------------------

1.为什么要使用数据库

2.什么是数据库

3.数据库的三层结构  https://www.cnblogs.com/wangjian920110/p/5454969.html
4.主流数据库
微软：SqlServer和access（小巧、免费、数据量不大）
Mysql（开源）轻量级数据库
Ibm：db2（海量数据的存储）
oracle：大型数据库、比较安全
sysbase：专注于linux下的开发，金融领域较多


思考：（1）项目中如何选择数据库？
           1.项目标的（预算）
   2.功能要求
           3.安全性和稳定性
           4.并发（用户数量）
   5.操作系统）（linux Unix Windows Macos）
oracle的服务：


5.表空间  http://blog.csdn.net/z69183787/article/details/23526423
SYSAUX SYSTEM TEMP USER UNDO分别对应oradata目录下的五个DBF文件
数据库实例：
6.sys system用户权限
conn 切换用户
disc 断开用户
passw user system 修改密码
show user 查看当前用户
exit 退出数据库
desc +表名：查看表结构

7.交互式命令
（1）&可以替代变量，变量执行时需要输入
select * from emp where deptno = '&deptno'; 查找表
（2）ed[it]:编辑指定sql文件的内容
 （3）spool可以将屏幕sqlplus上的内容输入到文件中
   

spool off 结束输入

--------------------------------------------day2-------------------------------------------------


---创建表空间
---问题K创建一二个名为hp的表空间，指定数据名为hp.dbf，大小为10M
create tablespace hp
datafile 'G:\oracle_project\hp\hp.dbf'
size 10M
autoextend on next 5M
maxsize unlimited;




drop tablespace hp   ---删除表空间




---创建用户 
--create user  用户名 identified by 密码 [default tablespace 表空间名] [temporary tablespace temp]（临时表空间）
create user hope identified by 123456; --用户+密码
create user hope1 identified by 123456  default tablespace users;--用户hope1+密码123456+表空间users
create user hope2 identified by 123456 default tablespace users temporary tablespace temp;


---修改用户密码
alter user hope identified by 654321;
----删除用户
drop user hope;
drop user hope cascade; ---删除相关数据


--赋予权限和角色
--系统权限允许用户执行某些数据库的操作
--对象权限：允许用户对某一特定对象执行特定操作
--角色是具有名称的一组权限的组合
--connect：临时用户
--resource：更为可靠和正式的用户
--dba：数据库管理员角色
--一般情况下，普通用户，有connect和resource权限就足够了



 
--赋予权限
grant connect to hope2  --connect角色
grant resource to hope2;
grant connect,resource to hope2;--同时赋予两个角色


grant select,update on scott.emp to hope2;----给hope2赋予查询和修改scott的权限
--撤销权限和角色
revoke resource from hope2;
revoke connect,resource from hope2;---同时撤销两个角色


---修改表
update scott.emp set sal=5000 where 


---oracle的数据类型
--cahr：可以存储定长的字符串 char(200) 存储10个剩下的空格补齐  解析快
--varchar:可以存储变长的字符串
--varchar2：可以存储变长的字符串 carchar2(200)存储10个剩下的闲置
--如何选择char 还是varchar2：已知字符串长度用char，不确定字符串长度用varchar2
--nchar和nvarchar2可以存储unicode的字符集
---number    ==number(38,0)  默认38位整数，
--number(p)  ==number(15,0)      p-->整数位数，
--number(p,s)   ==number(15,2)  p--->总位数，s---->小数点后位数 存储整型和浮点型
--PS：不建议使用varchar，Integer，float，double




--日期数据类型：
--sysdate：日期函数：显示当前日期
select sysdate from dual;--显示系统当前日期
--systimestamp:比date更精确的一个时间
select systimestamp from dual;
---to_date(字符串，格式)
select to_date('2018-01-05','yyyy-MM-dd') from dual;--将一个字符串转化为日期格式




--Date：日期类型，存储日期和时间
--TimeStamp：比Date更精确的一种日期类型
--默认的日期格式：
  --中文的oracle： 日-月-年  例1998年7月1日--->'01-7月-1998'
  --英文的oracle：day-mon-year 例1998年7月1日--->'01-JUL-1998'


--LOB数据类型
  --BLOB：存储二进制对象，图像、音频、视频
  --CLOB：存储字符格式的大型数据




--创建表：解决表结构的问题：表有几个字段，每个字段的长度是多少，每个字段是什么数据类型
create table student(stunum number(4),stuname varchar2(10),age number(2),addr varchar2(50));


--问题：往表中插入数据可能会出现的问题，比如：重复插入数据，内容不正确，格式不正确
--如何保证数据库表中数据的完整性和一致性？
--常见的约束： 主键（primary key） 外键（foreign key） 唯一（unique） 非空（not null） 默认约束（default）
               --用户自定义（check）
--什么是主键约束？唯一非空
--给表定义一个主键，主键是用来保证表记录的唯一非空
        --建表时添加主键
create table student(stunum number(4) primary key,stuname varchar2(10),age number(2),addr varchar2(50));
--查找
insert into student values(1001,'tong',20,'nanyang');


--再次插入相同数据或者主键为null，违反主键约束，不允许插入
--创建课程表
create table course(cno number(4) primary key,cname varchar2(20),cscore number(4));
--创建一张成绩表    主键：学号+课程号  称为联合主键
--一张表只能有一个主键
create table score(stunum number(4),cno number(4),score number(5,2),constraint pk_score primary key (stunum,cno));
create table course(cno number(4) constraint pk_course primary key ,cname varchar2(20),cscore number(2));


作业：
1.思考：删除表空间时如何删除关联的数据文件
 drop tablespace hp including contents and datafiles;

--------------------------------------------day3-------------------------------------------------

问题：
1.主键、外键、唯一、非空、默认、用户自定义约束是什么，什么作用，怎么用
2.建表时如何创建约束？
3.如果建表时未添加约束，建好表后，如何添加？
4.Oracle中如何修改表结构
5.序列是什么，什么用，怎么用，注意哪些

1.主键：保证字段的唯一非空，一张表只能由一个主键，一个主键可以由多个字段组成，称为联合主键

2.建表时添加主键约束：
create table test(id number(4) primary key,name varchar2(20));

create table test1(id number(4) constraint pk_test1 primary key,name varchar2(20));

create table test2(id number(4) ,name varchar2(20), constraint pk_test1 primary key(id));

外键约束：在另一张表中是主键
student(sno,sname,age,addr,idcard); --主键sno
course(cno,cname,cscore); --主键cno
score(sno,cno,score);--主键(sno,cno)  外键 sno    cno
--外键作用？如果Student表中没有1001学生信息，那么score表中也不应有该学生成绩。
--往成绩表插入数据时，如果学生表中么有该学号，或者课程表中没有该课程，不允许插入
create table student(sno number(4) primary key,
sname varchar2(20),
age number(3),
addr varchar2(50),
idcard number(18));

create table course(cno number(4)primary key,
cname varchar2(20),
cscore number(2));

--删除表及其数据的方法
drop table score cascade constraints;

外键创建方法：
第一种
create table score(
stuno number(4) references student(sno), --创建第一各外键约束
couno number(4) references course(cno),  --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno));  --创建一个主键约束
第二种：
create table score(
stuno number(4)constraint fk_stuno references student(sno),
couno number(4)constraint fk_couno references course(cno),
score number(5,2),
constraint pk_score primary key(stuno,couno));
第三种：
create table score(
stuno number(4),
couno number(4), 
score number(5,2),
constraint fk_stuno foreign key(stuno) references student(sno),
constraint fk_couno foreign key(couno) references course(cno),
constraint pk_score primary key(stuno,couno));

--总结1：在一张表中可以有多个外键但是只能有一个主键
--总结2：作为外键的字段名并不要求和被参考表一致
--总结3：定义外键约束后，往表中插入数据会参考父表中的值，只有父表中存在改值才会成功

--向student表中插入3条记录
insert into student values(1001,'zhangsan',19,'henan',55555555);
--查看student表记录
 select * from student;
--向course表中插入数据
insert into course values(999,'java基础',4);
--查看course表记录
 select * from course;

--思考：执行insert into score values(1003,9997,89);能成功吗？    答案是不能
--总结4：定义外键约束后，删除父表中内容前需要先将相关子表记录删除。
delete from student where sno=1001; --删除不掉，违反完整约束条件
--正确做法
delete from score where stuno=1001;  --先删子记录
delete from student where sno=1001;  --再删父记录

唯一约束：
第一种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) unique);
第二种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18)constraint uk_idcard unique);
第三种
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18),
constraint uk_idcard unique(idcard));

非空约束和默认约束
create table student(sno number(4) primary key,--主键约束
sname varchar2(20) not null, --非空约束
age number(3) default 18,   --默认约束，默认18
addr varchar2(50),
idcard number(18) unique); --唯一约束

--用户自定义约束check(约束条件)
create table student(sno number(4) primary key,--主键约束
sname varchar2(20) not null, --非空约束
age number(3) default 18 check(age between 10 and 45),   --默认约束，默认18,自定义约束，年龄在10--45
addr varchar2(50),
idcard number(18) unique); --唯一约束
--定义性别为男或女
create table student(sno number(4) primary key,
sname varchar2(20) not null,
age number(3) default 18 check(age between 10 and 45), 
gender char(10) check(gender in('male','female')),
addr varchar2(50),
idcard number(18) unique);
--总结1：定义了完整性约束后，输入的数据要符合约束的要求，否则不能插入

三、如何修改表结构？
--给student表添加字段regdate类型是date
alter table student add regdate date;
alter table student add province varchar2(350);
--删除刚添加的字段
alter table student drop column regdate;
--删除多列
alter table student drop (province,regdate);
--修改字段类型和长度
alter table student modify idcard char(20);
--修改表名student -->stu
alter table student rename to stu;
--修改字段名
alter table stu rename column gender to sex;

四、建表时未添加约束，建好表后如何添加约束
--添加主键约束
alter table student add constraint pk_sno primary key(sno);
--添加唯一约束
alter table student add constraint uk_idcard unique(idcard);
--添加非空约束
alter table student modify sname not null;
--添加默认约束
alter table student modify age default 18;
--添加检查约束
alter table student add constraint ck_age check(age between 18 and 50);
--给表添加外键约束
create table score(
sno number(4),
score number(5,2));
alter table score add constraint fk_sno foreign key(sno) references student(sno);

--禁止某个约束
alter table score disable constraint fk_sno;
--启用某个约束
alter table score enable constraint fk_sno;
--删除约束
alter table score drop constraint fk_sno;
--删除student主键约束
alter table student drop primary key;
alter table score drop constraint pk_sno;

五、序列是什么，怎么用，做什么用，注意哪些内容        主键自增？？？？？？？？
序列：有序的数字组成的一个集合  1 2 3 4 5 6 7
序列，序列是一个独立的数据库对象，作用是生成主键
--创建序列：
create sequence seq01   --序列名
start with 3           --序列起始值
increment by 1           --序列的步长
maxvalue 9999          --最大值
minvalue 0           --最小值
nocycle               --nocycle（序列不循环） cycle（序列循环使用）
cache 20;          --cache(为了加快序列的生成速度，每次生成20个值放在缓存中)
                     --nocache(不向缓存中存放序列值，使用一次生成一次)

--序列的使用:可以供多张表使用
--currval：返回序列的当前值不会引起序列的自增
--nextval：返回序列的下一个值会引起序列的自增
--查看序列的下一个值，会引起序列自增
select seq01.nextval from dual;         --第一次使用序列，必须使用序列名.nextval
--查看序列的当前值
select seq01.currval from dual; 

--序列相当于一个递增数列，每次插入一组数据，序列自增1，但是多张表使用的时候可能会造成
--主键的不连续
insert into student values(seq01.nextval,'zhang',18,'male','河南南阳',111111111);
--删除序列
drop sequence seq01;（序列名）

--------------------------------------------day4-------------------------------------------------


--将表emp的结构复制给emp1（不复制表内容，只复制表结构，也不复制表的约束）
create table emp1 as select * from emp where 1=2;
--复制emp表（内容结构都复制,不复制约束）
create table emp2 as select * from emp;
--扩展：创建一张表，只复制表的几个字段ename、sal、job
create table emp3(ename,sal,job) as select ename,sal,job from emp;
--扩展：创建表emp4，复制emp表中部门10中的员工信息
create table emp4 as select * from emp where deptno=10;


--1.sql的简介
--SQL:Structured Query Language 结构化查询语言的简称
--Oracle:C/S 客户端发起请求，通过网络传递给服务器，服务器对请求进行相应，将结果传递给客户端并显示
--2.sql组成
--DDL(数据定义语言Data Definition Language)：create alter drop   针对数据库对象的操作
--create user    create tablespace     create table     create sequence
--create view     create index      create package    create trigger    create procedure
--注意：DDL语言使用的时候不需要提交，系统会自动提交


--DML语言(数据操纵语言data manipulation language)：select  update   insert  delete针对表中的数据的操作


--DCL语言(数据控制语言data control Language)：控制存取权限 grant  revoke


--TCL(事务控制语言 Transaction Control Language)：commit   rollback   savepoint
--注意：事务控制语言主要是针对DML操作


--3.DML语言
--(1)insert
--向emp1中插入记录
insert into emp1 values(1001,'shelly','manager',7839,date'2018-1-09',3000,null,10);
--给五个字段插入
insert into emp1(empno,ename,sal,job,deptno)values(1002,'ellen',4000,'salseman',20);
--问题1：从emp表中复制20部门员工信息放入到emp1表中
insert into emp1 select * from emp where deptno=20;
--问题2：往emp1表中插入一条姓名为ellen记录，其余内容跟emp1表中的其他内容不变，empno在原来的基础上+1000
insert into emp1(empno,ename,job,sal,deptno) select empno+1000,ename,job,sal,deptno from emp1 where ename='ellen'; 
insert into emp1(empno,ename,job,sal,deptno) select empno+1000,ename,job,5000,deptno from emp1 where ename='ellen';
insert into emp1(empno,ename,job,sal,deptno) select 3001,'lily','anal',3000,30 from dual;
--等价于insert into emp1(empno,ename,job,sal,deptno) value(3001,'lily','anal',3000,30);
--插入多条数据
insert into emp1(empno,ename,sal,job)
select 9999,'lilei',3500,'mana' from dual union
select 9998,'lucy',4500,'mana' from dual union
select 9997,'hanmei',5500,'mana' from dual;
--union:Oracle的集合运算，求并集,使用时会去重
--union all:求并集运算，不去重
insert into emp1(empno,ename,sal,job)
select 9999,'lilei',3500,'mana' from dual unionall
select 9998,'lucy',4500,'mana' from dual unionall
select 9997,'hanmei',5500,'mana' from dual;




--(2)delete
--删除表中所有数据(不删表结构)
delete from emp1;
--删除表中的某几行记录
--问题:删除emp1表中部门编号为10的信息
delete from emp1 where deptno=10;


--（3）update
--问题：更新emp1表中员工的工资，如果工资小于1500，则给此员工涨1000
update emp1 set sal=sal+1000 where sal<1500;


--(4)select
---（1）查询表中所有字段
select * from emp1;
---(2)查询emp1表中员工的姓名，工资，奖金，姓名编号
select ename,sal,comm,deptno from emp1;
---(3)查询emp1表中部门30中员工的
select ename,sal,comm,deptno from emp1 where deptno=30;


--注意事项：
---（1）字段的数据类型，长度、精度都要符合表结构的要求
---（2）插入数据的个数应该与字段的个数一致
---（3）插入数据时要注意日期格式的处理
----日期格式的处理
----date声明
----（1）insert into emp1 values(1001,'shelly','manager',7839,date'2018-1-09',3000,null,10);
----使用日期默认格式
----（2）insert into emp1 values(1001,'shelly','manager',7839,'09-1月-2018',3000,null,10);
----使用to_date将字符串转换为日期
----（3）insert into emp1 values(1001,'shelly','manager',7839,to_date('2018-1-09','yyyy-mm-dd'),3000,null,10);


--4.DDL语言


--5.DCL语言


--6.TCL语言
savepoint 
rollback
commit 
--创建一个保存点：
savepoint mark1;  --保存断点mark1类似于存档
update emp1 set sal=sal+500 where comm is null;   --执行操作
rollback to mark1;         --在未提交前可以回滚到断点mark1处
commit;                 --提交事务，提交后不可回滚
--7.Oracle的运算符
--算术运算符：+ - * /


--连接运算符  ||
--问题：查询emp1中的员工姓名，在员工姓名前加上dear
select 'dear'||ename from emp1;
--问题：查询每个员工的工资显示为：Ellen的工资为:800
select ename||'的工资是:'||sal from emp1;
--比较运算符  >  >=  <  <=  <> (!=) =  is null  between..and..    in    like
--问题：查询工资大于3000的员工的信息
select * from emp1 where sal>3000;
--问题：查询部门编号不为20的员工信息    不等于<>和!=
select * from emp where deptno!=20; 
select * from emp where deptno<>20; 
--is null用来做空值的比较
--问题：查询部门中奖金为空值的员工信息
select * from emp where comm is null;
--问题：查询部门中奖金不为空值的员工信息
select * from emp where comm is not null;
---------注意：奖金为0于奖金为空不同
--between....and....查询工资在2000-5000之间的员工信息（包含边界）
select * from emp where sal between 2000 and 5000;
select * from emp where sal >= 2000 and sal<=5000;
--in：后边加一个集合
--问题：查询部门10或部门20中的员工的信息
select * from emp where deptno in(10,20);
select * from emp where deptno=10 or deptno=20;
--like：模糊查询
--通配符：
--- _：表示匹配一个字符，必须有一个字符
--- %: 表示匹配任意多个字符 匹配0个或多个字符
---[]：表示匹配方括号[]中的任意一个字符


--问题：查询姓名中带有llen的员工信息
select * from emp where ename like '%LLEN%';
--问题：查询姓名以A开头，以N结束的员工信息
select * from emp where ename like 'A%N';
--问题：查询员工姓名长度为5的员工   (五个下划线)
select * from emp where ename like '_____';


--逻辑运算符    优先级 not> and >or
--问题：查询emp表中部门10中的MANAGER或者部门20中的CLERK的员工信息
select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';
--问题：查询emp表中部门10或者20中的MANAGER中的员工信息
select * from emp where (deptno=10 or deptno=20) and job='MANAGER';
select * from emp where deptno in(10,20) and job='MANAGER';


--运算符的优先级
--算术运算符>连接运算符>比较运算符>逻辑运算符

-------------------------------------------------day5-----------------------------------------------------
--1.列别名和表别名
--针对字段的别名称为列别名    语法：字段名(as)字段别名
select 'dear'||ename as 姓名 from emp;   --加一个列别名 
--as可以省略
select 'dear '||ename 姓名 from emp;
--别名可以加双引号，双引号可以省略
select 'dear '||ename "姓名" from emp;
--有一种情况双引号不可省略：别名中有空格
select 'dear '||ename "姓 名" from emp;


--表别名
select ename,sal,job from emp  where deptno=10;
--给emp表一个别名 e  语法：表名 表别名    不能加as
select e.ename,e.sal,e.job from emp e  where e.deptno=10;
--规则：一旦给了表别名之后，所有字段的使用都要使用：表别名.字段名




--2.select ...from...where...group by...having...order by    
--注意：
--（1）select....from....必不可少,当没有表名时用dual凑
select sysdate from dual;
--（2）该语句的执行顺序：where...group by...having...order by 
--（3）这些关键字不能调换顺序
 
--3.排序 order by 正序、逆序、单列排、多列排
--问题：查询emp表中的姓名、工资，按照工资降序排列
select ename,sal from emp order by sal desc;
--问题：查询emp表中的姓名、工资，按照工资升序排列
select ename,sal from emp order by sal asc;
select ename,sal from emp order by sal;  --默认升序
--总结：语法：select.. from .. order by 字段名 asc/desc;
--asc升序   desc降序   默认升序
--问题：查询部门20的员工的姓名和工资，并按照工资进行降序
select ename,sal from emp where deptno=20 order by sal desc;


--多字段排序
--问题：查询emp表中员工的姓名、部门编号、工资，先按照部门进行升序排列，同部门内部按照工资降序排列
select ename,deptno,sal from emp order by deptno asc,sal desc;
--总结：多列排序语法order by 字段名1 asc\desc,字段名2 asc\desc, ..