mysql查询

查询子句顺序

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

1. select 用来指定查询哪些列，可以使用聚合函数。
2. from 用来指定查询哪张表。
3. where用来指定过滤条件，针对原表，那么条件中的列名只能是是原表的列名，不可以是别名或者使用聚合函数。
4. group by 用来对结果集进行分组，条件中的列可以使用原名，也可以使用别名。
5. having 用来指定对分组后的数据进行再次过滤的条件，条件中的列可以使用原名，也可以使用别名。
6. order by 对结果集中某一列进行排序，默认降序。有 asc 升序，desc 降序两种选择。条件中的列可以使用原名，也可以使用别名。
7. limit 用来取出结果集中的某些行.limit m,n取出的是从第m+1行开始一共n行的数据。limit n相当于limit 0,n

通配符过滤

Like操作符支付通配符和搜索模式，通配符是匹配值的一部分的特殊字符，字面值和通配符组成搜索条件,进行模糊搜索. 匹配任意字符任意次数：%
匹配任意字符一次：_

SELECT id, name FROM tb_account WHERE name LIKE 's%e'; //匹配s开头e结尾的名字
SELECT id, name FROM tb_account WHERE name LIKE 's_e'; //匹配s开头e结尾的中间只有一个字符的名字

正则表达搜索

mysql支持的正则表达式的初步支持,REGEXP之后跟正则表达式.

匹配字符类

. : 匹配任意字符
| ：或者
[] ：匹配特定字符

\ : 匹配特殊字符
[:alnum:] : 任意字符数字同（[a-zA-z0-9]）
[:alpha:] : 匹配任意字符
[:digit:] : 匹配任意数字
[:lower:] : [a-z]
[:upper:] : [A-Z]
[:space:] : 匹配任意空白字符

重复元字符

• : 0个或者多个
• ：1个或者多个 {1，}
  ? : 0个或者一个{0， 1}
  {n} : 指定n个
  {n,}: 不少于n个
  {n,m}: 不少于那个不多于m（<=255）

定位符

匹配特定位置的文本
^ : 文本开始，出现在集合中用来否定集合
% : 文本结尾
[[:<:]] :词的开始
[[:>:]] :词的结尾

SELECT id,name FROM tb_account WHERE name REGEXP '.000' ORDER BY name; //.匹配任意一个字符
SELECT id,name FROM tb_account WHERE name REGEXP BINARY 'S.00' ORDER BY name; //正则表达式区分大小写binary
SELECT id,name FROM tb_account WHERE name REGEXP '1000|2000|3000'; //|或者操作匹配之一
SELECT id,name FROM tb_account WHERE name REGEXP '[123] TO'; //[]匹配特定字符
SELECT id,name FROM tb_account WHERE name REGEXP '[1-5] TO'; //[1-5] [a-z] 用来匹配一个范围集合
SELECT id,name FROM tb_account WHERE name REGEXP '\\.'; //匹配特殊字符使用\\加特殊字符转义

SELECT name FROM tb_account WHERE name REGEXP '[[:digit:]]{4}';//[:dight:]数字是一个集合，匹配连在一起的4位数
SELECT name FROM tb_account WHERE name REGEXP '[0-9][0-9][0-9][0-9]'; //意思同上
SELECT name FROM tb_account WHERE name REGEXP '^[0-9\\.]'; //匹配数字或者.字符开头的字符串

创建计算字段

计算字段实在SELECT语句内创建的,拼接降至链接到一起构成单个值,Concat()函数拼接两列;RTrim()函数可以去掉值右边的空格;SQL支持列别名，别名是一个字段或者值的替换名，别名用AS关键字.计算字段可以对检索出的数据进行算数计算.

SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;
SELECT Concat(RTrim(vend_name), '(', vend_country, ')') FROM vendors ORDER BY vend_name;
SELECT Concat(RTrim(vend_name), '(', vend_country, ')') AS vend_title FROM vendors ORDER BY vend_name;
SELECT id,price,count, price*count AD exp FROM orders WHERE id = 2005; //对检索字段算术运算且创建别名

使用数据处理函数

使用函数对检索出的值进行处理

文本处理函数

Left() //返回串左边字符
Right() //返回串右边字符
Length() //串长度
Locate() //字串
Lower() //小写
Upper() //大写

时间日期处理函数

AddDate() //添加日期
AddTime() //添加时间时分秒
CurDate()
CurTime()
Date()
Time()
Now() //返回当前日期时间

数值处理函数

Abs()
Cos()
Sin()
Exp() //指数
Mod() //余数
Rand()
Sqrt() //平方根

SELECT name, Upper(namge) AS name_upcase FROM tb_account ORDER BY name;
SELECT id,name FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
SELECT id,name FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' and '2005-10-01';

汇总数据

