虚构一个微型在线书店的数据库和数据，作为后续MySQL脚本的执行源，方便后续MySQL和SQL的练习。
在虚构这个库的过程中，主要涉及的是如何使用命令行管理 MySQL数据库对象：数据库、表、索引、外键等；另一个更为重要的是如何Mock对应表的数据。
在虚构这个库的过程中，主要涉及的是如何使用命令行管理 MySQL数据库对象：数据库、表、索引、外键等；另一个更为重要的是如何Mock对应表的数据。
1、使用Navicat来管理mysql数据库，安装Navicat，点击左上方的链接，选择mysql并链接到你的mysql数据库 2、随便输入一个链接名，填写mysql用户名跟密码，这里建议输入本地mysql数据库便于区分，点击测试链接，成功，保存单前数据。
虚构书店数据库的dump脚本：Github
数据库（Database）
将要创建的虚拟书店的数据库名为： mysql_practice;
创建数据库的语法：
CREATE DATABASE [IF NOT EXISTS] database_name[CHARACTER SET charset_name][COLLATE collation_name]
IF NOT EXISTS： 可选项，避免数据库已经存在时报错。
CHARACTER SET：可选项，不指定的时候会默认给个。查看当前MySQL Server支持的字符集(character set):show character set; -- 方法1 show charset; -- 方法2 show char set; -- 方法3
COLLATE：针对特定character set比较字符串的规则集合；可选项，不指定的时候会默认给个。获取 charater set的 collationsshow collation like &39;; collation名字的规则： charater_set_name_ci 或者 charater_set_name_cs 或 charater_set_name_bin；_ci表示不区分大小写，_cs表示区分大小写；_bin表示用编码值比较。
示例：CREATE DATABASE my_test_tb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
TODO： 关于 character set和collations，内容稍微有点多，后面会单独记一篇文章。
登录的时候选择数据库
mysql -uroot -D database_name -p
登录后选择数据库
use database_name;
查看当前选的数据库
select database();
创建新数据库
create database if not exists mysql_practice;
通过下面的语句可以检查创建的数据库：
show create database mysql_practice;
可以看到，如果创建数据库时候没有指定 character set 和 collate 的话，会默认指定一套。
显示所有当前账户可见的数据库
show databases;
删除数据库
drop database if exists mysql_practice;
MySQL中 schema 是 database 的同义词，因此也可以使用下面语句删除数据库：
drop schema if exists mysql_practice;
数据表（Table）
MySQL创建数据表的语法
表列的定义语法：
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint;
表的约束（Table Constraints）: UNIQUE，CHECK，PRIMARY KEY and FOREIGN KEY.
查看表的定义
desc table_name;
01 我们可以利用Navicat这个客户端工具来创建表。 打开Navicat，连接上要操作的数据库，点击打开这个库。02 打开后，在右边的视图上就可以看到这个库里所有的表了。
创建mysql_practice数据表
导入region数据
下载region csv数据：【三级】省市区 数据下载.
导入语句：
LOAD DATA INFILE &39; INTO TABLE region FIELDS TERMINATED BY &39; ENCLOSED BY &34;&39;\n'IGNORE 1 ROWS;
导入如果报错：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
通过命令 mdfind -name my.cnf 找到mysql配置文件 my.cnf；
解决办法 (还没实际测试过，大都使用的是 LOATA DATA LOCAL INFILE 方式)
或者使用 LOAD DATA LOCAL INFILE代替 LOAD DATA INFILE 即：
LOAD DATA LOCAL INFILE &39; INTO TABLE region FIELDS TERMINATED BY &39; ENCLOSED BY &34;&39;\n'IGNORE 1 ROWS;
如果报错：
Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides
或者报错:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
查看配置: show variables like &34;;
修改配置: set global local_infile = 1;
生成Customer数据
创建一个SP：
USE mysql_practice;DROP PROCEDURE IF EXISTS sp_generate_customers;DELIMITER $$CREATE PROCEDURE sp_generate_customers()BEGIN-- Generate 10000 customer and customer_addressset @fNameIndex = 1;set @lNameIndex = 1;loop_label_f: LOOP IF @fNameIndex > 100 THEN LEAVE loop_label_f; END IF;set @fName = ELT(@fNameIndex，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;); loop_label_last: LOOP IF @lNameIndex > 100 THEN LEAVE loop_label_last; END IF; SET @lName =ELT(@lNameIndex，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;，&34;); -- insert into customer INSERT INTO customer(no，first_name，last_name，status，phone_number，updated_at，created_at) values( REPLACE(LEFT(uuid()，16)，&39;，&39;)， @fName， @lName， &39;， null， curdate()， curdate());-- insert into customer_addressset @randomArea = 0;SELECT id into @randomArea FROM region where deep = 2 ORDER BY RAND() LIMIT 1;INSERT INTO customer_address(customer_id，area_id，address_detail，is_default，updated_at，created_at)VALUES( @@Identity， @randomArea， &39;， 1， curdate()， curdate()); set @lNameIndex = @lNameIndex + 1; END LOOP loop_label_last; SET @lNameIndex = 1; -- Note: assign 1 to last name index，for next loop. SET @fnameIndex = @fnameIndex + 1;END LOOP loop_label_f;-- update address_detail in customer_addressUPDATE customer_address caJOIN region r on ca.area_id = r.id and r.deep = 2join region r2 on r.pid = r2.id and r2.deep = 1join region r3 on r2.pid = r3.id and r3.deep = 0SET ca.address_detail = concat(r3.ext_name，r2.ext_name，r.ext_name);END $$DELIMITER ;
调用SP:
call sp_generate_customers();
生成产品分类和产品数据
第零步： 手动插入产品分类到product_category表中
INSERT INTO product_category(code，name，parent_id，deep，updated_at，created_at)VALUES(&39;，&39;，0，0，curdate()，curdate())，(&39;，&39;，1，1，curdate()，curdate())，(&39;，&39;，1，1，curdate()，curdate())，(&39;，&39;，1，1，curdate()，curdate());
第一步： 用Python写个爬虫工具，抓取书店的商品信息。
下面是抓取当当搜索“科学”关键字的书籍列表。
import requestsimport csvfrom bs4 import BeautifulSoupdef crawl(url):res = requests.get(url)res.encoding = &39;soup = BeautifulSoup(res.text，&39;)n = 0section = soup.find(&39;，id=&39;)allLIs = section.find_all(&39;)39;output_science.csv&39;w&39;utf8&39;39;) 39;，&39;39;作为csv分隔符csv_writer.writerow([&39;，&39;，&39;，&39;，&39;，&39;])for books in allLIs:title = books.select(&39;)[0].text.strip().split(&39;，1)[0].strip()price = books.select(&39;)[0].text.strip(&39;)authorInfo = books.select(&39;)[0].text.strip().split(&39;)author = authorInfo[0]publishDate = authorInfo[1]publisher = authorInfo[2]n += 1csv_writer.writerow([n，title，price，author，publishDate，publisher])url = ';crawl(url)
第二步： 导入csv数据到MySQL数据表mock_science中。
CREATE TABLE `mock_science` (`id` int(11) NOT NULL， `name` varchar(200) DEFAULT NULL， `price` double DEFAULT NULL， `author` varchar(100) DEFAULT NULL， `publish_date` varchar(100) DEFAULT NULL， `publisher` varchar(100) DEFAULT NULL， PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
第三步： 插入科学类书信息到product表中
INSERT book(category_id，no，name，status，unit_price，author，publish_date，publisher，updated_at，created_at)SELECT 4，REPLACE(LEFT(uuid()，16)，&39;，&39;)，name，&39;，price，author，publish_date，publisher，curdate()，curdate()FROM mock_science;
循环第一到第三步，可以插入更多的产品信息。练习数据库最终抓取了JAVA，儿童，科学三个关键搜索出的第一页书籍。
生成订单数据
随机生成订单数据的SP(注意：这个sp生成的数据，还需要进一步处理):
USE mysql_practice;DROP PROCEDURE IF EXISTS sp_generate_orders;DELIMITER $$-- Reference:Generate orders for last two years.-- each day have orders range: [500，5000]CREATE PROCEDURE sp_generate_orders()BEGINSET @startDate = &39;;SET @endDate = curdate();loop_label_p: LOOP IF @startDate > @endDate THEN LEAVE loop_label_p;END IF; SET @randCustomerTotal = FLOOR(RAND()*50) + 100;SET @randBookTotal = FLOOR(RAND()*5) + 1; SET @randQty = FLOOR(RAND()*3) + 1; SET @query1 = CONCAT(&39;);SET @query1 = CONCAT(@query1，&39;，&39;&34;&34;，&39;，@randQty，&39;，&39;&34;&34;，&39;，&39;&34;&34; ，&39;，&39;&34;&34;);SET @query1 = CONCAT(@query1，&39;，@randCustomerTotal，&39;);SET @query1 = CONCAT(@query1，&39;，@randBookTotal，&39;);SET @query1 = CONCAT(@query1，&39;); PREPARE increased FROM @query1; EXECUTE increased;SET @startDate = DATE_ADD(@startDate， INTERVAL 1 DAY);END LOOP loop_label_p;END $$DELIMITER ;
总共会生成几十万或上百万条order数据；最好先简单加下index，不然query太慢，可以在创建db table后就加上。
添加index:
01 首先打开navicat for mysql工具，打开一个数据库，然后右键单击表，选择新建表，如下图所示 02 接下来在弹出的新建表界面中，我们通过添加栏位来增加表的字段，如下图所示 03 。
ALTER TABLE book ADD INDEX idx_unit_price(unit_price);ALTER TABLE customer_order ADD INDEX idx_order_no(no);ALTER TABLE customer_order ADD INDEX idx_order_date(order_date);ALTER TABLE customer_order ADD INDEX idx_quantity(quantity);
查mysql版本及安装情况：输入status可查看版本号，端口号等安装信息，用mysql客户端还可查看到安装路径。查建删数据库：查现有数据库：show databases; (database的复数形式，最后以英文分号“;”结尾)。新建数据库：create。
更新order no:
-- update order total_price-- please note it is better to add index first. otherwise it will be slow.-- update order_noupdate customer_orderset no = concat(REPLACE(LEFT(no，16)，&39;，&39;)，customer_id，book_id)where no is not null;-- update total price
如果不想有重复的order no，可以通过下面的sql更新order no:
-- 处理重复的 order noupdate customer_order cojoin(select no from customer_order co2 group by co2.no having count(*) > 1) as cdoon co.no = cdo.noset co.no = concat(REPLACE(LEFT(uuid()，16)，&39;，&39;)，customer_id，book_id);
如果还有重复的order no，继续run上面这个sql，直到没有重复的即可。
更新order表里的total_price:
-- update total priceupdate customer_order cojoin book bon co.book_id = b.idSET co.total_price = co.quantity * b.unit_price;
至此，我们的数据库表和对应的mock数据已经基本完成。使用mysqldump备份一下：
mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
下一步
视图（View）
存储过程（Store Procedure）
函数（Function）
触发器（Trigger）
定时任务（Job）
如果觉得本文对你有帮助，可以转发关注支持一下
