MySQL语句学习（五）--外连接、约束和自增长

一、mysql 表外连接

1、之前的查询，是利用WHERE子句对多张表形成笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的不显示

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

        注意：有些部门是没员工的，因此通过员工编号关联两张表，没员工的部门不会匹配显示，现在要求把所有部门名称显示出来，包括没员工的部门，即dept表列全显示，用到外连接

1、左外连接 

左外连接：左侧表完全显示。比如显示所有学生成绩，即使没有成绩的也要显示学生的学号和姓名，成绩那显示null

SELECT ... FROM 表1  LEFT JOIN 表2

                        ON  条件 

其中表1就是左表，表2是右表        表1的列完全显示，与表2匹配不上的项为null 

 2、右外连接 

右外连接：右侧表完全显示。比如显示所有学生成绩，即使没有成绩的也要显示学生的学号和姓名，成绩那显示null

SELECT ... FROM 表1  RIGHT JOIN 表2

                        ON  条件 

其中表1就是左表，表2是右表  ，表2的列完全显示，与表1匹配不上的项为null     

CREATE TABLE stu ( id INT, `name` VARCHAR(32));
                   
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');

CREATE TABLE exam(id INT, grade INT);

INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空）

--ID为3，4的没成绩，所以只显示两个1，2
SELECT `name`, stu.id, grade
    FROM stu, exam
    WHERE stu.id = exam.id;
-- 改成左外连接，显示1，2，3，4名字成绩，其中3，4成绩为null
SELECT `name`, stu.id, grade
    FROM stu LEFT JOIN exam
    ON stu.id = exam.id;

-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)，显示id为1，2，11的姓名，成绩，11的姓名为null
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
    FROM stu RIGHT JOIN exam
    ON stu.id = exam.id;


-- 列出部门名称和这些部门的员工信息(名字和工作)，
-- 同时列出那些没有员工的部门名。
/*
    分析：部门名称和部门员工信息来自emp表和dept表，同时列出那些没有员工的部门名表示要把部门名称完全显示，因此左外连接dept放左边，右外连接dept放右边。
*/
-- 使用左外连接实现
SELECT dname, ename, job
    FROM dept LEFT JOIN emp
    ON dept.deptno = emp.deptno
-- 使用右外连接实现
SELECT dname, ename, job
    FROM emp RIGHT JOIN dept
    ON dept.deptno = emp.deptno

二、mysql 约束

2.1、约束的基本介绍

约束用于确保数据库的数据满足特定的商业规则。

在mysql中，约束包括：not null、unique、primary key、foreign key和check五种·。

2.2、primary key(主键)-基本使用

1、字段名 字段类型 primary key

用于唯一标识表行的数据，当定义主键约束后，该列不能重复

2、primary key 定义的列（字段）中的内容不能重复也不能为null

3、一张表最多智能有一个主键，但是可以是复合主键                     

CREATE TABLE t18

(id INT ,

`name` VARCHAR(32),

email VARCHAR(32),

PRIMARY KEY (id, `name`) -- 这里就是复合主键，用于定义的后面

);

4、主键的指定方式有两种：（1）、直接在字段名后指定：字段名 字段类型 primary key

                                             （2）、在表定义最后写primary key（列名）

5、使用desc 表名查看表的字段结构，可以看到primary key的情况

6、在实际开发中往往都会设计一个主键

--主键的使用
CREATE TABLE t17
(id INT PRIMARY KEY, -- 表示 id 列是主键
`name` VARCHAR(32),
email VARCHAR(32));

-- 主键列的值是不可以重复，1和1重复
INSERT INTO t17
    VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t17
    VALUES(2, 'tom', 'tom@sohu.com');
INSERT INTO t17
    VALUES(1, 'hsp', 'hsp@sohu.com');

-- primary key 不能重复而且不能为 null。
INSERT INTO t17
    VALUES(NULL, 'hsp', 'hsp@sohu.com');

