[数据库]-数据类型与常见约束

数据类型

整型

# 常见数据类型
 
/*
数值型：
        整型
        小数：
                定点数
                浮点数
字符型：
        较短文本：CHAR,VARCHAR
        较长文本：TEXT,BLOB(较长的二进制数据)
日期型：
*/
 
# 一，整型
/*
分类：
TINYINT,SMALLINT,MEDIUMINT,INT/INTEGER,BIGINT
字节数从左至右以此增大，分别是1，2，3，4，8字节 
特点：
1. 不设置无符号类型，默认采用有符号类型。如果想设置无符号类型，需要添加UNSIGNED关键字
2. 如果插入数值超出范围会报out of range 异常，并且插入临界值
3. 如果不设置长度，会添加默认长度
4. 注意，这里设置的长度只是显示的长度，文本实际占用字节数并没有改变
仍然是按照规定大小创建，设置了长度后，如果输入内容超出长度限制，内容没有变化
，如果长度不足，剩余位数会用0填充
5. 即使用0填充后，输出内容我们直接也是看不到0的，想要看到0作为填充位，需要使用
ZEROFILL关键字，并且这个关键字会默认设置整型为无符号整型
*/
 
 
 
# 1. 如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
    t1 INT,
    t2 INT UNSIGNED,
    t3 INT(8),
    t4 INT(5) ZEROFILL
);
INSERT INTO tab_int VALUES(-15498,45454,-445,123);
 
DESC tab_int;
SELECT* FROM tab_int;
 
 
 
# 二，小数
/*
类型：
        浮点数类型：
            FLOAT(M,D) 4byte
            DOUBLE(M,D) 8byte 
        定点数类型：
            DEC(M,D)/DECIMAL(M,D)   （M+2）byte
            （最大取值范围和DOUBLE相同，给定decimal的有效值范围
            由M和D决定，精确度较高与DOUBLE）
特点：
1. M表示的是小数点和整数位数的和，小数为是2，所以整数位最多只能是3，所以超过后用最大值999.99代替
2. D表示的含义就是小数点保留位数，不足会自动补全，超出会四舍五入
3. M和D都可以省略
省略M和D后，DECIMAL默认填充(10,0)
FLOAT和DOUBLE会根据插入数据自动调整
4. 定点型精确度较高，如果插入数据要求较高精确度例如:货币运算可以采用
*/
 
DROP TABLE IF EXISTS tab_float;
CREATE TABLE tab_float(
        f1 FLOAT(5,2),
        f2 DOUBLE(5,2),
        f3 DECIMAL(5,2)
);
 
INSERT INTO tab_float VALUES(123.45,123.45,123.45);   # 能够正常插入
INSERT INTO tab_float VALUES(123.4,123.4,123.4);  # D表示的含义就是小数点保留位数，不足会自动补全，超出会四舍五入
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
INSERT INTO tab_float VALUES(1234.45,1234.45,1234.45);  
# M表示的是小数点和整数位数的和，小数为是2，所以整数位最多只能是3，所以超过后用最大值999.99代替
 
 
 
# 选择类型的原则
/*
所选择的类型越简单越好
所选择的类型越小越好
*/

字符型

# 串数据（文本类型+二进制数据）
 
/*
较短的文本：
CHAR(M)  表示固定长度字符
VARCHAR(M)  表示可变长度的字符
M 表示最长的字符数,CHAR可以省略，默认为1.VARCHAR不可以省略
较长的文本：
TEXT
BLOB(较大的二进制)
还有binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
特点
CHAR比较耗费空间，但是效率较高
VARCHAR更节省空间，效率略低于CHAR
*/
 
 
 
DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
        c1 ENUM('a','b','c','d')  # 枚举类型，同样是字符型，只能插入枚举的内容
        # 不区分大小写
 
);
INSERT INTO tab_char VALUES('a');
 
 
 
