java mysql 外键_java mysql外键约束 数据库之间关系 三大范式 多表查询

外键约束

? 在新表中添加外键约束语法:coustraint 外键约束名称 foreign key(外键的字段名称) references 主表的表名(主键字段名)

? 在已有表中添加主键外键约束:alter table 从表表名 add coustraints 外键约束名称 foreign key(外键的字段名称) references 主表的表名(主键字段名)

操作

-- 外键约束

-- 课程表

drop table course;

create table course (

c_id int primary key auto_increment,

c_name varchar(5)

);

-- 学生表  选课 外键约束添加在从表中  被约束的那一方

drop table student;

create table student(

s_id int primary key,

s_name varchar(10) not null,

s_c_id int,

-- 给s_c_id 添加外键约束

CONSTRAINT stu_cour_id FOREIGN KEY(s_c_id) REFERENCES course(c_id)

)

-- 往两张表中植入数据

insert into course VALUES(null,'数学'),(null,'英语'),(null,'历史'),(null,'物理');

insert into student VALUES(1,'小孙',1),(2,'小王',2),(3,'小刘',4);

-- 查询两张表的数据

select * from course;

select * from student;

-- 插入非法数据

-- 插入不存在的课程

-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`java31`.`student`, CONSTRAINT `stu_cour_id` FOREIGN KEY (`s_c_id`) REFERENCES `course` (`c_id`))

-- 因为从表中的s_c_id 被主表中的s_id约束

insert into student VALUES(4,'小丽',5);

删除外键语法: alter table 从表名 drop froeign key 外键名称;

级联操作:

注意:

在从表中,修改关联主表中不存在的数据,是不合法的

在主表中,删除从表中已经存在的主表信息,是不合法的.直接删除主表(从表中有记录数据关联)会报删除失败

概念: 在修改或者删除主表的主键时,同时他会更新或者删除从表中的外键值,这种动作我们称之为级联操作

语法:

更新级联 on update cascade 级联更新 只能时创建表的时候创建级联关系.当更新主表中的主键,从表中的外键字段会同步更新

删除级联 on delete cascade 级联删除 当删除主表中的主键时,从表中的含有该字段的记录纸会同步删除

操作:

-- 给从表student添加级联操作

create table student(

s_id int primary key,

s_name varchar(10) not null,

s_c_id int,

-- 给s_c_id 添加外键约束

CONSTRAINT stu_cour_id FOREIGN KEY(s_c_id) REFERENCES course(c_id)

on update cascade on delete cascade

)

insert into student VALUES(1,'小孙',1),(2,'小王',2),(3,'小刘',4);

insert into student VALUES(1,'小司马',1),(2,'小赵',1),(3,'小钱',1);

-- 查询学生表中的记录

select * from student;

-- 级联更新

-- 更新级联

update course set c_id = 10 where c_id = 1;

-- 删除级联

delete from course where c_id = 10;

约束总结

约束名    关键字    描述

主键    primary key    唯一不为空

默认    default    插入数据该字段没有赋值,系统会自动赋指定的默认值

非空    not null    该字段不能为null(空)

唯一约束    unique    该字段的值在整个表中只能出现1次

外键    foreign key    从表中添加外键,关联主表中的主键字段

表与表之间的关系

可以分成三类:

○ 一对一的关系: 一般情况下,一对一的关系基本不用,当发现两张表是一对一的关系,合成一张表.例如:人和身份证号,学生和学生证号

○ 一对多的关系:在表关系中最多的情况,也是最常见的,比如,部门和员工

○ 多对多的关系:从两个方向观察发现都是1-n的关系,比如:学生表和课程1表,企业和应聘者, 双选会

操作:

学生表,课程表,多对多关系

用户表,角色表 一对多关系

-- 创建学生表

use java31;

drop table student;

create table student(

s_id int primary key auto_increment,-- 主键约束

s_name  VARCHAR(10) NOT NULL,-- 非空约束

s_num INT UNIQUE NOT NULL-- 唯一约束,非空约束

);

-- 创建课程表

drop table course;

create table course(

c_id int primary key auto_increment comment '课程id', -- 主键约束

c_name VARCHAR(10) NOT NULL UNIQUE COMMENT '课程名称' -- 非空唯一约束

)

