小米的一道mysql题

月黑风高杀人夜 发布于 2015/08/04 10:48
阅读 560
收藏 1
四、数据库

某消费系统中包含2张表,用户信息表和消费流水表,结构和数据如下:

用户表:user

ID 

(自增主键)

NAME 

(非空)

Balance 

(非空)

1 A 19.50
2 A 20.50
3 B 100.00
4 C 200.00

 

流水表:charge

ID 

(自增主键)

User_id 

(非空)

FEE 

(非空)

Update_date
1 1 11.00 2013-10-01
2 2 21.00 2013-10-05
3 3 31.00 2013-10-06

 

1)请按要求写出上面两张表的建表sql语句;

2)请分别写出下面两组sql语句的输出结果;

Delete from user;

Insert into user value (‘’, ‘D’, 300);

 

Truncate user;

Insert into user value (‘’, ‘D’, 300);

3)请写出能产生下面查询结果的SQL语句:

Name Count Balance 

 

FEE
A 2 40.00 32.00
B 1 100.00 31.00
C 1 250.00 0.00

4)系统运行一段时间后,流水表的记录变得非常大,通过用户名(NAME)查询用户消费记录(FEE)时响应速度非常慢,请简述你的优化方案。

加载中
0
eechen
eechen
1)
CREATE DATABASE
IF NOT EXISTS mybase
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE `user` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `balance` decimal(10,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `user` (`name`, `balance`) VALUES ('A', 19.50);
INSERT INTO `user` (`name`, `balance`) VALUES ('A', 20.50);
INSERT INTO `user` (`name`, `balance`) VALUES ('B', 100.00);
INSERT INTO `user` (`name`, `balance`) VALUES ('C', 200.00);

CREATE TABLE `charge` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `fee` decimal(10,2) NOT NULL,
 `update_date` char(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (1, '11.00', '2013-10-01');
INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (2, '21.00', '2013-10-05');
INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (3, '31.00', '2013-10-06');

如果需要外键约束user_id:
CREATE TABLE `charge_fk` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `fee` decimal(10,2) NOT NULL,
 `update_date` char(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`user_id`),
 CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (1, '11.00', '2013-10-01');
INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (2, '21.00', '2013-10-05');
INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (3, '31.00', '2013-10-06');

3)
SELECT
`name` as `Name`,
COUNT(`name`) as `Count`,
SUM(`balance`) as `Balance`,
IFNULL(SUM(`fee`), 0.00) as `Fee`
FROM `user`
LEFT JOIN `charge` on `user`.`id` = `charge`.`user_id`
GROUP BY `name`
ORDER BY `Count` DESC;
查询结果:
A  2  40.00   2.00
B  1  100.00  31.00
C  1  200.00  0.00
IFNULL(SUM(`fee`), 0.00)表示如果为NULL,则设为0.00.
COALESCE(SUM(`fee`), 0.00)表示合并SUM(`fee`)和0.00.

4)
SELECT `name`, COUNT(`name`), SUM(`balance`), SUM(`fee`) FROM `user`
INNER JOIN `charge` on `user`.`id` = `charge`.`user_id`
WHERE `name` = 'A';
给user表的字段name和charge表的user_id建立索引.

2)
DELETE FROM `user`;  #删除表记录
INSERT INTO `user` VALUES ('', 'D', 300); #结果 5  D  300.00
TRUNCATE    `user`;  #清空表记录
INSERT INTO `user` VALUES ('', 'D', 300); #结果 1  D  300.00

0
双_双
双_双
校招?这难度应该不是社招吧
0
月黑风高杀人夜
月黑风高杀人夜
嗯 只是随便的运维一道题
0
陨落人间
陨落人间
这个很基础,如果纯手写,完全正确的把握不大。。
月黑风高杀人夜
月黑风高杀人夜
写写呗 [18][21]
0
pantrick
pantrick
开头看小米,我还兴奋了一下,看题目内容,匿了
0
潇洒非常
潇洒非常
都是一些很基础的题吧,只是这东西要手写出来还真的有些蛋疼了 。
0
月黑风高杀人夜
月黑风高杀人夜

引用来自“eechen”的评论

