Oracle.数据的增删改、表操作（创建，修改，删除）、数据类型

SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno;

SELECT dname,loc FROM dept;

SELECT JOB,ename FROM emp;

SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno;

注意：
批量执行SQL语句时，写成一个SQL脚本，使用SQLPLUS命令   @路径/A.SQL

2、交集运算：
INTERSECT:  查询两个查询语句中重复的部分

SELECT * FROM EMP WHERE deptno=30
INTERSECT
SELECT * FROM emp WHERE upper(JOB)='MANAGER';

3、差运算：
minus:第一个查询语句的结果减去交集

SELECT * FROM EMP WHERE deptno=30
minus
SELECT * FROM emp WHERE upper(JOB)='MANAGER';

SELECT * FROM emp WHERE upper(JOB)='MANAGER'
MINUS
SELECT * FROM EMP WHERE deptno=30;

select avg(sal) from emp where deptno=30; 

二、相关子查询
---查询员工姓名，部门名称
SELECT ename,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

SELECT ename,(SELECT dname FROM dept where deptno=emp.deptno)
FROM emp;

---查询员工的姓名，职务，要比员工所在职务的平均薪水高的。
SELECT ename,JOB
FROM EMP A
WHERE sal>(SELECT avg(sal) FROM emp WHERE JOB=A.JOB);

--2.查询工资为其部门最低工资的员工编号，姓名，工资
SELECT empno,ename,sal
FROM emp e
WHERE sal=(SELECT MIN(sal) FROM emp WHERE deptno=e.deptno);

2、exists:
查询哪些人是经理?
SELECT empno,ename,sal
FROM emp
WHERE empno in(select mgr from emp where mgr is not null);

SELECT empno,ename,sal
FROM emp m
WHERE EXISTS (SELECT empno FROM emp WHERE mgr=m.empno);

1.列出至少有一个雇员的所有部门名称。
SELECT dname
FROM dept
WHERE exists(SELECT empno FROM emp WHERE deptno=dept.deptno);
2.列出一个雇员都没有的所有部门名称。
SELECT dname
FROM dept
WHERE NOT EXISTS(SELECT empno FROM emp WHERE deptno=dept.deptno);

1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。
SELECT ename,deptno
FROM emp e
WHERE sal>(select avg(sal) from emp where deptno=e.deptno);
2.查询员工姓名和直接上级的名字。
SELECT ename,(SELECT ename FROM emp WHERE empno=e.mgr)
FROM emp e;
3.查询每个部门工资最高的员工姓名，工资。
SELECT ename,sal
FROM emp e
WHERE sal = (select max(sal) from emp where deptno=e.deptno);
4.查询每个部门工资前两名高的员工姓名，工资
SELECT ename,sal
FROM emp e
WHERE (select count(empno) from emp where deptno=e.deptno and sal>e.sal) <=1;

三、层次查询
LEVEL:等级

SELECT
FROM
WHERE
START WITH 起始条件 
CONNECT BY PRIOR 条件 

从顶向下查询：
SELECT level,empno,ename,mgr,sal
FROM emp
START WITH empno=7788
CONNECT BY PRIOR mgr = empno;

从底向上查询：
SELECT level,empno,ename,mgr,sal
FROM emp
START WITH upper(ename)='SMITH'
CONNECT BY PRIOR empno = mgr;

四、
1、创建数据库实例或删除实例，修改实例
配置移置工具---》database configuration assistant     相当于执行oracle安装目录下bin目录中的dbca.bat

一个数据库实例包含三类文件：数据文件（.dbf）、控制文件（.ctl）、日志文件（.log）

2、配置监听
配置移置工具---》net configuration assistant    相当于执行Oralce安装目录bin目录中的netca.bat

3、连接数据库：
sqlplus 用户名/密码@数据库服务器IP:监听端口/实例名

sqlplus 用户名/密码@本地服务名

sqlplus 用户名/密码   ---连接的是默认数据库，如果有多个实例存在，将不清楚自己连接的到底是哪一个实例

