一、数据库的定义
数据库(Database)是存储在计算机系统中的有组织的、通常是结构化的数据集合。数据库系统允许用户通过特定的方式(如查询语言)来插入、更新、删除和检索数据。
在数据库管理系统(DBMS)中,数据、表和数据库是三个层次不同的概念,它们之间有着明确的层次结构和关系
1. 数据(Data)
- 定义:数据是数据库中存储的基本信息单位,是数据库的内容。数据可以是数字、文本、日期、图像、声音等形式,表示现实世界中的实体或事件的特定属性。
- 举例:例如,员工的姓名 "Alice",员工的工资 5000,都是数据。
2. 表(Table)
-
定义:表是数据库中存储数据的结构化集合。表由行(记录)和列(字段)组成,每一行代表一个数据记录(数据项),每一列代表一个特定的属性或字段。
-
组成:
- 列(Column):列是表的垂直部分,每列存储特定类型的数据。例如,员工表中的列可能包括“姓名”、“年龄”、“职位”、“工资”等。
- 行(Row):行是表的水平部分,每行代表一个完整的数据记录。例如,员工表中的一行可能包含一个员工的所有信息:姓名为 "Alice",年龄为 30 岁,职位为“经理”,工资为 5000。
-
数据与表的关系:表格中的每个单元格存储的是具体的数据,即表格的行和列交叉处的值。
-
举例:例如,员工表 (Employees) 可以包括以下列和行:
ID | 姓名 | 年龄 | 职位 | 工资 |
---|---|---|---|---|
1 | Alice | 30 | 经理 | 5000 |
2 | Bob | 25 | 开发员 | 4000 |
3 | Charlie | 28 | 设计师 | 4500 |
在这个例子中:
- “Alice”、“Bob”等是具体的数据。
- "姓名"、"年龄"、"职位"等是表的列(字段)。
- 整个表格即为一个表,它存储了多条员工的记录。
3. 数据库(Database)
-
定义:数据库是一个存储多个表及其相关数据的容器,是更高一级的数据组织形式。数据库不仅包含表,还包括视图、索引、存储过程、触发器等数据库对象,以及管理这些对象的元数据(如表结构定义)。
-
表与数据库的关系:数据库包含了多个表,每个表存储特定类型的数据。一个数据库可以看作是数据表的集合,而每个表又是数据记录的集合。
-
组织结构:数据库中的表之间可能存在关系,例如通过外键实现的表间关联,反映了数据的逻辑关系。
-
举例:例如,一个公司数据库 (CompanyDB) 可以包含多个表:
Employees
表(存储员工信息)Departments
表(存储部门信息)Salaries
表(存储工资信息)
在这个例子中,CompanyDB
是数据库,里面包含了 Employees
、Departments
和 Salaries
等多个表,每个表中又包含具体的数据信息。
总结
- 数据 是数据库中最小的存储单元,是表中记录的内容。
- 表 是数据库中存储数据的结构化单位,由行和列组成,每个表保存了一类相关的数据记录。
- 数据库 就是表的集合。它是以一定的组织方式存储的相互有关的数据集合
4.数据库管理系统
数据库管理系统(DatabaseManagementSystem,DBMS)是实现对数据库资源有效组织、管理和存取的系统软件。它在操作系统的支持下,支持用户对数据库的各项操作
DBMS 的核心功能
- 数据定义:创建和修改数据库结构,如表格、视图、索引等。
- 数据操控:执行数据的插入、更新、删除和查询操作。
- 事务管理:确保一系列操作(事务)的完整性和一致性。
- 并发控制:管理多个用户同时访问数据,防止数据冲突。
- 安全性管理:控制对数据的访问权限,保护数据安全。
- 数据备份和恢复:提供数据的备份和恢复功能,以防止数据丢失。
DBMS的工作模式如下:
- 接受应用程序的数据请求和处理请求
- 将用户的数据请求(高级指令)转换为复杂机器代码(底层指令)
- 实现对数据库的操作
- 从数据库的操作中接受查询结果
- 对查询结果进行处理(格式转换)
- 将处理结果返回给用户
二、数据库的发展
数据库的发展可以分为几个阶段,通常被称为第一代、第二代和第三代数据库。每一代数据库技术都在前一代的基础上进行了改进,以适应不断变化的需求和技术进步。
第一代:层次型和网状型数据库
时间:20世纪60年代至70年代
1.1 层次型数据库
- 数据模型:层次型数据库采用树状结构(类似于文件系统的目录结构)来表示数据。数据以父子层次关系组织,每个节点(记录)有且只有一个父节点。
- 优点:数据结构简单,数据存取速度快,适合于某些特定的应用场景,如组织结构和物料清单管理。
- 缺点:层次模型不支持多对多关系,数据冗余较高,结构不灵活,修改数据库结构复杂。
- 示例:IBM 的 IMS(Information Management System)是典型的层次型数据库管理系统。
1.2 网状型数据库
- 数据模型:网状型数据库采用图结构来表示数据,允许记录有多个父节点,支持多对多关系。数据节点之间可以有多条连接路径。
- 优点:灵活性较高,支持复杂的多对多关系,减少了数据冗余。
- 缺点:数据库设计和维护复杂,查询操作难度较大,数据结构的修改不方便。
- 示例:CODASYL DBTG 模型是网状型数据库的代表,典型系统包括 IDMS(Integrated Database Management System)。
第二代:关系型数据库
时间:20世纪70年代末至今
2.1 关系型数据库(RDBMS)
- 数据模型:关系型数据库采用关系模型,将数据组织为表格形式(关系),表中的每一行代表一个记录,每一列代表一个字段。关系模型通过主键和外键来建立表之间的关联。
- 优点:
- 数据结构简单、直观,易于理解和使用。
- 使用 SQL(结构化查询语言)进行数据操作,查询灵活。
- 支持事务、并发控制、数据完整性和安全性。
- 易于维护,适合大多数业务场景。
- 缺点:
- 对于海量数据和高并发场景下的扩展性相对较弱。
- 处理复杂关系和嵌套数据时效率较低。
- 示例:典型的关系型数据库系统包括 Oracle Database、MySQL、PostgreSQL、Microsoft SQL Server 等。
第三代:面向对象数据库与NoSQL数据库
时间:20世纪90年代至今
3.1 面向对象数据库(OODBMS)
- 数据模型:面向对象数据库结合了面向对象编程的概念,将数据以对象的形式存储,支持对象的继承、多态性和封装。数据与操作行为绑定在一起,使得数据管理更加符合面向对象的思维方式。
- 优点:
- 支持复杂数据结构和对象关系映射(ORM)。
- 高度兼容面向对象编程语言,减少了编程难度。
- 能处理复杂的嵌套和层次结构数据。
- 缺点:
- 不如关系型数据库通用,标准化程度较低。
- 查询语言复杂,不如 SQL 那么广泛接受。
- 示例:ObjectDB、db4o、Versant 等。
3.2 非关系型数据库(NoSQL)
- 数据模型:NoSQL 数据库采用多种数据模型,如键值对、列族、文档、图数据库等,以应对不同类型的应用场景。NoSQL 数据库通常放弃了关系型数据库中的部分特性(如严格的 ACID 属性),以获得更好的扩展性和性能。
- 优点:
- 高扩展性,能够处理大规模数据和高并发。
- 数据模型灵活,适应不同类型的数据存储需求。
- 高效的读取和写入性能,适合分布式系统。
- 缺点:
- 一致性保证较弱,复杂查询功能较少。
- 缺乏统一的标准,系统设计和使用较为复杂。
- 示例:MongoDB(文档型)、Cassandra(列族型)、Redis(键值型)、Neo4j(图数据库)等。
总结
- 第一代数据库:层次型和网状型数据库,强调数据的层次和网络结构管理,但在灵活性和易用性上有所不足。
- 第二代数据库:关系型数据库,引入了关系模型和 SQL,成为最广泛使用的数据库类型,支持复杂的事务和数据操作。
- 第三代数据库:面向对象数据库和 NoSQL 数据库,针对复杂对象管理和大规模分布式数据处理提出了解决方案,适应了现代多样化的数据需求。
关系型数据库和非关系型数据库
关系型数据库(RDBMS)
- 数据结构:数据以表格形式存储,每个表由行和列组成,行代表记录,列代表字段。
- 查询语言:使用 SQL(结构化查询语言)进行数据操作。
- 特点:支持复杂查询和事务处理,数据一致性强(ACID属性)。
- 适用场景:适用于传统业务系统,如财务系统、ERP、CRM等。
- 示例:MySQL、PostgreSQL、Oracle、SQL Server。
非关系型数据库(NoSQL)
- 数据结构:支持多种数据模型,如键值对、文档、列族和图,不采用固定的表格结构。
- 查询语言:没有标准的查询语言,各数据库有不同的查询方式。
- 特点:高度扩展性,适合处理大量数据和高并发,通常在一致性上做出一定让步。
- 适用场景:适用于大数据、实时分析、社交网络等需要灵活性和高扩展性的场景。
- 示例:MongoDB(文档型)、Redis(键值型)、Cassandra(列族型)、Neo4j(图数据库)。
数据模型 | 表格形式,行和列 | 多样化:键值对、文档、列族、图 |
查询语言 | 标准化的 SQL | 多种查询语言,非标准化 |
事务管理 | 支持 ACID 事务 | 通常不支持或部分支持 ACID,强调 CAP 理论 |
扩展性 | 垂直扩展(通过增加单个服务器的能力) | 水平扩展(通过增加服务器数量) |
数据一致性 | 强一致性 | 最终一致性(在一些场景下) |
适用场景 | 复杂查询、事务处理、结构化数据 | 大规模数据、高并发、非结构化数据 |
代表系统 | MySQL、PostgreSQL、Oracle | MongoDB、Cassandra、Redis |
简要对比
- 关系型数据库:结构化数据,使用 SQL,适合复杂查询。
- 非关系型数据库:多种数据模型,灵活性高,适合大数据和动态应用。
三、MySQL数据库管理
1.了解数据库信息、表结构与存储引擎
1.2 查看数据库信息
在MySQL中,可以使用以下命令来查看当前服务器上存在的所有数据库:
SHOW DATABASES;
这个命令会列出所有可用的数据库。要切换到一个特定的数据库,可以使用USE
命令:
USE database_name;
切换到目标数据库后,可以查看该数据库中的所有表:
SHOW TABLES;
或者,指定数据库名称来查看该数据库中的表:
SHOW TABLES IN database_name;
1.2 查看表的结构信息
在管理数据库时,了解表的结构(即字段信息)是至关重要的。可以使用DESCRIBE
命令来查看表的结构:
DESCRIBE table_name;
这将显示以下信息:
- Field: 字段名称。
- Type: 数据类型。
- Null: 是否允许为空。
- Key: 主键或索引信息。
- Default: 默认值。
- Extra: 扩展属性,例如标志符列
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
1.3 常用的数据类型
在定义表结构时,选择合适的数据类型非常重要。以下是MySQL中常用的一些数据类型:
- INT:用于存储整数。
- FLOAT:单精度浮点数(4字节)。
- DOUBLE:双精度浮点数(8字节)。
- CHAR:固定长度的字符类型。如果实际存储的数据长度小于指定长度,会自动补空格。
- VARCHAR:可变长度的字符类型,节省存储空间。
- TEXT:用于存储长文本。
- DECIMAL(5,2):精确的定点数类型,例如存储5位数,其中2位是小数。
CREATE TABLE example (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2),
description TEXT
);
1.4 主键与复合主键
在数据库设计中,主键用于唯一标识表中的每一行记录。主键可以由一个或多个字段组成,后者被称为复合主键。例如:
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
1.5 MySQL数据文件存储
MySQL的数据文件存储在服务器的文件系统中,不同的存储引擎会使用不同的文件格式。默认情况下,MySQL的数据文件存放在/usr/local/mysql/data
目录下。
-
MyISAM存储引擎:
- .frm:存储表的结构定义。
- .MYD:存储表的数据。
- .MYI:存储表的索引。
-
InnoDB存储引擎:
- .ibd:存储表的数据和索引(如果使用独立表空间)。
- ibdata文件:如果使用共享表空间,所有表的数据和索引存放在一个或多个
ibdata
文件中。
1.6 MyISAM与InnoDB存储引擎
MySQL提供了多种存储引擎,其中最常用的是MyISAM和InnoDB。
- MyISAM:适合读操作频繁的应用,支持全文索引,文件存储更为简单。
- InnoDB:支持事务处理,提供外键约束,数据安全性更高,适合需要并发写操作的应用。
MyISAM表的数据文件和索引文件分别存储在.MYD
和.MYI
文件中。而InnoDB的存储则更为复杂,可以选择独立表空间模式(每个表一个.ibd
文件),或者共享表空间模式(使用ibdata
文件)。
2. SQL语言分类
SQL语言主要分为以下几类:
- DDL(数据定义语言):用于创建和修改数据库对象,如数据库、表、索引等。
- DML(数据操纵语言):用于对数据库中的数据进行管理,包括插入、删除和更新记录。
- DQL(数据查询语言):用于从数据表中查询符合条件的记录。
- DCL(数据控制语言):用于设置或更改数据库用户或角色的权限,如控制访问级别和许可。
2.1 DDL:数据定义语言
DDL语句用于定义和管理数据库结构。常用的DDL语句包括CREATE
、DROP
和ALTER
。
创建新的数据库
CREATE DATABASE 数据库名;
CREATE DATABASE kgc_school;
选择数据库
USE 数据库名;
创建新的表
CREATE TABLE 表名 (
字段1 数据类型,
字段2 数据类型,
...,
PRIMARY KEY (主键名)
);
示例
CREATE TABLE KY13 (
id INT NOT NULL,
name CHAR(10) NOT NULL,
score DECIMAL(5,2),
passwd CHAR(48) DEFAULT '',
PRIMARY KEY (id)
);
- NOT NULL: 不允许为空值。
- DEFAULT: 默认值。
- PRIMARY KEY: 主键字段,确保唯一且不为空。
显示表结构
DESC 表名;
删除指定的数据表
DROP TABLE 表名;
如果没有选择数据库,则需要指定数据库名:
DROP TABLE 数据库名.表名;
删除指定的数据库
DROP DATABASE 数据库名;
2.2 DML:数据操纵语言
DML语句用于对表中的数据进行管理,包括插入、更新和删除记录。
插入数据
INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO KY13 (id, name, score, passwd) VALUES (1, 'zhangsan', 70.5, PASSWORD('123456'));
更新数据
UPDATE 表名 SET 字段名1 = 字段值1[, 字段名2 = 字段值2] [WHERE 条件];
UPDATE KY13 SET passwd = PASSWORD('newpassword') WHERE name = 'zhangsan';
UPDATE KY13 SET name = 'wangxiaoer', passwd = '' WHERE id = 3;
删除数据
DELETE FROM 表名 [WHERE 条件];
DELETE FROM KY13 WHERE id = 4;
2.3 DQL:数据查询语言
DQL语句用于从数据表中查询符合条件的数据记录。
查询数据
SELECT 字段名1, 字段名2, ... FROM 表名 [WHERE 条件];
SELECT * FROM KY13;
SELECT id, name, score FROM KY13 WHERE id = 2;
限制查询结果数量
SELECT * FROM 表名 LIMIT 开始位置, 行数;
SELECT * FROM KY13 LIMIT 2; -- 显示前2行
SELECT * FROM KY13 LIMIT 2, 3; -- 从第3行开始显示3行
以列表方式显示
SELECT * FROM 表名 \G;
SELECT * FROM KY13 \G;
2.4 DCL:数据控制语言
DCL语句用于设置和管理数据库的访问权限和安全级别。
GRANT
: 授予用户权限。REVOKE
: 撤销用户权限。COMMIT
: 提交当前事务。ROLLBACK
: 回滚当前事务。
这些命令通常用于数据库的安全管理,确保数据的保护和用户权限的控制。
3. 数据表高级操作
3.1 清空表
清空表是指删除表中的所有数据而保留表结构。常用的方法有两种:DELETE
和TRUNCATE
。
方法一:使用 DELETE
DELETE FROM 表名;
- 特点:逐行删除数据,删除后的结果内包含删除的记录条目。
- 自增长字段:
DELETE
操作不会重置自增长字段的值,新插入的记录会从原来的最大ID继续递增。 - 事务处理:
DELETE
操作是可回滚的,意味着在事务中可以恢复数据。
方法二:使用 TRUNCATE
TRUNCATE TABLE 表名;
- 特点:快速删除所有数据,不返回被删除的条目。
TRUNCATE
实际上重新创建表,因此速度通常比DELETE
快。 - 自增长字段:
TRUNCATE
会重置自增长字段,新的记录ID从1开始。 - 事务处理:
TRUNCATE
操作是不可回滚的。
3.2 创建临时表
临时表在数据库会话中有效,连接关闭后会自动删除。临时表不能拥有外键约束。
创建临时表:
CREATE TEMPORARY TABLE 表名 (
字段1 数据类型,
字段2 数据类型,
...
[, PRIMARY KEY (主键名)]
);
示例:
CREATE TEMPORARY TABLE temp_table (
id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL,
cardid INT(18) NOT NULL UNIQUE KEY,
hobby VARCHAR(50)
);
操作:
- 在临时表中执行增、删、改、查操作与普通表相同。
- 临时表不会出现在
SHOW TABLES
的结果中。
注意:
- 临时表在会话结束时会被自动删除,无法创建外键。
3.3 克隆表
克隆表包括克隆表结构和数据,可以用于备份或迁移数据。
克隆表结构:
CREATE TABLE 新表名 LIKE 原表名;
克隆表数据:
INSERT INTO 新表名 SELECT * FROM 原表名;
示例:
CREATE TABLE backup_table LIKE original_table;
INSERT INTO backup_table SELECT * FROM original_table;
获取表结构:
SHOW CREATE TABLE 新表名\G;
3.4 修改表结构
重命名表:
ALTER TABLE 旧表名 RENAME 新表名;
扩展表结构(增加字段):
ALTER TABLE 表名 ADD 字段名 数据类型 [DEFAULT 默认值];
修改字段(列)名和数据类型:
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
修改字段数据类型:
ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型;
删除字段:
ALTER TABLE 表名 DROP 字段名;
示例:
ALTER TABLE users ADD address VARCHAR(50) DEFAULT 'Unknown';
ALTER TABLE users CHANGE name username VARCHAR(20) UNIQUE;
ALTER TABLE users MODIFY COLUMN age INT(3);
ALTER TABLE users DROP address;
扩展说明:
IF NOT EXISTS
:在创建表时,如果表已存在则不会报错。AUTO_INCREMENT
:自增长字段,默认从1开始递增。UNIQUE KEY
:确保字段值唯一,但允许空值。NOT NULL
:字段不能为空。