MySQL必知必会读书笔记一

这篇文章主要是自己阅读《Mysql必知必会》的时候自己写的sql，前面的一部分用的是自己的建立的一个user表，后面用的就是原书的表了

#检索
SELECT id  FROM account; #检索单列
SELECT id,name FROM account; #检索多个列
SELECT *  FROM account; #检索所有列
SELECT DISTINCT name FROM account; #DISTINCT关键字只返回不同的值，放在列名之前，只能应用于所有的列
SELECT name FROM account LIMIT 2; #LIMIT限制返回结果的条数5条
SELECT name FROM account LIMIT 1,2; #第一个参数开始位置，第二个参数要检索的条数（注意第一行从0开始）
SELECT name FROM account LIMIT 2 OFFSET 1; #与上一条意思等价，MYSQL5支持的新写法
SELECT account.name FROM account; #使用完全限定名的表名

#排序检索的数据
SELECT name FROM account ORDER BY name; #使用order by子句进行排序
SELECT id, name, money FROM account ORDER BY name, id; #多个列的排序，检索了三个列，
					#当出现多个排序规则时，仅当前一个规则相同时，才进行下一规则的排序
SELECT name FROM account ORDER BY name DESC;#添加关键字DESC倒序排序					
SELECT id, name, money FROM account ORDER BY name DESC, id; #多个列排序的降序的使用,name倒序，id正序
SELECT money FROM account ORDER BY money DESC LIMIT 1; #一个综合运用，找出最贵的存款的值

#过滤数据
SELECT * FROM account WHERE id = 2; #通过where子句进行过滤
			#注意点：order by 应该在 where后面
	#单个值的匹配
	#WHERE子句操作符 不等于<>, 在a,b两个值之间BETWEEN a AND b
SELECT * FROM account WHERE name = "aaa"; #字符串匹配(串类型需要加引号)，默认不区分大小写
SELECT * FROM account WHERE money > 2000;
SELECT * FROM account WHERE money BETWEEN 1500 AND 2500;
	#空值检查
SELECT id,name FROM account WHERE money IS NULL;

#数据过滤(用ADN,OR,NOT对where子句进行加强)
SELECT * FROM account WHERE id > 2 AND money > 2000; #AND操作符
SELECT * FROM account WHERE id > 2 OR money > 2000; #OR操作符
	#AND的优先级比OR高，如果需要改变次序，需要加上括号
	#推荐使用括号，消除歧义
SELECT * FROM account WHERE id IN (2,3) ORDER BY id; #IN指定范围
SELECT * FROM account WHERE id NOT IN (2,3) ORDER BY id; #使用NOT在WHERE子句中来否定后跟条件的关键字

#用通配符LIKE进行过滤
SELECT * FROM account WHERE name LIKE 'aa%'; # % 表示任意（可以为0个）字符出现任意次数，但是NULL和尾空格不可匹配
SELECT * FROM account WHERE name LIKE 'aa_'; # _ 表示单个字符
	#通配符使用建议：尽量少使用，也不要放在开头

#正则表达式
SELECT * FROM account WHERE name REGEXP '.aa' ORDER BY name; #基本字符匹配
SELECT * FROM account WHERE name REGEXP 'aaa|bbb' ORDER BY name; #进行OR匹配
SELECT * FROM account WHERE name REGEXP 'aa[a-z]' ORDER BY name; #范围匹配
SELECT * FROM account WHERE name REGEXP '\\.' ORDER BY name; #使用 \\ 转义，搜索.的行
	#写几个正则表达式的例子
	'\\([0-9] sticks?\\)' #匹配(1 sticks),(2.stick) ?让s可选
	'[[:digit:]]{4}' #匹配任意一个4位数字
	#定位符的例子
	'^[0-0-9\\.]' #匹配.或者数字开头

#创建计算字段
SELECT Concat(name, '(', money, ')') FROM account ORDER BY name; #使用Concat函数拼接
	#Trim,LTrim,RTrim函数可去掉空格
SELECT Concat(name, '(', money, ')') AS a_title FROM account ORDER BY name; #使用AS给拼接的字段创建别名
	#执行算术运算(这个例子不是user表的)
	SELECT id, quantity, price, quantity*item_price AS expanded_price FROM orderitems WHERE xxx; #增加一个计算字段总价

#使用数据处理函数
	#文本处理函数
	SELECT name Upper(name) AS upper_name FROM account ORDER BY name;	#Upper()转换为大写 
	#日期和时间处理函数
	SELECT id, num FROM orders WHERE DATE(order_date) = '2020-03-23'; #返回日期时间的日期部分

#聚集函数
	#AVG()平均,COUNT()行数,MAX()最大,MIN()最小,SUM()总和	
SELECT AVG(money) AS avg_money FROM account;
	#NULL会被忽略
SELECT AVG(DISTINCT money) AS avg_money FROM account; #DISTINCT去掉了相同的值
SELECT AVG(money), COUNT(money), MIN(money), MAX(money), SUM(money) FROM account; #组合聚集函数

#分组数据
	#GROUP BY子句和HAVING子句
SELECT name, COUNT(*) FROM account GROUP BY name; #分组
SELECT name, COUNT(*) FROM account GROUP BY name HAVING COUNT(*) > 2; #过滤分组

#子查询（少用）
	#第一个查询
	#SELECT id FROM account WHERE name = "aaa";
	#第二个查询
	#SELECT money FROM account WHERE id IN (1,7,3);
	SELECT money FROM account WHERE id IN 
		(SELECT id FROM account WHERE name = "aaa"); #列必须匹配

#联结表
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, 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; #多个表的联结 

#高级联结
	#别名
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'; #给表取别名
	#使用不同类型的联结
		#自联结
		SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 
			WHERE p1.vend_id = p2.vend_id
			AND p2.vend_id = 'DTNTR'; #首先找出ID为xx的物品的供应商，然后用找出这个供应商生产的物品
			#使用别名消除引用的二义性
		#自然联结: 排除多次出现的列
		#书签126
		

#组合查询
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关键字，将多个查询组合成一个结果集
	#包含或取消重复的行
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 子句进行排序
		
#全文本搜索
	#某些引擎才支持
CREATE TABLE productnotes
(
	note_id		int  		NOT NULL, AUTO_INCREMENT,
	prod_id     char		NOT NULL, 
	note_date	datetime	NOT NULL,
	note_text	text 		NULL,	
	PRIMARY KEY(note_id),
	FULLTEXT	#索引单个列
)	ENGINE=MyISAM;
	#进行全文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit'); #Match指定被搜索的列，Against指定使用的搜索表达式
	#使用扩展搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
	#使用布尔文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); #排除rope*