MYSQL语句

最新推荐文章于 2024-08-02 22:16:19 发布

wqjsmile

最新推荐文章于 2024-08-02 22:16:19 发布

阅读量354

点赞数

分类专栏： java笔记文章标签： mysql

本文链接：https://blog.csdn.net/qq_39917365/article/details/112547853

版权

java笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文是学习《MySQL必知必会》过程中记录的学习笔记，与大家分享~

《MySQL必知必会》链接：https://pan.baidu.com/s/1JV48foIMJpDaa8QjAqDBeg 提取码：7e9e

作用：选择数据库
输入：USE 数据库名;
输出：Database changed

作用：了解数据库和表
输入：SHOW DATABASES;
输出：返回当前选择的数据库的列表

作用：获得数据库内表的列表
输入：SHOW TABLES;
输出：返回当前选择的数据库内可用表的列表

作用：显示表列
输入：SHOW COLUMNS FROM 选定列表;
输出：针对指定的表返回一行，行中包含字段名、数据类型、是否允许NULL等信息
# DESCRIBE 选定列表; 是SHOW COLUMNS FROM 选定列表;的快捷方式

检索数据，必须至少给出两条信息——想选择什么，以及从什么地方选择

作用：检索单个列
输入：SELECT prod_name FROM products;
输出：从products表中检索出一个名为pro_name的列。

作用：检索多个列
输入：SELECT pro_id, pro_name, pro_price FROM products;
输出：从products表中检索出3个列。

作用：检索所有列
输入：SELECT * FROM products;
输出：返回表中的所有列

作用：检索不同内容的行(根据指定列)
输入：SELECT DISTINCT vend_id FROM products;
输出：只返回不同(唯一)的行。而且 DISTINCT 必须直接放在列名的前面。

作用：检索第一行or前几行(根据指定列)
输入：SELECT prod_name FROM products LIMIT 5;
输出：检索单个列，而且返回不多于5行(从第一行开始)

作用：检索某一连续的行(根据指定列)
输入：SELECT pro_name FROM products LIMIT 5, 5;
输出：检索单个列，而且返回从第5行开始的5行(带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。)

SELECT products.prod_name FROM products;
等价于：SELECT products.pro_name FROM products;
等价于：SELECT products.prod_name FROM crashcourse.products;

排序检索数据

作用：排序用 SELECT 语句检索出的数据
输入：SELECT prod_name FROM products ORDER BY prod_name;
输出：对prod_name列以字母顺序排序数据（按选择检索的列排序）

作用：按多个列排序。
输入：SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
输出：仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。(默认从A-Z)

作用：指定排序顺序。只能指定一个列
输入：SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
输出：DESC是降序排列，ASC是升序排列

作用：按多个列排序
输入：SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
输出：DESC关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字

作用：选出一个列中最高或最低的值
输入：SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
输出：

过滤数据

作用：根据WHERE语句中指定的搜索条件进行过滤，确定一个列是否包含特定的值。
输入：SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
输出：从products表中检索两个列，但不返回所有行，只返回prod_price值为2.50的行。
PS：在同时使用ORDER BY 和WHERE子句时，应该让ORDER BY 位于WHERE之后，否则将会产生错误。

作用：检查某个范围的值
输入：SEKECT prod_name, prod_price FROM products WHERE prod price BETWEEN 5 AND 10;
输出：检索价格在5美元和10美元之间的所有产品。

作用：检查具有NULL值的列。
输入：SELECT cust_id FROM customers WHERE cust_email IS NULL;
输出:

作用：通过组合WHERE子句来进行更强的过滤控制
输入：SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
输出：检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

作用：通过组合WHERE子句来进行更强的过滤控制
输入：SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
输出：检索由任一个指定供应商制造的所有产品的产品名和价格。

作用：组合 AND 和 OR
输入：SELECT pro_name, prod_price WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
输出：SQL在处理 OR 操作符时，优先处理 AND 操作符。SQL对上面句子的理解是，由供应商1003制造的任何价格为10美元以上的产品或者由供应商1002制造的任何产品，而不管其价格如何。

作用：针对SQL优先处理 AND 的解决方案
输入：SELECT pro_name, prod_price WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
输出：

