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

  1 SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
  2 
  3 SELECT dname,loc FROM dept;
  4 
  5 SELECT JOB,ename FROM emp;
  6 
  7 SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
  8 
  9 注意：
 10 批量执行SQL语句时，写成一个SQL脚本，使用SQLPLUS命令   @路径/A.SQL
 11 
 12 2、交集运算：
 13 INTERSECT:  查询两个查询语句中重复的部分
 14 
 15 SELECT * FROM EMP WHERE deptno=30
 16 INTERSECT
 17 SELECT * FROM emp WHERE upper(JOB)='MANAGER';
 18 
 19 3、差运算：
 20 minus:第一个查询语句的结果减去交集
 21 
 22 SELECT * FROM EMP WHERE deptno=30
 23 minus
 24 SELECT * FROM emp WHERE upper(JOB)='MANAGER';
 25 
 26 SELECT * FROM emp WHERE upper(JOB)='MANAGER'
 27 MINUS
 28 SELECT * FROM EMP WHERE deptno=30;
 29 
 30 select avg(sal) from emp where deptno=30; 
 31 
 32 二、相关子查询
 33 ---查询员工姓名，部门名称
 34 SELECT ename,dname
 35 FROM emp,dept
 36 WHERE emp.deptno=dept.deptno;
 37 
 38 SELECT ename,(SELECT dname FROM dept where deptno=emp.deptno)
 39 FROM emp;
 40 
 41 ---查询员工的姓名，职务，要比员工所在职务的平均薪水高的。
 42 SELECT ename,JOB
 43 FROM EMP A
 44 WHERE sal>(SELECT avg(sal) FROM emp WHERE JOB=A.JOB);
 45 
 46 --2.查询工资为其部门最低工资的员工编号，姓名，工资
 47 SELECT empno,ename,sal
 48 FROM emp e
 49 WHERE sal=(SELECT MIN(sal) FROM emp WHERE deptno=e.deptno);
 50 
 51 2、exists:
 52 查询哪些人是经理?
 53 SELECT empno,ename,sal
 54 FROM emp
 55 WHERE empno in(select mgr from emp where mgr is not null);
 56 
 57 SELECT empno,ename,sal
 58 FROM emp m
 59 WHERE EXISTS (SELECT empno FROM emp WHERE mgr=m.empno);
 60 
 61 1.列出至少有一个雇员的所有部门名称。
 62 SELECT dname
 63 FROM dept
 64 WHERE exists(SELECT empno FROM emp WHERE deptno=dept.deptno);
 65 2.列出一个雇员都没有的所有部门名称。
 66 SELECT dname
 67 FROM dept
 68 WHERE NOT EXISTS(SELECT empno FROM emp WHERE deptno=dept.deptno);
 69 
 70 1.查询薪水多于他所在部门平均薪水的雇员名字，部门号。
 71 SELECT ename,deptno
 72 FROM emp e
 73 WHERE sal>(select avg(sal) from emp where deptno=e.deptno);
 74 2.查询员工姓名和直接上级的名字。
 75 SELECT ename,(SELECT ename FROM emp WHERE empno=e.mgr)
 76 FROM emp e;
 77 3.查询每个部门工资最高的员工姓名，工资。
 78 SELECT ename,sal
 79 FROM emp e
 80 WHERE sal = (select max(sal) from emp where deptno=e.deptno);
 81 4.查询每个部门工资前两名高的员工姓名，工资
 82 SELECT ename,sal
 83 FROM emp e
 84 WHERE (select count(empno) from emp where deptno=e.deptno and sal>e.sal) <=1;
 85 
 86 三、层次查询
 87 LEVEL:等级
 88 
 89 SELECT
 90 FROM
 91 WHERE
 92 START WITH 起始条件 
 93 CONNECT BY PRIOR 条件 
 94 
 95 从顶向下查询：
 96 SELECT level,empno,ename,mgr,sal
 97 FROM emp
 98 START WITH empno=7788
 99 CONNECT BY PRIOR mgr = empno;
