mysql知会必会小节

第一章不区分大小写；（；）命令结束；所有空格忽略，可以分多行。1.1数据库基础1.1.1 数据库定义：保存有组织的数据容器1.1.2 表定义：某种特定数据类型的结构化清单模式（schema）：关于数据库和表的布局及特性的信息1.1.3 列和数据类型定义：表中的一个字段。所有表都是由一个或多个列组成分解数据：利用特定列对数据进行排序和过滤数据类型：所允许的数据类型。每个表列都有相应的数据类型，限制该

feng_xun123

10391人浏览 · 2020-08-08 19:54:29

feng_xun123 · 2020-08-08 19:54:29 发布

第一章

不区分大小写；（；）命令结束；所有空格忽略，可以分多行。

1.1数据库基础

1.1.1 数据库

定义：保存有组织的数据容器

1.1.2 表

定义：某种特定数据类型的结构化清单
模式（schema）：关于数据库和表的布局及特性的信息

1.1.3 列和数据类型

定义：表中的一个字段。所有表都是由一个或多个列组成
分解数据：利用特定列对数据进行排序和过滤
数据类型：所允许的数据类型。每个表列都有相应的数据类型，限制该列中存储的数据。

1.1.4 行

定义：表中的一个记录（row）；技术上行是对的

1.1.5 主键

定义：一列（或一组列），其值能够唯一区分表中每一行。（唯一标识表中每行的这个列成为主键）
应该总是定义主键
主键规则：任意两行都不具有相同的主键值；每一行都鼻祖具有一个主键值。

1.2 什么是SQL

定义:SQL(sequel)是结构化查询语言（structured Query Language）的缩写。

第二章

2.1.1 客户机-服务器软件

客户机：与用户打交道的软件；可以是mysql提供的工具，脚本，Web应用，程序设计语言等
服务器：负责所有数据访问和处理的软件，可以本地安装副本运行，也可以连接到运行远程的服务器上的一个副本

2.2 Mysql工具

2.2.1 MySQL命令行实用程序
命令用；或\g结束
2.2.2 Mysql administrator
图形交互客户机，简化Mysql服务器管理
2.2.3 Mysql Query Brower
为一个图形交互客户机，编写和执行mysql命令

第三章使用mysql

3.1 连接

3.2 选择数据库

USE关键字
USE crsahcourse;

注：关键字不用做表名或者列名

3.3 了解数据库和表

SHOW DATABASES；
SHOW TABLES；
SHOW COLUMNS FROM customers;显示表列
DISCRIBLE customers

HELP SHOW
什么是自动增量？某些表列需要唯一值，CREATE语句创建时定义
DISCRIBLE语句：作为SHOW COLUMNS FROM的快捷方式。

第四章检索数据库

4.1 SELECT语句

想选择什么以及从哪里选择

4.2 检索单列

SELECT prod_name FROM products;

4.3 检索多列

SELECT prod_id，prod_name,prod_price FROM products;

4.4 检索所有列

SELECT * FROM products;

4.5 检索不同的行

SELECT DISTINCT vend_id FROM products;
DISTINCT 关键字放在列名前。不能部分使用DISTINCT,指示返回不同值

4.6 限制结果

SELECT *prod_name FROM products LIMIT 5;
SELECT *prod_name FROM products LIMIT 3，4;
SELECT *prod_name FROM products LIMIT 4 OFFSET 3;
行0：检索出来的第一行为行0；行数不够；LIMIT用法。

4.7 使用完全限定的表名

SELECT DISTINCT products.vend_id FROM crashcourse.products;

第五章排序检索数据

ORDER BY

5.1 排序数据

子句：（clause）SAL语句由子句构成，必需或可选。一个子句由一个关键字和所提供的数据组成。
使用ORDERD BY子句取一个或多个列的名字。

SELECT prod_name FROM products ORDERD BY prod_name;

5.2 按多个列排列

指定列名，列名按照逗号隔开。
SELECT prod_id,prod_price,prod_name, FROM products ORDERD BY prod_price, prod_name;
多行具有相同prod_price,时才对产品按照prod_name进行排序。

5.3 指定排序方向

SELECT prod_id,prod_price,prod_name, FROM products ORDERD BY prod_price DESC;
SELECT prod_id,prod_price,prod_name, FROM products ORDERD BY prod_price DESC,prod_name;
DESC关键字只应用到直接位于其前面的列名。
在多列上降序排序：必须每个列指定DESC，升序排序ASC
区分大小写与排序顺序：理论认为不区分大小写（默认行为）。管理员可以改变这种行为。
SELECT prod_id,prod_price FROM products ORDERD BY prod_price DESC，LIMIT 1;
返回最贵的价格（找出一个列中最高或最低的值）
子句顺序：From 子句+ORDER BY+LIMIT

第6章过滤数据

6.1 使用where

where子句位于表名（From子句）之后
SELECT prod_name,prod_price
FROM products
WHERE prod_price=2.50;
SQL过滤与应用过滤：略
ORDER BY位于WHERE子句之后。

6.2 where子句操作符

=，<>(不等于),!=(不等于)，<,<=,>=,BETWEEN

6.2.1 检查单个值

SELECT prod_name,prod_price
FROM products
WHERE prod_price=2.50（<10;<=10;）;

6.2.2 不匹配检查

SELECT prod_name,prod_price
FROM products
WHERE vend_id <>（！=） 1003;
何时使用‘’。单引号限定字符串。

6.2.3 范围值检查

开始和结束两个值：BETWEEN AND
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

6.2.4 空值检查 IS NULL

在一个列不包含值时，称其为包含空值NULL
NULL空值与字段包含0，空字符串，仅仅包含空格不同
SELECT prod_name,prod_price
FROM products
WHERE prod_price IS NULL;

SELECT prod_name,prod_price
FROM products
WHERE cust_email IS NULL;
NULL与不匹配

第7章数据过滤（NOT IN）

7.1 组合where子句

多个where子句：and子句方式或者OR子句方式

7.1.1 AND操作符

SELECT prod_name,prod_price
FROM products
WHERE prod_price <= 10 AND vend_id=1003;

7.1.2 OR操作符

SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003;

7.1.3 计算次序

SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003 AND prod_price >= 10;
vend_id=1003 AND prod_price >= 10执行，再执行 vend_id=1002
AND的优先级大于OR
SELECT prod_name,prod_price
FROM products
WHERE (vend_id=1002 OR vend_id=1003) AND prod_price >= 10;

7.2 IN操作符

IN操作符指定条件范围，范围内的每个条件都可以进行匹配。IN取合法值——由圆括号内都好隔开。
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN(1002,1003)
ORDER BY prod_name;
等于：IN：where子句中用来指定要匹配值的清单的关键字，功能与OR相当
SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003
ORDER BY prod_name;

7.3 NOT操作符

where 条件子句中NOT操作符有且只有一个，否定它之后的所有条件
SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN(1002,1003)
ORDER BY prod_name;
mysql支持NOT对IN，BETWEEN，EXISTS取反。

第8章用通配符进行过滤（LIKE）

8.1 LIKE操作符

之前过滤共同点:过滤中使用的值都是已知的。构造通配符搜索模式。
通配符（wildcard）：用来匹配值的一部分的特殊字符
搜索模式：(SEARCH PATTERN):由字面值，通配符或者两者组合构成的搜索条件。

LIKE指示Mysql后面跟的搜索模式时通配符匹配而不是直接相等匹配进行比较。
谓词（predicate）

8.1.1. 百分号（%）通配符

