MySQL数据库笔记Day4

表强化：6约束 constraints

非空约束 not null

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user(
 
id INT AUTO_INCREMENT,
 
NAME VARCHAR(30) UNIQUE NOT NULL,
 
age INT,
 
phone VARCHAR(20) UNIQUE NOT NULL,
 
email VARCHAR(30) UNIQUE NOT NULL,
 
PRIMARY KEY (id)
 
);
 
DESC tb_user;
 
#id为自增主键，null值无效，数据库会自动用下一个id值替代
 
#age因为运行为null，所以可以设置为null
 
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);

唯一约束 unique

Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user(
 
id INT,
 
NAME VARCHAR(30) UNIQUE NOT NULL,
 
phone VARCHAR(20) UNIQUE NOT NULL,
 
email VARCHAR(30) UNIQUE NOT NULL,
 
PRIMARY KEY (id)
 
);
 
DESC tb_user;
 
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
 
INSERT INTO tb_user (id,NAME) VALUES(2,'tony');
 
执行上面语句出错：
 
Query : INSERT INTO tb_user (id,NAME) VALUES(2,'tony')
 
Error Code : 1062
 
Duplicate entry 'tony' for key 'name'
 
展示表结构：
 
DESC tb_user;

 主键约束 primary key

主键是一条记录的唯一标识，具有唯一性，不能重复

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user(
 
id INT,
 
NAME VARCHAR(30),
 
PRIMARY KEY (id)
 
);
 
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
 
INSERT INTO tb_user (id,NAME) VALUES(1,'hellen');
 
第二句插入就会报错：
 
Query : INSERT INTO tb_user (id,NAME) VALUES(1,'hellen')
 
Error Code : 1062
 
Duplicate entry '1' for key 'PRIMARY'
 
提示主键1的值已经存在，重复了

外键约束 foreign key

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
 
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);
 
DESC tb_user;
 
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错

 检查约束 check

很少使用，了解即可，录入age超过200将报错

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
createdTime DATE DEFAULT NOW()
 
);
 
DESC tb_user;

 表关联 association

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

一对一 one to one QQ和QQ邮箱，员工和员工编号
一对多 one to many 最常见，部门和员工，用户和订单
多对一 many to one 一对多反过来，员工和部门，订单和用户
多对多 many to many 老师和学生，老师和课程

 多表联查 join

笛卡尔积 Cartesian product

多表查询是指基于两个和两个以上的表的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示员工表emp中不只显示deptno，还要显示部门名称，而部门名称dname在dept表中。 

SELECT * FROM dept,emp #格式

 

上面这种查询两个表的方式称为：笛卡尔积(Cartesian product)，又称直积。一般笛卡尔积没有实际的业务意义，但多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

这点很值得注意，实际开发中尽量少用多表联查，其根本原因就在这里，查询过程中，现在内存中构建一个大大的结果集，然后再进行数据的过滤。那这个构建过程，和所使用的内存资源，包括过滤时的判断，都是既耗费资源，又浪费时间。

这就是阿里规范中禁止3张表以上的联查的原因

三种连接 join

• 内连接 inner join

• SELECT d.dname,e.ename,e.job
   
  FROM emp e INNER JOIN dept d
   
  ON e.deptno=d.deptno
   
  WHERE d.dname='research'
   
  

• 

• 左（外）连接 left join

• SELECT d.dname,e.ename,e.job
   
  FROM emp e  left join  dept d
   
  ON e.deptno=d.deptno
   
  WHERE d.dname='research'
   
  

• 

• 右（外）连接 right join

• SELECT d.dname,e.ename,e.job
   
  FROM emp e right join dept d
   
  ON e.deptno=d.deptno
   
  WHERE d.dname='research'
  

• 

 

 inner join、left join、right join的区别？

• INNER JOIN两边都对应有记录的才展示，其他去掉
• LEFT JOIN左边表中的数据都出现，右边没有数据以NULL填充
• RIGHT JOIN右边表中的数据都出现，左边没有数据以NULL填充