PostgreSQL 数据操作和查询（四）

绝地灬酷狼 2023-10-17 17:07 30阅读 0赞

### PostgreSQL使用查询工具插入语句 ###

![Center][]

**查看数据**

![Center 1][]

**生成更新脚本**

![Center 2][]

![Center 3][]

数据操作与查询,连接

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;

[Center]: https://img-blog.csdn.net/20171130151047177?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20171130151113612?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20171130152545135?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 3]: https://img-blog.csdn.net/20171130152554469?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center