mysql选择题解析_mysql一道题的解析

某消费系统中包含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

200.00

0.00

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

先建个库

CREATE DATABASE

IF NOT EXISTS mybase

DEFAULT CHARACTER SET utf8

COLLATE utf8_general_ci;

MySQL这样选择数据库字符集和数据库校对规则：

·如果指定了CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。

·如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。

·否则，采用服务器字符集和服务器校对规则。

如果在CREATE TABLE语句中没有指定表字符集和校对规则，则使用数据库字符集和校对规则作为默认值。

(1)

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;

注：此时如果我们添加数据的时候添加错了，然后我们用 delect from user where id=“？” 删除后，再添加新的数据主键自增字段不会重置 如下图：

此时我们应该使用

mysql> truncate table uses; //清空操作

接下来重新插入数据即可

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, //unsigned ：非负数

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

输出结果

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  5 | D    |  300.00 |

+----+------+---------+

1 row in set (0.00 sec)

正如上面提到了新的数据主键自增字段不会重置

使用Truncate user;

再插入结果如下

mysql> select * from user;

+----+------+---------+

| id | name | balance |

+----+------+---------+

|  1 | D    |  300.00 |

+----+------+---------+

1 row in set (0.00 sec)

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` //LEFT JOIN 左连接 以user表为基础匹配和 charge比较，找出符合 id的值等于user id的记录(条件)。

GROUP BY `name` //把满足上述过程的记录弄到一个分组里

ORDER BY `Count` DESC;  //     ORDER BY对结果集进行排序  asc 按升序排列 desc 按降序排列

IFNULL(SUM(`fee`), 0.00)表示如果为NULL,则设为0.00.

COALESCE(SUM(`fee`), 0.00)表示合并SUM(`fee`)和0.00.

查询结果:

A  2  40.00   2.00

B  1  100.00  31.00

C  1  200.00  0.00

SELECT `name`,

COUNT(`name`),

SUM(`balance`),

SUM(`fee`) FROM `user`

INNER JOIN `charge` on `user`.`id` = `charge`.`user_id`

WHERE `name` = 'A';

(4)

给user表的字段name和charge表的user_id建立索引.

如果需要外键约束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');

感谢热心的解答者                                  原答案作者：@eechen 感谢开源力量