sql常用命令

》》更新1

Update table1 t1 set colum_name=t2.colum_name form table2 t2

where t1.colum_name=t2.colum_name

》》更新2

update table1

set geom=(select geom from table2 where table2.guid=table1.guid)

》》相交

SELECT T1.*

FROM table1 T1

INNER JOIN table2 T2 ON ST_INTERSECTS(T1.GEOM,T2.GEOM)

》》查询

select * from table1 a left join table2 b on a.id=b.id

》》查图形表坐标系

select * from geometry_columns;

》》备份图形表

create table table1  as select * from  table2;

》》图形表坐标系丢失

SELECT DropGeometryColumn ('SCHEMA','table1','geom');

SELECT AddGeometryColumn ('table1', 'geom', 4528, 'MULTIPOLYGON', 2)

update table1 aa set geom=(select geom from table2 b where gid =aa.gid )

SELECT DropGeometryColumn ('gyl','table1','geom');

SELECT AddGeometryColumn ('table1', 'geom', 4528, 'MULTIPOLYGON', 2)

update table1 aa set geom=(select geom from table2 b where gid =aa.gid )

》》还原表

insert into  t1   select * form  t2

》》guid赋值

update table1 set guid=uuid_generate_v4()

》》删除

delete FROM "table1" where guid not in (SELECT guid from table2)

》》更新

update table1 aa set guid=(SELECT guid from table2 bb where bb.number=aa.guid)

》》填充guid

update table1 set guid=uuid_generate_v4()

update table1 set guid = number

》》limit

--LIMIT n：从第0+1(m=0)条开始，取n条数据，是LIMIT 0,n的缩写

SELECT id

FROM table1

LIMIT 2

--LIMIT m,n:从第m+1条开始，取n条数据

SELECT id

FROM table1

LIMIT 0,2

--LIMIT n OFFSET m：从第m+1条开始，取n条数据

SELECT id

FROM table1

LIMIT 2 OFFSET 0

》》where关键字无法与合计函数一起使用

SQL语句执行顺序：

FROM - ON - JOIN - WHERE - GROUP BY - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT