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")

7、使用背景：先生成两张子表，再将两张子表拼接。

SELECT * FROM 
((SELECT `bss`.*,`ad`.deptName FROM site `bss` LEFT JOIN `dept` `ad` ON `bss`.pdid = `ad`.DID) AS `a`) LEFT JOIN ((SELECT
`bssd`.sid
FROM
( `site_dept` `bssd` LEFT JOIN `inst` `atti` ON ( ( `bssd`.`did` = `atti`.`IID` ) ) )
GROUP BY
`bssd`.sid)
AS `b`) ON `a`.id = `b`.sid;