MySQL数据表与字段详解-优快云博客

本文链接：https://blog.youkuaiyun.com/HanghangzZ/article/details/108895080

数据表是由行和列组成的二维网络，为什么说MySQL是典型的关系型数据库，就是因为表和表之间存在关系

数据表相关操作

数据表
- 是数据库最重要的成分之一，数据是保存在数据表中的
- 数据表由行(row)和列(column)组成
- 每个数据表中至少有一列，行可以有零行、一行或者多行组成
- 表明要求唯一，不要含有特殊字符，最好含义明确
创建表
- CREATE TABLE [IF NOT EXISTS] tbl_name(
  
  字段名称字段类型 [完整性约束条件],
  
  字段名称字段类型 [完整性约束条件],
  
  …
  
  )ENGINE = 存储引擎 CHARSET = 编码方式;
  - 字段名称，如：用户名(userName),年龄(age),编号(id)等
    
    我们尽量不要使用中文，尽量使用英文
    
    可以使用COMMENT给字段加注释
  - 完整性约束条件
    - UNSIGNED：无符号，没有负数，从0开始
    - ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度
    - NOT NULL：非空约束，也就是插入值的时候这个字段必须要给值，并且值不能为空值
    - DEFAULT：默认值，如果插入记录的时候没有给字段赋值，则使用默认值。一般配合NOT NULL来使用
    - PRIMARY KEY：主键，标识记录的唯一性，一个表只能有一个主键，自动禁止为空
    - UNIQUE KEY：唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
    - AUTO_INCREMENT：自动增长，只能用于数值列，而且配合索引。一般我们给主键一个自动增长，实现一个自动化的效果。默认起始值从1开始，每次增长1。在测试UNIQUE时发现，就算记录插入失败了，AUTO_INCREMENT还是会自动增加。我们如果想让它自动增长，并且在插入记录的时候写了它，只能给它NULL。
    - FOREIGN KEY：外键约束
  - CHECK：CHECK(条件)检查约束，在插入记录时，必须要满足检查约束中的内容，条件的逻辑可以用or或者and来连接
MySQL中的数据类型
- 数值型
  - 整数型 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VAFxvdoX-1601535648940)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923181243612.png)]$
    
    我们可以根据我们数据对象的不同选用不同的类型。比如记录中国的人口，那么就有十几亿，我们就要选择较大的类型。选择记录一个班级，那么我们可以选择TINYINT，并且应该是无符号值，因为人数不会为负数。
  - 浮点数型 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xralRjJo-1601535648944)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923181505157.png)]$
    
    M代表浮点数的总长度，D代表小数点后几位。如：(9,2)代表它的小数位是两位，然后小数点前是七位。
    
    DECIMAL是定点数。
    
    DOUBLE类型是存在误差的，如果对精度要求较高，可以使用定点数，因为它的内部是用字符串的形式储存数值的(我也不知道为什么这样精度更高^{(*￣(oo)￣)})
    
    浮点型的存储范围比整数型要大。
- 字符串类型 $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W0sAp0KI-1601535648946)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923182034771.png)]$
  
  CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型
  
  例如：
  
  我们定义一个地址字段，我们写CHAR(50),代表最大长度为50个字符，而VARCHAR(50)代表0~50个字符之间；
  
  在存储空间上来说，如果只是往CHAR(50)中存入一个a，那么也要占用50个字节；如果往VARCHAR(50)中存入一个a，那么只占用2个字节，即长度+1个字节(那多出来的1个是保存我们的长度的)
  
  由此，CHAR是比较浪费空间的，而VARCHAR则会根据你给的内容的多少来分配空间。
  
  但是有好处也有弊端，因为它们的存储方式，包括它们的检索方式不一样，CHAR类型的检索速度比VARCAHR要快。可以想象成CHAR是拿空间换时间，而VARCHAR是拿时间换空间
  
  CHAR在保存值的时候，如果内容达不到指定的长度，后面会用空格来填充到指定的长度，但是在检索和查询这个数据的时候，它会把后面的空格给去掉。而VARCHAR在保存时不进行填充，尾部的空格会留下。这就是CHAR和VARCHAR在存储方式上的不同。
  
  如何选择？
  
  如果明确的知道存储内容的长度，那么就可以使用CHAR；不固定的长度则可以选用VARCHAR。当然，如果存储的长度大于255，那么就不能选择CHAR了。
  
  下面还有一堆TEXT类型的，可以保存文章、新闻内容。注意：TEXT相关的列不能有默认值；检索的时候不存在大小写转换，即区分大小写；存储效率没有CHAR或者VARCHAR高
  
  **ENUM类型：**在这个类型中，每一个值都有一个序号，按序号来进行排列。保存的时候保存的也不是这个值，而是这个值对应的编号。枚举类型会自动过滤掉空格。
  
  **SET类型：**可以和ENUM类型对照的去看，同样的每一个值都有一个序号，保存的时候保存的也不是这个值，而是这个值对应的编号。同样这个值后面的空格也会把它去掉。集合还有一个特性，在插入的时候，我们不用按照它的顺序来写，它会按照指定的列表中的顺序来重新排列。**应用：**用户的权限。如MySQL数据库中创建数据库，建表，增删改查。我们可以建立集合来给不同的用户不同的权限
