Java 数据库之约束,多表查询
一 约束
数据库中的约束:对插入表中的数据做出一种限定,为了保证数据的有效性和完整性
主键约束 primary key
唯一约束 unique
非空约束 not null
自增长约束 auto increment
非负约束 unsigend
外键约束 foreign key
1.1主键约束
非空且唯一,一张表只能有一个主键
如何添加主键:
create table test(
name varchar(10),
age int
)
insert into test values(null,null)//不加主键可以插入空值
//添加主键方式一
create table test2(
name varchar(10) primary key,//建表时添加主键
age int
)
insert into test2 values('aaa',null);
insert into teat2 values('aaa',null);//不能插入相同的主键
//添加主键方式二
//通过修改表添加主键
create table test3(
name varchar(10),
age int
)
alter table test add primary key(name);//表已经建好,通过修改表添加主键约束
//添加主键方式三
create table test4(
name varchar(10),
age int,
primary key(name)//设置主键
)
//将多个字段看做一个整体设置为主键
create table test5(
name varchar(10),
age int,
primary key(name,age)//设置联合主键
)
alter table test5 add primary key(name,age);
1.2 唯一约束,非空约束
create table test6(
name varchar(10) unique,//唯一约束:对null值不起作用
age int,
sex varchar(10) not null,//非空约束
id int unique not null //非空且唯一
)
1.3自增长约束
create table test7 (
name varchar(10),
age int,
id int primary key auto_increment//主键自增长约束
)
1.4非负约束
create table test8(
id tinyint unsigend,//非负约束
name varchar(10),
age int
)
//删除主键约束
alter table test drop primary key;//只删除了唯一
alter table test modify name carchar(20) null;//删除非空
//删除主键自增长约束
alter table test change id id int ;//删除自增长
alter table test drop primary key;//删除主键约束
alter table test modify id int null;//删除非空约束
1.5外键约束
实体:数据库中的表,可以看作一个实体
实体和实体之间的关系:一对一,一对多,多对多
ER图
实体用矩形表示
属性用椭圆表示
关系用菱形表示
连线:实体和属性之间,实体和联系之间,联系和属性之间用直线相连,并在直线上标注联系的类型
为了保证数据的有效性和完整性,需要在多表一方添加外键约束,去关联单表一方的主键,让两张表之间产生相互制衡,添加外键约束有如下特点:
- 单表一方不能删除多表还在引用的数据
- 多表一方不能添加单表没有描述的数据
//用户跟订单 用户属于单表一方 订单属于多表 一个用户可以有多张订单
create table uesr(
id int primary key auto_increment,
uesrname varchar(20)
);
create table orders (
id int primary key auto_increment,
totalprice double,
user_id int
) ;
//修改表添加外键约束
alter table orders add foreign key (user_id) references user(id);
//添加外键约束主表一方不能删除多表还在引用的数据
//多表一方不能添加主表没用描述的数据
级联删除,删除主表的一项 ,那么和这一项相关联的一并删除
alter table orders add foreign key (user_id) references user(id) on delete cascade;//级联删除
建议建表时提供一个整数型的主键自增长
为了查询方便,对于多对多关系,通常要引入一种中间表,把多对多变成两个一对多。
二 多表查询
//用户表(user)
create table `user` (
`id` int auto_increment primary key,
`username` varchar(50) -- 用户姓名
);
//订单表(orders)
create table `orders` (
`id` int auto_increment primary key,
`price` double,
`user_id` int
);
//给订单表添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);
//向user表中添加数据
insert into user values(3,'张三');
insert into user values(4,'李四');
insert into user values(5,'王五');
insert into user values(6,'赵六');
// 向orders 表中插入数据
insert into orders values(1,1314,3);
insert into orders values(2,1314,3);
insert into orders values(3,15,4);
insert into orders values(4,315,5);
insert into orders values(5,1014,null);
多张表联合查询
多表查询要有关联条件
内连接:不符合条件的信息,不做展示
select user.*,orders.*from user ,orders where user.id=orders.user_id;
外连接:左外连接 右外连接
查询所有的用户的订单信息,要求所有的用户展示出来,如果没有这个用户,就以null展示出来。
左外连接:left关键字 左边表中的信息全部会展示,在右边表中没有符合条件的以null展示
select user.*,orders.* from user left outer join orders on user.id=orders.user_id;
右外连接: 查询所有订单是属于哪个用户的,所有的订单必须展示,这个订单没有对应的用户用null展示
右边的表中信息全部展示,没有对应的以null展示
select orders.*,user.* from user right outr join orders on user.id=orders.user_id;
左外连接和右外连接可以互换