集合运算-高级子查询-层次查询-oracle数据库网络连接

--第八章集合运算
sql脚本一次执行
@d:/a.sql
d盘，a.sql 文件
注意：
当我们批量去执行sql语句时，写成一个sql脚本，使用sqlplus命令 @路径/a.sql
-.查询集合运算
1.并集:又称为联合运算,两个查询语句的查询出来的列的个数，对应列的类型相符才能合并
UNION: 将两个查询结果合并输出，查询结果中的重复记录只显示一次
UNION ALL: 将两个查询结果合并输出，查询结果中的重复记录显示多次

SELECT * FROM emp WHERE deptno=30
UNION
SELECT * FROM emp WHERE UPPER(job)='MANAGER';

SELECT * FROM emp WHERE deptno=30
UNION ALL
SELECT * FROM emp WHERE UPPER(job)='MANAGER';

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

3.差运算:
MINUS:第一个查询语句的结果减去交集

--第九章高级子查询
二.相关子查询
---查询员工姓名，部门名称
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);

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

2.exits:
查询哪些人是经理

SELECT empno,ename,sal
FROM emp
WHERE empno IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);

SELECT empno,ename,sal
FROM emp e
WHERE EXISTS(SELECT empno FROM emp WHERE mgr=e.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 enaem,sal
FROM emp e
WHERE sal=(SELECT MAX(sal) FROM emp WHERE e.deptno=emp.deptno);
4.查询每个部门工资前两名高的员工姓名，工资
SELECT ename,sal
FROM emp e
WHERE sal IN(SELECT * FROM (SELECT sal FROM emp WHERE e.deptno=emp.deptno ORDER BY sal DESC) ROWNUM<=2)

第十章层次查询
LEVEL：等级
SELECT
FROM
WHERE
START WITH 起始条件
CONNECT BY PRIOR 条件

从顶向下查询：查上属，上之所以为上级别高，值低，下属级别低，值高
SELECT empno,ename,mgr,sal
FROM emp
START WITH empno=7788
CONNECT BY PRIOR mgr=empno;
父类=子类

从底向上查询：查下属
SELECT empno,ename,mgr,sal
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr
子类=父类

SELECT level,empno,ename,mgr,sal
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr
ORDER BY LEVEL;

第十二章
oracle数据库网络连接
创建数据库实例或删除实例，修改实例
配置移植工具---》DATABASE configuration assistant
相当于oracle安装目录下的bin目录下的dbca.bat

一个数据库实例包含三类文件:数据文件(.dbf),控制文件(.ctl),日志文件(.log)
2.配置监听
配置移植工具---》netconfiguration assistant 相当与执行oracle安装目录bin目录中的netca.bat

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

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

sqlplus 用户名/密码 --连接的是默认数据库，如果有多个实例存在，将不知道连接的是哪个

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

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

conn 用户名/密码@本地服务名
conn /@数据库服务器IP:监听端口/实例名 AS SYSDBA;

四.数据的增删改
1.备份表
(01)全表备份
CREATE tabel 新表名 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;

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

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

2.新增表数据
(01)复制表数据
INSERT INTO 表名(列名,列名...) 子查询;

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

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

--不写列名,给表中的所有列赋值
INSERT INTO emp VALUES()

SELECT * FROM emp;
DESC emp;

注意：当插入数据时，表没有指定列名，如果列名想要插入null或是默认值，
值可以不写，要写成null或default
若插入时想要为默认值，填default
将job的默认值设为clerk
ALTER TABLE emp MODIFY(job DEFAULT 'clerk');

---写列名时，对于没有指定的列，如果有默认值直接赋默认值，如果没有默认值，给null
---使用子查询插入数据
---往emp表中给sales部门新增一名员工
INSERT INTO emp(empno,ename,job,sal,deptno)
VALUES(9999,'wangwu','salesman',2000,(SELECT deptno FROM dept WHERE UPPER(dname)='salers'));

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

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

----全表修改
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)='salers');

4.删除表数据
(01) DELETE [FROM] 表名 [WHERE 条件]

(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 事物保存点; ---会滚至事物保存点

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

2.数据类型：
数值型：NUMBER
字符串类型：VARCHAR2
日期型：DATE
大对象类型：CLOB,BLOB

3.创建表

CREATE TABLE 表名(
列名 数据类型(DEFAULT 默认值 约束),
列名 数据类型(DEFAULT 默认值 约束),
...
列名 数据类型(DEFAULT 默认值 约束),
)

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

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

(03)日期型：
默认日期：日-月-年

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

CREATE TABLE A(
obj CLOB,
obj2 BLOB

)

六.修改表
1.添加列
ALTER TABLE 表名 ADD(列名 数据类型)
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:---当主键记录被外键引用时，可以添加此选项删除表的同时删除相关约束

 

转载于:https://www.cnblogs.com/haxy/p/9296075.html