作用：指定条件范围，范围中的每个条件都可以进行匹配。
输入：SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
输出：检索供应商1002到1003制造的所有产品，并按照pro_name排序。

作用：否定之后所有跟的任何条件。
输入：SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN(1002, 1003) ORDER BY prod_name;
输出：MYSQL不是匹配1002和1003的vend_id，而是匹配1002和1003之外供应商的vend_id。

通配符 LIKE

作用：利用通配符创建比较特定的搜索模式，所谓通配符是指用来匹配值的一部分的特殊字节。此时需要用到 LIKE
输入：SELECT pro_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
输出：使用搜索模式'jet%',将检索任何以jet起头的词，%告诉MYSQL接受jet之后的任意字符，不管它有多少字符。
Ps:通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

作用：匹配任何位置包含特定文本的值。
输入：SELECT prod_id, prod_name FROM products WHERE pro_name LIKE '%anvil%';
输出：匹配任何位置包含anvil的值。

作用：
输入：SELECT prod_name FROM products WHERE products WHERE pro_name LIKE 's%e';
输出：找出以s起头以e结尾的所有产品。
PS：通配符%可以匹配任何东西，但无法匹配NULL。

通配符下划线_

作用：下划线的用途跟%一样，但下划线只匹配单个字符而不是多个字符。
输入：SELECT pro_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
	 SELECT pro_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil'; 
输出：与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

正则表达式

作用：匹配文本，将一个模式与一个文本串进行比较。不分大小写。
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
输出：REGEXP告诉MySQL，REGEXP后所跟的东西作为正则表达式(与文字正文1000匹配的一个正则表达式)处理。

作用：
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
输出：正则表达式.000，匹配到了1000和2000.

作用：
输入：SELECT prod_name FROM products WHERE pro_name REGEXP '1000|2000' ORDER BY prod_name;
输出：两个以上的条件可以用 OR 条件

作用：
输入：SELECT prod_name FROM prod_name REGEXP '[123] Ton' ORDER BY prod_name;
输出：这里使用了正则表达式 [123] Ton, [123]定义了一组字符，意思是匹配1或2或3，因此这句话意思是返回1 ton or 2 ton or 3 ton

作用：使用正则表达式寻找一个范围的值
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
输出：正则表达式[1-5] Ton. [1-5]定义了一个范围，意思是匹配1到5

作用：正则表达式匹配特殊字符
输入：SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
输出：为了匹配特殊字符，必须用\\为前导，\\.的意思是查找'.'，如果没有\\，则无法正常匹配。
Ps:为匹配反斜杠\字符本身，需要使用\\\

作用：匹配多个实例
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
输出：正则表达式\\([0-9] sticks?\\)中，\\(匹配(,[0-9]匹配任意数字，sticks?匹配stick和sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)，\\)匹配)。没有?，匹配stick和sticks会非常困难。

作用：匹配连在一起的4位数字
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
输出：[:digit:]匹配任意数字，因而它是数字的一个集合。{4}要求它前面的字符(任意数字)出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

作用：匹配特定位置的文本
输入：SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
输出：^匹配串的开始，因此^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。
Ps:^有两种用法，在集合中(用[和]定义)，用^来否定该几何，否则用来指串的开始处。

十、计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，比如，如果想在一个字段中既显示公司名，又现实公司地址，但这两个信息一般包含在不同的表列中。这就是计算字段发挥作用的地方了。计算字段并不实际存在于数据库表中，计算字段是运行时在SELECT语句内创建的。字段基本上与列的意思相同，经常互换使用，不过数据库一般称为列，字段通常用在计算字段的连接上。

作用：拼接字段，用Concat()实现拼接，多数DBMS使用+或\\来实现拼接
输入：SELECT Concat(vend_name, '(', vend_Country, ')') FROM vendors ORDER BY vend_name;
输出：Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

作用：删除数据左右侧多余的空格来整理数据
输入：SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;
     SELECT Concat(LTrim(vend_name), '(', LTrim(vend_country), ')') FROM vendors ORDER BY vend_name;
输出：RTrim删除右侧多余的空格，LTrim删除左侧多余的空格。

