MySQL read 查询语句8 主键 外键

-- 主键的使用

CREATE TABLE t17
    (id INT PRIMARY KEY, -- 表示id列是主键
    `name` VARCHAR(32),
    email VARCHAR(32));
INSERT INTO t17
    VALUES (1,'jack','jack@souhu.com'),
    (2,'tom','tom@souhu.com'),
    (1,'hsp','hsp@souhu.com'); #id是主键，重复会报错

    
-- 主键列的值是不可以重复
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');
    
SELECT * FROM t17;

-- 主键使用的细节讨论
-- 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`) -- 这里就是复合主键
    );

INSERT INTO t18
    VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t18
    VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t18
    VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t18;
 
 -- 外键演示
 -- 创建 主表 班级表
 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_stu
    VALUES (1,'tom',100)
INSERT INTO my_stu
    VALUES (2,'jack',200)
INSERT INTO my_stu
    VALUES (3,'hsp',300) -- 失败 因为300号班级不存在,添加班级才看可以添加学生成功   
SELECT * FROM my_stu

-- check的使用  此语法mysql8.0开始生效
CREATE TABLE t23(
    id INT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    sex VARCHAR(6) CHECK (sex IN ('man','woman')),
    sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
    );
DESC t23
-- 添加数据
INSERT INTO t23
    VALUES (1,'jack','main',1)
SELECT * FROM t23

-- 练习实例
-- 1.每个表的主外键
-- 2.客户姓名不能为空
-- 3.电邮不能重复
-- 4.客户的性别男女 check 枚举
-- 5.unitprice 在1.0-9999.99之间 check
CREATE DATABASE shop_db
CREATE TABLE goods (
    goods_id INT PRIMARY KEY ,
    goods_name VARCHAR(32) NOT NULL DEFAULT '',
    unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.00 AND unitprice < 9999.99),
    category INT NOT NULL DEFAULT 0,
    provider VARCHAR(32) NOT NULL DEFAULT 0
    );

CREATE TABLE customer (
    customer_id INT PRIMARY KEY ,
    `name` VARCHAR(32)  NOT NULL DEFAULT '',
    address VARCHAR(64)NOT NULL DEFAULT '',
    email VARCHAR(32) UNIQUE NOT NULL DEFAULT '',
    sex ENUM ('男','女'), # 或者sex CHAR(1) CHECK ('男','女')
    card_id CHAR(18)
    );
    
CREATE TABLE purchase (
    order_id INT UNSIGNED PRIMARY KEY ,
    customer_id INT NOT NULL DEFAULT 0,
    goods_id INT NOT NULL DEFAULT 0,
    nums INT ,
    FOREIGN KEY(goods_id) REFERENCES goods(goods_id),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    );
-- 记录purchase时 必须是有该goods表的goods_id和customer表的customer_id
-- 才能添加相应的购买表，保证商品和客户都是存在的
-- 而在删除goods和customer时必须先删除购买记录