-- 一张表最多只能有一个主键, 但可以是复合主键(比如 id+name)
CREATE TABLE t18
    (id INT PRIMARY KEY, -- 表示 id 列是主键
    `name` VARCHAR(32), PRIMARY KEY -- 错误的，一张表最多只能有一个主键
    email VARCHAR(32)); 

-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t18
(id INT , `name` VARCHAR(32),
email VARCHAR(32), 
PRIMARY KEY (id, `name`) -- 这里就是复合主键
);

2.3、not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

字段名 字段类型 not null

常以not null default 默认值使用

2.4、unique(唯一)

当定义了unique(唯一)约束后，该列值时不能重复的

字段名 字段类型 unique

注意事项：

1、如果没指定not null 则unique字段可以有多个null

2、一张表可以有多个unique字段

-- unique 的使用
CREATE TABLE t21
(id INT UNIQUE , -- 表示 id 列是不可以重复的. `name` VARCHAR(32) , email VARCHAR(32)
);

INSERT INTO t21
    VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t21
    VALUES(1, 'tom', 'tom@sohu.com');#错误id时unique的，不能重复


-- unqiue 使用细节
-- 1. 如果没有指定 not null , 则 unique 字段可以有多个 null 
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
    VALUES(NULL, 'tom', 'tom@sohu.com');
SELECT * FROM t21; 
-- 2. 一张表可以有多个 unique 字段
CREATE TABLE t22
(id INT UNIQUE , -- 表示 id 列是不可以重复的. 
`name` VARCHAR(32) UNIQUE , -- 表示 name 不可以重复
email VARCHAR(32)
);

2.5、foreign key(外键)

1、foreign key(外键)用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null（学生/班级的关系，如下图所示）

2、FOREIGN KEY (本表字段名) REFERENCES 主表名（主键名或unique字段名）

 foreign key(外键)的细节说明：

1、外键指向的表的字段，要求是primary key或者是unique

2、表的类型是innodb，这样的表才支持外键

3、外键字段的类型要和主键字段类型一致（长度可以不同）

4、外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为null】

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

-- 外键演示
-- 创建 主表 my_class
CREATE TABLE my_class (
    id INT PRIMARY KEY , -- 班级编号
    `name` VARCHAR(32) NOT NULL DEFAULT ''); 

-- 创建 从表 my_stu
CREATE TABLE my_stu (
    id INT PRIMARY KEY , -- 学生编号
    `name` VARCHAR(32) NOT NULL DEFAULT '', 
    class_id INT , -- 学生所在班级的编号

-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id))

-- 测试数据
INSERT INTO my_class
    VALUES(100, 'java'), (200, 'web');
INSERT INTO my_class
    VALUES(300, 'php');

SELECT * FROM my_class;
INSERT INTO my_stu
    VALUES(1, 'tom', 100);
INSERT INTO my_stu
    VALUES(2, 'jack', 200);
INSERT INTO my_stu
    VALUES(3, 'hsp', 300);
INSERT INTO my_stu
    VALUES(4, 'mary', 400); -- 这里会失败...因为 400 班级不存在
INSERT INTO my_stu
    VALUES(5, 'king', NULL); -- 可以, 外键 没有写 not null
SELECT * FROM my_class;

-- 一旦建立主外键的关系，数据不能随意删除了
DELETE FROM my_class
    WHERE id = 100;

 2.6、check

用于强制行数据必须满足的条件，假定在sal工资列上定义了check约束，并要求sal列值在1000~2000之间，如果不在这个范围就会提示出错

注意：oracle和sql server均支持check，但是mysql5.7母亲不支持check，语法可以通过，但不会执行生效

基本语法： 列名 类型  check （check条件）

--学习 oracle, sql server, 这两个数据库是真的生效. -- 测试
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)
);
-- 添加数据
INSERT INTO t23
    VALUES(1, 'jack', 'mid', 1);
SELECT * FROM t23;

2.7、约束练习

-- 使用约束的课堂练习

CREATE DATABASE shop_db;

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

