1. 库的操作1.1 创建数据库语法CREATE DATABASE [IF NOT EXISTS] db_name [create_specification ...]参数说明IF NOT EXISTS可选项如果要创建的数据库已存在则不会报错避免执行中断。CHARACTER SET指定数据库的字符集决定了可以存储哪些字符如中文、英文等。COLLATE指定字符集的校验规则决定了字符如何比较和排序如是否区分大小写。1.2 创建数据库案例案例1创建默认数据库create database db1;说明未指定字符集和校验规则时MySQL使用默认配置通常是utf8字符集和utf8_general_ci校验规则。案例2指定字符集create database db2 charsetutf8;说明明确指定使用utf8字符集适用于需要存储中文等多语言字符的场景。案例3指定字符集和校验规则create database db3 charsetutf8 collate utf8_general_ci;说明既指定字符集为utf8又指定校验规则为utf8_general_ci不区分大小写。1.3 字符集和校验规则1.3.1 查看系统默认字符集和校验规则show variables like character_set_database; show variables like collation_database;作用查看当前数据库的默认字符集和校验规则。1.3.2 查看数据库支持的字符集show charset;作用列出MySQL支持的所有字符集如utf8、gbk、latin1等。说明字符集决定了可以存储的字符范围例如utf8支持中文而latin1不支持。1.3.3 查看数据库支持的校验规则show collation;作用列出所有可用的校验规则。说明每个字符集对应多个校验规则如utf8_general_ci不区分大小写、utf8_bin二进制比较区分大小写。1.3.4 校验规则对数据库的影响不区分大小写的场景utf8_general_cicreate database test1 collate utf8_general_ci; use test1; create table person(name varchar(20)); insert into person values(a), (A), (b), (B);查询结果select * from person where namea; -- 结果返回 a 和 A因为不区分大小写。 select * from person order by name; -- 排序结果a, A, b, B不区分大小写时a和A被视为相同。区分大小写的场景utf8_bincreate database test2 collate utf8_bin; use test2; create table person(name varchar(20)); insert into person values(a), (A), (b), (B);查询结果select * from person where namea; -- 结果只返回 a因为区分大小写。 select * from person order by name; -- 排序结果A, B, a, b按ASCII码排序大写字母在小写字母之前。实际应用建议用户名/邮箱登录校验建议使用不区分大小写的校验规则避免用户因大小写输入错误而无法登录。密码验证密码应在应用层处理数据库不直接比较密码原文。需要精确匹配的场景如文件路径建议使用区分大小写的校验规则。1.4 操纵数据库1.4.1 查看数据库show databases;作用列出MySQL服务器中的所有数据库。1.4.2 显示创建语句show create database 数据库名;作用查看创建指定数据库的SQL语句。说明MySQL 建议我们关键字使用大写但是不是必须的。数据库名字的反引号,是为了防止使用的数据库名刚好是关键字。/*!40100 default.... */ 这个不是注释表示当前mysql版本大于4.01版本就执行这句话。1.4.3 修改数据库语法ALTER DATABASE db_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name;示例alter database mybase charsetgbk;说明将mytest数据库的字符集修改为gbk。注意修改字符集只会影响之后创建的表不会改变已有表的字符集。1.4.4 数据库删除语法DROP DATABASE [IF EXISTS] db_name;示例drop database if exists test1;执行结果数据库从show databases列表中消失。数据库对应的文件夹被删除。该数据库中的所有表和数据都会被永久删除级联删除。1.4.5 ubuntu下查看MySQL创建的数据库和表的文件信息MySQL存储所有数据库文件的根目录mysql select datadir; or mysql show variables like datadir;通过命令行查看文件结构# 进入MySQL数据目录 sudo ls -l /var/lib/mysql/ # 查看特定数据库的文件假设数据库名为mydb sudo ls -l /var/lib/mysql/mydb/MySQL根据存储引擎的不同会生成不同类型的物理文件文件扩展名存储引擎作用.frm所有引擎存储表的结构定义列名、数据类型、索引等.ibdInnoDB存储表的数据和索引每个表独立表空间.MYDMyISAM存储表的数据MY Data.MYIMyISAM存储表的索引MY Indexibdata1InnoDB系统表空间文件存储共享的系统表和数据ib_logfile0/1InnoDB事务日志文件redo log重要警告生产环境禁止随意删除数据库操作前务必确认数据库名称。建议定期备份重要数据库。1.4.6 备份和恢复1.4.6.1 备份语法mysqldump -P3306 -uroot -p密码 -B 数据库名 备份文件路径示例mysqldump -P3306 -uroot -p123456 -B mytest D:/mytest.sqltest2_backup.sql 把我们整个创建数据库建表导入数据的语句都装载这个文件中:说明mysqldump是MySQL自带的备份工具生成的SQL文件包含完整的建库、建表和插入数据的语句。1.4.6.2 恢复语法source 备份文件路径;示例source D:/mysql-5.7.22/mytest.sql;说明需要在MySQL命令行中执行会逐条执行备份文件中的SQL语句。1.4.6.3 注意事项备份单张或多张表mysqldump -uroot -p 数据库名 表名1 表名2 备份文件.sql备份多个数据库mysqldump -uroot -p -B 数据库1 数据库2 备份文件.sql不带-B参数的备份与恢复备份时mysqldump -uroot -p 数据库名 备份.sql。恢复时需要三步create database 数据库名; -- 先创建空数据库 use 数据库名; -- 切换到该数据库 source 备份.sql; -- 执行恢复最佳实践show processlist;定期备份重要数据库。备份文件应存储在安全的位置最好与数据库服务器分离。恢复前先确认目标数据库为空或做好数据保护。1.4.7 查看连接情况语法show processlist;示例输出字段说明Id连接标识符。User连接的用户。Host连接的主机。db当前连接的数据库。Command当前执行的命令类型Sleep表示空闲。Time命令执行时间。State当前状态。Info正在执行的SQL语句。实际应用性能排查当数据库响应慢时查看是否有长时间运行的查询。安全监控检查是否有异常IP连接数据库。连接数管理查看当前连接数是否过多。安全提示如果发现不是自己登录的陌生连接可能是数据库被入侵的迹象应立即采取措施如修改密码、限制IP访问等。
MySQL关于库的操作
1. 库的操作1.1 创建数据库语法CREATE DATABASE [IF NOT EXISTS] db_name [create_specification ...]参数说明IF NOT EXISTS可选项如果要创建的数据库已存在则不会报错避免执行中断。CHARACTER SET指定数据库的字符集决定了可以存储哪些字符如中文、英文等。COLLATE指定字符集的校验规则决定了字符如何比较和排序如是否区分大小写。1.2 创建数据库案例案例1创建默认数据库create database db1;说明未指定字符集和校验规则时MySQL使用默认配置通常是utf8字符集和utf8_general_ci校验规则。案例2指定字符集create database db2 charsetutf8;说明明确指定使用utf8字符集适用于需要存储中文等多语言字符的场景。案例3指定字符集和校验规则create database db3 charsetutf8 collate utf8_general_ci;说明既指定字符集为utf8又指定校验规则为utf8_general_ci不区分大小写。1.3 字符集和校验规则1.3.1 查看系统默认字符集和校验规则show variables like character_set_database; show variables like collation_database;作用查看当前数据库的默认字符集和校验规则。1.3.2 查看数据库支持的字符集show charset;作用列出MySQL支持的所有字符集如utf8、gbk、latin1等。说明字符集决定了可以存储的字符范围例如utf8支持中文而latin1不支持。1.3.3 查看数据库支持的校验规则show collation;作用列出所有可用的校验规则。说明每个字符集对应多个校验规则如utf8_general_ci不区分大小写、utf8_bin二进制比较区分大小写。1.3.4 校验规则对数据库的影响不区分大小写的场景utf8_general_cicreate database test1 collate utf8_general_ci; use test1; create table person(name varchar(20)); insert into person values(a), (A), (b), (B);查询结果select * from person where namea; -- 结果返回 a 和 A因为不区分大小写。 select * from person order by name; -- 排序结果a, A, b, B不区分大小写时a和A被视为相同。区分大小写的场景utf8_bincreate database test2 collate utf8_bin; use test2; create table person(name varchar(20)); insert into person values(a), (A), (b), (B);查询结果select * from person where namea; -- 结果只返回 a因为区分大小写。 select * from person order by name; -- 排序结果A, B, a, b按ASCII码排序大写字母在小写字母之前。实际应用建议用户名/邮箱登录校验建议使用不区分大小写的校验规则避免用户因大小写输入错误而无法登录。密码验证密码应在应用层处理数据库不直接比较密码原文。需要精确匹配的场景如文件路径建议使用区分大小写的校验规则。1.4 操纵数据库1.4.1 查看数据库show databases;作用列出MySQL服务器中的所有数据库。1.4.2 显示创建语句show create database 数据库名;作用查看创建指定数据库的SQL语句。说明MySQL 建议我们关键字使用大写但是不是必须的。数据库名字的反引号,是为了防止使用的数据库名刚好是关键字。/*!40100 default.... */ 这个不是注释表示当前mysql版本大于4.01版本就执行这句话。1.4.3 修改数据库语法ALTER DATABASE db_name [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name;示例alter database mybase charsetgbk;说明将mytest数据库的字符集修改为gbk。注意修改字符集只会影响之后创建的表不会改变已有表的字符集。1.4.4 数据库删除语法DROP DATABASE [IF EXISTS] db_name;示例drop database if exists test1;执行结果数据库从show databases列表中消失。数据库对应的文件夹被删除。该数据库中的所有表和数据都会被永久删除级联删除。1.4.5 ubuntu下查看MySQL创建的数据库和表的文件信息MySQL存储所有数据库文件的根目录mysql select datadir; or mysql show variables like datadir;通过命令行查看文件结构# 进入MySQL数据目录 sudo ls -l /var/lib/mysql/ # 查看特定数据库的文件假设数据库名为mydb sudo ls -l /var/lib/mysql/mydb/MySQL根据存储引擎的不同会生成不同类型的物理文件文件扩展名存储引擎作用.frm所有引擎存储表的结构定义列名、数据类型、索引等.ibdInnoDB存储表的数据和索引每个表独立表空间.MYDMyISAM存储表的数据MY Data.MYIMyISAM存储表的索引MY Indexibdata1InnoDB系统表空间文件存储共享的系统表和数据ib_logfile0/1InnoDB事务日志文件redo log重要警告生产环境禁止随意删除数据库操作前务必确认数据库名称。建议定期备份重要数据库。1.4.6 备份和恢复1.4.6.1 备份语法mysqldump -P3306 -uroot -p密码 -B 数据库名 备份文件路径示例mysqldump -P3306 -uroot -p123456 -B mytest D:/mytest.sqltest2_backup.sql 把我们整个创建数据库建表导入数据的语句都装载这个文件中:说明mysqldump是MySQL自带的备份工具生成的SQL文件包含完整的建库、建表和插入数据的语句。1.4.6.2 恢复语法source 备份文件路径;示例source D:/mysql-5.7.22/mytest.sql;说明需要在MySQL命令行中执行会逐条执行备份文件中的SQL语句。1.4.6.3 注意事项备份单张或多张表mysqldump -uroot -p 数据库名 表名1 表名2 备份文件.sql备份多个数据库mysqldump -uroot -p -B 数据库1 数据库2 备份文件.sql不带-B参数的备份与恢复备份时mysqldump -uroot -p 数据库名 备份.sql。恢复时需要三步create database 数据库名; -- 先创建空数据库 use 数据库名; -- 切换到该数据库 source 备份.sql; -- 执行恢复最佳实践show processlist;定期备份重要数据库。备份文件应存储在安全的位置最好与数据库服务器分离。恢复前先确认目标数据库为空或做好数据保护。1.4.7 查看连接情况语法show processlist;示例输出字段说明Id连接标识符。User连接的用户。Host连接的主机。db当前连接的数据库。Command当前执行的命令类型Sleep表示空闲。Time命令执行时间。State当前状态。Info正在执行的SQL语句。实际应用性能排查当数据库响应慢时查看是否有长时间运行的查询。安全监控检查是否有异常IP连接数据库。连接数管理查看当前连接数是否过多。安全提示如果发现不是自己登录的陌生连接可能是数据库被入侵的迹象应立即采取措施如修改密码、限制IP访问等。
