表强化: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填充