MySQL学习

一头拱白菜的猪ll

已于 2022-08-08 16:09:25 修改

阅读量678

点赞数

文章标签： mysql

于 2022-08-08 15:58:38 首次发布

本文链接：https://blog.csdn.net/weixin_45772393/article/details/126229510

版权

yMySQL学习

第十五章联结表

1.关系表

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

可伸缩性：能够适应不断增加的工作量而不失败，设计良好是数据库或应用程序称之为可伸缩性好

联结是一种机制，用来在一条select语句中关联表

01.创建关联

select vend_name, prod_name, prod_price
from vendors, products
WHERE vendors.vend_id = products.vend_id 
order by vend_name, prod_name;

结果

ACME	Bird seed	10.00
ACME	Carrots	2.50
ACME	Detonator	13.00
ACME	Safe	50.00
ACME	Sling	4.49
ACME	TNT (1 stick)	2.50
ACME	TNT (5 sticks)	10.00
Anvils R Us	.5 ton anvil	5.99
Anvils R Us	1 ton anvil	9.99
Anvils R Us	2 ton anvil	14.99
Jet Set	JetPack 1000	35.00
Jet Set	JetPack 2000	55.00
LT Supplies	Fuses	3.42
LT Supplies	Oil can	8.99

分析：

select检索两个表中的不同的列，使用where语句进行联结（需要使用完全限定名）。

02.where语句

where子句作为过滤条件只包含那些匹配给定的条件

03.内部联结

基于两个表之间的相等测试

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id ;

INNER JOIN指定两个表之间的联结关系

ON给出连接条件

04.联结多个表

   ```mysql

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
```

自己分析

oderitems：订单

products：产品

vendors：

从orderitems表，product是，vender表中筛选出产品编号为20005对应的产品名称，供应商，产品价格，和质量信息。

05.对比联结和子查询

联结

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id 
AND orderitems.order_num = orders.order_num;
AND prod_id = 'TNT2' ;

结果

Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Coyote Inc.	Y Lee
Wascals	Jim Jones
Yosemite Place	Y Sam
E Fudd	E Fudd

照书写

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2' ;

结果

Coyote Inc.	Y Lee
Yosemite Place	Y Sam

问题出在哪里了

原因第一行多了个分号

子查询

SELECT cust_name, cust_contact
FROM customers 
WHERE cust_id IN (SELECT cust_id
				  FROM orders
				  WHERE order_num IN (SELECT order_num 
									  FROM orderitems
									  WHERE prod_id = 'TNT2'));

思路：

消费者在什么时候下了一个订单，订单细节包括产品的ID

现在已知产品ID，要倒回去找

第十六章创建高级联结

01. 使用表别名

别名除了可以用于列名和计算字段外，SQL还允许给表起别名

理由：

缩短SQL语句
允许在单条SELECT语句中多次使用相同的表

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
And prod_id = 'TNT2' ;

表别名不仅能使用在WHERE语句中，还可以使用于SELECT的列表、ORDER BY子句以及语句中的其他部分

02.使用不同类型的联结

	自联接	自然联接	外部联结

	找出同一供应商的其他商品	派出多次出现，使每个列只返回一次	联结包含了在相关表中没有关联行的行
对比	子查询

自联结

已知某产品存在问题，查找生产个产品的供应商生产的其他产品有没有问题

子查询

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
								 FROM products
								 WHERE prod_id = 'DTNTR');

自联结

SELECT p1.prod_id, p1.prod_name
FROM products AS p1 , products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR' ;

自然联结

自己选中那些唯一的一列，这一般是通过对表使用通配符（select * ），对所有其他表的列使用明确的自己完成的

SELECT c.*, o.order_num, o.order_date, 
       oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id
AND   oi.order_num = o.order_num
AND   prod_id = 'F8' ;

外部联结

检索所有客户及其订单

内部联结
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

结果
10001	20005
10001	20009
10001	20010
10001	20011
10003	20006
10004	20007
10005	20008

外部联结
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;、

结果
10001	20005
10001	20009
10001	20010
10001	20011
10002	NULL
10003	20006
10004	20007
10005	20008

外部联结中包含没有关联行的行

03.使用带聚集函数的联结

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 ;

结果
Coyote Inc.	10001	4
Wascals 	10003	1
Yosemite Place	10004	1
E Fudd   	10005	1

SELECT customers.cust_name,
       customers.cust_id ,
			 COUNT(orders.order_num) AS num_ord 
FROM customers LEFT OUTER JOIN orders
 ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id ;