%表示任何字符出现任意次
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘jet%’;
区分大小写：根据Mysql设置，搜索可以区分大小写。
可以使用多个通配符。%还能匹配0个字符，代表搜索模式中给定位置的0个，1个或多个字符

SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘%anvil%’（‘s%e’）;
注意尾空格：’%anvil’改为‘%anvil%’
注意NULL，‘%’不能代表值为NULL的行

8.1.2 下划线（_）通配符

_只匹配单个字符：不能多也不能少
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘_ ton anvil’;
结果:1 _ ton anvil,2 _ ton anvil
.5 _ ton anvil就不再结果中

8.2 通配符使用技巧

不要过度使用通配符：时间消耗大
除非必要，必要把通配符用在搜索模式的开头处；
注意通配符位置

第9章用正则表达式进行搜索

正则表达式用来匹配文本的特殊的串（字符集合）。特殊语法和指令

使用Mysql正则表达式

正则表达式是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;

SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘.000’
ORDER BY prod_name;
'.'为正则表达式中一个特殊字符，表示匹配任意一个字符。

LIKE和REGEXP区别：
SELECT prod_name,prod_price
FROM products
WHERE prod_name LIKE ‘1000’
ORDER BY prod_name;
不返回数据
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘1000’
ORDER BY prod_name;
返回数据
LIKE：匹配整个列，被匹配的文本在列中出现，like不会找到她，不会返回行（除非使用通配符）。regexp被匹配的文本在列中出现，对应的行直接返回。
匹配不区分大小写：为了区分大小写可以使用：关键字BINARY
WHERE prod_name REGEXP 'JetPack .000’

9.2.2 进行OR匹配

搜索两串之一（要么为这串，要么为那串）使用’|‘
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘1000|2000’
ORDER BY prod_name;
1000和2000都匹配返回
两个以上的OR：1000|2000|3000

9.2.3 匹配几个字符之一

只想匹配特定的字符，’[]'和 ——括号起来的字符来完成：
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name;
返回数据匹配1或2或3，表示1 ton或2 ton或3 ton都匹配返回。
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘1|2|3 Ton’
ORDER BY prod_name;
返回出错：1，2，3 ton或匹配
[123]否定[^123]

9.2.4 匹配范围

集合来定义要匹配的一个或多个字符：[0123456789],
简化[0-9]
[a-z]
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘[1-5] Ton’
ORDER BY prod_name;

9.2.5 匹配特殊字符

要找出’.'字符的值：
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘.’
ORDER BY prod_name;
返回不是预期：
匹配特殊字符，要前导\,\-=-,\.=.——转义\(escaping)
SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘\.’
ORDER BY prod_name;
\\引用空白元字符（具有特殊含义的字符）
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表

**匹配: \\ **

9.2.6 匹配字符类

找出经常使用的数字、所有字母字符或所有数字字母字符等匹配。

9.2.7 匹配多个实例

对匹配的数目进行更强的控制：
重复元字符：
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
？0个或1个匹配（等于{0,1}）
{n} 指定数目匹配
{n,} 不少于指定数目匹配
{n,m} 匹配数目的范围（m不超过255）

SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘\$[0-9] sticks?\$’
ORDER BY prod_name;
’\$[0-9] sticks?\\’ : \\是匹配’\(’，[0-9]匹配任意数字，sticks？匹配stick和sticks（s后面的？使s可选，为1或0个s），\$是匹配‘)’。

SELECT prod_name,prod_price
FROM products
WHERE prod_name REGEXP ‘[[:digit:]]{4}’
ORDER BY prod_name;
’[[:digit:]]{4}’ 匹配连在一起的4位数字
等价于’[0-9][0-9][0-9][0-9]‘

9.2.8 定位符

匹配特定位置的文本：
定位元字符：
^ 文本开头
$ 文本结尾
[[:<:]] 词开始
[[:>:]] 词结尾

找一个数（包括以小数点开始的数）
‘^[[0-9]\\.]’
开头为数字或.才匹配

^的双重用法：在‘[]'中为否定，其他地方为串开头
简单的正则表达式测试：
SELECT ‘hello’ REGEXP ‘[0-9]’;
返回0，（没有hello中数字）

第10章创建计算字段

存储表中数据都不是应用程序所需要的，愮直接将从数据库中检索出转换、计算或格式化过的的数据。计算字段是运行时在SELECT语句内创建的。
字段（field）基本与列（column）相同。但是字段通常用计算字段连接上。

10.2 拼接字段

供应商name(location)给出供应商的位置
拼接（concatenate）：将值联结到一起构成单个值
Concat（）函数；其他DBMS中+或者||
SELECT Concat(vend_name,’(’,vend_country,’)’)
FROM vendors
ORDER BY vend_name;
备注：Trim函数：RTrim and LTrim表示去掉串左右，右，左的空格
SELECT Concat(RTrim(vend_name),’(’,vend_country,’)’)
FROM vendors
ORDER BY vend_name;
使用别名（导出列）：拼接后的字段没有名字，SQL支持别名（alias），是一个字段或值的替换名。别名关键字AS
SELECT Concat(RTrim(vend_name),’(’,RTrim(vend_country),’)’) AS vend_title
FROM vendors
ORDER BY vend_name;
任何客户机应用都可以按名引用这个列。

10.3 执行算术计算

SELECT id,nums,price
FROM items
WHERE order_num=20051;

SELECT id,
nums,
price,
nums*price AS expanded_price
FROM items
WHERE order_num=20051;
Mysql算术操作
+，-，，/
如何测试计算：省略FROM子句简单访问和处理表达式：
SELECT 23；
SELECT Trim(‘abc’);
SELECT Now();

小结：计算字段：Concat(),算术计算，AS别名

第11章使用数据处理函数

SQL支持函数来处理数据，给数据的转换和处理提供方便。

11.2 使用函数

支持以下的函数：
处理文本串；算术操作；处理日期和时间并从这些值中提取特定成分的日期和时间的函数；返回DBMS正在使用的特殊信息的系统函数。

11.2.1 处理文本串：文本处理函数

SELECT vend_name,Upper（vend_name）AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
Left();Length();Locate();Lower(),LTrim();Right();RTrim();
Soundex();SubString();Upper();
Soundex将任何文本串转换为描述其语音表示的字母数字模式的算法。
SELECT vend_name,cust_contact
FROM vendors
WHERE cust_contact=‘Y. Lie’
ORDER BY vend_name;
return NONE
SELECT vend_name,cust_contact
FROM vendors
WHERE Sounddex(cust_contact)=Soundex(‘Y. Lie’)
ORDER BY vend_name;
return Y Lee

11.2.2 日期和时间处理函数

AddDate();AddTime();CurDate();CurTime();Date();DateDiff();
Date_Add();Date_Format();Day();DayofWeek();Hour();Minute();
Month();Now();Second();Time();Year()

WHERE date=‘2005-09-08’;
WHERE Date(date) BETWEEN ‘2005-09-08’ AND ‘2007-09-08’;
WHERE Year(date) =2005 AND Month(date) =9;

11.2.3 数值处理函数

Abs();Cos();Exp();Mod();Pi();Rand();Sin();Sqrt();Tan()

第12章汇总数据

聚集函数

12.1 聚集函数

确定表中行数；获取表中行组的和；找出表列的最大值，最小值和平均值
聚集函数（aggregate funtion）：运行在行组上，计算和返回单个值的函数

AVG();COUNT();MAX();MIN();SUM()

12.1.1 AVG（）

所有列或选定列的平均值
SELECT AVG(prod_price) AS avg_price
FROM vendors;
表中所有产品的平均值
SELECT AVG(prod_price) AS avg_price
FROM vendors
WHERE vend_id=10003;
AVG()忽略列值为NULL的行

