学习MySQL（三）：数据类型约束条件

数据类型

字符串：char(num) 与 varchar(num)

延申面试问题：char与varchar有什么区别？

区别1：定长与变长

- char 固定长度，例如定义了char(8)，则这一列存储的内容长度都为8，不足8位则会用空格补充（查询时是不会有空格的，mysql会对此进行处理）
- varchar 可变长度，是指根据内容的长度进行存储

区别2：存储方式

- char直接存储字符内容
- varchar 开头由1-2个字符存储该字符的总长度，后面接着存储字符内容

总结：

- char的存取数据很快，但由于是定长，当大部分内容没有达到规定长度时，会浪费不少空间资源；
- varchar则不会，它根据实际长度存储，但由于存储的特殊形式造成存取速度不及char，当char存储的内容为一个定值时，则char不仅不浪费空间还提高了存取效果，因为varchar还要留出一部分存储字符串的长度；
- 很早之前，大家都觉得varchar好，节省资源，但是到现在，磁盘资源已经不成问题，因此更倾向于选择char，也就是所谓的以空间换时间了。

整型：int 不需要指定字符的长度，能满足日常绝大部分的整数存储

浮点型：

float(m,n) 单精度
Double(m,n) 双精度
decimal(m,n) 小数值

注：m代表总长度，包含小数部分。n代表小数长度，例如：float(5,3)表示小数位3，整数位为2的数值，总长度为5，例：32.234

-- 举个例子
-- 三者之间的区别
-- 四舍五入
CREATE TABLE test_tb ( NAME CHAR ( 8 ) NOT NULL, score1 FLOAT ( 5,2 ), score2 DOUBLE ( 5,2 ), score3 DECIMAL ( 5,2 ));
INSERT INTO test_tb VALUES ( "lisi", 45.2345, 45.2345, 45.2345 );
SELECT  * FROM  test_tb;


-- 使用默认值
CREATE TABLE test_tb1 ( NAME CHAR ( 8 ) NOT NULL, score1 FLOAT, score2 DOUBLE, score3 DECIMAL );
INSERT INTO test_tb1 VALUES ( "zhangsan", 99.1234567, 99.1234567, 99.1234567 );
SELECT * FROM test_tb1;

总结：

double默认比float更精确；
decimal默认存储的时decimal(10,0)，实际存储的是字符串类型；
日常使用float就可以解决大部分问题了。

日期时间：

date 日期
datetime 日期时间

-- 举个例子
CREATE TABLE test_tb2 ( ruxue date, kaixue datetime );
INSERT INTO test_tb2
VALUES
  ( "2022-08-09 06:56:00", "2022-09-01 06:56:00" );
SELECT * FROM test_tb2;

枚举：

Enum 单选，例如：性别
set 多选例如：爱好

-- 举个例子
CREATE TABLE test_tb3 (
  sex enum ( "男", "女" ),
  hobby 
SET ( "运动", "阅读", "旅行" ));
INSERT INTO test_tb3
VALUES
  ( "男", "运动,旅行" ),
  ( "女", "运动" );
SELECT * FROM test_tb3;

约束条件

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样，都是可选参数。

primary key：主键，指定该列的值可以唯一地表示该列记录

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。

-- 举个例子
CREATE TABLE test_tb4 (
  id INT PRIMARY KEY,
NAME CHAR ( 10 ));
INSERT INTO test_tb4 ( NAME )
VALUES
  ( "张三" );
报错内容：ERROR 1364 ( HY000 ): Field 'id' doesn’t have a DEFAULT VALUE


INSERT INTO test_tb4
VALUES
  ( 1, "张三" ),(
    1,
    "李四" 
  );
-- 报错内容：ERROR 1062 ( 23000 ): Duplicate entry '1' for key 'test_tb4.PRIMARY'

联合主键

CREATE TABLE test_tb5 (
  id INT,
  NAME CHAR ( 10 ),
PRIMARY KEY ( id, NAME ));
INSERT INTO test_tb5
VALUES
  ( 1, '张三' ),(
    1,
    '李四' 
  );
