http://www.verejava.com/?id=1717412284582
1 . 约束条件
Oracle数据库的约束包括:
NOT NULL 非空约束
UNIQUE 唯一键约束
PRIMARY KEY 主键约束 ----重点
FOREIGN KEY 外键约束 ----重点
1. NOT NULL 如果一个字段有非空约束插入数据时该字段必须给定一个值
CREATE TABLE student
(
id int(10) NOT NULL,
name VARCHAR(30) NOT NULL
);
//插入值判断是否合法
INSERT INTO student(id,name) VALUES(1,'张见');
INSERT INTO student(id,name) VALUES(2,NULL);
UPDATE student SET id=NULL WHERE id=1;
2. PRIMARY KEY 主键约束(参照完整性约束)
主键是唯一标识表中的某一行记录, 相当于非空且唯一
1 . 单主键
DROP TABLE student;
//表级约束
CREATE TABLE student
(
id int(10) ,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
//插入值判断是否合法
INSERT INTO student(id,name) VALUES(1,'张见');
INSERT INTO student(id,name) VALUES(2,'张见');
INSERT INTO student(id,name) VALUES(1,'张见');
INSERT INTO student(id,name) VALUES(NULL,'张见');
1 . 联合主键
联合唯一键的组合(id+subject)不能重复
CREATE TABLE scores
(
id int(10),
subject VARCHAR(30),
score int(3),
PRIMARY KEY (id,subject)
);
//插入值
INSERT INTO scores(id,subject,score) VALUES(1,'语文',90);
INSERT INTO scores(id,subject,score) VALUES(1,'数学',90);
INSERT INTO scores(id,subject,score) VALUES(2,'语文',90);
//不合法
INSERT INTO scores(id,subject,score) VALUES(2,'语文',90);
3. FOREIGN KEY 外键约束
外键约束 确保两个字段之间的参照完整性.
外键约束的特性:
1. 子表外键字段的值必须在主表参照字段的范围内, 或者为空值 NULL
2. 外键参照的必须是主表的主键或者是唯一键
3. 主表主键或唯一键被子表参照时, 主表对应的记录不允许删除.
4. 创建数据表时, 先创建主表, 然后再创建参照他的子表, 删除表时正好相反
5. 添加数据时. 先向主表添加记录, 然后再向子表添加记录, 删除正好相反
6. 实际开发中也在也有可能应用层控制参照完整性, 有利于数据移植
例如:
1. 1:1 的关联表 创建一个员工信息表:
CREATE TABLE emp
(
id int(10) ,
name VARCHAR(30),
job VARCHAR(30),
PRIMARY KEY (id)
);
创建一员工工资表:
CREATE TABLE salary
(
id int(10) PRIMARY KEY,
emp_id int(10),
salary float,
FOREIGN KEY (emp_id) REFERENCES emp(id)
);
向员工表 emp 插入数据
INSERT INTO emp(id,name,job)VALUES(1,'张浩','程序员');
INSERT INTO emp(id,name,job)VALUES(2,'李洁','测试');
向员工工资表插入数据
//合法
INSERT INTO salary(id,emp_id,salary)VALUES(1,1,5000);
INSERT INTO salary(id,emp_id,salary)VALUES(2,NULL,5000);
DELETE FROM emp WHERE id=2;
//非法
INSERT INTO salary(id,emp_id,salary)VALUES(3,3,5000);
DELETE FROM emp WHERE id=1;
DROP TABLE emp;
2. 两个表的关联查询
查出所有员工的和对应的工资信息
SELECT a.name ,b.salary FROM emp a,salary b WHERE a.id=b.emp_id ;
3. 1:多 查询所有员工,显示出部门名称
创建一个部门表
CREATE TABLE dept
(
id int(10) ,
name VARCHAR(10),
PRIMARY KEY (id)
);
向部门表插入数据
INSERT INTO dept(id,name)VALUES(1,'IT');
INSERT INTO dept(id,name)VALUES(2,'财务');
创建一个员工表
CREATE TABLE emp
(
id int(10),
name VARCHAR(10),
salary int(10),
dept_id int(10),
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
向员工表插入数据
INSERT INTO emp(id,name,salary,dept_id)VALUES(1,'王浩',6000,1);
INSERT INTO emp(id,name,salary,dept_id)VALUES(2,'李洁',5000,1);
INSERT INTO emp(id,name,salary,dept_id)VALUES(3,'张强',6000,2);
INSERT INTO emp(id,name,salary,dept_id)VALUES(4,'张涛',8000,2);
查询所有员工,显示出部门名称
SELECT a.name ,b.* FROM dept a,emp b WHERE a.id=b.dept_id ;
4. 多:多 表连接
错误的创建多表关联
创建一个老师表
CREATE TABLE teacher
(
id int(10) PRIMARY KEY,
name VARCHAR(20),
student_id int(10),
FOREIGN KEY (student_id) REFERENCES student(id)
);
创建一个学生表
CREATE TABLE student
(
id int(10) ,
name VARCHAR(20),
teacher_id int(10),
PRIMARY KEY (id),
FOREIGN KEY (teacher_id) REFERENCES teacher(id)
);
/
解决办法: 关联表技术
创建一个老师表
CREATE TABLE teacher
(
id int(10),
name VARCHAR(20),
PRIMARY KEY (id)
);
插入数据
INSERT INTO teacher(id,name)VALUES(1,'李老师');
INSERT INTO teacher(id,name)VALUES(2,'张老师');
INSERT INTO teacher(id,name)VALUES(3,'马老师');
创建一个学生表
CREATE TABLE student
(
id int(10) PRIMARY KEY,
name VARCHAR(20),
PRIMARY KEY (id)
);
INSERT INTO student(id,name)VALUES(1,'李洁');
INSERT INTO student(id,name)VALUES(2,'张涛');
INSERT INTO student(id,name)VALUES(3,'王军');
INSERT INTO student(id,name)VALUES(4,'李密');
创建 老师和学生的关联表
CREATE TABLE teacher_student
(
teacher_id int(10),
student_id int(10),
FOREIGN KEY (teacher_id) REFERENCES teacher(id),
FOREIGN KEY (student_id) REFERENCES student(id)
);
插入关联数据
INSERT INTO teacher_student(teacher_id,student_id)VALUES(1,1);
INSERT INTO teacher_student(teacher_id,student_id)VALUES(1,2);
INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,2);
INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,3);
INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,4);
查找出 李老师 教的所有学生信息
SELECT a.name teacher,c.name student FROM teacher a, teacher_student b,student c WHERE a.id=b.teacher_id AND b.student_id=c.id AND a.id=1;
查找出教过 张涛 的所有老师信息
SELECT a.name teacher,c.name student FROM teacher a, teacher_student b,student c WHERE a.id=b.teacher_id AND b.student_id=c.id AND c.id=2;