12.1.2 COUNT

表中行的数目或者符合特定条件的行数
COUNT():包括NULL
COUNT（column）：忽略NULL值
**SELECT COUNT() AS num_cust**
FROM vendors;

SELECT CPUNT(cust_email) AS num_cust
FROM vendors;

12.1.3 MAX

SELECT MAX(prod_price) AS max_price
FROM vendors;
对与文本数据，MAX函数返回最后一列；忽略列值为NULL的行

12.1.4 MIN

SELECT MIN(prod_price) AS min_price
FROM vendors;
对与文本数据，MAX函数返回最前一列；忽略列值为NULL的行

12.1.5 SUM

SELECT SUM(prod_price) AS sum_price
FROM vendors;
SELECT SUM(prod_price*nums) AS total_price
FROM vendors;
对与文本数据，MAX函数返回最后一列；忽略列值为NULL的行
在多列上进行计算：利用标准算术操作符，所有聚集函数都可以在多个列上执行计算。

12.2 聚集不同值

对所有行执行操作（指定ALL或者不指定默认ALL）
只包含不同的值，指定DISTINCT参数
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM vendors;
返回结果高，有许多物品具有相同的都价格。排除它们提升平均值
DISTINCT只能用于COUNT(DISTINCT 列名)

组合聚集函数

SELECT COUNT(*) AS num_items,
AVG(prod_price) AS avg_price,
MAX(prod_price) AS max_price
MIN(prod_price) AS min_price
FROM vendors;
别名不应该为表中实际的列名

第13 章分组数据

分组数据，能汇总表中数据的子集——GROUP BY 和HAVING子句
将数据分为多个逻辑组，对每个组进行聚集函数计算。

13.2 创建分组

SELECT vend_id, COUNT() AS num_prods
FROM vendors
GROUP BY vend_id;
COUNT() AS num_prods为计算字段
GROUP BY的规定：
1-GROUP BY子句可以包含任意数目的列。
2-GROUP BY子句中嵌套了分组，数据将在所有列最后规定的分组上进行汇总。
3-GROUP BY子句中列出的每列必须是检索列或者是有效表达式（不能是聚集函数）。
4-GROUP BY除了聚集函数计算语句外，SELECT 语句中的每个列都必须是在GROUP BY子句中给出。
5-如果分组列中存在NULL值，则NULL将作为一个分组返回；如果列中有多行NULL值，将它们分为一组。
6-GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前。

13.3 过滤分组

HAVING类似于WHERE子句，但是WHERE过滤行，HAVING过滤分组。
HAVING支持所有WHERE的操作符。（句法相同，只是关键字有所不同）

SELECT cust_id,COUNT() AS orders
FROM vendors
GROUP BY cust_id
**HAVING COUNT()>=2;**
订单至少位的所有顾客
SELECT cust_id,COUNT() AS orders
FROM vendors
WHERE prod_price >=10
GROUP BY cust_id
**HAVING COUNT()>=2;**

分组和排序

GROUP BY	ORDER BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择列都可以使用）	只能使用选择列或表达式，而且必须使用每个选择列的表达式
不一定需要	如果与聚集函数一起使用列（或表达式）你则必须使用

不要忘记ORDER BY：在使用GROUP BY 子句时，加上ORDER BY子句使输出唯一(正确排序)。
SELECT cust_id,COUNT() AS orders
FROM vendors
WHERE prod_price >=10
GROUP BY cust_id
**HAVING COUNT()>=2;**

SELECT cust_id,SUM(nums * price) AS ordertotal
FROM vendors
WHERE prod_price >=10
GROUP BY cust_id
HAVING SUM(nums * price)>=50;
ORDER BY ordertotal;

13.5 SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

GROUP BY	ORDER BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用（甚至非选择列都可以使用）	只能使用选择列或表达式，而且必须使用每个选择列的表达式
不一定需要	如果与聚集函数一起使用列（或表达式）你则必须使用

HAVING类似于WHERE子句，但是WHERE过滤行，HAVING过滤分组。
HAVING支持所有WHERE的操作符。（句法相同，只是关键字有所不同）

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’));
列必须匹配：WHERE子句与SELECT 子句具有相同数目的列。

SELECT cust_name,cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

15章联结表

15.1.1 关系表

一个存储供应商信息，另一张存储产品信息。
外键（foreign key）：为某个表中一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性（scale）：能够适应不断增加的工作量而不失败。

15.2 创建联结

SELECT vend_name,prod_name,prod_price
FROM vecdors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

15.2.1 WHERE 子句的重要性

笛卡儿积（cartesian product）:由没有联结条件的表关系返回的结果为笛卡儿积。检索出表的行数将是第一个表中行数乘第二个表中行数。
SELECT vend_name,prod_name,prod_price
FROM vecdors,products
ORDER BY vend_name,prod_name;

15.2.2 内部联结

目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。
SELECT vend_name,prod_name,prod_price
FROM vecdors,products
WHERE vendors.vend_id = products.vend_id；

SELECT vend_name,prod_name,prod_price
FROM vecdors INNER JOIN products
ON vendors.vend_id = products.vend_id；
ORDER BY vend_name,prod_name;

15.2.3 联结多个表

一个SELECT语句可以联结多个表的数目没有限制。
SELECT vend_name,prod_name,prod_price，quantity
FROM vecdors,products,orderitems
WHERE vendors.vend_id = products.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 200005；

SELECT vend_name,prod_name,prod_price，quantity
FROM vecdors
WHERE vend_id IN( SELECT vend_id
FROM products
WHERE prod_id IN( SELECT prod_id
FROM orderitems
WHERE prod_id = 200005))；

第16章创建高级联结

16.1 使用表别名

SELECT Concat(RTrim(vend_name),’(’,RTrim(vend_country),’)’) AS
vend_title
FROM vendors
ORDER BY vend_name;

SELECT cust_name,cust_contact
FROM customers As c,order AS o
WHERE c.cust_id=o.cust_id
AND prod_id=‘TNT2’;

16.2 使用不同类型的联结

16.2.1 自联结

使用表别名原因之一：单条SELECT语句中不止一次引用相同的表。
物品ID：DTNR问题，找到生产商生产的其他产品
SELECT prod_name,prod_id
FROM products
WHERE vendors.vend_id = （SELECT vend_id
FROM products
WHERE prod_id = ‘DTNR’）

SELECT p1.prod_name,p2.prod_id
FROM products AS p1,products AS p2
WHERE p1.vendors.vend_id = p2.vendors.vend_id
AND prod_id = ‘DTNR’

用自然结而不用子查询：自然结通常作为外语句用来代替从相同表格中检索数据时使用的子查询语句。

16.2.2 自然联结

*内联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。通过对表使用通配符（SELECT ），对所有其他表的列使用明确的子集来完成的。
SELECT c.*, o.order_num,o.order_date,
oi.prod_id,oi.prod_quantity,oi.item_price
FROM customers As c,order AS o,orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id=‘FB’;
所以没有重复的列被检索出来。

16.2.3 外部联结

联结包含了那些相关表中没有关联行的行。
SELECT vend_name,prod_name,prod_price
FROM vecdors INNER JOIN products
ON vendors.vend_id = products.vend_id；
ORDER BY vend_name,prod_name;

SELECT vend_name,prod_name,prod_price
FROM vecdors LEFT OUTER JOIN products
ON vendors.vend_id = products.vend_id；
ORDER BY vend_name,prod_name;

