Day.20 从零开始 - 实务需求学SQL_1

今天的主题来透过应用实例复习常用SQL语法，普通的解释相对无聊～所以我们边举例边看过程中可能遇到的问题。

• 情境模拟

需求: 设计一个投资游戏活动管控介面。

内容: 活动的(新增/删除/查询/更新)。 ( 这边不会提到程序API设计！！会偏题

• 资料表设计

接到需求时，想一下可能需要用到什麽栏位？

Example. 针对此需求会用到以下栏位-

活动ID , 活动名称 , 活动语言 , 更新时间 , 建立时间 , 活动开始时间 , 活动结束时间 , 游戏名称 , 游戏玩法 , 活动状态 , 平台ID ...。

• 新增资料库
  • active

CREATE DATABASE active;

• 新增资料表:
  • act(活动状态)
  • act_game(游戏项目内容)
  • act_name(活动内容)

(补充)

这边补充一下新手建表时栏位型别上，一开始会遇到的问题～

栏位宣告时 INT(11) / DECIMAL(3,2) / VARCHAR(10) 後面带的数字会影响长度还是？

1. VARCHAR(10) => 栏位值可以储存10个字符。
2. DECIMAL(3,2) => 栏位总共显示3位数(含小数点後2位)。ex.DECIMAL(3,2)范围: -9.99~9.99
3. 这边以INT型态解释差异=> INT(11) vs. INT(20) vs. INT(20) UNSIGNED ZEROFILL

1. 栏位名称 int(11)
2. 栏位名称 int(11) UNSIGNED ZEROFILL。
   • UNSIGNED -> 避免存入负的数值(对於浮点数型态而言只是避免负值)，而且对整数型态来说还可以储存更大的正整数值。ex. INT(11) UNSIGNED (0~4294967295)
   • ZEROFILL -> 表示如果存储的数字少於N个字符，将在数字左侧补零。
     

重点整理:

• 像我们一般时的宣告ex. INT(11), INT(20) 不会影响使用上的值范围！！
  • INT(11) & INT(20) 所代表的栏位储存最(大/小)范围值皆为(-2147483648 ~ 2147483647)。
• 真的会影响是当你加上UNSIGNED ZEROFILL时才有作用！ 不仅范围值会改变，还会有补0作用。

回到建立资料表部分，我们知道资料表上大概要有哪些栏位了那重点来了primary key, index 如何设定 ?

在设计资料表的过程中索引的设计会跟查询效率有直接的关系，随着数据量的增加一条查询SQL语句有无吃到index对执行上的效率差异是很明显的～

注意: Primary key 在整张表的纪录中一定是唯一的，不可以重复!!

• 新增资料表
  • act (纪录活动状态)

我们知道clustered index决定我们数据库中的数据分布，数据会依照clustered index key值排序，所以这边选择自增栏位的唯一性当做我们的主索引键(PK)。 ps. 不懂为何选择自增栏位更好!? 请参考前2天文章

CREATE TABLE `act` (
 `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
 `status` tinyint(4) NOT NULL COMMENT '活动状态',
 `start_time` int(11) unsigned NOT NULL COMMENT '活动开始时间',
 `end_time` int(11) unsigned NOT NULL COMMENT '活动结束时间',
 `platform_id` bigint(20) NOT NULL COMMENT '平台ID',
 `updated_at` int(11) unsigned NOT NULL COMMENT '更新时间',
 `created_at` int(11) unsigned NOT NULL COMMENT '建立时间',
 PRIMARY KEY (`actives_id`)
) 

(补充)

• 复合索引

一般来说我们使用一个栏位作为普通索引，而两个栏位(含)以上所组成的索引称为复合索引。

• 复合索引采最左匹配原则

1. 栏位顺序会关系到索引是否能用上，在查询条件中索引最左边的列被匹配到，後面的列才可能继续匹配。

2. 假设针对a,b,c栏位建立了复合索引 index(a,b,c) 相当於有index(a)和(a,b)和(a,b,c)会吃到该组索引。

3. 组合上使用频率高最具识别性的栏位放置最左，提升索引使用率。

Example. 针对act表建立一个平台&状态的复合索引～

mysql> ALTER TABLE act ADD INDEX platform_id_status(platform_id,status);

使用查询语法分析可以看到:

第一句SQL 和 第二句SQL: 符合最左匹配原则，皆吃得到该index。

第三句SQL: 因为条件只有status，根据最左匹配原则platform_id没被匹配到，所以吃不到该组index。

• 新增资料表
  • act_game (游戏项目内容)
  • act_name (活动内容)

在act_game这张资料表主要查询投资活动的游戏玩法支援，一个活动可以包含很多款游戏，而同一个活动下是不会有相同游戏的。所以我们可以选择使用(actives_id,game_name)的组合唯一性来作为本表primary key，帮助我们快速定位游戏提供了哪些玩法。

CREATE TABLE `act_game` (
  `actives_id` bigint(20) NOT NULL COMMENT '活动ID',
  `game_name` varchar(40) NOT NULL COMMENT '游戏名称',
  `support_rules` text NOT NULL COMMENT '提供玩法',
  `type` tinyint(4) NOT NULL COMMENT '投资类别',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立时间',
  PRIMARY KEY (`actives_id`,`game_name`)
) 

在act_name这张资料表纪录了活动ID支援各种不同语言的翻译，所以会有以下状况(同id多个对应语言)，所以选择使用(actives_id,language)的组合唯一性来作为本表primary key

CREATE TABLE `act_name` (
  `actives_id` bigint(20) NOT NULL COMMENT '活动ID',
  `actives_name` varchar(40) NOT NULL COMMENT '活动名称',
  `language` varchar(40) NOT NULL COMMENT '语言',
  `created_at` int(11) unsigned NOT NULL COMMENT '建立时间',
  PRIMARY KEY (`actives_id`,`language`)
) 

删除表资料(全部):

mysql> DELETE FROM 表名称;

mysql> TRUNCATE TABLE 表名称;