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:---当主键记录被外键引用时,可以添加此选项删除表的同时删除相关约束.