drop table t_stu_cour;

-- 中间表

create table t_stu_cour(

s_id INT,

c_id INT,

CONSTRAINT stu_id FOREIGN KEY(s_id) REFERENCES student (s_id),

CONSTRAINT cour_id FOREIGN KEY(c_id) REFERENCES course (c_id)

)

-- 创建用户表

drop table user;

create table user (

u_id int primary key auto_increment comment '用户表主键id', -- 主键约束

u_name varchar(10) not null, -- 非空约束

u_phone varchar(11) unique, -- 唯一约束

u_rid int, -- 需要添加的外键信息

CONSTRAINT user_rid foreign key (u_rid) REFERENCES role(r_id)

);

-- 创建角色表

drop table role;

create table role(

r_id int primary key auto_increment comment '角色表主键id', -- 主键约束

r_name varchar(10) not null unique, -- 唯一约束 非空约束

r_desc varchar(255)

);

表与表之间的总结

表之间的关系    关系维护,创建

一对一    合表,互为外键约束,表之间关系很少

一对多    在从表(多的那一方)的那一方创建外键,关联主表的主键字段,先创建主表,再创建从表

多对多    创建中间表,中间表分别添加外键约束关联各自对应的主键

数据库设计的范式

? 什么是范式?

在设计数据库的时候,需要遵从的规范要求,根据这些规范要求设计出合理的数据库.这些规范被称为范式

这些范式针对的是关系型数据库.

目前关系型数据库的范式有六种:第一范式(1NF),第二范式(2NF),第三范式(3NF),第四范式(4NF),第五范式(完美范式)(5NF),巴斯-科德范式(BCNF)

各种范式呈递次规范,越高的范式数据库的冗余性就越低.

? 前三个范式介绍

○ 第一范式(1NF);数据库中的每一列是不可分割的原子数据项.

○ 第二范式(2NF);在第一范式的基础上,非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对于码的部分函数依赖)??

§ 概念:

1.函数依赖:A-->B 如果通过A属性(属性组)的值,可以确定唯一的B属性值,可以称B依赖于A

2.完全函数依赖:A-B 如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值

3.部分函数依赖 A-B 如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一个或某一些属性即可

4.传递函数依赖 A-B B-C 如果通过A属性(属性组)的值,可以唯一确定B属性的值,再通过B属性的值,可以唯一确定C属性的值,

可以称C传递函数依赖于A

5.码:如果在一张表中,一个属性或者属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码??????

□ 主属性:码属性组中的所有属性

□ 非主属性:除主属性之外的其他属性

○ 第三范式(3NF):在第二范式的基础上,任何飞猪属性不依赖与其他的非主属性(在2NF基础上,消除传递函数依赖)

○ 三大范式总结

范式    特征

1NF    表中每一列具有原子性,表中的每一列不可分割

2NF    消除部分函数依赖,一张表只做一件事

3NF    消除传递函数依赖,表中的每一列都直接依赖于码(主键),不需要其他的字段(列)间接依赖于主键

多表连接查询

? 分类:内连接(显式内连接,隐式内连接),外连接(左外连接,右外连接)

? 笛卡尔积现象:

○ 左表中的每条记录和右表中的每条记录全关联组合,这种效果就称为笛卡尔积现象

? 消除笛卡尔积现象:

○ 添加条件过滤 ,使用where条件语句,达到过滤掉无效的数据.

? 内连接:inner join

○ 隐式内连接: 省略掉内连接关键字 inner join

§ 语法:select 字段列表 from 表民1,表名2,… where 条件语句

§ 操作:select * from department,employee where d_id = e_did;

○ 显式内连接:使用内连接关键字 inner join … on语句 inner可以省略

§ 语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件语句

§ 操作:select * from department inner join employ on d_id = e_did;

○ 总结

1.查询哪些表

2.确定表关联的条件

3.使用连接的方式

4.确定查询的字段信息,尽量少用*

? 外连接

○ 左外连接:使用left [outer] join … on 条件语句   outer 关键字可以省略

§ 语法:select 字段列表 from 左表(主表) left [outer] join 右表(从表/辅表) on 条件语句

§ 注意事项:用左表中的记录数据去匹配右表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null.保证左表中的数据全部显示

