博主依然不想打字，又向你仍来了一堆代码。。。
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 | sales@villagetoys.com | | Fun4All | Jim Jones | jjones@fun4all.com | | 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 | jjones@fun4all.com | Jim Jones | | Fun4All | dstephens@fun4all.com | 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 | sales@villagetoys.com | | Fun4All | Jim Jones | jjones@fun4all.com | | The Toy Store | Kim Howard | NULL | | Fun4All | jjones@fun4all.com | Jim Jones | | Fun4All | dstephens@fun4all.com | 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 | sales@villagetoys.com | | Fun4All | Jim Jones | jjones@fun4all.com | | The Toy Store | Kim Howard | NULL | | Fun4All | Denise L. Stephens | dstephens@fun4all.com | +---------------+--------------------+-----------------------+ 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 | sales@villagetoys.com | | Fun4All | Jim Jones | jjones@fun4all.com | | The Toy Store | Kim Howard | NULL | | Fun4All | Jim Jones | jjones@fun4all.com | | Fun4All | Denise L. Stephens | dstephens@fun4all.com | +---------------+--------------------+-----------------------+ 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 | jjones@fun4all.com | | Fun4All | Denise L. Stephens | dstephens@fun4all.com | | The Toy Store | Kim Howard | NULL | | Village Toys | John Smith | sales@villagetoys.com | +---------------+--------------------+-----------------------+ 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, -> 'liangbocv@126.com', -> '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 | sales@villagetoys.com | | 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 | jjones@fun4all.com | | 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com | | 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL | | 1021 | Andy Liang | NULL | Chengdu | NULL | 32767 | China | NULL | liangbocv@126.com | +------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+ 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 | sales@villagetoys.com | | 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 | jjones@fun4all.com | | 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com | | 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 | jjones@fun4all.com | | Fun4All | 1000000004 | dstephens@fun4all.com | +-----------+------------+-----------------------+ 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 | sales@villagetoys.com | | 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 | jjones@fun4all.com | | 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com | | 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 | sales@villagetoys.com | +------------+--------------+----------------+-----------+------------+----------+--------------+--------------+-----------------------+ 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的记录）等；
约束要比触发器快，尽量用约束；
完！！（对，这里又灭有代码了