第二章 SQL进阶

第二章 SQL进阶

第一节 多关联数据表创建

1. 数据表创建：员工信息表、分公司信息、客户信息表、销售信息表

员工信息表

注：sup_id 主管的id; branch_id 对应所属分支结构的id
分公司信息表

客户信息表

销售信息表

worker_id与customer_id为共同主键

1.1 互为外键-表格创建

Worker_Info的主键worker_id与Branch_Info的主键Branch_id 互为外键

#  建立新表  Worker_Info
CREATE TABLE Worker_Info (
  worker_id INT PRIMARY KEY,  # worker_id  主键  
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  sex VARCHAR(1),
  birthday DATE,
  payment INT,
  sup_id INT,
  branch_id INT);
describe Worker_Info
# 新建新表 Branch_Info
CREATE TABLE Branch_Info (
  branch_id INT PRIMARY KEY,
  name VARCHAR(40),
  manager_id INT,
  open_date DATE,
  # 参考 Worker_Info(worker_id)设置外键FOREIGN KEY manager_id
  FOREIGN KEY(manager_id) REFERENCES Worker_Info(worker_id) ON DELETE SET NULL);
describe Branch_Info

# 调整Worker_Info表格加入外键，建立第一个表之后才能设置第二个表的外键
ALTER TABLE Worker_Info
ADD FOREIGN KEY(branch_id) REFERENCES Branch_Info(branch_id) ON DELETE SET NULL;

ALTER TABLE Worker_Info
ADD FOREIGN KEY(sup_id) REFERENCES Worker_Info(worker_id) ON DELETE SET NULL;

1.2 设置联动 ON DELETE SET NULL与ON DELETE CASCADE

被参考的表格的主键被删除，参考的表格怎么办；
例如Branch_Info的主键Branch_id 被删除后，Worker_Info的Branch_id 怎么处理：
ON DELETE SET NULL：该字段数据变为NULL
ON DELETE CASCADE：该行数据都被删除

1.3 共同主键

# 新建表格Customer_Info 
CREATE TABLE Customer_Info (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(40),
  branch_id INT,
  FOREIGN KEY(branch_id) REFERENCES Branch_Info(branch_id) ON DELETE SET NULL);
# 新建表格
CREATE TABLE Sales_Info (
  worker_id INT,
  customer_id INT,
  total_revenue INT,
  PRIMARY KEY(worker_id, customer_id),   #共同主键设置
  FOREIGN KEY(worker_id) REFERENCES Worker_Info(worker_id) ON DELETE CASCADE,   #外键设置，删除该员工该行数据也没有意义了
  FOREIGN KEY(customer_id) REFERENCES Customer_Info(customer_id) ON DELETE CASCADE);

1.4 互为外键的数据表数据插入

引用的数据没有时，先插入null，引用数据创建好后，再update set

-- 数据插入
-- 为branch为A的插入Worker_Info、Branch_Info数据（互为外键所以过程相对繁琐）
-- A
INSERT INTO Worker_Info VALUES(1, 'Ben', 'Martin', 'M', '1953-11-03', 330000, NULL, NULL);
INSERT INTO Branch_Info VALUES(10, 'A', 1, '2000-01-07');

# 第一个数据插入时，第二个表的`branch_id`=10 还没有，需要更新
UPDATE Worker_Info  
SET branch_id = 10
WHERE worker_id = 1;   

INSERT INTO Worker_Info VALUES(2, 'Alice', 'Liu', 'F', '1969-05-10', 270000, 1, 10);  #第二个表的`branch_id`=10已经有了，不用更新了

-- B
INSERT INTO Worker_Info VALUES(3, 'David', 'Irvine', 'M','1972-03-29', 220000, 1, NULL);
INSERT INTO Branch_Info VALUES(11, 'B', 3, '2005-03-08');

UPDATE Worker_Info
SET branch_id = 11
WHERE worker_id = 3;

INSERT INTO Worker_Info VALUES(4, 'Angela', 'Wang', 'F','1982-12-21', 180000, 3, 11);
INSERT INTO Worker_Info VALUES(5, 'David', 'Zhang', 'M','1993-10-22', 120000, 3, 11);

-- C
INSERT INTO Worker_Info VALUES(6, 'Jane', 'Bush','F','1988-07-23', 180000, 3, NULL);

INSERT INTO Branch_Info VALUES(12, 'C', 6, '2012-03-23');

UPDATE Worker_Info
SET branch_id = 12
WHERE worker_id = 6;

INSERT INTO Worker_Info VALUES(7, 'Ming', 'Xiao','M', '1995-01-02', 100000, 6, 12);
INSERT INTO Worker_Info VALUES(8, 'Peter', 'Chen', 'M','1990-03-06', 120000, 6, 12);
INSERT INTO Worker_Info VALUES(9, 'Alex', 'Zhang', 'M','1988-11-11', 190000, 6, 12);

剩下的表格因为引用的已经有数据了，所以正常插入即可

-- 为 Customer_Info 插入数据
INSERT INTO Customer_Info VALUES(100, 'Tencent', 11);
INSERT INTO Customer_Info VALUES(101, 'Alibaba', 11);
INSERT INTO Customer_Info VALUES(102, 'Baidu', 12);
INSERT INTO Customer_Info VALUES(103, 'Linkedin', 12);
INSERT INTO Customer_Info VALUES(104, 'Google', 11);
INSERT INTO Customer_Info VALUES(105, 'Facebook', 12);
INSERT INTO Customer_Info VALUES(106, 'Amazon', 11);
-- 为 Sales_Info 插入数据
INSERT INTO Sales_Info VALUES(3, 100, 100000);
INSERT INTO Sales_Info VALUES(5, 100, 223333);
INSERT INTO Sales_Info VALUES(6, 102, 534343);
INSERT INTO Sales_Info VALUES(3, 101, 7677);
INSERT INTO Sales_Info VALUES(9, 103, 700056);
INSERT INTO Sales_Info VALUES(5, 104, 555435);
INSERT INTO Sales_Info VALUES(8, 103, 344543);
INSERT INTO Sales_Info VALUES(7, 105, 676342);
INSERT INTO Sales_Info VALUES(4, 106, 223416);
INSERT INTO Sales_Info VALUES(8, 105, 30986);

2. 多关联表建表注意事项

3. 多关联表数据插入