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;

在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');