MySQL Learning note 003

Retrieving information from a table

In front of me, I created a database test into the database created a table pet inserted some data. So the question is, if I want to take a look at the data in my watch. Don't worry, wait for me one by one.

SELECT statement

General format of the statement:

SELECT What_to_select

From which_table

WHERE conditions_to_satisfy;

This is the basic format of a simple SELECT statement, detailed and complex later.

Select All data

The simplest form of select is to retrieve all records from one table:

Mysql> SELECT * from pet;
+----------+--------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+----------+--------+---------+------+------------+------------+
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
| Puffball | Diane | Hamster | f | 1999-03-30 | NULL |
| 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 |
| Slim | Benny | Snake | m | 1996-04-29 | NULL |
+----------+--------+---------+------+------------+------------+
9 Rows in Set (0.00 sec)

Mysql>

That's right! This is the operation, not severe. (?????)

Simply say (not really) the UPDATE statement

Now that you can see all the data in the table, you have no problem checking the data in the table.

At this time you suddenly found (the book suggests), Bowser's birth date is wrong, should be born in 1989, not 1979 years.

So, you want to change this data. So you in a moonlight night, you silently delete (delete) all the data in the table, and in the Pet.txt file changed back, re-pet.txt import (LOAD DATA) into the table. This is obviously a very foolish thing to do.

However, a long, long time ago God had created such a command that UPDATE was used to change the problematic record.

Mysql> UPDATE Pet SET birth = ' 1989-08-31 ' WHERE name = ' Bowser ';
Query OK, 1 row affected (0.18 sec)
Rows matched:1 changed:1 warnings:0

Mysql>

This changes the wrong data.

Select a special row

For a chestnut: just now I used the UPDATE statement that God gave me to change Bowser's birthday, I want to have a look at the wood has changed successfully.

The operation is as follows:

SELECT * FROM pet WHERE name = ' Bowser ';

Mysql> SELECT *
From pet
WHERE name = ' Bowser ';
+--------+-------+---------+------+------------+------------+
| name | Owner | Species | sex | Birth | Death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | Dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in Set (0.00 sec)

Mysql>

Well... Looks like God didn't lie to us.

Let's take an example: I want to know which animal was born after 1998.

The operation is as follows:

SELECT * from pet WHERE birth > ' 1998-1-1 ';

Mysql> SELECT *
From pet
--WHERE Birth > ' 1998-01-01 ';
+----------+-------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | Hamster | f | 1999-03-30 | NULL |
| Chirpy | Gwen | Bird | f | 1998-09-11 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in Set (0.14 sec)

Mysql>

And then a chestnut: I want to mate with my family Li Guo to find a little female dog, and I'm going to find a little female dog as a mating object for Li Guo.

The operation is as follows:

SELECT * from pet WHERE species = ' dog ' and sex = ' f ';

Mysql> SELECT *
From pet
WHERE species = ' dog ' and sex = ' f ';
+-------+--------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | Dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
1 row in Set (0.00 sec)

Mysql>

Good, we have the Lee family after.

Or a chestnut: no story.

The operation is as follows:

SELECT * from Get WHERE species = ' snake ' OR species = ' bird ';

Mysql> SELECT *
From pet
WHERE species = ' snake ' OR species = ' bird ';
+----------+-------+---------+------+------------+-------+
| name | Owner | Species | sex | Birth | Death |
+----------+-------+---------+------+------------+-------+
| Whistler | Gwen | Bird | NULL | 1997-12-09 | NULL |
| Chirpy | Gwen | Bird | f | 1998-09-11 | NULL |
| Slim | Benny | Snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
3 Rows in Set (0.00 sec)

Mysql>

Another chestnut: There really is no story. Just to say that and and or can be mixed, but and than OR have a higher priority, so old drivers like me are brackets (for the sake of the stupid people to read).

The operation is as follows:

SELECT * from pet WHERE (species = ' cat ' and sex = ' m ') or (species = ' dog ' and sex = ' f ');

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 |
+-------+--------+---------+------+------------+-------+
2 rows in Set (0.00 sec)

Mysql>

Well, there's still a lot of unfinished talk behind.

MySQL Learning note 003