OUTER JOIN 。。。ON
LEFT为 OUTER JOIN 左边表还包括没有关联行的行。
SELECT vend_name,prod_name,prod_price
FROM vecdors RIGHT OUTER JOIN products
ON vendors.vend_id = products.vend_id；
ORDER BY vend_name,prod_name;

带聚集函数的联结

SELECT vend_name,prod_name,
COUNT（vendor.prod_price）AS price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id；
GROUP BY vend.vend_name;

SELECT vend_name,prod_name,
COUNT（vendor.prod_price）AS price
FROM vendors LEFT OUTER JOIN products
ON vendors.vend_id = products.vend_id；
GROUP BY vend.vend_name;
多出了客户Mouse House

第17章组合查询

融合利用UNION操作将多条SELECT语句组合一个结果集
执行多个查询SELECT语句，并将结果作为单个查询语句集返回——这些组合查询称为并（UNION）或复合查询（Compound query）。
情况：1）在单个查询中从不同的表返回类似结构的数据；
2）对单个表执行多个查询，按单个查询返回数据；
组合查询和多个where条件等功效

17.2 创建组合查询

在每条SELECT语句之间放上关键字UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5;

SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002);
组合语句：
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);
返回小于5和1001，1002的所有（或）结果；
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5
OR vend_id IN(1001,1002);
UNION规则：
1）必须由2条或2条以上的SELECT语句构成，语句之间实验UNION
2）UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过每个列不必相同次序出现）；
3）列数据类型必须兼容：类型不必完全相同，但必须是可以隐式转换的。

17.2.3 包含和取消重复的行

UNION默认返回去掉重复行，可以改变，使用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);
UNION实现了多个where条件不能完成的事——输出重复行

17.2.4. 对组合查询结果排序

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)
OREDR BY vend_id,prod_price;
最后select使用ORDER BY是排序所有select语句返回的结果

第18章全文本搜索

18.1 理解全文本搜索

搜索引擎：MyISAM和InnoDB，后者不支持全文本搜索。
之前的LIKE关键字通配符匹配文本和基于文本搜索的正则表达式匹配列值限制：
1）性能；
2）明确控制；
3）智能化结果。

18.2 使用全文本搜索

索引被搜索的行：SELECT和match()，Against（）一起使用来搜索

18.2.1 启用全文本搜索

创建表是启用全文本搜索。
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datatime NOT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MyISAM;

FULLTEXT(note_text)的指示对它进行索引。这里FULLTEXT索引单个列，也可以索引多个列。

18.2.2 进行全文本搜索

Match（）指定被搜索的行
Against()：指定要使用的搜索表达式
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against（‘rabbit’);
Against（‘rabbit’)指定rabbit作为搜索文本，返回两行包含rabbit的文本
Match()值必须于FUUTEXT（）定义中的相同，多列次序也应相同。
不区分大小写。
SELECT note_text
FROM productnotes
WHERE LIKE ‘%rabbit%’;

SELECT note_text,
Match(note_text) Against（‘rabbit’) AS rank
FROM productnotes;
返回所有结果，其中rank显示所有的note_text含有rabbit的等级程度。rabbit越前出现，等级越高。

18.2.4 布尔文本搜索

提供如下细节：1）匹配的词；
2）排斥的词；
3）排序提示；
4）表达式分组；
5）另外的内容。
即使没有FULLTEXT索引也可以使用
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against（‘rabbit’ IN BOOLEAN MODE);

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against（‘rabbit -rope*’ IN BOOLEAN MODE);
匹配包含rabbit但是不包含任意的以rope开头的词的行。

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，而且减少等级值
()	把词组成一个子表达式
~	取消一个词的排序值
*	词尾通配符
" "	定义一个短语（于单个词不同，为短语）

忽略词中的单引号——don‘t为dont
排序而不排列

第19章插入数据

INSERT插入行到数据库表的。
1）插入完整的行；
2）插入行的一部分；
3）插入多行；
4）插入某些查询的结果

19.2 插入完整的行

INSERT INTO customers
VALUES(NULL,
‘per E.Lapaw’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
不产生输出。
完全依赖特定次序的SQl语句是不安全的。
INSERT INTO customers（cust_name,
cust_address,
cust_citty,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email）
VALUES(‘per E.Lapaw’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
VALUES 中第一个值对应于表中第一列。
总是使用列的列表
省略列：条件：
1）该列定义允许NULL值（无值或空值）；
2）在表定义中给出默认值。
INSERT LOW_PRIORITY INTO：降低INSERT的优先级。数据检索很重要的情况下。

19.3 插入多行

INSERT INTO customers（cust_name,
cust_address,
cust_citty,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email）
VALUES(‘per E.Lapaw’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL);
INSERT INTO customers（cust_name,
cust_address,
cust_citty,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email）
VALUES(‘wuhua’,
‘101 Main Street’,
‘Los Angeles’,
‘CA’,
‘90048’,
‘USA’,
NULL,
NULL);
或者：
INSERT INTO customers（cust_name,
cust_address,
cust_citty,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email）
VALUES(‘per E.Lapaw’,
‘100 Main Street’,
‘Los Angeles’,
‘CA’,
‘90046’,
‘USA’,
NULL,
NULL)，
（‘wuhua’,**
‘101 Main Street’,
‘Los Angeles’,
‘CA’,
‘90048’,
‘USA’,
NULL,
NULL);
每组值用一对圆括号括起来，逗号隔开。

19.4插入检索出来的数据

INSERT SELECT
从另一张表中合并客户表到自己的customers表中。
INSERT INTO customers（cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email）
SELECT cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_cpuntry,
cust_contact,
cust_email
FROM cuetnew;
不关心SELECT返回的列名——对于使用不同列名的表导入数据非常有效。

第20章更新和删除数据

20.1 更新数据

UPDATE：
1）更新表中特定的行；
2）更新表中所有行。
不要忘记加where，否则更新所有行！安全问题
3部分组成：
1）要更新的表；
2）列名和它的新值；
3）确定更新行的过滤条件。
UPDATE customers
SET cust_email=‘elsmer@fudd.com’
WHERE cust_id=10005;
UPDATE总是以表名开始，SET用来赋新值给要更新的列。
UPDATE customers
SET cust_email=‘elsmer@fudd.com’，
cust_name=‘The Fudds’
WHERE cust_id=10005;
1-在UPDATE中使用SELECT搜索出来的子查询语句结果。
2-IGNORE关键字：UPDATE构成出错，取消操作，回复原始状态；IGNORE的话，继续进行。
3-除某列值，可以设置为NULL
UPDATE customers
SET cust_email=NULL
WHERE cust_id=10005;

20.2 删除数据

DELETE语句：
1）从表中删除特定的行
2）从表中删除所有行
不要省略where语句；DELETE与安全；
DELETE FROM customers
WHERE cust_id=10006;
DELETE FROM+表名；DELETE不要通配符和列名，因为删除整个行而不是特定列——删除特定列，请使用UPDATE

删除表的内容而不是表；更快的删除——TRUNCATE TABLE语句

20.3 更新和删除的指导原则

1）绝不使用不带WHERE子句的UPDATE和DELETE
2）在删除和更新之前，使用SELECT语句进行测试，保证where过滤的是正确的记录。
3）不允许删除具有与其他表相关联的数据的行。

小心使用，Mysql没有撤销（undo）按钮。

第21章创建和操纵表

21.1 创建表

1）使用具有交互式创建和管理表的工具；
2）表也可以使用mysql语句操纵。

21.1.1 表创建基础

CREATE TABLE:
1)新表的名字，在CREATE TABLE之后；
2）表列名字和定义，逗号隔开。
CREATE TABLE customers IF NOT EXISTS
（cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
PRIMARY KEY (cust_id）
)ENGINE InnoDB;
语句格式化——缩进格式；处理现有的表——首先手动删除，再重建，防止覆盖原有表，重建出错。

