第六章 插入，更新和删除数据
第六章 插入，更新和删除数据
第一节：插入数据
1，给表的所有字段插入数据
格式：INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n)；
INSERT INTO t_book VALUES(NULL,'我爱我家',20,'张三',1);
2，给表的指定字段插入数据
格式：INSERT INTO 表名(属性 1，属性 2，...，属性 n) VALUES(值 1，值 2，值 3，...，值 n)；
INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES(NULL,'我爱我家',20,'张三',1); INSERT INTO t_book(bookName,author) VALUES('我爱我家','张三');
3，同时插入多条记录
INSERT INTO 表名 [(属性列表)]
VALUES(取值列表 1)，(取值列表 2)
...，
(取值列表 n)；
INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES (NULL,'我爱我家2',20,'张三',1),(NULL,'我爱我家3',20,'张三',1);
第二节：更新数据
UPDATE 表名
SET 属性名 1=取值 1，属性名 2=取值 2，
...，
属性名 n=取值 n
WHERE 条件表达式；
UPDATE t_book SET bookName='Java编程思想',price=120 WHERE id=1; UPDATE t_book SET bookName='我' WHERE bookName LIKE '%我爱我家%';
第三节：删除数据
DELETE FROM 表名 [WHERE 条件表达式]
DELETE FROM t_book WHERE id=5; DELETE FROM t_book WHERE bookName='我';
维护大学表的时候用的脚本：
UPDATE national_higher_school SET provice_id='110' WHERE id>=1 AND id<=91; UPDATE national_higher_school SET provice_id='120' WHERE id>=92 AND id<=146; UPDATE national_higher_school SET provice_id='130' WHERE id>=147 AND id<=264; UPDATE national_higher_school SET provice_id='140' WHERE id>=265 AND id<=343; UPDATE national_higher_school SET provice_id='150' WHERE id>=344 AND id<=396; UPDATE national_higher_school SET provice_id='210' WHERE id>=397 AND id<=512; UPDATE national_higher_school SET provice_id='220' WHERE id>=513 AND id<=570; UPDATE national_higher_school SET provice_id='230' WHERE id>=571 AND id<=651; UPDATE national_higher_school SET provice_id='310' WHERE id>=652 AND id<=718; UPDATE national_higher_school SET provice_id='320' WHERE id>=719 AND id<=880; UPDATE national_higher_school SET provice_id='330' WHERE id>=881 AND id<=985; UPDATE national_higher_school SET provice_id='340' WHERE id>=986 AND id<=1104; UPDATE national_higher_school SET provice_id='350' WHERE id>=1105 AND id<=1192; UPDATE national_higher_school SET provice_id='360' WHERE id>=1193 AND id<=1289; UPDATE national_higher_school SET provice_id='370' WHERE id>=1290 AND id<=1432; UPDATE national_higher_school SET provice_id='410' WHERE id>=1433 AND id<=1561; UPDATE national_higher_school SET provice_id='420' WHERE id>=1562 AND id<=1684; UPDATE national_higher_school SET provice_id='430' WHERE id>=1685 AND id<=1808; UPDATE national_higher_school SET provice_id='440' WHERE id>=1809 AND id<=1950; UPDATE national_higher_school SET provice_id='450' WHERE id>=1951 AND id<=2021; UPDATE national_higher_school SET provice_id='460' WHERE id>=2022 AND id<=2038; UPDATE national_higher_school SET provice_id='500' WHERE id>=2039 AND id<=2101; UPDATE national_higher_school SET provice_id='510' WHERE id>=2102 AND id<=2210; UPDATE national_higher_school SET provice_id='520' WHERE id>=2211 AND id<=2267; UPDATE national_higher_school SET provice_id='530' WHERE id>=2268 AND id<=2336; UPDATE national_higher_school SET provice_id='540' WHERE id>=2337 AND id<=2342; UPDATE national_higher_school SET provice_id='610' WHERE id>=2343 AND id<=2434; UPDATE national_higher_school SET provice_id='620' WHERE id>=2435 AND id<=2479; UPDATE national_higher_school SET provice_id='630' WHERE id>=2480 AND id<=2491; UPDATE national_higher_school SET provice_id='640' WHERE id>=2492 AND id<=2509; UPDATE national_higher_school SET provice_id='650' WHERE id>=2510 AND id<=2553; SELECT * FROM national_higher_school WHERE id>=1 AND id<=91; SELECT * FROM national_higher_school WHERE id>=1951; SELECT * FROM national_higher_school WHERE id>=397 AND id<=512; SELECT * FROM national_higher_school WHERE id>512; SELECT * FROM national_higher_school WHERE provice_id='110'; SELECT * FROM national_higher_school WHERE provice_id='120';
UPDATE national_adult_school SET provice_id='110' WHERE id>=1 AND id<=24; UPDATE national_adult_school SET provice_id='120' WHERE id>=25 AND id<=38; UPDATE national_adult_school SET provice_id='130' WHERE id>=39 AND id<=44; UPDATE national_adult_school SET provice_id='140' WHERE id>=45 AND id<=56; UPDATE national_adult_school SET provice_id='150' WHERE id>=57 AND id<=58; UPDATE national_adult_school SET provice_id='210' WHERE id>=59 AND id<=78; UPDATE national_adult_school SET provice_id='220' WHERE id>=79 AND id<=92; UPDATE national_adult_school SET provice_id='230' WHERE id>=93 AND id<=113; UPDATE national_adult_school SET provice_id='310' WHERE id>=114 AND id<=128; UPDATE national_adult_school SET provice_id='320' WHERE id>=129 AND id<=136; UPDATE national_adult_school SET provice_id='330' WHERE id>=137 AND id<=145; UPDATE national_adult_school SET provice_id='340' WHERE id>=146 AND id<=151; UPDATE national_adult_school SET provice_id='350' WHERE id>=152 AND id<=154; UPDATE national_adult_school SET provice_id='360' WHERE id>=155 AND id<=162; UPDATE national_adult_school SET provice_id='370' WHERE id>=163 AND id<=173; UPDATE national_adult_school SET provice_id='410' WHERE id>=174 AND id<=185; UPDATE national_adult_school SET provice_id='420' WHERE id>=186 AND id<=199; UPDATE national_adult_school SET provice_id='430' WHERE id>=200 AND id<=211; UPDATE national_adult_school SET provice_id='440' WHERE id>=212 AND id<=226; UPDATE national_adult_school SET provice_id='450' WHERE id>=227 AND id<=232; UPDATE national_adult_school SET provice_id='460' WHERE id>=233 AND id<=233; UPDATE national_adult_school SET provice_id='500' WHERE id>=234 AND id<=237; UPDATE national_adult_school SET provice_id='510' WHERE id>=238 AND id<=254; UPDATE national_adult_school SET provice_id='520' WHERE id>=255 AND id<=258; UPDATE national_adult_school SET provice_id='530' WHERE id>=259 AND id<=260; UPDATE national_adult_school SET provice_id='610' WHERE id>=261 AND id<=276; UPDATE national_adult_school SET provice_id='620' WHERE id>=277 AND id<=282; UPDATE national_adult_school SET provice_id='630' WHERE id>=283 AND id<=284; UPDATE national_adult_school SET provice_id='640' WHERE id>=285 AND id<=285; UPDATE national_adult_school SET provice_id='650' WHERE id>=286 AND id<=292;
