psql 表的行操作

插入操作

除了数字值之外，都用单引号包围。

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

更新操作

UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  ;

删除操作

DELETE FROM table_name  
WHERE [condition];

查询操作

1.可以在查询的时候进行计算

ELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

2.DISTINCT 去重

SELECT DISTINCT city FROM weather;

2.where限制

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

3.order by 排序

SELECT * FROM weather
    ORDER BY city temp_lo;

4.group by 分组

SELECT NAME, SUM(SALARY)   
FROM EMPLOYEES   
GROUP BY NAME;

5.having 分组限制

SELECT NAME   
FROM STUDENT 
GROUP BY NAME HAVING COUNT (NAME) < 2;

连接查询

内链接
SELECT *
FROM weather, cities
WHERE cities.name = weather.city;

SELECT table1.columns, table2.columns  
FROM table1  
INNER JOIN table2  
ON table1.common_filed = table2.common_field;

外链接
左链接

SELECT table1.columns, table2.columns  
FROM table1  
LEFT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

以本表为主，链接表没有补充为null
右链接

SELECT table1.columns, table2.columns  
FROM table1  
RIGHT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

全链接

SELECT table1.columns, table2.columns  
FROM table1  
FULL OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

交叉链接（笛卡尔链接）

SELECT coloums   
FROM table1   
CROSS JOIN table2

三表链接

select * from student,teacher,project where student.id=teacher.sid and student.id=project.sid;
select * from student inner join teacher on student.id=teacher.sid inner join project on student.id=project.sid;