参考:http://dev.mysql.com/doc/employee/en/employees-preface.html
mysql提供了一套测试数据,可以用来帮助我们学习MySQL。
(1)下载数据
下载地址
(2)导入建库建表语句和数据
employee.sql中是建库建表语句,默认使用的是InnoDB引擎,可以自行修改
set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
使用mysql命令行工具,导入数据
mysql -uroot -p -t < employees.sql
这里会报错:
ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'
这是因为下载的数据没有跟着mysql版本升级改变,mysql5.7.5以后,这个变量被移除了,改用default_storage_engine就可以了
set default_storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
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)
);
(3)表结构的E-R图如下: