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

Assassin's Creed Shadows: Seashell Riddle Solution

4 weeks ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

3 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

4 weeks ago By DDD

Roblox: Dead Rails - How To Complete Every Challenge

1 months ago By DDD

How to fix KB5055523 fails to install in Windows 11?

2 weeks 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

Where is the login entrance for gmail email?

7727

Java Tutorial

1643

CakePHP Tutorial

1397

Laravel Tutorial

1290

PHP Tutorial

1233

Related knowledge

MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

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.

How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles