|
06-09-2004, 04:48 PM
|
#1 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3 Creating and Using a Database
From http://dev.mysql.com/doc/mysql/en/Database_use.html
3.3 Creating and Using a Database Now that you know how to enter commands, it's time to access a database. Suppose that you have several pets in your home (your menagerie) and you'd 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:
Use the SHOW statement to find out what databases currently exist on the server: mysql> SHOW DATABASES;+----------+| Database |+----------+| mysql || test || tmp |+----------+The list of databases is probably different on your machine, but the mysql and test databases are likely to be among them. The mysql database is required because it describes user access privileges. The test database is often provided as a workspace for users to try things out. Note that you may not see all databases if you don't have the SHOW DATABASES privilege. See section 14.5.1.2 GRANT and REVOKE Syntax. If the test database exists, try to access it: mysql> USE testDatabase changedNote that 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 username assigned to you and your_client_host is the host from which you connect to the server. |
|
|
|
06-09-2004, 04:49 PM
|
#2 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.1 Creating and Selecting a Database
From http://dev.mysql.com/doc/mysql/en/Cr..._database.html
3.3.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.) 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 menagerieDatabase changedYour 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 menagerieEnter password: ********Note that menagerie is not your password on the command just shown. 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. |
|
|
|
|
06-09-2004, 04:49 PM
|
#3 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.2 Creating a Table
From http://dev.mysql.com/doc/mysql/en/Creating_tables.html
3.3.2 Creating a Table Creating the database is the easy part, but at this point it's empty, as SHOW TABLES will tell 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 will need and what columns will be in each of them. You'll 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:
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 will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, 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's 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. Now that 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. |
|
|
|
|
06-09-2004, 04:50 PM
|
#4 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.3 Loading Data into a Table
From http://dev.mysql.com/doc/mysql/en/Loading_tables.html
3.3.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 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): name owner species sex birth death 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 '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 'pet.txt' INTO TABLE pet -> LINES TERMINATED BY '\r\n';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.3.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. |
|
|
|
|
06-09-2004, 04:51 PM
|
#5 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4 Retrieving Information from a Table
From http://dev.mysql.com/doc/mysql/en/Retrieving_data.html
3.3.4 Retrieving Information from a Table The SELECT statement is used to pull information from a table. The general form of the statement is: SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;what_to_select indicates what you want to see. This can be a list of columns, or * to indicate ``all columns.'' which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it's present, conditions_to_satisfy specifies conditions that rows must satisfy to qualify for retrieval. |
|
|
|
|
06-09-2004, 04:51 PM
|
#6 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4.1 Selecting All Data
From http://dev.mysql.com/doc/mysql/en/Selecting_all.html
3.3.4.1 Selecting All Data The simplest form of SELECT retrieves everything from a table: mysql> SELECT * FROM pet;+----------+--------+---------+------+------------+------------+| 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 || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || Slim | Benny | snake | m | 1996-04-29 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+--------+---------+------+------------+------------+This form of SELECT is useful if you want to review your entire table, for example, after you've just loaded it with your initial dataset. For example, you may happen to think that the birth date for Bowser doesn't seem quite right. Consulting your original pedigree papers, you find that the correct birth year should be 1989, not 1979. There are least a couple of ways to fix this:
|
|
|
|
|
06-09-2004, 04:52 PM
|
#7 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4.2 Selecting Particular Rows
From http://dev.mysql.com/doc/mysql/en/Selecting_rows.html
3.3.4.2 Selecting Particular Rows As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the SELECT statement. But typically you don't want to see the entire table, particularly when it becomes large. Instead, you're usually more interested in answering a particular question, in which case you specify some constraints on the information you want. Let's look at some selection queries in terms of questions about your pets that they answer. You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this: mysql> SELECT * FROM pet WHERE name = 'Bowser';+--------+-------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+--------+-------+---------+------+------------+------------+| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |+--------+-------+---------+------+------------+------------+The output confirms that the year is correctly recorded now as 1989, not 1979. String comparisons normally are case-insensitive, so you can specify the name as 'bowser', 'BOWSER', etc. The query result will be the same. You can specify conditions on any column, not just name. For example, if you want to know which animals were born after 1998, test the birth column: mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| Chirpy | Gwen | bird | f | 1998-09-11 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+-------+---------+------+------------+-------+You can combine conditions, for example, to locate female dogs: mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';+-------+--------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+-------+--------+---------+------+------------+-------+| Buffy | Harold | dog | f | 1989-05-13 | NULL |+-------+--------+---------+------+------------+-------+The preceding query uses the AND logical operator. There is also an OR operator: mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| Chirpy | Gwen | bird | f | 1998-09-11 | NULL || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || Slim | Benny | snake | m | 1996-04-29 | NULL |+----------+-------+---------+------+------------+-------+AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it's a good idea to use parentheses to indicate explicitly how conditions should be grouped: mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f');+-------+--------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen | cat | m | 1994-03-17 | NULL || Buffy | Harold | dog | f | 1989-05-13 | NULL |+-------+--------+---------+------+------------+-------+ |
|
|
|
|
06-09-2004, 04:52 PM
|
#8 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4.3 Selecting Particular Columns
From http://dev.mysql.com/doc/mysql/en/Se...g_columns.html
3.3.4.3 Selecting Particular Columns If you don't want to see entire rows from your table, just name the columns in which you're interested, separated by commas. For example, if you want to know when your animals were born, select the name and birth columns: mysql> SELECT name, birth FROM pet;+----------+------------+| name | birth |+----------+------------+| Fluffy | 1993-02-04 || Claws | 1994-03-17 || Buffy | 1989-05-13 || Fang | 1990-08-27 || Bowser | 1989-08-31 || Chirpy | 1998-09-11 || Whistler | 1997-12-09 || Slim | 1996-04-29 || Puffball | 1999-03-30 |+----------+------------+To find out who owns pets, use this query: mysql> SELECT owner FROM pet;+--------+| owner |+--------+| Harold || Gwen || Harold || Benny || Diane || Gwen || Gwen || Benny || Diane |+--------+However, notice that the query simply retrieves the owner field from each record, and some of them appear more than once. To minimize the output, retrieve each unique output record just once by adding the keyword DISTINCT: mysql> SELECT DISTINCT owner FROM pet;+--------+| owner |+--------+| Benny || Diane || Gwen || Harold |+--------+You can use a WHERE clause to combine row selection with column selection. For example, to get birth dates for dogs and cats only, use this query: mysql> SELECT name, species, birth FROM pet -> WHERE species = 'dog' OR species = 'cat';+--------+---------+------------+| name | species | birth |+--------+---------+------------+| Fluffy | cat | 1993-02-04 || Claws | cat | 1994-03-17 || Buffy | dog | 1989-05-13 || Fang | dog | 1990-08-27 || Bowser | dog | 1989-08-31 |+--------+---------+------------+ |
|
|
|
|
06-09-2004, 04:53 PM
|
#9 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4.4 Sorting Rows
From http://dev.mysql.com/doc/mysql/en/Sorting_rows.html
3.3.4.4 Sorting Rows You may have noticed in the preceding examples that the result rows are displayed in no particular order. It's often easier to examine query output when the rows are sorted in some meaningful way. To sort a result, use an ORDER BY clause. Here are animal birthdays, sorted by date: mysql> SELECT name, birth FROM pet ORDER BY birth;+----------+------------+| name | birth |+----------+------------+| Buffy | 1989-05-13 || Bowser | 1989-08-31 || Fang | 1990-08-27 || Fluffy | 1993-02-04 || Claws | 1994-03-17 || Slim | 1996-04-29 || Whistler | 1997-12-09 || Chirpy | 1998-09-11 || Puffball | 1999-03-30 |+----------+------------+On character type columns, sorting--like all other comparison operations--is normally performed in a case-insensitive fashion. This means that the order will be undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using the BINARY cast: ORDER BY BINARY col_name. The default sort order is ascending, with smallest values first. To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by: mysql> SELECT name, birth FROM pet ORDER BY birth DESC;+----------+------------+| name | birth |+----------+------------+| Puffball | 1999-03-30 || Chirpy | 1998-09-11 || Whistler | 1997-12-09 || Slim | 1996-04-29 || Claws | 1994-03-17 || Fluffy | 1993-02-04 || Fang | 1990-08-27 || Bowser | 1989-08-31 || Buffy | 1989-05-13 |+----------+------------+You can sort on multiple columns, and you can sort columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query: mysql> SELECT name, species, birth FROM pet -> ORDER BY species, birth DESC;+----------+---------+------------+| name | species | birth |+----------+---------+------------+| Chirpy | bird | 1998-09-11 || Whistler | bird | 1997-12-09 || Claws | cat | 1994-03-17 || Fluffy | cat | 1993-02-04 || Fang | dog | 1990-08-27 || Bowser | dog | 1989-08-31 || Buffy | dog | 1989-05-13 || Puffball | hamster | 1999-03-30 || Slim | snake | 1996-04-29 |+----------+---------+------------+Note that the DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order. |
|
|
|
|
06-09-2004, 04:53 PM
|
#10 |
|
Administrator
Join Date: Jun 2004
Posts: 383
|
3.3.4.5 Date Calculations
From http://dev.mysql.com/doc/mysql/en/Da...culations.html
3.3.4.5 Date Calculations MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates. To determine how many years old each of your pets is, compute the difference in the year part of the current date and the birth date, then subtract one if the current date occurs earlier in the calendar year than the birth date. The following query shows, for each pet, the birth date, the current date, and the age in years. mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Fluffy | 1993-02-04 | 2003-08-19 | 10 || Claws | 1994-03-17 | 2003-08-19 | 9 || Buffy | 1989-05-13 | 2003-08-19 | 14 || Fang | 1990-08-27 | 2003-08-19 | 12 || Bowser | 1989-08-31 | 2003-08-19 | 13 || Chirpy | 1998-09-11 | 2003-08-19 | 4 || Whistler | 1997-12-09 | 2003-08-19 | 5 || Slim | 1996-04-29 | 2003-08-19 | 7 || Puffball | 1999-03-30 | 2003-08-19 | 4 |+----------+------------+------------+------+Here, YEAR() pulls out the year part of a date and RIGHT() pulls off the rightmost five characters that represent the MM-DD (calendar year) part of the date. The part of the expression that compares the MM-DD values evaluates to 1 or 0, which adjusts the year difference down a year if CURDATE() occurs earlier in the year than birth. The full expression is somewhat ungainly, so an alias (age) is used to make the output column label more meaningful. The query works, but the result could be scanned more easily if the rows were presented in some order. This can be done by adding an ORDER BY name clause to sort the output by name: mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY name;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Bowser | 1989-08-31 | 2003-08-19 | 13 || Buffy | 1989-05-13 | 2003-08-19 | 14 || Chirpy | 1998-09-11 | 2003-08-19 | 4 || Claws | 1994-03-17 | 2003-08-19 | 9 || Fang | 1990-08-27 | 2003-08-19 | 12 || Fluffy | 1993-02-04 | 2003-08-19 | 10 || Puffball | 1999-03-30 | 2003-08-19 | 4 || Slim | 1996-04-29 | 2003-08-19 | 7 || Whistler | 1997-12-09 | 2003-08-19 | 5 |+----------+------------+------------+------+To sort the output by age rather than name, just use a different ORDER BY clause: mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY age;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Chirpy | 1998-09-11 | 2003-08-19 | 4 || Puffball | 1999-03-30 | 2003-08-19 | 4 || Whistler | 1997-12-09 | 2003-08-19 | 5 || Slim | 1996-04-29 | 2003-08-19 | 7 || Claws | 1994-03-17 | 2003-08-19 | 9 || Fluffy | 1993-02-04 | 2003-08-19 | 10 || Fang | 1990-08-27 | 2003-08-19 | 12 || Bowser | 1989-08-31 | 2003-08-19 | 13 || Buffy | 1989-05-13 | 2003-08-19 | 14 |+----------+------------+------------+------+A similar query can be used to determine age at death for animals that have died. You determine which animals these are by checking whether the death value is NULL. Then, for those with non-NULL values, compute the difference between the death and birth values: mysql> SELECT name, birth, death, -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) -> AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age;+--------+------------+------------+------+| name | birth | death | age |+--------+------------+------------+------+| Bowser | 1989-08-31 | 1995-07-29 | 5 |+--------+------------+------------+------+The query uses death IS NOT NULL rather than death <> NULL because NULL is a special value that cannot be compared using the usual comparison operators. This is discussed later. See section 3.3.4.6 Working with NULL Values. What if you want to know which animals have birthdays next month? For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the birth column. MySQL provides several date-part extraction functions, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here. To see how it works, run a simple query that displays the value of both birth and MONTH(birth): mysql> SELECT name, birth, MONTH(birth) FROM pet;+----------+------------+--------------+| name | birth | MONTH(birth) |+----------+------------+--------------+| Fluffy | 1993-02-04 | 2 || Claws | 1994-03-17 | 3 || Buffy | 1989-05-13 | 5 || Fang | 1990-08-27 | 8 || Bowser | 1989-08-31 | 8 || Chirpy | 1998-09-11 | 9 || Whistler | 1997-12-09 | 12 || Slim | 1996-04-29 | 4 || Puffball | 1999-03-30 | 3 |+----------+------------+--------------+Finding animals with birthdays in the upcoming month is easy, too. Suppose that the current month is April. Then the month value is 4 and you look for animals born in May (month 5) like this: mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;+-------+------------+| name | birth |+-------+------------+| Buffy | 1989-05-13 |+-------+------------+There is a small complication if the current month is December. You don't just add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1). You can even write the query so that it works no matter what the current month is. That way you don't have to use a particular month number in the query. DATE_ADD() allows you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays: mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));A different way to accomplish the same task is to add 1 to get the next month after the current one (after using the modulo function (MOD) to wrap around the month value to 0 if it is currently 12): mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;Note that MONTH returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1). |
|
|
|
 |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
|
|