21.1.2 使用NULL值

不允许NULL的列，不接受该列没有值的行。插入和更新必须有值。NULL为默认值——不指定NOT NULL
NULL与空串不同，’ ‘可以再NOT NULL列赋值。

21.1.3 主键介绍

主建值必须唯一。每行必须要有一个主键值。如果主键使用单个列，他的值必须唯一；使用多个列，这些列的组合值必须唯一。
CREATE TABLE customers IF NOT EXISTS
（cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
PRIMARY KEY (cust_id，cust_contact）
)ENGINE InnoDB;
主键唯一标识表中每一行的列，为不允许NULL的列

21.1.4 AUTO INCREMENT

本列每当增一行时自动增量。给该列赋予下一个可用的值。
cust_id int NOT NULL AUTO_INCREMENT
只允许一个AUTO INCREMENT的列，而且必须被索引。

覆盖AUTO INCREMENT——insert中指定一个未被使用的主键值，后续增量将会手动插入值；
确定AUTO INCREMENT的值——SELECT last_insert_id(),返回最后一个AUTO INCREMENT的值。

21.1.5 指定默认值

quantity int NOT NULL DEFAULT 1，
使用默认值而不是NULL值

21.1.6 引擎类别

InnoDB：是一个可靠的事务处理引擎，不支持全文本搜索；
MyISAM：是一个性能极高的引擎，支持全文本搜索，但不支持事务处理；
MEMORY：功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）。
引擎类型的混合使用.

更新表

ALTER TABLE
1） ALTER TABLE之后给出更改表的名字；
2）更改的列表。
ALTER TABLE vendors
ADD vend_phone char(20);
增加一个列

ALTER TABLE vendors
DROP COLUMN vend_phone;
删除列。

ALTER TABLE orederitems
ADD CONSTRAINT fk_oredritems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);
ALTER TABLE定义外键。

21.3 删除表

DROP TABLE

21.4 重命名表

RENAME TABLE　ｃｕｎ　ＴＯ　ｃｕｎ１；

RENAME TABLE　ｃｕｎ　ＴＯ　ｃｕｎ１，
ｃｕｎｉ　ＴＯ　ｃｕｎｉ２；

第２２章使用视图

２２.１视图

视图是虚拟的表。与包含数据的表不同，视图只包含使用时动态检索数据的查询。从３个表中检索数据：检索某个特定产品的客户
ＳＥＬＥＣＴ　ｃｕｓｔ＿ｎａｍｅ，ｃｕｓｔ＿ｃｏｎｔａｃｔ
ＦＲＯＭ　ｃｕｓｔｏｍｅｒｓ，ｏｒｄｅｒｓ，ｏｒｄｅｒｉｔｅｍｓ
ＷＨＥＲＥ　ｃｕｓｔｏｍｅｒｓ．ｃｕｓｔ＿ｉｄ＝ｏｒｄｅｒｓ．ｃｕｓｔ＿ｉｄ
ＡＮＤ　ｏｒｄｅｒｉｔｅｍｓ．ｏｒｄｅｒ＿ｎｕｍ＝ｏｒｄｅｒｓ．ｏｒｅｄｅｒ＿ｎｕｍ
ＡＮＤ　ｐｒｏｄ＿ｉｄ＝＇ＴＮＴ２＇；
可以将整个查询包装成一个名为ｐｒｏｄｕｃｔｃｕｓｔｏｍｅｒｓ的虚拟表，：
ＳＥＬＥＣＴ　ｃｕｓｔ＿ｎａｍｅ，ｃｕｓｔ＿ｃｏｎｔａｃｔ
ＦＲＯＭ　ｐｒｏｄｕｃｔｃｕｓｔｏｍｅｒs
ＷＨＥＲＥ　ｐｒｏｄ＿ｉｄ＝＇ＴＮＴ２＇；
视图不包含表中应该有的任何列或数据，它包含的是一个ＳＱＬ查询。

２２.１.１为什么使用视图

１）重用ＳＱＬ语句
２）简化ｓｑｌ操作
３）使用表的组成部分而不是整个表
４）保护数据，授予特定的用户使用（特定部分的访问权限）
５）更改数据格式和表示。
视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表检索出来的。在添加或者更改这些表中的数据时，视图将返回改变过的数据。

２２.１.２视图的规则和限制

１）与表一样，视图必须唯一命名；
２）对于可以重建的视图数目没有限制；
３）为了重建视图必须有足够的权限；
４）视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图；
５）ORDER　ＢＹ也可以在视图中使用，但如果从该视图检索数据的ＳＥＬＥＣＴ语句中也包含ORDER BY，那么该视图中的ORDER BY将被覆盖；
6）视图不能索引，也不能有关联的触发器或默认值
7）视图可以和表一起使用。如：编写一个联结表和视图的SELECT语句。

22.2 使用视图

1）视图CREATE VIEW语句来创建
2）SHOW　CREATE　VIEW viewname 查看视图的语句；
3）DROP VIEW viewname删除视图
4）更新视图，先DROP，在CREATE；或者，CREATE OR REPLACE　ＶＩＥＷ——视图不存在，直接创建；视图存在，更新的语句替换原有视图。

２２.２.１利用视图简化联结

隐藏复杂的ＳＱＬ语句
CREATE　VIEW　ｐｒｏｄｕｃｔｃｕｓｔｏｍｅｒ　ＡＳ
ＳＥＬＥＣＴ　ｃｕｓｔ＿ｎａｍｅ，ｃｕｓｔ＿ｃｏｎｔａｃｔ，ｐｒｏｄ＿ｉｄ
ＦＯＲＭ　ｃｕｓｔｏｍｅｒｓ，ｏｒｄｅｒｓ，ｏｒｄｅｒｉｔｅｍｓ
ＷＨＥＲＥ　ｃｕｓｔｏｍｅｒｓ．ｃｕｓｔ＿ｉｄ＝ｏｒｄｅｒｓ．ｃｕｓｔ＿ｉｄ
ＡＮＤ　ｏｒｄｅｒｉｔｅｍｓ．ｏｒｄｅｒ＿ｎｕｍ＝ｏｒｄｅｒｓ．ｏｒｄｅｒ＿ｎｕｍ；
返回已订购任意产品的所有客户的列表。
为了检索订购ＴＮＴ２产品的客户：
SELECT　ｃｕｓｔ＿ｎａｍｅ，ｃｕｓｔ＿ｃｏｎｔａｃｔ
ＦＲＯＭ　ｐｒｏｄｕｃｔｃｕｓｔｏｍｅｒｓ
ＷＨＥＲＥ prod_id=‘TNT2’;
利用视图可以一次性编写基础的SQL，然后多次使用。
创建可重用的视图：创建不受特定数据限制的视图是一种good idea。上面创建的视图返回生产所有的产品的客户列表，而不仅仅时TNT2的客户。

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

SELECT Contact (RTrim(vend_name),’(’,RTrim(vend_country),’)’)
AS vend_title
FROM vendors
ORDER BY vend_name;
经常用到名字+国家这种格式的数据，创建一个视图，需要的时候直接使用。
CREATE VIEW vendorlocation AS
SELECT Contact (RTrim(vend_name),’(’,RTrim(vend_country),’)’)
AS vend_title
FROM vendors
ORDER BY vend_name;
使用：
SELECT *
FROM vendorcountry;

22.2.3 用视图管理不需要的数据

