PostgresSQL基本操作总结:增删查改等
一、DDL语句
1. 建表语句
CREATE TABLE table_name(
col01_name data_type,
col02_name data_type,
col03_name data_type,
col04_name data_type);
示例:
CREATE TABLE score(
student_name varchar(40),
chinese_score int,
math_score int,
test_date date);
2. 删除表语句
DROP TABLE table_name;
二、DML语句
1. 插入语句
INSERT INTO *table_name*(col01_name,col02_name,col03_name) VALUES(col01_value,col02_value,col03_value);
示例:
INSERT INTO student(id,age,student_name) VALUES(2,13,"李四");
2. 更新语句
UPDATE table_name SET age = col_value;
UPDATE table_name SET col02_name = col02_value WHERE col01_name = col01_value;
示例:
UPDATE student SET age = 15;
UPDATE student SET age = 14 WHERE id = 3;
3. 删除语句
DELETE FROM table_name;
DELETE FROM table_name WHERE col01_name = col01_value;
示例:
DELETE FROM student;
DELETE FROM student WHERE id = 3;
三、查询语句
1. 单表查询语句
SELECT col01_name,col02_name,col03_name FROM table_name;
示例:
SELECT id,student_name,age FROM student;
2. 过滤条件
SELECT *FROM table_name WHERE col01_name = col01_value;
示例:
SELECT *FROM student WHERE id = 3;
3. 排序
SELECT *FROM table_name ORDER BY col01_name;
SELECT *FROM table_name DESC BY col01_name;+
注意:
(1)"ORDER BY"为顺序排序;
(2)"DESC BY"为逆序排序;
(3)排序句子"ORDER BY"应该在"WHERE"子句之后,如果顺序错了,就会报错。
4. 分组查找
SELECT col01_name,cout(*) FROM table_name GROUP BY col01_name;
注意:
使用"GROUP BY"语句时,需要使用聚合函数,常用的聚合函数为"count()"、"sum()"等。
End