Mysql之官方example初始化

学习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库不够用，可以按照同样的方式，在本地建其他库。