MySQL 数据库已经是目前最流行的数据库了。由于其社区版具有开源、免费、跨平台的特性,而且安装配置容易,功能丰富强大,所以被绝大多数企业作为首选数据库。尤其是对于 .NET 开发人员来说,进入 .NET Core 技术时代,绝大部分人也都从 SQL Server 转向了 MySQL 了,其主要原因还是 SQL Server 在跨平台技术上起步太晚了,在 Linux 平台下使用的公司和开发人员太少了,尤其是在微服务盛行的今天,其技术资料和成熟解决方案太少,再加上其不是开源免费,导致 SQL Server 的市场份额逐步萎缩。
今天发布的这篇博客,不是有关 MySQL 增删改查的基本 SQL 语句的总结,而是针对大家平时很需要,但是却一下子想不起来,在网上又不太好查到的 SQL 语句进行总结。主要目的还是方便我个人在使用的时候,能够快速找到,节省时间。
-- 查询 mysql 目前有哪些数据库 SHOW DATABASES; -- 查看数据库的字符集,格式如下: -- SHOW CREATE DATABASE 数据库名称; SHOW CREATE DATABASE testdb; -- 创建一个数据库,格式如下: -- CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名称 CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8; -- 修改数据库的字符集,格式如下: -- ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称; ALTER DATABASE testdb CHARACTER SET gbk; -- 使用某个数据库,使后续的 SQL 语句默认运行在这个数据库上 USE testdb; -- 查看当前正在使用哪个数据库 SELECT DATABASE(); -- 删除数据库,格式如下: -- DROP DATABASE IF EXISTS 数据库名称; DROP DATABASE IF EXISTS testdb;
-- 以我自己创建的数据库来举例 USE testdb; -- 查看 testdb 数据库中有哪些表 SHOW TABLES; -- 查看具体一张表的表结构 -- 格式为:DESC 表名; DESC users; -- 查看具体一张表的字符集 -- 格式为:SHOW TABLE STATUS FROM 库名 LIKE '表名'; SHOW TABLE STATUS FROM testdb LIKE 'users'; -- 修改一张表的字符集 -- 格式为:ALTER TABLE 表名 CHARACTER SET 字符集名称; ALTER TABLE users CHARACTER SET gbk; -- 参照现有数据库中的某张表,创建一个结构相同的表(只参考结构,不包含数据) -- 格式为:CREATE TABLE 表名 LIKE 被参照的表名; CREATE TABLE my_user LIKE users; -- 修改一个表的名称 -- 格式为:ALTER TABLE 表名 RENAME TO 新的表名; ALTER TABLE my_user RENAME TO custom_user; -- 为表添加一列 -- 格式为:ALTER TABLE 表名 ADD 列名 数据类型; ALTER TABLE users ADD gender VARCHAR(10); -- 修改一个表中某一列的名称 -- 格式为:ALTER TABLE 表名 MODIFY 列名 新数据类型; ALTER TABLE users MODIFY gender INT; -- 同时修改某一列的名称和数据类型 -- 格式为:ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; ALTER TABLE users CHANGE gender company VARCHAR(100); -- 删除表中某一列 -- 格式为:ALTER TABLE 表名 DROP 列名; ALTER TABLE users DROP company; -- 删除某张表 -- 格式为:DROP TABLE IF EXISTS 表名; DROP TABLE IF EXISTS custom_user;
大部分情况下,我们都使用图形化工具进行 mysql 中某个数据库的备份和还原,但是如果你想通过程序定期备份 mysql 中某个数据库的话,可以考虑通过程序执行命令行来实现备份还原。
注意:通过命令行备份 mysql 中某个数据库,并不是登录到 mysql 中执行备份命令,而是运行 mysql 的 bin 目录下的 mysqldump 这个可执行文件来备份数据库。在 windows 操作系统,需要远程连接到 mysql 安装的服务器上,在 linux 需要通过 Xshell 等工具连接到 mysql 所在服务器。
-- 备份语句格式为:mysqldump -u root -p 数据库名称 > 文件保存路径 -- windows 下的备份示例: mysqldump -u root -p testdb > d:\testdb20220131.sql -- linux 下的备份示例: mysqldump -u root -p testdb > /databak/testdb20220131.sql
还原数据库的命令操作,则需要登录到 mysql 中运行。一般情况下,建议创建一个新数据库,将备份先还原到这个新建的数据库中,然后通过修改程序的数据库连接字符串,实现数据库的切换。
mysql 官方没有提供修改数据库名称的命令。如果非得想让还原后的数据库名称跟原数据库名称相同的话,只能先删除原来的数据库,然后新建一个相同名称的数据库,然后将备份还原到新数据库中。这种操作相对比较危险一些。
在 Windows 操作系统,假如我们还是远程连接到 mysql 安装的服务器上,通过 cmd 窗口命令行进入 mysql 的 bin 目录下,在 linux 需要通过 Xshell 等工具连接到 mysql 所在服务器。
-- 登录到 mysql 中之后,执行还原命令的格式为:source 备份文件路径; -- 登录 mysql 格式为:mysql {-h 主机ip} {-P 端口号} -u 账号 -p {使用的数据库名称} -- 其中 {} 的内容,可以省略,默认使用 3306 端口,登录 localhost 的 mysql 数据库 mysql -u root -p -- 数据 root 账号的密码后,登录到 mysql 中 -- 新建一个数据库 CREATE DATABASE IF NOT EXISTS testdb2 CHARACTER SET utf8; -- 使用新数据库 USE testdb2; -- windows 下的还原示例: SOURCE d:\testdb20220131.sql -- linux 下的还原示例: SOURCE /databak/testdb20220131.sql
mysql 操作用户的绝大多数命令,需要使用 root 账号来操作,其它账号没有权限。
-- 查看当前登录用户 select user(); -- 登录到 mysql 之后,修改当前登录用户的密码 mysql -u root -p -- 输入密码正确后,登录到 mysql 中,然后通过以下语句修改密码 SET PASSWORD = '123456'; -------------------------------- -- 创建新用户:授权必须指定 ip 192.168.1.44 才能访问 mysql 的用户 wolfer create user 'wolfer'@'192.168.1.44' identified by '123456'; -- 创建新用户:授权必须指定 ip 段 192.168.1.* 才能访问 mysql 的用户 monkey create user 'monkey'@'192.168.1.%' identified by '123456'; -- 创建新用户:授权任何 ip 都能访问 mysql 的用户 alpha create user 'alpha'@'%' identified by '123456'; -- 删除用户 drop user 'wolfer'@'192.168.1.44'; -- 修改用户名 rename user 'alpha'@'%' to 'jobs'@'%'; -- 修改某个用户的密码 set password for 'monkey'@'192.168.1.%' = 'fastorder'; -------------------------------- -- 查看权限 show grants for 'jobs'@'%' -- 授权 jobs 用户仅对 testdb.user 表有查询、插入和更新的操作 grant select,insert,update on testdb.user to 'jobs'@'%'; -- 授权 jobs 用户对 testdb 下的所有表任意操作 grant all privileges on testdb.* to 'jobs'@'%'; -- 授权 jobs 用户对所有数据库中文件有任何操作 grant all privileges on *.* to 'jobs'@'%'; -- 取消 jobs 用户对 testdb 的 user 表的任意操作 revoke all on testdb.user from 'jobs'@'%'; -- 取消来自远程服务器的 jobs 用户对数据库 testdb 的所有表的所有权限 revoke all on db1.* from 'jobs'@'%'; -- 取消来自远程服务器的 jobs 用户所有数据库的所有的表的权限 revoke all privileges on *.* from 'jobs'@'%'; -- 注意:以上授权操作,以及取消授权操作, -- 在被操作账号不重新登录的情况下,需要执行以下语句才能生效 flush privileges;
OK,已经总结完毕,内容不是很多,希望上面这些 SQL 语句,能够对大家有所帮助。