MySQL學習筆記之命令總結

   用文字方式將資料裝入資料表中（eg:d:/mysql.txt）
   load data local infile "D:/mysql.txt" into table mytable

   匯入sql檔案命令（eg:d:/mysql.sql）
   use database;
   source d:/mysql.sql

   清空表
   delete from mytable
----------------------------------------------------

一：alter命令
    1：刪除列
    ALTER TABLE 【表名字】 DROP 【列名稱】
    2：增加列
    ALTER TABLE 【表名字】 ADD 【列名稱】 INT NOT NULL  COMMENT '註釋說明'
    3：修改列的型別資訊
    ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱（這裡可以用和原來列同名即可）】 BIGINT NOT NULL  COMMENT '註釋說明'
    4：重新命名列
    ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱】 BIGINT NOT NULL  COMMENT '註釋說明'
    5：重新命名錶
    ALTER TABLE 【表名字】 RENAME 【表新名字】
    6：刪除表中主鍵
    Alter TABLE 【表名字】 drop primary key
    7：新增主鍵
    ALTER TABLE 【表名字】 ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
    8：新增索引
    ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
    9: 新增唯一限制條件索引
    ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
    10: 刪除索引
    alter table tablename drop index emp_name;

二：update語句理解

   UPDATE的功能是更新表中的資料。這的語法和INSERT的第二種用法相似。必須提供表名以及SET表示式，在後面可以加WHERE以限制更新的記錄範圍。
   UPDATE table_anem SET column_name1 = value1, column_name2 = value2, ...
   WHERE ... ;
   
   如下面的語句將users表中id等於123的記錄的age改為24
   UPDATE users SET age = 24 WHERE id = 123;
   同樣，可以使用UPDATE更新多個欄位的值 UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
   上面的UPDATE語句通過WHERE指定一個條件，否則，UPDATE將更新表中的所有記錄的值。

   在使用UPDATE更新記錄時，如果被更新的欄位的型別和所賦的值不匹配時，MySQL將這個值轉換為相應型別的值。如果這個欄位是數值型別，而且所賦 值超過了這個資料型別的最大範圍，那麼MySQL就將這個值轉換為這個範圍最大或最小值。如果字串太長，MySQL就將多餘的字串截去。如果設定非空 欄位為空，那麼將這個欄位設定為它們的預設值，數字的預設值是0，字串的預設值是空串（不是null，是""）。
   
   有兩種情況UPDATE不會對影響表中的資料。
   1. 當WHERE中的條件在表中沒有記錄和它匹配時。
   2. 當我們將同樣的值賦給某個欄位時，如將欄位abc賦為'123'，而abc的原值就是'123'。
   
   和INSERT、REPLACE一樣，UPDATE也返回所更新的記錄數。但這些記錄數並不包括滿足WHERE條件的，但卻未被更新的記錄。如下同的UPDATE語句就未更新任何記錄。
   UPDATE users SET age = 30 WHERE id = 12;
   Query OK, 0 rows affected (0.00 sec)
   
   需要注意的時，如果一個欄位的型別是TIMESTAMP，那麼這個欄位在其它欄位更新時自動更新。
   在有些時候我們需要得到UPDATE所選擇的行數，而不是被更新的行數。我們可以通過一些API來達到這個目的。如MySQL提供的C API提供了一個選項可以得到你想要的記錄數。而MySQL的JDBC驅動得到的預設記錄數也是匹配的記錄數。
   
   UPDATE和REPLACE基本類似，但是它們之間有兩點不同。
   1. UPDATE在沒有匹配記錄時什麼都不做，而REPLACE在有重複記錄時更新，在沒有重複記錄時插入。
   2. UPDATE可以選擇性地更新記錄的一部分欄位。而REPLACE在發現有重複記錄時就將這條記錄徹底刪除，再插入新的記錄。也就是說，將所有的欄位都更新了。

三：Insert語句的理解
 
   看到這個標題也許大家會問，這有什麼好說的，呼叫多次INSERT語句不就可以插入多條記錄了嗎！但使用這種方法要增加伺服器的負荷，因為，執行每一次 SQL伺服器都要同樣對SQL進行分析、優化等操作。幸好MySQL提供了另一種解決方案，就是使用一條INSERT語句來插入多條記錄。這並不是標準的 SQL語法，因此只能在MySQL中使用。
   INSERT INTO users(name, age)
   VALUES('姚明', 25), ('比爾.蓋茨', 50), ('火星人', 600);
   上面的INSERT 語句向users表中連續插入了3條記錄。值得注意的是，上面的INSERT語句中的VALUES後必須每一條記錄的值放到一對(…)中，中間使用","分割。假設有一個表table1
   CREATE TABLE table1(n INT)；
   如果要向table1中插入5條記錄，下面寫法是錯誤的：
   INSERT INTO table1 (i) VALUES(1,2,3,4,5);
   MySQL將會丟擲下面的錯誤
   ERROR 1136: Column count doesn't match value count at row 1
   而正確的寫法應該是這樣：
   INSERT INTO t able1(i) VALUES(1),(2),(3),(4),(5);
   當然，這種寫法也可以省略列名，這樣每一對括號裡的值的數目必須一致，而且這個數目必須和列數一致。如：
   INSERT INTO t able1 VALUES(1),(2),(3),(4),(5);