- 日期时间类型
- $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-73RNACdA-1601535648949)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923182322451.png)]$
  
  TIME占用的内存是3个字节，如果我们存储单个的时间还是可以的，将来我们可以使用整型并利用时间戳来存储时间
  
  DATETIME就是TIME和DATE的结合版，不过在时间容量上没有TIME大
  
  TIMESTAMP和DATETIME基本一致，插入值的形式是一样的，不过前者的时间和日期的范围比后者更小。并且前者是自动带有时区的，它会根据你系统的时区来获得值
  
  **YEAR:**根据手册范围是1000到9999，但是实际测试是1901~2155。它的占用字节数很小，当我们只考虑存储年份的时候，我们可以使用year。
查看当前数据库下已有数据表
- SHOW TABLES;
  
  手册中的语法
  
  SHOW [EXTENDED] [FULL] TABLES
  [{FROM | IN} db_name]
  [LIKE ‘pattern’ | WHERE expr]
  
  其中FULL是默认的，也可以不写，使用FROM或者IN可以不用打开数据库就查看表,最后一行是带有正则表达式的语句，我们先暂时不用理他
查看指定数据表的详细信息
- SHOW CREATE TABLE tbl_name;
查看表结构
- DESC tbl_name;
- DESCRIBE tbl_name;
- SHOW COLUMNS FROM tbl_name;
删除指定的数据表
- DROP TABLE [IF EXISTS] tbl_name;
查询表中所有记录
- SELECT * FROM tbl_name 其中*表示所有的字段，如果写的是*，那么所有的字段都会被查出来
查询非默认数据库中的表信息
- SELECT * FROM db_name.tbl_name;

因为字段名称会一下子定义很多个，所以我们可以先在编辑器里面打好，然后再复制到命令行中去(但是我复制到命令行后，在编辑器里不同行的语句直接都挤到了一起，难受死了/(ㄒoㄒ)/~~)

实例：用户名表

-- 用户名表
CREATE TABLE IF NOT EXISTS imooc_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM('男','女','保密')
)ENGINE=INNODB,CHARSET=UTF8;	#这里它默认的存储引擎就是这个东东

-- 向表中插入记录
-- INSERT [INTO] tbl_name(id,username,...) VALUES(1,...)
INSERT INTO imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex) 
VALUES(1,'hanghang','hanghang','123@qq.com',20,'012345678901234567','01234567890',0,0,'浙江省','男');

-- 查询表中所有记录 SELECT * FROM tbl_name     其中*表示所有的字段，如果写的是*，那么所有的字段都会被查出来

INSERT INTO imooc_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(-5,'hanghang','hanghang''123@qq.com',20,'012345678901234567','01234567890',0,1,'浙江省','女');

测试数据类型

测试整型

-- 测试整型
CREATE TABLE test_int(
	a TINYINT,
    b SMALLINT,
    c MEDIUMINT,
    d INT,
    f BIGINT
);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tU5y5JUr-1601535648952)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923202800681.png)]$

我们在创建表的时候并没有给上括号，但是在创建后查看表结构，发现每一个后面都跟着一个括号，我们来拿tinyint举例说明。

这里面的(4)表示的是显示宽度，tinyint我们没有指定它是否无符号，它的范围是从-128~127，负数-128显示的时候正好是4位，所以宽度位4。其它几项亦如此

-- 可以只插入一个值
INSERT INTO test_int(a) VALUES(-128);
INSERT INTO test_int(a) VALUES(-129);

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O0QqGdIa-1601535648953)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923203525422.png)]$

超出范围，会报错，并且无法插入

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WFRM0Hn8-1601535648954)(C:\Users\杭杭\AppData\Roaming\Typora\typora-user-images\image-20200923203949052.png)]$

测试无符号整型

-- 测试无符号
CREATE TABLE test_unsigned(
	a tinyint,
    b tinyint unsigned
);
INSERT INTO test_unsigned(a,b) VALUES(-12,-12);
INSERT INTO test_unsigned(a,b) VALUES(0,0);
INSERT INTO test_unsigned(a,b) VALUES(0,256);

同理，超出范围直接报错

测试零填充

-- 测试零填充 ZEROFILL
CREATE TABLE