MySQL必知必会读书笔记

最新推荐文章于 2023-02-09 23:48:55 发布
第四代火影
最新推荐文章于 2023-02-09 23:48:55 发布
阅读量205
点赞数
文章标签： mysql
本文链接：https://blog.csdn.net/Dote2r/article/details/82427904
版权
-- 第三章    


# 返回可用数据库的一个列表
SHOW DATABASES;

# 返回当前选择的数据库内可用表的列表
SHOW TABLES;

# 返回一个表各列的信息	DESCRIBE customers是该命令的简写
SHOW COLUMNS FROM customers;


-- 第四章


# 检索不同的行	

SELECT vend_id FROM products;

SELECT DISTINCT vend_id FROM products;

# DISTINCT有以下几个注意点
# 1、DISTINCT关键字应用于所有列而不仅是前置它的列
# 2、如果指定的两列都不同，所有行都将检索出来

# 举例如下
-- 数据库test_table
-- id 		1 2 3 4 5 5
-- value 	a b c c e f 

SELECT DISTINCT value FROM test_table;
-- value 	a b c e f 

-- 结论1 SELECT DISTINCT + 单字段，可实现对该字段去重

SELECT DISTINCT value, id FROM test_table;
-- value 	a b c c e f 
-- id 		1 2 3 4 5 5

SELECT DISTINCT id, value FROM test_table;
-- id 		1 2 3 4 5 5
-- value 	a b c c e f 

-- 结论2 SELECT DISTINCT + 多字段，可实现对输入字段合并去重（只有多字段完全相同才会被“去重”的效果）

SELECT id, DISTINCT value FROM test_table;
-- 结论3 SELECT +字段 + DISTINCT + 字段，sql错误，DISTINCT必须放在开头

-- 想要的只对value字段内容去重，显示结果又能保留其他字段内容呢
SELECT min(id), value FROM test_table GROUP BY value
SELECT max(id), value FROM test_table GROUP BY value
SELECT id, min(value) FROM test_table GROUP BY id
SELECT id, max(value) FROM test_table GROUP BY id
-- 结论4 当DISTINCT无法满足只对单字段去重，并希望结果中显示对应的多字段内容的时候，可以用group by函数实现。
-- 但要注意希望在结果中显示的非去重目标字段，需要对它们分别添加筛选条件的函数或方法，否则sql语句会报错。


--第五章


-- ORDER BY 在 FORM 之后
-- LIMIT 在 ORDER BY 之后
-- 默认排序升序 ASC

SELECT prod_price 
FROM products 
ORDER BY product_price DESC
LIMIT 1;


--第六章


# 同时使用 ORDER BY 和 WHERE 子句，WHERE 在前面

# 范围值检查
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

# 空值检查 特殊的WHERE子句
SELECT prod_name
FROM products
WHERE prod_price IS NULL;


--第七章


# 优先级
# 列出价格为10元以上且由1002和1003制造的所有商品
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price > 10;
#这样会出错 正确的做法是
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price > 10;

# 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、IN 操作符一般比 OR 操作符执行更快
-- 3、IN 最大的优点就是：可以包含其他 SELECT 语句，动态地建立 WHERE 子句

# NOT 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
NOT 操作符的优点
1、配合 IN 联合使用时，找出条件列表不匹配的行非常简单
2、支持与 IN BETWEEN EXISTS 子句使用


--第八章


# LIKE 操作符

# 找出所有以词jet开头的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

# 任何位置包含文本anv1的值
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anv1%';
-- 注意空格 anv1 后面有空格将无法匹配

# 以s开头以e结尾的
SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';
-- 无法匹配NULL

# _ 通配符 只匹配单个字符而不是多个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

-- 使用通配符的技巧
-- 1、其他操作能达到目的，就不要用通配符
-- 2、不要把通配符置于搜索模式的开始


--第十二章


# 汇总数据

-- AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出
-- 为了获取多个列的平均值，必须使用多个AVG()函数
-- AVG()函数忽略值为NULL的行

-- 如果指定列名，则指定列的值为空的行被COUNT()函数忽略
-- 但如果COUNT()函数中用的是星号*，则不忽略

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products
WHERE vend_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;


--第十三章


# 获取供应商为1003提供的产品数目

SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id = 1003;

# 获取每个供应商的产品数目

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

# GROUP BY 子句必须出现在WHERE子句之后 ORDER BY 之前

# 过滤分组
# 不能使用 WHERE WHERE过滤行 HAVING 过滤组

SELECT cust_id, COUNT(*) AS orders_num
FROM orders
GROUP BY cust_id 
HAVING COUNT(*) >= 2;

SELECT cust_id, COUNT(*) AS orders_num
FROM orders
WHERE prod_price >= 10
GROUP BY cust_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;

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 子句顺序
-- SELECT
-- FROM
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY
-- LIMIT


--第十四章


# 需求：列出订购物品 TNT2 的所有客户

-- (1) 检索包含物品 TNT2 的所有订单的编号

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';		
-- 20005 20007

-- (2) 检索具有前一步骤列出的订单编号的所有客户的ID

SELECT cust_id
FROM order
WHERE order_num IN (20005, 20007);
-- 10001 10004

-- 子查询总是由内向外处理
SELECT cust_id
FROM order
WHERE order_num IN (SELECT order_num
					FROM orderitems
					WHERE prod_id = 'TNT2');

-- (3) 检索前一步骤返回的所有客户ID的客户信息

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001, 10004);

SELECT cust_name, cust_contact 
FROM customers
WHERE cust_id IN (SELECT cust_id
					FROM order
					WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id = 'TNT2'));

# 显示customers表中每个客户的订单总数 订单与相应的客户 ID 存储在 orders 表中

-- (1) 从 customers 表中检索客户列表
-- (2) 对于检索出的客户，统计其在 orders 表中的订单数目

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 vend_name, prod_name, prod_price 
FROM vendors, products 
WHERE vendors.vend_id = products.vend_id 
ORDER BY vend_name, prod_name;
-- 如果没有 where ，将返回第一个表中的行数乘以第二个表中的行数， 笛卡尔积


# 内部联结

SELECT vend_name, prod_name, prod_price 
FROM vendors INNER JOIN products 
ON vendors.vend_id = products.vend_id;

# 联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, prod_price, vend_name
WHERE products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

# 子查询和联表查询

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


--第十六章


# 使用表别名

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 = 0.order_num
AND prod_id = 'TNT2';

# 自联结 有时候处理比子查询快很多

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

# 自然联结

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


--第十七章


# 组合查询 UNION

-- 在单个表查询中从不同的表返回类似结构的数据
-- 在单个表执行多个查询，按单个查询返回数据

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

-- 包含或取消重复的行

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 ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;

-- 只能使用一条排序，必须出现在最后一条SELECT语句之后


--第二十一章


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,
	cust_state char(50) NULL,
	cust_zip char(50) NULL,
	cust_country char(50) NULL,
	cust_contact char(50) NULL,
	cust_email char(50) NULl,
	PRIMARY KEY (cust_id)
) ENGINE = InnoDB;