结果
Coyote Inc.	10001	4
Mouse House	10002	0
Wascals 	10003	1
Yosemite Place	10004	1
E Fudd  	10005	1

第十七章组合查询

01. 组合查询

两种情况下是使用

在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回结果

02.创建组合查询

使用UNION

只需在给出每条SELECT语句，在各条语句之间放上关键词UNION。

对单个表执行多个查询，按单个查询返回结果

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002); 

结果

1003	FC	    2.50
1002	FU1	    3.42
1003	SLING	4.49
1003	TNT1	2.50
1001	ANV01	5.99
1001	ANV02	9.99
1001	ANV03	14.99
1002	OL1	    8.99

UNION规则

UNION必须有两条或者两条以上的SELECT语句组成，语句之间用关键词UNION分离
UNION中每个查询必须包含相同的列、表达式或聚集函数（不过各列不需要以相同的次序列出）
列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含地转换的类型

包含或取消重复的行

默认，否则可用 UNION ALL

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002); 

结果

1003	FC  	2.50
1002	FU1  	3.42
1003	SLING	4.49
1003	TNT1	2.50
1001	ANV01	5.99
1001	ANV02	9.99
1001	ANV03	14.99
1002	FU1	    3.42
1002	OL1	    8.99

对组合查询结果排序

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5 
UNION 
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;

结果

1001	ANV01	5.99
1001	ANV02	9.99
1001	ANV03	14.99
1002	FU1     3.42
1002	OL1 	8.99
1003	TNT1	2.50
1003	FC   	2.50
1003	SLING	4.49

第十八章全文本搜索

MyISAM支持全文本搜索，InnoDB不支持全文本搜索

01.启用全文本搜索支持

利用FULLTEXT 子句启用全文本搜索

CREATE TABLE productnotes
(
 note_id   INT        NOT NULL AUTO_INCREMENT ,
 prod_id   char(10)   NOT NULL,
 note_date TIMEDATE   NOT NULL,
 PRIMARY key (note_id),
 FULLTEXT (note_text)//对note_text列进行索引
 ) ENGINE = MyISAM;

02.进行全文本搜索

在索引之后，使用两个函数Match()和Against()z执行全文本搜索，

其中Match()指定被搜索的列，传递给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们（而且次序相同）

Against()指定要使用的搜索表达式。

SELECT * FROM `productnotes`
WHERE MATCH(note_text) Against('rabbit');

结果
110	FC	2005-09-01 00:00:00	Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
104	FC	2005-08-19 00:00:00	Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.

LIKE语句完成
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

结果
Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
Customer complaint: rabbit has been able to detect trap, food apparently less effective now.

使用全文本搜索返回以文本的良好程度排序的数据，具有较高等级的行先返回。

全文本搜索如何排除行
SELECT note_text,
       MATCH(note_text) Against('rabbit') AS ranks
FROM productnotes;

03.使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果范围

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) Against ('anvils' WITH QUERY EXPANSION);

结果

Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
Please note that no returns will be accepted if safe opened using explosives.
Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
Matches not included, recommend purchase of matches or detonator (item DTNTR).

注意不要写错关键词

*04.布尔文本查询

可以完成

要匹配的词
要排斥的词
排序提示
表达式分组
另外一些内容

关键词

IN BOOLEAN MODB

应用

