SQL第5/n篇（更新中）常见字段类型和约束

作者: 粉红狐狸_dhf | 来源:发表于2020-07-14 17:30 被阅读0次

SQL第5/n篇（更新中）常见字段类型和约束
mysql 查询数据库某张表的所有字段、类型、注释及非空
SQL学习笔记——约束（重要!!!）
Django下模型操作
2018-11-26 oracle查询表信息（索引，外键，列等）
四、表操作和表查询
MongoDB 更新字段操作
greendao3.2 数据库增加字段
每天一SQL语句（03）：SQL 语句进阶篇-下
SQL第7/n篇（更新中）变量

常见类型

数值型

整型：tinyint,smallint, mediumint ,int,bigint
小数型：
    定点型:decimal(M,D)
    浮点型:float(M,D),double(M,D)

字符型

较短的文本： char ，varchar ，binary，varbinary
较长的文本： text ，blob(较长的二进制文本）

日期型：

     只表示时间：time
     只表示日期：date
     只表示年份：year 
     时间+日期：datetime ,timestamp

一、整型

1、分类

      tinyint smallint mediumint int/integer bigint
          1      2        3           4        8  保存的字节数

2、特点

①：默认是有符号，要求无符号（全是正数）添加unsigned 关键字
②：超出整型范围的数，会保存临界值
③：可以不设置长度，有默认长度
④：长度代表显示的长度，搭配zerofill使用的话，不够会左边添0，只针对unsigned数

CREATE DATABASE test;
DROP TABLE IF EXISTS tab_int ;
CREATE TABLE tab_int 
(
    t1_sign INT ,   #默认有符号
    t2_unsign INT UNSIGNED, #无符号
    t3_unsign INT(7) ZEROFILL #不够长度左边添0，搭配zerofill使用，且只能无符号数
);

DESC tab_int;

INSERT INTO tab_int VALUES(-1234,-1234);
INSERT INTO tab_int VALUES(-1111,1111,1111);

SELECT * FROM tab_int;

二、小数

1.分类

浮点型： 
    float（M,D）
    double（M,D）
定点型：
    dec（M,D）
    decimal（M,D)

2.特点

①：M：整数+小数位数   D：四舍五入保留的小数位数
   如果超过范围，插入临界值
②：定点型的精度较高，可以默认保留10位，可以在货币预算等精度要求较高的时候使用

CREATE TABLE tab_float(
    f1 FLOAT, #M好像默认是6位
    d1 DOUBLE,
    d2 DECIMAL  #默认（10,0）
);
DESC tab_float;
INSERT INTO tab_float VALUES(1234.1234,1234.1234,1234.1234);
SELECT * FROM tab_float;

原则：所选类型越简单越好，可以节省空间

三、字符型

1、分类

   较短的文本 ： char（M），varchar（M）
      M代表字符数，一个字母是一个字符，一个汉字也是一个字符

对比：

   写法        M的意思                                              特点       空间耗费    效率
    char       char(M)  包含的最大的字符数，可以省略，默认为1  固定长度的字符数    比较耗费    高
  varchar    varhar(M) 包含的最大的字符数，不可省略           可变长度的字符数     比较节省    低

比如：性别只有男女，用char(1)就好

其他：

（1）枚举型，enum：只能插入给定列表中的值，不在列表中的插不进去
    比如sex字段只能插入male，female
（2）set：用于保存集合
（3）binary，varbinary用于保存二进制

DROP TABLE IF EXISTS tab_char;
CREATE TABLE tab_char(
    c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('A');
INSERT INTO tab_char VALUES('m');
SELECT * FROM tab_char;

set

CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('A,C');
INSERT INTO tab_set VALUES('A,B,C,D');
INSERT INTO tab_set VALUES('e,f');
SELECT * FROM tab_set;

四、日期型

date只保存日期
time只保存时间
year只保存年份

datetime 保存日期+时间
timestamp 保存日期+时间

PK：

             字节      范围       时区影响
  datetime    8     1000-9999      不受
  timestamp   4     1970-2038       受

CREATE TABLE tab_time(
    t_time DATETIME,
    t_stamp TIMESTAMP
);
INSERT INTO tab_time VALUES(NOW(),NOW());
SELECT * FROM tab_time;
SHOW VARIABLES LIKE 'time_zone';

SET time_zone ='+9:00';
SHOW VARIABLES LIKE 'time_zone';

INSERT INTO tab_time VALUES(NOW(),NOW());
SELECT * FROM tab_time;

常见约束：六大约束

not null：非空
default ：默认值
primary key :主键唯一且非空
unique：唯一可以为空
check：在mysql中不支持，不报错但没用
foreign key ：限制两个表的关系，在从表添加外键约束，用于引用主表列的值
比如：学生表中的专业编号，员工表中的部门编号

添加约束的时机：

在插入数据之前，创建表或者修改表的时候

约束的添加位置分类：

列级约束：
    六大约束都可以写，但是foreign key 没效果，check 没用
表级约束：
    除了not null，default 都支持

create table 表（

一、列级约束

语法：在字段和类型的后面追加约束
约束类型： primary key，unique，default ，not null

CREATE DATABASE stud;
USE stud;
CREATE TABLE major(
    id INT PRIMARY KEY,
    mj_Name VARCHAR(20)
);
CREATE TABLE student(
    s_id INT PRIMARY KEY,
    gender CHAR(1) CHECK(gender='男' OR gender='女'),
    age INT DEFAULT 18,
    s_name CHAR(10) NOT NULL,
    seat INT UNIQUE,
    major_id INT REFERENCES major(id)
);
DESC student;
#查看student中所有索引，包括 主键，外键，唯一
SHOW INDEX FROM student;

二、表级约束

语法：在所有字段的后面追加约束
【constraint 约束名】约束类型（被约束的字段名）
约束类型： primary key，unique，(foreign key,check 尽管不支持)

写法一：constriant 不省略

DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    gendet CHAR(1),
    s_name VARCHAR(20),
    seat INT,
    age INT,
    major_id INT,
    
    CONSTRAINT pk PRIMARY KEY(id),#z在mysql中对主键改名字不起作用
    CONSTRAINT ck CHECK (gender='男' OR gender='女'),
    CONSTRAINT uq UNIQUE(seat),
    CONSTRAINT fk_student_major FOREIGN KEY(major_id) REFERENCES major(id) 

);
SHOW INDEX FROM student;

