|
Multiple TablesThe
Given these considerations, the mysql> CREATE TABLE event (name VARCHAR(20), date DATE, -> type VARCHAR(15), remark VARCHAR(255)); As with the
Load the records like this: mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event; Based on what you've learned from the queries you've run on the Suppose you want to find out the ages of each pet when they had their litters.
The mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ There are several things to note about this query:
You need not have two different tables to perform a join. Sometimes it is useful
to join a table to itself, if you want to compare records in a table to other
records in that same table. For example, to find breeding pairs among your pets,
you can join the mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ In this query, we specify aliases for the table name in order to be able to refer to the columns and keep straight which instance of the table each column reference is associated with. Getting information about databases and tablesWhat if you forget the name of a database or table, or what the structure of a given table is (e.g., what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports. You have already seen mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+ If you haven't selected any database yet, the result is blank. To find out what tables the current database contains (for example, when you're not sure about the name of a table), use this command: mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | pet | +---------------------+ If you want to find out about the structure of a table, the 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 | | +---------+-------------+------+-----+---------+-------+
If you have indexes on a table, Extracted from the MySQL Documentation JamHitz Productions |