...
Mysql tutorial, mysql tutorial, mysql and java tutorial, using mysql, mysql lessons, mysql,mysql tips,mysql cheat sheet!
Sycorax - complete tutorials
Programming Tutorials
" Java provides the industry - software companies and customer alike , an opportunity to create a true open computing environment where software is portable,
and customers benefit from increase competition. "
Java and the Future
December 1, 2008-LEJB 3.1: EJB New and Improved!
The EJB 3.0 specification was a huge improvement from what you were used to in the early versions of EJB. Available as an early draft, EJB 3.1 has many more features and is even easier to use.
December 1, 2008-Should Java Assert that Network I/O Can't Occur on the UI Thread?
Doing network I/O on the user interface (UI) thread is bad. Most developers know that and can tell you why; unfortunately, it's still done.
Register now to recieve special alert and latest technology news!
Selecting All Data From A Table
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_select
FROM which_table
WHERE conditions_to_satisfy;
This can be a list of columns, or * to indicate "all columns."
Selecting All Data
The simplest form of SELECT retrieves everything from a table:
mysql> SELECT * FROM animal;
+----------+--------+---------+------+------------+------------+
| 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 |
+----------+--------+---------+------+------------+------------+
To empty the table
mysql> DELETE FROM animal;
Fix only the erroneous record with an UPDATE statement:
mysql> UPDATE animal SET birth = '1990-06-12' WHERE name = 'Bowser';
Selecting Particular Rows
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 animal WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
mysql> SELECT * FROM animal 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 animal 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 animal 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 |
+----------+-------+---------+------+------------+-------+
Selecting Particular Columns
If you do not want to see entire rows from your table, just name the columns in which you are interested, separated by commas.
mysql> SELECT name, birth FROM animal;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
+----------+------------+
To find out who owns pets, use this query:
mysql> SELECT owner FROM animal;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
Notice that the query simply retrieves the owner column 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 animal;
+--------+
| owner |
+--------+
| Benny |
| Diane |
| Gwen |
| Harold |
+--------+