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+kSELECT column1, column2  FROM table1, table2  WHERE [ conditions ]  GROUP BY column1, column2  HAVING [ conditions ]  ORDER BY column1, column2select * from employee;--根据每个年龄断计算工资总和>200000select 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;