表:student
1.增加
1)创建表student
CREATE TABLE student ( "id" int4 NOT NULL, "name" varchar(20) )
2)增加主键
ALTER TABLE student ADD CONSTRAINT pk_student_id PRIMARY KEY(id);
3)插入字段
字段名:name 类型:varchar(255)
alter table student add name varchar(255);
4)插入数据
在student表中插入id为4,name为ccc的数据
INSERT INTO student(id,name) VALUES(4,'ccc');
2.删除
1)删除student表中id为1的字段
delete from student where id = 1;
2)删除表中所有数据
delete from student;
3)删除表student
drop table student;
3.更新
1)修改字段
将student表中id为4的数据中name修改为ccc1
UPDATE student SET NAME = 'ccc1' where id = 4;
4.查询
1)查询字段信息
select * from student;
2)查询某个字段
只返回name列
select name from student;
3)in的查询
返回id=1或id=2的数据
select * from student where id in(1,2);
返回id不为1,2的数据
select * from student where id not in(1,2);
4)查询排序
降序
select * from student order by id desc
结果如下图:
升序
select * from student order by id ASC
结果如下图:
5) 去除重复数据查询
select DISTINCT name from student
原查询: 变为:
或者:
可在后面加上order by
select name from student GROUP BY name
查询结果:
6) 查询个数
可根据需要自己添加条件where等
select count(*) from student