提升技能：MySQL 5.1英文教程，英语与数据库并进

mysql

需积分: 10 196 浏览量更新于2024-07-26 收藏 380KB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

本资源是一份详尽的MySQL 5.1英文教程，旨在帮助读者通过实践提升英语能力的同时，掌握数据库管理的基础知识。教程由官方MySQL参考手册编写，更新日期为2013年3月29日，具有较高的权威性。教程结构清晰，共分为八个部分： 1. Tutorial.1：作为开篇，可能介绍MySQL的基本概念和教程的目的，让读者对即将学习的内容有个整体了解。 2. Connecting to and Disconnecting from the Server：这部分详细讲解如何连接和断开MySQL服务器，包括基本的命令行接口（CLI）连接步骤，这对于理解和操作数据库至关重要。 3. Entering Queries：介绍了如何在MySQL中输入和执行SQL查询语句，包括创建、修改和删除数据库和表的操作。 4. Creating and Using a Database：这部分深入讨论数据库的创建、选择以及表的建立，包括加载数据、查询数据的不同方式，如选择所有数据、特定行或列，排序数据，日期计算，处理NULL值，以及模式匹配等。 5. Getting Information About Databases and Tables：讲解如何获取关于数据库和表的信息，这对于理解数据结构和进行查询分析非常重要。 6. Using mysql in Batch Mode：介绍批处理模式，允许用户一次性执行多条命令，提高了工作效率。 7. Examples of Common Queries：提供了一系列实际操作中的常见查询示例，如查找最大值、特定列的最大值按组分组、使用用户自定义变量，以及涉及到外键约束的复杂查询等，有助于读者理解和应用这些高级查询技巧。 8. Additional Topics：这部分可能包括更深入的专题，如存储过程、视图、事务处理等，确保读者能够全面了解MySQL的各个方面。通过这个教程，无论是初学者还是有一定经验的开发者，都能在提升英语阅读能力的同时，巩固和扩展MySQL数据库的知识体系。不断练习和应用这些内容，将有助于在实际项目中更有效地管理和操作数据库。

资源详情

资源推荐

Chapter 4. Creating and Using a Database

Table of Contents

4.1. Creating and Selecting a Database ............................................................................................ 10

4.2. Creating a Table ........................................................................................................................ 11

4.3. Loading Data into a Table .......................................................................................................... 12

4.4. Retrieving Information from a Table ............................................................................................ 13

4.4.1. Selecting All Data ........................................................................................................... 13

4.4.2. Selecting Particular Rows ................................................................................................ 14

4.4.3. Selecting Particular Columns ........................................................................................... 15

4.4.4. Sorting Rows .................................................................................................................. 16

4.4.5. Date Calculations ............................................................................................................ 17

4.4.6. Working with NULL Values .............................................................................................. 20

4.4.7. Pattern Matching ............................................................................................................. 20

4.4.8. Counting Rows ............................................................................................................... 23

4.4.9. Using More Than one Table ............................................................................................ 25

Once you know how to enter commands, you are ready to access a database.

Suppose that you have several pets in your home (your menagerie) and you would like to keep track of

various types of information about them. You can do so by creating tables to hold your data and loading

them with the desired information. Then you can answer different sorts of questions about your animals by

retrieving data from the tables. This section shows you how to perform the following operations:

• Create a database

• Create a table

• Load data into the table

• Retrieve data from the table in various ways

• Use multiple tables

The menagerie database is simple (deliberately), but it is not difficult to think of real-world situations

in which a similar type of database might be used. For example, a database like this could be used by

a farmer to keep track of livestock, or by a veterinarian to keep track of patient records. A menagerie

distribution containing some of the queries and sample data used in the following sections can be

obtained from the MySQL Web site. It is available in both compressed tar file and Zip formats at http://

dev.mysql.com/doc/.

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql |

| test |

| tmp |

+----------+

The mysql database describes user access privileges. The test database often is available as a

workspace for users to try things out.

Creating and Selecting a Database

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES

does not show databases that you have no privileges for if you do not have the SHOW DATABASES

privilege. See SHOW DATABASES Syntax.

If the test database exists, try to access it:

mysql> USE test

Database changed

USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you

like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.

You can use the test database (if you have access to it) for the examples that follow, but anything you

create in that database can be removed by anyone else with access to it. For this reason, you should

probably ask your MySQL administrator for permission to use a database of your own. Suppose that you

want to call yours menagerie. The administrator needs to execute a command like this:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host

from which you connect to the server.

4.1. Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using

it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to

your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true

