MySQL優化之-增刪改具體分析(圖)
#插入
將多行查詢結果插入到表中
語法
INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)
table_name1指定待插入資料的表;column_list1指定待插入表中要插入資料的哪些列;table_name2指定插入資料是從
哪個表中查詢出來的;column_list2指定資料來源表的查詢列,該列表必須和column_list1列表中的字段數相同,資料類型相同;
condition指定SELECT語句的查詢條件
從person_old表中查詢所有的記錄,並將其插入到person表
CREATE TABLE person ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, NAME CHAR(40) NOT NULL DEFAULT '', age INT NOT NULL DEFAULT 0, info CHAR(50) NULL, PRIMARY KEY (id) ) CREATE TABLE person_old ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, NAME CHAR(40) NOT NULL DEFAULT '', age INT NOT NULL DEFAULT 0, info CHAR(50) NULL, PRIMARY KEY (id) ) INSERT INTO person_old VALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police') SELECT * FROM person_old
可以看到,插入記錄成功,person_old表現在有兩筆記錄。接下來將person_oldperson_old表中的所有記錄插入到person表
INSERT INTO person(id,NAME,age,info) SELECT id,NAME,age,info FROM person_old; SELECT * FROM person
#可以看到資料轉移成功,這裡的id欄位為自增的主鍵,在插入時要保證該字段值的唯一性,如果不能確定,可以插入的時候忽略該字段,
只插入其他字段的值
如果再執行一次就會出錯
MYSQL和SQLSERVER的差別:
## 區別一
#當要匯入的資料中有重複值的時候,MYSQL會有三種方案方案一:使用ignore 關鍵字方案二:使用replace into
方案三:ON DUPLICATE KEY UPDATE
TRUNCATE TABLE person TRUNCATE TABLE persona_old INSERT INTO person_old VALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police') ##注意下面这条insert语句是没有ignore关键字的 INSERT INTO person(id,NAME,age,info) SELECT id,NAME,age,info FROM person_old; INSERT INTO person_old VALUES (13,'kay',26,'student') ##注意下面这条insert语句是有ignore关键字的 INSERT IGNORE INTO person(id,NAME,age,info) SELECT id,NAME,age,info FROM person_old;
SQLSERVER
在SQLSERVER這邊,如果要忽略重複鍵,就需要在建表的時候指定WITH (IGNORE_DUP_KEY=ON) ON [# PRIMARY]
這樣在插入重複值的時候,SQLSERVER第一次會保留值,第二次發現有重複值的時候,SQLSERVER就會忽略掉區別二
#插入自增列時的差異
##SQLSERVER需要使用SETIDENTITY_INSERT 表名ON 才能將自增欄位的值插入表中,若不加 SET IDENTITY_INSERT 表名 ON
則在插入資料到表中時,無法指定自增欄位的值,則id欄位不能指定值,SQLSERVER會自動幫你自動增加一INSERTINTO person(NAME,age,info) VALUES ('feicy',33,'student')
##你可以將id欄位的值指定為NULL,MYSQL會自動幫你增一
#
INSERTINTO person(id,NAME,age,info) VALUES (NULL,'feicy',33,'student')
也可以指定值
INSERT IGNORE INTO person(id,NAME,age,info) VALUES (16,'tom',88,'student')
也可以不寫id的值,MYSQL會自動幫你增一
INSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb')
你可以指定id字段的值也可以不指定,指定的時候只要當前id字段列沒有你正在插入的那個值就可以,即沒有重複值就可以
自由度非常大,而且無須指定SET IDENTITY_INSERT 表名 ON
選項#區別三
##唯一索引的NULL值重複問題 MYSQL
在MYSQL中UNIQUE 索引將會對null欄位失效
insert into test(a) values(null) insert into test(a) values(null)
上面的插入語句是可以重複插入的(聯合唯一索引也是一樣)
SQLSERVER
SQLSERVER則不行
CREATE TABLE person ( id INT NOT NULL IDENTITY(1,1), NAME CHAR(40) NULL DEFAULT '', age INT NOT NULL DEFAULT 0, info CHAR(50) NULL, PRIMARY KEY (id) ) CREATE UNIQUE INDEX IX_person_unique ON [dbo].[person](name) INSERT INTO [dbo].[person] ( [NAME], [age], [info] ) VALUES ( NULL, -- NAME - char(40) 1, -- age - int 'aa' -- info - char(50) ), ( NULL, -- NAME - char(40) 2, -- age - int 'bb' -- info - char(50) )
消息 2601,级别 14,状态 1,第 1 行 不能在具有唯一索引“IX_person_unique”的对象“dbo.person”中插入重复键的行。重复键值为 (<NULL>)。 语句已终止。
##更新比較簡單,就不多說了UPDATE person SET info ='police' WHERE id BETWEEN 14 AND 17
SELECT * FROM person
删除
删除person表中一定范围的数据
DELETE FROM person WHERE id BETWEEN 14 AND 17 SELECT * FROM person
如果要删除表的所有记录可以使用下面的两种方法
##方法一 DELETE FROM person ##方法二 TRUNCATE TABLE person
跟SQLSERVER一样,TRUNCATE TABLE会比DELETE FROM TABLE 快
MYISAM引擎下的测试结果,30行记录
跟SQLSERVER一样,执行完TRUNCATE TABLE后,自增字段重新从一开始。
################################ INSERT IGNORE INTO person(id,NAME,age,info) SELECT id,NAME,age,info FROM person_old; SELECT * FROM person TRUNCATE TABLE person INSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb') SELECT * FROM person
当你刚刚truncate了表之后执行下面语句就会看到重新从一开始
SHOW TABLE STATUS LIKE 'person'
以上是MySQL優化之-增刪改具體分析(圖)的詳細內容。更多資訊請關注PHP中文網其他相關文章!