写法二：constriant 省略

DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    gendet CHAR(1),
    s_name VARCHAR(20),
    seat INT,
    age INT,
    major_id INT,
    
    PRIMARY KEY(id),#z在mysql中对主键改名字不起作用
    CHECK (gender='男' OR gender='女'),
    UNIQUE(seat),
    FOREIGN KEY(major_id) REFERENCES major(id) 

);
SHOW INDEX FROM student;

写法三：通用写法

DROP TABLE IF EXISTS student;
CREATE TABLE student(
    s_id INT PRIMARY KEY,
    gender CHAR(1) CHECK(gender='男' OR gender='女'),
    age INT DEFAULT 18,
    s_name CHAR(10) NOT NULL,
    seat INT UNIQUE,
    major_id INT ,
    
    CONSTRAINT fk_student_major FOREIGN KEY (major_id) REFERENCES major(id)
);
SHOW INDEX FROM student;

1、主键和唯一键大PK

            保证唯一性   是否允许为空  一个表中可以有多少个  是否允许组合
    主键          √        ×                至多1个        √，但不推荐
    唯一键        √         √               可以有多个      √，但不推荐

2、外键的要求：

①：在从表设置外键关系
②：从表的外键列的类型和主表的关联列类型一致或兼容，名称不同无所谓
③：主表的关联列必须是一个key（主键或者唯一）
④：先插主表信息，再插入从表；删除数据时先删除从表信息

二、修改表添加约束

1、添加列级约束（六个约束都可以，foreign key 不起作用，check 不支持）
alter table 表 modify column 列类型新约束；

2、添加表级约束（not null ，default 外的都可以）
alter table 表 add 【constraint 简称】约束（受约束列名）【外键的引用】
先创建表


DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    gendet CHAR(1),
    s_name VARCHAR(20),
    seat INT,
    age INT,
    major_id INT
)

1、添加非空约束列

ALTER TABLE student MODIFY COLUMN s_name VARCHAR(20) NOT NULL;
DESC student;

2、添加默认约束列

ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18;

3、添加主键约束列

#列级
ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY;
#表级
ALTER TABLE student ADD CONSTRAINT pk  PRIMARY KEY(id);

4、添加唯一约束列

#列级
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE student DROP  COLUMN seat;
ALTER TABLE student ADD COLUMN seat  INT;
#表级
ALTER TABLE student ADD UNIQUE(seat);
#这跟添加列还是不同的，不写column 也不写类型
DESC  student;

5、添加外键约束列

ALTER TABLE student ADD  CONSTRAINT fk_student_major FOREIGN KEY (major_id) REFERENCES major(id);

三、修改表时删除约束

1、删除非空约束列，直接不写

ALTER TABLE student MODIFY COLUMN s_name VARCHAR(20) ;
DESC student;

2、删除默认约束列，直接不写

ALTER TABLE student MODIFY COLUMN age INT ;

3、删除主键约束列

ALTER TABLE student DROP PRIMARY KEY;

4、删除唯一约束列，删除的是索引名字，不知道的话先show一下

ALTER TABLE student DROP INDEX seat;
SHOW INDEX FROM student;
DESC  student;

5、添加外键约束列可能得有外键的简称

ALTER TABLE student DROP FOREIGN KEY fk_student_major;

列级约束与表级约束

            位置        支持的约束类型（两者check 在mysql中不支持）     别名
列级约束    列后面         六种语法上都支持，外键无效果                  不可以
表级约束    所有字段后面    不支持not null ，default              可以，但对主键名无效果