postgresql连接查询

1.创建表a

create table a(id integer primary key,name varchar(10),bno integer);

Paste_Image.png

2.向表a插入数据

INSERT INTO a ("id", "name", "bno") VALUES ('1', '李四', '101'),('2', '张三', '102'),('3', '张三', '101'),('4', '王五', '101'),('5', '赵', '103'),('6', '张三', '101');

Paste_Image.png

3.创建表b

create table b(id integer primary key,name varchar(10));

b

4.插入数据

INSERT INTO "public"."b" ("id","name") VALUES ('101', '工行'),('102', '农行'),('106', '农行');

Paste_Image.png

5.查看表a、b内容

表a 表b

---

6.内连接inner join<==>join

select * from a join b on a.bno=b.id;

等价于

select * from a inner join b on a.bno=b.id;

Paste_Image.png

反过来

select * from b inner join a on a.bno=b.id;

Paste_Image.png
** 内连接分析**
inner join 关键字在表中存在至少一个匹配时返回行。如a表中第1条记录中bno=101，即可和b表id为101的行拼接作为新表的第一行，a表中第5条记录没有和b表id匹配，所以新表中没有a.id=5的记录

---

7.外连接

7.1 全外连接full outer join<==>full join

• 以a表为主

select * from a full join b on a.bno=b.id;

Paste_Image.png

• 以b表为主

select * from b full join a on a.bno=b.id;

Paste_Image.png
外连接分析
FULL OUTER JOIN 关键字返回左表（a）和右表（b）中所有的行。如果 "a" 表中的行在 "b" 中没有匹配或者 "b" 表中的行在 "a" 表中没有匹配，也会列出这些行。
如以a表为主时，先将a表中所有记录列出，后面有对应的b.id的记录就直接拼接在后面，没有对应的记录就以空值填充（比如第5条记录），最后将b表中没有对应值的列出（比如第7条记录）。

---

7.2 左连接left outer join

• 以表a为主

select * from a left join b on a.bno=b.id;

Paste_Image.png

• 以表b为主

select * from b left join a on a.bno=b.id;

Paste_Image.png
左连接分析
left 关键字从左表（a）返回所有的行，即使右表（b）中没有匹配。
即将所有与其有关的记录都查找出来。

---

7.3 右连接

• 以表a为主

select * from a right join b on a.bno=b.id;

Paste_Image.png

• 以表b为主

select * from b right join a on a.bno=b.id;

Paste_Image.png

8.分组查询（以下数据纯属虚构）

_area表 _company表

8.1 统计各地区注册公司数量

select count(aid),a.name from _company c join _area a on c.aid=a.id group by a.id;

查询结果

8.2 统计不同注册资金的公司数量

select count(*),level from (
select
(case
when registeredcaptial<1000000 then '一级'
when registeredcaptial>=1000000 and registeredcaptial<2000000 then '二级'
when registeredcaptial>=2000000 and registeredcaptial<3000000 then '三级'
when registeredcaptial>=3000000 then '四级'
end )
as level from _company) a group by level order by count;

查询结果 理解

---

修改字段

1. 向表中添加新的字段
   alter table table_name add column_name varchar2(20) not null
2. 删除表中的一个字段
   delete table table_name column column_name
3. 修改表中的一个字段名
   alter table table_name oldname newname type
   如：alter table test change sno sid varchar(10)
   alter table table_name rename column oldname to newname
4. 添加主键约束
   alter table 表名
   add constraint 约束名 primary key (列名)
5. 添加唯一约束
   alter table 表名
   add constraint 约束名 unique (列名)
6. 添加默认约束
   alter table 表名
   add constraint 约束名 default(内容) for 列名
7. 添加check约束
   alter table 表名
   add constraint 约束名 check(内容)
8. 添加外键约束
   alter table 表名
   add constraint 约束名 foreign key(列名) references 另一表名(列名)
9. 删除约束
   alter table 表名
   drop constraint 约束名

MySql

分组统计查询(表名为user_，分组依据是注册时间--register)

表结构及数据

1.按注册时间分组

select count(*),register from user_ group by register;

Paste_Image.png

2.按注册月份分组

select count(*),register from user_ group by month(register);

Paste_Image.png

3.按注册年月分组

select count(*),date_format(register,"%Y-%m") as time
from user_ group by date_format(register,"%Y-%m");

Paste_Image.png