作用：使用别名，别名是一个字段或值的替换名，用AS关键字赋予，别名有时候也叫导出列
输入：SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
输出：AS vend_title指示SQL创建一个包含指定计算的名为vend_title的计算字段。

作用：对检索出的数据进行算术计算，MYSQL支持基本算术操作符+、-、*、/.
输入：SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
输出：expanded_price列为一个计算字段，计算为quantity*item_price。

十一、使用数据处理函数

多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理，而函数的可移值性却不强，几乎每种主要的DBMS的实现都支持其他实现不支持的函数。

大多数SQL实现支持以下类型的函数：①用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数。②用于在数值数据上进行算术操作(如返回绝对值，进行代数运算)的数值函数。③用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数，如返回两个日期之差，检查日期有效性等的日期和时间函数。④返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

作用：将文本都转化为大写
输入：SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
输出:Upper()将文本转化为大写

日期的格式必须为yyyy-mm-dd
作用：根据日期筛选
输入：SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';
输出：

作用：date()指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较
输入：SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
	SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
输出：限定order_date为9月1号 到 9月30号

十二、汇总数据

应用场景：①确定表中行数 ② 获得表中行组的和 ③找出表列的最大值、最小值和平均值。这些应用场景是需要汇总数据而不用把它们实际检索出来。

输入：SELECT AVG(prod_price) AS avg_price FROM products;
输入：SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
输出：前者返回products表中所有产品的平均价格，后者返回vend_id为1003的产品的价格平均值。
Ps:AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

作用：count()函数进行计数
输入：SELECT COUNT(*) AS num_cust FROM customers;
输出：COUNT(*)对所有行计数，不管行中各列有什么值。

作用：指定某一列进行计数
输入：SELECT COUNT(cust_email) AS num_cust FROM customers;
输出：

作用：MAX()求最大值
输入：SELECT MAX(prod_price) AS max_price FROM products;
输出：
Ps:MAX()可以用于文本数据，用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。

作用：MIN()求最小值
输入：SELECT MIN(prod_price) AS min_price FROM products;
输出：
PS：MIN()可以用于文本数据，用于文本数据时，如果数据按相应的列排序，则MAX()返回最前面的行。MIN()函数忽略列值为NULL的行。

作用: SUM()函数求和
输入：SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
输出：

作用：
输入： SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
输出：DISTINCT参数表示只考虑各个不同的价格。可以看到，在使用了DISTINCT后，avg_price较高，因为有多个物品具有相同的较低价格，排除它们提升了平均价格。

作用：把多个函数聚集起来
输入：SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;
输出：用单条SELECT语句执行了4个聚集计算，返回4个值。

十三、分组数据

作用：之前的汇总数据匹配的是特定的WHERE子句，比如：
输入：SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;
输出：这个语句返回供应商1003提供的产品数目，但是如果要返回每个供应商提供的产品数目呢？这种语法就实现不了

作用：通过SELECT 中 GROUP BY 子句来建立分组
输入：SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
输出：SEKECT语句指定俩那各个列，vend_id包含产品供应商的ID,num_prods为计算字段(用COUNT(*)函数建立)。GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。

作用：GROUP BY能提取出一个列的所有分组，但有时候不需要提取全部，而提取一部分。
输入：SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
输出：这条语句类似于上面的语句，最后一行增加了HAVING子句，它过滤COUNT(*)>=2(两个以上的订单)的那些分组。
Ps:HAVING支持所有WHERE操作符。

作用：在分组条件的同时加where过滤语句
输入: SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
输出：WHERE子句过滤所有prod_price至少为10的行，然后按vend_id分组数据，HAVING子句过滤计数2或2以上的分组，如果没有WHERE子句，将会多检索出两行，即销售产品价格都在10以下的行。

输入：SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;
输出：

作用：将ORDER BY 和 GROUP BY 合起来用
输入：SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
输出：

作用：
输入：SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*price) >= 50 ORDER BY ordertotal;
输出：GROUP BY子句用来按订单号分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。

迄今为止所有学过的子句。

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

十四、使用子查询，子句查询就是把多步骤的查询内容嵌套成一句话。

