dao连表查询 java 连表查询group by

目录

• 一、单表操作

• 1. 分组——> group by
• 2. 排序——> order by
• 3. 分页——> limit
• 4. 总结（很重要）

• 二、多表操作

• 1. 外键
• 2. 一对多（很常见类型）
• 3. 多对多（常见类型）
• 4. 一对一（不常见类型）

• 三、多表联查

• 1. 左连接——>left join 表名 on..
• 2. 右连接——>right join 表名 on..
• 3. 内连接——>inner join 表名 on..
• 4. 交叉连接

一、单表操作

1. 分组——> group by

• 分组指的是：将所有表记录按照某个相同字段进行归类
• 用法：

• select 聚合函数,选取的字段 from 表名 group by 选择分组的字段 having 条件

• 当group by后的字段为一个表的主键时，那么在select后面就可以查询该主键所在表的所有字段。否则select后查的字段只能是group by后的那个字段。
• group by后可以跟多个字段，表示相同这些字段分到一组。如[1,2] [2,3] [,2,3] [3,4] ， [1,2] 分为一组，[2,3] 分为一组 ，[3,4] 分为一组，共三组。

• 聚合函数：count/sum/max/min/avg

• having：表示对分组之后的聚合函数处理的结果，进行再一次的筛选。

• 注意：group by一般要和聚合函数一起使用。

• 注意：聚合函数也可单独使用，放在select 之后。但此时select后面不能再跟其他字段。

• where 条件语句和group by分组语句的先后顺序：
  where > group by > having(*********)
• 实例：

mysql> select depart_id,avg(age) from employee group by depart_id ;
				+-----------+----------+
				| depart_id | avg(age) |
				+-----------+----------+
				|         1 |  45.2500 |
				|         2 |  30.0000 |
				|         3 |  20.0000 |
				+-----------+----------+
				3 rows in set (0.00 sec)

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

2. 排序——> order by

• order by对查询的结果进行排序
• 用法：

• order by 字段名 asc/desc ，其中asc默认的表示升序排序，desc表示降序排序
• 如果对多个字段进行排序，如 order by age desc , id asc，则先对age进行降序排序，如果排完序记录中有相同的age时，再把有相同的这些行按id升序排序。

3. 分页——> limit

• 用法：

• limit 参数1 , 参数2 。参数1表示行索引，从该行开始，表记录第第一行数据的索引是0，往下递增，参数2表示取多少行。

4. 总结（很重要）

• 以上高级用法的使用顺序是：
• select * from 表名 where 条件 group by 条件 having 条件 order by 条件 limit 条件;
• where > group by > having > order by > limit
• 关键字执行的优先级

1. from：找到表
2. where：拿着where指定的约束条件，去文件/表中取出一条条记录
3. group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4. select：执行select
5. distinct：去重
6. having：将分组的结果进行having过滤
7. order by：将结果按条件排序：order by
8. limit：限制结果的显示条数

二、多表操作

1. 外键

• 使用外键的原因：

• 减少占用的空间
• 只需要修改一次原表中的数据 ，其余有对应外键的表中的数据就会相应的修改。

• 使用方法：
  constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

2. 一对多（很常见类型）

• 实例：

create table department(
					id int auto_increment primary key,
					name varchar(32) not null default ''
				)charset utf8;
				
				insert into department (name) values ('研发部');
				insert into department (name) values ('运维部');
				insert into department (name) values ('前台部');
				insert into department (name) values ('小卖部');
				
				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;
				
				insert into userinfo (name, depart_id) values ('zekai', 1);
				insert into userinfo (name, depart_id) values ('xxx', 2);
				insert into userinfo (name, depart_id) values ('zekai1', 3);
				insert into userinfo (name, depart_id) values ('zekai2', 4);
				insert into userinfo (name, depart_id) values ('zekai3', 1);
				insert into userinfo (name, depart_id) values ('zekai4', 2);
				insert into userinfo (name, depart_id) values ('zekai4', 5);

3. 多对多（常见类型）

• 实例：

create table boy (
					id int auto_increment primary key,
					bname varchar(32) not null default ''
				)charset utf8;
				
				insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');
				
				create table girl (
					id int auto_increment primary key,
					gname varchar(32) not null default ''
				)charset utf8;
				insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');
				
				create table boy2girl (
					id int auto_increment primary key,
					bid int not null default 1,
					gid int not null default 1,
					
					constraint fk_boy2girl_boy foreign key (bid) references boy(id),
					constraint fk_boy2girl_girl foreign key (gid) references girl(id)
				)charset utf8;
				
				insert into boy2girl (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);
				
				
				select * from boy left join  boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
				
				mysql> select * from boy left join  boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
				+----+----------+------+------+------+------+---------+
				| id | bname    | id   | bid  | gid  | id   | gname   |
				+----+----------+------+------+------+------+---------+
				|  1 | zhangsan |    1 |    1 |    1 |    1 | cuihua  |
				|  1 | zhangsan |    2 |    1 |    2 |    2 | gangdan |
				|  2 | lisi     |    5 |    2 |    2 |    2 | gangdan |
				|  2 | lisi     |    3 |    2 |    3 |    3 | jianguo |
				|  3 | zhaoliu  |    4 |    3 |    3 |    3 | jianguo |
				+----+----------+------+------+------+------+---------+
				5 rows in set (0.00 sec)

				mysql> select bname, gname from boy left join  boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;
				+----------+---------+
				| bname    | gname   |
				+----------+---------+
				| zhangsan | cuihua  |
				| zhangsan | gangdan |
				| lisi     | gangdan |
				| lisi     | jianguo |
				| zhaoliu  | jianguo |
				+----------+---------+
				5 rows in set (0.00 sec)
				
				mysql> select bname, gname from boy left join  boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid where bname='zhangsan';
				+----------+---------+
				| bname    | gname   |
				+----------+---------+
				| zhangsan | cuihua  |
				| zhangsan | gangdan |
				+----------+---------+
				2 rows in set (0.02 sec)

