oracle

 --创建表
-- select * from emp;
create table test_user(
Id number(10) primary key,
name varchar2(10) unique,
password varchar2(10)
);
create sequence seq_id start with 1 increment by 2;
insert into test_user values(seq_id.nextval,'admin1','admin12'),(seq_id.nextval,'admin1','admin12')


create table emp1(
eno varchar2(3),
ename varchar2(6),
sex varchar2(2),
age number(2),
deptno number
);
alter table emp1 add constraint constra_eno primary key(eno); 


alter table emp1 modify  ename not null;


alter table emp1 add constraint CK_age check(age<60 and age>18);


alter table emp1 add constraint uni_dep unique(deptno);


create sequence seq_emp1_deptno start with 20001 increment by 2;


alter table emp1 modify eno number(10);
create sequence  seq_emp1_eno start with 20001  increment by 2;


alter table emp1 modify sex varchar(10);


insert into emp1 values(seq_emp1_eno.nextval,'Tom','男',21,1);


alter table emp1 add constraint constraint_sex check (sex in('男','女','未知'));


insert into emp1 values(seq_emp1_eno.nextval,'Jerry','男',21,2);




create table test_user(
id number,
user_name varchar(50),
pass_word varchar2(50)
);




-- 根据现有的表创建新表，复制表
create table test_user1 as select * from test_user;


-- 复制表结构
create table test_user2 as select * from test_user where 1=2;


--创建视图，视图是一张需表
create view emp_view as select * from emp;
-- 视图是一张虚表 可以insert,update,delete 本质上:修改的是基表
create view emp_dept_view as select emp.empno,emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;




-- 创建视图 只授予只读权限 with read only 
create view emp3 as select * from emp with read only;


--添加主键 alter table 表名 add 约束名
alter table test_user add constraint pk_constran primary key(id);
--添加唯一约束
alter table test_user add constraint uni_cons unique(user_name);
--修改表，添加列 
alter table test_user add sex varchar2(5);
--添加检查性约束
alter table test_user add constraint check_sex4 check(sex in ('男','女','未知'));


insert into test_user values(10001,'admin','123456','男');
insert into test_user values(10002,'admin2','123456','女');
insert into test_user(id,pass_word,sex) values(10003,'123456','未知');


insert into test_user(id,pass_word,sex) values(10004,'123456','未知');


-- 创建用户: 
create user test_tbl identified by 123456;


-- unique 和primary key 的区别:
都是唯一，不能重复
unique 可以为空，并且可以为多个空


序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数；序列能生成最大38位的整数；序列名不能重复


--mysql 自增 ，auto_increment 
--sql server 自增， IDENTITY (1, 1)  useGeneratedKeys 获取自增主键
--oracle sequence序列递增


--dual 虚表 新创建的序列，必须先调用过nextval(对序列进行初始化) 才能调用currval
select seq_user.currval from dual; --序列的当前值
select seq_user.nextval from dual; --序列的下一个值


--引用序列 序列名.nextval
insert into test_user values(seq_user.nextval,'aa09','123456','男');


--rowid 伪列
select rowid,test_user.* from test_user;
--rownum 伪列 不能加 表名.rownum
select rownum,test_user.* from test_user where rownum<6;
select rownum,test_user.* from test_user where rownum>5;
select rownum,test_user.* from test_user where rownum=5;
select rownum,test_user.* from test_user where rownum!=5;
select rownum,test_user.* from test_user where rownum=1;
-- 查询>5 <11


select * from (select rownum rw,test_user.* from test_user where rownum<11) where rw>5 




select rownum,e.* from (select test_user.* from test_user order by user_name) e;




select rownum ,test_user.* from test_user order by sex






rownum


注意：只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９条记录。
不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立。


对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。