select note_text 
from productnotes
where match(note_text) against('heavy' in boolean mode);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from productnotes
    -> where match(note_text) against('heavy' in boolean mode' at line 2

有问题没解决

第十九章插入数据

01.数据插入

02.插入完整的行

mysql> insert into customers
    -> values(NULL,
    -> 'Pep E. LaPEw',
    -> '100 Main Street' ,
    -> 'Los Angeles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected (0.12 sec)>
mysql> select * from customers;
+---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit     | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus    | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie      | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix     | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago     | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10006 | Pep E. LaPEw   | 100 Main Street     | Los Angeles | CA         | 90046    | USA          | NULL         | NULL                |
+---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+
6 rows in set (0.05 sec)

注意每个列必须提供一个值

如果某列没有值，应该使用NULL值

SQL语句高度依赖表中列的定义次序

省略列

条件

该定义为允许NULL值（无值或空值）
在表定义中给出默认值，这表示如果不给出值，将使用默认值。

03.插入多个行

04.插入检索出的数据

mysql> insert into customers
(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM customers;
1062 - Duplicate entry '10001' for key 'PRIMARY'

解决问题

该问题是插入数据表中遇到键重复
1.IGNORE

INSERT IGNORE INTO Table_name(…..) VALUES(1,1),(2,2),(3,3);

使用IGNORE，如果插入的记录中存在重复值会忽略重复值的该记录行，不影响其他行的插入。

2.REPLACE

REPLACE INTO Table_name() VALUES(1,1),(2,2),(3,3)

使用replace当插入的记录遇到主键或者唯一重复时先删除表中重复的记录行再插入

3.*ON DUPLICATE KEY UPDATE *NAME1=VALUES(ID)+1

INSERT TO Table_name() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;

ON DUPLICATE KEY UPDATE后面使用VALUES指的是插入记录的值，而不使用VALUES指的是表的自身值。其后执行的UPDATE更新的记录是WHERE重复的主键或唯一键的ID。

以上三种方法均支持标准的INSERT语法，包括INSERT INTO…VALUES,INSERT INTO…SET,INSERT INTO…SELECT。
————————————————
版权声明：本文为CSDN博主「??or??」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhangyr_student/article/details/80119238



mysql> insert into customers
(
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT 
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM customers;
Query OK, 12 rows affected (0.04 sec)
Records: 12  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          |
+---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit     | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus    | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie      | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix     | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago     | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10006 | Pep E. LaPEw   | 100 Main Street     | Los Angeles | CA         | 90046    | USA          | NULL         | NULL                |
|   10007 | Coyote Inc.    | 200 Maple Lane      | Detroit     | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10008 | Mouse House    | 333 Fromage Lane    | Columbus    | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10009 | Wascals        | 1 Sunny Place       | Muncie      | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10010 | Yosemite Place | 829 Riverside Drive | Phoenix     | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10011 | E Fudd         | 4545 53rd Street    | Chicago     | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10012 | Pep E. LaPEw   | 100 Main Street     | Los Angeles | CA         | 90046    | USA          | NULL         | NULL                |
|   10014 | Coyote Inc.    | 200 Maple Lane      | Detroit     | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10015 | Mouse House    | 333 Fromage Lane    | Columbus    | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10016 | Wascals        | 1 Sunny Place       | Muncie      | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10017 | Yosemite Place | 829 Riverside Drive | Phoenix     | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10018 | E Fudd         | 4545 53rd Street    | Chicago     | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10019 | Pep E. LaPEw   | 100 Main Street     | Los Angeles | CA         | 90046    | USA          | NULL         | NULL                |
|   10020 | Coyote Inc.    | 200 Maple Lane      | Detroit     | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10021 | Mouse House    | 333 Fromage Lane    | Columbus    | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10022 | Wascals        | 1 Sunny Place       | Muncie      | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10023 | Yosemite Place | 829 Riverside Drive | Phoenix     | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10024 | E Fudd         | 4545 53rd Street    | Chicago     | IL         | 54545    | USA          | E Fudd       | NULL                |
|   10025 | Pep E. LaPEw   | 100 Main Street     | Los Angeles | CA         | 90046    | USA          | NULL         | NULL                |
+---------+----------------+---------------------+-------------+------------+----------+--------------+--------------+---------------------+
24 rows in set (0.08 sec)

mysql>

第二十章更新和删除数据

01.更新数据

更新表中特定的行
更新表中所有行

UPDATE语句由三部分组成：

要更新的表
列名和它们的新值
确定要更新行的过滤条件

更新单列
mysql> UPDATE customers 
SET cust_email = 'elmer@fudd.com'
where cust_id = 10005;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
更新多列
mysql> update customers
    -> set cust_name = 'The Fudds',
    ->     cust_email = 'elmer@fudd.com'
    -> where cust_id = 10005 ;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
删除某列的值
mysql> update customers 
    -> set cust_email = NULL 
    -> where cust_id = 10005;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

02.删除数据

从表中删除特定的行
从表中删除所有行

删除单行
mysql> delete from customers
    -> where cust_id = 10006;
Query OK, 1 row affected (0.07 sec)

从表中删除所有的行，不添加where条件

03.指导原则

除非确实打算更新或者删除每一行，否则绝对不要使用不带WHERE子句UPDATE或者DELETE语句。
保证每个表中都有主键
在使用UPDATE或者DELETE语句使用WHERE子句前，应该使用SELECT进行测试，保证它过滤的是正确的测试，以防编写的WHERE子句不正确。
使用强制实施引用完整性的数据库，这样数据库将不允许删除具有与其他表相关联的数据的行

第二十一章创建和操纵表

01.创建表

两种方法

使用具有交互式创建和管理表的工具
用MySQL语句进行操纵染发

给表添加一个列
mysql> alter table vendors
    -> add vend_phone CHAR(20);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
删除刚刚添加的列
mysql> alter table vendors
    -> drop column vend_phone;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0
定义外键
mysql> alter table orderitems
    -> add constraint fk_orderitems_orders
    -> foreign key (order_num) references orders (order_num);

第二十二章使用视图

01.视图的常见应用

重用SQL语句
简化复杂的SQL操作，在编写查询后，可以方便地重用而不必知道它的基本查询细节
使用表的组成部分而不是整个表
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据

02.视图的规则和限制

视图必须唯一命名
对于可以创建的视图数目没有限制
为了创建视图，必须具有足够的访问权限，这些限制通常由数据库管理人员授予
视图可以嵌套，既可以利用从其他视图中检索数据的查询来构造一个视图
ORDER BY可以用在视图，但如果从该视图检索数据的SELECT语句中也含有ORDER BY ，那么该视图中的ORDER BY 将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用，

03.使用视图

视图使用CREATE VIEM语句来创建
使用SHOW CREATE VIEW viewname；来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname；
更新视图时，可以先用DROP再用CREATE ，也可以直接用CREATE OR REPLACE VIEW。如果更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

mysql> create view productcustomers as
    -> select cust_name, cust_contact, prod_id
    -> from customers, orders, orderitems
    -> where customers.cust_id = orders.cust_id
    -> and orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.23 sec)

mysql> select cust_name, cust_contact
    -> from productcustomers
    -> where prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.09 sec)

用视图重新格式化检索出的数据

mysql> select concat(RTrim(vend_name), ')', RTrim(vend_country), ')')
    ->        as vend_title
    -> from vendors
    -> order by vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME)USA)              |
| Anvils R Us)USA)       |
| Furball Inc.)USA)      |
| Jet Set)England)       |
| Jouets Et Ours)France) |
| LT Supplies)USA)       |
+------------------------+
6 rows in set (0.03 sec)

当经常使用这个格式的结果，创建一个视图，每次使用时使用它即可。
mysql> create view vendorlocations as
    -> select concat(RTrim(vend_name), ')', RTrim(vend_country), ')')
    ->        as vend_title
    -> from vendors
    -> order by vend_name ;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from vendorlocations;
+------------------------+
| vend_title             |
+------------------------+
| ACME)USA)              |
| Anvils R Us)USA)       |
| Furball Inc.)USA)      |
| Jet Set)England)       |
| Jouets Et Ours)France) |
| LT Supplies)USA)       |
+------------------------+
6 rows in set (0.03 sec)

用视图过滤不想要的数据

视图对于应用普通的WHERE子句也是很有用

过滤没有电子邮件地址的客户
mysql> create view customeremaillist as
    -> select cust_id, cust_name, cust_email
    -> from customers
    -> where cust_email is not null;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
+---------+----------------+---------------------+
12 rows in set (0.06 sec)

使用视图与计算字段

检索某个特定订单中的物品，计算每种物品的总价格
mysql> create view orderitemsexpanded as 
    -> select order_num,
    ->        prod_id,
    ->        quantity,
    ->        item_price,
    ->        quantity*item_price as expanded_price
    -> from orderitems;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from orderitemsexpanded
    -> where order_num = 20005;
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20005 | ANV01   |       10 | 5.99       | 59.90          |
|     20005 | ANV02   |        3 | 9.99       | 29.97          |
|     20005 | TNT2    |        5 | 10.00      | 50.00          |
|     20005 | FB      |        1 | 10.00      | 10.00          |
+-----------+---------+----------+------------+----------------+
4 rows in set (0.05 sec)

04.更新视图

一般可以

下列条件不行

分组（使用GROUP BY 和 HAVING）
联结
子查询
并
聚集函数
DISTINCT
导出（计算）列

第二十三章使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。

使用存储过程的理由：

通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性
简化对变动的管理。如果表名、列名或业务逻辑（或别的任务）有变化，只需要更改存储过程的代码，使用它的人员甚至不需要知道这些变化。
提高性能
存在一些只能用在单个请求的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

01.使用存储过程

01.执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。

mysql> CALL productpricing(@pricelow,
    ->                     @pricehigh,
    ->                     @priceaverage);

02.创建存储过程

mysql> create procedure productpricing()
    -> begin
    ->    select avg(prod_price) as priceaverage
    ->    from products;
    -> end //
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> call productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571    |
+--------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

03.删除存储过程

mysql> drop procedure productpricing;
Query OK, 0 rows affected (0.09 sec)

注意：没有使用后面的（）

04.使用参数

一般，存储过程并不显示结果，而是把结果返回给你指定的变量

关键词：in，out，inout