视图对于应用普通的where语句很有用：定义一个customeremaillist视图，过滤没有邮件的客户：
CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

使用：
SELECT *
FROM customeremaillist ;

22.2.4 使用视图与计算字段

视图对于简化计算字段很有用：检索某个特定订单中的物品，计算每种物品的总价格：
SELECT prod_id,nums,price,nums*price AS expanded_price
FROM orderitems
WHERE order_num=20005;

CREATE VIEW orderitemsexpanded AS
SELECT prod_id,nums,price,nums*price AS expanded_price
FROM orderitems;
检索产品20005：
SELECT *
FROM orderitemsexpanded
WHERE order_num=20005;

22.2.5 更新视图

如果对视图增加，删除行，实际上是对基表增加和删除行。
不能确定被关系你的基数据，不能进行更新：
1）分组（GROUP BY 和 HAVING）；
2）联结；
3）子查询；
4) 并；
5）聚集函数
6）DISTINCT；
7）导出的（计算）列。
一般，将视图用于检索。

第23章使用存储过程

23.1 存储过程

执行这个处理需要针对许多表中的多条语句。执行语句次序也在变化。
存储过程就是以后使用而保存的一条或多条Mysql语句的集合。
简单，安全，高效
不能编写存储过程，但依然可以使用。

23.3 使用存储过程

23.3.1 执行存储过程

存储过程的执行称为调用，执行存储过程的语句称为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。
CALL productpricing（@pricelow,
@pricehigh,
@priceaverage）;
计算并返回产品的最高值，最低值和平均值。

23.3.2 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END:
备注：mysql命令行客户机的分隔符：
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) AS priceaverage
FROM products;
END//
DELIMITER;

使用：
CALL productpricing();
调用需要参数（）

23.3.3 删除存储过程

DROP PROCEDURE productpricing；
删除只是函数名字就可以

23.3.4 使用参数

一般的存储过程将结果返回给变量：
变量：内存中一个特定的位置，用来临时存储数据。
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
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:
关键字OUT指出相应的参数用来存储过程传出一个值（返回给调用者）。
Mysql支持IN（传递给存储过程）、OUT和INOUT（对存储过程传入和传出）的类型的参数。将检索到的值保存到相应的变量（指定关键字INTO）。
参数的数据类型：记录集不允许的类型
CALL productpricing(@pricelow
@pricehigh
@priceaverage);
存储过程将保存结果的3个变量的名字：
变量名：变量都必须以@开头。
不显示任何数据，但是可以显示（或在其他处理中使用）的变量；
SELECT @pricelow;
SELECT @pricelow,@pricehigh,@priceaverage;

