首页 > > 网络编程 > Mysql >

Mysql学习笔记007

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

计数行

直接上栗子：客户想要知道有多少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>

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>

反正就是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