学习mysql，最好的学习资料无非两种：书(推荐《高性能mysql》)和官网。个人比较喜欢直接去官网，最直接也最简单。今天，我们来根据官网的说明，到官网example DB下载页下载至本地，为后面的学习做准备。
首先，先下载example DB相关sql和数据。以employees库为例，早些时候，employees相关sql还是可以直接在官网下载的，现在转移到了github，不过没有关系，同样直接下载到本地，执行即可(前提本地安装过mysql服务)。执行过程如下：
-- cd 到employees.sql所在目录
cd /usr/loacl//test_db
-- 登陆mysql
mysql -u root -p password
-- 执行employees.sql
source employees.sql
执行完毕后，查看DB
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| sys |
+--------------------+
-- 查看employees库数据
mysql> use employees
Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
-- 查看employees表数据
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.17 sec)
-- 查看当前emp_no值最大记录
mysql> select * from employees order by emp_no DESC limit 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
这样mysql官方employees库就在本地初始化完毕了，当前employees.employees表仅有30W数据;可以使用show create table table_name查看当前表结构
-- 可以看到，employees表仅有主键emp_no，且主键非自增
mysql> show create table employees ;
-------------------------------------------------------------------------------------------------------------
| Table | Create Table |employees | CREATE TABLE `employees` (
`emp_no` int(11) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
-- 或者使用desc table_name
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
为了方便后面使用，我们再往employees表添加1000W数据，直接用自定义函数执行吧，
-- 定义批量插入函数 insertIntoEmployees
CREATE DEFINER=`root`@`localhost` FUNCTION `insertIntoEmployees`(num int) RETURNS bigint(20)
DETERMINISTIC
BEGIN
DECLARE gender CHAR(10);
SET @var = 0;
WHILE @var < num DO
SET @emp_no_id = @var + 500000;
-- 性别做一点区分
IF( MOD(@var,2) = 0) THEN
SET gender = 'M';
ELSE
SET gender = 'F';
END IF;
INSERT INTO employees(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`) VALUES (@emp_no_id,NOW(),"zhang",CONCAT("",@var),gender,NOW());
SET @var = @var + 1;
END WHILE;
RETURN 1;
END
-- 创建完毕后，执行，执行insertIntoEmployees会比较久
select insertIntoEmployees(10000000);
-- 执行完毕，查看数据结果
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 10300025 |
+----------+
1 row in set (1.68 sec)
-- 查看emp_no值最大的记录
mysql> select * from employees order by emp_no desc limit 1;
+----------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+----------+------------+------------+-----------+--------+------------+
| 10500001 | 2021-05-31 | zhang | 9999999 | F | 2021-05-31 |
+----------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
老是count(*)，写个存储过程看看会不会方便一点
-- 定义存储过程countProcedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `countProcedure`(IN tableName VARCHAR(20),OUT counts int)
BEGIN
Declare stmt varchar(2100);
SET @table_name = "";
SELECT TABLE_NAME from information_schema.TABLES where TABLE_NAME = tableName INTO @table_name;
SET @countSql = CONCAT("SELECT COUNT(*) FROM ",@table_name);
SET @logSql = CONCAT("executed sql : ",@countSql);
-- 这句可以注释掉，打印执行的sql
SELECT @logSql;
prepare stmt from @countSql;
execute stmt;
END
-- 定义完毕后，执行,查其他表，直接替换表名即可
mysql> CALL countProcedure('employees',@result);
+-----------------------------------------------+
| @logSql |
+-----------------------------------------------+
| executed sql : SELECT COUNT(*) FROM employees |
+-----------------------------------------------+
1 row in set (0.01 sec)
+----------+
| COUNT(*) |
+----------+
| 10300025 |
+----------+
1 row in set (1.47 sec)
好了，数据初始化基本上已经完成了，接下来就可以在employees上进行各种练习了，如果觉得employees库不够用，可以按照同样的方式，在本地建其他库。
