msql 复杂练习

https://blog.csdn.net/xiao__oaix/article/details/78122294

customer表branch 表account 表

depositor 表loan 表borrower表

CREATE TABLE branch
(branch_name VARCHAR(20) NOT NULL,
branch_city VARCHAR(20),
assets INT,
CONSTRAINT PRIMARY KEY(branch_name)
);

CREATE TABLE account
(account_number INT NOT NULL AUTO_INCREMENT,
branch_name VARCHAR(20),
balance INT,
CONSTRAINT PRIMARY KEY(account_number),
CONSTRAINT FOREIGN KEY(branch_name)REFERENCES branch(branch_name) ON DELETE CASCADE
);

CREATE TABLE depositor
(customer_name VARCHAR(20),
account_number INT,
CONSTRAINT FOREIGN KEY(account_number) REFERENCES account(account_number) ON DELETE CASCADE
);
CREATE TABLE customer
(customer_name VARCHAR(20),
customer_street VARCHAR(20),
customer_city VARCHAR(20),
CONSTRAINT PRIMARY KEY(customer_name)
);

CREATE TABLE loan
(loan_number INT,
branch_name VARCHAR(20),
amount INT,
CONSTRAINT FOREIGN KEY(branch_name) REFERENCES branch(branch_name) ON DELETE CASCADE,
CONSTRAINT PRIMARY KEY(loan_number)
);

CREATE TABLE borrower
(customer_name VARCHAR(20),
loan_number INT,
CONSTRAINT FOREIGN KEY(customer_name) REFERENCES customer(customer_name) ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY(loan_number) REFERENCES loan(loan_number) ON DELETE CASCADE
);

INSERT INTO customer VALUES('Adams','Spring','Pittsfield');
INSERT INTO customer VALUES('Brooks','Senator','Brooklyn');
INSERT INTO customer VALUES('Curry','North','Rye');
INSERT INTO customer VALUES('Glenn','Sand Hill','Woodside');
INSERT INTO customer VALUES('Green','Walnut','Stamford');
INSERT INTO customer VALUES('Hayes','Main','Harrison');
INSERT INTO customer VALUES('Johnson','Alma','Palo Alto');
INSERT INTO customer VALUES('Jones','Main','Harrison');
INSERT INTO customer VALUES('Lindasy','Park','Pittsfield');
INSERT INTO customer VALUES('Smith','North','Rye');
INSERT INTO customer VALUES('Turner','Putnam','Stamford');
INSERT INTO customer VALUES('Willianms','Nassau','Princeton');

SELECT * FROM customer;

INSERT INTO branch VALUES('Brighton','Brooklyn',7100000);
INSERT INTO branch VALUES('Downtown','Brooklyn',9000000);
INSERT INTO branch VALUES('Mianus','Horseneck',400000);
INSERT INTO branch VALUES('North Town','Rye',3700000);
INSERT INTO branch VALUES('Perryridge','Horseneck',1700000);
INSERT INTO branch VALUES('Pownal','Bennington',300000);
INSERT INTO branch VALUES('Redwood','Palo Alto',2100000);
INSERT INTO branch VALUES('Round Hill','Horseneck',8000000);

SELECT * FROM branch;

INSERT INTO account VALUES(101,'Downtown',500);
INSERT INTO account VALUES(102,'Perryridge',400);
INSERT INTO account VALUES(201,'Brighton',900);
INSERT INTO account VALUES(215,'Mianus',700);
INSERT INTO account VALUES(217,'Brighton',750);
INSERT INTO account VALUES(222,'Redwood',700);
INSERT INTO account VALUES(305,'Round Hill',350);

SELECT * FROM account;

INSERT INTO depositor VALUES('Hayes',102);
INSERT INTO depositor VALUES('Johnson',101);
INSERT INTO depositor VALUES('Johnson',201);
INSERT INTO depositor VALUES('Jones',217);
INSERT INTO depositor VALUES('Lindsay',222);
INSERT INTO depositor VALUES('Smith',215);
INSERT INTO depositor VALUES('Turner',305);

SELECT * FROM depositor;

INSERT INTO loan VALUES(11,'Round Hill',900);
INSERT INTO loan VALUES(14,'Downtown',1500);
INSERT INTO loan VALUES(15,'Perryridge',1500);
INSERT INTO loan VALUES(16,'Perryridge',1300);
INSERT INTO loan VALUES(17,'Downtown',1000);
INSERT INTO loan VALUES(23,'Redwood',2000);
INSERT INTO loan VALUES(93,'Mianus',500);

SELECT * FROM loan;

INSERT INTO borrower VALUES('Adams',16);
INSERT INTO borrower VALUES('Curry',93);
INSERT INTO borrower VALUES('Hayes',15);
INSERT INTO borrower VALUES('Jacson',14);
INSERT INTO borrower VALUES('Jones',17);
INSERT INTO borrower VALUES('Smith',11);
INSERT INTO borrower VALUES('Smith',23);
INSERT INTO borrower VALUES('Williams',17);

SELECT * FROM borrower;

//通过中间表交集如下一图,默认是inner join

SELECT customer_name,loan.loan_number loan_id,amount
FROM loan JOIN borrower ON loan.loan_number = borrower.loan_number;