MySql___(6) MySQL 必知必会

第11章使用数据处理函数

11.1 函数

11.2 使用函数

用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数。
用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数。

返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数。

11.2.1 文本处理函数

输入

SELECT *, Upper(name) AS new_name FROM product_tb;

输出

+----+----------------+-------+------+------+----------------+
| id | name           | price | num  | unit | new_name       |
+----+----------------+-------+------+------+----------------+
|  1 | ACME           |  30.2 |   10 | 千克 | ACME           |
|  2 | Anvils R Us    | 100.5 |    1 | 千克 | ANVILS R US    |
|  3 | Furball Inc.   |  10.2 |    5 | 千克 | FURBALL INC.   |
|  4 | Jst Set        |   1.2 |  100 | 千克 | JST SET        |
|  5 | Jouets Et Ours |     1 |    8 | 千克 | JOUETS ET OURS |
|  6 | LT Supplies    |   1.1 |    6 | 千克 | LT SUPPLIES    |
+----+----------------+-------+------+------+----------------+

分析

Upper()将name列转换成大写，并且使用new_name新列显示。

常用的文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

11.2.2 日期和时间处理函数

常用日期和时间处理函数

函数	说明
AddDate()	增加一个日期(天、周等)
AddTime()	增加一个时间(时、分等)
CurDate()	返回当前日期
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

输入

 SELECT * FROM product_tb WHERE Date(date) ='2020-07-08';

输出

+----+-------------+-------+------+------+------------+
| id | name        | price | num  | unit | date       |
+----+-------------+-------+------+------+------------+
|  2 | Anvils R Us | 100.5 |    1 | 千克 | 2020-07-08 |
+----+-------------+-------+------+------+------------+

假如你想检索出日期区间的数据，可以如下操作:

输入

SELECT * FROM product_tb WHERE Date(date) BETWEEN '2020-04-01' AND '2020-12-01';

输出

+----+----------------+-------+------+------+------------+
| id | name           | price | num  | unit | date       |
+----+----------------+-------+------+------+------------+
|  2 | Anvils R Us    | 100.5 |    1 | 千克 | 2020-06-08 |
|  3 | Furball Inc.   |  10.2 |    5 | 千克 | 2020-05-01 |
|  5 | Jouets Et Ours |     1 |    8 | 千克 | 2020-04-11 |
|  6 | LT Supplies    |   1.1 |    6 | 千克 | 2020-09-03 |
+----+----------------+-------+------+------+------------+

分析

其中,BETWEEN操作符用来把2020-04-01和2020-12-01定义为一个要匹配的日期范围。

11.2.3 数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

第12章汇总数据

12.1 聚集函数

确定表中行数(或者满足某个条件或包含某个特定值的行数)。
获得表中行组的和。
找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。

SQL聚集函数

函数说明

AVG() 返回某列的平均值

COUNT() 返回某列的行数

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUN() 返回某列值之和

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUN()	返回某列值之和

12.1.1 AVG()函数

使用AVG()返回product_tb表中所有产品的平均价格

输入

SELECT AVG(price) AS avg_price FROM product_tb;

输出

+--------------------+
| avg_price          |
+--------------------+
| 24.033333333333328 |
+--------------------+

指定特定类型产品的平均价格

输入

SELECT AVG(price) AS avg_price FROM product_tb WHERE Date(date) BETWEEN '2020-04-01' AND '2020-12-01';

输出

+-----------+
| avg_price |
+-----------+
|      28.2 |
+-----------+

12.1.2 COUNT()函数

COUNT()函数进行计数.可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式。

使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。

输入

SELECT COUNT(*) AS num_cust FROM  product_tb;

输出

+----------+
| num_cust |
+----------+
|        6 |
+----------+

分析

在此例子中,利用COUNT()*对所有行计数,不管行中各列有什么值。计数值在num_cust中返回。

12.1.3 MAX()函数

输入

SELECT MAX(price) AS max_price FROM  product_tb;

