MySQL基础知识入门:

SQL语句分类：

数据定义语言（DDL）：用于定义或更改数据库、表、索引等结构。

CREATE DATABASE / TABLE / INDEX：用于创建数据库、表或索引。ALTER DATABASE / TABLE：用于修改数据库或表的结构。DROP DATABASE / TABLE / INDEX：用于删除数据库、表或索引。

数据操作语言（DML）：用于插入、更新、删除和选择（检索）数据库中的数据。

INSERT INTO：用于插入新数据。UPDATE：用于更新现有数据。DELETE FROM：用于删除数据。SELECT：用于检索数据。

数据控制语言（DCL）：这些语句用于控制对数据库的访问，例如授权和撤销授权。

GRANT：用于授予权限。REVOKE：用于撤销权限。

事务控制语言（TCL）：这些语句用于控制事务处理，如开始事务、提交事务或回滚事务。

START TRANSACTION：开始一个新的事务。COMMIT：提交当前事务。ROLLBACK：回滚当前事务。

常用操作命令：

基础操作命令：

登录数据库：

mysql -u root -p

查看数据库版本：

SELECT VERSION();

显示当前用户：

SELECT USER();

查看数据库列表：

SHOW DATABASES; 

删除数据库：

DROP DATABASE <数据库名>;

连接数据库：

USE <数据库名>;

查看数据表：

SHOW TABLES;

删除数据表：

DROP TABLE <数据表名>;

创建数据表：例如：

CREATE TABLE ATT(ID INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME CHAR(20) NOT NULL,DEGREE DOUBLE(16,2));

插入数据：例如插入到ATT表中：

INSERT INTO TESTT(ID, NAME, DEGREE) VALUES(1, 'laowu', 3.14);

更新数据：例如更新ATT表中的NAME：

UPDATE ATT SET NAME = 'laowu' WHERE ID = 1;

删除数据：例如删除TESTT表中的数据：

DELETE FROM ATT WHERE ID = 1;

查询数据：例如查询ATT表中的所有数据：

SELECT * FROM ATT;

查询特定列：例如查询TESTT表中的NAME列：

SELECT NAME FROM ATT;

查询特定条件：例如查询TESTT表中NAME为'Tom'的数据：

SELECT * FROM ATT WHERE NAME = 'laowu';

排序查询结果：例如按DEGREE列降序排列查询ATT表的数据：

SELECT * FROM ATT ORDER BY DEGREE DESC;

分组查询结果：查询每个班级的学生数量,GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT class, COUNT(*) as student_count  FROM students  GROUP BY class;

LEFT JOIN(或 LEFT OUTER JOIN)返回左表中的所有记录和右表中连接字段相等的记录：列出所有学生及其可能的考试成绩（即使有些学生没有成绩）。

SELECT students.name, exams.score  FROM students  LEFT JOIN exams ON students.id = exams.student_id;

RIGHT JOIN(或RIGHT OUTER JOIN)返回右表中的所有记录和左表中连接字段相等的记录：获取所有考试的成绩，即使有些学生不存在（这在实际场景中很少见）

SELECT students.name, exams.score  FROM students  RIGHT JOIN exams ON students.id = exams.student_id;

IN 操作符允许你在 WHERE 子句中指定多个值：查询ID为1, 3, 5的学生的名字

SELECT name  FROM students  WHERE id IN (1, 3, 5);

EXISTS 用于测试子查询是否至少返回一个记录: 查询有考试成绩的学生

SELECT name  FROM students  WHERE EXISTS (      SELECT 1      FROM exams      WHERE students.id = exams.student_id  );

NOT EXISTS 与 EXISTS 相反，用于测试子查询是否不返回任何记录：查询没有考试成绩的学生

SELECT name  FROM students  WHERE NOT EXISTS (      SELECT 1      FROM exams      WHERE students.id = exams.student_id  );

NOT IN 操作符用于在 WHERE 子句中排除指定的多个值:查询ID不是1, 3, 5的学生的名字

SELECT name  FROM students  WHERE id NOT IN (1, 3, 5);

限制查询结果数量：例如查询ATT表中的前5条数据：

SELECT * FROM ATT LIMIT 5;

添加索引：例如在ATT表的NAME列上添加索引：

ALTER TABLE ATT ADD INDEX idx_name (NAME);

删除索引：例如删除ATT表的NAME列上的索引：

ALTER TABLE ATT DROP INDEX idx_name;

视图/存储过程/触发器常用命令：

创建视图：例如创建一个名为view_a的视图，查询ATT表中的NAME和DEGREE列：

CREATE VIEW view_a AS SELECT NAME, DEGREE FROM ATT;

删除视图：例如删除view_a视图：

DROP VIEW view_a;

创建存储过程：例如创建一个名为proc_testa的存储过程，用于查询TESTT表中的数据：

CREATE PROCEDURE proc_testa()BEGINSELECT * FROM ATT;END;

调用存储过程：例如调用proc_testa存储过程：

CALL proc_testa();

创建触发器：例如创建一个触发器，在ATT表中的数据被插入时执行特定操作：

