MySQL數據庫3分組與單表、多表查詢

• 2019 年 12 月 16 日
• 筆記

浮華褪盡，人比煙花寂寞…… ——張愛玲

一、表操作的補充

select * from 表名 where 列名 in （值1，值2,。。。）；

查出對應值的數據。

1.1null 和 not null

使用null的時候：

當創建的表中有null時我們如果需要查詢出來null所對應的信息，需要用select * from 表名 where 字段名 is null;

mysql> create table v1(id int auto_increment primary key,      -> name varchar(32),email varchar(32))charset=utf8;  Query OK, 0 rows affected (0.70 sec)    mysql> insert into v1(email) values('xxx');  Query OK, 1 row affected (0.07 sec)    mysql> select * from v1;  +----+------+-------+  | id | name | email |  +----+------+-------+  |  1 | NULL | xxx   |  +----+------+-------+  1 row in set (0.00 sec)    mysql> select * from v1 where name is null;  +----+------+-------+  | id | name | email |  +----+------+-------+  |  1 | NULL | xxx   |  +----+------+-------+  1 row in set (0.01 sec)    mysql> select *from v1 where name='';  Empty set (0.00 sec)

使用null會降低數據的查詢效率，不推薦使用，在創建表的時候建議把值默認為空。

1.2使用not null的時候

mysql> create table v2(id int auto_increment primary key,      -> name varchar(32) not null default '',email varchar(32)not null default '')charset=utf8;  Query OK, 0 rows affected (0.44 sec)      mysql> insert into v2(email) values('xxx');  Query OK, 1 row affected (0.06 sec)    mysql> select *from v2 where name='';  +----+------+-------+  | id | name | email |  +----+------+-------+  |  1 |      | xxx   |  +----+------+-------+  1 row in set (0.00 sec)

二、單表的操作（import）

2.1分組

分組：將所標記的某個相同字段進行歸類，比如員工信息表的職位分組，或者按照性別進行分組等。

2.1.1聚合函數

max（列）求出列中的最大值

min（列）求出列中的最小值

sum（列）對列中的數據求和

count（列）對列中的數據計數

avg（列）對列中的數據計算平均數

例子見group by

2.1.2group by

用法：

select 聚合函數, 選取的字段 from employee group by 分組的字段;

group by 是分組的關鍵詞，group by 必須和聚合函數（count）一塊出現。count（字段名），按照條件對字段中的數據進行計數。

例子：

1.以性別為例， 進行分組， 統計一下男生和女生的人數是多少個。

mysql> create table employee(      -> id int not null unique auto_increment primary key,      -> name varchar(20) not null,      -> gender enum('male','female') not null default 'male',      -> age int(3) unsigned not null default 28,      -> hire_date date not null,      -> post varchar(50),      -> post_comment varchar(100),      -> salary double(15,2),      -> office int,      -> depart_id int      -> )charset=utf8;  Query OK, 0 rows affected (0.61 sec)    mysql> insert into employee(name,gender,age,hire_date,post,salary,office,depart_id) values      -> ('小張','male',73,'20140701','研發部',3500,401,1),      -> ('小李','male',28,'20121101','研發部',2100,401,1),      -> ('小趙','female',18,'20150411','研發部',18000,403,3),      -> ('歪歪','female',48,'20150311','銷售部',3000.13,402,2),      -> ('丫丫','female',38,'20101101','銷售部',2000.35,402,2),      -> ('丁丁','female',18,'20110312','銷售部',1000.37,402,2),      -> ('小明','male',28,'20160311','運營部',10000.13,403,3),      -> ('小華','male',18,'19970312','運營部',20000,403,3),      -> ('小王','female',18,'20130311','運營部',19000,403,3);  Query OK, 9 rows affected (0.09 sec)  Records: 9  Duplicates: 0  Warnings: 0    mysql> select count(id),gender from employee group by gender;  +-----------+--------+  | count(id) | gender |  +-----------+--------+  |         4 | male   |  |         5 | female |  +-----------+--------+  2 rows in set (0.10 sec)    mysql> select gender,count(id) as total from employee group by gender;  #這裡可以用as重命名顯示的列名  +--------+-------+  | gender | total |  +--------+-------+  | male   |     4 |  | female |     5 |  +--------+-------+  2 rows in set (0.00 sec)

2.對部門進行分組， 求出每個部門年齡最大的那個人。

mysql> select depart_id , max(age) from employee group by depart_id;  +-----------+----------+  | depart_id | max(age) |  +-----------+----------+  |         1 |       73 |  |         2 |       48 |  |         3 |       28 |  +-----------+----------+  3 rows in set (0.04 sec)

3.對部門進行分組， 求出每個部門年齡求和。

mysql> select depart_id,sum(age) from employee group by depart_id;  +-----------+----------+  | depart_id | sum(age) |  +-----------+----------+  |         1 |      101 |  |         2 |      104 |  |         3 |       82 |  +-----------+----------+  3 rows in set (0.00 sec)

4.對部門進行分組， 求出每個部門年齡求平均數。

mysql> select depart_id,avg(age) from employee group by depart_id;  +-----------+----------+  | depart_id | avg(age) |  +-----------+----------+  |         1 |  50.5000 |  |         2 |  34.6667 |  |         3 |  20.5000 |  +-----------+----------+  3 rows in set (0.02 sec)

2.1.3having

對group by 之後的數據進行二次篩選

例子

5.對部門進行分組， 求出每個部門年齡求平均數,選出平均數最大的部門。

mysql> select depart_id,avg(age) from employee group by depart_id having avg(age)>35;  +-----------+----------+  | depart_id | avg(age) |  +-----------+----------+  |         1 |  50.5000 |  +-----------+----------+  1 row in set (0.00 sec)      mysql> select depart_id,avg(age)as pj from employee group by depart_id having pj>35;  +-----------+---------+  | depart_id | pj      |  +-----------+---------+  |         1 | 50.5000 |  +-----------+---------+  1 row in set (0.00 sec)

2.1.4升序和降序

order by 字段名 asc（升序）desc(降序)

升序和降序可以同時使用如age desc, id asc; 表示： 先對age進行降序， 如果age有相同的行， 則對id進行升序。

例子

mysql> select * from employee order by age desc,id desc;  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  | id | name   | gender | age | hire_date  | post      | post_comment | salary   | office | depart_id |  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  |  1 | 小張   | male   |  73 | 2014-07-01 | 研發部    | NULL         |  3500.00 |    401 |         1 |  |  4 | 歪歪   | female |  48 | 2015-03-11 | 銷售部    | NULL         |  3000.13 |    402 |         2 |  |  5 | 丫丫   | female |  38 | 2010-11-01 | 銷售部    | NULL         |  2000.35 |    402 |         2 |  |  7 | 小明   | male   |  28 | 2016-03-11 | 運營部    | NULL         | 10000.13 |    403 |         3 |  |  2 | 小李   | male   |  28 | 2012-11-01 | 研發部    | NULL         |  2100.00 |    401 |         1 |  |  9 | 小王   | female |  18 | 2013-03-11 | 運營部    | NULL         | 19000.00 |    403 |         3 |  |  8 | 小華   | male   |  18 | 1997-03-12 | 運營部    | NULL         | 20000.00 |    403 |         3 |  |  6 | 丁丁   | female |  18 | 2011-03-12 | 銷售部    | NULL         |  1000.37 |    402 |         2 |  |  3 | 小趙   | female |  18 | 2015-04-11 | 研發部    | NULL         | 18000.00 |    403 |         3 |  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  9 rows in set (0.00 sec)

2.1.5limit限制輸出

limit offset ，size

limit 起始行索引，向下查詢的長度(索引為0代表第一行)

例子

mysql> select * from employee limit 0,3;  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  | id | name   | gender | age | hire_date  | post      | post_comment | salary   | office | depart_id |  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  |  1 | 小張   | male   |  73 | 2014-07-01 | 研發部    | NULL         |  3500.00 |    401 |         1 |  |  2 | 小李   | male   |  28 | 2012-11-01 | 研發部    | NULL         |  2100.00 |    401 |         1 |  |  3 | 小趙   | female |  18 | 2015-04-11 | 研發部    | NULL         | 18000.00 |    403 |         3 |  +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+  3 rows in set (0.00 sec)

2.1.6查詢表示需要遵循的順序（important）

select * from 表名 where 條件 group by 條件 having 條件 order by 條件 limit 條件;

where > group by > having > order by > limit

三、多表操作

外鍵

主關鍵字(primary key)是表中的一個或多個字段，它的值用於唯一地標識表中的某一條記錄。

公共關鍵字（Common Key）在關係數據庫中，關係之間的聯繫是通過相容或相同的屬性或屬性組來表示的。如果兩個關係中具有相容或相同的屬性或屬性組，那麼這個屬性或屬性組被稱為這兩個關係的公共關鍵字。

如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外鍵。由此可見，外鍵表示了兩個關係之間的相關聯繫。以另一個關係的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。外鍵又稱作外關鍵字。

使用外鍵的原因：

1.減少佔用的內存空間

2.只需要修改主表的數據，從表的數據也會相應的跟着修改

3.1一對多

一對多指一個主表中的數據和從表中的數據是一對多的關係，如下例，一個部門可以有多個員工。

使用方法：

constraint 外鍵名 foreign key (被約束的字段) references 約束的表(約束的字段)

mysql> create table department(      -> id int auto_increment primary key,      -> name varchar(32) not null default '')      -> charset utf8;  Query OK, 0 rows affected (0.42 sec)    mysql> insert into department(name) values('研發部'),('運維部'),('前台部'),('小賣部');  Query OK, 4 rows affected (0.06 sec)  Records: 4  Duplicates: 0  Warnings: 0    mysql> create table userinfo(id int auto_increment primary key,      -> name varchar(32) not null default '',      -> depart_id int not null default 1,      -> constraint fk_user_depart foreign key (depart_id) references department(id))      -> charset utf8;  Query OK, 0 rows affected (0.39 sec)    mysql> insert into userinfo (name,depart_id) values('xiaozhu',1),('xiaoyu',1),      -> ('laohe',2),('longge',2),('ludi',3),('xiaoguo',4);  Query OK, 6 rows affected (0.21 sec)  Records: 6  Duplicates: 0  Warnings: 0    mysql> select * from userinfo;  +----+---------+-----------+  | id | name    | depart_id |  +----+---------+-----------+  |  1 | xiaozhu |         1 |  |  2 | xiaoyu  |         1 |  |  3 | laohe   |         2 |  |  4 | longge  |         2 |  |  5 | ludi    |         3 |  |  6 | xiaoguo |         4 |  +----+---------+-----------+  6 rows in set (0.00 sec)    mysql> select * from department;  +----+-----------+  | id | name      |  +----+-----------+  |  1 | 研發部    |  |  2 | 運維部    |  |  3 | 前台部    |  |  4 | 小賣部    |  +----+-----------+  4 rows in set (0.00 sec)    mysql> insert into userinfo(name,depart_id) values('xiaozhang',5);#depart_id受department.id的約束  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test2`.`userinfo`, CONSTRAINT `fk_user_depart` FOREIGN KEY (`depart_id`) REFERENCES `department` (`id`))    #聯表查詢  mysql> select userinfo.name as uname,department.name as dname from userinfo left      -> join department on depart_id = department.id;  +---------+-----------+  | uname   | dname     |  +---------+-----------+  | xiaozhu | 研發部    |  | xiaoyu  | 研發部    |  | laohe   | 運維部    |  | longge  | 運維部    |  | ludi    | 前台部    |  | xiaoguo | 小賣部    |  +---------+-----------+  6 rows in set (0.00 sec)

3.2多對多

多對多指當一個主表有多個從表時，從表之間的每個數據之間的關係就是多對多，如下圖，一個boy可以和多個girl約會，一個girl也可以和多個boy約會。