输出

+----------+
| max_price |
+----------+
|    100.5 |
+----------+

分析

这里,MAX()返回product_tb表中最贵的物品的价格。

12.1.4 MIN()函数，同上

12.1.5 SUM函数

输入

SELECT SUM(price) AS sum_price FROM product_tb;

输出

+--------------------+
| sum_price          |
+--------------------+
| 144.19999999999996 |
+--------------------+

12.2 聚集不同值

12.3 组合聚集不同值

输入

SELECT COUNT(*) AS num_items,MiN(price) AS price_min,MAX(price) AS price_max,AVG(price) AS price_avg FROM product_tb;

输出

+-----------+-----------+-----------+--------------------+
| num_items | price_min | price_max | price_avg          |
+-----------+-----------+-----------+--------------------+
|         6 |         1 |     100.5 | 24.033333333333328 |
+-----------+-----------+-----------+--------------------+

第13章数据分组

13.1 数据分组

输入

SELECT COUNT(*) AS num_count FROM product_tb WHERE vend_id=1002;

输出

+-----------+
| num_count |
+-----------+
|         2 |
+-----------+

13.2 创建分组

输入

SELECT vend_id, COUNT(*) AS num_prods FROM product_tb GROUP BY vend_id;

输出

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         2 |
|    1002 |         2 |
|    1003 |         2 |
+---------+-----------+

分析

上面的SELECT语句指定了两个列,vend_id包含产品供应商的ID,num_prods为计算字段(用COUNT(*)函数建立)。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。从输出中可以看到,供应商1001有2个产品,供应商1002有2个产品,供应商1003有2个产品。

13.3 过滤分组

HAVING 过滤分组，HAVING支持所有WHERE操作符。

13.4 分组和排序

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

13.5 SELECT子句顺序

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

第14章使用子查询

14.1 子查询

即嵌套在其他查询中的查询。

14.2 利用子查询进行过滤

14.3 作为计算字段使用子查询

输入

SELECT *,(SELECT COUNT(*) FROM product_tb WHERE product_tb.price=book_info.price) AS ordes FROM book_info ORDER BY name;

输出

+----+----------------------+-------+-----------+-----------+-------+
| id | name                 | price | bookCount | author    | ordes |
+----+----------------------+-------+-----------+-----------+-------+
| 17 | 1 书名a              |    30 |        60 | 作者A     |     4 |
|  2 | 1111                 |    30 |        50 | c         |     4 |
| 18 | 2书名b               |    50 |       200 | 作者B     |     0 |
| 19 | 3书名c               |    20 |        63 | 作者C     |     0 |
| 21 | 5书名                |    20 |        60 | 作者4     |     0 |
| 20 | 9书名d               |    37 |        50 | 作者三    |     0 |
|  6 | DD                   |    35 |        10 | 辛夷坞    |     0 |
|  1 | JAVAWeb              |  20.6 |        10 | b         |     0 |
| 22 | 书.名                |    15 |        66 | 作者五    |     0 |
|  5 | 名称一               |  30.5 |        20 | 安安      |     0 |
|  9 | 名称三               |    58 |        60 | back back |     0 |
|  3 | 名称二               |    80 |        77 | 我的作者  |     0 |
|  8 | 平凡的世界           |    40 |         0 | 路遥      |     3 |
| 14 | 我是作者             |    58 |        60 | 中文测试  |     0 |
|  4 | 或者                 |    30 |        40 | 余华      |     4 |
| 16 | 测试                 |  40.5 |       100 | 中        |     0 |
| 15 | 稳稳当当             |    40 |       100 | A         |     3 |
|  7 | 致我们终将逝去的青春 |    40 |         0 | 辛夷坞    |     3 |
+----+----------------------+-------+-----------+-----------+-------+

第15章联结表

15.1 联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作,很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

15.1.1 关系表

理解关系表的最好方法是来看一个现实世界中的例子。

