MYSQL 基础语法速记

-- 对两列排序输出，先 price 再在记过中按 name 排列
SELECT prod_id, pro_price, prod_name
FROM products
ORDER BY pro_price, prod_name;

-- limit 使用
SELECT pro_price
FROM products
ORDER BY pro_price DESC
LIMIT 1;

-- 不匹配检查
-- 不是供应商1003制造的产品
SELECT ven_id, prod_name
FROM products
WHERE ven_id <> 1003;

-- 不带括号SQL会根据优先级当作
-- 1003制造的10以上产品 或者 1002制造的任何产品
SELECT prod_name, pro_price
FROM products
WHERE (ven_id = 1002 OR ven_id = 1003) AND pro_price >= 10;

-- NOT
SELECT prod_name, pro_price
FROM products
WHERE ven_id NOT IN (1002, 1003)
ORDER BY prod_name;

-- %(任意字符) 通配符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';

-- _(一个字符) 通配符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

-- 通配符会导致搜索性能下降


-- 匹配任一字符
-- .000 正则表示 匹配任一字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

-- 与or类似
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000 | 2000'
ORDER BY prod_name;

-- [] 匹配几个字符之一
-- 匹配 1/2/3 Ton
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton' -- 即 [1|2|3]
ORDER BY prod_name;

-- - 匹配范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

-- \\ 匹配特殊字符
-- 匹配 .
SELECT ven_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY ven_name;

-- 预定义字符集
-- [:alnmum:] 同 [a-zA-Z0-9]
-- [:alpha:] 同 [a-zA-Z]
-- [:digit:] 同 [0-9]

-- 匹配任何数字，以及？可选元字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'

-- 匹配连在一起的任意4位数
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;

-- ^ 文本的开始
-- $ 文本的结束
-- [[:<:]] 词的开始
-- [[:>:]] 词的结尾


-- concat
-- 按照 name(location) 拼接字段输出
SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY ven_name;

-- 删除值右侧所有空格
-- 别名 (方便客户机引用)
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS
ven_title
FROM vendors
ORDER BY ven_name;

-- 算术计算
-- 汇总物品价格
SELECT prod_id
        quantity,
        item_price,
        quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

-- Soundex
-- 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
-- 表中是Y Lee，实际是Y Lie，根据发音查询
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

-- 时间处理函数
-- 只比较指定格式
SELECT cust_id, order_num
FROM orderitems
WHERE Date(order_date) = '2005-09-01';

-- 数值处理函数
-- Abs()
-- Exp()

-- 聚合函数 （提高检索效率）

-- 计算所有行，包含 NULL 行
SELECT COUNT(*) AS num_cust
FROM cutomers;

-- DISTINCT
-- 消除相同价格，只考虑不同价格
SELECT AVG(DISTINCT prod_proce) AS avg_price
FROM products
WHERE ven_id = 1003;

-- 组合聚合函数
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;

-- 过滤分组
-- GROUP BY子句指示先按 cust_id 排序分组
-- 导致对每个 cust_id 计算一次，而不是整个表
-- HAVING 过滤两个 以上订单
-- 先执行 GROUP BY 和 HAVING 子句
-- 输出2个以上订单的 供应商品信息
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

-- 找出 prod_price 至少为 10，且存在 2 个以上产品的信息
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

-- 对订单总价大于 50 的订单排序输出
SELECT order_num, SUM(quantity * item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;

-- 子查询

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

-- 显示customers表中每个客户的订单总数
SELECT cust_name, cust_state,
      (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;


SELECT ven_name, prod_name, pro_price
FROM vendors, products
WHERE vendors, products
WHERE vendors.ven_id = products.vend_id
ORDER BY ven_name, prod_name;

-- 联结
-- 与上式结果相同
SELECT ven_name, prod_name, pro_price
FROM vendors INNER JOIN products
 ON vendors.ven_id = products.vend_id;

-- 与14章1相同，但是这种方式更有效
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';

-- 组合查询

-- UNION
-- 取消掉重复行
-- UNION ALL 匹配所有行
SELECT ven_id, prod_id, pro_price
FROM products
WHERE pro_price <= 5
UNION
SELECT ven_id, prod_id, pro_price
FROM products
WHERE ven_id IN (1001, 1002)
ORDER BY ven_id, pro_price;

-- 启用全文本搜索支持
FULLTEXT(note_text);

-- 进行全文本搜索
-- Match指定搜索的列，Against指定搜索表达式
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

-- 布尔文本搜索
-- 不包含任意以rope开始的词语
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);


-- 不太安全，因为需要特定次序插入
-- 安全的机制时在表后加列名，这样次序只要匹配自己写的列即可
INSERT INTO Customers
VALUES(NULL,
  'Pep E. LaPew'),
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
  NULL,
  NULL);

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

-- UPDATE
UPDATE customers
SET cust_name = 'The Fudds',
  cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

-- DELETE 不删除表本身
-- 删除所有行
-- 删除所有行，使用 DELETE 比较慢，可以使用 TRUNCATE
DELETE FROM customers;
-- DROP删除表本身

-- 更新表结构
ALTER TABLE vendors
ADD vemd_phone CHAR(20);

-- 删除表列
ALTER TABLE vendors
DROP COLUM ven_phone;

-- 重命名表
RENAME TABLE customers2 TO customers;