查询语句--查询出只包含特定字符的数据集

查询 calculations 表。

查询要求为composition_id数据字符只在Al，Fe，Cr，O，H，Zr，Si，nb，U 九个元素中（字符若不在这九个中则不选，可以选择其中几个）

 

 

SELECT * FROM calculations WHERE composition_id REGEXP '^((Al|Fe|Cr|O|H|Zr|Si|nb|U)[0-9]+ *)+$'

插入新表

INSERT INTO calculations2 (
id,
configuration,
label,
entry_id,
path,
composition_id,
natoms,
input_id,
settings,
output_id,
energy,
energy_pa,
magmom,
magmom_pa,
dos_id,
band_gap,
irreducible_kpoints,
attempt,
nsteps,
converged,
runtime 
) SELECT
b.id,
b.configuration,
b.label,
b.entry_id,
b.path,
b.composition_id,
b.natoms,
b.input_id,
b.settings,
b.output_id,
b.energy,
b.energy_pa,
b.magmom,
b.magmom_pa,
b.dos_id,
b.band_gap,
b.irreducible_kpoints,
b.attempt,
b.nsteps,
b.converged,
b.runtime 
FROM
	(
SELECT
	* 
FROM
	( SELECT * FROM calculations WHERE composition_id REGEXP '^((Al|Fe|Cr|O|H|Zr|Si|nb|U)[0-9]+ *)+$' ) AS b
	)as b