PostgreSQL 数据操作和查询（四）

PostgreSQL使用查询工具插入语句

查看数据

生成更新脚本

数据操作与查询,连接

create table  employee(
   id integer primary key,
   name character(254),
   age integer ,
   sal real,
   address text
  )

--添加语法:
--INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
--VALUES (value1, value2, value3,...valueN);

  insert into employee 
  values
  ( 1,'mike',20,1500.5,'上海市'),
  ( 2,'boy',22,3500.5,'广州中山大道'),
  ( 3,'libra',18,7500.5,'北京市朝阳区'),
  ( 4,'刘芳',25,6500.5,'天津市');

--查询
  select *from employee;

--更新语法:
UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  
WHERE [condition];

update employee set name='刘菲',age=19,sal=3545 where id=2;



--Order By 排序查询
SELECT column-list  
FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

select * from employee where sal>2000 order by sal desc;


--group by 分组查询

SELECT column-list  
FROM table_name  
WHERE [conditions ]  
GROUP BY column1, column2....columnN  
ORDER BY column1, column2....columnN


--
INSERT INTO EMPLOYEE(id,name,age,address,sal) VALUES (6, '李洋', 24, '深圳市福田区中山路', 135000);  
INSERT INTO EMPLOYEE(id,name,age,address,sal) VALUES (7, 'Manisha', 19, 'Noida', 125000);  
INSERT INTO EMPLOYEE(id,name,age,address,sal) VALUES (8, 'Larry', 45, 'Texas', 165000);


--根据每个年龄断计算工资总和
select age, sum(sal) from employee group by age;



--having子句  ctrl+k
SELECT column1, column2  
FROM table1, table2  
WHERE [ conditions ]  
GROUP BY column1, column2  
HAVING [ conditions ]  
ORDER BY column1, column2

select * from employee;
--根据每个年龄断计算工资总和>200000
select age, sum(sal) from employee group by age having sum(sal)>200000;



--部门表
drop table dept;

CREATE TABLE public.dept
(
  id integer primary key,
  dept text,
  fac_id integer
);

-- 插入数据
INSERT INTO dept VALUES(1,'IT', 1);
INSERT INTO dept VALUES(2,'Engineering', 2);
INSERT INTO dept VALUES(3,'HR', 7);


select * from dept;


--1)内连接(INNER JOIN)
--语法
SELECT table1.columns, table2.columns  
FROM table1  
INNER JOIN table2  
ON table1.common_filed = table2.common_field;

--查询员工所在的部门
select * from employee e inner join dept d on e.id=d.id;

--2)左外连接(LEFT OUTER JOIN)
--语法
SELECT table1.columns, table2.columns  
FROM table1  
LEFT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

--查询员工所在的部门,没有找到的以NUll填充
select * from employee e left outer join dept d on e.id=d.id;


--3)右外连接(RIGHT OUTER JOIN)
--语法
SELECT table1.columns, table2.columns  
FROM table1  
RIGHT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;


--查询员工所在的部门,没有找到的以NUll填充
select * from employee e right outer join dept d on e.id=d.id;


--4)全连接(FULL OUTER JOIN)
--语法
SELECT table1.columns, table2.columns  
FROM table1  
FULL OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

--等条件的查询
select * from employee e full outer join dept d on e.id=d.id;

--5)跨连接(CROSS JOIN)
--语法
SELECT coloums   
FROM table1   
CROSS JOIN table2
--笛卡尔积
select *from  employee cross join dept;