Mysql Study Notes 007 and mysql Study Notes

Mysql Study Notes 007 and mysql Study Notes
Counting row

Directly put the chestnuts on: Do customers want to know how many pets are swollen? (A count (*) function is required)

The procedure is as follows:

Select count (*) FROM pet;

Mysql> select count (*) FROM pet;
+ ---------- +
| COUNT (*) |
+ ---------- +
| 9 |
+ ---------- +
1 row in set (0.09 sec)

Mysql>

 

Chestnut: Now the customer knows how many pets there are, and the customer wants to know how many people raise these pets.

The procedure is as follows:

SELECT owner, COUNT (*) FROM pet group by owner;

Mysql> SELECT owner, COUNT (*) FROM pet group by owner;
+ -------- + ---------- +
| Owner | COUNT (*) |
+ -------- + ---------- +
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+ -------- + ---------- +
4 rows in set (0.00 sec)

Mysql>

 

NOTE: You can use group by to GROUP every owner without her.

Mysql> SELECT owner, COUNT (*) FROM pet;
ERROR 1140 (42000): In aggregated query without group by, expression #1 of SELECT list con
Tains nonaggregated column 'test. pet. owner'; this is incompatible with SQL _mode = only_full _
Group_by
Mysql>

My Mysql reports this error. ,. ,

Errors are reported ,,,,

 

 

Continue to give examples (to deepen the impression): number of each animal

The procedure is as follows:

SELECT species, COUNT (*) FROM pet group by species;

Mysql> SELECT species, COUNT (*)
-> FROM pet
-> Group by species;
+ --------- + ---------- +
| Species | COUNT (*) |
+ --------- + ---------- +
| Bird | 2 |
| Cat | 2 |
| Dog | 3 |
| Hamster | 1 |
| Snake | 1 |
+ --------- + ---------- +
5 rows in set (0.20 sec)

Mysql>

 

 

Chestnut: quantity of each gender

The procedure is as follows:

SELECT sex, COUNT (*) FROM pet group by sex;

Mysql> SELECT sex, COUNT (*)
-> FROM pet
-> Group by sex;
+ ------ + ---------- +
| Sex | COUNT (*) |
+ ------ + ---------- +
| NULL | 1 |
| F | 4 |
| M | 4 |
+ ------ + ---------- +
3 rows in set (0.00 sec)

Mysql>

 

Chestnut: View quantity by type and gender

The procedure is as follows:

SELECT species, sex, COUNT (*) FROM pet group by species, sex;

Mysql> SELECT species, sex, COUNT (*)
-> FROM pet
-> Group by species, sex;
+ --------- + ------ + ---------- +
| Species | sex | COUNT (*) |
+ --------- + ------ + ---------- +
| Bird | NULL | 1 |
| Bird | f | 1 |
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
| Hamster | f | 1 |
| Snake | m | 1 |
+ --------- + ------ + ---------- +
8 rows in set (0.00 sec)

Mysql>

 

My current understanding is that grouping (group by) and counting (COUNT (*) first.

 

Chestnut: only check the Gender count of cats and dogs

The procedure is as follows:

SELECT species, sex, COUNT (*) FROM pet WHERE species = 'Dog' OR species = 'cat' group by species, sex;

Mysql> SELECT species, sex, COUNT (*)
-> FROM pet
-> WHERE species = 'Dog' OR species = 'cat'
-> Group by species, sex;
+ --------- + ------ + ---------- +
| Species | sex | COUNT (*) |
+ --------- + ------ + ---------- +
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
+ --------- + ------ + ---------- +
4 rows in set (0.00 sec)

Mysql>

 

 

The last chestnut in this section is about JJ pain: I want to know the species and Gender counts of sex animals.

The procedure is as follows:

SELECT species, sex, COUNT (*) FROM pet WHERE sex is not null group by species, sex;

Mysql> SELECT species, sex, COUNT (*)
-> FROM pet
-> WHERE sex IS NOT NULL
-> Group by species, sex;
+ --------- + ------ + ---------- +
| Species | sex | COUNT (*) |
+ --------- + ------ + ---------- +
| Bird | f | 1 |
| Cat | f | 1 |
| Cat | m | 1 |
| Dog | f | 1 |
| Dog | m | 2 |
| Hamster | f | 1 |
| Snake | m | 1 |
+ --------- + ------ + ---------- +
7 rows in set (0.00 sec)

Mysql>

 

 

 

 

 

 

Use more than one table

Currently, only one pet table is used in the test data path, but pets is lonely. They need another table to accompany them. This is what customers have to do. They need a table to record the pets time (event table ). Something to do !!!

 

 

 

Create a table (event) requires the pet sequence name, date, remark, and type ).

The procedure is as follows:

Create table event (name VARCHAR (20), date DATE, type VARCHAR (15), remark VARCHAR (255 ));

Mysql> create table event (
-> Name VARCHAR (20 ),
-> Date DATE,
-> Type VARCHAR (15 ),
-> Remark VARCHAR (255 ));
Query OK, 0 rows affected (0.74 sec)

Mysql>

 

Then we need to insert some data.

Forgive me for the mouse.

 

 

Insert into event VALUES ('fluffy', '2017-05-15 ', 'litter', '4 kittens, 3 female, 1 mal
E ');

Insert into event VALUES
('Buffy', '2017-06-23 ', 'litter', '5 puppies, 2 female, 3 Male '),
('Buffy', '2017-06-19 ', 'litter', '3 puppies, 3 female ');

Insert into event VALUES
('Chirpy', '2017-03-21 ', 'vet', 'needed beak straightened '),
('SLIM', '2017-08-03 ', 'vet', 'Broken rib '),
('Boken', '2017-10-12 ', 'kennel', NULL ),
('Fang ', '2017-10-12', 'kennel ', NULL ),
('Fang ', '2017-08-28', 'birthday', 'gave him a new chew toy '),
('Claws', '2017-03-17 ', 'birthday', 'gave him a new flea collar '),
('Whistler', '2017-12-09', 'birthday', 'fitst birthday ');

For convenience, I pasted the insert statement I wrote on it. (I don't know either of the above foreign languages, but I don't know whether to answer the question)

 

 

 

You can directly perform the following operations:

SELECT pet. name, (YEAR (date)-YEAR (birth)-(RIGHT (date, 5) <RIGHT (birth, 5) AS age, remark FROM pet, event WHERE pet. name = event. name AND event. type = 'litter ';

Mysql> SELECT pet. name,
-> (YEAR (date)-YEAR (birth)-(RIGHT (date, 5) <RIGHT (birth, 5) AS age,
-> Remark
-> FROM pet, event
-> WHERE pet. name = event. name AND event. type = 'litter ';
+ -------- + ------ + ----------------------------- +
| Name | age | remark |
+ -------- + ------ + ----------------------------- +
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+ -------- + ------ + ----------------------------- +
3 rows in set (0.13 sec)

Mysql>

 

It's the mother of pets when she was a few years old.

NOTE: For fields with the same name in two tables, it must be written to indicate the. Field name (pet. name event. name). The connection between the two tables depends on the pet. name = event. name connection. (There will be more advanced operations later)

 

Another operation is to divide a table into two tables .....

Direct chestnut: pets mating, the same type, a male and a mother.

The procedure is as follows:

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 ';

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 | species |
+ -------- + ------ + --------- +
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+ -------- + ------ + --------- +
3 rows in set (0.00 sec)

Mysql>

 

I always feel that this method is more difficult than finding it directly (* ￣ r ǒ ￣)

 

To be continued