作用：嵌套 子句查询
输入：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
上面的输入=SELECT order_num FROM orderitems WHERE orderitems WHERE prod_id = 'TNT2'; SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
输出：在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。首先执行SELECT order_num FROM orderitems WHERE prod_id='TNT2'，此查询返回两个订单：20005和20007.然后这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变成：SELECT cust_id FROM orders WHERE order_num IN (20005,20007).

作用：作为计算字段使用子查询
输入：SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;
输出：对客户10001的订单进行计数

作用：对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询
输入：SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
输出：这条语句对customers表中每个客户返回3列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。

作用：与上一个语句对比
输入：SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name;
输出：这句话和上一句的不同之处在于，这一句没有对cust_id进行限定，所以MySQL将假定是对orders表中的cust_id进行自身比较。

十五、联结表

联结是利用SQL的SELECT能执行的最重要的操作，使用联结之前应该了解关系表，关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值(即关系设计中的关系)互相关联。这里涉及到两个概念：主键和外键，外键是某个表中的一列，它包含另一个表的主键，定义了两个表之间的关系。分解数据为多个表能更好地存储、更方便地管理，但如果数据存储在多个表中，怎样用单条SELECT语句检索出数据呢？答案是使用联结，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

作用：创建联结
输入：SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
输出：与以前的SELECT语句不一样，最大的差别是所指定的两个列prod_name和prod_price在一个列表中，而另一个列vebd_name在另一个表中。而在FROM语句中，这条语句列出了两个表，分别是vendors和products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。

作用：和上面语句作用相同
输入：SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend.id = products.vend_id;
输出：这里，两个表之间的关系是FROM子句的组成部分，以 INNER JOIN 指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

作用：联结多个表
输入：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;
输出：此例子显示编号为20005的订单中的物品。订单物品存储在orderitems，每个产品按其产品ID存储，它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。

作用：对上面的嵌套SELECT用联结替代
输入：(原)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'));
输入：(现)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';
输出：这里没有在嵌套子查询，而是使用了两个联结。

十六、创建高级联结

作用：使用表别名
输入：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, orders AS o, orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';
Ps: 表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机

作用：使用不同类型的联结——自联结
输入：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第一次出现为别名1，第二次为别名2.

作用：使用不同类型的联结——自然联结
输入：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 = 'FB';
输出：在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的 列被检索出来。

作用：使用不同类型的联结——外部联结
输入：SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
输出：使用关键词 OUTER JOIN来指定外部联结，外部联结还包括没有关联行的行，在使用 OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表，RIGHT指出的是 OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表。
Ps:左外部联结和右外部联结之间唯一出差别是所关联的表的顺序不同，左外部联结可通过点到FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

作用：使用带聚集函数的联结
输入：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;
输出：检索所有客户及每个客户所辖的订单数,此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回

十七、组合查询

有两种情况需要用到组合查询：在单个查询中从不同的表返回类似结构的数据；对单个表执行多个查询，按单个查询返回数据。UNION从查询结果集中自动去除了重复的行，这是UNION的默认行为，如果不需要去除，可以使用UNION ALL.

作用：使用UNION
输入：SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5  UNION SELECT vend_id, prod_id, prod_price, prod_price FROM products WHERE vend_id IN (1001, 1002);
输出：这条语句由前面SELECT语句组成，语句中用UNION关键字分隔。
Ps:UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔，而且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);
输出：

作用：对组合查询结果进行排序
输入：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;
输出：这条UNION在最后一条SELECT语句后使用了ORDER BY子句，虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

十八、全文本搜索

作用：进行全文本搜索
输入：SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
输出：使用Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行，Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本，由于有两行包含词rabbit,这两行被返回。
Ps: 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

作用：使用查询扩展，查询扩展用来设法放宽所返回的全文本搜索结果的范围。比如想找出所有提到anvils的注释，但是只有一个注释包含词anvils，但还想找出可能与搜索相关的所有其他行，即便它们不包含词anvils.在使用查询扩展时，MySQL对数据和索引进行两边扫描来完成搜索。
输入：SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
输出：返回了7行，第一行包含anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词(customer和recommend),所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。

作用：布尔文本搜索，布尔文本可以提供：要匹配的词、要排斥的词、排列提示、表达式分组。
输入：SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
输出：此全文本搜索检索包含词heavy的所有行。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。

