學(xué)習(xí)mysql，最好的學(xué)習(xí)資料無非兩種：書(推薦《高性能mysql》)和官網(wǎng)。個人比較喜歡直接去官網(wǎng)，最直接也最簡單。今天，我們來根據(jù)官網(wǎng)的說明，到官網(wǎng)example DB下載頁下載至本地，為后面的學(xué)習(xí)做準(zhǔn)備。

首先，先下載example DB相關(guān)sql和數(shù)據(jù)。以employees庫為例，早些時候，employees相關(guān)sql還是可以直接在官網(wǎng)下載的，現(xiàn)在轉(zhuǎn)移到了github，不過沒有關(guān)系，同樣直接下載到本地，執(zhí)行即可(前提本地安裝過mysql服務(wù))。執(zhí)行過程如下：

-- cd 到employees.sql所在目錄
cd /usr/loacl//test_db
-- 登陸mysql
mysql -u root -p password
-- 執(zhí)行employees.sql
source employees.sql

執(zhí)行完畢后，查看DB

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
-- 查看employees庫數(shù)據(jù)
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表數(shù)據(jù)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.17 sec)

-- 查看當(dāng)前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庫就在本地初始化完畢了，當(dāng)前employees.employees表僅有30W數(shù)據(jù);可以使用show create table table_name查看當(dāng)前表結(jié)構(gòu)

-- 可以看到，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數(shù)據(jù)，直接用自定義函數(shù)執(zhí)行吧，

-- 定義批量插入函數(shù) 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;
        -- 性別做一點區(qū)分
        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

-- 創(chuàng)建完畢后，執(zhí)行，執(zhí)行insertIntoEmployees會比較久
select insertIntoEmployees(10000000);
-- 執(zhí)行完畢，查看數(shù)據(jù)結(jié)果
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);
    -- 這句可以注釋掉，打印執(zhí)行的sql
    SELECT @logSql;
    prepare stmt from @countSql;
    execute stmt;
END

-- 定義完畢后，執(zhí)行,查其他表，直接替換表名即可
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)

好了，數(shù)據(jù)初始化基本上已經(jīng)完成了，接下來就可以在employees上進(jìn)行各種練習(xí)了，如果覺得employees庫不夠用，可以按照同樣的方式，在本地建其他庫。