做例子：ordertotal接受订单号并返回该订单的合计：
CREATE PROCEDURE ordertotal (
IN onumber TNT,
OUT ototal DECIMAL(8,2),
BEGIN
SELECT SUM(price*nums)
FROM orderitems;
WHERE ordeer_num=onumber
INTO ototal;
END:
onumber 使用在where选择正确的行，INTO将计算结果存储在ototal中。
CALL ordertotal(20005,@total);
第一个参数订单号，第二个参数包含计算出来的合计的变量名。
显示合计值：
SELECT @total;

CALL ordertotal(20009,@total);
SELECT @total;

23.3.5 建立智能存储过程

订单合计，需要对某些特定客户增加营业税：
1）获得合计（以前一样）；
2）把营业税有条件的添加到合计；
3）返回合计（带或不带税）。

– name:ordertotal
– Perameters:onumber=order number
– taxable=0 if not taxable,1 if taxable
– ototal=order total variable

CREATE PROCEDURE ordertotal(
IN onumber TNT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total ,Optionally adding tax’
BEGIN
– Declare variable for total
DECLRE total DECIMAL(8,2);
– Declare tax percentage
DECLRE taxrate INT DEFAULT 6;

– get the order total
SELECT SUM(price*nums)
FROM orderitems
WHERE order_num=onumber
INTO total;

– is this taxable?
IF taxable THEN
–yes,so add taxrate to the total
SELECT total+(total/100*taxrate)INTO total;
END IF;

– And finally,save to out variable
SELECT total INTO ototal;
END;
DECLARE语句定义两个局部变量。这时，SELECT存储带total
而不是ototal中。
IF 。。。 THEN
END IF；
OMMENT将在SHOW PROCEDURE STATUS中显示。

CALL ordertotal(20009,0,@total);
SELECT @total;
CALL ordertotal(20009,1,@total);
SELECT @total;
booleane类型。

23.3.6 检查存储过程

为了显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE 语句：
SHOW CREATE PROCEDURE ordertotal；
限制过程状态结果：
SHOW CREATE PROCEDURE LIKE ‘ordertotal’;

第24章使用游标

24.1 游标

需要在检索出来的行中前进或后退一行或多行**：游标（cursor）是一个存储在Mysql服务器库的数据库查询，它检索出来的结果集。应用程序可以滚动和浏览其中的数据。**
游标主要用于交互式应用，其中用户滚动在屏幕上的数据，并对数据进行浏览和更改。——只能用于存储过程（和函数）。

24.2 使用游标

1）使用游标之前，必须声明（定义）它；
2）一旦声明后，必须打开游标以供使用；
3）对于填有数据的游标看，根据需要检索各行；
4）在结束游标使用时，必须关闭游标。

24.2.1 创建游标

定义ordernumbers的游标，使用可以检索所有订单的SELECT语句：

CREATE PROCEDURE processorders(
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END:

24.2.2 打开和关闭游标

游标用OPEN CURSOR来打开：
OPEN ordernumbers；
CLOSE ordernumbers；
都需要关闭！
到达END语句，隐含关闭游标
CREATE PROCEDURE processorders(
BEGIN
–Declare cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
–Open the cursor
OPEN ordernumbers；
–close the cursor
CLOSE ordernumbers；
END:

24.2.3 使用游标数据

FETCH语句：分别访问游标中每一行数据；指定检索数据列，（哪里）表。向前移动游标内部行指针。
CREATE PROCEDURE processorders(
BEGIN
–declare local variable
DECLARE o INT;

--Declare cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

--Open the cursor
OPEN ordernumbers；

--get order number
FETCH ordernumbers INTO o;

--close the cursor
CLOSE ordernumbers；

END:
检索当前行order_num列（自动从第一行开始）到一个名为o的局部声明的变量中。

CREATE PROCEDURE processorders(
BEGIN
–declare local variable
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;

--Declare cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;**
--Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
--CONTINUE HANDLER,条件出来执行语句，SQLSTATE '02000' 是一个未找到条
--件，当repeat由于没有更多的行供循环而不能继续时，出现这个条件。设置done1

--Open the cursor
OPEN ordernumbers；

--loop through all rows
REPEAT
	--get order number
	FETCH ordernumbers INTO o;
	--FETCH反复执行直到done为真。默认done为假0.
	
--end of loop
UNTIL done END REPEAT;

--close the cursor
CLOSE ordernumbers；

END:
DECLARE 要求指定变量名和数据类型，支持可选的默认值。
DECLARE 次序：定义的局部变量必须在任意游标和CONTINUE HANDLER之前。CONTINUE HANDLER必须在游标之后定义
重复or循环？REPEAT语句适合对于游标进行循环

CREATE PROCEDURE processorders(
BEGIN
–declare local variable
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);

--Declare cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;**
--Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
--CONTINUE HANDLER,条件出来执行语句，SQLSTATE '02000' 是一个未找到条
--件，当repeat由于没有更多的行供循环而不能继续时，出现这个条件。设置done1

--create a table to store the result 
CREATE TABLE IF NOT EXISTS ordertotals
	(order_num INT,total DECIMAL(8,2));

--Open the cursor
OPEN ordernumbers；

--loop through all rows
REPEAT
	--get order number
	FETCH ordernumbers INTO o;
	--FETCH反复执行直到done为真。默认done为假0.

	--get the total for this order
	CALL ordertotal(o,1,t);

	--INSERT order and total into ordertotals
	INSERT INTO ordertotals (order_num,total)
	VALUES(o,t);
	
--end of loop
UNTIL done END REPEAT;

--close the cursor
CLOSE ordernumbers；

END:
创建一个新表，存储每个订单的订单号和合计。
SELECT *
FROM ordertotals；

第25章触发器

某条语句在事件发生时自动执行：
触发器为响应以下任意语句而自动执行的一条sql语句。（或位于BEGIN和END之间的一组语句）：
1)DELETE;
2)INSERT;
3)UPDATE;

25.2 创建触发器

给出4条信息：
1）唯一的触发器名字；（数据库范围内使用唯一名）
2）触发器关联的表；
3）触发器应该响应的活动（DELETE,INSERT或UPDATE）；
4）触发器合适执行（处理之前或之后）。
CREATE TRIGGER
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT ‘Product added’;
AFTER INSERT之后
FOR EACH ROW每个插入行执行
使用insert语句添加一行或多行到products中，将会看到对每个成功的插入，都会显示Product added消息。
仅支持表：——视图和临时表不行
触发器失败：
触发器每个表每个事件每次地定义，且只允许一个触发器。每个表最多6个触发器（3*2）.

25.3 删除触发器

DROP TRIGGER newprodut；
修改一个触发器，必须先删除，再重建

25.4 使用触发器

25.4.1 INSERT触发器

1）INSERT触发器可以引用一个名为NEW地虚拟表，访问被插入地行；
2）在BEFORE INSERT触发器中，NEW中地值可以被更新（允许更改被插入地值）；
3）对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含自动生成值。
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
在orders每次插入，使用触发器将返回新的订单号。

INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);
此时order_num还自动返回。

25.4.2 DELETE触发器

1）在DELETE触发器代码内，可以引用一个名为OLD地虚拟表，访问被删除的行；
2）OLD中的值全部都是只读，不能更新。
CREATE TRIGGER deleteorder BRFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
存档的表与orders列一致。多语句触发器：BEGIN。。。END;

25.4.3 UPDATE触发器

1）引用一个名为OLD的虚拟表访问以前（UPDATE语句之前）的值，引用一个NEW的虚拟表访问新更新的值；
2）在BEFORE UPDATE触发器中，NEW的值可能被更改（允许更改将要用于UPDATE语句中的值）；
3）OLD的值全是只读，不能更新。
例子保证州名都是大写（不管update语句中给出的时大写还是小写）
CREATE TRIGGER updateorder BRFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
任何数据净化都在UPDATE之前。每更新一个行，NEW.vend_state中的值都Upper(NEW.vend_state)代替

第26章管理事务处理

COMMIT和ROLLBACK

26.1 事务处理

事务处理（transaction processing）：用来维护数据库的完整性，保证成批的MySQL操作要么完成执行，要么完全不执行。
术语：
1）事务（transaction）：指一组SQL语句；
2）回退（rollback）：撤销指定SQL语句的过程；
3）提交（commit）：指将未存储的SQL语句结果写入数据库表；
4）保留点（savepoint）：指事务处理过程中设置的临时占位符（place_holder），可以对它发布回退（与回退整个事务不同）。

26.2 控制事务处理

START TRANSACTION
标识事务的开始

26.2.1 使用ROLLBACK

SELECT *FROM ordertotal;
START TRANSACTION;
DELETE FROM ordertotal;
SELECT * FROM ordertotal;
ROLLBACK;
SELECT * FROM ordertotal;
回退START TRANSACTION之后的所有语句，最后一句SELECT显示该表不为空。
SELECT，CREATE和DROP不能执行回退。

26.2.2 COMMIT

事务处理中，不会隐含提交，需要明确提交，使用COMMIT语句。
START TRANSACTION;
DELETE FROM orderitems WHERE order_num =20001;
DELETE FROM orders WHERE order_num =20001;
COMMIT;
COMMIT语句仅在不出错时写出更改。隐含事务关闭：当COMMIT和ROLLBACK语句执行后，事务会自动关闭。

26.2.3 使用保留点

复杂的事务处理可能需要部分提交和回退。
SAVEPOINT delete1;
ROLLBACK TO delete1;
保留点越多越好；释放保留点：RELEASE SAVEPOINT

26.2.4 更改默认提交行为

默认时自动提交所有更改。
指示Mysql不自动提交更改：
SET autocommit=0;
autocommit是针对每个连接而不是服务器。

第27章全球化和本地化

字符集和校对顺序；
使用字符集和校对顺序。

第28章安全管理

28.1 访问控制

不要使用ROOT登陆

28.2 管理用户

用户账号和信息存储在mysql的数据库中：
USE mysql;
SELECT user FROM user;

28.2.1 创建用户账号

CREATE USER ben IDENTIFIED BY ‘p@$$w0rd’;
RENAME USER ben TO hua;

28.2.2 删除用户账号

DROP USER hua;
删除用户账号和所有相关的账号权限。

28.2.3 设置访问权限

看到用户账号的权限，使用SHOW GRANTS FOR
SHOW GRANTS FOR hua；
结果：GRANT USAGE ON *.* TO ‘hua’ @ ‘%’
USAGE ON *.* 表示在任意数据库和任意表上没有权限。

为了设置权限，使用GRANT语句，给出下信息：
1）要授予的权限；
2）被授予访问权限的数据库或表；
3）用户名。

GRANT SELECT ON crashcourse . TO hua;
允许用户hua在crashcourse（数据库的所有表）上使用SELECT，即只读访问权限*

SHOW GRANTS FOR hua；
GRANT USAGE ON *.* TO ‘hua’ @ ‘%’
GRANT SELECT ON ‘crashcourse’ .* TO ‘hua’ @ ‘%’

撤销权限：REVOKE
REVOKE SELECT ON crashcourse . FROM hua;*

GRANT和REVOKE在几个层次上控制访问权限：
1）整个服务器，使用GRANT ALL 和REVOKE ALL;
2）整个数据库，使用ON database .*；
3）特定的表，使用ON database.table;
4）特定的列；
5）特定的存储过程。
简化多次授权：
GRANT SELECT,INSERT ON crashcourse. TO hua;*

权限表：
ALL；ALTER；ALTER ROUTINE-存储过程；
CREATE;CREATE ROUTINE;
CREATE TEMPORARY-临时表;CREATE USER;CREATE VIEW;
DELETE;DROP;
EXECUTE-使用CALL和存储过程
FILE：SELECT INTO OUTFILE和LOAD　DATA　ＩＮＦＩＬＥ
GRANT　ＯＰＴＩＯＮ－ＧＡＲＮＴ,REVOKE
INDEX-CREATE INDEX ,DROP INDEX
INSERT;
LOCK TABLES;
PROCESS-SHOW FULL PROCESSLIST
RELOAD-FLUSH
REPLICATION CLIENT;REPLICATION SLAVE
ELECT
SHOW DATADASE
SHOW VIEW
SHUTDOWN
SUPER
UPDATE
USAGE-无访问权限

28.2.4 更改口令

SET PASSWORD FOR hua = Password(‘n3w p@$$w0rd’);

SET PASSWORD = Password(‘n3w p@$$w0rd’);

第29章数据库的维护

29.1 数据备份

mysqldump;mysqlhotcopy;BACKUP TABLE ,SELECT INTO OUTFILE-RESTORE TABLE来复原。

29.2 数据库维护

ANALYZE TABLE orders;

CHECK TABLE orders,orderitems;
REPAIR TABLE-修复表格;OPTIMIZE TABLE-回收空间;

29.3 诊断启动问题

–help;–safe-mode;–verbose显示全文本消息；–version

29.4 查看日志文件

错误日志-hostname.err中。
查询日志-hostname.log
二进制日志-记录更新过数据的所有语句hostname_bin；
缓慢查询日志-hostname_slow.log.
全部位于data目录中

第30章改善性能

多用户多线程的DBMS——SHOW PROCESSLIST显示所有活动进程，KILL命令终结某个特定进程；LIKE慢使用FULLTEXT.

附录

MySQL知识
 软件安装；
样例表——管理供应商，管理产品目录，管理顾客列表；录入顾客订单
MySQL语法的语句
用于在一个或多个列上创建索引。
CREATE INDEX indexname
ON tablename(column [ASC|DESC],…)
MySQL数据类型
1）串数据类型

数据类型	说明
CHAR	1-255个字符长度。长度必须在创建时指定，否则默认char(1)
ENUM	最多接受64K个串组成的一个预定义集合的某个串
LONGTEXT	最大长度4GB
MEDIUMTEXT	16K
TEXT	64K变长文本
SET	最多接受64个串组成的一个预定义集合的零个或多个串
TINYTEXT	255字节
VARCHAR	长度可变，最多不超过255字节。VARCHAR（n），存储0-n个字符的变长串。

串——引号
数值不是数值——邮编01234——存储数值=1234

2）数值数据类型

数据类型	说明
BIT	位字段，1-64位
BIGINT	整数值，支-9223372036854775808-9223372036854775807(0-2^64)
BOOLEAN	0/1
DECIMAL	精度可变的浮点型
DOUBLE	双精度浮点型
FLOAT	单精度浮点型
INT	整数值，支持-2147483648-2147483647（0-2^32）
MEADIUMINT	整数值，支持-8388608-8388607（0-2^24）
REAL	4字节的浮点值
SMALLINT	整数值，支持-32768-32767（0-2^16）
TINYINT	支持-128-127（0-2^8）

不使用引号，存储货币数据类型——DECIMAL(8,2)
3)日期和时间数据类型

数据类型	说明
DATE	表示1000-01-01~9999-12-31的日期，格式YYYY-MM-DD
DATETIME	DATE与TIME的组合
TIMESTAMP	功能和DATETIME相同（但范围小）
TIME	格式HH:MM:SS
YEAR	用2位数字表示，范围是70（1970年）~69（2069年），用4位数字表示，范围是1901年到2155年

4）二进制数据类型
可以存储任何数据——图像，文字

数据类型	说明
BLOB	blob最大长度64KB
MEDIUMBLOB	Blob最大长度16MB
LONGBLOB	Blob最大长度4GB
TINYBLOB	Blob最大长度255字节

2020/8/14 学习整理

向您推荐>>Eolink开发者社区

权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区

更多推荐

沃云统一开发平台介绍

沃云集成平台研发平台介绍1.平台优势2.平台原理3.研发平台使用方法4.遇到的问题5.现阶段实现的功能6.后续需要补充的功能和优化内容研发平台介绍1.平台优势解决孤岛式应用，实现能力共享；现有系统框架过于复杂，跨系统业务处理成本居高不下，协同服务共享，降低运维成本；提高项目应用资源监控能力，改善资源利用率；业务微服务化，快速发布、快速部署，快速响应业务需求变化；沃云平台不仅提供了自动化的、可快速部

云原生

(20200916 Solved)docker-compose up创建容器自动退出

问题描述如题，创建容器后自动退出了。并且docker start container无效解决方案原因是缺失了控制终端的配置，需要在docker-compose.yml中增加tty:true ，有时候这样也不行，需要再增加一个command:/bin/bash，命令不一定是这个，需要是一个不会退出的命令，然后用-d后台启动容器。Referencesdocker-compose启动容器后自动退出...

云原生

基于docker的test-containers环境百宝箱

笔者语录：我开了个公众号【Java你我他】，欢迎大家关注。在很多时候，程序猿们更关注代码本身，而不愿意把时间花费在环境搭建上，这也是Docker变得越来越受欢迎的原因之一。test-containe是Docker生态圈中的一颗新星，其主要针对测试领域、背靠Docker实现环境百宝箱功能。 test-containers：你要的环境，我都有~ 假设我们现在需要一个redis-clust

云原生

所有评论(0)

查看更多评论

feng_xun123

@feng_xun123

已为社区贡献1条内容

mysql知会必会小节

feng_xun123

第一章

1.1数据库基础

1.1.1 数据库

1.1.2 表

1.1.3 列和数据类型

1.1.4 行

1.1.5 主键

1.2 什么是SQL

第二章

2.1.1 客户机-服务器软件

2.2 Mysql工具

第三章 使用mysql

3.1 连接

3.2 选择数据库

3.3 了解数据库和表

第四章 检索数据库

4.1 SELECT语句

4.2 检索单列

4.3 检索多列

4.4 检索所有列

4.5 检索不同的行

4.6 限制结果

4.7 使用完全限定的表名

第五章 排序检索数据

5.1 排序数据

5.2 按多个列排列

5.3 指定排序方向

第6章 过滤数据

6.1 使用where

6.2 where子句操作符

6.2.1 检查单个值

6.2.2 不匹配检查

6.2.3 范围值检查

6.2.4 空值检查 IS NULL

第7章 数据过滤（NOT IN）

7.1 组合where子句

7.1.1 AND操作符

7.1.2 OR操作符

7.1.3 计算次序

7.2 IN操作符

7.3 NOT操作符

第8章 用通配符进行过滤（LIKE）

8.1 LIKE操作符

8.1.1. 百分号（%）通配符

8.1.2 下划线（_）通配符

8.2 通配符使用技巧

第9章 用正则表达式进行搜索

使用Mysql正则表达式

9.2.2 进行OR匹配

9.2.3 匹配几个字符之一

9.2.4 匹配范围

9.2.5 匹配特殊字符

9.2.6 匹配字符类

9.2.7 匹配多个实例

9.2.8 定位符

第10章 创建计算字段

10.2 拼接字段

10.3 执行算术计算

第11章 使用数据处理函数

11.2 使用函数

11.2.1 处理文本串：文本处理函数

11.2.2 日期和时间处理函数

11.2.3 数值处理函数

第12章 汇总数据

12.1 聚集函数

AVG();COUNT();MAX();MIN();SUM()

12.1.1 AVG（）

12.1.2 COUNT

12.1.3 MAX

12.1.4 MIN

12.1.5 SUM

12.2 聚集不同值

组合聚集函数

第13 章 分组数据

13.2 创建分组

13.3 过滤分组

分组和排序

13.5 SELECT子句顺序

第三章使用mysql

第四章检索数据库

第五章排序检索数据

第6章过滤数据

第7章数据过滤（NOT IN）

第8章用通配符进行过滤（LIKE）

第9章用正则表达式进行搜索

第10章创建计算字段

第11章使用数据处理函数

第12章汇总数据

第13 章分组数据

15章联结表

第16章创建高级联结

第17章组合查询

第18章全文本搜索

第19章插入数据

第20章更新和删除数据

第21章创建和操纵表

第２２章使用视图

２２.１.２视图的规则和限制

２２.２.１利用视图简化联结

第23章使用存储过程

第24章使用游标