MySQL示例数据库sakila-db的安装导入
sakila库是一个MySQL官方提供的模拟电影出租厅信息管理系统的数据库,类似于Oracle的scott库,sakila库的下载地址为:http://downloads.mysql.com/docs/sakila-db.zip。压缩包包括3个文件:sakila-schema.sql、sakila-data.sql和sakila.mwb,分别是sakila库的表结构创建、数据灌入、sakila的MySQL Workbench数据模型(可以在MySQL工作台打开查看数据模型)。
sakila-db.zip
下载:
https://dev.mysql.com/doc/index-other.html
或
http://downloads.mysql.com/docs/sakila-db.zip
[root@LHRDB tmp]# unzip sakila-db.zip Archive: sakila-db.zip creating: sakila-db/ inflating: sakila-db/sakila-data.sql inflating: sakila-db/sakila-schema.sql inflating: sakila-db/sakila.mwb [root@LHRDB tmp]# cd sakila-db [root@LHRDB sakila-db]# ll total 3396 -rw-r--r-- 1 root root 3398148 Jul 21 2016 sakila-data.sql -rw-r--r-- 1 root root 50019 Jul 21 2016 sakila.mwb -rw-r--r-- 1 root root 23424 Jul 21 2016 sakila-schema.sql [root@LHRDB sakila-db]# mysql < sakila-schema.sql [root@LHRDB sakila-db]# mysql < sakila-data.sql [root@LHRDB sakila-db]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.19 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_name | | db_name2 | | lhrdb | | mysql | | performance_schema | | sakila | | sys | +--------------------+ 8 rows in set (0.05 sec) mysql> use sakila; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec) mysql> select count(*) from customer; +----------+ | count(*) | +----------+ | 599 | +----------+ 1 row in set (0.00 sec) |
通常情况下对于一个全新的MySQL服务器,没有任何数据供我们测试和使用。对此,MySQL为我们提供了一些样本数据库,我们可以基于这些数据库作基本的操作以及压力测试等等。本文描述的是安装sakila数据库。该数据库需要安装在MySQL 5.0以上的版本。以下是其描述。
1、https://dev.mysql.com/doc/index-other.html下载资源
2、解压 :[root@localhost ~]# unzip sakila-db.zip
Archive: sakila-db.zip
creating: sakila-db/
inflating: sakila-db/sakila-schema.sql
inflating: sakila-db/sakila.mwb
inflating: sakila-db/sakila-data.sql
3、导入:
[root@mysql01 ~]# cd sakila-db
[root@mysql01 sakila-db]# ll
total 3396
-rw-r--r--. 1 root root 3398148 Jul 21 2016 sakila-data.sql
-rw-r--r--. 1 root root 50019 Jul 21 2016 sakila.mwb
-rw-r--r--. 1 root root 23424 May 19 15:20 sakila-schema.sql
[root@mysql01 sakila-db]# mysql -uroot -p < sakila-schema.sql
如果你的mysql版本过低,比如我的是5.1版本,则可能会报错:
ERROR 1214 (HY000) at line 175: The used table type doesn't support FULLTEXT indexes
原因在这个SQL文件中有提示:
-- Table structure for table `film_text`
-- InnoDB added FULLTEXT support in 5.6.10. If you use an
-- earlier version, then consider upgrading (recommended) or
-- changing InnoDB to MyISAM as the film_text engine
我的解决方法:
[root@mysql01 sakila-db]# vi sakila-schema.sql
找到CREATE TABLE film_text (
film_id SMALLINT NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY (film_id),
FULLTEXT KEY idx_title_description (title,description)
akila-schema.sqlENGINE=MyISAM DEFAULT CHARSET=utf8;
把InnoDB改成MyISAM 即可
[root@mysql01 sakila-db]# mysql -uroot -p < sakila-data.sql
导入完成!