mysql> create table boy(id int auto_increment primary key,      -> bname varchar(32) not null default'')charset utf8;  Query OK, 0 rows affected (0.36 sec)    mysql> insert into boy(bname) values('zhangsan'),('lisi'),('wangwu');  Query OK, 3 rows affected (0.09 sec)  Records: 3  Duplicates: 0  Warnings: 0    mysql> create table girl(id int auto_increment primary key,      -> gname varchar(32) not null default'')charset utf8;  Query OK, 0 rows affected (0.33 sec)    mysql> insert into girl(gname) values('xiaoli'),('xiaohua'),('xiaomei');  Query OK, 3 rows affected (0.06 sec)  Records: 3  Duplicates: 0  Warnings: 0    mysql> create table boy_girl(id int auto_increment primary key,      -> bid int not null default 1,      -> gid int not null default 1,      -> constraint fk_boy_girl_boy foreign key(bid) references boy(id),      -> constraint fk_boy_girl_girl foreign key(gid) references girl(id)      -> )charset utf8;  Query OK, 0 rows affected (0.42 sec)    mysql> insert into boy_girl(bid,gid) values(1,1),(2,2),(3,3),(3,1),(2,1),(1,2);  Query OK, 6 rows affected (0.04 sec)  Records: 6  Duplicates: 0  Warnings: 0    mysql> select * from boy left join boy_girl on boy.id = boy_girl.bid left join girl      -> on girl.id = boy_girl.gid;  +----+----------+------+------+------+------+---------+  | id | bname    | id   | bid  | gid  | id   | gname   |  +----+----------+------+------+------+------+---------+  |  1 | zhangsan |    1 |    1 |    1 |    1 | xiaoli  |  |  2 | lisi     |    5 |    2 |    1 |    1 | xiaoli  |  |  3 | wangwu   |    4 |    3 |    1 |    1 | xiaoli  |  |  1 | zhangsan |    6 |    1 |    2 |    2 | xiaohua |  |  2 | lisi     |    2 |    2 |    2 |    2 | xiaohua |  |  3 | wangwu   |    3 |    3 |    3 |    3 | xiaomei |  +----+----------+------+------+------+------+---------+  6 rows in set (0.03 sec)    mysql> select bname,gname from boy left join boy_girl on boy.id = boy_girl.bid      -> left join girl on girl.id = boy_girl.gid;  +----------+---------+  | bname    | gname   |  +----------+---------+  | zhangsan | xiaoli  |  | lisi     | xiaoli  |  | wangwu   | xiaoli  |  | zhangsan | xiaohua |  | lisi     | xiaohua |  | wangwu   | xiaomei |  +----------+---------+  6 rows in set (0.00 sec)

3.3一對一

一對一指的是兩個表中的數據是一對一的關係，使用unique（字段名）來約束這種關係。如下例，由於工資屬於員工的敏感信息，用單獨的表去存儲，這時工資和員工信息就是一對一的關係。

mysql> create table priv(      -> id int auto_increment primary key,      -> salary int not null default 0,      -> uid int not null default 1,      -> constraint fk_priv_user foreign key (uid) references userinfo(id),      -> unique(uid)) charset=utf8;  Query OK, 0 rows affected (0.52 sec)    mysql> insert into priv(salary,uid) values (10000,1),(12000,2),(15000,3),(8000,4),(9000,5),(9900,6);  Query OK, 6 rows affected (0.16 sec)  Records: 6  Duplicates: 0  Warnings: 0    mysql> select name,salary from userinfo left join priv on priv.uid = userinfo.id;  +---------+--------+  | name    | salary |  +---------+--------+  | xiaozhu |  10000 |  | xiaoyu  |  12000 |  | laohe   |  15000 |  | longge  |   8000 |  | ludi    |   9000 |  | xiaoguo |   9900 |  +---------+--------+  6 rows in set (0.00 sec)

3.4多表聯查

多表聯查就是將多個有關係的表放在一起查，使用的語句有：

left join……on查詢時以左邊的數據為主

right join ……on查詢時以右邊的數據為主

mysql> insert into department(name) values('業務部');  Query OK, 1 row affected (0.12 sec)    mysql> select userinfo.name as uname,department.name as dname from userinfo left      -> join department on depart_id = department.id;  +---------+-----------+  | uname   | dname     |  +---------+-----------+  | xiaozhu | 研發部    |  | xiaoyu  | 研發部    |  | laohe   | 運維部    |  | longge  | 運維部    |  | ludi    | 前台部    |  | xiaoguo | 小賣部    |  +---------+-----------+  6 rows in set (0.00 sec)    mysql> select userinfo.name as uname,department.name as dname from userinfo right      -> join department on depart_id = department.id;  +---------+-----------+  | uname   | dname     |  +---------+-----------+  | xiaozhu | 研發部    |  | xiaoyu  | 研發部    |  | laohe   | 運維部    |  | longge  | 運維部    |  | ludi    | 前台部    |  | xiaoguo | 小賣部    |  | NULL    | 業務部    |  +---------+-----------+  7 rows in set (0.00 sec)