MySQL 学习使用记录

1、使用背景：两张一样的表，从其中一张复制到另外一张。

INSERT INTO space_picture_site SELECT * FROM space_picture_building WHERE bid = 'overall'

2、使用背景：删除一张表的指定记录

DELETE from space_picture WHERE bid = 'overall'

3、使用背景：有表a、b，删除b表中不包含a表id的记录

DELETE FROM space_site1 WHERE id NOT IN (SELECT SID FROM space_site2)

4、使用背景：查询表中一个字段中存在相等的记录

select * from space_site_info where name in (select name from space_site_info group by name having count(name) > 1)

5、使用背景：选表中一个id 的一条记录，并修改该记录的值。

UPDATE bimap_space_picture_site SET typical = 1 WHERE id IN ( SELECT a.id FROM ( SELECT id FROM bimap_space_picture_site GROUP BY sid) AS a)

6、使用背景：将一列的某一个值替换为另外一个值

UPDATE bimap_space_picture_site SET thumburl = REPLACE(thumburl ,"sitePicture","thumPicture")