2018-07-11 08:26:00
有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员sporter表：（运动员编号sporterid，运动员姓名name，运动员性别sex，所属系department）
项目item表（项目编号itemid，项目itemname，loc地区）
成绩grade表（运动员编号sporterid，项目编号itemid，积分mark） 1.求出总积分最高的系名及总积分
SELECT department,sum(mark)
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY department
HAVING sum(mark)=(SELECT max(sum(mark))
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY department); 2.查询在一操场进行比赛的项目名称及其冠军的姓名
SELECT itemname,NAME
FROM sporter s,item i,grade g
WHERE s.sporterid=g.sporterid and i.itemid=g.itemid and loc='一操场' and mark=6; 3.找出参加了王玥所参加过的项目的其他同学的姓名
SELECT NAME
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
AND itemid IN(SELECT itemid
FROM sporter s,grade g
where s.sporterid=g.sporterid and name='王玥') and name!='王玥'; 4.经查,王玥因为使用了违禁药品，其成绩都记为0分，请在数据库中做出相应修改
update grade set mark=0 where sporterid=(select sporterid from sporter where name='王玥'); 5.经组委会协商，需要删除女子跳高比赛项目 一、约束
1、not NULL:非空约束，不允许为null值
建表时创建：
CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值 NOT NULL,
...
）; 修改表时创建非空约束：
ALTER TABLE 表名 MODIFY(列名 NOT NULL); 由not NULL 修改为null
ALTER TABLE 表名 MODIFY(列名 NULL); CREATE TABLE A(
ID NUMBER(4) DEFAULT 1 NOT NULL,
NAME VARCHAR2(20) NOT NULL
); INSERT INTO A(NAME) VALUES('aaa');
INSERT INTO A(ID) VALUES(5); 2、主键：又称为唯一索引键 不允许主键列的值为Null且不允许重复
PRIMARY KEY.
建表时创建：
CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值 primary key,
...
）; CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值,
...
列名 数据类型 DEFAULT 默认值,
constraint 约束名 primary key(约束内容)
）; 修改表时创建：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(约束内容); 删除主键约束：
ALTER TABLE 表名 DROP CONSTRAINT 约束名; DROP TABLE A;
CREATE TABLE A(
ID NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20)
); INSERT INTO A(NAME) VALUES('aaa');
INSERT INTO A(ID,NAME) VALUES(1,'aaa'); DESC A;
ALTER TABLE A MODIFY(ID NULL); ---不能将主键约束的not null修改为null ALTER TABLE A DROP CONSTRAINT SYS_C009789; 3、唯一约束 ： 不允许重复值
建表时创建：
CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值 unique,
...
）; CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值,
...
列名 数据类型 DEFAULT 默认值,
constraint 约束名 unique(约束内容)
）; 修改表时创建：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 unique(约束内容); 删除主键约束：
ALTER TABLE 表名 DROP CONSTRAINT 约束名; DROP TABLE A;
CREATE TABLE A(
ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(20) UNIQUE
); INSERT INTO A(ID) VALUES(3);
SELECT * FROM A; 4、检查约束 ：check
建表时创建：
CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值 check(条件),
...
）; CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值,
...
列名 数据类型 DEFAULT 默认值,
constraint 约束名 check(条件)
）; 修改表时创建：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 check(条件); 删除检查键约束：
ALTER TABLE 表名 DROP CONSTRAINT 约束名; DROP TABLE A; CREATE TABLE A(
ID NUMBER(3) CHECK(ID BETWEEN 1 AND 100)
); INSERT INTO A VALUES(0); 5、外键：子表的外键的值必须在父表的主键中存在
父表：主键
子表：外键
FOREIGN KEY 建表时创建：
CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值 references 父表(主键) on delete cascade,
...
）; CREATE TABLE 表名（
列名 数据类型 DEFAULT 默认值,
...
列名 数据类型 DEFAULT 默认值,
constraint 约束名 foreign key(外键) references 父表(主键) on delete cascade
）; 修改表时创建：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 foreign key(外键) references 父表(主键) on delete cascade; 删除检查键约束：
ALTER TABLE 表名 DROP CONSTRAINT 约束名; ON DELETE CASCADE:级联删除 当删除父表的主键记录时子表的相关记录会一并删除 DROP TABLE A;
CREATE TABLE A(
ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(20) UNIQUE
); CREATE TABLE b(
bid NUMBER(3) REFERENCES A(ID) ON DELETE CASCADE,
NAME VARCHAR2(20)
); INSERT INTO A VALUES(1,'aaa'); INSERT INTO b VALUES(1,'aaa'); SELECT * FROM A;
SELECT * FROM b;
DELETE FROM A; ALTER TABLE b ADD CONSTRAINT scott_b_name_fk FOREIGN KEY(NAME) REFERENCES A(NAME); drop table b; CREATE TABLE b(
bid NUMBER(3) REFERENCES A(ID),
NAME VARCHAR2(20)
); 二、视图：
CREATE [or replace] VIEW 视图名 AS 子查询[ WITH READ ONLY | WITH CHECK OPTION];
不占用空间
减少查询的难度 1、创建视图表：
---创建查询emp20部门员工信息的视图表
CREATE or replace VIEW v_emp20 AS SELECT * FROM emp WHERE deptno=40; SELECT * FROM v_emp20;
INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1111,'lisi',7566,3000,20);
INSERT INTO v_emp20(empno,ename,mgr,sal,deptno) VALUES(1112,'zhangsan',7566,3000,10); update v_emp20 set deptno=40; delete from v_emp20; SELECT * FROM emp; 2、with READ ONLY:只读视图
CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH READ ONLY;
SELECT * FROM v_emp30; DELETE FROM v_emp30;
INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10); 3、with CHECK OPTION:条件检查
CREATE OR REPLACE VIEW v_emp30 AS SELECT * FROM emp WHERE deptno=30 WITH CHECK OPTION;
SELECT * FROM v_emp30;
INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,10); ----错误 INSERT INTO v_emp30(empno,ename,mgr,sal,deptno) VALUES(1113,'zhangsan',7566,3000,30); DELETE FROM v_emp30; 4、复杂视图：
（01）视图的子查询中包含distinct,不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT DISTINCT deptno,JOB FROM emp; SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(20,'clerk');
UPDATE v_emp SET deptno=20; （02）视图的子查询中包含rownum，不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT rownum r,empno,ename,sal FROM emp; SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(20,8888,'aaa',2000);
UPDATE v_emp SET sal=20; （03）视图的子查询中包含分组函数，不允许对视图表增删改操作，只能查询
CREATE OR REPLACE VIEW v_emp AS SELECT count(empno) c,sum(sal) s FROM emp; SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(18,20000);
update v_emp set c=20; (04) 视图的子查询中包含了group BY 子句,不允许对视图表进行删除和修改的操作，允许新增和查询
CREATE OR REPLACE VIEW v_emp AS SELECT empno,ename FROM emp group by empno,ename;
SELECT * FROM v_emp;
DELETE FROM v_emp;
INSERT INTO v_emp VALUES(8888,'wangwu');
update v_emp set empno=20; （05）视图的子查询中包含了表达式, 不允许对视图表的表达式列进行增加和修改的操作，允许对非表达式的列进行增删改查
CREATE OR REPLACE VIEW v_emp AS SELECT empno,sal*12 salary FROM emp; SELECT * FROM v_emp;
DELETE FROM v_emp;
ROLLBACK;
INSERT INTO v_emp VALUES(8888,30000);
INSERT INTO v_emp(empno) VALUES(9999); update v_emp set empno=20 where rownum=1; （06）视图的子查询中不包含源表中的非空约束列,不允许对表进行新增，允许修改，删除 和查询
DESC emp;
CREATE OR REPLACE VIEW v_emp AS SELECT ename,sal FROM emp; INSERT INTO v_emp VALUES('aaa',5000); UPDATE v_emp SET sal=3000;
DELETE FROM v_emp;
rollback; 5、删除视图
DROP VIEW 视图名; DROP VIEW v_emp; 三、序列
1、创建序列
CREATE SEQUENCE 序列名
[INCREMENT BY n] ----步长
[START WITH n] ---序列的起始值 序列创建后第一次使用的第一个值
[MINVALUE n] ---序列的最小值 序列从第二次循环取值时的最小值
[MAXVALUE n] ---序列的最大值
[CYCLE] ---序列设置循环取值的标志
[CACHE n] ---序列缓存的个数 ---所有属性都为默认值的序列
CREATE SEQUENCE myseq; ---设置属性为指定值的序列
CREATE SEQUENCE seq1
INCREMENT BY 5
START WITH 10
MINVALUE 5
MAXVALUE 50
CYCLE
CACHE 5; 两个属性：
currval:取序列当前值
nextval:序列的下一个值
语法：序列名.属性名 注意：当一个序列创建成功之后，必须先用nextval生成第一个序列值才可以使用该序列 SELECT myseq.nextval FROM dual; SELECT myseq.currval FROM dual; SELECT seq1.nextval,seq1.currval FROM dual; DROP TABLE A CASCADE CONSTRAINTS;
CREATE TABLE A(
ID NUMBER(5) PRIMARY KEY
); insert into a values(myseq.nextval); select * from a; 2、修改序列
alter SEQUENCE 序列名
[INCREMENT BY n] ----步长
[MINVALUE n] ---序列的最小值 序列从第二次循环取值时的最小值
[MAXVALUE n] ---序列的最大值
[CYCLE] ---序列设置循环取值的标志
[CACHE n] ---序列缓存的个数 3、删除序列
DROP SEQUENCE 序列名; 四、索引
1、创建索引
手动创建:
CREATE INDEX 索引名 ON 表名(列名[,列名...]); CREATE TABLE employee1(
pno NUMBER(7),
pname VARCHAR2(20)
); CREATE INDEX inx_scott_pno ON employee1(pno);
INSERT INTO employee1 SELECT empno,ename FROM emp;
COMMIT; select * from employee1; INSERT INTO employee1 SELECT * FROM employee1; UPDATE employee1 SET pno=ROWNUM; SELECT *
FROM employee1
where pno=99999; SELECT *
FROM employee1
where UPPER(ENAME)='SCOTT'; 函数索引:
CREATE INDEX inx_scott_pname ON employee1(upper(pname)); CREATE INDEX inx_scott_pno_pname ON employee1(pno,pname); 自动创建索引:当创建主键或唯一键时,也会自动创建对应列的索引 2\删除索引:
DROP INDEX 索引名; DROP INDEX inx_scott_pname; select * from user_indexes; 1.使用子查询的方式，创建test表。
create table test99 as select empno,ename,sal,deptno from emp;
2.快速复制test表中的数据，复制到100w条左右
INSERT INTO TEST SELECT * FROM TEST;
DESC test99;
alter table test99 modify(empno number(12)); 五.用户管理
1\创建用户: 管理员有权限创建修改删除用户
CREATE USER 用户名 IDENTIFIED BY 密码; CREATE USER lwy IDENTIFIED BY lwy; 注意:在oracle里,新建的用户对数据库没有任意操作权限 2.赋权限:
赋系统权限: GRANT sys_privs.... TO 用户名| PUBLIC| 角色名 [WITH ADMIN OPTION];
常用的系统权限：
CREATE SESSION:
CREATE TABLE:
CREATE SEQUENCE:
UNLIMITED TABLESPACE; GRANT CREATE SESSION,CREATE TABLE,CREATE SEQUENCE,UNLIMITED TABLESPACE TO lwy WITH ADMIN OPTION; ---用户系统权限的数据字典表
select * from user_sys_privs; 加收系统权限：revoke sys_privs.... FROM 用户名;
revoke create table from lwy; 赋对象权限:GRANT obj_privs.... | all ON 对象名 TO 用户名|PUBLIC|角色名 [WITH GRANT OPTION];
grant select on scott.emp to lwy; grant all on scott.dept to lwy; ---用户对象权限的数据字典表：
select * from user_tab_privs; 加收对象权限：revoke obj_privs.... ON 对象名 FROM 用户名;
REVOKE SELECT ON scott.emp FROM lwy; 3.角色：
DBA:所有权限
RESOURCE:对实体进行操作，不能对结构操作
CONNECT:只能连接 --使用角色来给用户赋权限
GRANT 角色名 TO 用户名; ---将角色所拥有权限赋给用户 ---给一个用户赋所有权限
grant dba to 用户名; 自定义角色：
create role 角色名 not identified; 4.修改用户：
---修改密码：
alter user 用户名 identified by 新密码; ---给用户解锁：
ALTER USER 用户名 ACCOUNT UNLOCK; ---锁定用户
ALTER USER 用户名 ACCOUNT LOCK; 5.删除用户
drop user 用户名 cascade; DROP USER lwy CASCADE; 六、匿名块
1、定义匿名块：
DECLARE
定义部分; ---可选部分
BEGIN
执行部分; ---必选部分
EXCEPTION
异常处理部分; ---可选部分
end; ---往控制台输出hello world
BEGIN
dbms_output.put_line('hello world');
end; ---打开控制台输出开关
set serveroutput on; --定义变量，赋值给变量，输出变量值
DECLARE
v_id NUMBER(3);
BEGIN
v_id:=1;
dbms_output.put_line(v_id);
END; ---查询数据库表中使用into的数据输出到控制台
DECLARE
v_name VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=7788;
dbms_output.put_line(v_name || ',' || v_sal);
end; &:调出输入框接收输入数据。
---查询指定员工的薪水
SELECT * FROM emp WHERE empno=&no; select * from emp where upper(ename)=upper('&name'); DECLARE
v_name VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
dbms_output.put_line(v_name || ',' || v_sal);
END; DECLARE
v_name VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
dbms_output.put_line(v_name || ',' || v_sal);
exception
WHEN no_data_found THEN
dbms_output.put_line('对不起，你输入的用户不存在');
END; %TYPE:使用数据库中某一列的数据类型做为变量的数据类型
语法：表名.列名%type; DECLARE
v_name emp.ename%TYPE;
v_sal emp.sal%type;
BEGIN
SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=&no;
dbms_output.put_line(v_name || ',' || v_sal);
exception
WHEN no_data_found THEN
dbms_output.put_line('对不起，你输入的用户不存在');
END; %rowtype:行类型，使用数据库中某一个表的一行为数据类型
语法：表名%rowtype; DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp WHERE empno=&no;
dbms_output.put_line(v_emp.empno || ',' || v_emp.ename || ',' || v_emp.sal);
end;