


MySQL table design practice: Create a city information table and attractions table
In actual applications, we often need to store city-related information, such as city name, geographical location, population, etc. In tourism-related applications, it is also necessary to store information about attractions, including name, city, description, etc. In order to facilitate the management and query of this information, we can use the MySQL database to design and store these tables.
First, we need to create a table named city
to store city information. The table can contain the following fields:
-
id
: the unique identifier of the city, using integer data type; -
name
: city The name, uses the string type, the length can be set according to the actual situation; -
population
: The population of the city, uses the integer data type; -
latitude
: The latitude of the city, using the floating-point data type; -
longitude
: The longitude of the city, using the floating-point data type.
The following is an example of the SQL statement to create the city
table:
CREATE TABLE city ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), population INT, latitude FLOAT, longitude FLOAT );
Next, we need to create a table named attraction
, used to store information about attractions. The table can contain the following fields:
-
id
: the unique identifier of the attraction, using an integer data type; -
name
: the attraction The name of the attraction uses a string type, and the length can be set according to the actual situation; -
city_id
: The city ID to which the attraction belongs, using an integer data type, this field is the same ascity The
idfields of the
table are related; -
description
: Description of the attraction, using string type, the length can be set according to the actual situation.
The following is an example of the SQL statement to create the attraction
table:
CREATE TABLE attraction ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), city_id INT, description TEXT, FOREIGN KEY (city_id) REFERENCES city(id) );
Through the above SQL statement to create the table, we successfully created city
table and attraction
table. Next, we can insert some sample data into these two tables for subsequent operations and queries.
-- 向city表中插入示例数据 INSERT INTO city (name, population, latitude, longitude) VALUES ('北京', 2171, 39.9042, 116.4074), ('上海', 2415, 31.2304, 121.4737), ('广州', 1500, 23.1291, 113.2644); -- 向attraction表中插入示例数据 INSERT INTO attraction (name, city_id, description) VALUES ('故宫', 1, '位于北京市中心,是中国明清两代的皇宫'), ('外滩', 2, '上海著名的沿江滨路,风景秀丽'), ('珠江夜游', 3, '广州著名的夜间旅游项目,可欣赏到珠江两岸的美景');
Through the insertion of the above example data, we created information about three cities (Beijing, Shanghai, Guangzhou), and attraction information associated with these cities (Forbidden City, The Bund, Pearl River Night Tour).
In practical applications, we can easily store, query and update city information and scenic spot information based on the design of these two tables. Through reasonable table design, we can better manage and display the data information involved in the application.
In summary, MySQL table design practice needs to consider the fields of the table and the relationships with other tables. When creating the city information table and attractions table, we need to consider related fields and use foreign key associations to establish the relationship between the two tables. Through reasonable table design, we can better store and manage data, thereby improving application efficiency and performance. In practical applications, we can adjust and optimize the fields of the table according to specific needs.
The above is the detailed content of MySQL table design practice: Create a city information table and attractions table. For more information, please follow other related articles on the PHP Chinese website!

MySQL表设计指南:如何创建订单表和商品表简介在数据库设计中,正确地创建表格是非常重要的。本文将重点介绍如何创建订单表和商品表,以提供一个指南供读者参考。同时,为了更好地理解,本文还会提供相关的代码示例。订单表设计订单表是用来存储订单信息的表。下面是一个简单的订单表设计示例:CREATETABLEorders(order_idINTPRIMARY

MySQL表设计指南:创建一个简单的员工考勤表在企业管理中,员工的考勤管理是至关重要的一项任务。为了准确记录和统计员工的考勤情况,我们可以利用MySQL数据库来创建一个简单的员工考勤表。本篇文章将指导您如何设计和创建这个表,并提供相应的代码示例。首先,我们需要确定员工考勤表所需的字段。一般来说,员工考勤表至少需要包含以下字段:员工ID、日期、上班时间、下班时

如何设计一个可维护的MySQL表结构来实现在线预订酒店功能?在实现一个在线预订酒店的功能中,合理设计数据库表结构是非常重要的。一个良好的表结构可以提高系统的性能和可维护性。本文将介绍如何设计一个可维护的MySQL表结构来实现在线预订酒店功能,并提供具体的代码示例。酒店表(hotel)酒店表用于存储酒店的基本信息,例如酒店ID、酒店名称、地址、电话等。此外,可

MySQL表设计教程:创建一个简单的留言板表介绍在网站开发中,留言板是一个非常常见的功能,用于让用户在网站上发表评论、建立联系等。在设计留言板功能时,一个重要的步骤是创建适当的数据表来存储留言的信息。本文将教你如何使用MySQL来创建一个简单的留言板表。步骤一:创建数据库首先,我们需要创建一个数据库来存储留言板的数据。可以使用以下代码创建数据库:CREATE

MySQL表设计指南:创建一个简单的商品分类表在数据库设计中,良好的表设计是非常重要的,它直接影响到数据的存储和查询效率。本文将介绍如何创建一个简单的商品分类表,并提供相应的代码示例。一、表结构设计商品分类表主要包括以下字段:分类ID、分类名称、父分类ID。其中,分类ID是表的主键,分类名称存储分类的名称,父分类ID用于表示当前分类的父级分类。下面是商品分类

如何设计一个灵活的MySQL表结构来实现订单管理功能?订单管理是许多企业和电商网站的核心功能之一。为了实现这个功能,一个重要的步骤是设计一个灵活的MySQL表结构来存储订单相关的数据。一个好的表结构设计能够提高系统的性能和可维护性。本文将介绍如何设计一个灵活的MySQL表结构,并提供具体的代码示例来辅助理解。订单表(Order)订单表是存储订单信息的主要表。

MySQL表设计教程:创建一个简单的新闻表在开发网站或应用程序时,新闻表是一个常见的数据库表之一。它用于存储和管理新闻文章的相关信息,如标题、内容、作者、发布日期等。本文将介绍如何使用MySQL创建一个简单的新闻表,并给出相应的代码示例。首先,我们需要创建一个数据库来存储新闻表。可以使用以下代码来创建一个名为"news_db"的数据库:CREATEDATA

如何设计MySQL表结构来管理仓库库存随着物流行业的发展,仓库库存管理变得越来越重要。在仓库中,准确记录和管理库存可以帮助企业提高运营效率和客户满意度。MySQL作为一种广泛应用的关系型数据库管理系统,可以帮助我们有效地管理仓库库存。本文将探讨如何设计MySQL表结构来管理仓库库存,并提供具体的代码示例。仓库表(Warehouse)仓库表用于存储仓库的基本信


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 English version
Recommended: Win version, supports code prompts!

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)
