Mysql实操

安装

清华大学开源镜像文件：
MySQL的版本有很多，这里只安装纯粹的MySQL数据库，不附加任何工具。
下载链接

如果提示： MySQL安装报错：Please install the Redistributable then run this installer again.
先下载安装补丁

MySQL Workbench8.0 安装

MySQL Workbench可视化工具：
下载地址

首页报错：

因为windows中文桌面默认编码问题。
左侧-Administrator打开报错，修改方案：
安装路径：C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\os_utils.py

找到process代码：

process = subprocess.Popen(command, stdin = subprocess.PIPE, encoding="utf-8", stdout = subprocess.PIPE, stderr = subprocess.STDOUT, shell=True)

修改成：

process = subprocess.Popen(command, stdin = subprocess.PIPE, encoding="gbk", stdout = subprocess.PIPE, stderr = subprocess.STDOUT, shell=True)

点击options file报错：

修改my.ini文件：找到中文双引号，修改成英文双引号即可。
查看到data的存储路径，跟my.ini文件是同样的。修改到自己的路径即可。

字段类型

如果想要确切的存储小数(例如，金额等)，建议使用DECIMAL类型，而不是DOUBLE、float类型。

表

字段说明：

常用语句

参考资料

// TRUNCATE TABLE 删除记录 重置自动递增的属性字段
truncate table users;
// DELETE TABLE 删除记录
delete from users;

// IN 和 NOT IN
SELECT * from students where names NOT IN ('zhangyi','wangwu','lisi');

//  DISTINCT 即使符合条件的多条记录，也只会返回一条
SELECT  DISTINCT species from friends_of_pickles where height_cm >50;

// LIMIT 限制条数 放在语句最后
SELECT * FROM friends_of_pickles ORDER BY height_cm LIMIT 2;

// nested query 嵌套查询
SELECT * FROM family_members WHERE num_legs = (SELECT MIN(num_legs) FROM family_members);

// NULL ; NOT NULL
select * from family_members where favorite_book IS NOT NULL;

// multiple joins  复杂联合
SELECT character.name, actor.name
FROM character
INNER JOIN character_actor
ON character.id = character_actor.character_id
INNER JOIN actor
ON character_actor.actor_id= actor.id;

// CASE 语句返回
SELECT *,
CASE WHEN species = 'human' THEN 'talk'
WHEN species = 'dog' THEN 'bark' 
WHEN species = 'cat' THEN 'meow' 
ELSE '' 
END AS sound
FROM friends_of_pickles;

// SUBSTR 字符截取
// SUBSTR(name, 1, 5) 前5个字符
// SUBSTR(name, -4) 最后4个字符
select * from robots where SUBSTR(location,-2) ='NY' 

视图

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view_storage` AS
    SELECT 
        `storage`.`id` AS `id`,
        `storage`.`sno` AS `sno`,
        `storage`.`m_id` AS `m_id`,
        `storage`.`weight` AS `weight`,
        `storage`.`status` AS `status`,
        `storage`.`realweight` AS `realweight`,
        `storage`.`adduser` AS `adduser`,
        `storage`.`addtime` AS `addtime`,
        `material`.`mname` AS `mname`,
        `material`.`code` AS `code`,
        `material`.`unit` AS `unit`,
        `e_auth_local`.`users`.`realname` AS `realname`
    FROM
        ((`storage`
        JOIN `material`)
        JOIN `e_auth_local`.`users`)
    WHERE
        ((`storage`.`m_id` = `material`.`id`)
            AND (`storage`.`adduser` = `e_auth_local`.`users`.`id`))

触发器

修改表，下方tab找到触发器，新建即可。

CREATE DEFINER=`root`@`localhost` TRIGGER `biaoming_AFTER_INSERT` AFTER INSERT ON `biaoming` FOR EACH ROW BEGIN
	-- 变量声明
    declare incount int;
    declare oldnowno int;
    declare newnowno int;
    
	if new.status=1 then
		set incount = new.incount;
         set oldnowno = (select nowno from `order` where id = new.orderid);
        set newnowno = incount + oldnowno;
        update `order` set nowno = newnowno where id = new.orderid;
	end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `storage_BEFORE_INSERT` BEFORE INSERT ON `storage` FOR EACH ROW BEGIN
 SET @lastWeight = (SELECT realweight FROM `ll_mes_local`.`material` WHERE id=new.m_id LIMIT 1);
 SET @lastWeight = @lastWeight+new.weight;
 SET new.realweight= @lastWeight;
 UPDATE  `ll_mes_local`.`material` SET realweight=@lastWeight WHERE id=new.m_id;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `storage_BEFORE_INSERT` BEFORE INSERT ON `storage` FOR EACH ROW BEGIN
 declare lastWeight double;
 SET lastWeight = (SELECT realweight FROM `ll_mes_local`.`material` WHERE id=new.m_id LIMIT 1);
 if new.status = 0 then 
	set lastWeight = lastWeight  + new.weight;
elseif  new.status = 1 then 
	set lastWeight = lastWeight - new.weight;
end if;  
 SET new.realweight= lastWeight;
 UPDATE  `ll_mes_local`.`material` SET realweight=lastWeight WHERE id=new.m_id;
END

导出数据库

导入数据库

找到备份好的数据库文件，如果已经是有create databse的sql，直接点击import按钮即可。
如果没有create database的sql，选择或者新建database，点击import按钮。

数据库定时backup

使用工具SQLyog软件。
具体使用说明，可以查看狂神说笔记
SQLyog软件备份使用说明：参考地址

将Mysql 8.0数据导入到5.7及以下版本

全局搜索sql 替换字符

（8.0）utf8mb4_0900_ai_ci 替换为（5.7） utf8_general_ci

（8.0）utf8mb4 替换为（5.7） utf8