1)
CREATE DATABASE
IF NOT EXISTS mybase
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE `user` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `balance` decimal(10,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `user` (`name`, `balance`) VALUES ('A', 19.50);
INSERT INTO `user` (`name`, `balance`) VALUES ('A', 20.50);
INSERT INTO `user` (`name`, `balance`) VALUES ('B', 100.00);
INSERT INTO `user` (`name`, `balance`) VALUES ('C', 200.00);

CREATE TABLE `charge` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `fee` decimal(10,2) NOT NULL,
 `update_date` char(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (1, '11.00', '2013-10-01');
INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (2, '21.00', '2013-10-05');
INSERT INTO `charge` (`user_id`, `fee`, `update_date`) VALUES (3, '31.00', '2013-10-06');

如果需要外键约束user_id:
CREATE TABLE `charge_fk` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `fee` decimal(10,2) NOT NULL,
 `update_date` char(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`user_id`),
 CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (1, '11.00', '2013-10-01');
INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (2, '21.00', '2013-10-05');
INSERT INTO `charge_fk` (`user_id`, `fee`, `update_date`) VALUES (3, '31.00', '2013-10-06');

3)
SELECT
`name` as `Name`,
COUNT(`name`) as `Count`,
SUM(`balance`) as `Balance`,
IFNULL(SUM(`fee`), 0.00) as `Fee`
FROM `user`
LEFT JOIN `charge` on `user`.`id` = `charge`.`user_id`
GROUP BY `name`
ORDER BY `Count` DESC;
查询结果:
A  2  40.00   2.00
B  1  100.00  31.00
C  1  200.00  0.00
IFNULL(SUM(`fee`), 0.00)表示如果为NULL,则设为0.00.
COALESCE(SUM(`fee`), 0.00)表示合并SUM(`fee`)和0.00.

4)
SELECT `name`, COUNT(`name`), SUM(`balance`), SUM(`fee`) FROM `user`
INNER JOIN `charge` on `user`.`id` = `charge`.`user_id`
WHERE `name` = 'A';
给user表的字段name和charge表的user_id建立索引.

2)
DELETE FROM `user`;  #删除表记录
INSERT INTO `user` VALUES ('', 'D', 300); #结果 5  D  300.00
TRUNCATE    `user`;  #清空表记录
INSERT INTO `user` VALUES ('', 'D', 300); #结果 1  D  300.00

prefect !!!!
0
尚有智
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(32) NOT NULL COMMENT '名称',
    `balance` double NOT NULL COMMENT '金额',
  PRIMARY KEY (`uid`),
KEY `uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'A','19.50');
INSERT INTO `user` VALUES ('2', 'A','20.50');
INSERT INTO `user` VALUES ('3', 'B','100.00');
INSERT INTO `user` VALUES ('4', 'C','200.00');

/*************************************************/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `charge`
-- ----------------------------
DROP TABLE IF EXISTS `charge`;
CREATE TABLE `charge` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `uid` int(11) unsigned NOT NULL COMMENT 'uid',
    `fee` double  NOT NULL COMMENT '金额',
    `time` datetime NOT NULL COMMENT'时间',
  PRIMARY KEY (`id`),
     KEY `uid` (`uid`) USING BTREE,
    KEY `id` (`id`) USING BTREE,
  CONSTRAINT `uid` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of charge
-- ----------------------------
INSERT INTO `charge` VALUES ('1', '1', '11.00','2013-10-01');
INSERT INTO `charge` VALUES ('2', '2', '21.00','2013-10-05');
INSERT INTO `charge` VALUES ('3', '3', '31.00','2013-10-06');


/*
查询语句
*/
select a.name,count(a.name) as count,SUM(a.balance) as balance,IFNULL(SUM(b.fee),0.00) as fee
 from user as a LEFT JOIN charge as b
on a.uid = b.uid
GROUP  BY a.name
ORDER BY 'count' DESC

/**
删除成功,插入记录5,D,300

*/
delete from user;
insert into user(name,balance) value('D','300');


/*
清除成功,插入记录 1,D,300

*/
TRUNCATE user;
INSERT into user(name,balance) value('D','300');
返回顶部
顶部