IN： 传递给存储过程
**OUT：**从存储过程中传出
**INOUT：**对存储过程传入和传出

mysql> create procedure productpricing(
    ->    out pl decimal(8,2), //out关键字指出相应的参数用来从储存过程中传出一个值(返回给调用者)
    ->    out ph decimal(8,2),
    ->    out pa decimal(8,2)
    -> )
    -> begin
    ->   select min(prod_price)
    ->   into pl
    ->   from products;
    ->   select max(prod_price)
    ->   into ph 
    ->   from products;
    ->   select avg(prod_price)
    ->   into pa
    ->   from products;
    -> end //
Query OK, 0 rows affected (0.04 sec)

调用此存储过程，必须指定3个变量名
mysql> call productpricing(@pricelow,
    ->                     @pricehigh,
    ->                     @priceaverage);
    -> //
Query OK, 1 row affected (0.00 sec)
由于此存储过程要求3个参数，因此必须正好传递3个参数
变量名：所有MySQL变量都必须以@开始

显示检索出的产品平均价格
mysql> delimiter ;
mysql> select @priceaverage ;
+---------------+
| @priceaverage |
+---------------+
| 16.13         |
+---------------+
1 row in set (0.04 sec)

mysql> select @pricehigh, @pricelow, @priceaverage;
+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00      | 2.50      | 16.13         |
+------------+-----------+---------------+
1 row in set (0.04 sec)

ordertotal接受订单号并返回该订单的合计：

mysql> create procedure ordertotal(
    ->    in onumber int,
    ->    out ototal decimal(8,2)
    -> )
    -> begin
    ->    select sum(item_price*quantity)
    ->    from orderitems
    ->    where order_num = onumber
    ->    into ototal;
    -> end //
Query OK, 0 rows affected (0.12 sec)

mysql> call ordertotal(20005, @total);
    -> //
Query OK, 1 row affected (0.01 sec)

mysql> delimiter ;
mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.04 sec)

得到另一个订单的合计显示
mysql> call ordertotal(20009, @total);
Query OK, 1 row affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 38.47  |
+--------+
1 row in set (0.04 sec)

05.建立智能存储过程

mysql> -- Name：ordertotal
-- Parameter：onumber = order number 
--            taxable = 0 if not taxable, 1 if taxable 
--            ototal  = order total variable 

delimiter //
CREATE PROCEDURE ordertotalss(
     IN onumber INT,
	 IN taxable BOOLEAN,
	 OUT ototal DECIMAL(8,2)
)
BEGIN

    -- Declare variable for total 
	DECLARE total DECIMAL(8,2);
	-- Declare tax percentage 
	DECLARE taxrate INT DEFAULT 6;
	
	-- GET the order total 
	SELECT SUM(item_price*quantity)
	FROM orderitems 
	WHERE order_num = onumber 
    INTO total;
	
	-- Is this taxable ?
	IF taxable THEN
	   -- Yes, so add taxarate to the total 
		 SELECT total +(total/100*taxrate) INTO total;
	END IF;
  
	-- And finally, Save to out variable 
	SELECT total INTO ototal ;
	
	END //
1304 - PROCEDURE ordertotalss already exists
mysql> call ordertotalss(20005, 0, @total);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> delimiter //
mysql> call ordertotalss(20005, 0, @total);
    -> select @total //
Query OK, 1 row affected (0.00 sec)

+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.07 sec)

mysql> call ordertotalss(20005, 1, @total);
    -> select @total;
    -> //
Query OK, 1 row affected (0.00 sec)

+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.06 sec)

mysql>

第二十四章使用游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的数据集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

01.使用游标

在能够使用游标前，必须要声明（或定义）它。这个过程实际上没有检索数据，它只是定义要使用的select语句。
一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标。

02.创建游标

这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没有做

mysql> create procedure processorder()
    -> begin
    ->    -- declare the cursor
    ->    declare ordernumber cursor
    ->    for 
    ->    select order_num from orders;
    ->   
    ->    -- open the cursor
    ->    open ordernumber;
    ->   
    ->    
    ->    -- close the cursor
    ->    close ordernumber;
    -> 
    -> end //
Query OK, 0 rows affected (0.05 sec)

03.使用游标数据

mysql> create procedure processorders()
    -> begin
    ->    declare o int;
    ->    
    ->     
    ->    declare ordernumbers cursor
    ->    for
    ->    select order_num from orders;
    -> 
    ->    open ordernumbers;
    ->   
    ->    fetch ordernumbers into o;
    -> 
    ->    close ordernumbers;
    -> end //
Query OK, 0 rows affected (0.10 sec)