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, our
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 Section 5.8, “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
Enter password:host
-uuser
-p menagerie********
Note that 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.
Creating the database is the easy part, but at this point it's
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's 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's 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'
.
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;
+---------+-------------+------+-----+---------+-------+
| 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 | |
+---------+-------------+------+-----+---------+-------+
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 Chapter 11, Data Types.
Posted by Larry Blanchette on September 1 2005 8:21pm | [Delete] [Edit] |
you can use: show create table tablename, to get the DDL;
Posted by James Carrig on January 10 2006 8:44pm | [Delete] [Edit] |
While it is true that VARCHAR(20) means that the lengths in the columns "need not be 20," the more direct meaning is that the maximum column length is 20. Here is actual output which may speak more clearly than words:
mysql> CREATE TABLE demo_varchar ( words VARCHAR(5));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO demo_varchar VALUES ('abcdef');
ERROR 1406 (22001): Data too long for column 'words' at row 1
mysql> INSERT INTO demo_varchar VALUES ('abcde');
Query OK, 1 row affected (0.38 sec)
Posted by Raymond Peck on April 6 2006 10:03pm | [Delete] [Edit] |
Another issue is that I assume for all or most underlying table implementations a packed string table is used, so only as much space is used as required. Might want to add a link to more detailed info on the implications of various max lengths, and any variations between MyISAM, InnoDB, etc.
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
represent these in your text file, use \N
(backslash, 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
To load the text file pet.txt
into the
pet
table, use this command:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
Note that if you created the file on Windows with an editor that
uses \r\n
as a line terminator, you should
use:
mysql>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
->LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'
.)
You can specify the column value separator and end of line
marker explicitly in the LOAD DATA
statement
if you wish, but the defaults are tab and linefeed. These are
sufficient for the statement to read the file
pet.txt
properly.
If the statement fails, it is likely that your MySQL
installation does not have local file capability enabled by
default. See Section 5.7.4, “Security Issues with LOAD DATA LOCAL
”, for information
on how to change this.
When you want to add new records one at a time, the
INSERT
statement is useful. In its simplest
form, you supply values for each column, in the order in which
the columns were listed in the CREATE TABLE
statement. Suppose that Diane gets a new hamster named
“Puffball.” You could add a new record using an
INSERT
statement like this:
mysql>INSERT INTO pet
->VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Note that string and date values are specified as quoted strings
here. Also, with INSERT
, you can insert
NULL
directly to represent a missing value.
You do not use \N
like you do with
LOAD DATA
.
注意 这里的字串和日期的值 规定要用引号 ' 括住
From this example, you should be able to see that there would be
a lot more typing involved to load your records initially using
several INSERT
statements rather than a
single LOAD DATA
statement.
Posted by Doug Hall on February 17 2003 2:11pm | [Delete] [Edit] |
With Apple OS X: Use the terminal's drag and drop capability to insert the full path of the import file. This cuts down on the amount of typing, if you don't want deal with adding the import file into MySQL's data folder.
example:
%mysql --local-infile -u <username> -p <DatabaseName>
Enter password:<password>
mysql>load data local infile '<drag input file here>' into table <TableName>;
Posted by Brandon Stout on September 22 2004 10:56pm | [Delete] [Edit] |
you can also drag windows files to the command window, but you'll need to change the backslashes to double-backslashes or forwardslashes, and remove the c: at the beginning. If you have quotes around the path, you'll need to delete them as well.
Posted by tsaiching wong on December 3 2004 6:18am | [Delete] [Edit] |
mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';
--> just in case anyone experienced some discomfort following above instructions.
Posted by phil newcombe on December 23 2004 6:40pm | [Delete] [Edit] |
I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)
Posted by Mark Buchanan on January 6 2005 1:52am | [Delete] [Edit] |
When dragging file in Windows I found the quotes needed to be kept in.
Posted by Mike Hearn on January 17 2005 6:31pm | [Delete] [Edit] |
Doug Halls trick also works on Linux/BSD using the GNOME or KDE terminal emulator programs.
Posted by Dennis Verbunt on February 27 2005 2:06pm | [Delete] [Edit] |
I was having some problems getting this working in XP but got it working after checking my syntax multiple times and then ENABLING local infiles.
Also after draging the file into the command window I had to replace the windows style backslashes with linux style forward slashes.
mysql> LOAD DATA LOCAL INFILE "C:\Documents and Settings\Dennis\Desktop\menagerie\pet.txt" INTO TABL
E PET;
ERROR 2 (HY000): File 'C:Documents and SettingsDennisDesktopmenageriepet.txt' not found (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE "C:/Documents and Settings/Dennis/Desktop/menagerie/pet.txt" INTO TABL
E PET;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
Posted by MidnighToker on May 7 2005 9:33pm | [Delete] [Edit] |
I'm thrilled and amazed about being able to drop files into a console to get the path, but
for people not using the mouse, you can put a path from your home directory using '~/' in the path.
Eg if your file is 'pets.txt' in your home directory
LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;
NOTE: This only works on *nix opperating systems and OSX (as far as i know).
NOTE: A blank line at the end of your infile gives wierd results:
| Slim | Benny | snake | m | 1996-04-29 | NULL |Worth keeping an eye on.
| | NULL | NULL | NULL | NULL | NULL |
| Puffball | Daine | hampster | f | 1999-03-30 | NULL |
Posted by Ron Nelson on June 13 2005 6:28pm | [Delete] [Edit] |
One thing it took me a moment to understand is that 's didn't seem to work. "s did.
Posted by Mohamed Abdulla on August 17 2005 1:14pm | [Delete] [Edit] |
I noticed that in case you want to use LOAD DATA LOCAL INFILE or the INSERT commands to fill your table fields with data, special care should be taken for INTEGER AUTO_INCREMENT fields. In the case of "LOAD DATA LOCAL INFILE" command I pressed TAB where was supposed to enter AUTO_INCREMENT INT value, and it was accepted. In the case of "INSERT INTO table VALUES (.." command I just entered the name of the column <without quotes> where was supposed to supply the value of that field, and it worked! Actually in all cases it worked also when entering any string value like: 'i' or 's'. It will alwys be converted to the required INT value. I thought this may help!
Posted by zan sh on September 28 2005 6:54pm | [Delete] [Edit] |
Used \n for NULL but not working..It takes 0 value for tht(date value).Even tried to write NULL but takes 0.
Insert is accepting value NULL but not load command
Posted by Aaron Peterson on November 9 2005 5:35pm | [Delete] [Edit] |
With a defalut installation from FreeBSD ports, I had to use the command line
mysql -u user -p --local-infile menagerie
to start the mysql monitor, else the LOAD DATA LOCAL command failed with an error like the following:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
Also, the pet.txt downloaded from the mysql.com link (tgz archive) appears to have ended the lines with '\n' instead of '\r\n'.
Posted by hackajar on November 15 2005 11:41pm | [Delete] [Edit] |
When using "LOAD DATA INFILE" command, it's kinda tricky getting the data loaded when you don't want to provide a value for the "AUTO INCREMENT" field.
I wrote a quick script to just add in " \t" to beginning of each line in file. since the field was set "NOT NULL" and "AUTO INCREMENT" mysql spotted this and added a number for me.
You could probably do this just as well in Excel by adding a new colum before all others, and leaving it's data blank.
Posted by Gloria Rohmann on March 13 2006 12:44am | [Delete] [Edit] |
I tried preparing the text for load data local infile (pet.txt) on my mac (osX). Wasted a lot of time because there was no clear example of the text file format. The example should state specifically for us newbies:
Buffy,Gwen,cat,f,1998-03-02,/N,
Snuffy,Tom,dog,m,1999-0209,/N,
Additionally, the command should read:
mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet fields terminated by ',';
Posted by Andrew Cartine on March 16 2006 4:57pm | [Delete] [Edit] |
Using 5.0.19 on linux kernel 2.6.9-11, i found that i had to specify FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
without that, i couldn't import any records from my file. For some reason I was under the impression that commas and linefeeds were the default, but without specifying them I had no success.
User Comments
You can use this command to view the current database that you're connected to:
mysql> select database();
Add your own comment.