Mysql学习笔记007

2018-06-17 23:47:23来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 计数行

直接上栗子:客户想要知道有多少pet了, 肿么办?(需要用到一个COUNT(*)的函数)

操作如下:

SELECT COUNT(*) FROM pet;

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.09 sec)

mysql>

 

栗子:现在客户知道了有多少pet, 客户有想知道有多少人养这些pets。

操作如下:

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:使用GROUP BY 对每一个owner进行了分组,没有她,你自己看着办吧。

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>

我的Mysql报的是这个错。、。、

报出的错误都是错的、、、、

 

 

继续举栗子(为了加深印象):每种动物的数量

操作如下:

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>

 

 

栗子:每种性别的数量

操作如下:

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>

 

栗子:按种类和性别查看数量

操作如下:

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>

 

我的现在的理解是先分组(GROUP BY)再计数(COUNT(*))嘛

 

栗子:只看猫狗的性别计数

操作如下:

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>

 

 

本小节最后一个栗子在举就JJ痛:我想知道有性别的动物的种和性别计数

操作如下:

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>

 

 

 

 

 

 

使用一个以上的表 

现在test数据路中只用一个pet表, 但是啊pets孤单寂寞啊, 他们需要另一张表来陪伴他们。这是脑残的客户们就有要求了。他们需要一张表记录pets的时间(event表)。要搞事情!!!

 

 

 

创建(create)一张表(event) 需要有petの名字(name)、事情发生的日期(date)、事情描述(remark)、事情的类型(type)。

操作如下:

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>

 

然后就要插入一些数据了如图:

原谅我的鼠标吧

 

 

INSERT INTO event VALUES('Fluffy','1995-05-15','litter','4 kittens, 3 female, 1 mal
e');

INSERT INTO event VALUES
('Buffy','1993-06-23','litter','5 puppies, 2 female, 3 male'),
('Buffy','1994-06-19','litter','3 puppies, 3 female');


INSERT INTO event VALUES
('Chirpy','1999-03-21','vet','needed beak straightened'),
('Slim','1997-08-03','vet','broken rib'),
('Bowser','1991-10-12','kennel',NULL),
('Fang','1991-10-12','kennel',NULL),
('Fang','1998-08-28','birthday','Gave him a new chew toy'),
('Claws','1998-03-17','birthday','Gave him a new flea collar'),
('Whistler','1998-12-09','birthday','Fitst birthday');

为了方便,我把我写的插入语句贴在上面了。(上面的洋文,我一个都不认识不知道敲的对不对)

 

 

 

不多墨迹了直接上操作:

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>

 

反正就是pets几岁的时候当的母亲,生了啥啥啥之类的。

NOTE:对于两个表中有相同名字的字段要写成   表明.字段名(pet.name   event.name);两个表的连接是靠pet.name = event.name 连接的。(后面会有高级的操作的这里就不多说)

 

还有一种操作就是将一个表分成两个表。。。。。

直接上栗子:pets交配, 同种,一公一母。

操作如下:

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

 

总感觉这个方法比直接找更麻烦呢(* ̄rǒ ̄)

 

to be continued

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:PHP科普

下一篇:初码-Azure系列-记一次MySQL数据库向Azure的迁移