假如有一个包含产品目录的数据库表,其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格,以及生产该产品的供应商信息。现在,假如有由同一供应商生产的多种物品,那么在何处存储供应商信息(如,供应商名、地址、联系方法等)呢?将这些数据与产品信息分开存储的理由如下。

因为同一供应商生产的每个产品的供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空间。
如果供应商信息改变(例如,供应商搬家或电话号码变动),只需改动一次即可。
如果有重复数据(即每种产品都存储供应商信息),很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

在这个例子中,可建立两个表,一个存储供应商信息,另一个存储产品信息。vendors表包含所有供应商信息,每个供应商占一行,每个供应商具有唯一的标识。此标识称为主键(primary key)〈在第1章中首次提到3,可以是供应商ID或任何其他唯一值。

products表只存储产品信息,它除了存储供应商ID(vendors表的主键)外不存储其他供应商信息。vendors表的主键又叫作products的外键,它将vendors表与products表关联,利用供应商ID能从vendors表中找出相应供应商的详细信息。

**外键(foreign    key)**外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。|

这样做的好处如下:

* 供应商信息不重复,从而不浪费时间和空间;

* 如果供应商信息变动,可以只更新vendors表中的单个记录,相关表中的数据不用改动;

* 由于数据无重复,显然数据是一致的,这使得处理数据更简单。总之,关系数据可以有效地存储和方便地处理。因此,关系数据库的可伸缩性远比非关系数据库要好。

**可伸缩性(scale)**能够适应不断增加的工作量而不失败。设
  计良好的数据库或应用程序称之为可伴缩性好(scalewell)。

15.1.2 为什么要使用联结

联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。

15.2 创建联结

输入

SELECT bookName,bookCount,bookPrice FROM book_info,product_tb WHERE book_info.vend_id=product_tb.vend_id ORDER BY bookName;

输出

+----------------------+-----------+-----------+
| bookName             | bookCount | bookPrice |
+----------------------+-----------+-----------+
| 1 书名a              |        60 |        30 |
| 1 书名a              |        60 |        30 |
| 2书名b               |       200 |        50 |
| 2书名b               |       200 |        50 |
| 3书名c               |        63 |        20 |
| 3书名c               |        63 |        20 |
| 5书名                |        60 |        20 |
| 5书名                |        60 |        20 |
| 9书名d               |        50 |        37 |
| 9书名d               |        50 |        37 |
| DD                   |        10 |        35 |
| DD                   |        10 |        35 |
| DD                   |        10 |        35 |
| 书.名                |        66 |        15 |
| 书.名                |        66 |        15 |
| 名称三               |        60 |        58 |
| 名称三               |        60 |        58 |
| 名称三               |        60 |        58 |
| 平凡的世界           |         0 |        40 |
| 平凡的世界           |         0 |        40 |
| 平凡的世界           |         0 |        40 |
| 我是作者             |        60 |        58 |
| 我是作者             |        60 |        58 |
| 测试                 |       100 |      40.5 |
| 测试                 |       100 |      40.5 |
| 稳稳当当             |       100 |        40 |
| 稳稳当当             |       100 |        40 |
| 致我们终将逝去的青春 |         0 |        40 |
| 致我们终将逝去的青春 |         0 |        40 |
| 致我们终将逝去的青春 |         0 |        40 |
+----------------------+-----------+-----------+

完全限定列名在引用的列可能出现二义性时,处须使用完全限定列名(用一个点分隔的表名和列名)如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误.

15.2.1 WHERE 子句的重要性

15.2.2 内部联结

15.2.3 联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系

第16章创建高级联结

16.1 使用表别名

缩短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';

分析

可以看到,FROM子句中3个表全都具有别名。customers AS c建立c作为customers的别名,等等。这使得能使用省写的c而不是全名customers。在此例子中,表别名只用于WHERE子句。但是,表别名不仅能用于\HERE子句,它还可以用于SELECT的列表、0RDER BY子句以及语句的其他部分。

