MySQL之数据类型，建表以及约束

数据库

目录

数据库

1、MySQL数据类型

2、六大约束

3、表操作

1、创建表

2、删除表

3、修改表

4、复制表

---

1、MySQL数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

①字符类型：char，varchar，text，blob

②数值类型：int，bigint，float，decimal

int -> int

bigint -> long

float -> 成绩

decimal -> 货币类型(精度,小数)

③时间类型：date，time，datetime，timestamp

date -> yyyy:MM:dd HH:mm:ss

time -> HH:mm:ss

datetime -> yyyy:MM:dd

timestamp（时间戳） -> 长整数

2、六大约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

①NOT NULL：非空，用于保证该字段的值不能为空。比如学号，姓名...

②DEFAULT:默认，用于保证该字段有默认值。比如性别

③PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等

④UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号

⑤CHECK:检查约束【mysql中不支持】。比如年龄、性别

⑥FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值在从表添加外

键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号

3、表操作

1、创建表

#通用的写法：★
USE students;#使用students数据库
CREATE TABLE IF NOT EXISTS stuinfo(
  id       INT         PRIMARY KEY,#主键
  stuname  VARCHAR(20) NOT NULL,#非空
  sex      CHAR(1)     check(sex ='男' OR sex = '女'),#检查
  age      INT         DEFAULT 18,#默认
  seat     INT         UNIQUE,#唯一
  majorid  INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

#其他写法
CREATE TABLE stuinfo(
id      INT,
stuname VARCHAR(20),
gender  CHAR(1),
seat    INT,
age     INT,
majorid INT,
    
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

注意：添加外加约束时嘚保证与该表建立外键关系的表存在！！！

上述代码：如果没有major表，那么创建stuinfo表是会报下述错误：

1824 - failed to open the referenced table 'major'

2、删除表

DROP TABLE 表名

注意：如果该表和其他表建立了外键约束！删表要注意顺序。

如果stuinfo表和major表存在主外键约束！

stuinfo表的majorID字段作为该表的外键与major表的主键id建立了约束！

如果进行删除表操作，如果先删除了major表，会报错！！！

3730 - Cannot drop table 'major' referenced by a foreign key constraint 'fk_stuinfo_major' on table 'stuinfo'.

如何理解主外键？↓↓↓

可以理解为建立了依赖关系！学生表中的外键班级idcid与班级表的主键cid建立了外键约束！

那么学生表的班级idcid字段会依赖班级表中的cid字段学生表依赖班级表！！！

如果班级表被删除，那么学生表中的班级idcid字段依赖的数据从何而来！？很明显是不合理的，因此该操作是不

被允许的！删除了学生表，但是班级表并不依赖学生表，操作成功！

3、修改表

/*
 * 语法
 * alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
 */

#修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;

#删除列
ALTER TABLE book_author DROP COLUMN annual;

#修改表名
ALTER TABLE author RENAME TO book_author;

#二、修改表时添加约束

#1、添加列级约束
#alter table 表名 modify column 字段名 字段类型 新约束;

#2、添加表级约束
#alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4、复制表

#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';

#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;