四：replace語句

   我們在使用資料庫時可能會經常遇到這種情況。如果一個表在一個欄位上建立了唯一索引，當我們再向這個表中使用已經存在的鍵值插入一條記錄，那將會丟擲一 個主鍵衝突的錯誤。當然，我們可能想用新記錄的值來覆蓋原來的記錄值。如果使用傳統的做法，必須先使用DELETE語句刪除原先的記錄，然後再使用 INSERT插入新的記錄。而在MySQL中為我們提供了一種新的解決方案，這就是REPLACE語句。使用REPLACE插入一條記錄時，如果不重 復，REPLACE就和INSERT的功能一樣，如果有重複記錄，REPLACE就使用新記錄的值來替換原來的記錄值。

   使用REPLACE的最大好處就是可以將DELETE和INSERT合二為一，形成一個原子操作。這樣就可以不必考慮在同時使用DELETE和INSERT時新增事務等複雜操作了。
   在使用REPLACE時，表中必須有唯一索引，而且這個索引所在的欄位不能允許空值，否則REPLACE就和INSERT完全一樣的。

   在執行REPLACE後，系統返回了所影響的行數，如果返回1，說明在表中並沒有重複的記錄，如果返回2，說明有一條重複記錄，系統自動先呼叫了 DELETE刪除這條記錄，然後再記錄用INSERT來插入這條記錄。如果返回的值大於2，那說明有多個唯一索引，有多條記錄被刪除和插入。

   REPLACE的語法和INSERT非常的相似，如下面的REPLACE語句是插入或更新一條記錄。
   REPLACE INTO users (id,name,age) VALUES(123, '趙本山', 50);
   
   插入多條記錄：
   REPLACE INTO users(id, name, age)
   VALUES(123, '趙本山', 50), (134,'Mary',15);
   REPLACE也可以使用SET語句
   REPLACE INTO users SET id = 123, name = '趙本山', age = 50;
   
   上面曾提到REPLACE可能影響3條以上的記錄，這是因為在表中有超過一個的唯一索引。在這種情況下，REPLACE將考慮每一個唯一索引，並對每一 個索引對應的重複記錄都刪除，然後插入這條新記錄。假設有一個table1表，有3個欄位a, b, c。它們都有一個唯一索引。
   CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
   假設table1中已經有了3條記錄
   a b c
   1 1 1
   2 2 2
   3 3 3
   下面我們使用REPLACE語句向table1中插入一條記錄。
   REPLACE INTO table1(a, b, c) VALUES(1,2,3);
   返回的結果如下
   Query OK, 4 rows affected (0.00 sec)
   在table1中的記錄如下
   a b c
   1 2 3
   我們可以看到，REPLACE將原先的3條記錄都刪除了，然後將（1, 2, 3）插入。

五：DELETE和TRUNCATE TABLE
   
   在MySQL中有兩種方法可以刪除資料，一種是DELETE語句，另一種是TRUNCATE TABLE語句。DELETE語句可以通過WHERE對要刪除的記錄進行選擇。而使用TRUNCATE TABLE將刪除表中的所有記錄。因此，DELETE語句更靈活。
   
   如果要清空表中的所有記錄，可以使用下面的兩種方法：
   DELETE FROM table1
   TRUNCATE TABLE table1
   其中第二條記錄中的TABLE是可選的。
   
   如果要刪除表中的部分記錄，只能使用DELETE語句。
   DELETE FROM table1 WHERE ...;
   如果DELETE不加WHERE子句，那麼它和TRUNCATE TABLE是一樣的，但它們有一點不同，那就是DELETE可以返回被刪除的記錄數，而TRUNCATE TABLE返回的是0。

   如果一個表中有自增欄位，使用TRUNCATE TABLE和沒有WHERE子句的DELETE刪除所有記錄後，這個自增欄位將起始值恢復成1.如果你不想這樣做的話，可以在DELETE語句中加上永真的WHERE，如WHERE 1或WHERE true。
   DELETE FROM table1 WHERE 1;

   上面的語句在執行時將掃描每一條記錄。但它並不比較，因為這個WHERE條件永遠為true。這樣做雖然可以保持自增的最大值，但由於它是掃描了所有的記錄，因此，它的執行成本要比沒有WHERE子句的DELETE大得多。
   DELETE和TRUNCATE TABLE的最大區別是DELETE可以通過WHERE語句選擇要刪除的記錄。但執行得速度不快。而且還可以返回被刪除的記錄數。而TRUNCATE TABLE無法刪除指定的記錄，而且不能返回被刪除的記錄。但它執行得非常快。

   和標準的SQL語句不同，DELETE支援ORDER BY和LIMIT子句，通過這兩個子句，我們可以更好地控制要刪除的記錄。如當我們只想刪除WHERE子句過濾出來的記錄的一部分，可以使用LIMIB， 如果要刪除後幾條記錄，可以通過ORDER BY和LIMIT配合使用。假設我們要刪除users表中name等於"Mike"的前6條記錄。可以使用如下的DELETE語句：
DELETE FROM users WHERE name = 'Mike' LIMIT 6;
   一般MySQL並不確定刪除的這6條記錄是哪6條，為了更保險，我們可以使用ORDER BY對記錄進行排序。
   DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6