mysql (多表查询 常用的约束 )

mysql中常用的约束:

主键约束(primary key)
自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用
唯一约束(unique)
非空约束(not null)
外键约束(foreign key)
sex ENUM(‘男’,‘女’) – 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果
非负约束 UNSIGNED
例如：这个数据类型 TINYINT 能表示的范围 -128—127
添加了这个约束 TINYINT UNSIGNED 范围就是 0----255 也就是没有了负数值

主键约束:被修饰过的字段唯一非空

注意:一张表只能有一个主键,这个主键可以包含多个字段

方式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; – 就是修改字段值可以为null

唯一约束( 被修饰过的字段唯一,对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:建表的同时在约束区域添加约束
所有的字段声明完成之后,就是约束区域了
unique(字段1,字段值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'

非空约束

特点:被修饰过的字段非空

	方式:
		create table nn(
			id int not null,
			username varchar(20) not null
		);
		
	insert into nn values(null,'tom');--  错误的 Column 'id' cannot be null

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

truncate 清空表

格式:
truncate 表名; 干掉表,重新创建一张空表
和delete from 区别:
delete属于DML语句 truncate属于DDL语句
delete逐条删除 truncate干掉表,重新创建一张空表

案例解释外键

网上商城的实体:

用户 订单 商品 分类

常见关系:

一对多. 用户和订单  分类和商品
多对多. 订单和商品	学生和课程
一对一. 丈夫和妻子	

ER图可以描述实体于实体之间的关系

实体用矩形表示
属性用椭圆表示
关系用菱形表示

ER图的介绍

在ER图中有如下四个成分：

矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。
（对于一对一联系，要在两个实体连线方向各写1；
对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

一对多

在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表,
为了表示一对多的关系,一般会在多表的一方添加一个字段,字段名称自定义(建议:主表的名称_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   
);

为了保证数据的有效性和完整性,添加约束(外键约束).

在多表的一方添加外键约束
	格式:
		alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
	例如:
		alter table orders add foreign key(user_id) references user(id);

添加了外键约束之后有如下特点:

1.主表中不能删除从表中已引用的数据
2.从表中不能添加主表中不存在的数据

开发中处理一对多:
在多表中添加一个外键,名称一般为主表的名称_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：建表的时候就加上了级联更新和级联删除
演示级联删除和级联更新：建表的时候就加上了级联更新和级联删除 – 建表的时候就加上了级联更新和级联删除

CREATE TABLE zhu(
   zid INT PRIMARY KEY AUTO_INCREMENT,
   zname VARCHAR(20)

);
CREATE TABLE cong(
   zid INT PRIMARY KEY AUTO_INCREMENT,
   zscore INT,
   zzid INT,

   FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE
);

多对多

例子:商品和订单
	-- 创建商品表
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);

开发中处理多对多:
	引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多了为了
	保证数据的有效性和完整性需要在中间表上添加两个外键约束即可.

多表查询的几种方式：

内连接

外连接

子查询

笛卡尔积:

多张表无条件的联合查询

 select a.*,b.* from a,b;

内连接

格式1:显式的内连接

  select a.*,b.* from a [inner] join b on ab的连接条件

格式2:隐式的内连接

 select a.*,b.* from a,b where ab的连接条件

外连接

左外连接:

 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值展示

子查询 ( 一个查询依赖另一个查询 )

隐式内连接:

 select user.*,orders.* from user ,orders where user.id=orders.user_id;

显示内连接

 select user.*,orders.* from user join orders on user.id=orders.user_id

查询所有用户的订单详情
左外连接: user在

 select user.*,orders.* from user left join orders on user.id=orders.user_id;

查询所有订单的用户详情
右外连接:orders 在右

 select orders.*,user.* from user right join orders on user.id=orders.user_id;

自查询

比如:我要查询emp表中 员工姓名 所对应的 上司姓名

把 emp 表 看作两张表 a , b

条件 a 表的上司号=b表的员工号

 SELECT a.`mgr` AS 上司号,a.ename,b.ename,b.`empno` 员工号 FROM emp AS a,emp AS b WHERE a.`mgr`=b.`empno`;