一、约束
作用:
为了保证数据的有效性和完整性
mysql中常用的约束:
1、主键约束(primary key)
2、自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用
3、唯一约束(unique)
4、非空约束(not null)
5、外键约束(foreign key)
sex ENUM('男','女') -- 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果
非负约束 UNSIGNED
例如:这个数据类型 TINYINT 能表示的范围 -128---127
我添加了这个约束 TINYINT UNSIGNED 范围就是 0----255 也就是没有了负数值
1、主键约束
被修饰过的字段唯一非空
注意:一张表只能有一个主键,这个主键可以包含多个字段
方式1:建表的同时添加约束 格式: 字段名称 字段类型 primary key
方式2:建表的同时在约束区域添加约束
所有的字段声明完成之后,就是约束区域了
格式: primary key(字段1,字段2)
create table pk01(
id int,
username varchar(20),
primary key (id)
);
insert into pk01 values(1,'tom');-- 成功
insert into pk01 values(1,'tom');-- 失败 Duplicate entry '1' for key 'PRIMARY'
insert into pk01 values(null,'tom');-- 失败 Column 'id' cannot be null
create table pk01(
id int primary key,
username varchar(20),
primary key (id)
);
– 错误的 一张表只能有一个主键
方式3:建表之后,通过修改表结构添加约束
create table pk02(
id int,
username varchar(20)
);
alter table pk02 add primary key(字段名1,字段名2..);
alter table pk02 add primary key(id,username);
insert into pk02 values(1,'tom');-- 成功
insert into pk02 values(1,'tomcat');-- 成功
insert into pk02 values(1,'tomcat');-- 失败
删除主键约束
情况1: 这个字段,只有主键约束,分两步来删除主键约束
第一步: alter table 表名 drop primary key; – 这样只删除了唯一,他还有个非空约束,所以得再删除非空约束
第二步: alter table [表名] modify [列名] varchar(20) null; --修改字段名还为原来的字段 加上null即可
情况2: 这个字段,是一个int类型字段,既有主键约束,又有自增长约束,那么得先删除自增长约束,在删除主键约束
第一步:删除自增长约束,其实就是修改自增长字段名和数据类型还为原来的字段名和类型
alter table 表名 change 字段名 字段名 数据类型; --删除自增长约束
第二步:删除主键约束
alter table 表名 drop primary key;
第三步:删除非空约束
ALTER TABLE test3 MODIFY sid INT NULL; – 就是修改字段值可以为nu
2、自增长约束
auto_increment 自增
要求:
1.被修饰的字段类型支持自增. 一般int
2.被修饰的字段必须是一个key 一般是primary key
create table ai01(
id varchar(10) auto_increment
);-- 错误 Incorrect column specifier for column 'id'
create table ai01(
id int auto_increment
);-- 错误 Incorrect table definition; there can be only one auto column and it must be defined as a key
3、唯一约束(了解)
被修饰过的字段唯一,对null不起作用
方式1:建表的同时添加约束 格式: 字段名称 字段类型 unique
create table un(
id int unique,
username varchar(20) unique
);
insert into un value(10,'tom');-- 成功
insert into un value(10,'jack');-- 错误 Duplicate entry '10' for key 'id'
insert into un value(null,'jack');-- 成功
insert into un value(null,'rose');-- 成功
方式2:建表的同时在约束区域添加约束
所有的字段声明完成之后,就是约束区域了
方式3:建表之后,通过修改表结构添加约束
alter table 表名 add unique(字段1,字段2);-- 添加的联合唯一
alter table 表名 add unique(字段1);-- 给一个添加唯一
alter table 表名 add unique(字段2);-- 给另一个添加唯一
create table un01(
id int,
username varchar(20)
);
alter table un01 add unique(id,username);
insert into un01 values(1,'tom');-- 成功
insert into un01 values(1,'jack');-- 成功
insert into un01 values(1,'tom');-- 失败 Duplicate entry '1-tom' for key 'id'
4、非空约束
特点:被修饰过的字段非空
方式:
create table nn(
id int not null,
username varchar(20) not null
);
insert into nn values(null,'tom');-- 错误的 Column 'id' cannot be null
外键约束
为了保证数据的有效性和完整性,添加约束(外键约束).
在多表的一方添加外键约束
格式:
alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
例如:
alter table orders add foreign key(user_id) references user(id);
特点:
1.主表中不能删除从表中已引用的数据
2.从表中不能添加主表中不存在的数据
引入多表查询
二、多表查询
ER图
在ER图中有如下四个成分:
矩形框:表示实体,在框中记入实体名。
菱形框:表示联系,在框中记入联系名。
椭圆形框:表示实体或联系的属性,将属性名记入框中。对于主属性名,则在其名称下划一下划线。
**连线:**实体与属性之间;实体与联系之间;联系与属性之间用直线相连,并在直线上标注联系的类型。
(对于一对一联系,要在两个实体连线方向各写1;
对于一对多联系,要在一的一方写1,多的一方写N;对于多对多关系,则要在两个实体连线方向各写N,M
1、一对多
在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表,
为了表示一对多的关系,一般会在多表的一方添加一个字段,字段名称自定义(建议:主表的名称_id)
字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键
一对多:
用户和订单
-- 创建用户表
create table user(
id int primary key auto_increment,
username varchar(20)
);
-- 创建订单表
create table orders(
id int primary key auto_increment,
totalprice double,
user_id int
);
开发中处理一对多:
在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.
那如果添加了外键后我想删除主表中的数据 怎么办 ?
方式1: 级联删除
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE;
然后你就可以删除主表中的数据了
当然你可以级联删除和级联更新 都加上
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
方式2:先把带有外键的多表的数据删除,再删除一表中的数据
2、多对多
例子:商品和订单
– 创建商品表
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);
– 创建中间表
create table orderitem(
oid int,
pid int
);
– 添加外键约束
alter table orderitem add foreign key(oid) references orders(id);
alter table orderitem add foreign key(pid) references product(id);
**开发中处理多对多:**★
引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了
为了保证数据的有效性和完整性:需要在中间表上添加两个外键约束即可.
三、 多表查询
技术分析:
1、内连接
2、外连接
3、子查询
1、内连接:★
格式1:显式的内连接
select a.*,b.* from a [inner] join b on ab的连接条件
格式2:隐式的内连接
select a.*,b.* from a,b where ab的连接条件
2、外连接:★
左外连接:★
select a.*,b.* from a left [outer] join b on 连接条件; outer 可以不写
意思:
先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),
符合条件则展示出来,不符合以null值展示.
右外连接:
select a.*,b.* from b right [outer] join a on 连接条件; outer 可以不写
意思:
先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),
符合条件则展示出来,不符合以null值展示.
加粗子查询:★
就是将一个查询的结果作为一张临时表
一个查询依赖另一个查询.样式
演示:
在这里插入-- 22、获取有两个或多个售货员为之工作的项目名称
SELECT project_name FROM project WHERE project_no=(SELECT project_no FROM works_on GROUP BY job HAVING COUNT(*)>=2 AND works_on.`job`='Clerk');代码片