作用：布尔文本搜索，包含谁不包含谁
输入：SELECT note_text FROM productnotes  WHERE Match(node_text) Against('heavy -rope*' IN BOOLEAN MODE);
输出：和上面的结果相比，这个语句输出结果只有一行，这一次仍然匹配词heavy，但-rope*明确地指示MySQL排除包含rope*(任何以rope开始的词，包括ropes)的行，所以第一行就被排除出去了

十九、插入数据

作用：用来插入行到数据库表——插入完成的行
输入：INSERT INFO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
输出：无
分析：此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值，应该用NULL值，各个列必须以他们在表定义中出现的次序填充，这种方法虽然语法简单，但是并不安全，应该尽量避免使用。

作用：用来改进上一种形式
输入：INSERT INFO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('Pep E.LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
分析：此例子完成与前一个INSERT语句完全相同的工作，但在表明后的括号里明确地给出了列名。在插入行时，MySQL将用VALUES列表中的相应值填入列表中的对应项。因为提供了列名，VALUES必须以指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。使用这种语法，还可以省略列。

作用：插入多个行
输入：INSERT INFO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E.LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),('M.Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA')
分析：这个语句里，单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

作用：插入检索出的数据
输入：INSERT INFO 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 custnew;
分析：这个例子使用INSERT SELECT 从custnew中将所有数据导入customers.SELECT中列出的每个列对应于customers表名后所跟的列表中的每个列。INSERT SELECT中的SELECT语句可包含WHERE子句以过滤插入的数据。

二十、更新和删除数据

作用：使用UPDATE语句更新(修改)表中的数据
输入：UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
分析：UPDATE customer以要更新的表的名字开始，在此例子中，要更新的表的名字为customers。SET命令用来将新值赋给被更新的列，比如这里SET子句设置cust_email列为指定的值；UPDATE语句以WHERE子句结束，它高速MySQL更新哪一行，没有WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行。

作用：更新多个列
输入：UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' Where cust_id = 10005;
分析：在更新多个列时，只需要使用单个SET命令，每个"列=值"对之间用逗号分隔(最后一列之后不用逗号)。

作用：删除数据
输入：DELETE FROM customers WHERE cust_id = 10006;
分析：DELETE FROM要求指定从中删除数据的表明，WHERE子句过滤要删除的行。DELETE不需要列名或通配符。DELETE删除整行而不是删除列，为了删除指定的列，应使用UPDATE语句。

二十一、创建和操作表

作用：创建表
输入：CREATE TABLE customers
(
cust_id        int                  NOT NULL AUTO_INCREMENT,
cust_name      char(50)             NOT NULL,
cust_address   char(50)             NULL,
cust_city      char(50)             NULL,
    ...
    ...
    PRIMARY KEY (cust_id)
)   ENGINE=InnoDB;
输出：表名紧跟在CREATE TABLE关键词后面，实际的表定义(所有列)括在圆括号内。各列之间用逗号分隔。表的主键可以在创建表时用 PRIMARY KEY 关键字指定。这里，列cust_id指定作为主键列，主键值必须唯一。
PS:允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行。换句话说，在插入或更新行时，该列必须有值。

