您现在的位置是:首页 >技术交流 >MySQL示例数据库(MySQL Sample Databases) 之 World数据库网站首页技术交流
MySQL示例数据库(MySQL Sample Databases) 之 World数据库
简介MySQL示例数据库(MySQL Sample Databases) 之 World数据库
文章目录
MySQL示例数据库(MySQL Sample Databases) 之 World数据库
官方示例数据介绍
MySQL 官方提供了多个示例数据库,在MySQL的学习、开发和实践中具有非常重要的作用,能够帮助初学者更好地理解和应用MySQL的各种功能和特性。
官方示例数据具体列表如下:
1. employee data (large dataset, includes data and test/verification suite)
2. world database
3. world_x database
4. sakila database
5. airportdb database (large dataset, intended for MySQL on OCI and HeatWave)
6. menagerie database
这些数据库都可以通过如下官方网址进行下载和安装
https://dev.mysql.com/doc/index-other.html
World数据库
MySQL官方网站提供的示例数据库 world Database,包含了一个关于国家、城市和语言的实际数据集。该数据库包含了以下三个表:
-
country:
该表包含了全球所有国家的信息,包括国家名称、首都、人口、面积、所属洲别等信息。 -
city:
该表包含了世界各国的城市信息,包括所属国家、城市名称、人口等信息。 -
countrylanguage:
该表包含了全球各国语言的信息,包括所属国家、语言名称、官方语言等信息。
通过这三个表之间的连结和查询,可以实现各种复杂的跨表查询功能,例如根据某个国家的首都查询该国家的语言、或者查询某个语言在全球范围内被哪些国家使用等等。
world Database提供了一个非常实用的数据集,可以帮助开发人员更好地理解MySQL的查询语句、表关联和查询优化等概念。同时,通过该数据集,用户还可以编写实际的SQL查询语句以及应用程序,提高自己的MySQL编程技能。
World 数据库安装
下载压缩包后就可以解压安装了。
解压:
ubuntu@mysql-vm:~$ unzip world-db.zip
Archive: world-db.zip
creating: world-db/
inflating: world-db/world.sql
安装:
ubuntu@mysql-vm:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16
Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> source world-db/world.sql
查看数据
mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)
ubuntu@mysql-vm:~$ mysqlshow -uroot -prootroot -vv world
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: world
+-----------------+----------+------------+
| Tables | Columns | Total Rows |
+-----------------+----------+------------+
| city | 5 | 4079 |
| country | 15 | 239 |
| countrylanguage | 4 | 984 |
+-----------------+----------+------------+
3 rows in set.
world-db/world.sql的脚本内容
-- MySQL dump 10.13 Distrib 8.0.19, for osx10.14 (x86_64)
--
-- Host: 127.0.0.1 Database: world
-- ------------------------------------------------------
-- Server version 8.0.19-debug
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @old_autocommit=@@autocommit;
--
-- Current Database: `world`
--
/*!40000 DROP DATABASE IF EXISTS `world`*/;
CREATE DATABASE `world` DEFAULT CHARACTER SET utf8mb4;
USE `world`;
--
-- Table structure for table `city`
--
DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `city`
--
-- ORDER BY: `ID`
set autocommit=0;
INSERT INTO `city` VALUES (1,'Kabul','AFG','Kabol',1780000);
...
commit;
--
-- Table structure for table `country`
--
DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint DEFAULT NULL,
`Population` int NOT NULL DEFAULT '0',
`LifeExpectancy` decimal(3,1) DEFAULT NULL,
`GNP` decimal(10,2) DEFAULT NULL,
`GNPOld` decimal(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `country`
--
-- ORDER BY: `Code`
set autocommit=0;
INSERT INTO `country` VALUES ('ABW','Aruba','North America','Caribbean',193.00,NULL,103000,78.4,828.00,793.00,'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW');
...
commit;
--
-- Table structure for table `countrylanguage`
--
DROP TABLE IF EXISTS `countrylanguage`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `countrylanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` decimal(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `countrylanguage`
--
-- ORDER BY: `CountryCode`,`Language`
set autocommit=0;
INSERT INTO `countrylanguage` VALUES ('ABW','Dutch','T',5.3);
...
commit;
--
-- Dumping events for database 'world'
--
--
-- Dumping routines for database 'world'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
SET autocommit=@old_autocommit;
-- Dump completed on 2020-01-22 9:56:18
参考
https://dev.mysql.com/doc/world-setup/en/
风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。