-- 对两列排序输出,先 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;
MYSQL 基础语法速记
于 2023-07-18 11:48:23 首次发布