MySql 数据表的设计

MySql 5.7 中文文档 - searchdoc.cn

MySql 数据库的常见操作

1. 数据值和列类型

2. 数据字段属性

字段 小写 阐述
UNSIGNED unsigned 数字范围:0-255
ZEROFILL zerofill 使用 0 进行左填充
AUTO_INCREMENT auto_increment 自动增量属性
NULLNOT NULL - -
DEFAULT default 设置默认值

3. 创建索引

索引虽好,但不可滥用:
索引应该创建在 搜索排序分组 等操作所涉及的数据列上。也就是在 where 子句,关联检索中的 from 子句order bygroup by 子句中出现过的数据列最适合用来创建这种索引。记住:不要创建太多索引,因为索引是会消耗系统资源的,要适可而止。

索引 小写 阐述
PRIMARY KEY primary key 主键 索引
UNIQUE unique 唯一 索引
INDEXKEY indexkey 常规 索引
FULLTEXT fulltext 全文索引( 只能用于 MyISAM 表 )
并且只能在 CHARVARCHARTEXT 类型的列上创建
MySQL 全文索引(FULLTEXT)

3.1 添加索引

1.添加主键索引 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

2.添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`) 
3.添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

4.添加普通索引(index_name 索引名称)
ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 
5.添加组合索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

3.2 删除索引

# 删除索引 (index_name 索引名称)
ALTER TABLE `table_name` DROP INDEX `index_name`;

3.3 组合索引说明

组合索引的索引文件以B-Tree格式保存,在创建组合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到。

如果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

(1)col_a = "some value";
(2)col_a = "some value" and col_b = "some value";
(3)col_a = "some value" and col_b = "some value" and col_c = "some value";
(4)col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句,mysql会自动优化成第3条的样子。下面的情况就不会用到索引:

col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

4. 数据表对象 管理

操作数据表相关语句

4.1 创建表(CREATE TABLE)

CREATE TABLE IF NOT EXISTS 表名称 (
  字段名1 列类型 [属性] [索引],
  字段名2 列类型 [属性] [索引]
) [表类型] [表字符集];
// 创建表  -  InnoDB  -  MyISAM
CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  username VARCHAR (50) NOT NULL ,
  userpass VARCHAR (50) NOT NULL ,
  telno VARCHAR (20) NOT NULL UNIQUE ,
  sex ENUM('男','女') NOT NULL DEFAULT '男' ,
  birthday DATE NOT NULL DEFAULT '1970-01-01'
) COMMENT '用户表' ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

4.2 修改表(ALTER TABLE)

ALTER TABLE 表名 ACTION;   // 修改表的语法格式
// ACTION 是指 ALTER TABLE 的从句。

4.2.1 为指定的表 增加 一个新字段

ALTER TABLE 表名 字段名 <建表语句> [FIRST | AFTER 列名];

// 例如:在 username 后增加 email 字段
ALTER TABLE users ADD email VARCHAR(30) NOT NULL AFTER `username`;

4.2.2 为指定的表 更改 原有的字段类型

ALTER TABLE 表名 CHANGE(MODIFY) 列表 <建表语句>

// 例如:将 telno 重命名为 phone 并重新设置其字段属性(如果不需要重命名,则写两次 telno 即可)
ALTER TABLE users CHANGE telno phone INT UNSIGNED DEFAULT '0';

4.3 删除表(DROP TABLE)

DROP TABLE IF EXISTS 表名;

5. 数据表的 存储引擎

MyISAM 和 InnoDB 表类型的功能简单对比表

表类型功能对比 MyISAM 表 InnoDB
事务处理 不支持 支持
数据行锁定 不支持,只有表锁定 支持
外键约束 不支持 支持
表空间大小 相对小 相对大,最大是 2 倍
全文索引 支持 不支持
COUNT 问题 执行 COUNT(*) 查询时,速度慢

10. MySql配置文件解析

mysql配置文件详解5.7
Mysql配置文件/etc/my.cnf解析

# 数据库默认字符集,主流字符集支持一些特殊表情符号 (特殊表情符占用4个字节)
character-set-server = utf8mb4
#数据库字符集对应一些排序等规则, 注意要和 character-set-server 对应
collation-server = utf8mb4_general_ci

# innoDB 存储引擎 (MyISAM)
default_storage_engine = InnoDB
# mysql 5.7新增特性, 磁盘临时表默认引擎, 默认InnoDB
internal_tmp_disk_storage_engine = InnoDB

# 设置client连接mysql时的字符集,防止乱码
# init_connect='SET NAMES utf8mb4'

11. 数据库的设计技巧

11.1 数据库的设计要求

  • 数据表里没有重复的、冗余的数据
  • 数据表里没有 order1、order2、order3 等数据列。要知道,就算定义了 10 个这样的数据列,也迟早会发生某个用户想要订购 11 见商品的事情
  • 全体数据表的空间占用越小越好
  • 使用频率高的数据库查询都能以简单高效的方式执行。

11.2 命名的技巧

  • MySQL 对数据列的名字不区分字母的大小写形式,但对数据库和数据表的名字却区分。因此,至少是再给数据库和数据表命名时,应该以一种统一的模式来使用大写和小写字母。
  • 数据库、数据表和数据列的名字最多可以有 64 个字符。
  • 在名字里要避免使用特殊字符。
  • 数据列和数据表的名字应该有意义。
  • 按照一定规范系统地给数据列命名有助于减少粗心产生的错误。(统一命名规范)
讨论数量: 0

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!