§ 操作:select d.*,e.e_username from department as d left outer join employee as e on e.e_did=d.d_id;

○ 右外连接:使用right [outer] join … on 条件语句    outer关键字可以省略

§ 语法:select 字段列表 from 右表(主表) right [outer] join 左表(从表/辅表) on 条件语句.

§ 注意事项:用右表中的记录数据去匹配左表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null.保证右表中的数据全部显示

§ select * from employ right outer join departement on e_did = d_id right outer join salary on e_id = s_eid

子查询:

? 什么是子查询?

? 概念:一个查询的结果是另一个查询的条件,形成查询嵌套,里面查询称之为子查询,一定要出现小括号.

? 子查询有三种情况:

○ 子查询的结果可以是单行单列,只有一个字段,这一个字段只有一个值

○ 也可以是多行单列,只有1个字段,这个字段有多个值

○ 还可以是多行多列,有多个字段,多个字段分别有多个值

○ 操作:

§ 第一种情况;单行单列

□ 语法: select 查询字段列表 from 表名 where 字段 比较运算符(子查询)

□ 特征:我们可以在where后面使用比较运算符

§ 第二种情况:多行单列

□ 语法:select 查询字段列表 from 表名 where 字段 in(子查询)

□ 特征:结果值是一个集合或者一个数组,父查询使用in运算符

□ 操作:

SELECT d_name from department WHERE d_id in(

select

DISTINCT e_did

FROM

employee

where

age > (

select avg(age) from employee -- 30

)-- 2 3 4

);

§ 第三种情况:多行多列,一般情况下我们可以作为一张虚拟表,进行关联二次查询,一般需要给这个虚拟表起一个别名来实现

□ 语法:select 查询字段列表 from 表名 ,(子查询) as 新表名 where 条件语句;

□ 特征:多行多列不能再使用in运算符或者是比较运算符,而是需要进行多表关联,给查询出来的多行多列起别名.

? 子查询总结:

○ 单行单列:只有一个值,在where后面可以使用比较运算符,作为条件

○ 多行单列:是一个集合值或者数组值,在where后面使用的是in运算符,作为条件

○ 多行多列:大多数多列结果值是放在from后面的,作为多表关联的.可以进行二次条件过滤

事务:

? 什么是事务:

一个业务操作中,这个操作,要么完全执行成功,要么被撤销掉.这个业务操作是一个整体操作,在这个整体中所有的sql语句要么全部执行成功

要么被回滚(业务执行失败).

? 操作:张三给李四转账10000,张三的账户剪掉1万块 利斯的账户增加1万块

-- 创建账户表account表()

create table account(

id int primary key auto_increment,-- 主键id

username VARCHAR(10) not null,-- 账户

balance double -- 账户余额

)

-- 插入两条数据

insert into account values(null,'张三',20000),(null,'李四',20000);

-- 张三给李四转账10000块钱

-- 先让张三的钱-10000

update account set balance = balance - 10000 where username = '张三';

-- 添加一条语句

update account set balance = balance - 10000 username = '张三';

-- 再让李四的钱+10000

update account set balance = balance + 10000 where username = '李四';

-- 还原数据

update account set balance = 20000;

-- 查询账户表

select * from account;

? 手动操作事务:

○ 三个动作:

§ 开启事务:start transaction;

§ 提交事务:commit;

§ 回滚事务:rollback;

? 事务的四大特性:

1.原子性:这个事务是一个整体,在这个整体中,是不可分割的,在事务中所有的sql语句要么完全执行成功,要么都失败

2.一致性:事务在执行前和执行后数据库中的数据状态是一致的.转账:张三和李四转帐前余额都是20000,转账后,如果成功:张三是10000,李四是30000

如果失败:张三是20000,李四也是20000.

3.隔离性:事务与事务之间是互不影响的,在多个事务并发执行的时候应该处于隔离状态.

4.持久性:一旦事务执行成功,对数据库的影响是持久的.

? 事物的隔离级别:

1.读未提交--read unconmmitted

2.读已提交--read committed

3.可重复读--repeatable read

4.串行化--serializable锁表(安全性最高,性能最低)

由事务隔离级别引发并发访问操作的问题:脏读,不可重复读,幻读,