MySQL表的创建

一 字段类型

1.数值类型

类型

大小

范围(有符号)

范围(无符号)

用途

tinyint

1字节

-128-127

0,255

最小整数

int

2字节

-2147483648-2147483647

大整数值

float(m,n)

4字节

单精度浮点型(浮点数)

double(m,n)

8字节

双精度浮点型(浮点数)

decimal(m,n)

如果m>n m+2 否则 n+2

浮点数(更加精确)

注意：
  1. int(3) 或者 tinyint(2) :3或者2不会去限制你所存储数据的长度 只有在配合zerofill 零填充的时候 才有意义
  2. 如果 没有配合zerofill的时候 后面不建议 有数值
  3. decimal 在存储浮点数的时候更加精确
  4. 在存储浮点数的时候 如果小数点超出了范围 5包含5以下都会舍去 否则就进一位

2.字符串类型

类型

大小

用途

char

0-255字节

存储定长的字符串

varchar

0-65535字节

变长字符串

text

0-65535字节

长文本数据

blob

0-65535字节

二进制的文本(不建议)

enum(‘w’,’m’)

65535个成员

枚举

set(‘w’,’m’)

64个成员

集合

注意：
(1) char 和 varchar 的区别:
  • char 和 varchar 最大的长度 都为 255
  • char的执行效率高于varchar varchar 相对于 char 节省存储空间
  • 如果使用char 传入的数据的长度 小于指定的长度的时候 存储的实际长度 不够的会拿空格来填充
  • 如果使用 varchar 传入的数据的长度 小于指定的长度的时候 存储的实际长度 为传进来的数据的长度
(2) enum 和 set 的区别
  • enum 只能选择其给定的值中的某一个
  • set 可以选择给定的之中的 某几个

3.日期和时间类型(了解)

类型

大小

范围

格式

用途

date

3

1000-01-01/9999-12-31

YYYY-MM-DD

日期值

time

3

-838:59:59/838:59:59

HH:MM:SS

时间值

year

1

1901/2155

YYYY

年份值

datetime

4

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

混合日期和时间

注意：

存储日期的时候 哦们可以用整形来存储 时间戳 这样可以做到任意转换 以及 计算

二 字段约束

  1. unsigned 无符号
    只能用于设置数值类型，不允许负数的出现
    最大存储的长度 会扩大一倍
  2. zerofill 0填充
    只能用于设置数值类型的时候 位数不足 用0填充
  3. auto_increment 自增
    用于设置字段 自动增长的属性 常用于主键 每增加一条数据 该字段值会自动加1
  4. default 默认值
    如果在插入数据的时候 某些字段没有插入值 那么它的值就为你的默认值
  5. null 和 not null
    默认为null
    not null 那么就必须给该字段一个值
null 的注意事项:
  1. null意味着没有值 或者 未知的值
  2. 可以测试 某个字段是否为null
  3. 不能对 null进行算数运算
  4. 对null进行运算 结果还为null

三 MySQL的索引

主键索引 primary key

唯一索引 unique

普通/常规索引 index

全文索引 fulltext（了解）

(1) 主键索引

主要作用是确定数据表里数据记录的位置

一般来说 每个表里都会有一个主键索引候选的约束条件为(not null auto_increment)

(2) 唯一索引

唯一索引的主要功能：防止重复的数据插入

和主键索引的区别：主键索引每个表只能有一个 但是唯一索引可以有多个

(3) 常规 索引

常规索引 是关系型数据中 最重要的技术 如果说 想要提升数据库的效率 首先想到的就是 添加索引

  1. 多占用磁盘空间
  2. 会减慢 插入 删除 和 修改的效率
例如：
Create Table: CREATE TABLE `userinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `userpass` varchar(50) NOT NULL,
  `telno` varchar(20) NOT NULL,
  `sex` enum('w','m') NOT NULL DEFAULT 'm',
  `birthday` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `telno` (`telno`),  # UNIQUE KEY 索引名称(索引的字段)
  KEY `username` (`username`),   # index      索引名称(索引的字段)
  KEY `userpass` (`userpass`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
注意：在给字段添加索引的时候  如果没有给对应的字段 起索引名的时候 那么索引名为字段名

四 数据的存储

mysql的存储 是以文件的形式来存储

存储的路径为: C:\ProgramData\MySQL\MySQL Server 5.7\Data\库名\表名

.frm 后缀的文件: 存储表的框架结构

innoDB表引擎的文件存储结构

.ibd后缀的文件: 存储表的数据和索引

MyISAM 表引擎的文件存储结构

.MYD: MY Data 用来存储表的数据

.MYI: MY Index 用来存储表的索引

五 innoDB和MyISAM 引擎的区别

innoDB和MyISAM是mysql非常重要的俩个表的存储引擎

1.innoDB 支持事物处理 比较安全

2.MyISAM引擎 执行效率更高 常用于 博客 论坛啦

3.MyISAM 会产生3个文件 innoDB产生俩个文件

alter table 表名 engine = myisam;

六 事物处理

前期工作：

修改当前表的存储引擎是否为innoDB

alter table 表名 engine = innodb

(1) 查询是否为自动提交

select @@autocommit (1为自动提交 0为手动提交)

(2) 设置手动提交

set autocommit = 0

(3) 事物开始

begin

(4) 执行事物的代码

代码块

(5) 执行提交 或者 回滚

commit work 提交

rollback work 回滚

七 对于表结构的操作

  1. 给表添加新的字段（新添加的字段默认排在最后一位）
    关键字：add
    alter table 表名 add 字段名 字段类型 约束条件
  2. 添加的字段排在某个位置
    alter table 表名 add 字段名 字段类型 约束条件
    first 排在第一位
    after 字段名 排在某个字段的后面
  3. 删除某个字段
    关键字:drop
    alter table 表名 drop 字段名
  4. 修改某个字段的类型以及约束条件
    关键字：modify
    alter table 表名 modify 字段名 约束条件
    alter table user modify sex tinyint not null default 1 after username;
  5. 修改字段名称
    关键字 change
    alter table 表名 change 旧字段 新字段 字段类型 约束条件
    alter table user change sex mysex tinyint;
  6. 添加索引
    说明：创建常规索引 可以使用index 后者key 来进行创建
    起索引名称：alter table 表名 add 索引类型 索引名称(索引的字段)
    alter table user add index myusername(username);
    不起索引名：alter table 表名 add 索引类型(索引字段)
    alter table user add index(username);
  7. 删除索引
    alter table 表名 drop key 索引名
    alter table user drop key username;
  8. 表的重命名
    alter table 表名 rename 新表名
  9. 创建 一个 和a一样的表结构 表b
    create table b like a