笔记081112
2008-11-12 下午6:18 发布人 tiger chang
**********************************************
NAME :CHANGTIGER
DATE :13:37 2008-11-12
EMAIL :o52tiger@yahoo.com.cn
**********************************************
################################## 知识点复习 #########################################
等值链接 from A a , B b where a.id = b.id ;
不等值链接 from A a , B b where a.id between b.aid and b.bid
自链接 from A a , A b where a.id = b.id
外连接
c s
1 1001 1
2 1002
where c.id(+) = s.cid
where c.id = s.cid
组查询
单行函数 single-row
upper(name) round(age) trunc(age) ............
分组函数 group function
count(*) avg(*) min(*) max(*) ..............
group by age ....分组
group by age having ........分组条件过滤
select count(*) ,min(salary) from employee group by age
SELECT COUNT(*)
FROM STUDENT2
WHERE AGE > 20
AND AGE < 30
GROUP BY AGE
HAVING COUNT(*) > 10
ORDER BY AGE;
SELECT TRUNC(AGE/10),COUNT(TRUNC(AGE/10))
FROM STUDENT2
GROUP BY TRUNC(AGE/10);
查看年龄段的人数
子查询
SELECT * FROM A WHERE A.ID IN (SELECT SID FROM B WHERE B.SID > 10);
分页显示的查询
SQL> SELECT ID , SNAME , AGE
2 FROM (
3 SELECT ID , SNAME , AGE , ROWNUM R
4 FROM (SELECT ID , SNAME , AGE FROM STUDENT2 ORDER BY ID) WHERE ROWNUM <= 7)
5 WHERE R > 5;
ROWNUM 只能使用小于号，不能使用大于号，相当于一个伪字段
常用系统函数
1 字符
length,ltrim,replace,rtrim,substr,trim
select length('abcdef') from abc;查询字符数 如本语句结果是6
select length('abc好def') from abc;
select lengthb('abc好def') from abc;查询字节数，为7；
select ltrim(' abc好ef') from abc; 空格
select rtrim('abc好ef ')from abc; 空格
select trim (' abc ')from abc;
select length(a),length(b) from abc;
select substr('abcdefg',2,3) from abc;从第二个开始取串取三个即得 bcd
select substr('abcdefg',1,3) from abc
select substr('abcdefg',length('abcdefg')- 3 + 1,3) from abc;
select sysdate from abc;
2 日期
sysdate，curtent_date,next_day
select sysdate from abc;
select current_date from abc;
alter session set nls_date_format-'dd-mon-yyyy hh:mi:ss';
select next_day(sysdate,'星期三') from abc; 下个星期三是几号，
3 转换
to_char,to_date,to_number
select sysdate from abc; 查看系统日期
select to_char(sysdate,'yyy-mm-dd hh:mi:ss') from abc; 转换成字符
select to_date('12-3月-04') from abc; 将字符串转换成日期
select to_number('333') from abc; 将字符串转换成数字
4聚集函数
sum,avg,max,min,count
select max(price) from book; 求最大值
select min(price) from book; 求最小值
select avg(price) from book; 求平均值
select count(price) from book;
select count(*) from book;查询有几条记录 求数据总个数
select sum(price) from book; 求总数
select * from book where price > 20;
5其他
user,decode,nvl
select user from abc; 查看用户
connect scott/changtiger 转换到用户scott/密码是changtiger
select sum(decode(sex,'男'，1，0)) 男人数,sum(decode(sex,'女',1,0))女人数 from e;
select a1,nvl(a2,'未输入')a2 from aa; 在null的地方写上'未输入'
select * from aa where a2 is null; 为空
select * from aa where a2 is not null; 不为空
select * from aa order by a1 asc; 升序
select * from aa order by a1 desc; 降序
select distinct a1 from aa;没个a1 只显示一个，不重复。
select all a1 frm aa;
实体和关系 ER图
关系类型
一对一关系 one - to - one
多对一关系 many - to - one
多对多关系 many - to - many
SQL> SELECT USER FROM TIGER2; 查看用户的例子
USER
------------------------------
SCOTT
SCOTT
SCOTT
SCOTT
SQL>
################################## 知识点复习 #########################################
################################## 建表前 #########################################
关系类型
ONE TO ONE
一对一的关系
MANY TO MANY
多对多的关系
MANY TO ONE
多对一关系
约束
主键约束（自然主键 和 业务主键） 唯一的，不可为空 可以是数字， 字符串等， 主键一般不可修改
非空约束
外键约束 两个表之间的约束 可以是一对一的，多对一的
注意点
0 表名列名要规范
1 实体和表名对应，多对多的关系和表名对应
2 实体里的属性要和表的列名对应
3 不能使用系统关键字
4 主键 外键的设计
建表范式
第一范式(1NF)first normal form
减少数据冗余 减少完整性问题 在关系R中的每个具体关系，每个属性值都是不可在分的最小数 据单位。all attributes must be single-valued
字段必须是不可在分的最小的数据单位
第二范式 (2NF) Second normal form
如果关系模式R(u,f)中的所有非主属性都完全依赖于任意一个候选关键字，则该 关系属于第二范式
简单的讲就是：就是说每个表有个主键，其他字段完全依赖于该主键。
第三范式(3NF) Third normal form
如果关系模式(u , f)中的所有非主属性对任何候选关键字都不存在传递依赖，则 称关系R属于第三范式
比如有这么一张表，学生id，学生名字，所学专业，该专业办公室
其中学生id，确定了专业，专业确定了专业办公室，则说明有传递依赖，即不满足第三范式
用空间换时间 ----可以有适当的冗余
################################### 建表前 ##########################################
################################# 字符串类型 ######################################
CREATE TABLE
数据类型 字符串类型
char varchar varchar2
1
char The default length for a CHAR column is 1 byte and the maximum
allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10)
column, but the string is blank-padded to 10 bytes before it is stored.
2 varchar
___________________________________________________________________________________
CREATE TABLE TIGER3(
A CHAR(10),
B VARCHAR(10),
C VARCHAR2(10)
);
INSERT INTO TIGER3 VALUES('AAA','AAA','AAA');
SQL> DESC TIGER3;
Name Null? Type
----------------------------------------- -------- ----------------
A CHAR(10)
B VARCHAR2(10)
C VARCHAR2(10)
SQL> INSERT INTO TIGER3 VALUES('AAA','AAA','AAA');
1 row created.
SQL> SELECT * FROM TIGER3;
A B C
---------- ---------- ----------
AAA AAA AAA
SQL> select length(a),length(b),length(c) from tiger3;
LENGTH(A) LENGTH(B) LENGTH(C)
---------- ---------- ----------
10 3 3
查询结果显示对于char类型是一个固定长度的类型，而其他的varchar和varchar2是根据数据的大小来可变的。
varchar 是标准的数据类型在每种数据库中都有的，而varchar2是oracle自己的标准的数据类型
When
you create a VARCHAR2 column, you supply the maximum number of bytes or
characters of data that it can hold. The maximum length of VARCHAR2
data is 4000 bytes
超过4000 bytes就要用大数据类型。
建议使用char类型，其查询效率较高，但浪费存储空间.
################################# 数字类型 ######################################
数字类型
CREATE TABLE TIGER4(
A NUMBER,
B NUMBER(3),
C NUMBER(5,2)
);
INSERT INTO TIGER4(A) VALUES(12345678901234567890123);
INSERT INTO TIGER4(B) VALUES(1.23);
INSERT INTO TIGER4(C) VALUES(1.23222);
number Each NUMBER value requires from 1 to 22 bytes.
NUBER(3) 插入的只有整数部分
NUMBER(5,2) 插入总位数为5的小数点后最多为2位的数据;
SQL> SELECT * FROM TIGER4;
A B C
---------- ---------- ----------
1.2346E+22
1
1.23
自己测试一下INT 类型
################################### 时间类型 ########################################
CREATE TABLE TIGER5(
A DATE
);
INSERT INTO TIGER5 VALUES(SYSDATE);
INSERT INTO TIGER5 VALUES('08-AUG-08'); ---会自动类型转换不用to_date函数，必须是 (日-月-年) 的格式
INSERT INTO TIGER5 VALUES(TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI'));
INSERT INTO TIGER5 VALUES(TO_DATE('98-DEC-25 17:30:23','YY-MON-DD HH24:MI:SS'));
INSERT INTO TIGER5 VALUES(TO_DATE('98-DEC-25 7:30:23 AM','YY-MON-DD HH:MI:SS AM'));
INSERT INTO TIGER5 VALUES(TO_DATE('98-DEC-25 17:30:23','YY-MON-DD HH24:MI:SS'));
INSERT INTO TIGER5 VALUES (TRUNC(SYSDATE));
INSERT INTO TIGER5 VALUES (TO_DATE('3-OCT-2002','DD-MON-YYYY'));
INSERT INTO TIGER5 VALUES (TO_DATE('3-OCT-1999','DD-MON-YYYY'));
INSERT INTO TIGER5 VALUES (TO_DATE('OCT-3-2002','MON-DD-YYYY'));
INSERT INTO TIGER5 VALUES (TO_DATE('OCT/3/2003','MON-DD-YYYY')); 也可以用这样的分割线
INSERT INTO TIGER5 VALUES (TO_DATE('OCT:3:2004','MON-DD-YYYY')); 同上
SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS') FROM TIGER5;
SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS am') FROM TIGER5;
SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS pm') FROM TIGER5;
SELECT TO_CHAR(A,'DD-MM-YYYY HH24:MI:SS') FROM TIGER5;
SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM TIGER5;
SELECT ADD_MONTHS(SYSDATE,4) FROM TIGER5;
SQL> SELECT ROUND(A,'MONTH') FROM TIGER5; 四舍五入
SQL> SELECT ROUND(A,'YEAR') FROM TIGER5;
SQL> SELECT TRUNC(A,'MONTH') FROM TIGER5; 截取
SQL> SELECT TRUNC(A,'YEAR') FROM TIGER5;
ROUND
returns date rounded to the unit specified by the format model fmt. The
value returned is always of datatype DATE, even if you specify a
different datetime datatype for date. If you omit fmt, then date is
rounded to the nearest day. The date expression must resolve to a DATE
value.
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.
SQL> SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS pm') FROM TIGER5 WHERE A > SYSDATE;
SQL> DESC TIGER5;
Name Null? Type
----------------------------------------- -------- -------
A DATE
SQL> INSERT INTO TIGER5 VALUES(SYSDATE);
1 row created.
SQL> SELECT * FROM TIGER5;
A
---------
12-NOV-08
SQL> SELECT LENGTH(A) FROM TIGER5;
LENGTH(A)
----------
9
SQL> SELECT * FROM TIGER5;
A
---------
12-NOV-08
08-AUG-08
12-NOV-08
25-DEC-98
03-OCT-02
03-OCT-99
03-OCT-02
7 rows selected.
SQL> SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS') FROM TIGER5;
TO_CHAR(A,'DD-MM-YY
-------------------
12-11-2008 03:42:55
08-08-2008 12:00:00
12-11-2008 12:00:00
25-12-2098 05:30:00
03-10-2002 12:00:00
03-10-1999 12:00:00
03-10-2002 12:00:00
03-10-2003 12:00:00
03-10-2004 12:00:00
03-10-2004 12:00:00
10 rows selected.
SQL> SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS pm') FROM TIGER5;
TO_CHAR(A,'DD-MM-YYYYH
----------------------
12-11-2008 03:42:55 pm
08-08-2008 12:00:00 am
12-11-2008 12:00:00 am
25-12-2098 05:30:00 pm
03-10-2002 12:00:00 am
03-10-1999 12:00:00 am
03-10-2002 12:00:00 am
03-10-2003 12:00:00 am
03-10-2004 12:00:00 am
03-10-2004 12:00:00 am
25-12-2098 05:30:23 pm
TO_CHAR(A,'DD-MM-YYYYH
----------------------
25-12-2098 07:30:23 am
25-12-2098 05:30:23 pm
13 rows selected.
SQL> SELECT TO_CHAR(A,'DD-MM-YYYY HH24:MI:SS') FROM TIGER5;
TO_CHAR(A,'DD-MM-YY
-------------------
12-11-2008 15:42:55
08-08-2008 00:00:00
12-11-2008 00:00:00
25-12-2098 17:30:00
03-10-2002 00:00:00
03-10-1999 00:00:00
03-10-2002 00:00:00
03-10-2003 00:00:00
03-10-2004 00:00:00
03-10-2004 00:00:00
25-12-2098 17:30:23
TO_CHAR(A,'DD-MM-YY
-------------------
25-12-2098 07:30:23
25-12-2098 17:30:23
13 rows selected.
SQL> SELECT TO_CHAR(A,'DD-MM-YYYY HH:MI:SS pm') FROM TIGER5 WHERE A > SYSDATE;
TO_CHAR(A,'DD-MM-YYYYH
----------------------
25-12-2098 05:30:00 pm
25-12-2098 05:30:23 pm
25-12-2098 07:30:23 am
25-12-2098 05:30:23 pm
################################## 大文件数据类型 #########################################
CLOB 大的文本文件
BLOB 大的二进制文件
CREATE TABLE TIGER6(
A CLOB
);
INSERT INTO TIGER6 VALUES('ASDFASDFASD'); ----------仅供测试插入
CREATE TABLE TIGER7(
A BLOB
);
大数据类型
需要写程序来存储数据
LOB 数据类型
LOB(Large Object) 数据类型存储非结构化数据，比如二进制文件，图形文件，或其他外部文件。
LOB 可以存储到4G字节大小。数据可以存储到数据库中也可以存储到外部数据文件中。LOB数据的
控制通过DBMS_LOB 包实现。BLOB, NCLOB, 和CLOB 数据可以存储到不同的表空间中，BFILE存储在
服务器上的外部文件中。LOB数据类型有以下几种：
BLOB: 二进制数据
CLOB: 字符型数据
BFILE: 二进制文件
其他数据类型
ROWID ROWID 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。
################################# 主键约束 #########################################
CREATE TABLE TIGER8(
ID NUMBER(4) PRIMARY KEY,
NAME CHAR(10)
);
INSERT INTO TIGER8 VALUES(1,'ABC');
INSERT INTO TIGER8 VALUES(2,'ABCD');
INSERT INTO TIGER8 VALUES(3,'ABCE');
INSERT INTO TIGER8 VALUES(3,'ABCDE');
SQL> INSERT INTO TIGER8 VALUES(3,'ABCDE');
INSERT INTO TIGER8 VALUES(3,'ABCDE')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C002746) violated
主键必须是唯一的，不能为空的，建表时用PRIMARY KEY 标识主键
SQL> DESC TIGER8;
Name Null? Type
----------------------------------------- -------- -------------
ID NOT NULL NUMBER(4)
NAME CHAR(10)
ID为主键其 不能为空 NOT NULL
修改一个没有主键的表，给其添加主键
ALTER TABLE CLASS2 ADD CONSTRAINT S_PK1 PRIMARY KEY (ID);
ALTER TABLE STUDENT2 ADD CONSTRAINT S_PK1 PRIMARY KEY (ID);
CREATE TABLE TIGER9(
ID NUMBER(4),
NAME CHAR(10),
CONSTRAINT ID_PK PRIMARY KEY(ID)
);
第二种建立主键的方式；
################################# NOT NULL ##########################################
CREATE TABLE TIGER10(
ID NUMBER NOT NULL,
NAME VARCHAR2(10)
);
INSERT INTO TIGER10 VALUES(1, 'CHANGTIGER');
INSERT INTO TIGER10 VALUES(NULL,'TIGERCHANG')
SQL> INSERT INTO TIGER10 VALUES(NULL,'TIGERCHANG');
INSERT INTO TIGER10 VALUES(NULL,'TIGERCHANG')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TIGER10"."ID")
ALTER TABLE TIGER10 MODIFY ID NULL;
用MODIFY 修改表的属性可以为空
SQL> ALTER TABLE TIGER10 MODIFY ID NULL;
Table altered.
插入数据
SQL> INSERT INTO TIGER10 VALUES(NULL,'TIGERCHANG') ;
1 row created.
查看数据
SQL> SELECT * FROM TIGER10 ;
ID NAME
---------- ----------
1 CHANGTIGER
TIGERCHANG
SQL>
再改回去
由于里面已经有一条可为空的数据，所以不能改，必须修改后才可以；
SQL> ALTER TABLE TIGER10 MODIFY ID NOT NULL;
ALTER TABLE TIGER10 MODIFY ID NOT NULL
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found
修改数据
SQL> UPDATE TIGER10 SET ID = 2 WHERE NAME = 'TIGERCHANG';
1 row updated.
修改回去
SQL> ALTER TABLE TIGER10 MODIFY ID NOT NULL;
Table altered.
查看数据
SQL> SELECT * FROM TIGER10 ;
ID NAME
---------- ----------
1 CHANGTIGER
2 TIGERCHANG
SQL>
################################## UNIQUE #########################################
CREATE TABLE TIGER11(
ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(10) CONSTRAINT S_UNIQUE UNIQUE
);
INSERT INTO TIGER11 VALUES(100,'TIGER');
INSERT INTO TIGER11 VALUES(101,'TIGER');
SQL> INSERT INTO TIGER11 VALUES(101,'TIGER');
INSERT INTO TIGER11 VALUES(101,'TIGER')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.S_UNIQUE) violated
SQL> DESC TIGER11;
Name Null? Type
----------------------------------------- -------- ---------------------
ID NOT NULL NUMBER(3)
NAME VARCHAR2(10)
SQL> SELECT * FROM TIGER11;
ID NAME
---------- ----------
100 TIGER
################################## CHECK #########################################
插入的数据必须在CHECK提供的里面选择
CREATE TABLE TIGER12(
A CHAR,
CONSTRAINT C_K CHECK(A IN ('A','B','C'))
);
INSERT INTO TIGER12 VALUES('A');
INSERT INTO TIGER12 VALUES('B');
INSERT INTO TIGER12 VALUES('D');
SQL> INSERT INTO TIGER12 VALUES('A');
1 row created.
SQL> INSERT INTO TIGER12 VALUES('D');
INSERT INTO TIGER12 VALUES('D')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.C_K) violated
SQL> SELECT * FROM TIGER12;
A
-
A
############################### FOREIGN KEY ############################################
建一张班级表
CREATE TABLE CLASS3(
ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(10) UNIQUE
);
建一张学生表 气所在班级的ID外键到班级表的主键ID,
主表是班级表，从表是学生表
建表时要主要顺序，删除也是要注意顺序
先建主表，后删除主表
CREATE TABLE STUDENT3(
SID NUMBER(3) PRIMARY KEY,
SNAME VARCHAR2(10) NOT NULL,
CID NUMBER(3),
CONSTRAINT FK_SC FOREIGN KEY(CID) REFERENCES CLASS3(ID)
);
INSERT INTO CLASS3 VALUES(100,'JAVA');
INSERT INTO CLASS3 VALUES(101,'C++');
INSERT INTO CLASS3 VALUES(103,'ENGLISH');
INSERT INTO CLASS3 VALUES(104,'GOOGLE');
INSERT INTO STUDENT3 VALUES(001,'TIGER',100);
INSERT INTO STUDENT3 VALUES(002,'TIGER2',101);
INSERT INTO STUDENT3 VALUES(003,'TIGE3',105);
---插入此条数据时由于没有班级105，违反外键约束，不能插入。
SQL> INSERT INTO STUDENT3 VALUES(003,'TIGE3',105);
INSERT INTO STUDENT3 VALUES(003,'TIGE3',105)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_SC) violated - parent key not found
SQL>
表结构展示
SQL> DESC CLASS3;
Name Null? Type
----------------------------------------- -------- ---------------------
ID NOT NULL NUMBER(3)
NAME VARCHAR2(10)
表结构展示
SQL> DESC STUDENT3;
Name Null? Type
----------------------------------------- -------- ----------------
SID NOT NULL NUMBER(3)
SNAME NOT NULL VARCHAR2(10)
CID NUMBER(3)
表中数据
SQL> SELECT * FROM STUDENT3;
SID SNAME CID
---------- ---------- ----------
1 TIGER 100
2 TIGER2 101
表中数据
SQL> SELECT * FROM CLASS3;
ID NAME
---------- ----------
100 JAVA
101 C++
103 ENGLISH
104 GOOGLE
先删除从表中有用到主表主键的数据，在删除主表中的数据
如果从表中有外键约束到主表的数据，则不能删掉，要注意删除顺序 即可。
SQL> DELETE FROM CLASS3 WHERE ID = 100;
DELETE FROM CLASS3 WHERE ID = 100
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_SC) violated - child record found
SQL> SELECT * FROM STUDENT3;
SID SNAME CID
---------- ---------- ----------
1 TIGER 100
2 TIGER2 101
SQL> DELETE FROM STUDENT3 WHERE CID = 100;
1 row deleted.
SQL> DELETE FROM CLASS3 WHERE ID = 100;
1 row deleted.
SQL> SELECT * FROM CLASS3;
ID NAME
---------- ----------
101 C++
103 ENGLISH
104 GOOGLE
###################### 级联删除 ON DELETE CASCADE ###########################
修改原本没有级联的表有级联
ALTER TABLE STUDENT3 DROP CONSTRAINT FK_SC ;
ALTER TABLE STUDENT3 ADD CONSTRAINT FK_SC FOREIGN KEY(CID) REFERENCES CLASS3(ID) ON DELETE CASCADE;
先删除表的外键
在添加外键并有级联这里添加的是DELETE级联
SQL> ALTER TABLE STUDENT3 DROP CONSTRAINT FK_SC ;
Table altered.
SQL> ALTER TABLE STUDENT3 ADD CONSTRAINT FK_SC FOREIGN KEY(CID) REFERENCES CLASS
3(ID) ON DELETE CASCADE;
Table altered.
原本表中的数据
SQL> SELECT * FROM CLASS3;
ID NAME
---------- ----------
101 C++
103 ENGLISH
104 GOOGLE
100 JAVA
SQL> SELECT * FROM STUDENT3;
SID SNAME CID
---------- ---------- ----------
2 TIGER2 101
1 TIGER 100
删除主表中的数据 由于级联关系从表中的数据也别删掉了。
SQL> DELETE FROM CLASS3 WHERE ID = 100;
1 row deleted.
SQL> SELECT * FROM CLASS3;
ID NAME
---------- ----------
101 C++
103 ENGLISH
104 GOOGLE
SQL> SELECT * FROM STUDENT3;
SID SNAME CID
---------- ---------- ----------
2 TIGER2 101
SQL>
######################### ON DELETE SET NULL ##################################
修改原本没有级联的表有级联
ALTER TABLE STUDENT3 DROP CONSTRAINT FK_SC ;
ALTER TABLE STUDENT3 ADD CONSTRAINT FK_SC FOREIGN KEY(CID) REFERENCES CLASS3(ID) ON DELETE SET NULL ;
删除外键
SQL> ALTER TABLE STUDENT3 DROP CONSTRAINT FK_SC ;
Table altered.
添加外键和ON DELETE SET NULL
SQL> ALTER TABLE STUDENT3 ADD CONSTRAINT FK_SC FOREIGN KEY(CID) REFERENCES CLAS
3(ID) ON DELETE SET NULL;
Table altered.
SQL>
删除主表中的数据
SQL> DELETE FROM CLASS3 WHERE ID = 100;
1 row deleted.
看到主表中的数据删掉了，从表中有与ON DELETE SET NULL的关系没有删掉数据 而是将对应数据设为NULL (和级联对比一下)
SQL> SELECT * FROM CLASS3;
ID NAME
---------- ----------
101 C++
103 ENGLISH
104 GOOGLE
SQL> SELECT * FROM STUDENT3;
SID SNAME CID
---------- ---------- ----------
2 TIGER2 101
1 TIGER
SQL>
################################ USER_CONSTRAINTS ###########################################
查看系统表USER_CONSTRAINTS 的结构
DESC USER_CONSTRAINTS;
SQL> DESC USER_CONSTRAINTS;
Name Null? Type
----------------------------------------- -------- ----------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
查看约束的名称 约束的类型 表的名字 。
SQL> SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
S_PK1 P CLASS2
SYS_C002973 P CLASS3
SYS_C002974 U CLASS3
PK_DEPT P DEPT
PK_EMP P EMP
FK_DEPTNO R EMP
EMP_LAST_NAME_NN C EMPLOYEES_DEMO
EMP_EMAIL_NN C EMPLOYEES_DEMO
EMP_HIRE_DATE_NN C EMPLOYEES_DEMO
EMP_JOB_NN C EMPLOYEES_DEMO
EMP_SALARY_NN C EMPLOYEES_DEMO
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
EMP_SALARY_MIN C EMPLOYEES_DEMO
EMP_EMAIL_UK U EMPLOYEES_DEMO
S_PK P LIUML_INFO1
LIUML_S_PK P LIUML_STUDENT
FK_SC R STUDENT3
SYS_C002984 C STUDENT3
SYS_C002985 P STUDENT3
SYS_C003207 P TEST_CLASS
SYS_C003208 U TEST_CLASS
SYS_C002883 P TEST_NULL
SYS_C002732 P TEST_PK
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
LIUML_ID_PK P TEST_PK1
SYS_C003209 C TEST_STUDENT
SYS_C003210 P TEST_STUDENT
LIUML_S_FK R TEST_STUDENT
SYS_C002887 P TEST_UNIQUE
SYS_C002888 U TEST_UNIQUE
SYS_C002923 C TIGER10
SYS_C003065 P TIGER11
S_UNIQUE U TIGER11
C_K C TIGER12
SYS_C002746 P TIGER8
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
ID_PK P TIGER9
SYS_C002739 P T_PK
35 rows selected.
SQL> SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'STUDENT3';
SQL> SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE,TABLE_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'STUDENT3';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
FK_SC R STUDENT3
SYS_C002984 C STUDENT3
R 表示外键
P 表示主键
C 表示NOT NULL & CHECK
U 表示唯一性约束
###########################################################################
MODIFY 只能用来修给非空约束 不能修改外键 只能先删后加。
ALTER TABLE TIGER11 MODIFY NAME NULL;
###########################################################################
课后练习
1） 公司有多少人和提成不为空的人数。
2） 工资大于0的人数。
3） 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
4） 工资大于1500的部门的人数。
5） 各个部门平均工资和人数，按照部门名字升序排列。
6） 是否有员工在同一个部门，而且工资相同，列出这样的部门号和工资、人数。
7） 哪些部门工资高于1000的人数超过2人，列出部门名字。
8） 哪些员工和biri同部门。
9） 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资(降序)。
10）哪些员工的工资，介于32和33部门（33高些）平均工资之间。
11）所在部门平均工资高于1500的员工名字。
12）列出各个部门的ID，以及这个部门的最高工资员工名字和工资。
13）哪个员工是整个公司的最高工资
14）部门平均工资最高的是多少。
15）哪些部门的人数比32号部门的人数多。