CREATE TRIGGER after_insert_attAFTER INSERT ON ATTFOR EACH ROWBEGIN-- 在这里编写触发器的操作END;

删除触发器：例如删除触发器after_insert_att：

DROP TRIGGER after_insert_att;

创建函数：例如创建一个名为get_avg_degree的函数，计算TESTT表中的平均DEGREE值：

CREATE FUNCTION get_avg_degree()RETURNS DOUBLEBEGINDECLARE avg_degree DOUBLE;SELECT AVG(DEGREE) INTO avg_degree FROM TESTT;RETURN avg_degree;END;

调用函数：例如调用get_avg_degree函数：

SELECT get_avg_degree();

创建游标：例如创建一个游标，用于遍历查询结果：

DECLARE cur CURSOR FOR SELECT * FROM TESTT;OPEN cur;FETCH cur;-- 在这里编写处理游标的逻辑CLOSE cur;

事务/权限常用命令：

事务处理：使用BEGIN, COMMIT和ROLLBACK语句进行事务处理，确保数据的完整性和一致性。例如：

START TRANSACTION;INSERT INTO TESTT(ID, NAME, DEGREE) VALUES(2, 'Jerry', 3.14);COMMIT; -- 提交事务

关闭自动提交：可以使用SET autocommit=0语句关闭自动提交，以便进行事务处理：

SET autocommit=0;

开启自动提交：在事务处理完成后，使用COMMIT语句提交事务并开启自动提交：

COMMIT;SET autocommit=1;

设置用户权限：例如为用户'username'设置对数据库'mydb'的所有权限：

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost';

撤销用户权限：例如撤销用户'username'对数据库'mydb'的所有权限：

REVOKE ALL PRIVILEGES ON mydb.* FROM 'username'@'localhost';

创建备份：可以使用mysqldump工具对数据库进行备份，例如备份数据库mydb到文件mydb_backup.sql：

mysqldump -u username -p mydb > mydb_backup.sql

恢复备份：使用mysql命令将备份文件恢复到数据库中，例如从mydb_backup.sql恢复到mydb数据库：

mysql -u username -p mydb < mydb_backup.sql

导入数据：可以使用LOAD DATA INFILE语句将数据文件导入到数据库表中，例如将data.txt文件导入到ATT表的ID和NAME列中：

LOAD DATA INFILE 'data.txt' INTO TABLE ATT FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

导出数据：可以使用SELECT ... INTO OUTFILE语句将查询结果导出到文件中，例如将TESTT表的数据导出到data.txt文件中：

SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM TESTT;

设置字符集：可以使用SET NAMES语句设置数据库连接的字符集，例如设置字符集为UTF-8：

SET NAMES 'utf8';

性能优化常用命令：

查看当前服务器配置的最多连接数

SHOW VARIABLES LIKE 'max_connections';

查看当前已使用最大连接数

SHOW GLOBAL STATUS LIKE '%max_used_connections%';

查看最大连接数

SHOW GLOBAL STATUS LIKE '%Threads_connected%';

查看当前服务器配置的等待超时时间

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

查看当前服务器配置的交互式超时时间

SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

查看当前服务器的慢查询日志

SHOW GLOBAL STATUS LIKE 'Slow_queries';

查看当前服务器的查询缓存使用情况

SHOW GLOBAL STATUS LIKE 'Qcache%';

查看当前服务器的InnoDB存储引擎的使用情况

SHOW GLOBAL STATUS LIKE 'Innodb%';

查看当前服务器的状态信息

SHOW GLOBAL STATUS;

优化表

首先，确保你已经备份了重要的数据。使用以下命令来优化表：

OPTIMIZE TABLE tablename;

优化查询语句

使用EXPLAIN命令来分析查询的执行计划，找出性能瓶颈：

EXPLAIN SELECT * FROM table WHERE condition;

SELECT id, name FROM table WHERE condition;

SELECT * FROM table WHERE condition LIMIT 10;

优化数据表结构

使用适当的数据类型，例如使用INT代替VARCHAR：

ALTER TABLE table MODIFY column INT;

ALTER TABLE table ADD COLUMN last_updateTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP;

OPTIMIZE TABLE table;

优化索引

为经常用于查询条件的字段创建索引：

CREATE INDEX index_name ON table (column);

SHOW INDEX FROM table;

CREATE INDEX index_name ON table (column1, column2);

配置优化

调整my.cnf或my.ini配置文件中的参数，如innodb_buffer_pool_size, query_cache_size, max_connections等：

[mysqld]innodb_buffer_pool_size=1Gquery_cache_size=256Mmax_connections=500

使用缓存

开启查询缓存（如果使用的是旧版本的MySQL）：

SET GLOBAL query_cache_type = 1;

监控和日志

使用工具如MySQLTuner来评估MySQL的性能并给出建议。查看慢查询日志以找到需要优化的查询：

SHOW GLOBAL VARIABLES LIKE 'slow_query_log';SHOW GLOBAL VARIABLES LIKE 'long_query_time';