下载安装
下载
Win安装版:https://downloads.mysql.com/archives/installer/
Java连接驱动,操作系统选择Platfomm independent:https://downloads.mysql.com/archives/c-j/
卸载
1、关闭mysql服务,卸载软件
2、删除mysql文件,用Everything软件搜索
3、删除注册表,搜索mysql80
4、cmd中删除服务:sc delete mysql80
安装
1、跳过更新
选择No不可更新
2、自定义安装
选择Custom自定义安装
3、选择安装
选择MySQL-Server具体的服务,移动到右边去
4、安装路径
点击Advanced Ootions,配置安装路径
5、密码设置
一直点击Next下一步,到输入确认密码
6、服务设置
可以设置Windows Service Name服务名,勾选Start the MySQL Server at System Startup是否开机自动启动
7、跳过权限配置
配置
环境变量
设置中搜索 编辑系统环境变量,系统变量中选择Path,点击编辑-新建,把mysql安装目录的bin路径配置上
数据库配置
[mysqld]
basedir="D:/Soft/MySQL/MySQL Server 8.0.42"
datadir="D:/Soft/MySQL/MySQL Server 8.0.42/Data"
port=3306
character-set-server=utf8mb4
default-storage-engine=INNODB
max_connections=200
log-error="D:/Soft/MySQL/MySQL Server 8.0.42/Data/error.log"
[client]
port=3306
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
常用函数
-- 日期格式化
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
-- 字符截取
UPDATE 表名
SET compute_date = SUBSTRING(compute_date, 1, 7)
-- 字段分组显示
SELECT
CASE WHEN dept_code = 1 THEN '老师'
WHEN dept_code = 2 THEN '学生'
END AS '其他'
FROM 表名
-- 笛卡尔积
SELECT code1,code2
FROM 表1,表2
-- 增量更新
INSERT INTO a (id, value, update_time)
SELECT * FROM b
ON DUPLICATE KEY UPDATE
update_time = CURRENT_TIMESTAMP; -- 冲突时:更新时间刷新
-- 任意聚合
SELECT
exec_unit_code AS '执行科室代码',
ANY_VALUE(exec_unit_name) AS '执行科室',
charge_item_code AS '收费项目代码',
ANY_VALUE(charge_item_name) AS '收费项目'
FROM (
SELECT
exec_unit_code,
exec_unit_name,
charge_item_code,
charge_item_name
FROM intermediate.v_opd_charge_info
WHERE charge_date BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
GROUP BY exec_unit_code, charge_item_code
UNION ALL
SELECT
exec_unit_code,
exec_unit_name,
charge_item_code,
charge_item_name
FROM intermediate.v_inp_charge_item_info
WHERE charge_date BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'
GROUP BY exec_unit_code, charge_item_code
) AS combined_data
GROUP BY exec_unit_code, charge_item_code;
常见问题
字符集统一
-- 设置字符集
SET collation_connection = 'utf8mb4_general_ci'
-- 查看字符集
SHOW FULL COLUMNS FROM 表名
-- 修改整个表字符集
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
锁表杀进程
-- 找到超时的表,查询超时的SQL
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_query LIKE '%表名%'
-- 杀掉进程 blocking_thread_id
KILL 进程id
忘记密码
-- 关闭服务
net stop mysql80
-- 跳过权限
mysqld --console --skip-grant-tables --shared-memory --datadir="D:/Soft/MySQL/MySQL Server 8.0.42/DB/Data"
-- 重开cmd窗口登录
mysql -u root
-- 刷新权限
flush privileges;
-- 修改密码
alter user root@localhost identified by '123';
查询优化
索引
-- 查看是否命中
type:访问类型
const(唯一索引等值查询)
eq_ref(主键关联)
ref(普通索引等值查询)
range(范围查询)
ALL 可能是全表扫描,索引可能未命中
key:索引名
key_len:索引长度
rows:扫描行数
Extra:额外信息
Using index:覆盖索引,性能极佳
Using index condition:索引下推
Using filesort:需额外排序
Using temporary:需临时表
-- 最佳实践
最左前缀原则:联合索引中,只有查询条件使用了最左列,索引才会生效;
等高优先:值分组更唯一的字段,放在联合索引左侧
等值优于范围:将等值条件(=、IN)的字段,放在范围(>、<、BETWEEN)的字段之前
排序靠后:将排序放最后,并确保索引顺序与排序顺序一致
数量控制:大量索引会降低增删改速度,一个表数量不超过6个
索引覆盖:让索引包含查询所有字段,如 SELECT a, b FROM table WHERE c=1,索引 (c, a, b)
长字符:对长字符串,可只对前几个字符创建:CREATE INDEX idx_email_prefix ON users(email(10))
-- 失效场景
索引使用列函数、计算、类型转换、左模糊查询(LIKE '%abc')
使用OR且两侧条件并非都有索引
索引列在条件中存在范围查询时会熔断,范围之前部分可用使用索引
临时表
-- 创建
CREATE TEMPORARY TABLE temp_high_value_users AS
SELECT user_id, username, email FROM users WHERE total_purchases > 1000;
CTE
Common Table Expression,公用表表达式
-- 使用格式
WITH
cte1 AS (SELECT ... FROM ...),
cte2 AS (SELECT ... FROM cte1 WHERE ...) -- CTE可以引用前面定义的CTE
SELECT ... FROM cte2 ...;
储存过程
-- 包含 IN 和 OUT 参数的例子DELIMITER //CREATE PROCEDURE GetOrderCountByUser(IN user_id INT, OUT order_count INT)BEGINSELECT COUNT(*) INTO order_count FROM orders WHERE user_id = user_id;**END //**DELIMITER ;SET @user_id = 123;SET @count = 0; -- 初始化一个变量用于接收OUT参数的值CALL GetOrderCountByUser(@user_id, @count);SELECT @count AS order_count; -- 查看结果-- 条件判断1IF condition THENstatements;ELSEalternative_statements;END IF;-- 条件判断2CASE case_valueWHEN value1 THEN statements;WHEN value2 THEN statements;ELSE else_statements;END CASE;-- 循环1WHILE condition DOstatements;END WHILE;-- 循环2REPEATstatements;UNTIL conditionEND REPEAT;-- 循环3loop_label: LOOPstatements;IF condition THENLEAVE loop_label; -- 退出循环END IF;END LOOP; -- 查看某个数据库下的所有存储过程
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'your_db_name';-- 查看某个存储过程的定义
SHOW CREATE PROCEDURE your_procedure_name;-- 删除存储过程
DROP PROCEDURE IF EXISTS your_procedure_name;
变量
-- 使用
以 @开头,在当前会话中存储临时值。
修改中赋值: SET @var_name = value
查询中赋值: SELECT @var_name := value;
查询结果赋值:SELECT COUNT(*) INTO @user_count FROM users;
引用:SELECT @var_name
窗口函数
-- 常用函数
1.排序函数:ROW_NUMBER(, RANK(), DENSE_RANK()
2.聚合函数:SUM(), AVG(), MAX(), MIN()
3.偏移函数:LAG(), LEAD()
4.分布函数:NTILE(), CUME_DIST(), PERCENT_RANK()
5.首尾函数:FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()