sqlplus /@数据库服务器IP:监听端口/实例名 as sysdba;

sqlplus登录数据库成功后，切换用户
conn 用户名/密码@数据库服务器IP:监听端口/实例名

conn 用户名/密码@本地服务名

conn /@数据库服务器IP:监听端口/实例名 as sysdba;

四、数据的增删改
1、备份表
（01）全表备份
CREATE TABLE 新表名 AS 子查询;

---将emp表全表备份
CREATE TABLE emp_bak AS SELECT * FROM emp;

SELECT * FROM emp_bak;

(02）只备份表结构，不备份数据
CREATE TABLE 新表名 AS 子查询 WHERE 1=2;
CREATE TABLE emp_bak1 AS SELECT * FROM emp where 1=2;

select * from emp_bak1;

(03)在备表的同时，修改列名
CREATE TABLE 新表名(新列名,新列名....) AS SELECT 列名,列名.... FROM 表名 WHERE 条件;

CREATE TABLE emp_bak2(pno,ename,work,salary) AS SELECT empno,ename,job,sal from emp;

SELECT * FROM emp_bak2;

2、新增表数据
（01）复制表数据
INSERT INTO 表名（列名,列名....） 子查询;

---将dept表的数据复制到emp表中
INSERT INTO emp(empno,ename,JOB) SELECT * FROM dept;

select * from emp;

(02)插入数据
INSERT INTO 表名[(列名,列名....)] VALUES(值1,值2....);

---不写列名,给表中的所有列赋值
INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);

INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
注意：当插入数据时，表没有指定列名，如果列想要插入null或是默认值，值不可以不写，要写成null或default

SELECT * FROM EMP;
desc EMP;
ALTER TABLE emp MODIFY(JOB DEFAULT 'clerk');

---写列名,对于没有指定的列，如果有默认值直接赋默认值，如果没有默认值，给null
INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);

select *　from emp;

INSERT INTO emp(ename,JOB,sal,comm,deptno) VALUES('lily','salesman',2000,200,10);  ---出错

---使用子查询插入数据
---往emp表中给SALES部门新增一名员工
INSERT INTO emp(empno,ename,JOB,sal,deptno) 
VALUES(9999,'wangwu','salesman',2000,(SELECT deptno FROM dept WHERE upper(dname)='SALES'));

3、修改表数据
UPDATE 表名 SET 列名=新值,列名=新值,... [WHERE 条件];

--修改emp表中职务为CLERK的员工的薪水，都增加20%;
UPDATE emp SET sal=sal*1.2 WHERE upper(job)='CLERK';

SELECT * FROM emp;

UPDATE emp set ename='aaa',job='test',mgr=7788,sal=sal*1.3,comm=500 WHERE empno=10;

SELECT * FROM EMP WHERE EMPNO=10;

---全表修改
UPDATE emp SET sal=sal*0.8,comm=100;

---使用子查询
UPDATE EMP SET sal=sal+500,comm=200 WHERE deptno=(SELECT deptno FROM dept WHERE UPPER(dname)='SALES');

4、删除表数据
（01）DELETE [FROM] 表名 [WHERE 条件];

DELETE FROM emp WHERE deptno=10;

DELETE emp WHERE deptno=20;

DELETE * FROM emp;  ---错误的

DELETE emp;

select * from emp;

INSERT INTO emp SELECT * FROM emp_bak;

(02)截断表：删除全表数据
TRUNCATE TABLE 表名;

truncate table emp;

DELETE 和TRUNCATE的区别：
   DELETE 可以删除部分数据，truncate只能删除全表数据
   DELETE 删除的数据可以回滚，truncate不允许回滚
   DELETE删除的速度比truncate慢，因为delete要写日志

5、事务：TPL

DQL:数据查询语言   SELECT
DML:数据操作语言   INSERT DELETE UPDATE
DDL:数据定义语言   CREATE ALTER DROP
DCL:数据控制语言   GRANT REVOKE
TPL:事务语言      COMMIT,ROLLBACK,SAVEPOINT

