你的位置:首页 > 数据库

[数据库]SQL必知必会 14


博主依然不想打字,又向你仍来了一堆代码。。。

 

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: 0mysql> 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: custcopymysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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 testADD CONSTRAINT PRIMARY KEY (cust_id);ALTER TABLE testADD CONSTRAINT UNIQUE KEY (id);ALTER TABLE testADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES customers (cust_id);ALTER TABLE testADD 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的记录)等;

约束要比触发器快,尽量用约束;

 

完!!(对,这里又灭有代码了