mysql中geriop查找_mysql

一、MySQL的SQL语句

First column

Second column

SQL

Structure Query Language

DDL(Data Definition Language )

create , drop , alter

DML(Data Manipulation Language )

insert , update , delete

manipulation

[me nipu'leition] 操纵

update

修改

二、Creating and Using a Database

Suppose that you have serveral 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.

Creating a database

Create a table

Load data into table

Retrieve data from the table in various ways

use multiple tables

word

translation

menagerie

[me'naigeri]小动物园

UTF-8

8-bit Unicode Transformation Format,万国码

format

版式

transformation

变换

snoop

窥探，探听

三、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;

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

mysql>USE menagerie

Database changed

四、Creating a Table

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 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 contains 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 interesting,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 data of birth.Then,whenever you need age,you can calculate it as the difference between the current data and the birth data.MySQL provides functions for doing date arithmetic,so this is not difficult.Storing birth data rather than age has other advantages,too:

You can use the databases for tasks such as generating reminder 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 DATA,death DATA);

word

translation

descriptive

描写的，描述的，分类的

bare minimum

最低限度

generating

生成

somewhat

稍微，有点

sufficient

充足的，足够的

specify

指定，详述

specify the layout

指定布局

species

物种

四、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

NULL

Claws

Gwen

cat

m

1994-03-17

NULL

Buffy

Harold

dog

f

1989-05-13

NULL

Fang

Benny

dog

m

1990-08-27

NULL

Bowser

Diane

dog

m

1979-08-31

1995-07-29

Chirpy

Gwen

bird

f

1998-09-11

NULL

Whislter

Gwen

bird

NULL

1997-12-09

NULL

Slim

Benny

Snake

m

1996-04-29

NULL

Puffball

Diane

hamster

f

1999-03-30

五、Deleting Data from a Table

delete from pet where name='Whistler'

六、Loading Data into a Table

mysql>LOAD DATA LOCAL INFILE'/path/pet.txt'INTO TABLE pet

>LINES TERMINATED BY '\r\n';

七、pet.txt

BuffyHarolddog f1989-05-13NULL

FangBennydog m1990-08-27NULL

BowserDianedog m1979-08-31NULL

ChirpyGwenbird f1998-09-11NULL

WhistlerGwenbird NULL1997-12-09NULL

SlimBennySnakem1996-04-29NULL

PuffballDianehamsterf1999-03-30NULL

八、MySQL的SQL语句

First column

Second column

SQL

Structure Query Language

DDL(Data Definition Language )

create , drop , alter

DML(Data Manipulation Language )

insert , update , delete

manipulation

[me nipu'leition] 操纵

update

修改

九、Loading Data into a Table

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 (shch as unknown sexes or death dates for animals that are still living),you can use NULL valuesl.To represent these in your text file,use \N(blackflash,capital-N).For example,the record for Whistler the bird would look like this(where the whitespace between values is a single tab character):

WhistlerGwenbird\N1997-12-09\N

If you created the file on Windows with an editor that uses \r\n as a line terminator,you should use this statement instead:

mysql>LOAD DATA LOCAL INFILE'/path/pet.txt'INTO TABLE pet

>LINES TERMINATED BY '\r\n';

十、Retrieving Informatica from a Table

The SELECT statement is used to pull information from a table.The general form the statement is:

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

what_to_select indicates what you want to see.This can be a list of columns

indicate "all columns"

-which_table indicates the tables from which you want to retrieve data.The WHERE clause is optional.

conditions_to_satisfy specifies one or more conditions that rows must satisfy to quality for retrieval.

word

translation

retrieval

检索

specify

指定；详述

terminated

结束

十一、DELETING A TABLE

delete from table where 1=1;

after that,we can use the statement :

DESCRIBE pet;

十二、Loading data into a table

mysql>LOAD DATA LOCAL INFILE 'E:\pet.txt' INTO TABLE pet

>LINES TERMINATED BY '\r\n';