16.2 使用不同类型的联结

16.2.1 自联结

假如你发现某物品(其ID为DTNTR)存在问题,因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商,然后找出这个供应商生产的其他物品。

方法一，子查询

输入

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';

分析

此查询中需要的两个表实际上是相同的表,因此products表在FROM子句中出现了两次。虽然这是完全合法的,但对products的引用具有二义性,因为MySQL不知道你引用的是products表中的哪个实例。
为解决此问题,使用了表别名。products的第一次出现为别名p1,第二次出现为别名p2。现在可以将这些别名用作表名。

16.2.2 自然联结

16.2.3 外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如,可能需要使用联结来完成以下工作:

对每个客户下了多少订单进行计数,包括那些至今尚未下订单的客户;
列出所有产品以及订购数量,包括没有人订购的产品;
计算平均销售规模,包括那些至今尚未下订单的客户。

下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单:

SELECT customers.cust_id,order.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id;

外部联结语法类似。为了检索所有客户,包括那些没有订单的客户,可如下进行:

输入

SELECT customers.cust_1id,o0rders.order_num FROM customers LEFT 0UTER JOIN orders 0N customers.cust_id=orders.cust_id;

输出

+----+----+
|cust_id|order_num|
+--------+----+
|10001| 20005|
|10001| 20009|
|10002| NULL|
|10003| 20006|
|10004| 20007|
|10005| 20008|
+--------+----+

16.3 使用带聚集函数的联结

输入

SELECT book_info.bookName,book_info.vend_id,Count(book_info.bookCount) AS book_count FROM book_info INNER JOIN product_tb ON book_info.vend_id=product_tb.vend_id;

16.4 使用联结和联结条件

注意所使用的联结类型。一般我们使用内部联结,但使用外部联
结也是有效的。
保证使用正确的联结条件,否则将返回不正确的数据。
应该总是提供联结条件,否则会得出笛卡儿积.
在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结。这将使故障排除更为简单。

第17章

17.1 组合查询

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

17.2 创建组合查询

17.2.1 使用UNION

创建UNION涉及编写多条SELECT语句，首先来看单条语句;

输入

 SELECT bookName,bookPrice, vend_id FROM book_info WHERE bookPrice <=30;

输出

+----------+-----------+---------+
| bookName | bookPrice | vend_id |
+----------+-----------+---------+
| JAVAWeb  |      20.6 |    1004 |
| 1111     |        30 |    1004 |
| 或者     |        30 |    1004 |
| 1 书名a  |        30 |    1002 |
| 3书名c   |        20 |    1001 |
| 5书名    |        20 |    1001 |
| 书.名    |        15 |    1001 |
| 书名e    |        30 |    1005 |
+----------+-----------+---------+

输入

SELECT name ,vend_id,price FROM product_tb WHERE vend_id IN(1001,1003);

输出

+----------------+----+---------+-------+
| name           | id | vend_id | price |
+----------------+----+---------+-------+
| ACME           |  1 |    1001 |    30 |
| Anvils R Us    |  2 |    1001 |    30 |
| Jouets Et Ours |  5 |    1003 |    40 |
| LT Supplies    |  6 |    1003 |    40 |
| Adb            |  7 |    1003 |    40 |
+----------------+----+---------+-------+

分析

第一条SELECT检索价格不高于30的所有物品。第二条SELECT使用IN找出供应商1001和1003生产的所有物品。

使用UNION组合

输入

 SELECT bookName,bookPrice, vend_id FROM book_info WHERE bookPrice <=30 UNION SELECT name ,price,vend_id FROM product_tb WHERE vend_id IN(1001,1003);

输出

