这篇文章主要是自己阅读《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 bSELECT * 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) = '-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, vendorsWHERE products.vend_id = vendors.vend_idAND orderitems.prod_id = products.prod_idAND order_num = 20005; #多个表的联结 #高级联结#别名SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_titleFROM vendors ORDER BY vend_name; #复习之前的别名用法SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oiWHERE c.cust_id = o.cust_idAND oi.order_num = o.order_numAND prod_id = 'TNT2'; #给表取别名#使用不同类型的联结#自联结SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_idAND p2.vend_id = 'DTNTR'; #首先找出ID为xx的物品的供应商，然后用找出这个供应商生产的物品#使用别名消除引用的二义性#自然联结: 排除多次出现的列#书签126#组合查询SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5UNIONSELECT 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 <= 5UNION AllSELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002); #默认不包含，这里选择包含#只准使用一个ORDER BY 子句进行排序#全文本搜索#某些引擎才支持CREATE TABLE productnotes(note_idint NOT NULL, AUTO_INCREMENT,prod_idcharNOT NULL, note_datedatetimeNOT NULL,note_texttext 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*