# SET类型类似枚举类型，但是能一次向一个单元插入多个值
# 同样不区分大小写
DROP TABLE IF EXISTS tab_set;
CREATE TABLE tab_set(
        s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('b');
INSERT INTO tab_set VALUES('a,b,c');

日期型

# 日期型
/*
DATE 4字节  只能保存日期
TIME 3字节  只能保存时间
YEAR 1字节  只能保存年
DATETIME 8字节 保存日期和时间 1000-9999
TIMESTAMP 4字节 时间戳 1970-2038
TIMESTAMP支持的时间范围较小，但是受实际的时区和MySQL版本影响，更能反映出实际的日期
DATETIME 则只能反映插入时的当时地区
*/
 
DROP TABLE IF EXISTS tab_date;
CREATE TABLE tab_date(
        t1 DATETIME,
        t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
 
SELECT * FROM tab_date;
 
 
SHOW VARIABLES LIKE 'time_zone';  # 显示当前时区
SET time_zone='+9:00';  # 修改当前时区，可以看到修改完时区后，TIMESTAMP数据跟随时区变化，DATETIME数据不变化

常见约束介绍

# 常见的约束介绍
 
 
/*
约束含义：一种限制，用于限制表中的数据,为了保证表中数据的准确和可靠性（一致性）
CREATE TABLE text(
        字段名 字段类型 约束
);
六大约束：
        NOT NULL:非空约束，保证该字段的值不能为空
        DEFAULT:默认约束，用于该字段有默认值
        PRIMARY KEY:主键，用于保证该字段的值具有唯一性并且非空
        UNIQUE:保证该字段的值具有唯一性，但是可以为空值
        CHECK:【Mysql不支持】检查约束，比如年龄，性别可以用来限制
        FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
        （在从表添加外键约束，用于引用主表中的某列的值）
添加约束的时机：
        1. 新建表时
        2. 修改表时
约束的添加分类：
        列级约束：六大约束语法都支持，但外键约束没有实际效果
        表级约束：除了非空，默认约束，其他都支持
*/
 
 
# 创建表时添加约束
 
# 创建列级约束
/*
直接在字段名和类型后面追加，约束类型即可
只支持：默认，非空，主键，唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE stu_info(
        id INT PRIMARY KEY,  # 添加主键约束
        stuName VARCHAR(20) NOT NULL,  # 非空约束
        gender CHAR(1) CHECK(gender='男' OR gender='女'),  # 检查约束，在Mysql中没有效果
        seat INT UNIQUE,  # 唯一约束
        age INT DEFAULT 18,  # 默认约束
        majorID INT REFERENCES major(id)  # 外键约束，没有实际效果
);
 
CREATE TABLE major(
        id INT PRIMARY KEY,
        majorName VARCHAR(20)
);
 
 
# 查看stuinfo表中所有的索引，包括主键，外键，唯一
SHOW INDEX FROM stu_info;
 
 
# 添加表级约束
/*
语法：
在各个字段的最下面添加
【CONSTRAINT 约束名】 约束类型(字段名)
一般情况下，为了简便，我们只把外键放在表级约束中
*/
DROP TABLE IF EXISTS stu_info;
CREATE TABLE stu_info(
        id INT,
        stuName VARCHAR(20) ,
        gender CHAR(1) ,
        seat INT ,
        age INT ,
        majorID INT,
 
        CONSTRAINT pk PRIMARY KEY(id),  # 主键
        CONSTRAINT uq UNIQUE(seat),   # 唯一键
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)  # 外键
 
);
 
 
# 主键和唯一键的区别
/*
1. 主键不允许为空，唯一键允许为空
2. 都允许组合键(只要列中有一项信息不同即可，完全相同仍旧不可以插入)不推荐使用
*/
 
 
 
# 外键
/*
要求在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容
主表的关联列必须是一个KEY（主键/唯一键）
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
*/
 
 
# 修改表时添加约束
/*
列级约束：
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名);
*/
 
 
DROP TABLE IF EXISTS stu_info;
CREATE TABLE stu_info(
        id INT,
        stuName VARCHAR(20) ,
        gender CHAR(1) ,
        seat INT,
        age INT ,
        majorID INT
);
 
# 用列级约束修改
# 与直接修改表的格式一样，都是重新定义表的类型然后直接再后面增加约束即可
ALTER TABLE stu_info MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
 
# 主键和唯一键等还可以用表级约束修改
ALTER TABLE stu_info ADD PRIMARY KEY(id);
ALTER TABLE stu_info ADD UNIQUE(id);
 
# 外键只有用表记约束才有效果
ALTER TABLE stu_info ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
 
 
 
# 修改表时删除约束
# 删除列级约束仍然向上面修改时一样，只需要不写，或者用NULL代替即可
# 修改表级约束需要用到DROP关键字
ALTER TABLE stu_info DROP PRIMARY KEY;
ALTER TABLE stu_info DROP INDEX seat;
# 删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_stuinfo_major;