概述
关系模型数据库是一种以表作为实体,以主键和外键关系作为联系的一种数据库结构
表[table]
是关系型数据库的核心单元,是数据存储的地方
优势
-
降低存储数据的冗余度
-
更高的数据的一致性
-
存储数据可以共享
-
能够实现数据的安全性
-
便于维护数据的完整性
-
建立书库所遵循的标准
存储形式
-
层次模型
-
网状模型
-
关系模型
把世界看作由实体和关系组成
实体指现实世界中客观存在的事物,事物的特性称为属性
关系就是指实体间的联系
关系型数据库基于关系模型而实现,实体指表(数据存储的地方),关系就是主键和外键关联
主键
在关系型数据库表中,用一个唯一的标识符来标识每一行,这个标识符就是主键 Primary Key
特点:
-
不可重复
-
不能为空
外键
在关系型数据库中,外键Foregin Key
是用来表达表与表之间关联关系的列
关系
-
1对1关系
一条主表记录对应一条从表记录,同时一条从表记录对应一条主表记录
-
1对多关系
一条主表记录对应多条从表记录,同时一条从表记录对应一条主表记录
通常把多边定为从表,从表利用外键,引用主表的主键,外键加在从表之中
-
多对多关系
一条主表记录对应多条从表记录,同时一条从表记录对应多条主表记录
利用中间表,描述主表和从表的多对多关系
此时主表一般指被外键引用的表
而从表指存在外键的表,外键引用主表的主键
关系型数据库管理系统
关系型数据库只是一个保存数据的容器,大多数依靠数据库管理系统的软件来管理数据库中数据
管理系统分类
-
本地数据库管理系统
RDBMS与数据库应用程序运行在同一客户端的进程中
-
数据库服务器管理系统
RDBMS和数据库应用程序运行在不同的进程,通常在不同的机器上
一般数据库应用程序在客户端
RDBMS则在专门的数据库服务器上
数据库引擎
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能
使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求
使用合适的存储引擎,将会提高整个数据库的性能
InnoDB
存储引擎
InnoDB
是事务数据库的首选引擎,提供提交、回滚、崩溃恢复能力的事务安全能力,并可以实现并发控制,InnoDB
是默认的MySQL引擎
MyISAM
存储引擎
拥有较高的插入、查询速度,但不支持事务
MEMORY
存储引擎
将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问
主要用于数据量不大的临时数据
功能
查询存储引擎
show engines;
建表时设置存储引擎
create table 表名(
......
)engine=MyISAM;
修改已创建表的存储引擎
alter table 表名 engine=引擎;
结构化查询语言SQL
SQL是结构化查询语言Structured Query Language
的英文缩写,是一种用于管理关系型数据库,并与数据库中的数据进行通讯的计算机语言
SQL方言
SQL提供所有基本的数据操作,不同的RDBMS之间存在一些差别
每个RDBMS实现标准的方法可能有细微差别,不同的RDB
SQL语言的分类
-
数据定义语言(DDL)--------> 创建、修改、删除数据库的内部数据结构
-
数据查询语言(DQL)--------> 用于数据库中数据的查询
-
数据操作语言(DML)--------> 用于数据库中数据的修改、包括添加、删除、修改等
-
数据控制语言(DCL)--------> 控制数据库访问权限
创建数据库
大多数RDBMS中,可以用如下简单SQL语句创建整个数据库
create database 数据库名称; --创建数据库 drop database 数据库名称; --删除数据库
数据库名中服务器中是唯一的
创建表
CREATE TABLE <表名>
(<列名><列的数据类型>[<列的约束>])
创建表SQL语句
CREATE TABLE 表格名称(
表格列的信息
列名1 列的数据类型 列的约束,
列名2 列的数据类型 列的约束
)
CREATE TABLE user_GIRD(
user_name VARCHAR(255),
user_age VARCHAR(11),
user_address VARCHAR(255)
);
数据类型
按照数据结构来分
数据类型的出现是为了把数据分成所需内存大小不同的数据来进行存储
-
整数类型
-
浮点类型
-
字符串类型
-
日期类型
-
二进制类型
整数类型
类型 | 大小(字节) | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 | (-128,127) | 小整数值 | |
SMALLINT | 2 | (-32 768,32 767) | 大整数值 | |
MEDIUMINT | 3 | (-8 388 608, 8 388 607) | 大整数值 | |
INT | 4 | (-2 147 483 648, 2 147 483 647) | 大整数值 | |
BIGINT | 8 | (-9 233 372 036 854 775 808, 9 223 372 036) | 极大整数值 |
浮点类型
类型 | 大小 | 精度 | 用途 |
---|---|---|---|
FLOAT | 4字节 | 7位小数 | 单精度浮点数 |
DOUBLE | 8字节 | 15位小数 | 双精度浮点数 |
DECIMAL(M,D) | 17字节 | 30位小数 | 小数值 |
字符串类型
类型 | 范围 | 用途 |
---|---|---|
CHAR(n) | 0-255 | 定长子符串 |
VARCHAR(n) | 0-65535 | 变长字符串 |
TINYTEXT | 0-255 | 短文本字符串 |
TEXT | 0-65 535 | 长文本数据 |
MEDIUMTEXT | 0-16 777 215 | 中等长度文本数据 |
LONGTEXT | 0-4 294 967 295 | 极大文本数据 |
CHAR
定长字符串,如果加入的数据长度部位设定值,会自动以空格做填充,VARCHAR
则不会
日期时间类型
类型 | 大小 | 格式 | 用途 |
---|---|---|---|
DATE | 3字节 | YYYY-MM-DD (2000-10-10) | 日期值 |
TIME | 3字节 | HH:MM:SS (12:10:05) | 时间值或持续时间 |
YEAR | 1字节 | YYYY (2000) | 年份值 |
DATETIME | 8字节 | YYYY-MM-DD HH:MM:SS (2000-10-10 12:10:05) | 混合日期和时间值 |
TIMESTAMP | 4字节 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
二进制类型
类型 | 大小 | 用途 |
---|---|---|
TINYBLOB | 0-255字节 | 不超过255个字符的二进制字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
一般情况下,不会将二进制文件中的数据放在数据库中,而是会有一个专门存放文件的文件服务器,中数据库中只需要保存文件中文件服务器中的路径
ENUM枚举类型
在MySQL中,ENUM是一个字符串对象,其值是从列创建时定义的允许值列表中选择的
ENUM数据类型,提供紧凑型的数据存储。MySQL ENUM使用数字索引(1,2,3,...)来表示字符串值
ENUM枚举类型使用语法
CREATE TABLE t_employee(
employeeName VARCHAR(50),
edu ENUM( "高中", "大专","本科" )
);
管理表(DDL)
**原表格
CREATE TABLE t_test1(
T_account TEXT,
T_PWD TEXT,
T_MONEY DOUBLE
);
添加新列
ALTER TABLE 表名 ADD 新列名 新列的数据类型 新列的约束 ALTER TABLE t_test1 ADD T_tel VARCHAR(255);
修改列
ALTER TABLE 表名 CHANGE 旧列名 新列名 新列数据类型 新列约束 ALTER TABLE t_test1 CHANGE T_tel T_telphone VARCHAR(255);
删除列
ALTER TABLE 表名 DROP COLUMN 列名 ALTER TABLE t_test1 DROP COLUMN T_telphone;
删除表
DROP TABLE 表名 DROP TABLE t_test_int;
数据操作语言(DML)
添加记录
利用insert into语句可以向表中添加记录
INSERT INTO 表名(列名列表) VALUES(列数据);
INSERT INTO t_test1(T_account,T_PWD,T_MONEY)
VALUES("张三","这是新密码",2000.0),
("张三","这是新密码",2000.0),
("张三","这是新密码",2000.0),
("张三","这是新密码",2000.0),
("张三","这是新密码",2000.0)
删除
DELETE FROM 表名 WHERE 过滤条件 DELETE FROM t_test1 WHERE T_MONEY=2000.0
修改
UPDATE 表名 SET 列1=新值1,列2=新值2 WHERE 过滤条件 UPDATE t_test1 SET T_PWD="112233",T_MONEY=20000 WHERE T_account="张三"
查询
SELECT 投影列 FROM 表名 WHERE 过滤条件 -- 不写条件表示查询所有 SELECT * FROM 表名 (表示查询所有信息)
数据完整性
-
可靠性
-
准确性
为什么要保证数据库完整性?
为了防止垃圾数据的产生,从而影响数据库的执行效率
实体完整性
保证每行所代表的实体能互相区别,不能存在两条一模一样的记录
实现方式
主键约束(Primary Key)
主键是表中的列,主键列不能为空,不能重复,一个表只能有一个主键列
主键列需满足的条件
-
值不能为空
-
值必须唯一
-
不能有业务含义
-
值不能发生变动
CREATE TABLE 表名(
主键列名 列的数据类型 PRIMARY KEY, 指定主键约束
-- 主键列名一般pk开头表示
);
CREATE TABLE student_info2(
pk_id INT PRIMARY KEY,
Student_name VARCHAR(255),
Student_birthday DATE,
Student_class VARCHAR(255)
);
CREATE TABLE 表名(
主键列名 列的数据类型 PRIMARY KEY AUTO_INCREMENT, 表示自动增加列
-- 主键列名一般pk开头表示
);
CREATE TABLE student_info2(
pk_id INT PRIMARY KEY AUTO_INCREMENT,
Student_name VARCHAR(255),
Student_birthday DATE,
Student_class VARCHAR(255)
);
-- 建表后添加删除
主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
唯一约束(Unique)
唯一约束是指给定列的所有值必须唯一,该列值表中每一行的值必须唯一
它和主键约束的区别在于该列可以为空,并且可以在一张表中给多个列设置唯一约束
CREATE TABLE 表名(
列名 列的数据类型 UNIQUE
)
-- 可以给多个列加
CREATE TABLE 表名(
列名 列的数据类型 UNIQUE NOT NULL - - 表示不能为空
)
-- 建表后添加删除
添加:alter table table_name add unique 约束名(字段)
删除:alter table table_name drop key 约束名
域完整性
保证指定列的数据的有效性
实现方式
非空约束(NOT NULL)
指定列的值不能插入null
-- 建表时添加
CREATE TABLE t_student(
id INT
-- not null 非空约束 该列不能不赋值
name VARCHAR(50) not null
);
-- 建表后添加删除
添加:alter table table_name modify 列名 数据类型 not null
删除:alter table table_name modify 列名 数据类型 null
默认约束
添加记录时,如果该列的值不做插入,那么以默认值插入
CREATE TABLE 表名(
列名 列的数据类型 DEFAULT 值
)
-- 在此列不写数据时默认填入设定的值
-- 建表后添加删除
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default
检查约束
检查插入列数据的有效值
PS :MySQL不支持检查约束
CREATE TABLE 表名(
列名 CHECK (列名>数值 AND 列名<数值 )
);
CREATE TABLE 表名(
money CHECK(money >1000 AND money<15000)
);
引用完整性
从表外键中出现的数据,必须在主表的主键列中出现
实现方式
外键约束
外键和外键约束
-
外键是用来表示表与表之间联系的,是表中的一个列
-
外键约束是指在外键列上加上一个约束,强制外键列引用的数据是正确的
-
如果违反该约束,则不允许该条数据的修改
-
PS:没有建立外键约束不等于没有外键
ALTER TABLE 添加外键约束的表名 ADD CONSTRAINT 要添加外键约束的外键列 FOREIGN KEY(外键列名) REFERENCES 主键表(主键名)
ALTER TABLE 添加外键约束的表名 ADD FOREIGN KEY(外键列名) REFERENCES 主键表(主键名)
-- 员工表(pk_id,f_name,f_money,f_in_date,fk_deptID)
CREATE TABLE t_employee(
pk_id INT PRIMARY KEY AUTO_INCREMENT,
f_name VARCHAR(255),
f_money DOUBLE,
f_in_date DATE,
fk_deptID INT
);
-- 部门表(pk_deptID,f_name)
CREATE TABLE t_dept(
pk_deptID INT PRIMARY KEY AUTO_INCREMENT,
f_name VARCHAR(255)
);
ALTER TABLE t_employee ADD CONSTRAINT fk_deptID
FOREIGN KEY (fk_deptID) REFERENCES t_dept(pk_deptID)
-- 或
ALTER TABLE t_employee ADD FOREIGN KEY (fk_deptID) REFERENCES t_dept(pk_deptID)
-- 或者如下 前提是需要先创建部门表
CREATE TABLE t_employee(
pk_id INT PRIMARY KEY AUTO_INCREMENT,
f_name VARCHAR(255),
f_money DOUBLE,
f_in_date DATE,
fk_deptID INT,
FOREIGN KEY (fk_deptID) REFERENCES t_dept(pk_deptID)
-- FOREIGN KEY (外键列) REFERENCES 主表(主键列)
);
-- 建表时添加 主表需在从表之前进行建立
CREATE TABLE t_dept (
pk_deptId INT PRIMARY KEY auto_increment,
f_name VARCHAR (255)
)
CREATE TABLE t_employee (
pk_id INT PRIMARY KEY auto_increment,
fk_deptId INT,
FOREIGN KEY (fk_deptId) REFERENCES t_dept (pk_deptId)
-- foreign key(外键列) references 主表(主键列)
);
-- 建表后添加删除 主表和从表建立顺序随意
CREATE TABLE t_dept (
pk_deptId INT PRIMARY KEY auto_increment,
f_name VARCHAR (255)
)
CREATE TABLE t_employee (
pk_id INT PRIMARY KEY auto_increment,
fk_deptId INT
);
删除主表记录
此时想要删除外键约束下的部门数据
-
先删除和部门相关的所有员工信息
再删除部门信息
-
外键置空
先将和部门相关的所有员工外键设置为null
再删除部门信息
-- 方式1 DELETE FROM t_employee WHERE fk_deptID=3; DELETE FROM t_dept WHERE pk_deptID=3; -- 方式2 UPDATE t_employee SET fk_deptID=NULL WHERE fk_deptID=3 DELETE FROM t_dept WHERE pk_deptID=3;