SQL语句
SQL语句
摘录自《MySQL必知必会》。
目录
一 查询SELECT
1.排序 order by
SELECT prod_name FROM products;
SELECT prod_name FROM products ORDER BY prod_name;
-- 按多个列进行排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
-- 指定排序方向，升序( ASC )or降序( DESC)
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price ASC
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC
-- desc只作用于其前面的列名，例如按照多个列排序
-- 我们在prod_price后方加上DESC关键字只有按prod_price进行降序排列
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;
-- 与LIMIT组合使用可以找出最值
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC LIMIT 1;
-- 需要注意的是 ORDER BY 子句必须为SELECT语句中的最后一条子句
2.过滤 where
2.1 单个where
-- 使用WHERE关键字来过滤数据，满足WHERE后条件的信息将会被检索出来
-- 此时查找价格为2.50的商品
SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50;
-- WHERE 的操作符有
-- = , != , <> ( 不等于 ), < , <= , > , >= ,BETWEEN ( 在指定 的两个值之间 )
-- MYSQL在执行匹配时默认不区分大小写
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name , prod_price FROM products WHERE prod_price < 10;
-- <> 和 != 等价
SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id,prod_name FROM products WHERE vend_id != 1003;
-- 范围值检查BETWEEN
-- 需要使用AND来连接起始值和终止值
-- 会在起始值至终止值之间进行匹配，包括起始值和终止值
SELECT prod_name ,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10
-- 空值检查IS NULL
-- 空值与包含0，空字符串或者包含空格不同
SELECT cust_id FROM customers WHERE cust_email IS NULL;
2.2 组合where （多个条件过滤）
-- ---------------------------------------------------
-- 组合使用WHERE关键字来建立更加强大搜索条件
-- ---------------------------------------------------
-- 前提：MYSQL允许给出多个WHERE子句。以两种方式使用，AND子句(逻辑与)或者OR子句（逻辑或）。
SELECT prod_id , prod_price , prod_name FROM products WHERE vend_id = 1003 AND prod_price <=10;
SELECT prod_id , prod_price , prod_name FROM products WHERE vend_id = 1003 OR vend_id = 1002;
-- 在使用OR和AND组合WHERE子句时会遇到先判定AND还是OR的问题
-- SQL在处理OR前会先处理AND操作符
-- 例如下列这个例子我们想要查询出 vend_id 为1002或1003 并且价格大于等于10 的物品
-- 这样会造成歧义
-- 1.（vend_id 为1002或vend_id 为1003 ）并且价格大于等于10 的物品
-- 2.vend_id 为1002或（vend_id 为1003 并且价格大于等于10 的物品）
SELECT prod_name , prod_price FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;
-- 由于SQL语言的特性，会将其处理为第二种歧义，因此需要我们认为的给其加上括号,注意不要打成中文括号
SELECT prod_name , prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;
-- IN操作符，用于指定条件范围，范围中的每个条件都进行匹配。
-- 区别于BETWEEN的范围取值，IN的所有合法值均在圆括号中
SELECT prod_name, prod_price FROM products
WHERE vend_id IN (1002,1003) ORDER BY prod_name;
-- 实际上IN的作用和OR是相似的
SELECT prod_name,prod_price FROM products
WHERE( vend_id = 1002 OR vend_id = 1003) ORDER BY prod_name;
-- 但是IN有它特殊的优点
-- 1. 在使用长的合法选项清单时，IN操作符的语法更加清楚直观
-- 2. 计算的次序更易管理
-- 3. IN操作符比OR操作符执行更快
-- 4. IN最大的优点可以包含其他SELECT语句
-- NOT操作符（逻辑非）
-- 否定后跟条件,常用于复杂子句与IN联合
SELECT prod_name,prod_price FROM products
WHERE vend_id NOT IN(1002,1003)
ORDER BY prod_name;
-- LIKE 操作符，模糊搜索，比如搜索姓王的所有人
-- 通配符，为在搜索句子中使用通配符必须使用LIKE
-- %（最常用的通配符）,表示任何字符出现的任意次数
-- _ 通配符，用途与%类似，但_只能匹配单个字符
SELECT prod_id , prod_name FROM products
WHERE prod_name LIKE 'jet%';
-- % 还可以匹配0个字符例如 ‘s%e’ 可以匹配到 ‘se’
-- % 无法匹配到NULL
SELECT prod_name FROM products
WHERE prod_name LIKE 's%e';
-- _ 通配符
SELECT prod_id , prod_name FROM products
WHERE prod_name LIKE '_ ton anvil';
SELECT prod_id , prod_name FROM products
WHERE prod_name LIKE '% ton anvil';
-- 通配符局限
-- 通配符的功能是有代价的，通配符搜索的处理一般要比之前的技巧所花时间更长
-- LIKE局限
-- LIKE 后跟的查询条件若与列中存在一个条件恰好相等，相应的行并不会返回，但是REGEXP可以
2.3 利用正则表达式
更详细的正则表达式语法请自己搜索。
-- -------------------------------
-- 使用正则表达式进行搜索
-- 正则表达式的作用是匹配文本
-- 查询含有字段1000的prod_name
SELECT prod_name FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
-- 查询含有字段 *000的prod_name,在正则表达式中 . 匹配任意字符
SELECT prod_name FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
-- 注意 ： MySql 中正则表达式自3.23.4之后不再区分大小写，若有区分大小写的需求，则需使用BINARY关键字
-- 正则表达式 逻辑或 | , 可使用两个或两个以上
SELECT prod_name FROM products
WHERE prod_name REGEXP '1000|2000' -- 匹配1000或2000的串
ORDER BY prod_name;
-- 匹配多个字符
-- 符号 [ ] 在正则表达式中为定义一组字符，逐个匹配其中内容，例如下面的例子则是匹配 1 Ton,或2 Ton,或 3 Ton
-- 类似于or ， 于此同时 [123]为匹配123，[^123]为不匹配123
SELECT prod_name FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
-- 范围匹配
-- 使用 - 来定义一个集合，[0-9][a-z]用来查询范围0-9和a-z的范围字符
-- [1-3] [ a-d ]里面也可以是任意字母任意数字
SELECT prod_name FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
-- 匹配特殊字符
-- 与其他语言类似，如果要输入一个特有符号需要用到转义字符 这里为 \\
-- \\f 换页 \\n 换行 \\r 回车 \\t 制表 \\v 纵向制表 \\\
SELECT vend_name FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
-- 重复元字符
-- * 0个或者多个匹配
-- + 1个或者多个匹配，等于{1，}
-- ？ 0个或1个匹配，等于{0，1}
-- {n} 指定数目的匹配
-- {n,} 不少于指定数目的匹配
-- {n,m} 匹配数目的范围（m不超过255）
SELECT prod_name FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
-- 连续匹配四位数字
SELECT prod_name FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
-- 定位符号
-- ^ 文本开始 $ 文本末尾
-- [[:<:]] 词的开始 [[:>:]] 词的结尾
SELECT prod_name FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
3. 查询字段处理（AS、函数）
-- 查询字段的处理
-- 这里指select与from之间列出的需要查询的字段
-- CONCAT() 拼接函数
-- 在一些情况下，我们想要把不同列的信息按照一定的格式输出出来
-- 例如我们想要输出一个人的国籍和信息并按照这种方式显示==> name（country）
-- 在SQL语句里是无法输出 ( 的，因此我们需要将几个串拼接起来，使用concat函数
SELECT CONCAT(vend_name,'(',vend_country,')') FROM vendors
ORDER BY vend_name;
-- -----------------------------------------------------------------------------------------
-- RTrim()函数
-- 去掉值右边的空格
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') FROM vendors
ORDER BY vend_name;
-- MySQL除了支持RTrim去掉串右边的空格，还支持LTrim去除串左边的空格以及Trim去掉串两边的空格
-- -----------------------------------------------------------------------------------------
-- 计算 + - * /
-- 查看表中订单号20005的数据，quantity为物品数量，item_price为物品价格
SELECT prod_id,quantity,item_price FROM orderitems
WHERE order_num = 20005;
-- 我们可以在查询字段中对字段进行计算操作,并将其命名为expanded_price
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems
WHERE order_num = 20005;
3.1 AS
-- ----------------------------------------------------------------------------------------
-- AS 关键字 ，设定别名
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors
ORDER BY vend_name;
-- 在计算得出的新的列并命名为vend_title后，任何应用都可以按照这个列名引用这个列
4. 函数
-- 利用函数处理数据无疑带来了很大的方便，但是函数没有SQL的移植性强
-- 每种DBMS都有自己的独特函数并且其他DBMS并不支持
-- 大多数SQL都支持以下类型的函数
-- 1.处理文本串的 文本函数
-- 2.用于在数值上进行算数操作的 数值函数
-- 3.用于处理日期和时间值并从中提取特定成分的 日期和时间函数
-- 4.返回DBMS正使用的特殊信息的 系统函数
4.1 文本处理函数
-- 1.文本处理函数
-- Upper() 转换为大写
SELECT vend_name,UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
-- 常用文本处理函数
-- Left() 返回串左边的字符
-- Length() 返回串的长度
-- Locate() 找出串的一个字串
-- Lower() 将串转换为小写
-- LTrim() 去掉串左边的空格
-- Right() 返回串右边的字符
-- RTrim() 去掉串右边的空格
-- Soundex() 返回串的SOUNDEX值
-- SubString() 返回字串的字符
-- Upper() 转换为大写
-- 其中soundex是一个将任何文本串转换为描述其语音表示的字符数字模式的算法
-- soundex考虑了类似的发音字符和音节，使得其能对串的发音进行比较而不是字符
-- 举个例子，加入我们此时表customers中有一位客户Coyote Inc.，其联系名为Y.Lee ，如果是输入错误应给是Y.Lie
-- 我们先使用比较文本的方式查找
SELECT cust_name,cust_contact FROM customers
WHERE cust_contact = 'Y.Lie'; -- 未查询出数据
-- 使用soundex函数
SELECT cust_name,cust_contact FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y.Lie'); -- 找到了Lee
4.2 日期和时间函数
-- -------------------------------------------------------------
-- 2.日期和时间函数
-- 日期和时间采用相应的数据类型和特殊的存储格式，以便能快速和有效的排序或者过滤，并且节省物理存储空间。
-- SQL日期和时间的数据类型有三种 DATE (yyyy-mm-dd) 、Time(hh:mm:ss) 、 DateTime(yyyy-mm-dd hh:mm:ss )
-- 常用日期和时间处理函数
-- AddDate() 增加一个日期
-- AddTime() 增加一个时间
-- CurDate() 返回当前日期
-- CurTime() 返回当前时间
-- Date() 返回日期时间的日期部分
-- DateDiff() 计算两个日期之差
-- Date_Add() 高度灵活的日期运算函数
-- Date_Format() 返回一个格式化的日期或时间串
-- Day() 返回一个日期的天数部分
-- DatOfWeek() 返回一个日期，返回对应的星期几
-- Hour() 返回一个时间的小时部分
-- Minute() 返回一个时间的分钟部分
-- Month() 返回一个日期的月份部分
-- Now() 返回当前的日期和时间
-- Second() 返回一个时间的秒部分
-- Time() 返回一个日期时间的时间部分
-- Year() 返回一个日期的年份部分
-- 日期和时间函数在操作时（插入更新 where比较）时需要注意格式
-- 日期 yyyy-mm-dd 时间 hh:mm:ss
-- 下面举例，找出时间为2005-09-01的订单记录
SELECT cust_id,order_num FROM orders
WHERE order_date = '2005-09-01';
-- 但是order_date 为datetime类型 包含时间部分，若使用上面的方式会有很大机率查询失败
-- 因此更为稳妥的办法是使用Date函数提取其中的日期部分进行比较
SELECT cust_id,order_num FROM orders
WHERE DATE(order_date) = '2005-09-01';
-- 举例二，查询2005年9月的所有订单
SELECT cust_id,order_num FROM orders
WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;
4.3 数值处理函数
-- ------------------------------------------------------------------------
-- 3.数值处理函数
-- 用于代数和几何运算
-- Abs() 返回一个数的绝对值
-- Cos() 返回一个数的余弦
-- Exp() 返回一个数的指数值
-- Mod() 返回操作数的余数
-- Pi() 返回圆周率
-- Rand() 返回一个随机数
-- Sin() 返回一个角度的正弦
-- Sqrt() 返回一个数的算数平方更
-- Tan() 返回一个角度的正切
4.4 数据汇集函数
-- 汇总数据而不用将其实际检索出来，检索数据便于分析和报表生成
-- 举几个例子
-- 1. 确定表中行数
-- 2. 获得表中行组的和
-- 3. 找出列中最大值、最小值、平均值
-- 汇总函数实际给出的是以上这些汇总信息
-- SQL汇总函数
-- AVG() 返回某列的平均值
-- COUNT() 返回某列的行数
-- MAX() 返回某列的最大值
-- MIN() 返回某列的最小值
-- SUM() 返回某列值之和
-- AVG() 返回某列的平均值
-- AVG只能用来获取（单个）特定数值列的平均值，而且列名必须作为参数给出
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products
WHERE vend_id = 1003;
-- COUNT() 返回某列的行数
-- COUNT有两种使用方式
-- 1. COUNT(*)对表中行数目进行计数，不管列中包含NULL还是非空值
-- 2. COUNT（column）对表中特定列中具有值得行进行计数，忽略NULL
SELECT COUNT(*) AS num_cust FROM customers; -- 5
SELECT COUNT(cust_email) AS num_cust FROM customers; -- 3
-- MAX() 返回某列的最大值
-- MySQL支持MAX来返回任意列中得最大值，若是文本列，则返回最后一行,MAX忽略NULL
SELECT MAX(prod_price) AS max_price FROM products;
-- MIN() 返回某列的最小值
-- 特点与MAX刚好相反
SELECT MIN(prod_price) AS min_price FROM products;
-- SUM() 返回某列值之和
SELECT SUM(quantity) AS items_ordered FROM orderitems
WHERE order_num = 20005;
-- sum中可以进行计算,SUM忽略NULL
SELECT SUM(item_price*quantity) AS total_price FROM orderitems
WHERE order_num = 20005;
-- 以上的函数都可以单个或者多个组合使用
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;
4.4.1 DISTINCT
-- DISTINCT字符 去除重复得数据
-- 在计算平均值时，可能只考虑各个不同值
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products
WHERE vend_id = 1003; -- 输出：15.998
SELECT AVG( prod_price) AS avg_price FROM products
WHERE vend_id = 1003; -- 输出：13.212857
5.分组 GROUP BY
-- 分组将数据分为多个逻辑组，便于对每个组进行聚集计算
SELECT vend_id, COUNT(*) AS num_prods FROM products
GROUP BY vend_id;
-- 注意：
-- 1.GROUP BY子句可以包含任意数目的列。
-- 2.如果在GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。
-- 3.GROUP BY子句中列出的每个列都必须是检索列或有效的表达式。
-- 4.SELECT中的每个列都必须在GROUP BY中给出
-- 5.如果分组列中具有NULL值，则NULL将作为一个分组返回。
-- 6.GROUP BY语句必须出现在WHERE子句之后，ORDER BY子句之前。
-- 对于以上每点更详细的解释在《MySQL必知必会》P.84
-- 过滤分组 HAVING
-- HAVING的作用与WHERE类似，区别在于WHERE过滤指定行而不是分组
SELECT cust_id,COUNT(*) AS orders FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;
-- WHERE在分组前进行过滤，HAVING在分组后进行过滤
-- WHERE和HAVING可以组合使用
SELECT vend_id,COUNT(*) AS num_prods FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
-- 上面的语句在分组前过滤掉了价格低于10的商品，然后按照vend_id进行了分组，最后又在组内过滤掉了数量小于2的组
SELECT vend_id,COUNT(*) AS num_prods FROM products
WHERE prod_price >= 10
GROUP BY vend_id
-- 对比一下 去掉第一次过滤和第二次过滤的数据
SELECT vend_id,COUNT(*) AS num_prods FROM products
GROUP BY vend_id
-- 与order by结合使用
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING ordertotal >= 50
ORDER BY ordertotal;
-- SELECT 子句的执行顺序
-- 1.SELECT
-- 2.FROM
-- 3.WHERE
-- 4.GROUP BY
-- 5.HAVING
-- 6.ORDER BY
-- 7.LIMIT
6.子查询
6.1 完全限定名
6.2 相关子查询
-- 子查询，即嵌套在其他查询中的查询
-- 表orders存储订单号、客户id、订单日期的每个订单，其中客户id cust_id 关联到customers表中
-- 表orderitems存储订单信息（包括订单号、物品id、数量、价格等）
-- 表customers存储用户信息
-- 现在我们要查询出 订购TNT2的所有客户（由于订单信息表中没有客户信息）
-- 简单分为两个步骤
-- 1.从订单信息表中查询出所有产品id为TNT2的订单
SELECT order_num FROM ordersitems
WHERE prod_id = 'TNT2';
-- 2.根据上表查到的订单号，从orders表中查出对应的客户id
SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num FROM ordersitems WHERE prod_id = 'TNT2');
-- 简单来说就是2的查询范围是1的查询结果
-- 按照上面这个例子来说，如果我们要获得客户的更多信息，现在有了客户id就便于操作了
SELECT cust_name,cust_contact FROM customers
WHERE cust_id IN (SELECT cust_id FROM orders
WHERE order_num IN (SELECT order_num FROM ordersitems
WHERE prod_id = 'TNT2'));
-- 子查询还可用于查询字段中
-- 下列是一条查询客户id为10001的订单数的查询语句
SELECT COUNT(*) FROM orders
WHERE cust_id = '10001';
-- 如果我偶们想要显示客户id为10001的其他信息并且显示订单数（不在同一个表中）
SELECT cust_name,cust_contact,(SELECT COUNT(*) FROM orders WHERE orders.`cust_id` = '10001') AS orders
FROM customers
WHERE customers.`cust_id` = '10001';
-- 由上可以看出在同一条查询语句中若出现两个不同的表，并且表中包含同一名称字段时，应该在前面加上表名，否则会报错
-- 这叫做完全限定名
-- 相关子查询
-- 涉及外部查询的子查询
-- 列名若具有多义姓就需要使用完全限定名
7. 联结
主键（primary key）: 表中唯一的标识。
外键（foreign key）：外键为表中的一列，它是另一个表的主键值，定义了两个表之间的关系。
7.1 标准联结（内部联结）
-- 为什么要使用联结？
--> 分解数据为多个表能更有效地存储，更方便地处理，并且具有更大地可伸缩性。相应地，这些好处需要付出其他地代价。
--> 联结时一种机制，用来在一条SELECT语句中关联表，因此称之为联结
-- -- 创建联结
-- 等值联结，基于两个表之间的相等测试，也称为内部联结
-- >在此句中 vendors.`vend_id` = products.`vend_id` 作为了联结条件
SELECT vend_name,prod_name,prod_price FROM vendors,products
WHERE vendors.`vend_id` = products.`vend_id`
ORDER BY vend_name,prod_name;
-- 笛卡尔积（集合叉乘）
-- >由没有联结条件地表关系返回的结果为笛卡尔积。检索出的行数目将是第一个表中的行数乘以第二个表中的行数
SELECT vend_name,prod_name,prod_price FROM vendors,products
ORDER BY vend_name,prod_name;
-- FROM子句联结
-- 对上面第一句查询语句做一些语法上的修改,查询的结果完全相同
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.`vend_id` = products.`vend_id`;
-- 区别于第一句查询语句，这里使用的语法，是from的子句，联结条件用特定的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;
-- >由于MySQL处理联结的方式是在处理联结时关联指定的每个表。这种处理非常耗费资源，因此应该仔细检查以避免联结不必要的表。
-- 使用联结优化子查询中的一个例子，该例子如下
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 orders.`order_num` = orderitems.`order_num`
AND prod_id = 'TNT2';
7.2 高级联结
-- 表别名
-- 与列别名类似用AS定义
-- 与列别名不同的是，表别名只用于查询语句的执行中
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';
-- 上面所说的联结一般称为内部联结或者等值联结（equijion）的简单联结
-- 还有三种联结分别是自联结、自然联结、外部联结
7.2.1 自联结
-- 自联结
-- >假设我们需要查询商品(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中找到具有和商品id‘DTNTR’相同的vend_id的其他商品
-- >但是由于多次使用表products，所以为了避免多义性使用了表的别名（相当于复制了一份表，然后对比着复制的表查找
-- >通常情况下，处理联结的效率要高于处理子查询
7.2.2 自然联结
-- 自然联结
-- >联结的条件是，至少有一个列出现在不止一个表中，可能有些拗口，但是联结条件就是根据这列来查找的
-- >标准的联结（内部联结）返回所有数据，甚至相同的列出现多次。自然联结排除多次出现，使每个列只返回一次。
-- >上面的功能是通过对表使用通配符，对其他的表使用明确子集来完成的
SELECT c.* , 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';
7.2.3 外部联结
-- 外部联结
-- >包含了在相关表中没有关联行的行，这种类型的联结成为外部联结
-- >一个简单的内部联结，检索所有客户及其订单
SELECT customers.`cust_id` , orders.`order_num`
FROM customers INNER JOIN orders
ON customers.`cust_id` = orders.`cust_id`;
-- >外部联结
SELECT customers.`cust_id` , orders.`order_num`
FROM customers LEFT OUTER JOIN orders
ON customers.`cust_id` = orders.`cust_id`;
-- 从检索结果来看，很明显能够看出，外部联结能够查询出没有关联行的行（这里体现为NULL的行）
-- 字段LEFT 表示从OUTER JOIN左边的表选择所有的行，若要从右边选择所有的行，则需使用RIGHT
7.2.4 使用聚集函数的联结
-- 使用带聚集函数的联结
-- 检索所有客户及每个客户所下订单数
SELECT customers.`cust_id` ,customers.cust_name , 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 customers.`cust_id` ,customers.cust_name , COUNT(orders.`order_num`) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.`cust_id` = orders.`cust_id`
GROUP BY customers.cust_id;
8. 组合查询（UNION）
-- 组合查询
-- 执行多条SELECT语句，并将查询结果的并集作为单个查询结果返回
-- 使用场景
-- 1.单个查询从多不同的表返回类似结构的查询数据
-- 2.对单个表执行多个查询，按照单个查询结果返回
-- 使用UNION操作符来组合数条SQL查询
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);
-- UNION规则
-- 1.UNION必须由两条或者两条以上的SELECT语句组成，语句之间需要用UNION分隔
-- 2.UNION的每个查询必须包含相同的列、表达式或聚集函数（出现次序可不同）
-- 3. 列数据类型必须兼容（不必完全相同，但必须是DBMS可以隐含地转换地类型）
-- 在使用UNION时默认是不输入重复地行，若需要输出全部行需要加上ALL
SELECT vend_id , prod_id ,prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);
-- 对组合查询的排序,只需要在最后加上一条ORDER BY
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)
ORDER BY vend_id,prod_price;
9.全文本查询（MyISAM引擎）
-- MySQL最常用的引擎MyISAM、InnoDB，前者支持全文本搜索，后者不支持。
-- 区别于前面的表都是使用InnoDB，表productnotes使用MyISAM创建
-- 如果要使用全文本搜索，应该设置表的引擎为MyISAM
-- 在前面的组合Where中阐述过模糊搜索，它使用关键字LIKE和通配符匹配文本
-- 对于文本的搜索前面还使用过正则表达式
-- LIKE和正则表达式的限制：
-- 1.性能：通配符和正则表达式匹配通常会尝试匹配表中的所有行。由于被搜索的行数不断增加，所以这些搜索可能非常耗时
-- 2.明确控制：很难明确的控制匹配什么和不匹配什么
-- 3.智能化的结果：它们不能提供一种智能化的选择结果的方法。
-- 正因为有如上的限制，我们才要使用全文本搜索，很明显它能解决上面的限制。
-- 全文本搜索时，MySQL不比查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。
-- >这样可以快速有效的决定哪些词匹配，哪些词不匹配。
9.1 全文本搜索（Match Against）
-- ---------------------------------------------------------------------------------------
-- 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。
1.启用全文本搜索模式
-- 一般会在创建表时启用全文本搜索。CREATE TABLE时添加FULLTEXT子句
CREATE TABLE productnotes(
note_id INT NOT NULL AUTO_INCREMENT,
prod_id CHAR(10) NOT NULL,
note_data DATETIME NOT NULL,
note_text TEXT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text) -- 使用了FULLTEXT对列note_text进行了索引
)ENGINE=MYISAM;
-- 在定义后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
2.使用全文本搜索
-- 在 索引之后使用函数Match()和Against()执行全文本搜索
-- 其中Match()指定被搜索的列，Against()指定要使用的搜索表达式
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');
-- 注意：
-- 此处传递给Match()的值必须与FULLTEXT()定义中的相同.指定多个列的话，必须将其全部列出，并且还要保证次序正确。
-- 除非使用BINARY，否则全文本搜索不区分大小写。
对比LIKE
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';
-- 查询结果相同，但是次序不同。
-- 全文本搜索具有对解果排序的功能，搜索词在文本中的位置越靠前，则具有越高的优先级。
SELECT note_text,MATCH(note_text) AGAINST('rabbit') AS rank
FROM productnotes;
9.2 全文本查询之扩展查询
3. 全文本搜索之查询扩展
-- 假设我想找出所有提到anvils的注释。只有一个注释包含词anvils
-- 但是我还想找出可能与我搜索有关的所有其他行，即使它们不包含词anvils
MySQL是怎样来完成查询扩展功能的？
-- 1.首先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
-- 2.其次，MySQL检查这些匹配行并选择所有有用的词（何为有用）
-- 3.再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词
-- 先进行一次简单的全文本搜索
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils'); -- 结果是只返回一行
-- 查询扩展
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION); -- 7 条
-- 现在来解释上面提到的何为有用的词？
-- 这个例子里，第二条结果中包含了在第一条中出现过的两个词（customer[顾客]和recommend[推荐]）它们和anvils[铁砧]没有任何关系。
-- 我推测 在我们包含条件anvils的结果中出现的名词？动词？应该就是所谓的有用的词
9.3 布尔操作（IN BOOLEAN MODE）及布尔操作符
4. 布尔文本搜索
-- 全文本搜索的另一种形式，布尔方式
-- 可以提供以下条件：
-- 1. 要匹配的词
-- 2.要排斥的词（包含该词则不反悔）
-- 3. 排列提示（指定某些词比其他词更重要，具有更高的优先级）
-- 4.表达式分组
-- 5.另外一些内容
-- 即使没有FULLTEXT索引也可使用布尔方式
-- 这是一种非常缓慢的操作（其性能随着数据量的增加而降低）
-- 例子1
-- 关键字 IN BOOLEAN MODE
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy' IN BOOLEAN MODE);
-- 这个例子使用了关键字IN BOOLEAN MODE，但就查询结果来看与没有该操作符没有区别。
-- 实际上是因为没有指定布尔操作符，并且使用了该关键字的搜索行为有个重要的差别（这个差别将在接下来几个例子中体现）
-- 例子2
-- 排斥 以'rope'开头的词
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);
-- 可以看见against中有两个条件 heavy 和 -rope* 理解为and操作（即heavy and -rope*）即可
-- 上面例子2出现了布尔操作符，下面列出全文本布尔操作符
-- + 包含，词必须存在
-- - 排除，词必须不出选
-- > 包含，而且增加等级值（优先级）
-- < 包含，且减少等级值
-- （） 把词组成子表达式（允许对该词组进行布尔操作）
-- ~ 取消一个词的排序值
-- * 词尾通配符
-- "" 定义一个短语（类似于"for example"）
-- 下面对上面的操作符做一下简单的示例
-- 例子3
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE);
-- 搜索包含rabbit和bait的行
-- 例子4
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit bait'IN BOOLEAN MODE);
-- 与上面的例子3作为对比，因为不含任何布尔操作符，所以此处的条件是 rabbit or bait
-- 例子5
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('"rabbit bait"'IN BOOLEAN MODE);
-- 搜索短语 "rabbit bait"，它们是一个整体
-- 例子6
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('>rabbit <carrot'IN BOOLEAN MODE);
-- 增加前者的优先级，降低后者的优先级
-- 这条不太懂,搜索出的两条结果都没有carrot
-- 例子7
SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('+safe +(<combination))'IN BOOLEAN MODE);
-- 匹配safe 和combination 并且降低后者的优先级
9.4 全文本搜索的小注释
-- 1.MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
-- 2.如果表中的行数少于3行，则全文本搜索不反回结果（每个词至少出现了50%）
-- 3.忽略单引号，索引 don’t为索引dont
-- 4. 不具有词分隔符（日语汉语）的语言不能恰当的返回全文本搜索结果
-- 5. 仅在MyISAM引擎中支持全文本搜索
二、 插入INSERT INTO
-- 插入数据INSERT
-- 插入可以以下几中方式使用
-- 1. 插入完整的行
-- 2.插入行的一部分
-- 3.插入多行
-- 4.插入某些查询的结果
10.1 插入完整的行
INSERT INTO customers
VALUES( NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
-- 此种语法中，必须对每个列提供一个值，如果该列没有值那么应该给NULL
-- 并且插入顺序必须按照它在表中定义的顺序出现
-- 这种语法虽然简单，但并不安全，应该尽量避免使用。因为它依赖于表中列的定义次序
10.2 插入行的一部分
-- 更复杂一些的语法如下（给定要插入的列名）
INSERT INTO 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);
-- 这种语法在表名后的括号内提供了列名，加入数据的顺序需要与提供的列名的顺序一致，不再需要按照列在表中出现的次序来添加
-- 同样的，即使改变括号中列名次序，只需在添加时按照对应列名输入相应的数据即可
-- 即第二种语法可以给行的某一部分列（括号内给出的）添加数据
-- 注意：
-- 插入列的一部分，理所当然的就是省略一部分列，但是省略的列必须满足以下条件
-- 1. 该列允许为NULL值（空值或无值）
-- 2. 在表定义中给出默认值。即不给它添加数据，该列会使用默认值。
10.3 LOW_PRIORITY
提高性能
-- 在有多条语句执行时，INSERT很可能降低等待处理的SELECT语句（一般这条是最重要的）的性能
-- 我们可以在INSERT和INTO之间加入关键字 LOW_PRIORITY来降低这句的优先级
-- INSERT LOW_PRIORITY INTO
-- LOW_PRIORITY同样适用于DELETE、UPDATE
10.4 插入多行
-- 3.插入多行
INSERT INTO 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'
);
-- 插入多行只需要在VALUES后添加多个括号，括号用逗号分隔开，每个括号为一行
-- 每行插入的信息必须按照表名后括号内的列名来进行插入操作
10.5 插入某些查询的结果
-- 将SELECT语句与INSERT语句结合，将SELECT的结果插入表中。这就是所谓的SELECT INSERT
-- 现在假设我们需要从另一张表中合并客户到customers表。不需要每次读取一行，直接进行如下操作
-- 前提是另一张表custnew的结构与customers相同，并且在填充时，不能使主键值重复（即新表中主键值不能和customers表中主键值重复）
-- 否则后续的INSERT语句会执行失败
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
-- 实际上custnew的列名不必与customers一样，INSERT SELECT会将新表第一列数据插入到旧表的第一列位置，以次类推
-- 同样的SELECT语句中也可以添加之前介绍过的如WHERE来过滤数据
三、更新和删除
-- 更新UPDATE
-- 更新数据可以采用两种方式
-- 1.更新特定的行
-- 2.更新表中的所有行
-- 因此在使用UPDATE时需要细心，稍不注意就会更新表中所有行
-- UPDATE语句由三部分组成
-- 1.要更新的表
-- 2.列名和它们的新值(SET)
-- 3.确定要更新行的过滤条件(WHERE)
-- 例1 修改客户10005的电子邮件地址
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
-- 例2 更新多个行只需要一个SET
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
-- 在UPDATE语句中可以使用子查询
-- 使用UPDATE删除某个列的值,将其设置为NULL
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
-- 删除数据
-- 1.删除特定的行
-- 2.删除所有行
DELETE FROM customers
WHERE cust_id = 10006;
-- 如果想要速度更快的删除所有行，不要使用DELETE，可以使用TRUNATE TABLE,它完成相同的工作，但速度更快
11.1 IGNORE
-- IGNORE关键字
-- 如果使用UPDATE更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消。
-- 为了使即使发生错误也继续进行更新，就使用IGNORE关键字
-- UPDATE IGNORE customers ...
浙公网安备 33010602011771号