100 
101 从底向上查询：
102 SELECT level,empno,ename,mgr,sal
103 FROM emp
104 START WITH upper(ename)='SMITH'
105 CONNECT BY PRIOR empno = mgr;
106 
107 四、
108 1、创建数据库实例或删除实例，修改实例
109 配置移置工具---》database configuration assistant     相当于执行oracle安装目录下bin目录中的dbca.bat
110 
111 一个数据库实例包含三类文件：数据文件（.dbf）、控制文件（.ctl）、日志文件（.log）
112 
113 2、配置监听
114 配置移置工具---》net configuration assistant    相当于执行Oralce安装目录bin目录中的netca.bat
115 
116 3、连接数据库：
117 sqlplus 用户名/密码@数据库服务器IP:监听端口/实例名
118 
119 sqlplus 用户名/密码@本地服务名
120 
121 sqlplus 用户名/密码   ---连接的是默认数据库，如果有多个实例存在，将不清楚自己连接的到底是哪一个实例
122 
123 sqlplus /@数据库服务器IP:监听端口/实例名 as sysdba;
124 
125 sqlplus登录数据库成功后，切换用户
126 conn 用户名/密码@数据库服务器IP:监听端口/实例名
127 
128 conn 用户名/密码@本地服务名
129 
130 conn /@数据库服务器IP:监听端口/实例名 as sysdba;
131 
132 四、数据的增删改
133 1、备份表
134 （01）全表备份
135 CREATE TABLE 新表名 AS 子查询;
136 
137 ---将emp表全表备份
138 CREATE TABLE emp_bak AS SELECT * FROM emp;
139 
140 SELECT * FROM emp_bak;
141 
142 (02）只备份表结构，不备份数据
143 CREATE TABLE 新表名 AS 子查询 WHERE 1=2;
144 CREATE TABLE emp_bak1 AS SELECT * FROM emp where 1=2;
145 
146 select * from emp_bak1;
147 
148 (03)在备表的同时，修改列名
149 CREATE TABLE 新表名(新列名,新列名....) AS SELECT 列名,列名.... FROM 表名 WHERE 条件;
150 
151 CREATE TABLE emp_bak2(pno,ename,work,salary) AS SELECT empno,ename,job,sal from emp;
152 
153 SELECT * FROM emp_bak2;
154 
155 2、新增表数据
156 （01）复制表数据
157 INSERT INTO 表名（列名,列名....） 子查询;
158 
159 ---将dept表的数据复制到emp表中
160 INSERT INTO emp(empno,ename,JOB) SELECT * FROM dept;
161 
162 select * from emp;
163 
164 (02)插入数据
165 INSERT INTO 表名[(列名,列名....)] VALUES(值1,值2....);
166 
167 ---不写列名,给表中的所有列赋值
168 INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
169 
170 INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
171 注意：当插入数据时，表没有指定列名，如果列想要插入null或是默认值，值不可以不写，要写成null或default
172 
173 SELECT * FROM EMP;
174 desc EMP;
175 ALTER TABLE emp MODIFY(JOB DEFAULT 'clerk');
176 
177 ---写列名,对于没有指定的列，如果有默认值直接赋默认值，如果没有默认值，给null
178 INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);
179 
180 select *　from emp;
181 
182 INSERT INTO emp(ename,JOB,sal,comm,deptno) VALUES('lily','salesman',2000,200,10);  ---出错
183 
184 ---使用子查询插入数据
185 ---往emp表中给SALES部门新增一名员工
186 INSERT INTO emp(empno,ename,JOB,sal,deptno) 
187 VALUES(9999,'wangwu','salesman',2000,(SELECT deptno FROM dept WHERE upper(dname)='SALES'));
188 
189 3、修改表数据
190 UPDATE 表名 SET 列名=新值,列名=新值,... [WHERE 条件];
191 
192 --修改emp表中职务为CLERK的员工的薪水，都增加20%;
193 UPDATE emp SET sal=sal*1.2 WHERE upper(job)='CLERK';
194 
195 SELECT * FROM emp;
196 
197 UPDATE emp set ename='aaa',job='test',mgr=7788,sal=sal*1.3,comm=500 WHERE empno=10;
198 
199 SELECT * FROM EMP WHERE EMPNO=10;
200 
201 ---全表修改
202 UPDATE emp SET sal=sal*0.8,comm=100;
203 
204 ---使用子查询
205 UPDATE EMP SET sal=sal+500,comm=200 WHERE deptno=(SELECT deptno FROM dept WHERE UPPER(dname)='SALES');
206 
207 4、删除表数据
208 （01）DELETE [FROM] 表名 [WHERE 条件];
209 
210 DELETE FROM emp WHERE deptno=10;
211 
212 DELETE emp WHERE deptno=20;
213 
214 DELETE * FROM emp;  ---错误的
215 
216 DELETE emp;
217 
218 select * from emp;
219 
220 INSERT INTO emp SELECT * FROM emp_bak;
221 
222 (02)截断表：删除全表数据
223 TRUNCATE TABLE 表名;
224 
225 truncate table emp;
226 
227 DELETE 和TRUNCATE的区别：
228    DELETE 可以删除部分数据，truncate只能删除全表数据
229    DELETE 删除的数据可以回滚，truncate不允许回滚
230    DELETE删除的速度比truncate慢，因为delete要写日志
231 
232 5、事务：TPL
233 
234 DQL:数据查询语言   SELECT
235 DML:数据操作语言   INSERT DELETE UPDATE
236 DDL:数据定义语言   CREATE ALTER DROP
237 DCL:数据控制语言   GRANT REVOKE
238 TPL:事务语言      COMMIT,ROLLBACK,SAVEPOINT
239 
240 1、commit:提交
241 2、ROLLBACK; 回滚
242 3、SAVEPOINT 保存点名;   设置事务保存点
243 4、rollback TO 事务保存点;   --回滚至事务保存点
244 
245 select * from emp;
246 
247 
248 INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
249 
250 INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
251 savepoint aa;
252 INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);
253 
254 insert into emp select * from emp_bak;
255 
256 ROLLBACK TO aa;
257 
258 rollback;
259 
260 INSERT INTO emp VALUES(8888,'zhangsan','java dev',7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
261 
262 INSERT INTO emp VALUES(8889,'zhangsan',DEFAULT,7788,to_date('2018-07-11','yyyy-mm-dd'),4000,500,40);
263 commit;
264 INSERT INTO emp(empno,ename,sal) VALUES(6666,'JACK',1000);
265 
266 INSERT INTO emp SELECT * FROM emp_bak;
267 
268 CREATE TABLE dept_bak AS SELECT * FROM dept;
269 
270 select * from emp;
271 
272 ROLLBACK;
273 
274 五、新增表：
275 1、查询表结构：  sqlplus命令
276 DESC 表名;
277 
278 2、创建表：
279 CREATE TABLE 表名(
280   列名 数据类型 [DEFAULT 默认值 约束],
281   列名 数据类型 [DEFAULT 默认值 约束],
282   ...
283   列名 数据类型 [DEFAULT 默认值 约束]
284 );
285 
286 3、数据类型：
287 数值型：  NUMBER
288 字符串类型： VARCHAR2
289 日期型 ：   DATE
290 大对象类型：  clob,blob
291 
292 (01)数值 NUMBER(p,s)  p有效位数，s精度
293 s=0:NUMBER(p)
294 s>0:小数点右边的位数为s，小数点左边的有效位数为p-s位
295 s<0:小数点左边的有效位数为：p+|s|位
296 
297 CREATE TABLE A(
298   id1 NUMBER(5),
299   id2 NUMBER(5,2),
300   id3 NUMBER(5,-2)
301 );
302 
303 select * from a;
304 INSERT INTO A(id1) VALUES(455.55);
305 INSERT INTO A(id1) VALUES(45555.55);
306 INSERT INTO A(id1) VALUES(455555); ---插入失败
307 
308 INSERT INTO A(id2) VALUES(455.55);
309 INSERT INTO A(id2) VALUES(455.55555);
310 INSERT INTO A(id2) VALUES(455);
311 
312 INSERT INTO A(id3) VALUES(455.55);
313 INSERT INTO A(id3) VALUES(45555);
314 INSERT INTO A(id3) VALUES(4555555);
315 INSERT INTO A(id3) VALUES(45555555);---插入失败
316 
317 (02)字符串类型
318 CHAR(n):固长字符串，无论字符串的长度是否为N,结果都为n，不足的补空格
319 VARCHAR2(n)：变长字符串，字符串有几位占用几位
320 
321 drop table a;
322 
323 CREATE TABLE A(
324   name1 CHAR(10) references emp(ename),
325   name2 VARCHAR2(10)
326 );
327 
328 INSERT INTO A VALUES('aaa','aaa');
329 INSERT INTO A VALUES('aaabbbccca','aaa');
330 SELECT name1,LENGTH(name1),name2,LENGTH(name2)
331 FROM A;
332 
333 (03)日期型：
334 默认日期：日-月-年
335 DROP TABLE A;
336 
337 CREATE TABLE A(
338   birthday DATE
339 );
340 
341 INSERT INTO A VALUES('10-10月-2018');
342 insert into a values(to_date('20181010','yyyymmdd'));
343 
344 SELECT * FROM A;
345 
346 (04)对象(4G以内):
347 CLOB:文件对象
348 blob:二进制对象
349 
350 drop table a;
351 
352 CREATE TABLE A(
353   obj CLOB,
354   obj2 BLOB
355 );
356 
357 insert into a values('fdcvgsjkmadqwjkrwerghejwklfdsnmkasdfbwekrqwetrwerqwere','000000000111111000011111');
358 
359 select * from a;
360 
361 六. 修改表
362 1.添加列
363 ALTER TABLE 表名 ADD(列名 数据类型 [DEFAULT 默认值 约束],列名 数据类型 [DEFAULT 默认值 约束]...);
364 2.修改列的属性:修改数据类型,长度,默认值,约束 
365 alter table 表名 modify(列名 数据类型 [DEFAULT 默认值 约束]...);
366 3.修改列名
367 alter table 表名 rename column 旧的列名 to 新的列名;
368 4.删除列
369 alter table 表名 drop column 列名;
370 5.修改表名
371 rename 旧的表名 to 新的表名;
372 
373 七.删除表
374 drop table 表名 [cascade constraints];
375 
376 CASCADE CONSTRAINTS:---当主键记录被外键引用时,可以添加此选项删除表的同时删除相关约束.