一、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
Buffy Harold dog f 1989-05-13 NULL
Fang Benny dog m 1990-08-27 NULL
Bowser Diane dog m 1979-08-31 NULL
Chirpy Gwen bird f 1998-09-11 NULL
WhistlerGwen bird NULL 1997-12-09 NULL
Slim Benny Snake m 1996-04-29 NULL
PuffballDiane hamster f 1999-03-30 NULL
八、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):
Whistler Gwen bird \N 1997-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';