+----------------+-----------+---------+
| bookName       | bookPrice | vend_id |
+----------------+-----------+---------+
| JAVAWeb        |      20.6 |    1004 |
| 1111           |        30 |    1004 |
| 或者           |        30 |    1004 |
| 1 书名a        |        30 |    1002 |
| 3书名c         |        20 |    1001 |
| 5书名          |        20 |    1001 |
| 书.名          |        15 |    1001 |
| 书名e          |        30 |    1005 |
| ACME           |        30 |    1001 |
| Anvils R Us    |        30 |    1001 |
| Jouets Et Ours |        40 |    1003 |
| LT Supplies    |        40 |    1003 |
| Adb            |        40 |    1003 |
+----------------+-----------+---------+

17.2.2 UNION规则

UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句,将要使用3个UNION关键字)。
UNION中的每个查询必须包含相同的列、表达式或聚集函数〔不过各个列不需要以相同的次序列出)。
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。

17.2.3 UNION ALL包含重复行

17.2.4 对组合查询结果排序

ORDER BY

第18章全文本搜索

18.1 理解全文本搜索

18.2 使用全文本搜索

18.2.1启用全文本搜索支持

一般在创建表时启用全文本搜索，CRETE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

输入

CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

为了进行全文本搜索,MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列,如果需要也可以指定多个列。

不要在导入数据时使用FULLTEXT更新索引要花时间,虽然不是很多,但毕竟要花时间。如果正在导入数据到一个新表,此时不应该启用FULLTEXT索引。应该首先导入所有数据,然后再修改表,定义FULLTEXT。这样有助于更快地导入数据(而丁使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。

18.2.2 进行全文本搜索

在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。

输入

 SELECT note_text FROM productnotes WHERE Match(note_text) Against('love');

输出

+-------------------------------------------------+
| note_text                                       |
+-------------------------------------------------+
| I love thee to the depth and breadth and height |
+-------------------------------------------------+

搜索不区分大小写 除非使用BINARY方式，否则全文搜索不区分大小写。

也可以简单的使用LIKE子句完成

输入

SELECT note_text FROM productnotes WHERE note_text LIKE '%love%';

18.2.3 使用查询扩展

18.2.4 布尔文本搜索

为了匹配包含heavy但不包含任意以rope开始的词的行,可使用以下查询:

输入

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -repe*' IN BOOLEAN MODE) ;

全文布尔操作符

布尔操作符	说明
+	包含,词必须存在
-	排除,词必须不出现
>	包含,而且增加等级值
<	包含,丁减少等级值
()	把词组成子表达式〔允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

第19章插入数据

19.1 数据插入

插入完整的行;
插入行的一部分;
插入多行;
插入某些查询的结果。

19.2插入完整的行

输入
```
INSERT INTO book_info VALUES(NULL,'名字',16.5,100,'作者',1003);
```
没有输出 INSERT语句一般不会产生输出。

分析

此例子插入一个新客户到book_info表。存储到每个表列中的数据在VALUES子句中给出,对每个列必须提供一个值。如果这个列没有值应该使用NULL值(假定表允许对该列指定空值)。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时,该列由MySQL自动增量。你不想给出一个值(这是MySQL的工作),又不能省略此列(如前所述,必须给出每个列),所以指定一个NULL值(它被MySQL忽略,MySQL在这里插入下一个可用的cust_id值)。

虽然这种语法很简单,但并不安全,应该尽量避免使用。上面的SQL]语句高度依赖于表中列的定义次序,并东还依赖于其次序容易获得的信息。即使可得到这种次序信息,也不能保证下一次表结构变动后各个列保持完全相同的次序。因此,编写依赖于特定列次序的SQL语句是很不安全的。如果这样做,有时难免会出问题。

更安全的插入

输入

INSERT INTO book_info (id,bookName,bookPrice,bookCount,author,vend_id) 
VALUES(NULL,'名字1',18.5,190,'作者二',1003);

19.3 插入多个行

输入

INSERT INTO book_info (id,bookName,bookPrice,bookCount,author,vend_id) 
VALUES(NULL,'名字1',18.5,190,'作者二',1003),(NULL,'解忧杂货店',56.5,190,'东野圭吾',1003);