作用：创建由多个列组成的主键，应该以逗号分隔的列表给出各列名
输入：CREATE TABLE orderitems
(
order_num   int      NOT NULL,
order_item  int      NOT NULL,
prod_id   char(10)   NOT NULL,
quantity    int      NOT NULL
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

作用：指定默认值
输入：CREATE TABLE orderitems
(
order_num   int      NOT NULL,
order_item  int      NOT NULL,
prod_id   char(10)   NOT NULL,
quantity    int      NOT NULL  DEFAULT 1,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
输出：这条语句创建包含组成订单的各物品的orderitems表。quantity列包含订单中每项物品的数量，给该列的描述添加文本DEFAULT 1指示MYSQL，在未给出数量的情况下使用数量1,。
Ps:与大多数DBMS不一样，MySQL不允许使用函数作为默认值，只支持常量。

作用：更新表
输入：ALTER TABLE vendors ADD vend_phone CHAR(20):
输出：这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。

作用：删除表中的列
输入：ALTER TABLE vendors DROP COLUMN vend_phone;
输出：这条语句把vendors表删除一个名为vend_phone的列

作用：删除表
输入：DROP TABLE customers2;
输出：这条语句删除customers2表，删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

作用：重命名表
输入：RENAME TABLE customers2 TO customers;
输出：RENAME TABLE所做的仅是重命名一个表，如果想对多个表重命名,可以使用下面语句：
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products TO products;

二十二、使用视图

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

输入：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';
输出：从3个表中检索数据，检索订购了某个特定产品的客户。

作用：使用视图把整个查询包装成一个名为productcustomers的虚拟表，然后轻松地检索出相同的数据。
输入：SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
输出：productcustomers是一个视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询(与上面用以正确联结表的相同的查询。)

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

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据。

重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

输入：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;
输出：这条语句创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。如果执行SELECT * FROM productcustomers,将列出订购了任意产品的客户。

作用：为了检索订购了产品TNT2的客户
输入：SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
输出：

作用：用视图重新格式化检索出的数据
输入: SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
输出：

现在，加入经常需要这个格式的结果，不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。为了把此语句转换为视图，可以如下进行：

输入：CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
分析：这条语句使用与以前的SELECT语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据，可
输入：SELECT * FROM vendorlocations;
输出：

作用：用视图过滤不想要的数据
输入：CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;
分析：在发送电子邮件到邮件列表时，需要排除没有电子邮件地址的用户，这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使他们不被检索出来。
输入：SELECT * FROM customeremaillist;
输出：

作用：使用视图与计算字段
输入：SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
输出：

转换一个视图：CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;
输入：SELECT * FROM orderitemsexpanded WHERE order_num = 20005;
输出：

通常情况下，视图是可更新的，更新一个视图将更新其基表，视图本身没有数据，如果你对视图增加或删除行，实际上是对其基表增加或删除行。但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新(包括插入和删除)。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：分组、联结、子查询、并、聚集函数、DISTINCT、导出(计算)列。本章许多例子的视图都是不可更新的，因为视图主要用于数据检索。

二十三、使用存储过程

作用：执行存储过程
输入：CALL productpricing(@pricelow, @pricehigh, @priceaverage);
输出：执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

作用：创建存储过程
输入：CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage FROM products;
END;
输出：此存储过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。
分析：在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为了以后使用而创建它。

作用：删除存储过程
输入：DROP PROCEDURE productpricing IF EXISTS;
输出：

作用：创建存储过程
输入：CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price)
	INFO pl
	FROM products;
	SELECT Max(prod_price)
	INFO ph
	FROM products;
	SELECT Avg(prod_price)
	INFO pa
	FROM products;
END;
输出：此存储过程接受3个参数，pl存储产品最低价格， ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来存储过程传出一个值(返回给调用者)

作用：调用前面的存储过程
输入：CALL productpricing(@pricelow, @pricehigh, @priceaverage);
输入：SELECT @pricehigh, @pricelow, @priceaverage;
输出：

一个例子：
# 创建存储过程：
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;
分析：onumber定义为IN，因为订单号被传入存储过程。ototal定义为OUT，因为要从存储过程返回合计。SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计。

# 调用这个新存储过程
CALL ordertotal(20005, @total);
分析：必须给ordertotal传递两个参数，第一个参数为订单号，第二个参数为包含计算出来的合计的变量名。
# 显示此合计
SELECT @total;

一个复杂的例子：
需求：你需要获取与以前一样的订单合计，但需要对合计增加营业税。不过只针对某些顾客。那么，你需要做下面几件事情：(1)获得合计(与以前一样),(2)把营业税有条件地添加到合计(3)返回合计(带或不带税)。
输入：
-- Name: ordertotal
-- Parameter: onumber = order number
--            taxable = 0 if not taxable, 1 if taxable
--            ototal = order total variable
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
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 taxrate to the total
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
		-- And finally, save to out variable
		SELECT total INTO ototal;
	END;
	分析：
	此存储过程有很大的变动，首先，增加了注释(前面放置--)。在存储过程复杂性增加时，这样做很重要。添加了另外一个参数taxable，它是一个布尔值(如果要增加税则为真，否则为假)。在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值(这个例子中的taxrate的默认值被设置为6%)。SELECT语句已经改变，因此将其结果存储到total(局部变量)而不是ototal

二十四、使用游标

游标的应用场景：有时，需要在检索出来的行中前进或后退一行或多行。游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。不像大多数DBMS，MySQL游标只能用于存储过程。

# 创建游标
输入：
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
END;
分析：DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失。在定义游标之后，可以打开它。

# 打开和关闭游标
输入：
OPEN ordernumbers;
分析：在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。
CLOSE ordernumbers;
分析：CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

第一个例子：从游标中检索单个行。
输入：
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
	DECLARE o INT;
	-- Declare the 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;
分析：FETCH用来检索当前行的order_num列(自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

第二个例子：循环检索数据，从第一行到最后一行。
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	-- Declare the cursor
	DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
	
	-- Declare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Loop through all rows
	REPEAT
		-- Get order number
		FETCH ordernumbers INTO o;
		
	-- End of loop
	UNTIL done END REPEAT;
	
	--Close the cursor
	CLOSE ordernumbers;
	
END;
分析：这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用，用一个DEFAULT 0(假，不结束)定义变量done。那么，done怎么才能在结束时被设置为真呢？就是用下面这个语句：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE'02000'出现时，SET done=1.'02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

第三个例子
CREATE PROCEDURE processorders()
BEGIN
	-- Delare local variables
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	
	-- Declare the cursor
	DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
	-- Declare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- Create a table to store the results 
	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;
		
		-- Get the total for this order
		CALL ordertotal(o,l,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;
分析：在这个例子中，增加了另一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话)，名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程，来计算每个订单的带税的合计(结果存储到t)。最后，用INSERT保存每个订单的订单号和合计。

二十五、使用触发器

应用场景：MySQL语句都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句。DELETE； INSERT；UPDATE。其他MySQL语句不支持触发器。

(1) 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写。(2) 每当订购一个产品时，都从库存数量中减去订购的数量。(3) 无论何时删除一行，都在某个存档表中保留一个副本。

作用：创建触发器
输入：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
分析：CREATE TRIGGER 用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，此触发器将在INSERT语句成功执行后执行。这个触发器还指定 FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

触发器按每个表每个事件每次定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE的之前和之后)。单一触发器不能与多个事件或多个表关联，所以，如果需要对一个INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

作用：删除触发器
输入：DROP TRIGGER newproduct；
分析：触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

作用：使用触发器
(1) INSERT 触发器
输入： CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
分析： 此代码创建一个名为neworder的触发器，它按照 AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照 AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。
测试：
输入：INSERT INTO orders(order_data, cust_id) VALUES(Now(), 10001);
输出：order_num=20010
分析：orders包含3个列。order_data和cust_id必须给出，order_num由MySQL自动生成，而现在order_num还自动被返回。

(2) DELETE 触发器
输入：
CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW 
BEGIN 
	INSERT INTO archive_orders(order_num, order_data, cust_id) 		 	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
分析：在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_orders的存档表中(为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表)
使用BEFORE DELETE触发器的优点(相对于AFTER DELETE触发器来说)，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

(3) UPDATE 触发器
输入：CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
分析：每次更新一个行时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。

二十六、管理事务处理

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事物处理的应用场景：在数据库的读写存储操作过程中由于某些未知的原因导致的故障，结果是数据库中存在不正确的地方，但是人却不知道。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据库表。如果发生错误。则进行回退以恢复数据库到某个已知且安全的状态。

事务(transaction)指一组SQL语句

回退(rollback)指撤销指定SQL语句的过程

提交(commit)指将未存储的SQL语句结果写入数据库表

保留点(savepoint)指事务处理中设置的临时占位符，你可以对它发布回退(与回退整个事务处理不同)

作用：使用rollback
输入：
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
分析：这个例子从显示ordertotals表的内容开始，首先执行一条SELECT以显示该表不为空。然后开始一个事务处理，用一条DELETE语句删除ordertotals中所有的行。另一条SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。
ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)。
事务处理用来管理INSERT、UPDATE和DELETE语句，不能回退SELECT语句，不能回退CREATE或DROP操作。

