文章目录
基本概念
数据库
（Database 简称 DB）：是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。
作用：保存、管理数据
类别：
关系型数据库（SQL）
- postgreSQL、达梦、MySQL（PHP）、Oracle（Java）、Microsoft SQL Server（c++ .NET）、Microsoft Access、SQLite、DB2
非关系型数据库（NOSQL）
- Redis（移动端）、MongoDB
数据库管理系统
（Database Management System 简称 DBMS）：数据管理软件，科学组织和存储数据、高效地获取和维护数据。
数据库管理系统是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。数据库管理系统可以依据它所支持的数据库模型来作分类，例如关系式、XML；或依据所支持的计算机类型来作分类，例如服务器群集、移动电话；或依据所用查询语言来作分类，例如SQL、XQuery；或依据性能冲量重点来作分类，例如最大规模、最高运行速度；亦或其他的分类方式。不论使用哪种分类方式，一些DBMS能够跨类别，例如，同时支持多种查询语言。
MySQL
是现流行的开源、免费的关系型数据库
- 免费、开源数据库
- 小巧、功能齐全
- 使用便捷
- 可运行于 Windows 或 Linux 操作系统
- 可适用于中小型甚至大型网站应用
MySQL 的运行机制
结构化查询语句分类
| 名称 | 解释 | 命令 | |
|---|---|---|---|
| DDL（Data Definition Language） | 数据定义语言 | 定义和管理数据对象，如数据库，数据表等 | CREATE、DROP、ALTER |
| DML（Data Manipulation Language） | 数据操作语言 | 用于操作数据库对象中所包含的数据 | INSERT、UPDATE、DELETE |
| DQL（Data Query Language） | 数据查询语言 | 用于查询数据库数据 | SELECT |
| DCL（Data Control Language） | 数据控制语言 | 用来管理数据库的语言，包括管理权限及数据更改 | GRANT、COMMIT、ROLLBACK |
数据定义 DDL
操作数据库
# 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
# 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
# 查看数据库
SHOW DATABASES;
# 切换数据库
USE 数据库名;
操作数据表
查看数据表
# 显示表结构
desc 表名
describe 表名;
# 显示表创建语句
show create table 表名
创建数据表
CREATE TABLE [IF NOT EXISTS] `表名` (
`字段名1` 列类型 [ 属性 ] [ 索引 ] [注释] ,
`字段名2` 列类型 [ 属性 ] [ 索引 ] [注释] ,
......
`字段名n` 列类型 [ 属性 ] [ 索引 ] [注释]
) [ 表类型 ] [ 表字符集 ] [注释];
反引号用于区别MySQL保留字与普通字符
列类型
规定数据库中该列存放的数据类型
- 数值类型
- 字符串类型
- 日期和时间型数值类型
- NULL值
在数据库中，NULL 是一个特殊的值，表示缺失或未知的数据。NULL 与空字符串（''）或零（0）不同，它表示该字段没有被赋予任何值。
特点
1. 未知数据：NULL 表示该字段的值是未知的。例如，如果某个学生的出生日期未知，那么 BirthDate 字段可以设置为 NULL。
不等于任何值：NULL 不等于任何值，包括 NULL 本身。因此，不能使用 = 或 != 来比较 NULL。例如，SELECT * FROM student WHERE BirthDate = NULL; 这样的查询不会返回任何结果。
2. 特殊处理：在 SQL 中，需要使用 IS NULL % IS NOT NULL 来检查字段是否为 NULL，例：
SELECT * FROM student WHERE BirthDate IS NULL;
3. 影响聚合函数：聚合函数（如 SUM AVG COUNT 等）通常会忽略 NULL 值。
SELECT AVG(Score) FROM student; -- 如果 Score 列中有 NULL 值，它们会被忽略
4. 默认值：在创建表时，可以指定列的默认值为 NULL或其他值。
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(100),
birthdate DATE DEFAULT NULL
);
注意事项
数据完整性：NULL 可能会影响数据的完整性和一致性。使用 NOT NULL 约束强制某些字段必须有值。
查询和逻辑：在编写查询，特别是在进行条件判断时，需要注意 NULL 的存在。
-- 正确的方式
SELECT * FROM student WHERE birthdate IS NULL OR birthdate < '1990-01-01';
-- 错误的方式
SELECT * FROM student WHERE birthdate = NULL OR birthdate < '1990-01-01';
| 数值类型 | 说明 | 取值范围 | 存储需求 |
|---|---|---|---|
| tinyint | 非常小的数据 | 有符值： -2^7 ~ 2^7-1 无符号值：0 ~ 2^8-1 | 1字节 |
| smallint | 较小的数据 | 有符值： -2^15 ~ 2^15-1 无符号值： 0 ~ 2^16-1 | 2字节 |
| mediumint | 中等大小的数据 | 有符值： -2^23 ~ 2^23-1 无符号值： 0 ~ 2^24-1 | 3字节 |
| int | 标准整数 | 有符值： -2^31 ~ 2^31-1 无符号值：0 ~ 2^32-1 | 4字节 |
| bigint | 较大的整数 | 有符值： -2^63 ~2^63-1 无符号值：0 ~2^64-1 | 8字节 |
| float | 单精度浮点数 | ± 1.1754351e - 38 | 4字节 |
| double | 双精度浮点数 | ± 2.2250738585072014e - 308 | 8字节 |
| decimal | 字符串形式的浮点数 | decimal(m, d) | m个字节 |
| 字符串类型 | 说明 | 最大长度 |
|---|---|---|
| char[(M)] | 固定长字符串，检索快但费空间 0 <= M <= 255 | M字符 |
| varchar[(M)] | 可变字符串 0 <= M <= 65535 | 变长度 |
| tinytext | 微型文本串 | 2^8–1字节 |
| text | 文本串 | 2^16–1字节 |
| 日期和时间型数值类型 | 说明 | 取值范围 |
| DATE | YYYY-MM-DD，日期格式 | 1000-01-01 ~ 9999-12-31 |
| TIME | Hh:mm:ss ，时间格式 | -838:59:59~838:59:59 |
| DATETIME | YY-MM-DD hh:mm:ss | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 |
| TIMESTAMP | YYYYMMDDhhmmss格式表示的时间戳 | 197010101000000 ~ 2037年的某个时刻 |
| YEAR | YYYY格式的年份值 | 1901~2155 |
| 字段属性 | 说明 | 补充 |
|---|---|---|
| UNSIGNED | 无符号的 | 声明该数据列不允许负数 |
| ZEROFILL | 0填充的 | 不足位数的用0来填充，如 int(3),5则为 005 |
| AUTO_INCREMENT | 自动增长的，每添加一条数据，自动在上一个记录数上加1 | 通常用于设置主键，且为整数类型 可定义起始值和步长 |
| NULL&NOT NULL | 默认为NULL,即没有插入该列的数值 | 如果设置为NOT NULL，则该列必须有值 |
| DEFAULT | 默认的 | 用于设置默认值 例如，性别字段，默认为“男”，否则为“女”；若无指定则默认为“男” |
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) UNSIGNED COMMENT '编码号'
) COMMENT='测试表';
在数据库中创建一个名为 test 的表（如果该表不存在的话），并且有一个无符号整数类型的字段 id，以及对表和字段的注释。
数据表类型
MySQL 的数据表的类型
MyISAM、InnoDB 、HEAP、BOB、CSV等
| 名称 | MyISAM | InnoDB |
|---|---|---|
| 事务处理 | × | √ |
| 数据行锁定 | × | √ |
| 外键约束 | × | √ |
| 全文索引 | √ | × |
| 表空间大小 | 较小 | 较大，约2倍 |
使用 MyISAM：节约空间及相应速度
使用 InnoDB：安全性，事务处理及多用户操作数据表
使用语句新建 subject 表，设置数据表为 MyISAM 类型。具体设计如下：
USE myschool;
CREATE TABLE `subject` (
`SubjectNo` INT(11) NOT NULL COMMENT '课程编号',
`SubjectName` VARCHAR(50) NULL COMMENT '课程名称',
`ClassHour` INT(4) NULL COMMENT '学时',
`GradeID` INT(4) NULL COMMENT '年级编号'
) ENGINE = MyISAM DEFAULT CHARSET = utf8
MySQL 数据表以文件方式存放在磁盘中
包括表文件、数据文件以及数据库的选项文件
位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表
例如我5.7版本的位置是 C:\ProgramData\MySQL\MySQL Server 5.7\Data\myschool\data
InnoDB类型数据表只有一个*.frm文件，数据文件为上一级目录的 ibdata1 文件
MyISAM类型数据表对应三个文件：
*.frm – 表结构定义文件
*.MYD – 数据文件
*.MYI – 索引文件
修改数据表
# 修改表（ALTER TABLE）
# 修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
# 添加字段
ALTER TABLE 表名 ADD 字段名 列类型[属性]
# 修改字段
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]
# 删除字段
ALTER TABLE 表名 DROP 字段名
删除数据表
DROP TABLE [IF EXISTS] 表名
-
IF EXISTS 为可选，判断是否存在该数据表
-
如删除不存在的数据表会抛出错误