文章目录
了解数据库
MySQL
是数据库(Database,简称DB)的一种。
数据库
:长期存放在计算机内,有组织、可共享的大量数据的集合,是一个数据“仓库”。
现在市面上主要流行的数据库可以分为两种类型
关系型数据库(SQL)
MySQL、Oracle、SQL Server、SQLite、DB2
非关系型数据库(NOSQL)
Redis、MongoDB
MySQL
MySQL
是一款开源的关系型数据库管理系统,由瑞典MySQL AB公司开发,后被Oracle收购。MySQL的主要特点
包括:
-
开源免费
:MySQL使用GPL协议,可以免费使用和修改源代码。 -
性能高效
:MySQL的查询速度和操作效率非常快,读写性能优异。 -
易于扩展
:支持大型数据库,可以处理拥有高并发访问和庞大数据量的应用。 -
简单易用
:MySQL语法简单,容易学习使用,并有完善的文档。 -
跨平台性
:支持多种操作系统,如Windows、Linux、Unix等。 -
支持多种编程语言
:提供了ODBC、JDBC等接口,支持多种编程语言访问。 -
安全稳定
:提供良好的安全性,支持SSL加密传输,数据备份恢复也比较方便。
MySQL
是最流行的开源数据库,被广泛应用于各种Web应用和嵌入式数据库的开发中。它一般与PHP、Python等语言配合使用,是Web应用栈的关键组件之一。
安装
可以直接从官网下载https://www.mysql.com/cn/
也可以下载神器phpStudy
一键启动
MySQL登录
进入安装目录\mysql\bin,搜索栏搜索cmd
mysql -h 服务器主机地址 –u 用户名 -p用户密码
建了一个微信公众号音耀,后续会在上面更新一些有用资源和笔记,大家有兴趣的话可以加一下谢谢了。(上面会更新完整代码)
使用Navicat Premium 16进行数据库管理
Navicat Premium 是一款功能强大的数据库管理工具,具有以下功能:
-
支持多种数据库:MySQL、MariaDB、SQLite、Oracle、PostgreSQL等数据库都可以通过Navicat Premium进行连接管理。
-
可视化数据库设计:提供可视化的数据库模型设计器,可以通过拖拽的方式设计数据库模型。
-
简单的数据操作:提供图形化的表数据浏览、编辑和搜索功能。可以快速进行数据查询、新增、修改和删除等操作。
-
多表数据对比:通过数据对比工具,可以直观地比较两个表或者数据库的差异。
-
数据传输与同步:实现不同数据库之间表结构和数据的传输,也可以进行数据库之间的实时同步。
-
数据库迁移:可以将其他数据库(如Access、SQL Server等)的数据迁移到Navicat Premium 支持的数据库。
-
批量执行SQL:可以进行SQL语句的批量编辑和执行。
-
连接云数据库:支持将本地或网络中的数据库连接到云端。
-
安全连接:支持SSH、HTTP、HTTPS等安全连接方式,保证数据传输安全。
综上所述,Navicat Premium是一个功能强大、操作简便的跨平台数据库管理工具,适用于各种不同场景的数据库管理工作。
首先连接本地数据库
新建一个数据库testdb
下面就可以进行操作了
新建数据库
新建数据表
图形化管理数据表
新建查询(命令操作
)
MySQL查询语句分类
汇总后的MySQL语句分类表格如下:
注意
:MySQL并不区分大小写,为了方便记忆,我将关键字统一小写
分类 | 说明 | 主要关键字 |
---|---|---|
DDL | 数据定义语言,用于定义数据库对象 | create, drop, alter |
DQL | 数据查询语言,用于查询数据库数据 | select |
DML | 数据操作语言,用于增删改查数据库记录 | insert, delete, update, select |
DAL | 数据访问语言,用于访问数据库信息 | set transaction, commit, rollback,show,use |
DCL | 数据控制语言,用于定义数据库访问权限 | grant, revoke |
TCL | 事务控制语言,用于管理数据库事务 | commit, rollback, savepoint |
CCT | 游标控制语言,用于管理游标 | declare cursor, fetch |
基本学习
命令行操作数据库
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
删除数据库
DORP DATABASE [IF EXISTS] 数据库名;
查看数据库
show DataBases;
显示当前MySQL服务器中存在的所有数据库名称。
使用数据库
USE database_name;
use语句不进行实际的数据操作,只是将后面所有的SQL语句针对指定的数据库来执行。
所以use语句是用于访问和选择数据库,属于数据访问语言DAL的一种。
创建数据表
CREATE TABLE [ IF NOT EXISTS ] `表名` (
`字段名1` 列类型 [ 属性 ] [ 索引 ] [注释] ,
`字段名2` 列类型 [ 属性 ] [ 索引 ] [注释] ,
… …
`字段名n` 列类型 [ 属性 ] [ 索引 ] [注释]
) [ 表类型 ] [ 表字符集 ] [注释] ;
假设我们要创建一个名为 employees
的表,其中包含三个字段:id
、name
、和 email
。id
是一个整数类型的字段,我们将其设置为主键;name
是一个可变字符串类型的字段;email
也是一个可变字符串类型的字段。
CREATE TABLE IF NOT EXISTS `employees` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
`name` VARCHAR(100) NOT NULL COMMENT '员工姓名',
`email` VARCHAR(255) NOT NULL COMMENT '员工邮箱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';
在这个例子中:
IF NOT EXISTS
是一个可选语句,用于只在同名表不存在时创建新表。employees
是要创建的表名。- 每个
字段名
后面跟的是该字段的列类型
,可能还有一些额外的属性
,例如NOT NULL
表示该字段不能有NULL值,AUTO_INCREMENT
表示自动递增。 PRIMARY KEY
是一个索引
,表示该字段是表的主键。COMMENT
是字段或表的注释。ENGINE=InnoDB
是指定的表类型
,InnoDB是MySQL的默认存储引擎之一。DEFAULT CHARSET=utf8mb4
是表字符集
,utf8mb4是推荐的字符集,支持存储Unicode字符。- 表的
注释
放在创建表的语句末尾。
最后,整个语句以分号 ;
结尾,表示SQL语句的结束。
列数据类型
MySQL中常见的列数据类型包括:
- 数值类型:INTEGER、SMALLINT、DECIMAL等
- 字符串类型:CHAR、VARCHAR、TEXT等
- 日期时间类型:DATE、TIME、DATETIME、TIMESTAMP等
- 布尔类型:BOOL、BOOLEAN
数值类型用于存储整数或浮点数值。
字符串类型用于存储字符串和文本内容。CHAR是定长的,VARCHAR是变长的。
日期时间类型用于存储日期、时间或两者合并的值。TIMESTAMP可以自动存储时间戳。
布尔类型用于存储真或假值。
此外,MySQL还支持二进制数据、空间数据等特定类型。
选择合适的数据类型对表的设计很重要,它会影响存储空间、查询性能等。
常见的原则是不要定义比实际需要更大或更精确的类型,那样会浪费存储和降低效率。
小知识:
在MySQL中,NULL值表示一个未知的或不适用的值。NULL并不是一种数据类型,而是可以应用于任何数据类型的字段的特殊标记。
一些关于NULL的特点:
- NULL与任何值进行比较,结果都为NULL
- NULL与NULL进行比较,结果也是NULL
- 对NULL进行计算,结果通常也是NULL
- NULL值不等于0或空字符串’’
- 判断NULL需要使用IS NULL或IS NOT NULL,不能用=或!=
例如:
CREATE TABLE test (
id INT,
name VARCHAR(50) NULL
);
INSERT INTO test VALUES (1, NULL);
SELECT * FROM test WHERE name = NULL; -- 不会返回记录
SELECT * FROM test WHERE name IS NULL; -- 可以返回id为1的记录
在MySQL中,NULL被用于表示一个未知或不存在的值,它的行为与一般值不同。使用和判断NULL值需要特殊的语法。
NULL自身没有数据类型,可以赋值给任意类型的字段。
数值类型
类型 | 说明 | 取值范围 | 存储需求 |
---|---|---|---|
TINYINT | 很小的整数 | 有符号:-128 到 127 无符号:0 到 255 | 1 字节 |
SMALLINT | 小的整数 | 有符号:-32768 到 32767 无符号:0 到 65535 | 2 字节 |
MEDIUMINT | 中等大小的整数 | 有符号:-8388608 到 8388607 无符号:0 到 16777215 | 3 字节 |
INT或INTEGER | 标准整数 | 有符号:-2147483648 到 2147483647 无符号:0 到 4294967295 | 4 字节 |
BIGINT | 大整数 | 有符号:-2^63 到 2^63 - 1 无符号:0 到 2^64 - 1 | 8 字节 |
FLOAT | 单精度浮点数 | -3.402823466E+38 到 -1.175494351E-38,0 和 1.175494351E-38 到 3.402823466E+38 | 4 字节 |
DOUBLE | 双精度浮点数 | -1.7976931348623157E+308 到 -2.2250738585072014E-308,0 和 2.2250738585072014E-308 到 1.7976931348623157E+308 | 8 字节 |
DECIMAL或NUMERIC | 定点数 | decimal(M, D)取决于M和D的值(M是数字的总数,D是小数点后的位数) 最大数为65(M)和30(D) | m个字节 |
字符串类型
类型 | 说明 | 最大长度 |
---|---|---|
CHAR | 定长字符串 | 0 到 255 字符 |
VARCHAR | 变长字符串 | 0 到 65535 字符 |
TINYTEXT | 很小的文本 | 255 字符 (2^8 - 1) |
TEXT | 长文本数据 | 65535 字符 (2^16 - 1) |
MEDIUMTEXT | 中等长度文本 | 16777215 字符 (2^24 - 1) |
LONGTEXT | 极大文本数据 | 4294967295 字符 (2^32 - 1) |
BINARY | 定长二进制字符串 | 0 到 255 字节 |
VARBINARY | 变长二进制字符串 | 0 到 65535 字节 |
TINYBLOB | 很小的二进制数据 | 255 字节 (2^8 - 1) |
BLOB | 二进制数据 | 65535 字节 (2^16 - 1) |
MEDIUMBLOB | 中等长度的二进制数据 | 16777215 字节 (2^24 - 1) |
LONGBLOB | 极大二进制数据 | 4294967295 字节 (2^32 - 1) |
ENUM | 枚举,一个列的值限定在给定的列表内 | 最多65535个不同的值 |
SET | 集合,一个列可以包含零个或多个给定列表中的值 | 最多64个不同的值 |
注意,对于VARCHAR
和TEXT
类型,最大长度也取决于数据库的最大行大小以及字符集的使用。VARCHAR
的最大有效长度可能会因为使用的字符集(如utf8
或utf8mb4
)和其他列的存在而减少。此外,ENUM
和SET
类型的长度取决于定义时列举的值的数量和长度。
日期时间类型
类型 | 说明 | 取值范围 |
---|---|---|
DATE | 日期值 | ‘1000-01-01’ 到 ‘9999-12-31’ |
TIME | 时间值或持续时间 | ‘-838:59:59’ 到 ‘838:59:59’ |
DATETIME | 日期和时间的组合 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ |
TIMESTAMP | 时间戳,日期和时间的组合,UTC | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC |
YEAR | 年份值 | 1901 到 2155 及 0000 |
注意,TIMESTAMP
类型显示为 UTC
时间。在存储和检索过程中,它会自动转换为当前时区的时间。此外,DATETIME
类型不包含时区信息,而 TIMESTAMP
类型与时区相关。YEAR
类型可以有不同的显示宽度,比如 YEAR(2)
或 YEAR(4)
。TIME
类型除了表示一天中的时间,还可以表示时间间隔,最长可表示超过一个月的时间。
布尔类型
MySQL 并没有一个专门的布尔数据类型。然而,它使用 TINYINT(1)
来表示布尔值。
类型 | 说明 | 取值范围 | 备注 |
---|---|---|---|
TINYINT(1) | 布尔类型 | 0 到 1 | 0 表示 false,1 表示 true |
在MySQL中,通常使用 0
表示假(false)和 1
表示真(true)。虽然 TINYINT(1)
可以存储从 -128
到 127
的值(如果是有符号的),或者从 0
到 255
的值(如果是无符号的),但在用作布尔类型时,通常只使用 0
和 1
。有时,在SQL语句中将会看到 BOOLEAN
或 BOOL
作为列类型的定义,但这些只是 TINYINT(1)
的同义词,并不是独立的数据类型。
属性
在MySQL中,列的“属性”定义了列的特定特性和行为。以下是一些常见的列属性以及它们的含义
属性 | 描述 |
---|---|
NOT NULL | 列不能存储 NULL 值。 |
DEFAULT | 为列指定一个默认值。如果插入操作未指定值,则使用默认值。 |
AUTO_INCREMENT | 对于每一条新记录,自动递增列的值。通常用于主键。 |
UNSIGNED | 对于数值类型,指定列不能为负。 |
ZEROFILL | 对于数值类型,空位以0填充。 |
COMMENT | 为列提供注释。 |
CHARACTER SET | 为字符列指定字符集。 |
COLLATE | 为字符列指定排序规则。 |
索引
索引”指的是对数据库表中一列或多列的值进行排序的数据库表结构。索引用于快速检索数据库表中的特定信息。在MySQL中,可以在创建表时为一个或多个列指定索引,以提高查询性能。
有几种类型的索引,包括但不限于:
- PRIMARY KEY: 一个表中只能有一个主键,它唯一标识表中的每一行,并且不能为 NULL。
- UNIQUE: 这个索引保证列中的所有值都是唯一的。与主键不同,你可以有多个唯一索引,并且列可以接受 NULL 值(除非另外指定了 NOT NULL)。
- INDEX: 一般索引,用于提高查询性能,可以有多个。
- FULLTEXT: 专门用于全文搜索。
- FOREIGN KEY: 用于InnoDB表,强制执行参照完整性。
例如,在创建表时为字段创建索引的SQL语句如下:
CREATE TABLE IF NOT EXISTS `my_table` (
`field1` INT NOT NULL AUTO_INCREMENT,
`field2` VARCHAR(100) NOT NULL,
`field3` DATETIME DEFAULT NULL,
PRIMARY KEY (`field1`),
UNIQUE KEY `unique_index` (`field2`),
INDEX `field3_index` (`field3`)
);
在这个例子中,field1
被指定为主键,field2
有一个唯一索引,而 field3
有一个一般索引。
索引可以在创建表时定义,也可以在表创建后使用 ALTER TABLE
或 CREATE INDEX
语句添加。虽然索引可以加快检索速度,但它们也需要额外的存储空间,并且在插入、更新或删除数据时可能会降低性能,因为索引本身也需要更新。因此,应该根据实际情况合理地使用索引。
注释
数据库注释
数据库注释通常用来说明表、列或索引的用途和其他相关信息。这对于维护大型数据库和确保开发者之间的良好沟通非常有价值。
表注释
表注释提供了有关表整体目的和用途的信息。例如,在创建表时,可以这样添加注释:
CREATE TABLE employees (
) COMMENT='员工信息表,包含个人和职务细节';
列注释
列注释提供了有关表中特定列用途的信息。例如,在定义列时,可以这样添加注释:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识符',
name VARCHAR(100) NOT NULL COMMENT '员工姓名',
);
索引注释
虽然在SQL中直接对索引添加注释不常见,但有时在创建索引时也可以提供说明性的SQL注释:
CREATE INDEX idx_lastname ON employees (lastname); -- 用于快速按姓氏查找员工的索引
在这些例子中,COMMENT用于直接在SQL语句中提供关于表结构的元数据。这些注释可以使用数据库管理工具查看,也可以在数据库的元数据中查询到。
数据表的存储位置
MySQL数据表以文件方式存放在磁盘中
包括表文件、数据文件以及数据库的选项文件
位置:MySQL安装目录\data下存放数据表。目录名对应数据库名,该目录下文件名对应数据表
注意
:
InnoDB类型数据表只有一个*.frm文件,数据文件为上一级目录的 ibdata1 文件
MyISAM类型数据表对应三个文件:
*.frm – 表结构定义文件
*.MYD – 数据文件
*.MYI – 索引文件
当然,以下是关于MySQL中外键的Markdown笔记。
外键作用
外键是用来建立两个表之间的联系,保证参照完整性的一种数据库约束。其主要作用包括:
- 参照完整性:确保关联表的字段值必须出现在主表的关联列中。
- 数据一致性:维护数据库表之间的数据一致性。
- 级联操作:可以设置级联更新或删除,当主表中的数据变化时,外键表中的数据也会相应变化。
创建外键
当创建新表时,可以直接在表的定义中包括外键约束。
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderNumber INT NOT NULL,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
在这个例子中,Orders
表的 CustomerID
列是一个外键,它引用了 Customers
表的 CustomerID
列。
建表时指定外键约束
在创建表时,可以在列定义后添加外键约束:
CREATE TABLE TableName (
Column1 DataType,
Column2 DataType,
...
CONSTRAINT FK_ConstraintName FOREIGN KEY (Column1, Column2, ...) REFERENCES OtherTableName (Column1, Column2, ...)
);
建表后修改
如果需要在表创建后添加外键约束,可以使用 ALTER TABLE
语句:
ALTER TABLE TableName
ADD CONSTRAINT FK_ConstraintName
FOREIGN KEY (ColumnName) REFERENCES OtherTableName(ColumnName);
删除外键
要删除已存在的外键约束,可以使用 ALTER TABLE
语句:
ALTER TABLE TableName
DROP FOREIGN KEY FK_ConstraintName;
删除外键、删除表
在删除表之前,你可能需要先删除外键约束,以避免引用完整性问题:
ALTER TABLE TableName
DROP FOREIGN KEY FK_ConstraintName;
DROP TABLE TableName;
在上面的命令中,首先删除了 TableName
表的外键约束 FK_ConstraintName
,随后删除了表。如果其他表没有引用该表的外键,则可以直接删除表:
DROP TABLE TableName;
请根据实际情况替换 TableName
、ColumnName
、FK_ConstraintName
和 OtherTableName
以适应你的数据库结构。