1、commit:提交
2、ROLLBACK; 回滚
3、SAVEPOINT 保存点名;   设置事务保存点
4、rollback TO 事务保存点;   --回滚至事务保存点

select * from emp;


INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);

INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
savepoint aa;
INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);

insert into emp select * from emp_bak;

ROLLBACK TO aa;

rollback;

INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);

INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
commit;
INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);

INSERT INTO emp SELECT * FROM emp_bak;

CREATE TABLE dept_bak AS SELECT * FROM dept;

select * from emp;

ROLLBACK;

五、新增表：
1、查询表结构：  sqlplus命令
DESC 表名;

2、创建表：
CREATE TABLE 表名(
  列名 数据类型 [DEFAULT 默认值 约束],
  列名 数据类型 [DEFAULT 默认值 约束],
  ...
  列名 数据类型 [DEFAULT 默认值 约束]
);

3、数据类型：
数值型：  NUMBER
字符串类型： VARCHAR2
日期型 ：   DATE
大对象类型：  clob,blob

(01)数值 NUMBER(p,s)  p有效位数，s精度
s=0:NUMBER(p)
s>0:小数点右边的位数为s，小数点左边的有效位数为p-s位
s<0:小数点左边的有效位数为：p+|s|位

CREATE TABLE A(
  id1 NUMBER(5),
  id2 NUMBER(5,2),
  id3 NUMBER(5,-2)
);

select * from a;
INSERT INTO A(id1) VALUES(455.55);
INSERT INTO A(id1) VALUES(45555.55);
INSERT INTO A(id1) VALUES(455555); ---插入失败

INSERT INTO A(id2) VALUES(455.55);
INSERT INTO A(id2) VALUES(455.55555);
INSERT INTO A(id2) VALUES(455);

INSERT INTO A(id3) VALUES(455.55);
INSERT INTO A(id3) VALUES(45555);
INSERT INTO A(id3) VALUES(4555555);
INSERT INTO A(id3) VALUES(45555555);---插入失败

(02)字符串类型
CHAR(n):固长字符串，无论字符串的长度是否为N,结果都为n，不足的补空格
VARCHAR2(n)：变长字符串，字符串有几位占用几位

drop table a;

CREATE TABLE A(
  name1 CHAR(10) references emp(ename),
  name2 VARCHAR2(10)
);

INSERT INTO A VALUES('aaa','aaa');
INSERT INTO A VALUES('aaabbbccca','aaa');
SELECT name1,LENGTH(name1),name2,LENGTH(name2)
FROM A;

(03)日期型：
默认日期：日-月-年
DROP TABLE A;

CREATE TABLE A(
  birthday DATE
);

INSERT INTO A VALUES('10-10月-2018');
insert into a values(to_date('20181010','yyyymmdd'));

SELECT * FROM A;

(04)对象(4G以内):
CLOB:文件对象
blob:二进制对象

drop table a;

CREATE TABLE A(
  obj CLOB,
  obj2 BLOB
);

insert into a values('fdcvgsjkmadqwjkrwerghejwklfdsnmkasdfbwekrqwetrwerqwere','000000000111111000011111');

select * from a;

六. 修改表
1.添加列
ALTER TABLE 表名 ADD(列名 数据类型 [DEFAULT 默认值 约束],列名 数据类型 [DEFAULT 默认值 约束]...);
2.修改列的属性:修改数据类型,长度,默认值,约束 
alter table 表名 modify(列名 数据类型 [DEFAULT 默认值 约束]...);
3.修改列名
alter table 表名 rename column 旧的列名 to 新的列名;
4.删除列
alter table 表名 drop column 列名;
5.修改表名
rename 旧的表名 to 新的表名;

七.删除表
drop table 表名 [cascade constraints];

CASCADE CONSTRAINTS:---当主键记录被外键引用时,可以添加此选项删除表的同时删除相关约束.