mysql的高级查询语句

数据库是用来存储数据，更新，查询数据的工具，而查询数据是一个数据库最为核心的功能，数据库是用来承载信息，而信息是用来分析和查看的。所以掌握更为精细化的查询方式是很有必要的。本文将围绕数据的高级查询语句展开。 

为了下面查询语句的运用，事先准备了两张表，后续也会根据查询功能的运用会对表进行一些变动，或则创建新表：   

CREATE TABLE info (   id int(4) ,   name char(4),   height double  ) ;     CREATE TABLE info2 (   name char(4)    hobby char(10)    date char(10) ) ;

select 字段1，字段2 from 表名;

 SELECT DISTINCT "字段" FROM "表名";

 SELECT "字段" FROM 表名" WHERE "条件";

 SE SELECT "字段" FROM "表名" WHERE "条件1"  AND "条件2";LECT "字段" FROM "表名" WHERE "条件1"  OR "条件2";

 SELECT "字段" FROM "表名" WHERE "字段" IN ('值1'， '值2'， ...);   #in,遍历一个取值列表

 SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';

 like查询通常会与通配符配合使用

%：百分号表示零个、一一个或多个字符

 _：划线表示单个字符

order by，按关键字排序。

注意：

  select * from info  order by height;   select * from info  order by height asc;   select * from info  order by height desc;

SELECT abs(-1),rand(), mod(5,3) ,power(2,3);

  SELECT truncate(1.89,2); SELECT truncate(1.89,1);

 truncate 和 round区别

select ceil(1.76); select floor(1.76);

select greatest(1,2,3,55,12,55,61); select least(1,2,3,55,12,55,61);

例：

select max(height) from info; select min(height) from info; select sum(height) from info;

 SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);  

#trim后面括号前不能有空格  ​  #[位置]：值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。   #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。

select trim(leading 's' from 'swmming' ); select trim(trailing 'g' from 'swmming' ); select trim(both 'l' from 'lol' );

substr(x,y)    #截取x字符串 从第y个开始，截取到末尾   substr(x,y,z)  #截取x字符串 从第y个开始截取 ，截取长度为z    

select substr(hobby,2) from info2; select substr(hobby,3) from info2;

方法一：

方法二：

 select length(hobby) from info2;  

select replace(name,'小','大') from info2;  

对GROUPBY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

"GROUP BY"有一个原则，凡是在"GROUP BY"后面出现的字段，必须在SELECT 后面出现；

凡是在SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在"GROUP BY"后面。  

用来过滤由"GROUP BY"语句返回的记录集，通常与"GROUP BY"语句联合使用。

HAVING语句的存在弥补了WHERE 关键字不能与聚合函数联合使用的不足。

where只能对原表中的字段进行筛选，不能对group by后的结果进行筛选。

select name,sum(saving) from info3 group by name having sum(saving)>1500;  

 SELECT 字段1,字段2 AS 字段2的别名 from 表名;   #AS可以省略不写

select name,sum(saving) as total_saving  from info3  group by name having sum(saving)>1000; select name,sum(saving) as total_saving  from info3  group by name having total_saving>1000;

对下面的表进行saving比较并且进行排名通过表的自我连接进行实现 

 表的自我连接达到排名的原理分析及操作思路：

1.以上面的数据表为例，假设共有四个人，他们手中的金额各不相同。我们已经进行表的自我连接。

2. 使用count计数，只计数大于等于自身手上金额的人数，比如2000的小明，大于等于他的人数只有1个，就计数值也可以当作他的排名

3.再比如800的小红，大于等于她的有4个人，就该计数值为4，同理可以证明她排名第四。  

select A.name,A.saving,count(A.saving) as rank from info3 as A,info3 as B where A.saving 1000);

求“北京地区”的所有saving值之和

select sum(saving) from info3   where name in (select name from info  where address='北京');

格式： 

 SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

