MySQL Getting Started Tutorial 4 - Create a table and load data into the table-Mysql Tutorial-php.cn

Table of Contents

After creating the table, you need to fill in the content. This task can be accomplished through the LOAD DATA and INSERT statements.

Home

Database

Mysql Tutorial

MySQL Getting Started Tutorial 4 - Create a table and load data into the table

黄舟

Feb 23, 2017 am 11:29 AM

mysql

1. Create the table

Creating the database is the easy part, but at this point it is empty, as SHOW What TABLES will tell you:

mysql> SHOW TABLES; Empty set (0.00 sec)

Copy after login

The harder part is deciding what your database structure should be: what database tables you need, and what columns are in each database table.

You will need a table containing a record for each of your pets. It may be called a pet table, and it should contain, at a minimum, the name of each animal. Since the name itself is not very interesting, the table should contain additional information. For example, if there is more than one person in your household with pets, you may want to list the owner of each animal. You may also want to record some basic descriptive information such as species and gender.

What’s your age? That might be fun, but storing into a database is not a good thing. Age changes over time, which means you'll want to keep updating your records. Instead, it's better to store a fixed value such as the birthday, so that whenever you need the age, you can calculate it as the difference between the current date and the date of birth. MySQL provides date operation functions, so this is not difficult. Storing date of birth instead of age has other advantages:

· You can use the database for tasks such as generating reminders of upcoming pet birthdays. (If you think this kind of query is a bit silly, note that it's the same problem as identifying customers from a business database to whom birthday wishes will be sent soon, because computers facilitate personal contact.) Calculates age based on a date, not just the current date. For example, if you store death dates in a database, you can easily calculate how old a pet was when it died.

You might think of other useful types of information in the pet table, but these are enough for now: name, owner, species, gender, birth and death dates.

Use a CREATE TABLE statement to specify the layout of your database table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),     -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Copy after login

VARCHAR is suitable for name, owner, and species columns because the column values are variable-length. The columns don't all have to be the same length, and they don't have to be 20. You can pick any length from 1 to 65535 and choose a value that makes the most sense. (If the selection is inappropriate and it turns out that you need a longer field,

MySQL

provides an ALTER TABLE statement.) Multiple types of values can be used to represent the values in the animal record. Gender, for example, "m" and "f", or "male" and "female". Using the single characters "m" and "f" is the easiest way.

Obviously, the birth and death columns should use the DATE data class.

After creating the database table, SHOW TABLES should produce some output:

mysql> SHOW TABLES; +---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

Copy after login

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;

Copy after login

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Copy after login

You can use DESCRIBE at any time, for example, if you forget the name or type of a column in the table.

2. Load data into the table

After creating the table, you need to fill in the content. This task can be accomplished through the LOAD DATA and INSERT statements.

Suppose your pet record is described as follows. (Assume that the expected date format in

MySQL

is YYYY-MM-DD; this may be different than what you are used to.)

name ownerspeciesFluffyHaroldcatf1993-02-04GwenHarolddogfdogm1990-08-27##BowserDianem## ChirpyGwenbirdf##1998-09-11WhistlerGwen#bird##1997-12-09Slim1996-04-29

##sex

birth

death

##Claws

cat

1994-03-17

##Buffy

##1989-05-13

##Fang

Benny

dog

1979-08-31

1995-07-29

Benny

snake

因为你是从一个空表开始的，填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。

你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件中表示这些内容，使用\N（反斜线，字母N）。例如，Whistler鸟的记录应为(这里值之间的空白是一个定位符)：

name	owner	species	sex	birth	death
Whistler	Gwen	bird	\N	1997-12-09	\N

要想将文本文件“pet.txt”装载到pet表中，使用这个命令：

mysql> LOAD DATA LOCAL INFILE &#39;/path/pet.txt&#39; INTO TABLE pet;

Copy after login

请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：

mysql> LOAD DATA LOCAL INFILE &#39;/path/pet.txt&#39; INTO TABLE pet     -> LINES TERMINATED BY &#39;\r\n&#39;;

Copy after login

（在运行OS X的Apple机上，应使用行结束符'\r'。）

如果你愿意，你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。这对读入文件“pet.txt”的语句已经足够。

如果该语句失败，可能是你安装的MySQL不与使用默认值的本地文件兼容。

如果想要一次增加一个新记录，可以使用INSERT语句。最简单的形式是，提供每一列的值，其顺序与CREATE TABLE语句中列的顺序相同。假定Diane把一只新仓鼠命名为Puffball，你可以使用下面的INSERT语句添加一条新记录：

mysql> INSERT INTO pet     -> VALUES (&#39;Puffball&#39;,&#39;Diane&#39;,&#39;hamster&#39;,&#39;f&#39;,&#39;1999-03-30&#39;,NULL);

Copy after login

注意，这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用INSERT语句插入NULL代表不存在的值。不能使用LOAD DATA中所示的的\N。

从这个例子，你应该能看到涉及很多的键入用多个INSERT语句而非单个LOAD DATA语句装载你的初始记录。

以上就是MySQL入门教程4 —— 创建表并将数据装入表的内容，更多相关内容请关注PHP中文网（www.php.cn）！

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

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

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Nordhold: Fusion System, Explained

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Blue Prince: How To Get To The Basement

1 months ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1664

CakePHP Tutorial

1423

Laravel Tutorial

1318

PHP Tutorial

1269

C# Tutorial

1248

Related knowledge

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL for Beginners: Getting Started with Database Management Apr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

Solve MySQL mode problem: The experience of using the TheliaMySQLModesChecker module Apr 18, 2025 am 08:42 AM

When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.

See all articles