首页 > > 网络编程 > Mysql >

SQL必知必会 14-22（完）

2018-06-17 22:53:08来源：未知阅读 ()

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

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的记录）等；

约束要比触发器快，尽量用约束；

完！！（对，这里又灭有代码了

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：mysql数据库权限及编码

下一篇：Mysql启停以及恢复备份恢复数据库

IDC资讯：主机资讯注册资讯托管资讯 vps资讯网站建设

网站运营：建站经验策划盈利搜索优化网站推广免费资源

网站联盟：联盟新闻联盟介绍联盟点评网赚技巧

行业资讯：搜索引擎网络游戏电子商务广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器安全防护

软件技巧：其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识：网络协议网络安全网络管理组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条

最新资讯

热门关注

热门标签