19.5 插入检索的值

输入

INSERT INTO book_info (id,bookName,bookPrice,bookCount,author,vend_id) 
SELECT id,bookName,bookPrice,bookCount,author,vend_id FROM custnew ;

第20章

20.1 更新数据

为了更新(修改)表中的数据,可使用UPDATE语句。

更新表中特定行;
更新表中所有行。

步骤

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

UPDATE book_info SET bookName ='红楼梦' WHERE id=22;

更新更多列

UPDATE book_info SET bookName ='西游记',bookPrice='66' WHERE id=23;

20.2 删除数据

可以两种方式使用DELETE:

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

输入

DELETE FROM book_info WHERE id =1;

20.3 更新和删除的指导原则

除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键(如果忘记这个内容,请参阅第15章》,尼可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WMHERE子句不正确。

使用强制实施引用完整性的数据库(关于这个内容,请参阅第15章),这样MySQL将不允许删除具有与其他表相关联的数据的行。

第21章创建和操作表

21.1.1 表创建基础

输入

CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

21.1.2 使用NULL值

每个表列或者是NULL列,或者是NOT NULL列,这种状态在创建时由表的定义规定。

21.1.3 主键再介绍

PRIMARY KEY (note_id)

为创建多个列组成的主键，应该以逗号分隔的列表给出各列名。

21.1.4 使用 AUTO_INCREMENT

ID自增长

21.1.5 指定默认值

输出

CREATE TABLE users(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY   KEY(order_num,order_item)
)ENGINE=InnoDB;

21.1.6 引擎类型

InnoDB是一个可靠的事务处理引擎(参见第26章),它不支持全文本搜索;
MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表):;
MyISAM是一个性能极高的引擎,它支持全文本搜索(参见第18章),但不支持事务处理。

21.2 更新表

为更新表定义,可使用ALTERTABLE语句。但是,理想状态下,当表中存储数据以后,该表就不应该再被更新。

为了使用ALTER TABLE更改表结构,必须给出下面的信息:
- 在ALTERTABLE之后给出要更改的表名(该表必须存在,否则将
  出错);
- 所做更改的列表。

输入

ALTER TABLE book_info ADD publication_num CHAR(20);

分析

这条语句给book_info表增加一个名为publication_num的列,必须明确其数据类型。

删除刚刚添加的列，可以这样做：

输入

 ALTER TABLE book_info DROP COLUMN publication_num;

21.3 删除表

输入

DROP TABLE book_info;

21.4 重命名表

使用RENAME TABLE语句可以重命名一个表:

输入

RENAME TABLE book_info TO book_info2;

分析

RENAME TABLE所做的仅是重命名一个表。可以使用下面的语句对多个表重命名：

RENAME TABLE backup_customers TO Customers,backup_vendors TO vendors,
backup_products   TO products;

第22章使用视图

22.1 视图

需要MySQL 5 MySQL5添加了对视图的支持。因此，本章内容适用于MySQL5以以后的版本。

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

22.1.1 为什么使用视图

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

22.1.2 视图的规则和限制

与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
对于可以创建的视图数目没有限制。
为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
RDERBY可以用在视图中,但如果从该视图检索数据SELECT中也含有0RDERBY,那么该视图中的O0RDERBY将被覆盖。
视图不能索引,也不能有关联的触发器或默认值。
视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。

22.2 使用视图
- 视图用CREATE VIEW语句来创建。
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
- 用DROP删除视图,其语法为DROP VIEW viewname;。
- 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE 0R REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。
22.2.1 利用视图简化复杂的联结

输入

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

22.2.3 用视图过滤不想要的数据

22.2.4 使用视图与计算字段

将其转换为一个视图

输入

 CREATE VIEW mybookview1  AS SELECT id,bookName,bookPrice,bookCount,author,vend_id,bookPrice*bookCount AS total FROM book_info WHERE vend_id=1003;

通过视图查询

输入