(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。


（2）rownum对于大于某值的查询条件
   如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。


查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。


（3）rownum对于小于某值的查询条件
rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。


(4)查询rownum在某区间的数据，必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。


(5)rownum和排序  
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
rownum并不是按照排序列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。




视图简介:
视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。 通过创建视图可以提取数据的逻辑上的集合或组合。


视图的优点：
1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
2.用户通过简单的查询可以从复杂查询中得到结果。
3.维护数据的独立性，试图可从多个表检索数据。
4.对于相同的数据可产生不同的视图。


视图的分类：
视图分为简单视图和复杂视图。


两者区别如下：
1.简单视图只从单表里获取数据，复杂视图从多表获取数据；
2.简单视图不包含函数和数据组，复杂视图包含；
3.简单视图可以实现DML操作，复杂视图不可以。


视图的定义原则：
1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；
2.在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句；
3.如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn;
4.OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。


视图上的DML 操作：
DML操作应遵循的原则：
1.简单视图可以执行DML操作；
2.在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行；
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据： 




--创建表


create table test_user(
id number,
user_name varchar(50),
pass_word varchar2(50)
);


--添加主键 alter table 表名 add 约束名
alter table test_user add constraint pk_constran primary key(id);
--添加唯一约束
alter table test_user add constraint uni_cons unique(user_name);
--修改表，添加列 
alter table test_user add sex varchar2(5);
--添加检查性约束
alter table test_user add constraint check_sex4 check(sex in ('男','女','未知'));


insert into test_user values(10001,'admin','123456','男');
insert into test_user values(10002,'admin2','123456','女');
insert into test_user(id,pass_word,sex) values(10003,'123456','未知');


insert into test_user(id,pass_word,sex) values(10004,'123456','未知');


-- unique 和primary key 的区别:
都是唯一，不能重复
unique 可以为空，并且可以为多个空


序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数；序列能生成最大38位的整数；序列名不能重复


--mysql 自增 ，auto_increment 
--sql server 自增， IDENTITY (1, 1)  useGeneratedKeys 获取自增主键
--oracle sequence序列递增


--dual 虚表 新创建的序列，必须先调用过nextval(对序列进行初始化) 才能调用currval
select seq_user.currval from dual; --序列的当前值
select seq_user.nextval from dual; --序列的下一个值


--引用序列 序列名.nextval
insert into test_user values(seq_user.nextval,'aa09','123456','男');


--rowid 伪列
select rowid,test_user.* from test_user;
--rownum 伪列 不能加 表名.rownum
select rownum,test_user.* from test_user where rownum<6;
select rownum,test_user.* from test_user where rownum>5;
select rownum,test_user.* from test_user where rownum=5;
select rownum,test_user.* from test_user where rownum!=5;
select rownum,test_user.* from test_user where rownum=1;
-- 查询>5 <11


select * from (select rownum rw,test_user.* from test_user where rownum<11) where rw>5 




select rownum,e.* from (select test_user.* from test_user order by user_name) e;




select rownum ,test_user.* from test_user order by sex






rownum


注意：只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９条记录。
不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件不成立。


对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。


(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。


（2）rownum对于大于某值的查询条件
   如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。


查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。


（3）rownum对于小于某值的查询条件
rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。


(4)查询rownum在某区间的数据，必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。


(5)rownum和排序  
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
rownum并不是按照排序列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。


1. 进入sqlplus
 sqlplus sys/admin as sysdba;
 grant read,write on directory data_pump_dir to scott;


  //data_dump_dir目录对象对应操作系统上D:\app\Administrator\admin\orcl\dpdump。
导入：
impdp scott/tiger directory=data_pump_dir dumpfile=bbs_bak.dmp schemas=scott  


2.退出sqlplus，在命令行下  
导出
expdp scott/tiger directory=data_pump_dir schemas=scott dumpfile=bbs_bak.dmp logfile=bbs_baklog.log


3.在D:\app\Administrator\admin\orcl\dpdump目录下找到导出的dmp文件






4.导入操作
将备份文件拷贝到data_pump_dir对应的操作系统目录下。
1）首先进入Sqlplus，给scott授权
sqlplus sys/admin as sysdba;
 grant read,write on directory data_pump_dir to scott;
2）然后退出sqlplus，在命令行模式下执行导入命令，
impdp scott/tiger directory=data_pump_dir dumpfile=bbs_bak.dmp schemas=scott




-- 根据现有的表创建新表，复制表
create table test_user1 as select * from test_user;


-- 复制表结构
create table test_user2 as select * from test_user where 1=2;


--创建视图，视图是一张需表
create view emp_view as select * from emp;
-- 视图是一张虚表 可以insert,update,delete 本质上:修改的是基表
create view emp_dept_view as select emp.empno,emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;




-- 创建视图 只授予只读权限 with read only 
create view emp3 as select * from emp with read only;






--添加主键 alter table 表名 add 约束名
alter table test_user add constraint pk_constran primary key(id);
--添加唯一约束
alter table test_user add constraint uni_cons unique(user_name);
--修改表，添加列 
alter table test_user add sex varchar2(5);
--添加检查性约束
alter table test_user add constraint check_sex4 check(sex in ('男','女','未知'));


insert into test_user values(10001,'admin','123456','男');
insert into test_user values(10002,'admin2','123456','女');
insert into test_user(id,pass_word,sex) values(10003,'123456','未知');


insert into test_user(id,pass_word,sex) values(10004,'123456','未知');


-- unique 和primary key 的区别:
都是唯一，不能重复
unique 可以为空，并且可以为多个空


序列（SEQUENCE）是一个命名的顺序编号生成器，它能以串行的方式生成一系列顺序整数；序列能生成最大38位的整数；序列名不能重复


第6章 视图
1.什么是视图。它有什么作用。
视图是一个 虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上是一张或者多张表上的预定义查询，这些表称为基表。
2.创建一个视图，此视图要包括以下信息：员工编号，员工姓名。


3.创建一个视图，此视图要包括以下信息：员工编号，员工姓名，部门编号，部门名称。




4.创建一个视图，此视图要包括以下信息：员工编号，员工姓名，经理编号，经理姓名。


5.创建一个视图，此视图要包括以下信息：部门编号，员工个数。


6.创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数。
c
7.创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数，部门内所有员工薪水的合计，部门内员工最高薪水，部门内员工最低薪水，部门内平均薪水。




第7章
 用户角色权限
1．创建新用户RXXT，密码RXXT，


2．创建用户“TEST1_自己姓名拼音”和“TEST2_自己姓名拼音”两个用户。
使用第1个用户创建表CUSTOMER，语句如下：


插入如下数据：


然后使用第2个用户登录系统，并做如下操作：
(1) 使用SQL查询一下CUSTOMER表：SELECT * FROM TEST1_自己姓名拼音.CUSTOMER; //表名前需要带上第1个用户的用户名。
(2) 使用SQL再插入一条数据：INSERT INTO TEST1_自己姓名拼音.CUSTOMER(ID, NAME) VALUES(3, '付海');
(3) 使用SQL修改数据“王小明”，修改成“王晓明”。
(4) 使用SQL删除所有数据。
(5) 使用SQL把此表删除掉。

select * from emp;
select * from dept;
select * from emp_grade;
--连接
select * from emp,dept,emp_grade where emp.deptno=dept.deptno(+) and emp.grade_id=emp_grade.id(+);
select * from emp left join dept on emp.deptno=dept.deptno
              right join emp_grade on emp.grade_id=emp_grade.id
               
--笛卡尔成绩
select * from emp,dept order by empno;
--等值连接
select * from emp,dept where emp.deptno=dept.deptno;


--左连接 右连接(显示主表的所有信息) (+)在哪侧的表是匹配表
--（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
--显示所有员工的部门信息
select * from emp,dept where emp.deptno=dept.deptno(+);
--显示所有部门的员工信息
select * from dept,emp where emp.deptno(+)=dept.deptno;


    --左外连接(左边的表不加限制)
    --右外连接(右边的表不加限制)
    --全外连接(左右两表都不加限制)


    -- 左外连接（ tb1 LEFT OUTER JOIN/ LEFT JOIN  tal2  on tbl1.id=tabl2.id） 
    --右外连接（ tb1 RIGHT OUTER JOIN/RIGHT JOIN  tal2 on tbl1.id=tabl2.id）
    --全外连接（tb1 FULL OUTER JOIN/FULL JOIN tal2 on tbl1.id=tabl2.id）
   
 --左外连接
    select * from emp left join dept on emp.deptno=dept.deptno;
 --右外连接
    select * from emp right join dept on emp.deptno=dept.deptno;
 -- 全连接
   select * from emp full join dept on emp.deptno=dept.deptno;
   
--子查询 （单行子查询 多行子查询） in()


-- 只要出现聚合函数，除聚合函数外还有其他列的时候，其他列必须放在group by
 --聚合函数max() min() sum() avg()对列 count()行记录数统计
 -- group by 先进行分组，再进行统计
 
 -- 单行子查询  > < =(单个值)
 
 --查询薪资最低的员工信息 
  select * from emp where sal=(select min(sal) from emp)
  
 --查询工资高于部门20中所有员工的雇员信息。 
   select * from emp where sal>(select max(sal) from emp where deptno=20)
   
   select * from emp where sal>all(select sal from emp where deptno=20)
   
 --in  all 多行子查询
   select * from emp where sal in(select sal from emp where deptno=20)
   
-- 多列子查询
   --查询各部门中工资最低的雇员信息。
   select * from emp,(select deptno,min(sal) min_sal from emp group by deptno)e 
   where emp.deptno=e.deptno and emp.sal=e.min_sal;
   select * from emp where (deptno,sal) in(select deptno,min(sal) min_sal from emp group by deptno)
   
-- 表自连接 
select * from stu_grade;
--即参加计算机又参加英语考试的学生信息
select * from stu_grade A,stu_grade B where A.Id=B.id and A.COURSE_NAME='计算机' and B.Course_Name='英语'

select * from emp;
--concat(param1,param2) 拼接两个字符串 ||
select concat('0371-','8678565') as telephone from dual;
select concat(ename,empno) from emp;
select ename||empno from emp;
--initcap 首字母大写
select initcap(ename),ename from emp;
-- upper(全大写) lower(全小写) 
select upper(ename),lower(ename),ename from emp;
select instr('410106199012132018','19901213',1,1) instring from dual;
--instr(param1,param2,param3,param4) 查找字符串 1：被查找的字符串 2,要查找的字符 3，起始位置 4第几次出现
select empno,ename,hiredate, instr(to_char(hiredate,'yyyy-mm-dd'),'1981',1,1) from emp
--lpad(param1,param2,param3)字符串左侧粘贴 1，原字符串 2,补充后达到的位数 3,补充字符
--rpad(param1,param2,param3) 字符串右侧粘贴
select lpad(rpad(empno,8,'#'),10,'*') from emp;


--ltrim(param1,param2)从字符串param1左侧删除param2 rtrim(param1,param2) 字符串param1右侧删除param2
select ltrim('##100#024##','#') from dual;
select ltrim('##100024##','#'),rtrim('**200001****','*') from dual;
--param2省略时，去除空格
select ltrim('   admin   '),rtrim('   admin   ') from dual;
select ltrim('   admin   ',' '),rtrim('   admin   ',' ') from dual;
--去除左右两侧的空格
select trim('   admin   ') from dual;


--trim(leading param1 from param2) leading (头) param2从头部去除param1
--trim(trailing param2 from param2) trailing (尾) param2 从尾部去除param1
--trim(both param2 from param2) both (头,尾)   param2从头,尾(两侧)去除param1
select trim(leading '#' from '###admin##') from dual;
select trim(trailing '#' from '###admin##') from dual;
select trim(both '#' from '###adm#in##') from dual;





--substr(param1,number1,number2)截取字符串 param1,目标字符串 number1起始位，number2长度
select substr('13012345678',3,8) from  dual;


--replace(param1,param2,param3)替换字符串 param1目标字符串,param2被替换的字符串，param3替换字符串
select replace('云和培训java培训','yunhe','教育') from dual;
select ename,replace(ename,'A','a') from emp 


select mod(7,3) from dual;


select * from emp;
-- mod(number1，number2) number1对number2 取余数
select comm,mod(comm,1000) from emp;
-- round(number1,number2) nubmer2为正数，小数点向后四舍五入number2，number2为负数，小数点向前四舍五入number2
select sal,round(sal,-2) from emp;
-- trunc(number1,number2) 截取，nubmer2为正数，小数点向后截取number2，number2为负数，小数点向前截取number2
select sal,trunc(sal,-2) from emp;


--sign(number) 判断number符号,大于0返回1，等于0返回0，小于0返回-1
select sign(10) from dual;
select sign(-10) from dual;
select sign(0) from dual;