2018-07-11 08:26:00
1 有某个学生运动会比赛信息的数据库，保存了如下的表：
2 运动员sporter表：(运动员编号sporterid，运动员姓名name，运动员性别sex，所属系department)
3 项目item表(项目编号itemid，项目itemname，loc地区)
4 成绩grade表(运动员编号sporterid，项目编号itemid，积分mark)
5
6 1.求出总积分最高的系名及总积分
7 SELECT department,sum(mark)
8 FROM sporter s,grade g
9 WHERE s.sporterid=g.sporterid
10 GROUP BY department
11 HAVING sum(mark)=(SELECT max(sum(mark))
12 FROM sporter s,grade g
13 WHERE s.sporterid=g.sporterid
14 GROUP BY department);
15
16 2.查询在一操场进行比赛的项目名称及其冠军的姓名
17 SELECT itemname,NAME
18 FROM sporter s,item i,grade g
19 WHERE s.sporterid=g.sporterid and i.itemid=g.itemid and loc='一操场' and mark=6;
20
21 3.找出参加了王玥所参加过的项目的其他同学的姓名
22 SELECT NAME
23 FROM sporter s,grade g
24 WHERE s.sporterid=g.sporterid
25 AND itemid IN(SELECT itemid
26 FROM sporter s,grade g
27 where s.sporterid=g.sporterid and name='王玥') and name!='王玥';
28
29 4.经查,王玥因为使用了违禁药品，其成绩都记为0分，请在数据库中做出相应修改
30 update grade set mark=0 where sporterid=(select sporterid from sporter where name='王玥');
31
32 5.经组委会协商，需要删除女子跳高比赛项目
33
34 一、约束
35 1、not NULL:非空约束，不允许为null值
36 建表时创建：
37 CREATE TABLE 表名(
38 列名 数据类型 DEFAULT 默认值 NOT NULL,
39 ...
40 );
41
42 修改表时创建非空约束：
43 ALTER TABLE 表名 MODIFY(列名 NOT NULL);
44
45 由not NULL 修改为null
46 ALTER TABLE 表名 MODIFY(列名 NULL);
47
48 CREATE TABLE A(
49 ID NUMBER(4) DEFAULT 1 NOT NULL,
50 NAME VARCHAR2(20) NOT NULL
51 );
52
53 INSERT INTO A(NAME) VALUES('aaa');
54 INSERT INTO A(ID) VALUES(5);
55
56 2、主键：又称为唯一索引键 不允许主键列的值为Null且不允许重复
57 PRIMARY KEY.
58 建表时创建：
59 CREATE TABLE 表名(
60 列名 数据类型 DEFAULT 默认值 primary key,
61 ...
62 );
63
64 CREATE TABLE 表名(
65 列名 数据类型 DEFAULT 默认值,
66 ...
67 列名 数据类型 DEFAULT 默认值,
68 constraint 约束名 primary key(约束内容)
69 );
70
71 修改表时创建：
72 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(约束内容);
73
74 删除主键约束：
75 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
76
77 DROP TABLE A;
78 CREATE TABLE A(
79 ID NUMBER(4) PRIMARY KEY,
80 NAME VARCHAR2(20)
81 );
82
83 INSERT INTO A(NAME) VALUES('aaa');
84 INSERT INTO A(ID,NAME) VALUES(1,'aaa');
85
86 DESC A;
87 ALTER TABLE A MODIFY(ID NULL); ---不能将主键约束的not null修改为null
88
89 ALTER TABLE A DROP CONSTRAINT SYS_C009789;
90
91 3、唯一约束 ： 不允许重复值
92 建表时创建：
93 CREATE TABLE 表名(
94 列名 数据类型 DEFAULT 默认值 unique,
95 ...
96 );
97
98 CREATE TABLE 表名(
99 列名 数据类型 DEFAULT 默认值,
100 ...
101 列名 数据类型 DEFAULT 默认值,
102 constraint 约束名 unique(约束内容)
103 );
104
105 修改表时创建：
106 ALTER TABLE 表名 ADD CONSTRAINT 约束名 unique(约束内容);
107
108 删除主键约束：
109 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
110
111 DROP TABLE A;
112 CREATE TABLE A(
113 ID NUMBER(3) PRIMARY KEY,
114 NAME VARCHAR2(20) UNIQUE
115 );
116
117 INSERT INTO A(ID) VALUES(3);
118 SELECT * FROM A;
119
120 4、检查约束 ：check
121 建表时创建：
122 CREATE TABLE 表名(
123 列名 数据类型 DEFAULT 默认值 check(条件),
124 ...
125 );
126
127 CREATE TABLE 表名(
128 列名 数据类型 DEFAULT 默认值,
129 ...
130 列名 数据类型 DEFAULT 默认值,
131 constraint 约束名 check(条件)
132 );
133
134 修改表时创建：
135 ALTER TABLE 表名 ADD CONSTRAINT 约束名 check(条件);
136
137 删除检查键约束：
138 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
139
140 DROP TABLE A;
141
142 CREATE TABLE A(
143 ID NUMBER(3) CHECK(ID BETWEEN 1 AND 100)
144 );
145
146 INSERT INTO A VALUES(0);
147
148 5、外键：子表的外键的值必须在父表的主键中存在
149 父表：主键
150 子表：外键
151 FOREIGN KEY
152
153 建表时创建：
154 CREATE TABLE 表名(
155 列名 数据类型 DEFAULT 默认值 references 父表(主键) on delete cascade,
156 ...
157 );
158
159 CREATE TABLE 表名(
160 列名 数据类型 DEFAULT 默认值,
161 ...
162 列名 数据类型 DEFAULT 默认值,
163 constraint 约束名 foreign key(外键) references 父表(主键) on delete cascade
164 );
165
166 修改表时创建：
167 ALTER TABLE 表名 ADD CONSTRAINT 约束名 foreign key(外键) references 父表(主键) on delete cascade;
168
169 删除检查键约束：
170 ALTER TABLE 表名 DROP CONSTRAINT 约束名;
171
172 ON DELETE CASCADE:级联删除 当删除父表的主键记录时子表的相关记录会一并删除
173
174 DROP TABLE A;
175 CREATE TABLE A(
176 ID NUMBER(3) PRIMARY KEY,
177 NAME VARCHAR2(20) UNIQUE
178 );
179
180 CREATE TABLE b(
181 bid NUMBER(3) REFERENCES A(ID) ON DELETE CASCADE,
182 NAME VARCHAR2(20)
183 );
184
185 INSERT INTO A VALUES(1,'aaa');
186
187 INSERT INTO b VALUES(1,'aaa');
188
189 SELECT * FROM A;
190 SELECT * FROM b;
191 DELETE FROM A;
192
193 ALTER TABLE b ADD CONSTRAINT scott_b_name_fk FOREIGN KEY(NAME) REFERENCES A(NAME);
194
195 drop table b;
196
197 CREATE TABLE b(
198 bid NUMBER(3) REFERENCES A(ID),
199 NAME VARCHAR2(20)
200 );
201
202
203 二、视图：
204 CREATE [or replace] VIEW 视图名 AS 子查询[ WITH READ ONLY | WITH CHECK OPTION];
205 不占用空间
206 减少查询的难度
207
208 1、创建视图表：
209 ---创建查询emp20部门员工信息的视图表
210 CREATE or replace VIEW v_emp20 AS SELECT * FROM emp WHERE deptno=40;
211
212 SELECT * FROM v_emp20;
213 INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1111,'lisi',7566,3000,20);
214 INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1112,'zhangsan',7566,3000,10);
215
216 update v_emp20 set deptno=40;
217
218 delete from v_emp20;
219
220 SELECT * FROM emp;
221
222 2、with READ ONLY:只读视图
223 CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH READ ONLY;
224 SELECT * FROM v_emp30;
225
226 DELETE FROM v_emp30;
227 INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10);
228
229 3、with CHECK OPTION:条件检查
230 CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH CHECK OPTION;
231 SELECT * FROM v_emp30;
232 INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10); ----错误
233
234 INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,30);
235
236 DELETE FROM v_emp30;
237
238 4、复杂视图：
239 (01)视图的子查询中包含distinct,不允许对视图表增删改操作，只能查询
240 CREATE OR REPLACE VIEW v_emp AS SELECT DISTINCT deptno,JOB FROM emp;
241
242 SELECT * FROM v_emp;
243 DELETE FROM v_emp;
244 INSERT INTO v_emp VALUES(20,'clerk');
245 UPDATE v_emp SET deptno=20;
246
247 (02)视图的子查询中包含rownum，不允许对视图表增删改操作，只能查询
248 CREATE OR REPLACE VIEW v_emp AS SELECT rownum r,empno,ename,sal FROM emp;
249
250 SELECT * FROM v_emp;
251 DELETE FROM v_emp;
252 INSERT INTO v_emp VALUES(20,8888,'aaa',2000);
253 UPDATE v_emp SET sal=20;
254
255 (03)视图的子查询中包含分组函数，不允许对视图表增删改操作，只能查询
256 CREATE OR REPLACE VIEW v_emp AS SELECT count(empno) c,sum(sal) s FROM emp;
257
258 SELECT * FROM v_emp;
259 DELETE FROM v_emp;
260 INSERT INTO v_emp VALUES(18,20000);
261 update v_emp set c=20;
262
263 (04) 视图的子查询中包含了group BY 子句,不允许对视图表进行删除和修改的操作，允许新增和查询
264 CREATE OR REPLACE VIEW v_emp AS SELECT empno,ename FROM emp group by empno,ename;
265 SELECT * FROM v_emp;
266 DELETE FROM v_emp;
267 INSERT INTO v_emp VALUES(8888,'wangwu');
268 update v_emp set empno=20;
269
270 (05)视图的子查询中包含了表达式, 不允许对视图表的表达式列进行增加和修改的操作，允许对非表达式的列进行增删改查
271 CREATE OR REPLACE VIEW v_emp AS SELECT empno,sal*12 salary FROM emp;
272
273 SELECT * FROM v_emp;
274 DELETE FROM v_emp;
275 ROLLBACK;
276 INSERT INTO v_emp VALUES(8888,30000);
277 INSERT INTO v_emp(empno) VALUES(9999);
278
279 update v_emp set empno=20 where rownum=1;
280
281 (06)视图的子查询中不包含源表中的非空约束列,不允许对表进行新增，允许修改，删除 和查询
282 DESC emp;
283 CREATE OR REPLACE VIEW v_emp AS SELECT ename,sal FROM emp;
284
285 INSERT INTO v_emp VALUES('aaa',5000);
286
287 UPDATE v_emp SET sal=3000;
288 DELETE FROM v_emp;
289 rollback;
290
291 5、删除视图
292 DROP VIEW 视图名;
293
294 DROP VIEW v_emp;
295
296 三、序列
297 1、创建序列
298 CREATE SEQUENCE 序列名
299 [INCREMENT BY n] ----步长
300 [START WITH n] ---序列的起始值 序列创建后第一次使用的第一个值
301 [MINVALUE n] ---序列的最小值 序列从第二次循环取值时的最小值
302 [MAXVALUE n] ---序列的最大值
303 [CYCLE] ---序列设置循环取值的标志
304 [CACHE n] ---序列缓存的个数
305
306 ---所有属性都为默认值的序列
307 CREATE SEQUENCE myseq;
308
309 ---设置属性为指定值的序列
310 CREATE SEQUENCE seq1
311 INCREMENT BY 5
312 START WITH 10
313 MINVALUE 5
314 MAXVALUE 50
315 CYCLE
316 CACHE 5;
317
318 两个属性：
319 currval:取序列当前值
320 nextval:序列的下一个值
321 语法：序列名.属性名
322
323 注意：当一个序列创建成功之后，必须先用nextval生成第一个序列值才可以使用该序列
324
325 SELECT myseq.nextval FROM dual;
326
327 SELECT myseq.currval FROM dual;
328
329 SELECT seq1.nextval,seq1.currval FROM dual;
330
331 DROP TABLE A CASCADE CONSTRAINTS;
332 CREATE TABLE A(
333 ID NUMBER(5) PRIMARY KEY
334 );
335
336 insert into a values(myseq.nextval);
337
338 select * from a;
339
340 2、修改序列
341 alter SEQUENCE 序列名
342 [INCREMENT BY n] ----步长
343 [MINVALUE n] ---序列的最小值 序列从第二次循环取值时的最小值
344 [MAXVALUE n] ---序列的最大值
345 [CYCLE] ---序列设置循环取值的标志
346 [CACHE n] ---序列缓存的个数
347
348 3、删除序列
349 DROP SEQUENCE 序列名;
350
351 四、索引
352 1、创建索引
353 手动创建:
354 CREATE INDEX 索引名 ON 表名(列名[,列名...]);
355
356 CREATE TABLE employee1(
357 pno NUMBER(7),
358 pname VARCHAR2(20)
359 );
360
361 CREATE INDEX inx_scott_pno ON employee1(pno);
362 INSERT INTO employee1 SELECT empno,ename FROM emp;
363 COMMIT;
364
365
366 select * from employee1;
367
368 INSERT INTO employee1 SELECT * FROM employee1;
369
370 UPDATE employee1 SET pno=ROWNUM;
371
372
373 SELECT *
374 FROM employee1
375 where pno=99999;
376
377 SELECT *
378 FROM employee1
379 where UPPER(ENAME)='SCOTT';
380
381 函数索引:
382 CREATE INDEX inx_scott_pname ON employee1(upper(pname));
383
384 CREATE INDEX inx_scott_pno_pname ON employee1(pno,pname);
385
386 自动创建索引:当创建主键或唯一键时,也会自动创建对应列的索引
387
388 2\删除索引:
389 DROP INDEX 索引名;
390
391 DROP INDEX inx_scott_pname;
392
393 select * from user_indexes;
394
395 1.使用子查询的方式，创建test表。
396 create table test99 as select empno,ename,sal,deptno from emp;
397 2.快速复制test表中的数据，复制到100w条左右
398 INSERT INTO TEST SELECT * FROM TEST;
399 DESC test99;
400 alter table test99 modify(empno number(12));
401
402 五.用户管理
403 1\创建用户: 管理员有权限创建修改删除用户
404 CREATE USER 用户名 IDENTIFIED BY 密码;
405
406 CREATE USER lwy IDENTIFIED BY lwy;
407
408 注意:在oracle里,新建的用户对数据库没有任意操作权限
409
410 2.赋权限:
411 赋系统权限: GRANT sys_privs.... TO 用户名| PUBLIC| 角色名 [WITH ADMIN OPTION];
412 常用的系统权限：
413 CREATE SESSION:
414 CREATE TABLE:
415 CREATE SEQUENCE:
416 UNLIMITED TABLESPACE;
417
418 GRANT CREATE SESSION,CREATE TABLE,CREATE SEQUENCE,UNLIMITED TABLESPACE TO lwy WITH ADMIN OPTION;
419
420 ---用户系统权限的数据字典表
421 select * from user_sys_privs;
422
423 加收系统权限：revoke sys_privs.... FROM 用户名;
424 revoke create table from lwy;
425
426 赋对象权限:GRANT obj_privs.... | all ON 对象名 TO 用户名|PUBLIC|角色名 [WITH GRANT OPTION];
427 grant select on scott.emp to lwy;
428
429 grant all on scott.dept to lwy;
430
431 ---用户对象权限的数据字典表：
432 select * from user_tab_privs;
433
434 加收对象权限：revoke obj_privs.... ON 对象名 FROM 用户名;
435 REVOKE SELECT ON scott.emp FROM lwy;
436
437 3.角色：
438 DBA:所有权限
439 RESOURCE:对实体进行操作，不能对结构操作
440 CONNECT:只能连接
441
442 --使用角色来给用户赋权限
443 GRANT 角色名 TO 用户名; ---将角色所拥有权限赋给用户
444
445 ---给一个用户赋所有权限
446 grant dba to 用户名;
447
448 自定义角色：
449 create role 角色名 not identified;
450
451 4.修改用户：
452 ---修改密码：
453 alter user 用户名 identified by 新密码;
454
455 ---给用户解锁：
456 ALTER USER 用户名 ACCOUNT UNLOCK;
457
458 ---锁定用户
459 ALTER USER 用户名 ACCOUNT LOCK;
460
461 5.删除用户
462 drop user 用户名 cascade;
463
464 DROP USER lwy CASCADE;
465
466
467 六、匿名块
468 1、定义匿名块：
469 DECLARE
470 定义部分; ---可选部分
471 BEGIN
472 执行部分; ---必选部分
473 EXCEPTION
474 异常处理部分; ---可选部分
475 end;
476
477
478 ---往控制台输出hello world
479 BEGIN
480 dbms_output.put_line('hello world');
481 end;
482
483 ---打开控制台输出开关
484 set serveroutput on;
485
486 --定义变量，赋值给变量，输出变量值
487 DECLARE
488 v_id NUMBER(3);
489 BEGIN
490 v_id:=1;
491 dbms_output.put_line(v_id);
492 END;
493
494 ---查询数据库表中使用into的数据输出到控制台
495 DECLARE
496 v_name VARCHAR2(20);
497 v_sal NUMBER(7,2);
498 BEGIN
499 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=7788;
500 dbms_output.put_line(v_name || ',' || v_sal);
501 end;
502
503 &:调出输入框接收输入数据。
504 ---查询指定员工的薪水
505 SELECT * FROM emp WHERE empno=&no;
506
507 select * from emp where upper(ename)=upper('&name');
508
509
510 DECLARE
511 v_name VARCHAR2(20);
512 v_sal NUMBER(7,2);
513 BEGIN
514 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
515 dbms_output.put_line(v_name || ',' || v_sal);
516 END;
517
518 DECLARE
519 v_name VARCHAR2(20);
520 v_sal NUMBER(7,2);
521 BEGIN
522 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
523 dbms_output.put_line(v_name || ',' || v_sal);
524 exception
525 WHEN no_data_found THEN
526 dbms_output.put_line('对不起，你输入的用户不存在');
527 END;
528
529 %TYPE:使用数据库中某一列的数据类型做为变量的数据类型
530 语法：表名.列名%type;
531
532 DECLARE
533 v_name emp.ename%TYPE;
534 v_sal emp.sal%type;
535 BEGIN
536 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
537 dbms_output.put_line(v_name || ',' || v_sal);
538 exception
539 WHEN no_data_found THEN
540 dbms_output.put_line('对不起，你输入的用户不存在');
541 END;
542
543 %rowtype:行类型，使用数据库中某一个表的一行为数据类型
544 语法：表名%rowtype;
545
546 DECLARE
547 v_emp emp%rowtype;
548 BEGIN
549 SELECT * INTO v_emp FROM emp WHERE empno=&no;
550 dbms_output.put_line(v_emp.empno || ',' || v_emp.ename || ',' || v_emp.sal);
551 end;
2627
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