MYSQL数据库中常用的表连接有三种：

 其他实现内连接的方式：

 select * from info A, info3 B where A.name=B.name;  select * from info A inner join info3 B using(name);

select * from info A LEFT JOIN info3 B on A.name=B.name;

视图：可以被当作是虚拟表或存储查询。

视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。 临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。 视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。 比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。  

格式

CREATE VIEW "视图表名" AS "SELECT 语句";   #创建视图表  ​  DROP VIEW "视图表名";                     #删除视图表

创建需求：独立创建一个视图，用来统计info和info3 两张表之间，先对name进行address的汇总，再计算地区的saving之和 。要求体现出address  和sum（sving） 两个字段

create view v_address_saving as select A.address,sum(B.saving) total_saving from info A inner join info3 B on A.name=B.name group by address;  

 视图的好处：创建视图的过程虽然和高级查询语句（通过两个select语句进行组合条件划分生成派生表）一样，过程是复杂的，但是如果该查询操作是需要经常使用的，创建视图就很有必要，不仅能简化查询过程，还能对该查询进行进一步操作，而且十分简便。

 进一步需求： 需要计算出苏州和上海两个地区的saving之和

select sum(total_saving) as suzhou_shanghai_saving from v_address_saving where address='苏州'or address='上海';

视图能否插入数据，要看情况而定： 

1）如果视图表是两个表的连接查询（比如视图的A字段来自A表，B字段来自B表，数据是无法插入的）。因为表结构和原表不一致。视图中的字段是根据原表中某个字段，通过函数运算，产生的新字段，而没有真正能够存储的字段，所以该数据是无法插入的

2）如果视图表结构与原表保持一致，数据是可以插入的，插入的数据是存储在原表中，视图所更新出的数据，其实是映射原表的数据。

UNION联集：将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类。

生成结果的数据记录值将没有重复，且按照字段的顺序进行排序。#合并后去重

 格式：[select 语句1] UNION [select 语句2];

select name from info union all select name from  info3;

  create view v_info as select distinct name from info union all select distinct name from info3;  ​ select name,count(*) from v_info group by name;  ​   select name from v_info group by name having count(*) >1;  

select A.name from info A inner join info3 B on A.name=B.name;  ​ select A.name from info A inner join info3 B using(name);  

select distinct A.name from info A inner join info3 B using(name);

select * from info A left join info3 B using(name); select distinct A.name from info A left join info3 B using(name) where B.name is not null;

 #使用右连接查出store_name字段的交集值，之后去重  select * from info A right join info3 B using(name);  select distinct A.name from info A right join info3 B using(name) where A.name is not null;    ​或  select distinct A.name from info A right join info3 B on A.name=B.name where A.name is not null;  

select distinct name from info where name in (select name from info3);  

（1）联级方法中 count（*） select address,case address     -> when '上海' then height-10     -> when '北京' then height+5     -> else height+10     -> end     -> "new_height",name     -> from info;

正则符号                           作用 ^                              匹配文本的开始字符 $                              匹配文本的结束字符 .                               匹配任何单个字符 *                               匹配零个或多个在它前面的字符 +                              匹配前面的字符 1 次或多次 字符串                      匹配包含指定的字符串 l                                 或，“|”前面的不成立时，就匹配后面的字符串 [...]                           匹配字符集合中的任意一个字符 [^...]                         匹配不在括号中的任何字符 {n}                           匹配前面的字符串 n 次 {n,m}                        匹配前面的字符串至少 n 次，至多m 次  

格式

select "字段" from "表名" where "字段" regexp '正则表达式';

​​​​​​​

无值的长度为0，不占用空间；而NULL值的长度是NULL，是占用空间的。

IS NULL或者IS NOT NULL，是用来判断字段是不是为NULL或者不是NULL，不能查出是不是无值的。

无值的判断使用=' '或者< >' '来处理。代表不等于。

在通过 count ()指定字段统计有多少行数时，如果遇到NULL值会自动忽略掉，遇到无值会加入到记录中进行计算。