05-数据库 - 续
一. 数据类型(续)
1. 日期类型
- DATE 用于保存年月日
1999-10-16
- TIME 用于保存时分秒
10:10:10
- DATETIME 用于表示年月日时分秒
1990-10-16 10:10:10
- TIMESTAMP(时间戳) 保存的UTC时间, 记录的自1970年元旦至表示的时间所经过的毫秒
CREATE TABLE userinfo(
id INT, # id类型, 整数类型
name VARCHAR(32), # name字符串类型, 最大宽度为32
gender CHAR(1), # gender字符串类型,最多1位字符
birth DATETIME, # birth 日期类型,保存年月日时分秒
salary DOUBLE(7,2)# salary 浮点型, 最多保存 99999.99
)CHARSET=UTF8;
DESC userinfo;
# 插入日期类型的数据时,DATETIME可以保存 年月日时分秒, 可以用字符串来表示
# 但是格式必须为 "yyyy-MM-dd hh:mm:ss"
INSERT INTO userinfo(id, name, gender, birth, salary) VALUES
(1,'张三','男','1999-10-16 08:35:29',5000.99);
# 插入数据
# 2,李四,男,生日,薪资
INSERT INTO userinfo(id, name, gender, birth, salary) VALUES
(2,'李四','男','1998-05-11 18:10:10',6888.98);
# 3,王五,女,生日,薪资
INSERT INTO userinfo(id, name, gender, birth, salary) VALUES
(3,'王五','女','1998-05-11 18:10:10',6888.98);
SELECT * FROM userinfo;
二. 约束条件
我们可以为表字段添加约束条件, 可以限制对表的操作必须符合我们添加的约束条件,否则数据库会拒绝该操作
1. 主键约束 PrimaryKey
1.1 主键约束
- 主键约束要求该字段的值必须是 非空且唯一
- 主键约束一般会为表中第一个字段添加, 并且通常该字段取名为 “id”
# 创建表 - 主键约束
CREATE TABLE userinfo1(
id INT PRIMARY KEY, # PRIMARY KEY主键
username VARCHAR(32),
password CHAR(32),
moble CHAR(11),
salary DOUBLE(7,2),
create_time DATETIME
)CHARSET=UTF8;
DESC userinfo1;
# 测试主键
INSERT INTO userinfo1(id) VALUES (1);
# 报错: 因为主键唯一的
INSERT INTO userinfo1(id) VALUES (1);
# 报错: 因为主键是非空的
INSERT INTO userinfo1(id) VALUES (null);
INSERT INTO userinfo1(id) VALUES (2);
# 修改id数据为1: 报错: 不可以将重复的值修改到主键字段
UPDATE userinfo1 SET id=1 WHERE id=2;
SELECT * FROM userinfo1;
注意: 主键字段不能插入重复的值, 并且在修改表记录是, 也不可以将重复的值修改到主键字段
1.2 自增长 - AUTO_INCREMENT
具有主键约束的字段通过主键的生成方式由系统完成, 自增长就是其中的一种方式
# 演示自增长
CREATE TABLE userinfo1(
# AUTO_INCREMENT自增长
id INT PRIMARY KEY AUTO_INCREMENT, # PRIMARY KEY主键
username VARCHAR(32),
password CHAR(32),
moble CHAR(11),
salary DOUBLE(7,2),
create_time DATETIME
)CHARSET=UTF8;
DESC userinfo1;
# 未指定id字段, 会自动赋值并保持自增长
INSERT INTO userinfo1(username,password) VALUES ('hanmeimei','123456');
INSERT INTO userinfo1(username,password) VALUES ('lilei','654321');
# 如果想要指定id字段, 可以通过null进行占位
INSERT INTO userinfo1(id,username,password) VALUES (null,'langlang','123456');
INSERT INTO userinfo1(id,username,password) VALUES (null,'lucy','123456');
# 虽然删除了4,但是插入数据的时候还是会插入5
DELETE FROM mydb.userinfo1 WHERE id>3;
INSERT INTO userinfo1(id,username,password) VALUES (null,'lily','123456');
SELECT * FROM mydb.userinfo1;
2. 非空约束 - NOT NULL
被施加非空约束的字段, 该字段的值不能为空
CREATE TABLE userinfo1(
# AUTO_INCREMENT自增长
# PRIMARY KEY主键
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32) NOT NULL , # 不允许为空
password CHAR(32) NOT NULL ,
moble CHAR(11),
salary DOUBLE(7,2) NOT NULL DEFAULT 0.00,#通过default设置默认值
create_time DATETIME
)CHARSET=UTF8;
# 插入数据时,不能向具有非空约束的字段插入null值
# 报错: password不允许为空
INSERT INTO userinfo1(id,username) VALUES (null,'hanmeimei');
# 报错: salary不允许为空
# 当salary设置默认值之后, 程序运行成功,因为 具有默认值, salary不为空
INSERT INTO userinfo1(id,username,password) VALUES (null,'lilei','123456');
DESC userinfo1;
SELECT * FROM userinfo1;
3. 唯一性约束 (UNIQUE)
具有唯一性约束的字段不允许重复, 但是可以为null
# 演示唯一性约束 UNIQUE
CREATE TABLE userinfo1(
# AUTO_INCREMENT自增长
# PRIMARY KEY主键
id INT PRIMARY KEY AUTO_INCREMENT,
# UNIQUE 表示唯一
username VARCHAR(32) NOT NULL UNIQUE , # 不允许为空
password CHAR(32) NOT NULL ,
moble CHAR(11),
salary DOUBLE(7,2) NOT NULL DEFAULT 0.00,#通过default设置默认值
create_time DATETIME
)CHARSET=UTF8;
# 插入数据的时,不允许将重复的值插入到具有唯一性约束的字段中
INSERT INTO userinfo1(username,password) VALUES ('张三','123456');
# 报错: 不可以再次插入张三到username字段中
INSERT INTO userinfo1(username,password) VALUES ('张三','123456');
DESC mydb.userinfo1;
SELECT * FROM mydb.userinfo1;
4. 检查约束(CHECK)
检查约束允许我们自定义约束条件, 仅当满足指定的条件才可以进行操作
# 演示检查约束
CREATE TABLE user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL UNIQUE ,
age INT CHECK(age>0 && age<120)
)CHARSET=UTF8;
# 插入数据, age字段必须符合检查约束的要求
# 运行成功, age符合字段要求
INSERT INTO user(id,name,age) VALUES (null,'张三',22);
# 报错: 以为年龄不符和检查约束的要求 0<age<120
INSERT INTO user(id,name,age) VALUES (null,'李四',160);
DESC user;
SELECT * FROM user;
5. 外键约束
5.1 外键约束
- 概述: 外键约束是一种限制, 他通过对表的行或列的数据做出限制,来确保数据的完整性, 关联性
- 功能: 建立表与表之间的某种约束关系, 由于这种关系的存在, 能够让表与表之间的数据更加完整, 关联性更强
USE mydb;
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(32) NOT NULL
)CHARSET = UTF8;
SHOW TABLES ;
INSERT INTO dept(id, dname) VALUES
(1,'技术部'),
(2,'销售部'),
(3,'市场部'),
(4,'行政部'),
(5,'财务部'),
(6,'总裁办');
SELECT * FROM dept;
# 创建人员表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age TINYINT,
salary DOUBLE(8,2),
dept_id INT
)CHARSET = UTF8;
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(1,'Lily',29,20000,2),
(2,'Tom',27,16000,1),
(3,'Joy',30,28000,1),
(4,'Emma',24,8000,4),
(5,'Abby',28,17000,3);
SELECT * FROM emp;
# 上面的两个表每个人都应该指定其部门, 但是实际上没有约束的情况下, 人员可以
# 没有部门或者可以添加不存在的部门, 这显然是不合理的
5.2 外键约束语法 (FOREIgn KEY)
[CONSTRAINT symbol] FOREIGN KEY(外键字段)
REFERENCES tbl_name(主表的主键)
[ON DELETE {RESTRICT | CASCADE | SET NULL}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL}]
主表和从表
在同一个数据库中, B表的外键 与 A表的主键相对应的, A表为主表, B表为从表
1. 添加外键 - 创建数据表时指定外键
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age TINYINT,
salary DOUBLE(8,2),
dept_id INT,
# 创建数据表时指定外键
CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id)
)CHARSET = UTF8;
2. 添加外键 - 修改数据表时指定外键
# 修改表时添加外键
ALTER TABLE emp ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id);
3. 代码演示
# 插入数据时填写不存在的部门表, 报错
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(null,'Jack',22,8000,0);
4. 解除外键约束
# 通过外键名称解除外键约束
ALTER TABLE emp DROP FOREIGN KEY dept_fk;
# 解除外键约束后, 数据插入成功
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(null,'Jack',22,8000,0);
5. 综合代码
USE mydb;
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(32) NOT NULL
)CHARSET = UTF8;
SHOW TABLES ;
INSERT INTO dept(id, dname) VALUES
(1,'技术部'),
(2,'销售部'),
(3,'市场部'),
(4,'行政部'),
(5,'财务部'),
(6,'总裁办');
SELECT * FROM dept;
# 创建人员表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
age TINYINT,
salary DOUBLE(8,2),
dept_id INT
# 创建数据表时指定id字段
# CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id)
)CHARSET = UTF8;
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(1,'Lily',29,20000,2),
(2,'Tom',27,16000,1),
(3,'Joy',30,28000,1),
(4,'Emma',24,8000,4),
(5,'Abby',28,17000,3);
SELECT * FROM emp;
# 修改表时添加外键
ALTER TABLE emp ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id);
# 上面的两个表每个人都应该指定其部门, 但是实际上没有约束的情况下, 人员可以
# 没有部门或者可以添加不存在的部门, 这显然是不合理的
# 报错: 因为外键约束,部门id必须是dept表中存在的id
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(null,'Jack',22,8000,0);
# 通过外键名称解除外键约束
ALTER TABLE emp DROP FOREIGN KEY dept_fk;
# 解除外键约束后, 数据插入成功
INSERT INTO emp(id, name, age, salary, dept_id) VALUES
(null,'Jack',22,8000,0);