建设网站书籍pdf下载,网站诊断,wordpress 退出登录,wordpress设置登录背景图片文章目录服务器#xff0c;数据库#xff0c;表关系MySQL 数据存储逻辑SQL 分类存储引擎库的操作查看数据库创建数据库查看创建语句删除数据库选择#xff08;切换#xff09;数据库查看当前选择的数据库修改数据库字符集和排序规则表的操作创建表查询表查询表结构插入数据…
文章目录服务器数据库表关系MySQL 数据存储逻辑SQL 分类存储引擎库的操作查看数据库创建数据库查看创建语句删除数据库选择切换数据库查看当前选择的数据库修改数据库字符集和排序规则表的操作创建表查询表查询表结构插入数据检索数据修改表删除表备份还原show processlist服务器数据库表关系
所谓安装数据库服务器就是在机器上安装了一个数据库管理系统程序这个管理程序可以管理多个数据库一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据一般会在数据库中创建多个表以保存程序中实体的数据。
数据库服务器、数据库和表的关系如下 mysqld 就是 MySQL 数据库服务器的守护进程程序它是 MySQL 数据库系统的核心组件之一。mysqld 进程负责监听 MySQL 数据库服务器的端口接受客户端连接请求处理 SQL 查询请求管理数据库文件、表、索引等对象以及执行各种数据库操作如备份、恢复、复制等。mysqld 进程在运行时会读取 MySQL 配置文件通常是 /etc/my.cnf 文件根据配置文件中的参数设置来决定 MySQL 数据库服务器的行为。
当在系统中安装 MySQL 数据库时通常会同时安装 mysqld 进程。在 Linux 系统中可以通过启动、停止、重启 mysqld 进程来控制 MySQL 数据库服务器的启停和重启常见的命令是 systemctl start/stop/restart mysqld。 查看配置文件
cat /etc/my.cnfdatadir 参数是指定 MySQL 数据库服务器存储数据库文件的目录路径。
建立一个数据库本质是在 Linux 下建立了一个目录。也就是说你不仅可以使用 SQL 指令创建数据库也可以在 datadir 路径下直接新建目录来创建数据库但是我们不建议这样做。
建立一个表本质是在 Linux 下建立了一个文件。
MySQL 数据存储逻辑
MySQL 是一个关系型数据库管理系统RDBMS它是基于表的。
MySQL 中的表是由列列名和列的数据类型和行具体的数据组成的每个表都有一个唯一的表名并且可以在表中定义索引以提高查询效率。
例 SQL 分类
DDL(Data Definition Language)数据定义语言用来维护存储数据的结构侧重于对数据库和表结构进行属性操作。代表指令create drop alterDML(Data Manipulation Language) 数据操纵语言用来对数据进行操作侧重于对数据库和表结构进行内容操作。代表指令insert delete update DML 中以单独分了一个 DQL 数据查询语言代表指令select DCL(Data Control Language) 数据控制语言主要负责权限管理和事务。代表指令grant revoke commit
存储引擎
存储引擎是数据库管理系统存储数据为存储的数据建立索引、更新、查询等技术的实现方法。
MySQL 的核心就是插件式存储引擎支持多种存储引擎。
show engines; -- 查看支持的存储引擎库的操作
查看数据库
语法
SHOW DATABASES;该命令将返回所有可用的数据库列表包括系统默认的数据库和用户创建的数据库。
例
执行该命令将返回类似如下结果
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| stu |
--------------------其中information_schema、mysql 和 performance_schema 是 MySQL 系统默认的数据库。
创建数据库
语法
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];大写的表示关键字。实际上 SQL 语言对关键字的大小写不敏感所以平时写 SQL 语句时关键字可大写也可小写。 [] 表示可选项。
CREATE DATABASE 用于创建一个新的数据库。
[IF NOT EXISTS] 表示如果该数据库不存在才执行创建操作否则将会抛出错误。
database_name 为要创建的数据库的名称可以使用任意字符但需要遵循 MySQL 标识符的命名规则。
[CHARACTER SET charset_name] 用于指定数据库的字符集。
[COLLATE collation_name] 用于指定数据库的排序规则。 例
创建一个名为 mydb 的数据库
CREATE DATABASE mydb;如果该数据库不存在则创建一个名为 mydb 的数据库。如果该数据库已经存在则会返回一个错误。如果需要在创建数据库时指定字符集和排序规则可以使用以下语句
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;该语句创建了一个名为 mydb 的数据库并将其字符集设置为 utf8mb4排序规则设置为 utf8mb4_unicode_ci。如果该数据库已经存在则不会执行创建操作。
注
database_name 通常使用反引号括起来。
在 MySQL 中使用反引号将标识符括起来可以使其成为一个独立的标识符即使标识符中包含了空格、关键字或其他特殊字符。 查看创建语句
SHOW CREATE DATABASE 命令用于显示指定数据库的创建语句。该语句包含了创建数据库时使用的所有参数和选项包括数据库名、字符集、排序规则等。
例 删除数据库
语法
DROP DATABASE [IF EXISTS] database_name;注意
一旦数据库被删除所有与之相关的数据都将永久删除。因此在执行此操作之前请确保已经备份好需要保留的数据。
选择切换数据库
语法
USE database_name;database_name 是要选择的数据库名称。该语句将使得后续的查询、插入、更新等操作默认在该数据库中进行。
查看当前选择的数据库
使用 DATABASE() 函数
SELECT DATABASE();该函数不需要任何参数调用它将返回当前选择的数据库名称。如果当前没有选择任何数据库则该命令返回 NULL。
修改数据库
语法
-- 修改数据库字符集和校验规则
ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation_name;old_database_name 是要修改名称的数据库名称new_database_name 是新的数据库名称。
database_name 是要修改的数据库名称character_set_name 是新的字符集名称collation_name 是新的校验规则名称。
CHARACTER SET 也可以写成 CHARSET
例
将名为 test 的数据库的名称修改为 new_test
ALTER DATABASE test RENAME TO new_test;将 test 数据库的字符集修改为 utf8mb4校验规则修改为 utf8mb4_general_ci
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;注意
修改数据库的字符集和校验规则会影响所有表格和列的字符集和校验规则。因此在执行 ALTER DATABASE 命令之前请确保已经备份了所有数据并且了解修改操作可能带来的风险。
ALTER不支持修改数据库名
MySQL 修改数据库名称比较麻烦不支持直接修改需要通过其它方式间接达到修改数据库名称的目的。
在 MySQL 5.1.23 之前的旧版本中我们可以使用 RENAME DATABASE 来重命名数据库但此后版本因为安全考虑删掉了这一条命令。
上面说过可以通过在 datadir 下创建目录来创建数据库那么当然也可以在 datadir 下直接修改目录名但是我们不建议这样做。
字符集和排序规则
要查看 MySQL 实例的默认字符集和排序规则可以使用以下命令
SHOW VARIABLES LIKE character_set_%;
SHOW VARIABLES LIKE collation_%;字符集和排序规则在 MySQL 中非常重要因为它们直接影响到数据库中存储和处理的数据。
字符集决定了数据库中可以存储哪些字符以及如何存储这些字符。不同的字符集支持不同的字符如 ASCII 字符集只支持英文字母和数字而 UTF-8 字符集支持更广泛的字符范围包括不同语言的文字和符号等。同时字符集还定义了每个字符的编码方式以确保数据可以在不同的系统和应用程序之间正确地传输和处理。
排序规则决定了在对数据库中的字符数据进行排序时使用哪种规则。这是因为不同的字符集和语言中字符的排序方式可能是不同的。例如在中文中字符的排序方式是根据拼音排序而在英文中则是按照字典序排序。如果没有正确地设置排序规则就可能导致排序结果不准确或不符合预期。
因此字符集和排序规则对于正确存储和处理数据库中的字符数据非常重要尤其是在涉及多语言和多字符集的应用程序中。在 MySQL 中通过设置合适的字符集和排序规则可以确保数据库中的数据可以正确地存储、传输和处理。
注:
在“创建数据库”的语法中我们注意到在创建数据库时可以指定字符集和排序规则但是通常我们不这样做。因为不需要指定它就会使用我们已经配置好的默认字符集和排序规则。
表的操作
创建表
语法
CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype constraints,column2 datatype constraints,...columnN datatype constraints
) [CHARSET字符集] [COLLATE校验规则] [ENGINE存储引擎];table_name 是要创建的表格名称。
column1 到 columnN 是表格的列名。
datatype 是列数据类型。
constraints 是列的约束条件如默认值、非空、唯一性等。
最后一行指定字符集校验规则存储引擎是可选项
例
创建一个名为 students 的表格包含 id、name 和 gender 三个字段可以使用以下命令
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50) NOT NULL,gender ENUM(M, F) DEFAULT M
);id、name 和 gender 三个字段分别使用了 INT、VARCHAR 和 ENUM 三种数据类型。其中id 是主键name 不允许为空gender 有默认值 ‘M’且只能取值为 ‘M’ 或 ‘F’。
执行该命令后如果创建成功系统会提示 Query OK, 0 rows affected表示成功创建了一个名为 students 的表格。如果创建失败系统会给出相应的错误提示信息。
注使用 comment 注释表和列
在 MySQL 中可用 comment 注释表和列可以用来记录该表或列的作用、用途、限制、注意事项等信息。
例如在创建一个名为 employees 的表时可以添加一个注释来描述该表的作用
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),hire_date DATE,salary DECIMAL(10,2),comment 员工信息表
);使用 SHOW CREATE TABLE 命令可以查看表的详细信息包括注释信息。例如
SHOW CREATE TABLE employees;输出结果如下
CREATE TABLE employees (id int(11) NOT NULL,name varchar(50) DEFAULT NULL,hire_date date DEFAULT NULL,salary decimal(10,2) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT员工信息表在列中添加注释的方式与上面类似只需要在列定义后面添加 COMMENT 关键字即可。
查询表
在选择了数据库后使用
SHOW TABLES;查询表结构
语法
DESC table_name;执行 DESC 命令后MySQL 会返回该表格的列信息如列名、数据类型、是否允许为空、默认值等。
例
查询刚才创建的 students 表格 该输出表格中的每一行表示一个列的信息包括 Field列名、Type数据类型、Null是否允许为空、Key键类型和 Default默认值Extra 表示该列的一些额外信息如自增长属性、虚拟列等。如果某个字段不允许为空则其 Null 列显示 NO如果该字段为主键则其 Key 列显示 PRI。
插入数据
语法
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);table_name 表示要插入数据的表格名称。
column1, column2, column3, ... 列出了要插入数据的列名称。
value1, value2, value3, ... 对应了这些列要插入的值。
例
向 students 表格中插入一条记录包括 id、name 和 gender 三个列可以使用以下语句
INSERT INTO students (id, name, gender)
VALUES (1, Alice, F);这条语句会向 students 表格中插入一条 id 为 1、name 为 Alice、gender 为 F 的记录。如果不指定列名称可以省略列名列表如下所示
INSERT INTO students
VALUES (1, Alice, F);这条语句与上述语句等价因为 students 表格中的列顺序与指定的值的顺序一致。不过不建议这样写上面的写法不容易出错同时可以使代码更加易于维护。
检索数据
SELECT 语句用于从一个或多个表中检索数据。
语法
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;column1, column2, ... 列出了要检索的列名称可以使用通配符 * 表示所有列。
table_name 指定了要检索数据的表格名称。
WHERE condition 是可选的表示用于筛选要检索的行的条件可以使用比较运算符、逻辑运算符、通配符等进行筛选。
ORDER BY column_name ASC|DESC 是可选的表示按照指定列进行升序或降序排列。
例
从名为 student 的表格中检索所有列 只检索 name 和 gender 两列并且只需要检索 gender 为 F 的记录 将 students 表格按照 id 列进行降序排列 以上只是 SELECT 语句的一些基本用法实际上 SELECT 语句有很多高级用法如聚合函数、子查询、连接查询等这些我们会在后面的章节中进行讲解。
修改表
-- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;-- 修改列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;-- 修改列名
ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型;-- 删除列
ALTER TABLE 表名 DROP 列名;-- 添加主键
ALTER TABLE 表名 ADD PRIMARY KEY (列名);-- 添加唯一索引
ALTER TABLE 表名 ADD UNIQUE (列名);-- 添加普通索引
ALTER TABLE 表名 ADD INDEX (列名);-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;-- 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名; -- TO可省略-- 修改存储引擎
ALTER TABLE 表名 ENGINE存储引擎名称;-- 修改表的字符集和校对规则
ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集名称 COLLATE 校对规则名称;删除表
DROP TABLE [IF EXISTS] table_name [, table_name] ... [RESTRICT | CASCADE];IF EXISTS可选项表示在删除表时如果表不存在则不会报错。table_name需要删除的表的名称支持同时删除多个表用逗号隔开。RESTRICT可选项表示在删除表时如果有其他对象依赖于该表则不会删除该表。CASCADE可选项表示在删除表时同时删除依赖于该表的对象。
备份
使用 mysqldump 工具备份
mysqldump 是一个命令行工具可以用来备份 MySQL 数据库。
语法
mysqldump -u [用户名] -p [密码] [数据库名] [备份文件路径][用户名] 和 [密码] 分别是要使用的 MySQL 用户名和密码
[数据库名] 是要备份的数据库名称[备份文件路径] 是备份文件的保存路径。
注[数据库名] 可以带 -B 选项这样在还原数据库时不需要先创建空的数据库。
例
备份名为 test 的数据库可以使用以下命令
mysqldump -u root -p test /home/user/test_backup.sql本质上
mysqldump 本质上是通过生成一组 SQL 语句来备份数据库中的数据和结构。备份时mysqldump 会将数据库中所有的表结构和数据导出为一组 SQL 语句并将这些语句保存到一个文本文件中。在需要恢复数据时可以使用这个文本文件来重新创建数据库并将数据导入到新创建的数据库中。
打开备份的文件类似于这样 注
如果你只想备份一个数据库中的特定几个表可以使用 mysqldump 命令的 --tables 参数指定要备份的表名语法如下
mysqldump -u username -p --tables database_name table1 table2 table3 backup_file.sql同时备份多个数据库
mysqldump -u username -p --databases db1 db2 db3 backup_file.sql如果备份一个数据库时没有带上 -B 参数在恢复数据库时需要先创建空数据库然后使用数据库再使用 source 来还原。
还原
source命令是 MySQL 客户端工具中的一个命令用于在当前 MySQL 连接中执行指定的 SQL 脚本文件。
基本步骤 选择要执行脚本文件的数据库。 use database_name;使用source命令执行 SQL 脚本文件也就是我们刚刚用 mysqldump 备份出来的 sql 文件。 source /path/to/script.sql;/path/to/script.sql是要执行的SQL脚本文件的路径。
例
进入一个空的数据库 执行备份文件会显示许多 Query OK 查询表表结构检索数据发现数据还原成功 show processlist
SHOW PROCESSLIST 命令用于显示当前 MySQL 服务器上正在执行的所有连接和相应的进程列表。
例 上述结果中每一行代表一个MySQL连接和进程的信息其中Id表示连接IDUser表示连接的用户名Host表示连接的主机名db表示当前连接正在使用的数据库名Command表示当前连接正在执行的命令Time表示连接已经持续的时间单位为秒State表示当前连接的状态Info表示当前连接正在执行的SQL语句或命令。