-- 商品 goods（商品号 goods_id，商品名 goods_name，单价 unitprice，商品类别 category，

-- 供应商 provider);

-- 客户 customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,身份证 card_Id);

-- 购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id,购买数量 nums);

-- 1 建表，在定义中要求声明 [进行合理设计]：

-- (1)每个表的主外键；

-- (2)客户的姓名不能为空值；

-- (3)电邮不能够重复;

-- (4)客户的性别[男|女] check 枚举..

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

-- 商品 goods
CREATE TABLE goods (
        goods_id INT PRIMARY KEY, 
        goods_name VARCHAR(64) NOT NULL DEFAULT '', 
        unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
        CHECK (unitprice >= 1.0 AND unitprice <= 9999.99), 
        category INT NOT NULL DEFAULT 0, 
        provider VARCHAR(64) NOT NULL DEFAULT '');

-- 客户customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,-- 身份证 
--card_Id);

CREATE TABLE customer(
    customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定
    `name` VARCHAR(64) NOT NULL DEFAULT '', 
    address VARCHAR(64) NOT NULL DEFAULT '', 
    -- 这里使用的枚举类型, 是生效
    email VARCHAR(64) UNIQUE NOT NULL, sex ENUM('男','女') NOT NULL , 
    card_Id CHAR(18));

-- 购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id, 购买数量 nums);

CREATE TABLE purchase(
        -- 外键约束在最后
        order_id INT UNSIGNED PRIMARY KEY, customer_id CHAR(8) NOT NULL DEFAULT '', 
        -- 外键约束在最后    
        goods_id INT NOT NULL DEFAULT 0 , 
        nums INT NOT NULL DEFAULT 0, 
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id), 
        FOREIGN KEY (goods_id) REFERENCES goods(goods_id));

2.8、自增长

2.8.1、自增长基本介绍

在某张表中，存在一个id列（整数），希望在添加记录时候，该列从1开始，自动的增长。

字段名 整型 primary key auto_increment

自增长的使用：

1、一般来说，自增长是和primary key配合使用

2、自增长也可以单独使用，但需要配合一个unique

3、自增长修饰的字段为整数型的，小数也可以，但很少用

4、自增长是从1开始，也可以通过下面指令修改：

        ALTER TABLE 表名 auto_increment = 新的开始值

5、如果添加数据时，给自增长字段（列）赋值了，则以指定的值为准，如果制定了自增长，一般来说，就按照自增长的规则来添加数据

-- 演示自增长的使用
-- 创建表
CREATE TABLE t24
    (id INT PRIMARY KEY AUTO_INCREMENT, 
    email VARCHAR(32)NOT NULL DEFAULT '', 
    `name` VARCHAR(32)NOT NULL DEFAULT '');

-- 测试自增长的使用
#自增长修饰的id赋值null，该列从1开始
INSERT INTO t24
    VALUES(NULL, 'tom@qq.com', 'tom');
#自增长修饰的不赋值，自动增长为2
INSERT INTO t24(email, `name`) 
    VALUES('hsp@sohu.com', 'hsp');
#自增长修饰的id从666开始增长
INSERT INTO t24
    VALUES(666, 'tom@qq.com', 'tom');

-- 修改默认的自增长开始值
ALTER TABLE t25 AUTO_INCREMENT = 100

三、总结字段修饰--类型、约束、自增长、字符集类型、字符校准

字段名 字段类型 约束 自增长

在mysql中，约束包括：not null、unique、primary key、foreign key和check五种·。

primary key 仅有一个，或复合定义

not null不为空，一般配合默认值使用：NOT NULL DEFAULT ''

unique 唯一，该字段内容不能重复，但可以有多个unique修饰的字段

foreign key 外键 ，两张表关联起来，如学生表有字段班级，班级表有字段班级，学生表为从表，班级为主表。FOREIGN KEY(student.id) REFERENCES class(id)

check:强制约束，mysql5.9版本语法通过，但不生效，不用