目录
表的约束
空属性
默认值(defualut)
comment:列描述
zerofill :显示约束
主键
自增长:auto_increment
唯一键
外键
查询数据
表的约束
真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性。比如有一个字段是email,要求是唯一的。
表的约束有很多,这里主要介绍如下几个:null/ not null,default,comment,zerofill.primary, key,auto_increment,unique key.
表的约束:表中一定要有各种约束,让我们未来插入数据是符符合预期的,约束本质是通过技术手段,逼迫程序员,插入正确数据。反过来,站在MySQL的视角,凡是插入进来的数据,都是符合数据约束的。
约束的最终的目标:保证数据的完整性和可预期性。
空属性
两个值:null(默认的)和not null(不为空)
数据库默认字段基本都是字段为空,但是实际开发时,尽可能保证字段不为空,因为数据为空没办法参与运算。
默认值(defualut)
默认值:某一种数据会经常性的出现某个具体的值,可以在一开始就制定好,在需要真实数据的时候,用户可以选择性的使用默认值。
create table if not exists t13 (name varchar(20) not null,age tinyint unsigned default 18,gender char(1) default '男'
);
comment:列描述
没有实际含义,专门用来描述字段,会根据表创建语句保存,用来给程序员或dba来继续了解。
create table if not exists t14 (name varchar(20) not null, comment "这是用户姓名"age tinyint unsigned default 18, comment"这是用户年龄"gender char(1) default '男' comment"这是用户性别"
);
我们可以采用show create table (表的名字)来查看描述的字段。
zerofill :显示约束
在 MySQL 中,ZEROFILL
是一种用于数字列的属性,它会在数字前面填充零,以确保列的显示宽度达到指定的长度。这在处理数字时可以使输出更整齐,尤其是在需要对齐数字的情况下。
假设我们有一个表 products
,我们希望存储产品的 ID,并且希望这些 ID 在输出时总是显示为 5 位数,不足的部分用零填充。
CREATE TABLE products (product_id INT(5) ZEROFILL,product_name VARCHAR(50)
);
在这个例子中,product_id
列被定义为 INT(5) ZEROFILL
,这意味着它的显示宽度为 5 位,不足的部分会用零填充。
复制下载
INSERT INTO products (product_id, product_name) VALUES (1, 'Product A');
INSERT INTO products (product_id, product_name) VALUES (23, 'Product B');
INSERT INTO products (product_id, product_name) VALUES (456, 'Product C');
INSERT INTO products (product_id, product_name) VALUES (7890, 'Product D');
SELECT product_id, product_name FROM products;
输出结果
+------------+-------------+
| product_id | product_name|
+------------+-------------+
| 00001 | Product A |
| 00023 | Product B |
| 00456 | Product C |
| 07890 | Product D |
+------------+-------------+
- 在查询结果中,
product_id
列的值总是显示为 5 位数,不足的部分用零填充。 - 例如,
1
被显示为00001
,23
被显示为00023
,依此类推。 ZEROFILL
只影响显示,不会改变存储的实际值。- 如果你在
SELECT
查询中使用CAST
或CONVERT
函数,可能会影响ZEROFILL
的效果。
ZEROFILL
是一个有用的属性,可以帮助你在数据库中以一致的格式显示数字,尤其是在需要对齐和格式化输出时。
主键
主键:primary key 用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能由一个主键;主键所在的列通常是整数类型。
create table if not exists test_key(id int unsigned primary key comment '这是学生的学号',name varchar(20) not null);
当我们插入数据的时候,对应的id内的整数不能重复。反正插入的数据一定是不冲突的。
当表创建好以后但是没有主键的时候,我们可以追加主键。
alter table 表名 add primary key(字段列表)
删除主键
alter table 表名 drop primary key;
复合主键:一个主键可以被添加一列,或者多列上。
CREATE TABLE enrollments (student_id INT NOT NULL,course_id INT NOT NULL,enrollment_date DATE,PRIMARY KEY (student_id, course_id)
);
自增长:auto_increment
auto_increment:当对应字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。
自增长的特点:
任何一个字段要做自增长,前提是本身是一个索引。
自增长字段必须是整数。
一张表最多只能有一个自增长。
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL
);
插入数据
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
INSERT INTO users (username, email) VALUES ('charlie', 'charlie@example.com');
查询数据
SELECT * FROM users;
输出结果
+---------+----------+-------------------+
| user_id | username | email |
+---------+----------+-------------------+
| 1 | alice | alice@example.com |
| 2 | bob | bob@example.com |
| 3 | charlie | charlie@example.com|
+---------+----------+-------------------+
特点
- 在插入数据时,我们没有为
user_id
指定值,MySQL 自动为其分配了 1、2、3 等唯一值。 - 如果我们再次插入新用户,
user_id
会继续自动增长。
唯一键
唯一键(Unique Key)用于确保表中某一列或某几列的值是唯一的,防止重复数据的插入。下面是一个使用唯一键的示例。
假设我们要创建一个 users
表,用于存储用户信息。我们希望每个用户的电子邮件地址是唯一的。
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL UNIQUE
);
user_id
列是自增长的主键。username
列用于存储用户名。email
列设置为UNIQUE
,这意味着每个用户的电子邮件地址必须是唯一的,不能重复。
插入数据
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com');
尝试插入重复的电子邮件
INSERT INTO users (username, email) VALUES ('charlie', 'alice@example.com');
结果
在尝试插入重复的电子邮件时,数据库会返回错误,提示违反唯一约束。
查询数据
SELECT * FROM users;
输出结果
+---------+----------+-------------------+
| user_id | username | email |
+---------+----------+-------------------+
| 1 | alice | alice@example.com |
| 2 | bob | bob@example.com |
+---------+----------+-------------------+
特点
- 在
email
列上设置唯一键后,任何尝试插入重复电子邮件的操作都会失败。 - 唯一键可以用于确保数据的完整性,避免重复记录。
总结
唯一键是数据库设计中非常重要的一部分,确保特定列的值在表中是唯一的,有助于维护数据的完整性和一致性。
外键
外键用于定义主表和从表之间的关系,外键约束主要定义在从表上,主表则必须是有主键约束或者unique约束。当定义外键 后,要求外键列数据必须在主表的主键列存在或为null。
语法:
foreign key (字段名) references 主表(列)
当然可以!以下是使用小写字母的外键示例。
示例:学生和课程表
假设我们有两个表:students
表和 courses
表。我们希望在 enrollments
表中记录学生选修的课程,并使用外键来确保数据的完整性。
创建学生表
create table students (student_id int auto_increment primary key,student_name varchar(50) not null
);
创建课程表
create table courses (course_id int auto_increment primary key,course_name varchar(100) not null
);
创建选课表(带外键)
create table enrollments (enrollment_id int auto_increment primary key,student_id int,course_id int,enrollment_date date,foreign key (student_id) references students(student_id),foreign key (course_id) references courses(course_id)
);
解释
students
表存储学生信息,student_id
是主键。courses
表存储课程信息,course_id
是主键。enrollments
表记录学生选修的课程,student_id
和course_id
列分别是外键,引用students
表和courses
表的主键。
插入数据
insert into students (student_name) values ('alice');
insert into students (student_name) values ('bob');insert into courses (course_name) values ('math');
insert into courses (course_name) values ('science');
插入选课数据
insert into enrollments (student_id, course_id, enrollment_date) values (1, 1, '2023-09-01');
insert into enrollments (student_id, course_id, enrollment_date) values (2, 2, '2023-09-02');
尝试插入无效的外键
insert into enrollments (student_id, course_id, enrollment_date) values (3, 1, '2023-09-03');
结果
在尝试插入无效的 student_id
时,数据库会返回错误,提示违反外键约束,因为 student_id
为 3 的学生并不存在于 students
表中。
查询数据
select * from enrollments;
输出结果
+----------------+------------+-----------+-----------------+
| enrollment_id | student_id | course_id | enrollment_date |
+----------------+------------+-----------+-----------------+
| 1 | 1 | 1 | 2023-09-01|
| 2 | 2 | 2 | 2023-09-02|
+----------------+------------+-----------+-----------------+
特点
- 外键约束确保
enrollments
表中的student_id
和course_id
必须在students
和courses
表中存在。 - 这有助于维护数据的完整性,防止无效的引用。
总结
外键在数据库设计中非常重要,能够确保表之间的关系和数据的完整性。通过使用外键,可以有效地管理和维护数据之间的关联。