4. 一对一（不常见类型）

• 实例：

user :
					id   name  age  
					1    zekai  18   
					2    zhangsan 23  
					3    xxxx   19   
				
				由于salary是比较敏感的字段，因此我们需要将此字段单独拆出来， 变成一张独立的表
				
				private：
					
					id  salary   uid  (外键 + unique)
					1    5000     1
					2    6000     2
					3    3000     3
					
					
				create table user (
					id int auto_increment primary key,
					name varchar(32) not null default ''
				)charset=utf8;
				
				insert into user (name) values ('zhangsan'),('zekai'),('kkk');
				
				
				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 user(id),
					unique(uid)
				)charset=utf8;
				
				insert into priv (salary, uid) values (2000, 1);
				insert into priv (salary, uid) values (2800, 2);
				insert into priv (salary, uid) values (3000, 3);
				
				insert into priv (salary, uid) values (6000, 1);
				ERROR 1062 (23000): Duplicate entry '1' for key 'uid'

三、多表联查

1. 左连接——>left join 表名 on..

• 表示已left左边的表为主，会把左边的表中的信息全部显示，被join的表按照左边的表的数据一一对应显示。
• 实例

# 原表
mysql> select * from department;
			+----+--------+
			| id | name   |
			+----+--------+
			|  1 | 研发部 |
			|  2 | 运维部 |
			|  3 | 前台部 |
			|  4 | 小卖部 |
			+----+--------+
			4 rows in set (0.07 sec)

			mysql> select * from userinfo;
			+----+--------+-----------+
			| id | name   | depart_id |
			+----+--------+-----------+
			|  1 | zekai  |         1 |
			|  2 | xxx    |         2 |
			|  3 | zekai1 |         3 |
			|  4 | zekai2 |         4 |
			|  5 | zekai3 |         1 |
			|  6 | zekai4 |         2 |
			+----+--------+-----------+
			6 rows in set (0.00 sec)

# 联表查询：            
			
# 错误写法				
mysql> select name  from userinfo left join department on depart_id = department.id;
ERROR 1052 (23000): Column 'name' in field list is ambiguous

# 正确写法
mysql> select userinfo.name as uname, department.name as dname  from userinfo left join department on depart_id = department.id;

# 查询结果
				+--------+--------+
				| uname  | dname  |
				+--------+--------+
				| zekai  | 研发部 |
				| zekai3 | 研发部 |
				| xxx    | 运维部 |
				| zekai4 | 运维部 |
				| zekai1 | 前台部 |
				| zekai2 | 小卖部 |
				+--------+--------+
				6 rows in set (0.00 sec)

2. 右连接——>right join 表名 on..

• 表示已right右边的表为主，会把右边的表中的信息全部显示，被join的表按照右边的表的数据一一对应显示。
• 实例：

mysql> insert into department (name) values ('财务部');
				Query OK, 1 row affected (0.04 sec)

				mysql>
				mysql> select * from department;                     );
				+----+--------+
				| id | name   |
				+----+--------+
				|  1 | 研发部 |
				|  2 | 运维部 |
				|  3 | 前台部 |
				|  4 | 小卖部 |
				|  5 | 财务部 |
				+----+--------+
				5 rows in set (0.00 sec)

				mysql> select * from userinfo;
				+----+--------+-----------+
				| id | name   | depart_id |
				+----+--------+-----------+
				|  1 | zekai  |         1 |
				|  2 | xxx    |         2 |
				|  3 | zekai1 |         3 |
				|  4 | zekai2 |         4 |
				|  5 | zekai3 |         1 |
				|  6 | zekai4 |         2 |
				+----+--------+-----------+
				6 rows in set (0.00 sec)

				mysql> select userinfo.name as uname, department.name as dname  from userinfo left join department on depart_id = department.id;
				+--------+--------+
				| uname  | dname  |
				+--------+--------+
				| zekai  | 研发部 |
				| zekai3 | 研发部 |
				| xxx    | 运维部 |
				| zekai4 | 运维部 |
				| zekai1 | 前台部 |
				| zekai2 | 小卖部 |
				+--------+--------+
				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  |
				+--------+--------+
				| zekai  | 研发部 |
				| zekai3 | 研发部 |
				| xxx    | 运维部 |
				| zekai4 | 运维部 |
				| zekai1 | 前台部 |
				| zekai2 | 小卖部 |
				| NULL   | 财务部 |
				+--------+--------+
				7 rows in set (0.00 sec)

3. 内连接——>inner join 表名 on..

• 把两个表中共同的部分取出来连接。
• 实例：

mysql> select * from department inner join userinfo on department.id=userinfo.depart_id;
				+----+--------+----+--------+-----------+
				| id | name   | id | name   | depart_id |
				+----+--------+----+--------+-----------+
				|  1 | 研发部 |  1 | zekai  |         1 |
				|  1 | 研发部 |  5 | zekai3 |         1 |
				|  2 | 运维部 |  2 | xxx    |         2 |
				|  2 | 运维部 |  6 | zekai4 |         2 |
				|  3 | 前台部 |  3 | zekai1 |         3 |
				|  4 | 小卖部 |  4 | zekai2 |         4 |
				+----+--------+----+--------+-----------+
				6 rows in set (0.00 sec)

4. 交叉连接

• 语法：from 表1,表2