Windows下MySQL示例库employees安装教程

Windows下MySQL示例库employees安装教程

操作系统：win7 64位

MySql版本：

示例库版本：employees_db-full-1.0.6.tar.bz

一、示例下载

employees示例下载地址https://https://www.360docs.net/doc/8812940824.html,/test-db/

下载employees_db-full-1.0.6.tar.bz版本

https://https://www.360docs.net/doc/8812940824.html,/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz

下载完毕后记录解压位置，例如我的位置为：D:\employees_db；

二、 employees.sql文件编辑

编辑文件主要有两处：

1、以下两处地方，将storage_engine修改为default_storage_engine

setdefault_storage_engine = InnoDB;

select CONCAT('storage engine: ', @@default_storage_engine) as INFO;

2、source *.dump 在dump文件前都加入绝对路径，注意将路径中的“\”为“/”，例如sourceD:/employees_db/load_departments.dump;

为了方便可以将解压目录下文件employees.sql替换成以下内容，注意解压路径：

DROP DATABASE IF EXISTS employees;

CREATE DATABASE IF NOT EXISTS employees;

USE employees;

SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';

DROP TABLE IF EXISTS dept_emp,dept_manager,titles,salaries,employees,departments; setdefault_storage_engine = InnoDB;

select CONCAT('storage engine: ', @@default_storage_engine) as INFO;

CREATE TABLE employees (

emp_no INT NOT NULL,

birth_date DATE NOT NULL,

first_name VARCHAR(14) NOT NULL,

last_name VARCHAR(16) NOT NULL,

gender ENUM ('M','F') NOT NULL,

hire_date DATE NOT NULL,

PRIMARY KEY (emp_no)

);

CREATE TABLE departments (

dept_no CHAR(4) NOT NULL,

dept_name VARCHAR(40) NOT NULL,

PRIMARY KEY (dept_no),

UNIQUE KEY (dept_name)

CREATE TABLE dept_manager (

dept_no CHAR(4) NOT NULL,

emp_no INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

KEY (dept_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no)

);

CREATE TABLE dept_emp (

emp_no INT NOT NULL,

dept_no CHAR(4) NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

KEY (dept_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no)

);

CREATE TABLE titles (

emp_no INT NOT NULL,

title VARCHAR(50) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

KEY (emp_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no,title, from_date)

);

CREATE TABLE salaries (

emp_no INT NOT NULL,

salary INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

KEY (emp_no),

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

PRIMARY KEY (emp_no, from_date)

SELECT 'LOADING departments' as 'INFO';

source D:/employees_db/load_departments.dump;

SELECT 'LOADING employees' as 'INFO';

source D:/employees_db/load_employees.dump;

SELECT 'LOADING dept_emp' as 'INFO';

sourceD:/employees_db/load_dept_emp.dump;

SELECT 'LOADING dept_manager' as 'INFO';

sourceD:/employees_db/load_dept_manager.dump ;

SELECT 'LOADING titles' as 'INFO';

sourceD:/employees_db/load_titles.dump;

SELECT 'LOADING salaries' as 'INFO';

sourceD:/employees_db/load_salaries.dump;

三、 dump文件导入

打开命令行cmd，切换目录路径到解压路径，比如我的就是D:\employees_db；

cd /d D:\employees_db

mysql -t -uroot -p

按回车会提示你输入密码

其中-t代表的是一种输出格式，-u后面即为你的用户名，-p后面代表的是当前用户名的密码，等待几秒钟，因为数据量比较大，最后你可以进入到你的mysql图形化工具或者命令行，使用show databases便可以查到数据库已经导入到你的数据库了。

参考文档：

[1]在windows导入mysql的示例employees数据库

https://www.360docs.net/doc/8812940824.html,/sfqn_pgs/article/details/50876395

[2]Mysql导入官方示例employees问题解决记录

https://www.360docs.net/doc/8812940824.html,/storage_networking/2016/04/1449660.html