Mybatis框架学习的SQL

数据表

/*
Navicat Premium Data Transfer

Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80018
Source Host : localhost:3306
Source Schema : mydb

Target Server Type : MySQL
Target Server Version : 80018
File Encoding : 65001

Date: 24/12/2020 13:34:06
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for bonus

---

DROP TABLE IF EXISTS bonus;
CREATE TABLE bonus (
ENAME varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
JOB varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
SAL double(7, 2) NULL DEFAULT NULL,
COMM double(7, 2) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of bonus

---

---

– Table structure for dept

---

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
DEPTNO int(2) NOT NULL,
DNAME varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
LOC varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (DEPTNO) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of dept

---

INSERT INTO dept VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

---

– Table structure for emp

---

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
EMPNO int(4) NOT NULL,
ENAME varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
JOB varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
MGR int(4) NULL DEFAULT NULL,
HIREDATE date NULL DEFAULT NULL,
SAL double(7, 2) NULL DEFAULT NULL,
COMM double(7, 2) NULL DEFAULT NULL,
DEPTNO int(2) NULL DEFAULT NULL,
PRIMARY KEY (EMPNO) USING BTREE,
INDEX FK_DEPTNO(DEPTNO) USING BTREE,
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of emp

---

INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800.00, NULL, 20);
INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600.00, 300.00, 30);
INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250.00, 500.00, 30);
INSERT INTO emp VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975.00, NULL, 20);
INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250.00, 1400.00, 30);
INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850.00, NULL, 30);
INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450.00, NULL, 10);
INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000.00, NULL, 10);
INSERT INTO emp VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500.00, 0.00, 30);
INSERT INTO emp VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23’, 1100.00, NULL, 20);
INSERT INTO emp VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03’, 950.00, NULL, 30);
INSERT INTO emp VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-12-03’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300.00, NULL, 10);

---

– Table structure for salgrade

---

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
GRADE int(11) NOT NULL,
LOSAL double(7, 2) NULL DEFAULT NULL,
HISAL double(7, 2) NULL DEFAULT NULL,
PRIMARY KEY (GRADE) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

---

– Records of salgrade

---

INSERT INTO salgrade VALUES (1, 700.00, 1200.00);
INSERT INTO salgrade VALUES (2, 1201.00, 1400.00);
INSERT INTO salgrade VALUES (3, 1401.00, 2000.00);
INSERT INTO salgrade VALUES (4, 2001.00, 3000.00);
INSERT INTO salgrade VALUES (5, 3001.00, 9999.00);

SET FOREIGN_KEY_CHECKS = 1;

关联查询需要的数据：

CREATE TABLE projects (
pid int(2) NOT NULL AUTO_INCREMENT,
pname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
money int(11) NULL DEFAULT NULL,
PRIMARY KEY (pid) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO projects VALUES (1, ’ ***大学OA’, 500000);
INSERT INTO projects VALUES (2, ‘学生选课系统’, 100000);
INSERT INTO projects VALUES (3, ‘讲师测评系统’, 20000);
INSERT INTO projects VALUES (4, '线上问答系统 ', 20000);
CREATE TABLE projectrecord (
empno int(4) NOT NULL,
pid int(2) NOT NULL,
PRIMARY KEY (empno, pid) USING BTREE,
INDEX fk_project_pro(pid) USING BTREE,
CONSTRAINT fk_emp_pro FOREIGN KEY (empno) REFERENCES emp (EMPNO) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_project_pro FOREIGN KEY (pid) REFERENCES projects (pid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO projectrecord VALUES (7369, 1);
INSERT INTO projectrecord VALUES (7521, 1);
INSERT INTO projectrecord VALUES (7369, 2);
INSERT INTO projectrecord VALUES (7499, 2);
INSERT INTO projectrecord VALUES (7521, 2);
INSERT INTO projectrecord VALUES (7369, 3);
INSERT INTO projectrecord VALUES (7499, 3);
INSERT INTO projectrecord VALUES (7521, 3);
INSERT INTO projectrecord VALUES (7369, 4);
INSERT INTO projectrecord VALUES (7499, 4);

错误的解决办法

MySql数据库导入sql错误 Unknown collation: ‘utf8mb4_0900_ai_ci’
错误原因：高版本数据库（8.0）转存sql文件 并导入低版本数据库（5.7）

解决办法：

方案一：升级mysql至高版本

方案二：将需要导入的sql文件，把其中的
utf8mb4_0900_ai_ci全部替换为utf8_general_ci
utf8mb4替换为utf8
重新执行sql文件