作用：使用COMMIT
输入：
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010；
COMMIT;
分析：一般的MySQL语句都是直接针对数据库表执行和编写的，即所谓的隐含提交，即提交操作室自动的。但是在事务处理块中，提交不会隐含地进行。为了进行明确的提交，使用COMMIT语句。在这个例子中，从系统中完全删除订单20010.因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交。

作用：使用保留点
适用范围：简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。例如，前面描述的添加订单过程为一个事务处理，如果发生错误，只需要返回到添加orders行之前即可，不需要回退到customers表。为了支持回退部分事务处理，必须能在事务处理块中合适的位置放占位符，这样如果需要回退，可以回退到某个占位符。这些占位符称为保留点，为了创建保留点，可使用SAVEPOINT语句。
输入：
SAVEPOINT delete1；
分析：每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到本例给出的保留点，可如下进行：
输入：
ROLLBACK TO delete1;

作用：更改默认的提交行为
输入：SET autocommit=0;
分析：autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0指示MySQL不自动提交更改。

二十七、全球化和本地化

数据库表被用来存储和检索数据，不同的语言和字符集需要以不同的方式存储和检索。因此，MySQL需要适应不同的字符集(不同的字母和字符)，适应不同的排序和检索数据的方法。

输入：SHOW CHARACTER SET;
分析：这条语句显示所有可用的字符集以及每个字符集的描述和默认校对
输入：SHOW COLLATION;
分析：这条语句显示所有可用的校对，以及它们适用的字符集。可以看到有的字符集具有不止一种校对。

