登录mysql数据库
注：文中mysql版本为8.0而并非5.7
使用命令行登录
在操作系统中打开命令行工具/或使用xshell等ssh登录
使用命令行登录数据库：根据所使用的数据库类型，输入相应的命令行登录命令。
mysql -u [用户名] -p[密码] -P 3306 -h [数据库地址]
-u 用户名 -p密码 -P端口 -h 数据库地址
登录成功：如果用户名和密码正确无误，命令行工具会显示登录成功，并允许用户进行数据库操作。
注意：输入密码时与p之间不要有空格，不然空格也会被视为密码的一部分，这通常会导致登录失败
使用客户端登录（如navicat等）
输入主机（本地连接默认用localhost）、端口（默认是3306）、用户名、密码后
点击左下角测试连接，连接成功后点击确定保存即可通过客户端访问mysql数据库。
数据库管理相关
创建数据库：
创建数据库是数据库管理的第一步。使用以下SQL语句创建一个新的数据库：
CREATE DATABASE database_name
[ character_set_collate ]
[ ENCRYPTION [=] { 'Y' | 'N' } ]
[ DATA DIRECTORY = 'path/to/directory' ]
[ INDEX DIRECTORY = 'path/to/directory' ]
[ CHARACTER SET charset_name ]
[ COLLATE collation_name ]
[ SQL SECURITY { DEFINER | INVOKER } ]
[ COMMENT 'string' ];
database_name是新数据库的名称。
character_set_collate指定默认的字符集和校对规则。
ENCRYPTION 选项用于指定数据库文件是否应该被加密。
DATA DIRECTORY和INDEX DIRECTORY选项用于指定数据库文件和索引文件的存储路径。
CHARACTER SET和COLLATE选项用于指定数据库的默认字符集和校对规则。
SQL SECURITY选项用于定义存储过程和函数的SQL安全特性。
COMMENT选项用于添加数据库的描述。
删除数据库
删除数据库是一个危险的操作，在生产正式环境请做好备份谨慎使用。使用以下SQL语句删除一个数据库：
DROP DATABASE database_name;
database_name是要删除的数据库的名称。
修改数据库
修改数据库通常涉及更改数据库的默认字符集、校对规则或其他属性。使用以下SQL语句修改数据库：
ALTER DATABASE database_name
[ character_set ]
[ collate ]
[ ENCRYPTION { 'Y' | 'N' } ]
[ UPGRADE DATA DIRECTORY NAME ]
[ REMOVE DEFINER { 'CURRENT_USER' | 'CURRENT_ROLE' | user | role } ]
[ DEFAULT ROLE role FOR user ]
[ SQL SECURITY { DEFINER | INVOKER } ]
[ COMMENT 'string' ];
character_set和collate选项用于更改数据库默认字符集和校对规则。
ENCRYPTION选项用于更改数据库文件的加密状态。
UPGRADE DATA DIRECTORY NAME选项用于更改数据库文件的存储路径。
REMOVE DEFINER选项用于更改存储过程和函数的DEFINER。
DEFAULT ROLE选项用于为用户设置默认角色。
SQL SECURITY和 COMMENT选项用于更改数据库的SQL安全特性和描述。
查看数据库列表
查看当前MySQL服务器上所有数据库的列表：
SHOW DATABASES
[ LIKE 'pattern' | WHERE expr ];
LIKE 'pattern'用于过滤数据库名称。
WHERE expr用于使用条件表达式过滤数据库。
选择数据库
在执行数据库操作之前，通常需要选择一个数据库：
USE database_name;
database_name是要操作的数据库的名称。
用户和权限管理
数据库管理还包括用户账户和权限的管理。以下是一些基本的权限管理命令：
创建用户：
CREATE USER user_name [IDENTIFIED BY 'password'];
删除用户：
DROP USER user_name;
授予权限：
GRANT privileges ON database_name TO user_name;
撤销权限：
REVOKE privileges ON database_name FROM user_name;
刷新权限：
FLUSH PRIVILEGES;
数据库维护
数据库维护包括检查和修复数据库、优化表和分析表等操作。以下是一些基本的维护命令：
检查表：
CHECK TABLE table_name;
修复表：
REPAIR TABLE table_name;
优化表：
OPTIMIZE TABLE table_name;
分析表：
ANALYZE TABLE table_name;
表管理相关
创建表结构：
CREATE TABLE table_name (column1 datatype1 [constraint1], column2 datatype2 [constraint2],... [ table_options ]);
table_name 是新表的名称。
column1, column2, ... 是表中的列名。
datatype 是列的数据类型，如 INT, VARCHAR, DATE 等。
constraints 是列的约束条件，如 NOT NULL, PRIMARY KEY, FOREIGN KEY 等。
table_options 是表级别的选项，如字符集、存储引擎等。
查看表结构：
查看表的结构，包括列名、数据类型、是否可以为NULL、键信息等：
DESCRIBE table_name;
DESC table_name;
table_name 是表的名称。
修改表结构：
添加新列：
ALTER TABLE table_name
ADD column_name datatype;
修改列：
ALTER TABLE users MODIFY COLUMN email VARCHAR(500) NOT NULL;
删除列：
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
重命名列：
ALTER TABLE table_name
CHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME datatype;
添加主键：
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
删除主键：
ALTER TABLE table_name
DROP PRIMARY KEY;
重命名表：
RENAME TABLE old_table_name TO new_table_name;
复制表结构：
CREATE TABLE new_table_name LIKE existing_table_name;
⑨ 复制表结构和数据：
CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;
查看所有表：
SHOW TABLES;
删除表：
DROP TABLE users;
索引管理：
创建索引：
创建单列索引：
CREATE INDEX index_name ON table_name (column_name);
创建复合索引（多列索引）：
CREATE INDEX index_name ON table_name (column_name1, column_name2);
查看索引：
查看某个表的所有索引：
SHOW INDEXES FROM table_name;
查看数据库中所有索引：
SHOW INDEXES FROM database_name;
删除索引：
当索引不再需要时，使用DROP INDEX语句删除索引。
删除指定的索引：
DROP INDEX index_name ON table_name;
如果索引属于当前数据库，可以省略数据库名：
DROP INDEX index_name ON table_name;
修改索引：
mysql不支持直接修改索引，但可以通过删除再新建的方式来修改。
优化索引：
优化表的索引，回收未使用的空间，更新索引的统计信息：
对于有大量更新、删除操作的表，定期执行此命令可能有助于维护索引性能。
OPTIMIZE TABLE table_name;
分析索引：
分析表的索引和数据，收集关于索引选择性的信息：
ANALYZE TABLE table_name;
检查索引：
检查表的索引是否损坏：
CHECK TABLE table_name;
修复索引：
如果索引损坏，可以使用以下命令尝试修复：
REPAIR TABLE table_name;
约束管理：
创建约束
主键约束：
CREATE TABLE table_name (
column_name1 INT NOT NULL,
column_name2 VARCHAR(100),
PRIMARY KEY (column_name1)
);
唯一约束：
CREATE TABLE table_name (
column_name1 INT NOT NULL,
column_name2 VARCHAR(100),
UNIQUE (column_name2)
);
外键约束：
CREATE TABLE child_table (
child_id INT,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
);
检查约束（MySQL 8.0及以上版本）：
CREATE TABLE table_name (
column_name INT,
CHECK (column_name > 0)
);
查看约束
查看表的约束：
SHOW CREATE TABLE table_name;
查看所有外键约束：
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'table_name';
删除约束
删除主键约束：
ALTER TABLE table_name DROP PRIMARY KEY;
删除唯一约束：
ALTER TABLE table_name DROP INDEX unique_index_name;
删除外键约束：
ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;
删除检查约束（MySQL 8.0及以上版本）：
ALTER TABLE table_name DROP CHECK check_constraint_name;
修改约束
修改主键约束：
先删除旧的主键约束，然后添加新的主键约束：
ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (new_column_name);
修改外键约束：
先删除旧的外键约束，然后添加新的外键约束：
ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;
ALTER TABLE child_table ADD FOREIGN KEY (new_column_name) REFERENCES parent_table(parent_id);
添加约束
在已存在的表中添加约束：
添加唯一约束：
ALTER TABLE table_name ADD UNIQUE (column_name);
添加外键约束：
ALTER TABLE child_table ADD FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id);
添加检查约束（MySQL 8.0及以上版本）：
ALTER TABLE table_name ADD CHECK (column_name > 0);
数据操作
详细说明数据的增删改查（CRUD）操作。
(1)创建（Create）
插入单行数据：
使用 SQL 的 INSERT 语句。例如，在一个名为“students”的表中插入一条学生记录，表中有“id”（整数类型，主键）、“name”（字符串类型）、“age”（整数类型）等字段。
INSERT INTO students (id, name, age) VALUES (1, '张三', 18);
解释：这条语句将一个学生的信息插入到“students”表中，指定了学生的编号为 1，姓名为“张三”，年龄为 18。
插入多行数据：
可以一次性插入多条记录，使用多个 VALUES 子句。
INSERT INTO students (id, name, age) VALUES (2, '李四', 19), (3, '王五', 20);
解释：这条语句同时插入了两个学生的信息。
(2)读取（Read）
查询所有数据
使用 SELECT * FROM 表名; 可以查询表中的所有数据。
SELECT * FROM students;
解释：这条语句将返回“students”表中的所有学生记录。
查询特定字段数据
可以指定要查询的字段名称。
SELECT name, age FROM students;
解释：这条语句只查询“students”表中的学生姓名和年龄字段。
条件查询
使用 WHERE 子句添加查询条件。
SELECT * FROM students WHERE age > 18;
解释：这条语句查询“students”表中年龄大于 18 的学生记录。
排序查询
使用 ORDER BY 子句对查询结果进行排序。
SELECT * FROM students ORDER BY age DESC;
解释：这条语句查询“students”表中的所有学生记录，并按照年龄降序排列。
(3)更新（Update）
更新单个字段
使用 UPDATE 表名 SET 字段名=新值 WHERE 条件;
UPDATE students SET age = 20 WHERE id = 1;
解释：这条语句将“students”表中编号为 1 的学生年龄更新为 20。
更新多个字段
可以同时更新多个字段的值。
UPDATE students SET name = '张三新', age = 19 WHERE id = 1;
解释：这条语句将编号为 1 的学生姓名更新为“张三新”，年龄更新为 19。
(4)删除（Delete）
删除单行数据
使用 DELETE FROM 表名 WHERE 条件;
DELETE FROM students WHERE id = 1;
解释：这条语句删除“students”表中编号为 1 的学生记录。
删除所有数据
如果不指定条件，将删除表中的所有数据。
DELETE FROM students;
注意：这种操作要谨慎使用，因为它会删除所有数据且通常不可恢复。
sql语法指南
sql函数
Sql函数：
是对数据执行操作的预定义的代码块。MySQL 提供了多种内置函数，包括字符串函数、数值函数、日期和时间函数等。
(1)字符串函数：
CONCAT(str1, str2, ...)：将多个字符串连接在一起。
LOWER(str)：将字符串转换为小写。
UPPER(str)：将字符串转换为大写。
TRIM(str)：去除字符串两端的空格。
LENGTH(str)：返回字符串的长度。
LOCATE(substring ，string，start_position)：返回子字符串在字符串中的位置。
(2)数值函数：
ABS(number)：返回数值的绝对值。
ROUND(value, decimal_places)：将数值四舍五入到指定的小数位数。
CEILING(value)：返回大于或等于数值的最小整数。
FLOOR(value)：返回小于或等于数值的最大整数。
RAND（）：返回一个随机数。
SQRT（value）：返回一个数在平方根。
(3)日期和时间函数：
NOW()：返回当前的日期和时间。
CURDATE()：返回当前的日期。
CURTIME()：返回当前的时间。
YEAR（date）:从日期中提取年份。
DATEDIFF(date1, date2)：返回两个日期之间的天数差。
TIMESTAMPDIFF(unit, datetime1, datetime2)：返回两个日期/时间之间的差异。
(4)聚合函数：
COUNT(column)：返回列中非 NULL 值的数量。
SUM(column)：返回列中数值的平均和。
AVG(column)：返回列中数值的平均值。
MIN(column)：返回列中的最小值。
MAX(column)：返回列中的最大值。
sql表达式
SQL 表达式是由一个或多个 SQL 元素组成的组合，用于在查询时执行计算。这些元素可以是列名、常量、函数，也可以是算术运算符、比较运算符和逻辑运算符。
(1)算术表达式：
由数值常量、列名和算术运算符（+、-、*、/、%等）组成。
(2)比较表达式：
由列名、常量、函数返回值和比较运算符（=、<、>、<=、>=、<> 等）组成。
(3)逻辑表达式：
由比较表达式、逻辑运算符（AND、OR、NOT 等）组成。
(4)字符串表达式：
由字符串常量、列名（代表字符串数据）和字符串运算符（如连接运算符 || 或函数 CONCAT () 等）组成。
(5)日期表达式：
由日期常量、列名（代表日期数据）和日期函数、运算符组成。
(6)位运算表达式
位运算表达式在处理二进制数据时使用：
位与（&）
位或（|）
位异或（^）
位非（~）
位左移（<<）
位右移（>>）
高级特性
索引创建与优化：
索引可以加快数据检索速度，但会减慢数据插入、更新和删除操作。
索引的类型：
单列/普通索引：基于单个列创建的索引。
普通索引是最常见的索引类型。
它用于加速查询操作，提高查询性能。
允许重复值，可以在一个字段上创建多个普通索引。
复合索引：基于多个列创建的索引。
唯一索引：确保索引列中的值是唯一的。
唯一索引要求索引列的值是唯一的，不允许重复值。
它用于保证数据的唯一性约束。
唯一索引可以加速唯一性检查和查找操作。
全文索引：
全文索引用于支持全文搜索功能。
它可以在文本数据中快速搜索关键词，支持模糊搜索、分词和关键词匹配等操作。
全文索引适用于文本处理和搜索引擎等应用场景。
空间索引：
空间索引用于处理空间数据，如地理位置信息、二维图形等。
它支持空间范围查询和最近邻查询等操作。
空间索引使用特定的空间算法来加速空间数据的检索。
聚簇索引（Clustered Index）：决定表中数据的物理存储顺序。
非聚簇索引（Non-Clustered Index）：不影响表中数据的物理存储顺序。
索引的创建:
使用CREATE INDEX语句创建索引。
例如，创建一个单列索引：CREATE INDEX index_name ON table_name(column_name);
索引的方法：
B树索引（B-Tree Index）：
B树索引是一种常见的平衡树结构索引，适用于范围查询和精确查找。
B树索引适用于传统的关系型数据库系统，如MySQL、Oracle等。
B树索引的特点是支持高效的插入和删除操作，能够自动平衡和调整树结构。
哈希索引（Hash Index）：
哈希索引使用哈希表的结构来快速查找数据，适用于等值查找。
哈希索引通常用于内存数据库或具有特定查询模式的场景。
哈希索引的特点是查找速度快，但不支持范围查询和排序操作。
索引的优化：
选择合适的列创建索引：
通常选择经常用于查询条件、连接条件和排序的列创建索引。
避免过多的索引：
过多的索引会增加数据库的维护成本，降低数据插入、更新和删除的性能，检查并移除重复或不必要的索引，以减少存储空间占用和更新开销。
定期维护索引：
随着数据的变化，索引可能会变得碎片化，影响性能。可以定期使用数据库管理工具对索引进行重建和优化。
创建复合索引：
对于多列查询，创建复合索引可以提高查询性能。将最具选择性的列放在索引前面。
注意复合索引的列顺序，确保符合查询的最左前缀原则。
使用覆盖索引：
确保查询的所有列都在索引中，这样MySQL可以直接从索引中获取数据，而不需要访问表的数据行。
监控索引使用情况：
使用EXPLAIN和SHOW INDEX命令来检查索引的使用情况，识别未使用的索引。
如何避免索引失效：
避免在索引列上使用函数或运算符，这会导致索引失效。
确保查询条件从索引的最左列开始，以利用索引。
确保索引类型与查询的数据类型要匹配
避免使用不等于（!= 或 <>）操作符
避免在LIKE语句中以通配符%开头
不满足复合索引的最左前缀原则
避免使用is null和is not null
避免OR连接条件：
将OR条件改为多个查询并使用UNION ALL合并结果，以利用索引。
优化查询语句：
使用EXPLAIN分析查询执行计划，确保索引被有效利用。
避免全表扫描，尽量让查询条件涉及到索引字段。
使用前缀索引：
对于长字符串列，可以使用前缀索引来节省空间并提高查询效率。
视图
视图定义：
视图是基于SQL查询的虚拟表，它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作。视图可以看作是一个动态生成的数据表，其内容是从其他表中选择、过滤和计算得到的。可以简化查询语句，提高查询的可维护性和可读性。
视图用途：
简化复杂查询：
通过将复杂的查询逻辑封装到视图中，可以简化应用程序中的查询操作。
数据安全性：
视图可以限制用户对底层表的访问权限，从而提供更高的数据安全性和隐私保护。
数据抽象和封装：
通过视图，可以将多个表的数据抽象为一个虚拟表，简化数据模型和应用程序开发。
性能优化：
视图可以预先计算和存储结果集，以提高查询性能，并避免重复执行复杂查询。
视图优点：
数据的抽象和简化：
视图是一个虚拟表，它可以根据特定的查询语句从一个或多个表中选择、过滤和计算数据。通过使用视图，可以将复杂的查询逻辑和多表连接操作封装为一个简化的视图查询，提供了更简洁、更易于理解的数据模型。
数据安全性：
视图可以限制用户对底层表的访问权限。通过给用户授予对视图的访问权限，可以隐藏底层表的结构和敏感数据，只允许用户在特定条件下查看和操作数据。这为数据库提供了更高的安全性和数据保护。
逻辑数据分离和模块化：
通过视图，可以将数据逻辑分离为不同的模块。这使得数据库的维护和管理更加灵活，可以根据需要对各个模块进行独立修改和优化，无需影响其他模块。
提高查询性能：
视图可以预先计算和存储查询结果，从而提高查询性能。当使用视图进行查询时，MySQL 可以利用预先计算的结果，而不需要重新执行复杂的查询操作。这对于频繁执行相同查询的场景非常有用。
简化应用开发：
通过将复杂的查询逻辑封装为视图，应用程序开发人员可以更快速、更轻松地构建应用程序。他们只需要简单地查询视图，而无需关心视图背后的复杂查询逻辑和表结构。
视图缺点：
性能影响：
视图查询可能在执行时产生额外的性能开销。因为视图是根据查询语句动态生成的，每次查询时都需要重新计算视图的结果。对于复杂的视图和大型数据集，这可能导致查询较慢，影响数据库性能。
更新限制：
默认情况下，MySQL 不允许对包含特定条件的视图进行更新操作。这些条件包括使用聚合函数、DISTINCT、GROUP BY 和 HAVING 等的视图。因此，如果你使用的视图有这些限制条件，你将无法对其进行直接的插入、更新或删除操作。
数据一致性：
视图查询的结果是根据底层表的数据动态生成的，而不是存储实际的数据副本。这意味着如果底层表的数据发生了变化，但视图查询结果没有及时更新，可能导致数据一致性的问题。
限制和复杂性：
视图的使用是受到一些限制的，特别是在涉及复杂的查询和多表连接时。一些复杂的查询逻辑和操作可能无法在视图中实现，这可能需要使用其他技术或重新设计查询。
管理复杂性：
随着数据库中视图的数量增加，管理和维护视图变得更加困难。复杂的视图层次结构和依赖关系可能会导致维护和调试问题的增加
创建视图：
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition`。
使用视图：
可以像使用普通表一样对视图进行查询、插入、更新和删除操作，但需要注意的是，对视图的操作最终会影响到底层的表。
存储过程/函数
定义：
存储过程：
是一组预先编译好的 SQL 语句集合，存储在数据库中，可以被反复调用，用于执行复杂的操作，可以接收输入参数，并返回输出参数或结果集。
函数：
一种特殊类型的存储过程，它只返回一个标量值（单个数据值）。
主要区别：
返回值：
存储过程可以返回多个值（通过输出参数）或结果集。
函数只能返回单个标量值。
事务控制：
存储过程通常在事务中执行，这意味要么所有语句都成功执行，要么整个事务回滚。
函数可以在不使用事务的情况下执行，称为不确定性函数。
副作用：
存储过程通常具有副作用，例如更新或插入数据。
函数通常没有副作用，并且只返回一个值。
可重用性：
存储过程和函数都可以在多次查询中重用。
不过，函数通常比存储过程更易于重用，因为它们只返回一个值。
性能：
存储过程通常比函数性能更好，因为它们是预编译的。
但是，如果函数只执行简单的计算，则它可能比存储过程更快。
选择标准：
如果需要执行复杂的操作，具有副作用并需要返回多个值，则使用存储过程。
如果只需要返回单个标量值并且没有副作用，则使用函数。
创建存储过程/函数：
DELIMITER //
CREATE PROCEDURE procedure_name(param1 TYPE, param2 TYPE, ...)
BEGIN
-- SQL statements
-- 可以包括SELECT语句、INSERT、UPDATE、DELETE等
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION function_name(param1 TYPE, param2 TYPE, ...)
RETURNS TYPE
BEGIN
-- SQL statements
-- 通常包含SELECT语句，返回单个值
END //
DELIMITER ;
(5)调用存储过程/函数：
CALL procedure_name(PARAMETERS)`。
CALL InsertEmployee('John Doe', 5000.00, 'Marketing');
SELECT name, CalculateAge(birth_date) AS age FROM employees;
触发器
定义：
触发器是自动执行的SQL语句，当满足特定条件时触发。
是一种特殊的存储过程，它在特定的数据库操作（如INSERT、UPDATE或DELETE）之前或之后自动执行。触发器可以用来执行数据校验、自动更新其他表中的数据、维护审计日志等。
创建触发器：
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW SQL_STATEMENTS`。
DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END //
DELIMITER ;
注意事项：
性能影响：
触发器可能会影响数据库操作的性能，因为它们会在每次相关的数据库操作时执行。
调试难度：
触发器的代码可能难以调试，因为它们的执行是由数据库管理系统自动管理的。
逻辑复杂性：
过度使用触发器可能会导致数据库逻辑变得复杂和难以维护。
版本兼容性：
在不同的数据库版本之间迁移时，触发器的代码可能需要调整以确保兼容性。
权限要求：
创建触发器需要相应的数据库权限。
递归触发：
触发器可能会递归触发自身或其他触发器，这可能导致无限循环，需要谨慎设计。
数据一致性：
触发器中的错误可能会导致数据不一致，因此在编写触发器时需要确保逻辑正确。
监控和日志记录：
虽然触发器可以用于日志记录，但过多的日志记录可能会影响性能，并且需要定期清理日志表。