聚集函数可以用来汇总数据，对行进行计数平局值最大最小值而不用检索所有数据，运行在行组上计算返回单个值
AVG() //返回某列的平均值
COUNT() //......的行数
MAX() //......最大值
MIN() //......最小值
SUM() //......和值

SELECT AVG(count) AS avg_count FROM orders WHERE name = 'sss';
SELECT COUNT(*) AS num FROM orders;
SELECT COUNT(id) AS id_count FROM orders;
SELECT MAX(id) AS id_max FROM orders;
SELECT MIN(price) AS min_price FROM orders;

SELECT AVG(DISTINCT price) AS avg_price FROM orders WHERE name = "ssss";// 平均值但是去除率相同价格
SELECT COUNT(*) AS num, MIN(price) AS min_price, MAX(price) AS max_price FROM orders;//组合聚集函数

分组数据

计算是在表的所有数据或者匹配特定的WHERE子句的数据上数据上进行的.分组允许吧数据分为多个逻辑组,以便于对每个组进行聚集计算.
SELECT语句的GROUP BY 创建分组, 它必须在ORDER BY之前, 且必须是索引列或者表达式.
过滤分组，可以规定包括哪些分组，排除哪些分组。使用HAVING.
分组和排序可以完成相同的工作但是本质不同，一般在使用GROUP BY时也应该给出ORDER BY不要依赖GROUP BY排序.

SELECT id, COUNT(*) AS num FROM orders GROUP BY id;
//select指定两列，num为计算字段使用COUNT(*)函数建立,按照id排序分组，对每个id而不是整个表计算一次num.

SELECT cust_id, COUNT(*) AS num FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; //过滤COUNT(*)>=2的那些分组
SELECT cust_id, COUNT(*) AS num FROM orders where price >= 10 GROUP BY cust_id HAVING COUNT(*) >= 2;

使用子查询

子查询即嵌套在其他查询中的查询，子查询可以作为一个计算字段.

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'; //查询prod_id为tnt2的所有订单检索其order_num列
SEELCT cust_id FROM order WHERE order_num IN(2005,2006);// 查询具有订单2006 2006的客户id
//将第一个查询的结果作为第二个查询的输入
SELECT cust_id FROM order WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 1000; //对每个客户计算count(*) 可以作为子查询
SELECT cust_name, 
      (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customsers.cust_id) AS orders
      FROM customers ORDER BY cust_name;

连接表

用来在一条SELECT语句中关联表的机制成为联结，联结可以多个表返回一组输出。仪表需要关系表即外键：摸个表中的一列包含另外一个表的主键。应该保证所有联结都有where子句。FROM a inner join b on a.id = b.id 是内部联结的一种形式，和用select where联结没有差别. 自联结通常作为外部语句替代从相同的表中简述时使用的子查询语句. 在两个表中的行进行联结是需要包含没有关联行的那些行，此事需要外部联结. 外部联结分为外部左联结和外部右联结，区别是联结顺序不同获取所有行的表不同。

SELECT vend_name, prod_name, prod_price FROM venders, products WHERE verdors.vend_id = products.vend_id;

//上面查询是内部联结可以送INNER JOIN指定 ON为条件接收传递给WHERE的数据
SELECT vend_name, prod_name, prod_price FROM venders INNER JOIN products ON verdors.vend_id = products.vend_id;

//联结多个表
SELECT cust_name,cust_contact FROM customers, orders, orderitems WHERE customers.cust_id =orderst_id AND
 orderitems.order_num = orders.order_num AND prod_id = 'TNT2';

//使用表别名
SELECT cust_name,cust_contact FROM customers AS c, orders AS o, orderitems SA oi WHERE c.sut_id = o.cust_id AND
oi.order_num = o.order_num AND prod_id = 'TNT2';

//自联结替代子查询
SELECT prod_id, name FROM products WHERE id = (SELECT id FROM products WHERE prod_id = 'AA');
SELECT p1.id,p1.name FROM products AS p1, products AS p2 WHERE p1.id = p2.id AND p2.prod_id = 'AA';

//外部联结
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id= orders.cust_id;
//从左边cuteomers中选择所有行

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id= orders.cust_id;
//orders中选择所有行

组合查询

多数sql语句是从一个或者多个表中返回数据的单条select语句。执行多条查询将结果作为单个查询结果集返回则为组合查询使用union关键字. UNION规则：

• 多条select语句，查询必须包含相同的列
• 列数据类型必须兼容
• 默认取消重复的行，返回所有的行需要使用 UNION ALL
• 对组合结果排序的ORDER BY放在最后一条select语句之后

SELECT vend_id, prod_id,prod_price FROM products WHERE prod_prices <=  5
UNION
SELECT vend_id, prod_id,prod_price FROM products WHERE vend_id IN（1001,1002);
ORDER BY vend_id