博主依然不想打字，又向你仍來了一堆代碼。。。
13（續）
在SELECT中用COUNT（）以及聯合
mysql> SELECT customers.cust_id,COUNT(orders.order_num) AS num_ord
-> FROM customers INNER JOIN orders
-> ON customers.cust_id=orders.cust_id
-> GROUP BY cust_id;
+------------+---------+
| cust_id | num_ord |
+------------+---------+
| 1000000001 | 2 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+---------+
4 rows in set (0.00 sec)
14、組合查詢
UNION，UNION ALL（顯示重復行）；
多條SELECT合並為一個查詢輸出；
必須有相同的列數，順序可以不一樣但數據類型要可以轉換的；
UNION ALL顯示重復行，除此以外的使用，都可以用WHERE來替換；
ORDER BY放在最後一句，只需要一句；
不過事實證明，順序還是會有影響的 。。。
mysql> SELECT cust_name,cust_contact,cust_emali
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI');
ERROR 1054 (42S22): Unknown column 'cust_emali' in 'field list'
mysql> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI');
+---------------+--------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
+---------------+--------------+-----------------------+
3 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_email,cust_contact
-> FROM customers
-> WHERE cust_name='Fun4All';
+-----------+-----------------------+--------------------+
| cust_name | cust_email | cust_contact |
+-----------+-----------------------+--------------------+
| Fun4All | [email protected] | Jim Jones |
| Fun4All | [email protected] | Denise L. Stephens |
+-----------+-----------------------+--------------------+
2 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION
-> SELECT cust_name,cust_email,cust_contact
-> FROM customers
-> WHERE cust_name='Fun4All';
+---------------+-----------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+-----------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Fun4All | [email protected] | Jim Jones |
| Fun4All | [email protected] | Denise L. Stephens |
+---------------+-----------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION
-> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_name='Fun4All';
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Fun4All | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION ALL
-> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_name='Fun4All';
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Village Toys | John Smith | [email protected] |
| Fun4All | Jim Jones | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Fun4All | Jim Jones | [email protected] |
| Fun4All | Denise L. Stephens | [email protected] |
+---------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_state IN ('IL','IN','MI')
-> UNION
-> SELECT cust_name,cust_contact,cust_email
-> FROM customers
-> WHERE cust_name='Fun4All'
-> ORDER BY cust_name;
+---------------+--------------------+-----------------------+
| cust_name | cust_contact | cust_email |
+---------------+--------------------+-----------------------+
| Fun4All | Jim Jones | [email protected] |
| Fun4All | Denise L. Stephens | [email protected] |
| The Toy Store | Kim Howard | NULL |
| Village Toys | John Smith | [email protected] |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)
15、插入數據
INSERT,INSERT INTO,INSERT SELECT,SELECT INTO
插入完整的行，插入行的一部分，插入某些查詢結果；
用INSERT INTO替代INSERT，提高可移植性；
注意格式寫法；
最好寫上列名；
INSERT一般只能插入一條，INSERT SELECT是個例外；
SELECT INTO實現復制（MySQL和Oracle的有所不同）
mysql> CREATE TABLE custNew(
-> cust_id INT PRIMARY KEY,
-> cust_contact VARCHAR(32),
-> cust_email VARCHAR(32),
-> cust_name VARCHAR(32),
-> cust_address VARCHAR(64),
-> cust_city VARCHAR(16),
-> cust_state VARCHAR(16),
-> cust_zip SMALLINT,
-> cust_country VARCHAR(16)
-> );
Query OK, 0 rows affected (0.21 sec)
mysql> INSERT INTO custNew(
-> cust_id,
-> cust_email,
-> cust_name,
-> cust_zip,
-> cust_country,
-> cust_city)
-> VALUES(
-> 1021,
-> '[email protected]',
-> 'Andy Liang',
-> 610000,
-> 'China',
-> 'Chengdu');
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> INSERT INTO customers(
-> cust_id,
-> cust_email,
-> cust_name,
-> cust_zip,
-> cust_country,
-> cust_city)
-> SELECT cust_id,
-> cust_email,
-> cust_name,
-> cust_zip,
-> cust_country,
-> cust_city
-> FROM custNew;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | [email protected] |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | [email protected] |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | [email protected] |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
| 1021 | Andy Liang | NULL | Chengdu | NULL | 32767 | China | NULL | [email protected] |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
6 rows in set (0.00 sec)
mysql> CREATE TABLE custcopy(
-> cust_id VARCHAR(16),
-> cust_contact VARCHAR(32),
-> cust_email VARCHAR(32),
-> cust_name VARCHAR(32),
-> cust_address VARCHAR(64),
-> cust_city VARCHAR(16),
-> cust_state VARCHAR(16),
-> cust_zip VARCHAR(16),
-> cust_country VARCHAR(16)
-> );
Query OK, 0 rows affected (0.26 sec)
mysql> SELECT *
-> INTO custcopy
-> FROM customers;
ERROR 1327 (42000): Undeclared variable: custcopy
mysql> CREATE TABLE custcopy2 AS
-> SELECT *
-> FROM customers;
Query OK, 6 rows affected (0.41 sec)
Records: 6 Duplicates: 0 Warnings: 0
16、更新和刪除數據
UPADTE,DELETE
容易使用，但請小心使用；（沒有UNDO！！！）
UPDATE為null=DELETE；
一些習慣：一定帶WHERE ,避免對整個表有影響；
對數據做更改時，請先SELECT查詢一下，看對象是否沒錯；
確保每個表有主鍵
mysql> SELECT cust_id,cust_address,cust_state
-> FROM customers
-> WHERE cust_id='1021';
+---------+--------------+------------+
| cust_id | cust_address | cust_state |
+---------+--------------+------------+
| 1021 | NULL | NULL |
+---------+--------------+------------+
1 row in set (0.00 sec)
mysql> UPDATE customers
-> SET cust_address='2006 Xiyuan Ave.',
-> cust_state='SC'
-> WHERE cust_id='1021';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT cust_id,cust_address,cust_state
-> FROM customers
-> WHERE cust_id='1021';
+---------+------------------+------------+
| cust_id | cust_address | cust_state |
+---------+------------------+------------+
| 1021 | 2006 Xiyuan Ave. | SC |
+---------+------------------+------------+
1 row in set (0.00 sec)
mysql> UPDATE customers
-> SET cust_email=NULL
-> WHERE cust_id='1021';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> DELETE FROM customers
-> WHERE cust_id='1021';
Query OK, 1 row affected (0.08 sec)
17、創建和操作表
CREATE,DROP,ALTER,RENAME,DEFAULT
更新表，和更新數據一樣小心，做好備份；
重命名不太一樣，mysql是RENAME
mysql> ALTER TABLE custcopy2
-> ADD cust_gender VARCHAR(8);
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2016-08-10 |
+----------------+
1 row in set (0.16 sec)
mysql> ALTER TABLE custcopy2
-> ADD add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DROP TABLE custcopy;
Query OK, 0 rows affected (0.13 sec)
mysql> RENAME TABLE custcopy2 TO custcopy;
Query OK, 0 rows affected (0.10 sec)
18、使用視圖
VIEW
view實際上就是個虛擬的表，只包含使用時動態檢索數據的查詢；
使用view，有利於代碼重用，保護數據，簡化操作；
視圖名也要唯一；可以嵌套，一般禁止ORDER BY；
用於簡化復雜聯結，格式化輸出，過濾不想要的數據等；
WHERE語句會自動合並
mysql> CREATE VIEW shit AS
-> SELECT cust_name,cust_id,cust_contact,cust_address
-> FROM customers
-> ;
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT cust_name,cust_address
-> FROM shit
-> WHERE cust_id='1021';
Empty set (0.00 sec)
mysql> SELECT * FROM customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | [email protected] |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | [email protected] |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | [email protected] |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> SELECT cust_name,cust_address
-> FROM shit
-> WHERE cust_id='1000000001';
+--------------+----------------+
| cust_name | cust_address |
+--------------+----------------+
| Village Toys | 200 Maple Lane |
+--------------+----------------+
1 row in set (0.00 sec)
mysql> CREATE VIEW hasEmail AS
-> SELECT cust_name,cust_id,cust_email
-> FROM customers
-> WHERE cust_email IS NOT NULL;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT *
-> FROM hasEmail
-> WHERE cust_name='Fun4All';
+-----------+------------+-----------------------+
| cust_name | cust_id | cust_email |
+-----------+------------+-----------------------+
| Fun4All | 1000000003 | [email protected] |
| Fun4All | 1000000004 | [email protected] |
+-----------+------------+-----------------------+
2 rows in set (0.00 sec)
19、存儲過程
PROCEDURE,DELIMITER,CALL
以下引用自百度：存儲過程（Stored Procedure）是在大型數據庫系統中，一組為了完成特定功能的SQL 語句集，存儲在數據庫中，經過第一次編譯後再次調用不需要再次編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象，任何一個設計良好的數據庫應用程序都應該用到存儲過程。
有點好玩又有點繁瑣的。。。
mysql> DELIMITER &&
mysql> CREATE PROCEDURE showAll()
-> BEGIN
-> SELECT * FROM customers;
-> END &&
Query OK, 0 rows affected (0.04 sec)
mysql> CALL showAll();
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | [email protected] |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | [email protected] |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | [email protected] |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.08 sec)
mysql> DELIMITER $
mysql> CREATE PROCEDURE getID(
-> IN id VARCHAR(32))
-> BEGIN
-> SELECT *
-> FROM customers
-> WHERE cust_id=id;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> CALL getID('1000000001')$
+------------+--------------+----------------+-----------+------------+----------+--------------+--------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+--------------+----------------+-----------+------------+----------+--------------+--------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | [email protected] |
+------------+--------------+----------------+-----------+------------+----------+--------------+--------------+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
注意輸入參數那裡的變化。
20、事務管理
TRANSACTION,COMMIT,ROLLBACK,SAVEPOINT
用來維護數據庫的完整性，保證成批的SQL操作要麼完全執行，要麼完全不執行；
保留點越多越好，更好的是形成習慣~
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVEPOINT begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customers(cust_name,cust_country)
-> VALUES('Andy Liang','China');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> ROLLBACK TO begin;
Query OK, 0 rows affected (0.04 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
MySQL是一步步來的，就是慢慢監督的一樣。
21、使用游標
CURSOR
略。。。。（老子做web不想用游標，你來打我啊
22、約束，索引，觸發器
約束:
PRIMARY KEY,UNIQUE,REFERENCES,ALTER,CONSTRAINT,CHECK
主鍵：值互異，NOT NULL，不修改/更新，不重用，每個表僅能有一個主鍵，可以用來定義外鍵（舉例：id
唯一：值互異，可以有NULL，可修改/更新，可重用，可有多個唯一鍵，不能用來定義外鍵
外鍵：兩個表，確定子表某一列的合法性
檢查約束：CHECK，靈活約束
mysql> CREATE TABLE test(
-> id SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> socialid VARCHAR(64) UNIQUE,
-> cust_id VARCHAR(16) NOT NULL REFERENCES customers(cust_id),
-> quantity SMALLINT CHECK (quantity>0),
-> gender VARCHAR(8) CHECK (gender LIKE '[MF]')
-> );
Query OK, 0 rows affected (0.20 sec)
另外，用ALTER語句再對表進行更改操作
ALTER TABLE test ADD CONSTRAINT PRIMARY KEY (cust_id); ALTER TABLE test ADD CONSTRAINT UNIQUE KEY (id); ALTER TABLE test ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES customers (cust_id); ALTER TABLE test ADD CONSTRAINT CHECK (quantity<1000);
索引：
INDEX
搜索某些列的效率提高；
請動態的創建索引——數據庫在不斷更新，索引效率在變化；索引效率提升的代價是插入等操作性能的犧牲；大量空間的使用；
mysql> CREATE INDEX prod_name_id
-> ON PRODUCTS (prod_name);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
觸發器：
TRIGGER
在特定的數據庫活動發生時自動執行；
一般用途：保證數據一致（全大寫等），聯動（log的記錄）等；
約束要比觸發器快，盡量用約束；
完！！（對，這裡又滅有代碼了
