MySQL的基本操作可以包括兩個方面:MySQL常用語句如高頻率使用的增刪改查(CRUD)語句和MySQL高級功能,如存儲過程,觸發器,事務處理等。而這兩個方面又可以細分如下:
1. 表(或數據庫)操作語句
1.1. 查詢表(或數據庫)
獲取所有可用的數據庫:SHOW DATABASES;選擇數據庫:USE customers;用於顯示數據庫服務器的狀態信息:SHOW STATUS;用來顯示授權用戶的安全權限:SHOW GRANTS;用來顯示數據庫服務器或警告信息:SHOW ERRORS 或者 SHOW WARNINGS;用於顯示創建數據庫時的創建語句:SHOW CREATE DATABASE customers;用於顯示創建表時的創建語句:SHOW CREATE TABLE customers;獲取當前所選的數據庫中所有可用的表:SHOW TABLES;獲取表中所有列的信息:SHOW COLUMNS FROM tableName;同時DESCRIBE語句有相同的效果:DESCRIBE tableName;1.2. 新建表(或)數據庫
新建數據庫1.3 刪除表(或數據庫)
刪除數據庫:DROP DATABASE customers;刪除表,使用DROP TABLE子句:DROP TABLE customers。1.4 更新表
更新表結構信息可以使用ALTER TABLE子句,如為表增加一列:ALTER TABLE vendors ADD vend_name CHAR(20);另外經常用於定義外鍵,如: ALTER TABLE customers ADD CONSTRAINT fk_custormer_orders FOREIGN KEY(cust_id) REFERENCES orders (order_cust) 重命名錶,使用RENAME子句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors;更改多個表名,之間用逗號間隔2 表數據操作語句
2.1 查詢表數據
基本查詢語句
根據過濾條件查詢表中的單列或者多列或者全部列的信息SELECT FROM WEHERE:SELECT cust_id,cust_name FROM customers WHERE cust_id=10086;其中過濾條件操作符有:=,<>,!=,,>=,BETWEEN AND,IS NULL;為查詢出的某一列信息去重DISTINCT:SELECT DISTINCT cust_name FROM customers;限制單列查詢結果的行數:SELECT cust_name FROM customers LIMIT 5;LIMIT後跟一個數值,表示從第0行開始取,共取5行數據;如果LIMIT 5,5表示從第5行(數據庫中實際第6行記錄)開始取,共取5行數據。注意:數據是從第0行開始計數的;ORDER BY聯結查詢
內聯結用又稱之為內部聯結,是基於兩個表 之間的的相等測試。如果不加過濾條件,會造成“笛卡爾積”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;同樣可以使用WHERE進行多表聯結查詢,但是更推薦使用INNER JOIN等聯結方式;外部聯結包括左外聯結LEFT JOIN和右外聯結RIGHT JOIN和全連接FULL JOIN。例如查詢每個客戶的訂單數:SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id;LEFT JOIN 會全部返回左表數據,RIGHT JOIN會全部返回右表數據,FULL JOIN會將左右兩個表的數據全部返回;聯結查詢與聚集函數一起使用。如查詢每個客戶的訂單數:SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;組合查詢
多個查詢(SELECT)可以使用UNION將多個查詢結果進行合併成一個結果集返回,UNION必須包含兩個及兩個以上的SELECT查詢,並且每個傳必須包含相同的列、表達式或聚集函數,數據類型不必完全相同,MySQL會進行隱式的類型轉換。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);UNION返回的是去重後的結果,如果不需要去重則可以使用UNION ALL;可以多組合查詢使用ORDER BY進行排序,但是是針對的最終的結果集進行排序,而不是其中單個SELECT查詢進行排序,因此對於組合查詢來說ORDER BY子句只有一個。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id使用函數對數據進行處理
拼接列名:SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName;執行算術表達式計算:SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems;文本處理函數如Upper(),LTrim(),RTrim()等函數。比如使用Upper函數將文本轉換成大寫:SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName;時間和日期處理函數,如Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01';數值處理函數,如Abs(),Cos()等;常用的聚集函數。如AVG(),COUNT(),MAX(),MIN()以及SUM()。SELECT COUNT(*) AS numbers, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products;2.2 插入表數據
向表中插入行數據可以使用INSERT INTO子句,更安全的方式是指定列名。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com');在INSERT INTO子句中能夠省略列名的前提條件是:該列可以允許定義為NULL值或者在定義該列時給出去了默認值;如果插入多行數據可以將多組值用逗號進行分隔即可。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com');將查詢出來的數據插入表中,可以使用INSERT SELECT語句。INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5;其中SELECT中可以帶WHERE過濾條件;INSERT SELECT通常被用於複製表數據2.3 更新表數據
如果要更新表數據的話,使用UPDATE子句:UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001;注意:如果不加WHERE條件指定到某一行的話,會更新表中某一列全部的數據。2.4 刪除表數據
如果從表中刪除數據的話,可以使用DELETE子句。DELETE FROM customers WHERE cust_id = 10086;刪除的數據必定是表中行數據,而不是某一列。因此,與UPDATE子句相比,DELETE子句並不需要指定是哪一列,而僅僅只需要指定具體的表名即可;注意:如果不添加WHERE指定條件的話,會將整個表中所有行數據全部刪除。另外,DELETE只是刪除表中的數據,而不會刪除表結構信息;3. SQL中關鍵字執行順序
在SQL語句中每個關鍵字都會按照順序往下執行,而每一步操作,會生成一個虛擬表,最後產生的虛擬表會作為執行的最終結果返回。下面的是常用的關鍵字的執行順序:
<code>(8)SELECT (9)DISTINCT (1)FROM (3) JOIN (2) ON (4)WHERE (5)GROUP BY (6)WITH{CUBE|ROLLUP} (7)HAVING (10)ORDER BY (11)LIMIT/<code>FROM:對FROM左邊的表和右邊的表計算笛卡爾積,產生虛表VT1;ON:對虛擬表VT1進行ON篩選,只有那些符合條件的行才會被記錄在虛擬表VT2中;JOIN:如果是OUT JOIN,那麼將保留表中(如左表或者右表)未匹配的行作為外部行添加到虛擬表VT2中,從而產生虛擬表VT3;WHERE:對虛擬表VT3進行WHERE條件過濾,只有符合的記錄才會被放入到虛擬表VT4;GROUP BY:根據GROUP BY子句中的列,對虛擬表VT4進行分組操作,產生虛擬表VT5;CUBE|ROLLUP:對虛擬表VT5進行CUBE或者ROLLUP操作,產生虛擬表VT6;HAVING:對虛擬表VT6進行HAVING條件過濾,只有符合的記錄才會被插入到虛擬表VT7中;
4. 索引
MySQL索引的建立對於MySQL的高效運行是很重要的,索引可以大大提高MySQL的檢索速度。索引分單列索引和組合索引。單列索引,即一個索引只包含單個列,而組合索引,即一個索引包含多個列。
4.1 創建索引
創建索引有兩種方式,一種是直接利用CREATE INDEX進行創建,另外一種則是通過修改表結構來進行添加,則是利用ALTER TABLE語句。
使用CREATE INDEX 語法為: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...) 其中對應的語法變量信息如下: [UNIQUE|FULLTEXT|SPATIAL] 其中括號中的這三個關鍵字表示創建的索引類型,它們分別表示唯一索引、全文索引、空間索引4.2 刪除索引
刪除指定表中指定名稱的索引,語法為:
<code>ALTER TABLE table_name DROP INDEX index_name;/<code>
例如刪除名稱為idx_cust_name的索引,其SQL語句為:
<code>ALTER TABLE customers DROP INDEX idx_cust_name;/<code>
4.3 修改索引
在MySQL中並沒有提供修改索引的直接指令,一般情況下,我們需要先刪除掉原索引,再根據需要創建一個同名的索引,從而變相地實現修改索引操作。
<code>--先刪除 ALTER TABLE user DROP INDEX idx_user_username; --再以修改後的內容創建同名索引 CREATE INDEX idx_cust_name ON customers (cust_name(8));/<code>
4.4 查看索引
在MySQL中,要查看某個數據庫表中的索引也非常簡單,只需要使用以下兩個命令中的任意一種即可。
<code>--如果查看索引前,沒有使用user db_name等命令指定具體的數據庫,則必須加上FROM db_name SHOW INDEX FROM table_name [FROM db_name] --如果查看索引前,沒有使用user db_name等命令指定具體的數據庫,則必須加上db_name.前綴 SHOW INDEX FROM [db_name.]table_name/<code>
5. 存儲過程
什麼是存儲過程?存儲過程簡單來說,就是為了複用性或者實現複雜的業務功能,而保存的一條或多條MySQL語句的集合,可將其視為批文件;為什麼使用存儲過程?(1)通過把處理封裝在容易使用的單元中,簡化複雜的操作;(2)由於不要求反覆建立一系列處理步驟,這保證了數據的完整性,如果所有的開發人員和應用程序都使用同一存儲過程,則所使用的代碼都是相同的;(3)簡化對變動的管理。如果表名、列名或業務邏輯有變化,只需要更改存儲過程的代碼,使用它的開發人員甚至不需要知道這些變化,也就是具備了安全性;(4)提高了性能,因為使用存儲過程比單獨使用SQL語句要快;(5)存儲過程可用來編寫功能更靈活的代碼。因此,存儲過程的具備三個特性:創建存儲過程
創建存儲過程。如需要統計用戶訂單總金額,如果該用戶需要交稅的話,訂單總金額則需要再加上稅費 DELIMITER // CREATE PROCEDURE ordertotal( IN custid INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) )COMMENT 'obtain total order price' BEGIN /*declare variable for total*/ DECLARE total DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6; /*get the order total*/ SELECT SUM(item_price*item_quantity) INTO total FROM customers WHERE cust_id = custid; /*is this taxable?*/ IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal; END //有這樣一些細節: 1. 使用CREATE PROCEDURE語句進行創建,()圓括號中為存儲過程的參數,其中參數類型有:1. IN類型,表示傳遞給存儲過程;2. OUT 類型,表示存儲過程返回的結果,在調用存儲過程時需要傳入@開始的變量;3. INOUT類型,表示在存儲過程中可以傳入和傳出; 2. DECLARE用來聲明一個變量,如這裡的total,taxrate。注意MySQL中定義變量時都是變量名在前,數據類型在後。 3. 存儲過程具體邏輯寫在BEGIN END之間; 4. 將值賦給變量使用INTO
執行存儲過程
使用CALL子句執行存儲過程,CALL子句接受存儲過程的名稱以及需要傳遞的參數。 CALL ordertotal(1,TRUE,@total); SELECT @total;如果存儲過程中定義了OUT類型的輸入參數,那麼在執行存儲過程時需要傳入變量,如這裡@total,並且變量都是用@開始的。如果存儲過程中沒有參數的話,就用空圓括號表示即可,CALL ordertotal();
刪除存儲過程
刪除存儲過程,可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal;查詢存儲過程
顯示創建一個存儲過程的語句,可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal;查詢所有存儲過程的狀態,如果在定義存儲過程中使用COMMENT添加註釋,可以查看。同時可以LIKE進行過濾結果。如SHOW PROCEDURE STATUS LIKE '%order%';6. 事務處理
什麼是事務? 事務處理是用來維護數據庫的完整性,它保證成批的MySQL操作要麼完全執行,要麼完全不執行。事務處理是一種機制,用來管理必須成批執行的MySQL操作,它們要麼時作為整體執行或者完全不執行。關鍵概念: 事務:是指一組SQL語句; 回退:是指撤銷指定的SQL語句的過程; 提交:指將未存儲的SQL語句的結果寫入數據庫表中; 保留點:指事務處理中設置的臨時佔位符,可以對它發佈回退;如何創建執行事務? START TRANSACTION; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18); SELECT * FROM customers; SAVEPOINT insertinto; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); ROLLBACK TO insertinto; 執行結果為:插入數據('1',5,18)有效,因為,只會從保留點SAFEPOINT之後開始回退,也就是說保留點SAFEPOINT之前的SQL語句執行的結果仍然有效。 有這樣一些細節: STAET TRANSACTION用來表示下面的SQL語句集為一段事務; SAFEPOINT 用於指定保留點insertinto; ROLLBACK TO表示從指定保留點開始回退,也就是說保留點之前的SQL語句執行結果依然有效。如果僅僅使用ROLLBACK進行回退的話就表示從STAET TRANSACTION之後所有的SQL語句執行效果都會撤銷;MySQL提交(寫或保存)操作是自動進行的,這稱之為隱含提交。但是在事務處理塊中,提交不會隱含進行,要使用COMMIT子句進行提交。如: START TRANSACTION; INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18); INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); COMMIT; 採用COMMIT提交事務,如果兩條SQL語句都執行成功,才會將數據都寫入表中。