作用：给表指定字符集和校对
输入：
CREATE TABLE mytable
(
	columnn1 INT,
	columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;
分析：此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序。这个例子指定了CHARACTER SET和COLLATE两者。

作用：用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行。
输入：SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;
分析：此SELECT使用COLLATE指定一个备用的校对顺序，这显然会影响到结果排序的次序。

二十八、安全管理

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

作用：MySQL用户账号和信息存储在名为mysql的MYSQL数据库中，访问这个表
输入：
USE mysql;
SELECT user FROM user;
分析：mysql数据库有一个名为user的表，包含所有用户账号。user表有一个名为user的列，用来存储用户登录名。

作用：创建一个新用户账号
输入：CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
分析：CREATE USER创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子给出了一个口令。IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。

作用：重命名一个用户账号
输入：RENAME USER ben TO bforta;

作用：删除一个用户账号
输入：DROP USER bforta

作用：查看某用户的访问权限
输入：SHOW GRANTS FOR bforta;
分析：输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限。

作用：设置访问权限
输入：GRANT SELECT ON crashcourse.* TO bforta;
分析：此GRANT允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。
PS：同时赋予多个权限。可以通过列出各权限并用逗号分隔，将多条GRANT语句穿在一起
GRANT SELECT， INSERT ON crashcourse.* TO bforta;

作用：展示上面的更改
输入：SHOW GRANTS FOR bforta;
分析：每个GRANT添加(或更新)用户的一个权限。MySQL读取所有授权，并根据它们确定权限。GRANT的反操作为REVOKE，用它来撤销特定的权限

作用：撤销特定的权限
输入：REVOKE SELECT ON crashcourse.* FROM bforta;
分析：这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会报错。

作用：更改口令
输入：SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
分析：在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

二十九、数据库维护

作用：ANALYZE TABLE用来检查表键是否正确。
输入：ANALYZE TABLE orders;
输出：

作用：CHECK TABLE发现和修复问题
输入：CHECK TABLE orders, orderitems;
输出：

wqjsmile

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL语句

本文是学习《MySQL必知必会》过程中记录的学习笔记，与大家分享~《MySQL必知必会》链接：https://pan.baidu.com/s/1JV48foIMJpDaa8QjAqDBeg 提取码：7e9e作用：选择数据库输入：USE 数据库名;输出：Database changed作用：了解数据库和表输入：SHOW DATABASES;输出：返回当前选择的数据库的列表作用：获得数据库内表的列表输入：SHOW TABLES;输出：返回当前选择的数据库内可用表的列表作用：显示
复制链接

扫一扫