MySql

安装

1. 下载 mysql 5.7 + sequel-pro【迅雷下载比较快】
   • server: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-macos10.14-x86_64.dmg
   • client: https://github.com/sequelpro/sequelpro/releases/download/release-1.1.2/sequel-pro-1.1.2.dmg
2. 双击安装
   • 下一步下一步下一步下一步
   • 首次安装 mysql 会提供默认密码，一定要记录 mysql 分配的初始密码。
3. 添加环境变量：
   • vim .zshrc
   • alias mysql=/usr/local/mysql/bin/mysql
   • source .zshrc
4. 初次部署修改密码
   • mysql -uroot -p
   • SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘aaa123456’);
5. 如果 sql 不生效：可能是 sql mode 的问题，
   参考：https://www.jianshu.com/p/500e2397c15b

卸载

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

常用 SQL

-- 建表
CREATE TABLE `db_odeer_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `env` varchar(20) DEFAULT NULL COMMENT '系统环境',
  `user_name` varchar(50) NOT NULL COMMENT '用户名',
  `user_mail` varchar(50) DEFAULT NULL COMMENT '用户邮箱',
  `user_info` text COMMENT '用户信息',
  `fe_route` varchar(200) DEFAULT NULL COMMENT '前端路由',
  `api_route` varchar(200) DEFAULT NULL COMMENT '接口路由',
  `page_name` varchar(100) NOT NULL COMMENT '功能名、页面名',
  `request_all_info` text COMMENT '所有请求信息',
  `create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
  `update_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '更新时间',
  `system` varchar(50) DEFAULT NULL COMMENT '系统',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70743 DEFAULT CHARSET=utf8mb4 COMMENT='Node层';
-- 删表
DROP TABLE db_odeer_map;
-- 插入记录
INSERT INTO `db_odeer_map`(name, age, sex) VALUES ('xiaoa','18', '1');
-- 查询记录
SELECT * FROM `db_odeer_map`
-- 更新记录
UPDATE db_odeer_map SET status= 3, auditor = '算法审核' WHERE id = 1;
-- 删除记录
DELETE FROM db_odeer_map WHERE user_id=3;
-- 聚合
SELECT
    page_name,
    GROUP_CONCAT(DISTINCT user_name, '-', user_mail) as user_name,
    count(page_name) as num,
FROM
    db_odeer_map
where
    env = '${env}'
    and create_time > '${startTime}'
GROUP BY
    page_name DESC,
    user_name
ORDER BY
    num DESC