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;
在customer表 查找customer_street中包含main字符段对应的customer_name
SELECT customer_name FROM customer
WHERE customer_street LIKE "%Main%";
(SELECT customer_name FROM depositor)
UNION
(SELECT customer_name FROM borrower);
联合查表并去重
见下图
求两个表交集去重，见下标
SELECT DISTINCT d.customer_name FROM depositor AS p
INNER JOIN borrower AS d ON p.customer_name=d.customer_name;
求平均值
SELECT AVG(balance) FROM account
WHERE branch_name = 'Perryridge' OR branch_name = 'Mianus';
select count(*) from customer; //求总行数
求两个表相同键的值一共有几行
SELECT COUNT(*) FROM depositor,account
WHERE account.account_number = depositor.account_number;
分组
SELECT branch_name,COUNT(*) FROM depositor,account
WHERE account.account_number = depositor.account_number
GROUP BY branch_name;
SELECT branch_name, COUNT(*) FROM account GROUP BY branch_name;
SELECT branch_name FROM branch
WHERE branch_name != 'Brooklyn' AND
assets > SOME(SELECT assets FROM branch WHERE branch_city = 'Brooklyn');