SELECT * FROM mybookview1 WHERE vend_id=1003;

输出

+----+----------------------+-----------+-----------+-----------+---------+-------+
| id | bookName             | bookPrice | bookCount | author    | vend_id | total |
+----+----------------------+-----------+-----------+-----------+---------+-------+
|  6 | DD                   |        35 |        10 | 辛夷坞    |    1003 |   350 |
|  7 | 致我们终将逝去的青春 |        40 |         0 | 辛夷坞    |    1003 |     0 |
|  8 | 平凡的世界           |        40 |         0 | 路遥      |    1003 |     0 |
|  9 | 名称三               |        58 |        60 | back back |    1003 |  3480 |
| 24 | 名字                 |      16.5 |       100 | 作者      |    1003 |  1650 |
| 25 | 名字1                |      18.5 |       190 | 作者二    |    1003 |  3515 |
| 26 | 名字1                |      18.5 |       190 | 作者二    |    1003 |  3515 |
| 27 | 解忧杂货店           |      56.5 |       190 | 东野圭吾  |    1003 | 10735 |
+----+----------------------+-----------+-----------+-----------+---------+-------+

22.2.5 更新视图

如果视图定义中有以下操作,则不能进行视图的更新:

分组(使用GROUPBY和HAVING);
联结;
子查询;
并;
聚集函数(Min()、Count()、Sum()等);
DISTINCT:;
导出(计算)列。

第23章使用存储过程

23.1 存储过程

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。

23.2 为什么要使用存储过程

23.3 使用存储过程

23.3.1 执行存储过程

输入

CALL productpricing(@pricelow,@pricehigh,@priceeaverage);

分析

其中,执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格。

23.3.2 创建存储过程

输入

CREATE PROCEDURE productpricing() 
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

分析

此存储过程名为 productpricing，用CREATE PROCEDURE productpricing() 语句定义。

如何使用这个存储过程?

输入

CALL productpricing();

23.3.3 删除存储过程

输入

DROP PROCEDURE productpricing;

使用参数

23.3.5 建立智能存储过程

23.3.6检查存储过程

第24章使用游标

24.1游标

MYSQL游标只能用于存储过程(和函数)。

24.2 使用游标

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

24.2.1 创建游标

24.2.2 打开关闭游标

24.2.3 使用游标数据

第25章使用触发器

25.1 触发器

触发器，在某个表发生更改时自动处理。
支持触发器语句

DELETE
INSERT
UPDATE

25.2创建触发器

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动（DELETE、INSERT或UPDATE）；
触发器何时执行（处理之前或之后）。

输入
```
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
```
分析

CREATETRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行,这里给出了AFTERINSERT,所以此触发器将在INSERT语句成功执行后执行。这个触发器远指定FOREACHROW,因此代码对每个插入行执行。在这个例子中,文本Productadded将对每个插入的行显示一次。

25.3 删除触发器

输入

DROP TRIGGER newproduct;

分析

触发器不能更新或覆盖。为了修改一个触发器,必须先删除它,然后再重新创建。

25.4 使用触发器

25.4.1 INSERT触发器

在INSERT触发器代码内,可引用一个名为NEW的虑拟表,访问被捍入的行;
在BEFOREINSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。

输入

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

分析

此代码创建一个名为neworder的触发器,它按照AFTER INSERT 0N orders执行。在插入一个新订单到orders表时,MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行,因为在BEFORE INSERT语句执行之前,新order_num还没有生成。
对于orders的每次插入使用这个触发器将总是返回新的订单号。

25.4.2 DELETE触发器

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 158,736评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,167评论 1赞 291
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,442评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,902评论 0赞 204
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,302评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,573评论 1赞 216
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,847评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,562评论 0赞 197
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,260评论 1赞 241
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,531评论 2赞 245
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,021评论 1赞 258
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,367评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,016评论 3赞 235
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,068评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,827评论 0赞 194
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,610评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,514评论 2赞 269