PostgreSQL-4-DML数据操纵语言

1、查询语句

\h SELECT  查看SELECT语句说明

 

基本语法

SELECT column1, column2, columnN FROM table_name;  查询单列/多列数据

SELECT * FROM table_name;  查询所有列数据

 

CREATE TABLE student(no int,studentname text,age int,classno int);

INSERT INTO student VALUES(1,'张三',14,1);

INSERT INTO student VALUES(2,'吴二',15,1);

INSERT INTO student VALUES(3,'李四',13,2);

INSERT INTO student VALUES(4,'吴三',15,2);

INSERT INTO student VALUES(5,'王二',15,3);

INSERT INTO student VALUES(6,'李三',14,3);

INSERT INTO student VALUES(7,'吴三',15,4);

INSERT INTO student VALUES(8,'张四',14,4);  创建表格

SELECT * FROM student;

SELECT no,studentname FROM student;

 

LIMIT：限制数据条数，OFFSET：偏移；

SELECT * FROM student LIMIT 4;

SELECT * FROM student LIMIT 4 OFFSET 2; 显示4条数据，从第2条开始

 

DISTINCT：显示不同的值（唯一值）

SELECT DISTINCT age FROM student; 只显示student表格中，age字段的唯一值

 

ORDER BY：排序

SELECT * FROM student ORDER BY age;   按照年龄排序

SELECT * FROM student ORDER BY age DESC;   按照年龄排序，降序（字母A-Z为升序）

SELECT studentname,age FROM student ORDER BY age;   筛选字段排序

SELECT studentname,age,classno FROM student ORDER BY age,classno;   多列排序

 

 

2、插入数据

\h INSERT  查看INSERT语句说明

 

基本语法

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

 

INSERT INTO student VALUES(9,'大鹏',18,6);

INSERT INTO student(no,studentname,classno) VALUES(10,'呵呵',8); 这里只插入3列字段的数据

INSERT INTO student VALUES(11,'小哥哥',21,8),(12,'小姐姐',19,8),(13,'老王',35,8); 多行插入数据

 

 

3、删除数据

\h DELETE  查看DELETE语句说明

 

基本语法

DELETE FROM table_name WHERE [condition];

 

DELETE FROM student;  删除所有数据

DELETE FROM student WHERE studentname = '张四';

DELETE FROM student WHERE age > 14;

 

 

4、更新数据

\h UPDATE  查看UPDATE语句说明

 

基本语法

UPDATE table_name

       SET column1 = value1, column2 = value2...., columnN = valueN

       WHERE [condition]; 

 

UPDATE student SET age = 18;  将所有人的age字段更新为18

UPDATE student SET age = 35 WHERE studentname = '老王';  有判断条件

UPDATE student SET classno = 2,studentname = '隔壁老王' WHERE studentname = '老王';  多数据更新