INSERT INTO test_tb5
VALUES
  ( 1, '张三' );
-- 报错内容：ERROR 1062 - Duplicate entry '1-张三' for key 'test_tb5.PRIMARY'

了解内容：auto_increment

CREATE TABLE test_tb6 (  id INT PRIMARY KEY auto_increment, NAME CHAR ( 8 ));
INSERT INTO test_tb6 ( NAME ) VALUES ( '张三' ),( '李四' ),( '王五' );

not mull：非空约束，指定某列不能为空

unique：唯一约束，指定某列或几列组合不能重复

-- 举个例子
CREATE TABLE test_tb7 ( NAME CHAR ( 8 ) UNIQUE );
INSERT INTO test_tb7 VALUES ( "张三" );
INSERT INTO test_tb7 VALUES (  "张三" );
-- 报错信息：ERROR 1062 (23000): Duplicate entry '张三' for key 'test_tb7.NAME' 
联合唯一，只有当你设置的这些字段同时重复时才会报错
CREATE TABLE test_tb8 ( NAME CHAR ( 8 ), class CHAR ( 5 ), UNIQUE ( NAME, class ));
INSERT INTO test_tb8 VALUES ( "张三", "五年级" );
INSERT INTO test_tb8 VALUES ( "张思", "五年级" );


INSERT INTO test_tb8 VALUES ( "张三", "五年级" );

-- 报错信息：ERROR 1062 - Duplicate entry '张三-五年级' for key 'test_tb8.NAME'

default：默认值，对于性别一列，如果大部分都是男性，可以设置为默认值，不填则取默认值，填写了则覆盖默认值

-- 举个例子
CREATE TABLE test_tb9 ( id INT NOT NULL, NAME CHAR ( 8 ), sex enum ( "男", "女" ) DEFAULT "男" );
DESC test_tb9;
INSERT INTO test_tb9 ( sex ) VALUES ( "男" );
-- 报错信息：1364 - Field 'id' doesn't have a default value


INSERT INTO test_tb9 ( id, sex ) VALUES ( 1, "男" );
INSERT INTO test_tb9 ( id, NAME ) VALUES ( 1, "张三" );
SELECT * FROM test_tb9;
INSERT INTO test_tb9 VALUES ( 1, "张三", "女" );

foreign key：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

多表联查进行关联的约束条件

假设我们要描述所有公司的员工，需要描述的属性有：工号、姓名、部门

公司有3个部门，但是有1个亿的员工，那意味着这个字段需要重复存储，部门名字越长，越浪费空间

解决方法：我们完全可以定义一个部门表，然后让员工信息表关联该表，如何关联，即foreign key

前提条件：类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一

-- 举个例子
CREATE TABLE dep (
  id INT PRIMARY KEY auto_increment,
dep_name CHAR ( 10 ));
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment,
  NAME CHAR ( 8 ),
  dep_id INT,
FOREIGN KEY ( dep_id ) REFERENCES dep ( id ));


-- 级联更新、级联删除
CREATE TABLE emp1 (
  id INT PRIMARY KEY auto_increment,
  NAME CHAR ( 8 ),
  dep_id INT,
FOREIGN KEY ( dep_id ) REFERENCES dep ( id ) ON DELETE CASCADE ON UPDATE CASCADE
);

练习题

-- 创建订单库
CREATE DATABASE db_order;
USE db_order;


-- 创建订单表
CREATE TABLE hy_order_2021 ( id VARCHAR ( 100 ), order_money FLOAT ( 6, 2 ), member_id INT, create_time datetime, STATUS INT );


-- 创建订单明细表
CREATE TABLE hy_orderitem_2021 (
  id VARCHAR ( 100 ),
  order_id VARCHAR ( 100 ),
  item_id INT,
  item_name VARCHAR ( 200 ),
  price FLOAT ( 4, 1 ),
item_num INT 
);