Mysql外连接与约束

 Mysql外连接

emp表

dept表

 显示部门名称和这些部门的员工的名称和工作，同时要求显示出那些没有员工的部门

SELECT dname,ename,job FROM emp,dept WHERE emp.deptno = dept.deptno ORDER BY dname 

可以发现同时要求显示出那些没有员工的部门这个需求做不到，OPERATIONS部门就显示不出来，这时候就需要用到下面内容的知识 

左/右连接

在这里将emp表当作左表，dept表当右表

语法

SELECT .. FROM 表1 LEFT JOIN 表2 ON 条件

现在继续完成上列需求： 显示部门名称和这些部门的员工的名称和工作，同时要求显示出那些没有员工的部门

SELECT dname,ename,job 
	FROM dept LEFT JOIN emp
	ON emp.deptno = dept.deptno ORDER BY dname

LEFT JOIN 表示左边的表全显示，即dept中没有与emp表中对上的记录也要显示 

SELECT dname,ename,job 
	FROM emp RIGHT JOIN dept 
	ON emp.deptno = dept.deptno ORDER BY dname

如图可知，没有员工的OPERATIONS部门也显示了出来，字段值为空

Mysql约束

基本介绍

约束用于确保数据库的数据满足特定的商业规则，在mysql中，约束包括: not null、unique,primary key,foreign key,和check 五种.

主键（primary key）

语法

字段名 字段类型 primary key

细节说明：

1. primary key不能重复而且不能为nuIl

2. 一张表最多只能有一个主键，但可以是复合主键

CREATE TABLE ttt(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`)
);

注：

此时只有当id，name同时相同才报错

3. 主键的指定方式 有两种：

        - 直接在字段名后指定:字段名 primakry key

        - 在表定义最后写 primary key(列名);

4. 使用desc 表名，可以看到primary key的情况

not bull（非空）

当列定义为not null时，插入数据时必须为列提供数据

语法：

字段名 字段类型 not null

unique（唯一）

当定义了唯一约束后，该列是不能重复的

语法

字段名 字段类型 unique

1. 如果没有指定 notnu11，则unigue 字段可以有多个nu11

2. 一张表可以有多个umique学段

foreign（外键）

介绍

用于定义主表和从表之间的关系:

外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

语法

FOREIGN(本表字段名)REFERENCES
主表名(主键名或unique字段名)

实例：

创建学生表和班级表

学生表（从表）	班级表（主表）
id name class_id	id class_name

CREATE TABLE class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
);
CREATE TABLE stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT, -- 外键
	-- 确定外键关系
	FOREIGN KEY (class_id) REFERENCES class(id)
);

INSERT INTO class
	VALUES(100,'JAVA'),(200,'WEB')
INSERT INTO stu
	VALUES(1,'tom',100),(2,'jack',200)
INSERT INTO stu
	VALUES(3,'hsp',300)

如图可知，当class表中主键id没有300这条记录时，在从表外键class_id输入300会进行报错，原因是class表中主键id没有300这条记录与其及进行链接

使用细节

1. 外键指向的表的字段，要求是primary key 或者是unique
2. 表的类型是innodb,这样的表才支持外键

3. 外键字段的类型要和主键字段的类型一致(长度可以不同)

4. 外键字段的值，必须在主键字段中出现过，或者为nuIl [前提是外键字段允许为nuI]

5. 一旦建立主外键的关系，数据不能随意删除了

check

介绍

用于强制行数据必须满足的条件

实例：

创建t33表：

假定在sal列上定义了check约束,并要求sal列值在1000~2000之间，如果不再1000~2000之间就会提示出错。

create table t23(
	id int primary key,
	`name` varchar(32),
	sex varchar(6) check (sex in('man','woman')),
	sal double check (sal > 1000 and sal <2000)
);

由于这里使用的是mysql5.7版本，只对check进行语法校验，但并不生效，所以不做结果展示

Mysql约束实践

商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成:

商品goods(商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider);

客户customer(客户号customer id,姓名name,住址address,电邮email性别sex,身份证card ld);购买purchase(购买订单号order_id，客户号customer _id,商品号goods id,购买数：nums);
1建表，在定义中要求声明[进行合理设计]:

(1)每个表的主外键:
(2)客户的姓名不能为空值;
(3)电邮不能够重复;
(4)客户的性别[男|女]check 枚举.

(5)单价unitprice在1.0-9999.99 之间 check

create table goods(
	goods_id int primary key,
	goods_name varchar(10) not null default ' ',
	unitprice decimal(10,2) not null default 0
		check (unitprice >=1.0 and unitprice <= 9999.99),
	category varchar(10),
	provider varcahr(10)
);
create table customer(
	customer_id int primary key,
	`name` varchar(10),
	address varchar(20),
	email varchar(20),
	sex enum('男','女') not null,
	card_id varchar(20)
);
create table purchase(
	order_id varchar(20),
	customer_id int,
	goods_id int,
	nums int,
	foreign key (customer_id) references customer(customer_id),
	foreign key (goods_id) references goods(goods_id)
);