《SQL语句练习2》
Oracle系列《三》：表、（约束）索引、序列、视图的使用
一、创建、删除、修改表
建立表：Oracle中主要数据类型 VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)
如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如
SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1==2)
例如创建表Person如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30),
age NUMBER(3),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M'
);
如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令
SQL> ALTER TABLE person ADD(address VARCHAR2(20));
修改表中的列属性
SQL> ALTER TABLE person MODIFY(address VARCHAR2(30));
在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令
在Oracle中提供RENAME命令对表进行重命名
SQL> RENAME person to personer;
在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)
SQL> TRUNCATE TABLE personer;
（与DROP TABLE区别：前者删除内容，后者删除表）
二、表的约束
约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束
添加约束如下：
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(30) NOT NULL,
age NUMBER(3) CHECK(age BETWEEN 0 AND 150),
birthday DATE,
sex VARCHAR(2) DEFAULT 'M' ,
CONSTRAINT person_pid_pk PRIMARY KEY(pid),
CONSTRAINT person_name_uk UNIQUE(name),
CONSTRAINT person_sex_ck CHECK(sex IN('M','F'))
);
以上约束可以采取自动命名和手动命名
现在要再添加一张表，使用主-外键约束
CREATE TABLE book(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
);
这时候如果要删除掉person表的话，就会出现错误，此时可以使用强制性的删除手段
DROP TABLE person CASCADE CONSTRAINT;
但是这种做法一般不用
如果在person 和 book 表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被
book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，
可以使用级联删除的操作
CREATE TABLE book(
bid NUMBER PRIMARY KEY,
bname VARCHAR(20),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
);
添加约束语法如下：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段);
约束类型命名规则：
PRIMARY KEY:主键字段_PK
UNIQUE：字段_UK
CHECK：字段_CK
FOREIGN KEY：父子段_子字段_FK
SQL> ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);
SQL> ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);
SQL> ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);
SQL> ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
ON DELETE CASCADE;
删除约束语法如下：
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;
SQL> ALTER TABLE person DROP CONSTRAINT person_age_CK;
SQL> ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;
ROWNUM伪列
SQL> SELECT ROWNUM,empno,ename FROM emp;
ROWNUM采用自动编号的形式出现
加入只想显示前5条记录，那么ROWNUM<=5
SQL> SELECT ROWNUM,empno,ename FROM emp;
WHERE ROWNUM<=5;
但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式
SQL> SELECT * FROM (SELECT ROWNUM m,empno,ename
FROM emp
WHERE ROWNUM<=10) tmp
WHERE tmp.m>5;
不好的查询思路：
select ROWNUM,empno,job,mgr from emp t where rownum<=10
MINUS
select ROWNUM,empno,job,mgr from emp t where rownum<=5
集合操作：在Oracle中提供了3类集合操作:并、交、差
UNION：将多个查询结果组合到一个查询结果之中，没有重复值
UNION ALL：与UNICON不同的是包含重复值
INTERSECT：返回多个查询结果中相同的部分
MINUS：返回两个查询结果的差集
验证UNION和UNION ALL
SQL> CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)
SQL> SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录)
SQL> SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)
验证MINUS 和 INTERSECT
SQL> SELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录)
SQL> SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)
案例：
主键约束添加删除
1、创建表的同时创建主键约束
一、无命名 create table accounts ( accounts_number number primary key, accounts_balance number );
二、有命名 create table accounts ( accounts_number number primary key, accounts_balance number, constraint yy primary key(accounts_number) );
2、删除表中已有的主键约束
一、无命名 SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='accounts';
找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;
二、有命名 ALTER TABLE ACCOUNTS DROP CONTRAINT yy;
3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);
Oracle中视图的操作
1、创建视图
CREATE VIEW 视图名称 AS 子查询
这条子查询是非常复杂的语句
SQL> CREATE VIEW empv20 AS
SELECT empno,ename,job,hiredate
FROM emp
WHERE deptno=20;
2、查询视图
SQL> SELECT * FROM empv20;
3、删除视图
SQL> DROP VIEW empv20;
如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令
CREATE OR REPLACE 视图名称 AS 子查询
视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(
SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) min
FROM emp
GROUP BY deptno) ed,emp e
WHERE d.deptno=ed.deptno AND e.sal=e.min;
在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图，以上红色部门
如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作
SQL> UPDATE empv20 SET deptno=30 WHERE empno=7369;
发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的
SQL提供了两个重要的参数
WITH CHECK OPTION：不能更新视图的创建条件
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION;
创建条件不能进行更新了，但其他字段仍然可以更新
SQL> UPDATE empv20 SET ename='wilson' WHERE empno=7369;
所以这时可以使用视图的第2个条件：创建只读视图
SQL> CREATE OR REPLACE VIEW empv20 AS
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY;
Oracle系列《四》：数据库的设计分析
一、序列的使用
在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成
序列的创建格式
CREATE SEQUENCE sequence
[INCREMENT BY n][START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]
创建一个myseq的序列
SQL> CREATE SEQUENCE myseq;
创建完该序列之后，所有的自动增长应该由用户自己处理
nextVal:取得序列的下一个内容
currVal:取得序列的当前内容
建立一张表验证序列
CREATE TABLE testseq(
next NUMBER,
curr NUMBER,
);
SQL> INSERT INTO testseq(next,curr) VALUES(myseq.nextval,myseq.currval);
将这条SQL执行5次，然后进行查表操作
SQL> SELECT * FROM testseq;
可以发现,nextval的内容始终在自动增长，而curr使用取出当前操作的序列的结果，该序列增长幅度为1
如果要进行修改，则加上 INCREMENT BY 长度的语句
SQL> DROP SEQUENCE myseq;
SQL> CREATE SEQUENCE myseq INCREMENT BY 2;
发现每次取出的结果都是奇数 1,3,5,7,9，序列是从1开始的，我们可以指定序列的开始位置，例如
SQL> CREATE SEUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 2 CACHE 2 CYCLE;
二、同义词的概念(了解)
SQL> SELECT SYSDATE FROM dual;
dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到
SQL> SELECT * FROM tab WHERE TNAME='DUAL';
此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用"用户名.表名称"
该情况就是同义词的作用
创建同义词：
CREATE SYSNONYM 同义词名称 FOR 用户名.表名称';
例如，将scott.emp 定义 emp 的同义词
SQL> CREATE SYSNONYM emp FOR scott.emp;
删除同义词
SQL> DROP SYSNONYM emp;
同义词这种特性只适合于Oracle数据库
三、用户管理(*)
创建用户: CREATE USER 用户名 IDENTIFIED BY 密码;
SQL> CREATE USER test IDENTIFIED BY test123;
打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权
SQL> GRANT CREATE SESSION TO test;
将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限
Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可
在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户
SQL> GRANT CONNECT,RESOURCE TO test;
管理员对用户密码进行修改：
SQL> ALTER USER test IDENTIFIED BY hello;
在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式
ALTER USER 用户名 PASSWORD EXPIRE;
SQL> ALTER USER test PASSWORD EXPIRE;
这时会提示用户输入旧口令及新的密码
锁住用户和对用户解锁
SQL> ALTER USER test ACCOUNT LOCK;
SQL> ALTER USER test ACCOUNT UNLOCK;
此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可
SQL> GRANT SELECT,DELETE ON scott.emp TO test;
收回权限的命令：
SQL> REVOKE SELECT,DELETE ON scott.emp FROM test;
数据库的备份与恢复
数据库备份：exp
数据库恢复：imp
嵌套表的概念(了解)
嵌套表：在一个表中还包含另外一个子表
首先为嵌套表指定类型，该类型需要单独定义
SQL> CREATE TYPE project_ty AS OBJECT(
priod NUMBER(4),
proname VARCHAR2(30),
prodate DATE
);
/
该类型创建成后，不意味着此类型能够直接使用，要为此类型指定一个名称
SQL> CREATE TYPE project_nt AS TABLE OF project_ty
/
这样就可以使用project_nt表示project_ty类型，现在根据此类型创建department表
SQL> CREATE TABLE department(
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(50) NOT NULL,
projects project_nt
)NESTED TABLE projects STORE AS project_nt_tab_temp;
如果要进行数据插入的话
SQL> INSERT INTO department(deptno,dname,projects) VALUES(
1,'tech',
project_nt(
project_ty(1001,'ERP',SYSDATE),
project_ty(1002,'CRM',SYSDATE)
)
);
查询部门表，可以返回多个项目
SQL> SELECT * FROM department;
如果需要查看一个部门的全部项目的话，查询嵌套表
SQL> SELECT * FROM TABLE
(SELECT projects FROM department WHERE deptno=1);
更新编号为1001的项目名称
SQL> UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro
SET VALUES(pro)=project_ty('1001','APR',SYSDATE) WHERE pro.proid=1001;
可变数组的概念：是嵌套表的升级版 ... (有用到再了解)
数据库范式的概念
第一范式：所有的信息都集中在一张表上，例如
CREATE TABLE person(
pid NUMBER(4) PRIMARY KEY,
name VARCHAR2(50),
info VARCHAR(200)
);
第一范式会出现问题，例如创建一张学生选课表
CREATE TABLE selectCourse(
stuno VARCHAR2(50),
stuname VARCHAR2(50),
stuage NUMBER,
cname VARCHAR2(50),
grade NUMBER,
credit NUMBER
);
以上不仅所有的课程信息冗余了，而且还存在以下的问题：
1、没有学生选该门课，那么该门课就消失了
2、课程本身有编号，按照以上设计，课程编号肯定重复
3、要更改课程信息，则要修改许多记录
使用第二范式进行修改
CREATE TABLE student(
stuno VARCHAR2(10) PRIMARY KEY,
stuname VARCHAR2(20),
stuage NUMBER
);
CREATE TABLE course(
cid NUMBER(5) PRIMARY KEY,
cname VARCHAR2(20),
credit NUMBER
);
CREATE TABLE selectCourse(
stuno VARCHAR2(50),
cid NUMBER(5),
grade NUMBER,
设置主-外键关系
);
以上设计解决了以下问题：
1、学生不选课，课程不会消失
2、更新课程的时候直接更新课程表
3、所有关联关系在关系表中体现
这里是完成了多-多关系
使用第三范式：
按照第二范式的设计一张学生表，包括学号、姓名、年龄、所在院校、学院地址、学院电话等
会出现一个学生同时在多个学院同时上课，正常应该是：一个学院包含多个学生，一个学生属于一个学院C
CREATE TABLE collage(
cid NUMBER(40) PRIMARY KEY,
cname VARCHAR2(50),
caddress VARCHAR2(20),
ctel VARCHAR2(20)
);
CREATE TABLE student(
stuno VARCHAR2(50) PRIMARY KEY,
stuname VARCHAR2(50),
stuage NUMBER,
cid NUMBER(4),
建立主-外键关联
);
以上是很明确的1对多的关系
Oracle系列《五》：SQL综合练习
【1】列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
<1>按工作分组，分组条件最低工资大于1500
SQL> SELECT job,MIN(sal) FROM emp
GROUP BY job
HAVING MIN(sal)>1500;
<2>根据求出来的工作来求全部的雇员工作
SQL> SELECT e.job,COUNT(e.empno) FROM emp e
WHERE e.job IN(SELECT job FROM emp
GROUP BY job
HAVING MIN(sal) > 1500)
GROUP BY e.job;
【2】列出在部门'SALES'工作的员工姓名
<1>先查询SALES的部门编号
SQL> SELECT deptno FROM dept WHERE dname='SALES';
<2>SELECT ename FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
【3】列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
<1>求出公司平均薪金
SQL> SELECT AVG(sal) FROM emp;
<2>列出薪金高于平均薪金的所有员工
SQL> SELECT * FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);
<3>查询所在部门信息
SQL> SELECT e.*,d.dname,d.loc FROM emp e,dept d
WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno;
<4>查询上级领导
SQL> e.empno,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr = m.empno(+);
<5>求出工资的工资等级
SQL> SELECT e.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m,salgrade s
WHERE e.sal(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+)
AND e.sal BETWEEN s.losal AND s.hisal;
【4】列出与"SCOTT"从事相同工作的所有员工及部门名称
<1>找出与SCOTT相同工作的雇员，但不能包括自己
SQL> SELECT empno,ename,job FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
<2>与部门表关联，查询部门名称
SQL> SELECT e.empno,e.ename,e.job,d.dname
FROM emp e,dept d
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
AND e.deptno=d.deptno;
【5】列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
<1>列出部门30员工的薪金
SQL> SELECT sal FROM emp WHERE deptno=30;
<2>上述条件作为子查询，这里注意上述结果是返回多条记录的，所以要用IN
SQL> SELECT ename,sal FROM emp
WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;
【6】列出在每个部门工作的员工数量、平均工资和平均服务期限
<1>每个部门的员工数量：可求出部门名称
SQL> SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno;
GROUP BY d.dname;
<2>求平均工资和服务年限
SQL> SELECT d.dname,COUNT(e.empno),AVG(e.sal),AVG(MONTH_BETWEENS(sysdate,hiredate)/12) year
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
【7】列出所有部门的详细信息和部门人数
<1>列出所有部门人数
SQL> SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;
<2>将以上查询当做一张临时表
SQL> SELECT d.*,ed.cou
FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp
GROUP BY deptno) ed
WHERE d.deptno = ed.deptno;
但是以上没有40部门的信息，则应该使用NVL和左连接操作
SQL> SELECT d.*,NVL(ed.cou,0)
FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp
GROUP BY deptno) ed
WHERE d.deptno = ed.deptno(+);
【8】列出各种工作的最低工资及从事该工作的雇员姓名
<1>按照工作分组，使用MIN函数求出最低工资
SQL> SELECT job,MIN(sal) FROM emp
GROUP BY job;
<2>按照工资查询雇员信息
SQL> SELECT * FROM emp
WHERE sal IN(SELECT MIN(sal) FROM emp
GROUP BY job);
【9】列出各个部门 MANAGER的最低薪金
SQL> SELECT deptno,MIN(sal)
FROM emp
WHERE job='MANAGER'
GROUP BY deptno;
【10】列出所有员工的年工资，按年薪从低到高排序
SQL> SELECT ename,(sal+NVL(comm,0))*12 income
FROM emp ORDERY BY income;
【11】求出部门名称中，带'S'字符的部门员工，工资合计，部门人数
<1>使用模糊查询，获得部门编号
SQL> SELECT deptno FROM dept WHERE dname LIKE '%S%';
<2>上面作为子查询
SQL> SELECT deptno,SUM(sal),COUNT(empno)
FROM emp
WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%'
GROUP BY deptno;
【12】给任职10年以上的人加薪10%
SQL> UPDATE emp SET sal=sal+(sal*0.1)
WHERE MONTH_BETWEENS(sysdate,hiredate)/12>10;
【综合题】有个学生运动会比赛信息的数据库，需要建立如下的表，结构如下
运动员sporter: (运动员编号 sporterid,运动员姓名 name,运动员性别 sex,所属系号 department)
项目item: (项目编号itemid,项目名称itemname,项目比赛地点 location)
成绩grade: (运动员编号 sporterid,项目编号itemid,积分mark)
1、建表要求
<1>定义各个表的主外键约束
<2>运动员姓名和所属系别不能为空
<3>积分要么控制，要么为6,4,2,0,
CREATE TABLE sporter(
sporterid NUMBER(4) PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
sex VARCHAR2(2) NOT NULL,
department VARCHAR2(20) NOT NULL,
CONSTRAINT sporter_sex_CK CHECK(sex IN('M','F'))
);
CREATE TABLE item(
itemid VARCHAR2(4)PRIMARY KEY,
itemname VARCHAR2(20) NOT NULL,
location VARCHAR2(20) NOT NULL
);
CREATE TABLE grade(
sporterid NUMBER(4),
itemid VARCHAR2(20),
mark NUMBER(2),
CONSTRAINT sporter_grade_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
CONSTRAINT item_grade_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
CONSTRAINT grade_mark_CK CHECK(mark IN(6,4,2,0))
);
记录可自己视情况插入，完成以上的查询语句
1、求出目前总积分最高的系名，及其积分
SQL> SELECT s.department,SUM(g.mark) sum
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department
ORDER BY sum DESC;
当然上述查出来的结果是排序的多条记录，使用ROWNUM最为简便
SQL> SELECT * FROM (
SELECT s.department,SUM(g.mark) sum
FROM sporter s,grade g
where s.sporterid = g.sporterid
GROUP BY s.department
ORDER BY sum DESC)
WHERE ROWNUM=1;
2、找出场地为'S1'，进行比赛的各项目名称及其冠军的姓名
<1>首先确定一操场中的全部项目和每个项目的最高成绩
SQL> SELECT i.itemname,s.name,g.mark
FROM item i,grade g,sporter s
WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid = g.sporterid;
<2>根据上述结果求出最高分
SQL> SELECT i.item,s.name,g.mark
FROM item i,grade g,sporter s
WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid AND g.mark=6;
3、找出参加了wilson所参加过的项目的其他同学的姓名
<1>找到wilson参加过的项目编号
SQL> SELECT g.itemid FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name='wislon';
<2>SELECT DISTINCT s.name
FROM sporter s, grade g
WHERE s.sporterid=g.sporterid AND s.name<>'wilson';
AND g.itemid IN(SELECT g.itemid FROM sporter s,grade g
WHERE s.sporterid=g.sporterid AND s.name='wilson');
4、wilson使用了违禁药物，成绩记为0
SQL> UPDATE grade SET makr=0
WHERE sporterid=(SELECT sporterid FROM sporter WHERE name='wilson');
5、删除S2项目
SQL> DELETE FROM item WHERE itemid='S2'