for table names. (Under Windows, this restriction does not apply, although you must refer to databases

and tables using the same lettercase throughout a given query. However, for a variety of reasons, the

recommended best practice is always to use the same lettercase that was used when the database was

created.)

Note

If you get an error such as ERROR 1044 (42000): Access denied for user

'monty'@'localhost' to database 'menagerie' when attempting to

create a database, this means that your user account does not have the necessary

privileges to do so. Discuss this with the administrator or see The MySQL Access

Privilege System.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current

database, use this command:

mysql> USE menagerie

Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql

session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select

the database on the command line when you invoke mysql. Just specify its name after any connection

parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerie

Enter password: ********

Creating a Table

Important

menagerie in the command just shown is not your password. If you want to supply

your password on the command line after the -p option, you must do so with no

intervening space (for example, as -pmypassword, not as -p mypassword).

However, putting your password on the command line is not recommended,

because doing so exposes it to snooping by other users logged in on your machine.

Note

You can see at any time which database is currently selected using SELECT

DATABASE().

4.2. Creating a Table

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

mysql> SHOW TABLES;

Empty set (0.00 sec)

The harder part is deciding what the structure of your database should be: what tables you need and what

columns should be in each of them.

You want a table that contains a record for each of your pets. This can be called the pet table, and

it should contain, as a bare minimum, each animal's name. Because the name by itself is not very

interesting, the table should contain other information. For example, if more than one person in your

family keeps pets, you might want to list each animal's owner. You might also want to record some basic

descriptive information such as species and sex.

How about age? That might be of interest, but it is not a good thing to store in a database. Age changes

as time passes, which means you'd have to update your records often. Instead, it is better to store a fixed

value such as date of birth. Then, whenever you need age, you can calculate it as the difference between

the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not

difficult. Storing birth date rather than age has other advantages, too:

• You can use the database for tasks such as generating reminders for upcoming pet birthdays. (If you

think this type of query is somewhat silly, note that it is the same question you might ask in the context

of a business database to identify clients to whom you need to send out birthday greetings in the current

week or month, for that computer-assisted personal touch.)

• You can calculate age in relation to dates other than the current date. For example, if you store death

date in the database, you can easily calculate how old a pet was when it died.

You can probably think of other types of information that would be useful in the pet table, but the ones

identified so far are sufficient: name, owner, species, sex, birth, and death.

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

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, and species columns because the column values vary

in length. The lengths in those column definitions need not all be the same, and need not be 20. You can

normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a poor

choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.

Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or

perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.

Loading Data into a Table

The use of the DATE data type for the birth and death columns is a fairly obvious choice.

Once you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;

+---------------------+

| Tables in menagerie |

+---------------------+

| pet |

+---------------------+

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

mysql> DESCRIBE pet;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| sex | char(1) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what

types they have.

For more information about MySQL data types, see Data Types.

4.3. Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for

this.

Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in

'YYYY-MM-DD' format; this may be different from what you are used to.)

name owner species sex birth death

Fluffy Harold cat f 1993-02-04

Claws Gwen cat m 1994-03-17

Buffy Harold dog f 1989-05-13

Fang Benny dog m 1990-08-27

Bowser Diane dog m 1979-08-31 1995-07-29

Chirpy Gwen bird f 1998-09-11

Whistler Gwen bird 1997-12-09

Slim Benny snake m 1996-04-29

Because you are beginning with an empty table, an easy way to populate it is to create a text file

containing a row for each of your animals, then load the contents of the file into the table with a single

statement.

You could create a text file pet.txt containing one record per line, with values separated by tabs, and

given in the order in which the columns were listed in the CREATE TABLE statement. For missing values

(such as unknown sexes or death dates for animals that are still living), you can use NULL values. To

剩余89页未读，继续阅读

dinggang007

粉丝: 759
资源: 7

提升技能：MySQL 5.1英文教程，英语与数据库并进

MySQL5.7-用户手册（完整版）英文

mysql_tutorial （mysql简明教程，英文原版的哦）

mysql安装配置教程

mysql安装配置教程2023

mysql免费安装配置教程

mysql-8.0.21安装配置教程windows

php mysql 英文教材

mysql二级考试教程_全国计算机等级考试二级教程MySQL练习题及答案

mysqlworkbench 设置成中文

学生成绩管理系统mysql

geoserver中文教程

ubnutu安装mysql

ubuntu20.04服务器版安装教程

英语完整词汇在哪里下载sql版本

dbeaver下载和安装教程

cms python

discuz config_global.php,Discuz! X config_global.php配置注释教程

单词天天斗小程序项目源码 (毕业设计_实战小程序学习_微信小程序完整项目).zip

navicat官网汉化包

学生成绩管理系统前后端代码

最新资源