面试遇到的数据库题

1.现有表如下

CREATE DATABASE IF NOT EXISTS company;

USE company;
CREATE TABLE customer(
user_id VARCHAR(10) NOT NULL,
user_name VARCHAR(64) NOT NULL,
PRIMARY KEY(user_id)
);
CREATE TABLE shopping(
product_id INT(10) NOT NULL,
user_id VARCHAR(10) NOT NULL,
amout DECIMAL(12,2) NOT NULL
);
INSERT INTO customer VALUES('A','张三'),('B','李四'),('C','王五');
INSERT INTO shopping VALUES(1000000000,'A',30),(1000000001,'A',80),(1000000002,'A',10.50),
(1000000001,'B',30),(1000000004,'B',100),
(1000000005,'C',200);

列出购物超过一次且平均单价超过50的客户信息

方法一：用IN非相关子查询

SELECT A.user_id,A.user_name
FROM customer A
WHERE A.user_id IN (SELECT B.user_id FROM shopping B GROUP BY B.user_id HAVING COUNT(B.user_id)>1 AND AVG(B.amout)>50);

方法二：用EXISTS相关子查询

SELECT A.user_id,A.user_name
FROM customer A
WHERE EXISTS (SELECT B.user_id FROM shopping B WHERE A.user_id = B.user_id GROUP BY B.user_id HAVING COUNT(B.user_id)>1 AND AVG(B.amout)>50);

2.关于分组后取前N条的在别一篇里专讲

3.

CREATE DATABASE IF NOT EXISTS school;
USE school;
CREATE TABLE `s`(
`sid` INT(32) NOT NULL AUTO_INCREMENT,
`sname` VARCHAR(64) DEFAULT NULL,
`age` INT(32) DEFAULT 0,
PRIMARY KEY(`sid`)
);
CREATE TABLE `c`(
`cid` INT(32) NOT NULL AUTO_INCREMENT,
`cname` VARCHAR(64) NOT NULL,
PRIMARY KEY(`cid`)
);
CREATE TABLE `sc`(
`sid` INT(32) NOT NULL,
`cid` INT(32) NOT NULL,
FOREIGN KEY(`sid`) REFERENCES `s`(`sid`),
FOREIGN KEY(`cid`) REFERENCES `c`(`cid`)
);
ALTER TABLE `c` ADD `cno` CHAR(10) NOT NULL;
ALTER TABLE `c` DROP COLUMN `cno`;
ALTER TABLE `c` ADD `cno` CHAR(1) NOT NULL UNIQUE;
ALTER TABLE sc ADD PRIMARY KEY(sid,cid);
INSERT INTO `s`(`sname`,`age`) VALUES('小1',20),('小2',21),('小3',21),('小4',23),('小5',20),('小6',22),('小7',19),('小8',18),('小9',22);
INSERT INTO `c`(`cno`,`cname`) VALUES('A','语文'),('B','数学'),('C','英语'),('D','物理'),('E','生物'),('F','化学');
INSERT INTO `sc` VALUES('1','1'),('1','2'),('1','3'),('1','4'),('1','5'),
('2','1'),('2','4'),('2','6'),
('3','1'),
('4','2'),('4','3'),('4','4'),('4','5'),
('5','1'),('5','2'),('5','3'),('5','4'),('5','5'),('5','6'),
('6','1'),('6','2'),('6','3'),('6','4'),('6','5'),('6','6'),
('7','2'),('7','4'),('7','6'),
('8','3'),('8','4'),('8','5'),('8','6');


-- 按年龄从大到小正序查询出第3-6名的学生信息 MYSQL
SELECT s.`sid`,s.`sname`,s.`age`
FROM s
ORDER BY s.`age` ASC
LIMIT 2,4;
-- 查询出选课超过三门的学生的信息用EXISTS
SELECT s.`sid`,s.`sname`,s.`age`
FROM s
WHERE EXISTS (SELECT sc.`sid` FROM sc WHERE s.`sid`=sc.`sid` GROUP BY sc.`sid` HAVING COUNT(sc.`cid`) > 3);


-- 查询出选课超过三门的学生的信息用IN
SELECT s.`sid`,s.`sname`,s.`age`
FROM s
WHERE s.`sid` IN (SELECT sc.`sid` FROM sc GROUP BY sc.`sid` HAVING COUNT(sc.`cid`) > 3);

4.

CREATE DATABASE IF NOT EXISTS company;
USE company;
CREATE TABLE ledger(
ledger_id INT(10) NOT NULL AUTO_INCREMENT,
product_id INT(10) NOT NULL,
number INT(10) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_date DATE NOT NULL,
PRIMARY KEY(ledger_id)
);
ALTER TABLE ledger MODIFY COLUMN create_date DATETIME NOT NULL;
INSERT INTO ledger VALUES
(NULL,'1000000000','1','20.00','2018-03-26 01:00:00'),
(NULL,'1000000000','3','60.00','2018-03-26 22:27:46'),
(NULL,'1000000001','2','30.00','2018-03-26 22:27:46'),
(NULL,'1000000004','3','48.60','2018-03-27 22:27:46'),
(NULL,'1000000005','6','220.00','2018-03-27 22:27:46'),
(NULL,'1000000006','10','15.00','2018-03-27 22:27:46'),
(NULL,'1000000007','1','20.00','2018-03-27 22:27:46'),
(NULL,'1000000007','4','80.00','2018-03-28 09:27:43'),
(NULL,'1000000006','20','30.00','2018-03-28 15:27:58'),
(NULL,'1000000008','1','200.00','2018-03-28 18:27:58'),
(NULL,'1000000008','3','600.00','2018-03-28 22:27:58'),
(NULL,'1000000009','20','300.00','2018-03-29 08:27:58'),
(NULL,'1000000010','1','80.00','2018-03-29 13:27:58'),
(NULL,'1000000010','1','80.00','2018-03-29 22:27:58'),
(NULL,'1000000004','1','16.20','2018-03-29 22:49:58');

统计每天的销售额

SELECT SUM(l.amount) total,DATE_FORMAT(create_date,'%Y-%m-%d') 'date'
FROM ledger l
GROUP BY DATE_FORMAT(create_date,'%Y-%m-%d');

5.根据如下订单表，请使用sql语法，按购买时间统计出每天的新客人数和老客人数

名词定义如下：

新客：当天有购买，且之前没有购买的人为新客，新客人数加1

老客：当天有购买，且之前也有购买的人为老客，老客人数加1

CREATE TABLE order_journal(
trading_time DATETIME NOT NULL,
customer VARCHAR(64) NOT NULL
);
INSERT INTO order_journal VALUES
('2017-01-01 22:01:23','张三'),('2017-01-02 14:02:20','张三'),
('2017-01-02 23:15:55','李四'),('2017-01-02 23:51:20','李四'),
('2017-01-03 23:07:15','李四'),('2017-01-03 20:13:28','王五');

sql:这题比较难，日他大爷，想了好长时间放弃了

-- 先把每天的新的客人罗列出来，然后再按天统计这些人数  
SELECT t7.trading_times,new_customer_number,(CASE  WHEN old_customer_number IS NULL THEN 0 ELSE old_customer_number END) old_customer_number  
FROM  
(SELECT DATE_FORMAT(t2.trading_time,'%Y-%m-%d') trading_times,COUNT(t2.customer) new_customer_number  
FROM   
(SELECT j.`trading_time`,j.`customer`  
FROM order_journal j  
WHERE 1 > (SELECT COUNT(1) FROM order_journal t1 WHERE t1.`customer` = j.`customer` AND t1.`trading_time` < j.`trading_time`)) t2  
GROUP BY trading_times) t7  
LEFT JOIN  
(SELECT DATE_FORMAT(t6.trading_time,'%Y-%m-%d') trading_times,COUNT(t6.customer) old_customer_number  
FROM   
(SELECT t3.`trading_time`,t3.`customer`  
FROM order_journal t3  
WHERE 0 != (SELECT COUNT(1) FROM order_journal t4 WHERE t4.`customer` = t3.`customer` AND t4.`trading_time` < t3.`trading_time`)  
    AND t3.`trading_time` =   
        (SELECT MIN(t5.`trading_time`) FROM order_journal t5   
         WHERE t5.`customer` = t3.`customer` AND DATE_FORMAT(t3.`trading_time`,'%Y-%m-%d')=DATE_FORMAT(t5.`trading_time`,'%Y-%m-%d') )   
) t6  
GROUP BY trading_times) t8  
ON t7.trading_times = t8.trading_times 
UNION  
SELECT t8.trading_times,(CASE  WHEN new_customer_number IS NULL THEN 0 ELSE new_customer_number END) new_customer_number,old_customer_number  
FROM  
(SELECT DATE_FORMAT(t2.trading_time,'%Y-%m-%d') trading_times,COUNT(t2.customer) new_customer_number  
FROM   
(SELECT j.`trading_time`,j.`customer`  
FROM order_journal j  
WHERE 1 > (SELECT COUNT(1) FROM order_journal t1 WHERE t1.`customer` = j.`customer` AND t1.`trading_time` < j.`trading_time`)) t2  
GROUP BY trading_times) t7  
RIGHT JOIN  
(SELECT DATE_FORMAT(t6.trading_time,'%Y-%m-%d') trading_times,COUNT(t6.customer) old_customer_number  
FROM   
(SELECT t3.`trading_time`,t3.`customer`  
FROM order_journal t3  
WHERE 0 != (SELECT COUNT(1) FROM order_journal t4 WHERE t4.`customer` = t3.`customer` AND t4.`trading_time` < t3.`trading_time`)  
    AND t3.`trading_time` =   
        (SELECT MIN(t5.`trading_time`) FROM order_journal t5   
         WHERE t5.`customer` = t3.`customer` AND DATE_FORMAT(t3.`trading_time`,'%Y-%m-%d')=DATE_FORMAT(t5.`trading_time`,'%Y-%m-%d') )   
) t6  
GROUP BY trading_times) t8  
ON t7.trading_times = t8.trading_times
ORDER BY trading_times;

运行结果如下，今天多次试错才发现Mysql不支持FULL  OUTER JOIN 操作

可能有简单做法，不过1点多了，睡觉了，明天还有面试。

数据多的时候发现个问题，

SELECT DATE_FORMAT(t2.trading_time,'%Y-%m-%d') trading_times ........ group by trading_times

我原来没有加trading_time没加s，和数据库字段名一样,然后我觉得group by 后的trading_time已经是'%Y-%m-%d'格式了，谁知还是datetime，所以有问题，重新改了个字段名，ok了