MySQL数据库的约束以及多表查询

一、约束

作用：
为了保证数据的有效性和完整性

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');代码片