目录
对MySQL数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。例如只取10条数据、对查询结果进行排序或分组等等。
一、常用查询
1、order by按关键字排序
使用select 语句可以将需要的数据从mysql数据库中查询出来,如果对查询的结构进行排序,可使用order by语句来对语句实现排序,并最终将排序的结果返回给用户,这个语句的排序不光可以针对某一个字段,也可以针对多个字段。
select 字段 from 表名 order by 字段 ASC(DESC)
ASC(DESC)
ASC 是按照升序进行排名的,是默认的排序方式,即ASC可以省略
DESC 是按照降序的方式进行排序的
order by 也可以通过 where 子句对查询结果进行进一步的过滤
可进行多字段的排序
模板表
1.1 升序排序
select 字段名1,字段名2[,...] from 表名 order by 字段名;
select name,level from pokemon order by level;
1.2 降序排序
select 字段名1,字段名2[,...] from 表名 order by 字段名 desc;
elect name,level from pokemon order by level desc;
1.3 结合where进行条件过滤再排序
select 字段名1,字段名2[,...] from 表名 判断 order by 字段名;
select name,属性,level from pokemon where level>50 order by level;
1.4 多字段排序
order by 语句也可以使用多个字段来进行拍讯,当排序的第一个字段相同的记录有多条的情况下,这些多条的记录再按照第二个字段进行排序,order by 后面跟多个字段时,字段之间使用英文逗号隔开,优先级是按先后顺序而定, 但order by 之后的第一个参数只有在出现相同值时,第二个字段才有意义。
select 字段名1,字段名2[,...] from 表名 order by 字段名3,字段名4;
select 性别,name,属性,level from pokemon order by 性别,level;
全都顺序排序
按第一个条件顺序排序,第二个条件倒序排序
全都倒序排序
按第一个条件顺序倒序,第二个条件倒序排序
2、and/or判断
在大型数据库中,有时查询数据需要数据符合某些特点条件
AND/OR 且/或 和shell脚本中一样使用方法
2.1 and/or (且/或的使用)
select * from info where id>2 and id<5; #与
select id,name,level from pokemon where level>50 and id<5;
select * from info where id>2 or id<5; #且
select id,name,level from pokemon where level>50 or id<4;
2.2 嵌套/多条件
select * from info where id>2 or (id>3 and id <5);
#查看id<2或者3<id<5的内容
select id,name,level from pokemon where level>50 and level<90;
查看等级大于50并且小于90的数据
select id,name,level from pokemon where level>50 and level<90 order by level desc;
查看等级大于50并且小于90的数据,按降序显示
3、distinct 查询不重复记录
select distinct 字段 from 表名﹔
distinct 必须放在最开头
distinct 只能使用需要去重的字段进行操作
distinct 去重多个字段时,含义是:几个字段同时重复时才能被过滤,会默认按左边第一个字段为依据。
4、GROUP BY对结果进行分组
通过sql查询出来的结果,还可以对其进行分组,使用group by语句来实现,group by通常都是结合集合函数一起使用的。
常用的聚合函数包括 :计数(count)、求和(sum)、求平均数(avg)、最大值(max)、最小值(min),group by 分组的时候可以按一个或多个字段对结果进行分组处理。
- 对于group by后面的字段的查询结果进行汇总分组,通常是结合聚合函数一起使用的
- group by 有一个原则,就是select后面的所有列中,没有聚合函数的列必须出现在group by的后面
select 字段,聚合函数 from 表名 [where 字段名(匹配) 数值] group by 字段名;
select count(name),hobby from info group by hobby;
#count(name): 记录name字段的数据出现的次数
#group by hobby: 按照拥有相同hobby的记录进行分组。最终结果为:hobby为2的有2个人,为3的有2个人,为4的1个人,为5的2个人。select count(name),score.hobby from info where score>=80 group by hobbye;
#统计分数大于80的记录个数,以hobby分组,统计每组大于80的有多少人select count(*) from info;
#统计表有多少行select count(name),score,hobby from info where score>=80 group by hobby order by count(name) desc;
#统计80分以上的成绩,并且以id进行分组统计每组的个数,并且以个数降序
5、limit 限制结果条目
limit 限制输出的结果记录
在使用 MySQL SELECT 语句进行查询时,结果集返回的是所有匹配的记录(行)。有时候仅需要返回第一行或者前几行,这时候就需要用到 LIMIT 子句。
语法格式:
select 字段 from 表名 limit [offset,] number
limit 的第一个参数是位置偏移量(可选参数),是设置 mysql 从哪一行开始
如果不设定第一个参数,将会从表中的第一条记录开始显示。
第一条偏移量是0,第二条为1
offset 为索引下标
number 为索引下标后的几位
6、设置别名(alias—as)
在 mysql 查询时,当表的名字比较长或者表内某些字段比较长时,为了方便书写或者多次使用相同的表,可以给字段列或表设置别名,方便操作,增强可读性。
列的别名 select 字段 as 字段别名 表名
表的别名 select 别名.字段 from 表名 as 别名
as 可以省略
使用场景:
- 对复杂的表进行查询的时候,别名可以缩短查询语句的长度
- 多表相连查询的时候(通俗易懂、减短sql语句)
在为表设置别名时,要保证别名不能与数据库中的其他表的名称冲突。
列的别名是在结果中有显示的,而表的别名在结果中没有显示,只在执行查询时使用。
6.1 查询表的记录数量,以别名显示
6.2 利用as,将查询的数据导入到另外一个表内
create table pokemon2 as select * from pokemon;
select * from pokemon2;
select * from pokemon;
创建一个新表,定义表结构
此处as起到的作用:
- 创建了一个新表, 并定义表结构,插入表数据(与pokemon表相同)
- 但是”约束“没有被完全”复制“过来 #但是如果原表设置了主键,那么附表的:default字段会默认设置一个0
7、通配符
- 通配符主要用于替换字符串的部分字符,通过部分字符的匹配将相关结构查询出来
- 通常通配符都是跟like一起使用的,并协同where
- 子句共同来完成查询任务,常用的通配符有两个,分别是:“%” 和 “_”。
%:百分号表示零个、一个或多个字符
_:下划线表示单个字符
select id,name from info where name like "z%"
#查询名称以z开头的记录
select id,name info where name like 'z_a%';
#查询名称以z开头,z和a之间隔一个任意字符。a后面任意长度字符
select id,name from info where name like '%a%';
#查询包含字符a的name的记录
select id,name from info where name like '__ang__'
#查询两个任意字符,再是ang,再是两个任意字符。共7个字符的记录
二、子查询
- 子查询也被称作内查询(内连)或嵌套查询,是指在一个查询语句里面还嵌套着另一个查询语句。
- 子查询语句是先于主查询语句被执行,且结果作为外层的条件返回给主查询进行下一步的查询。
- 在查询中可以与主语句相同的表,也可以是不同的表
1、select查询
子语句可以与主语句所查询的表相同,也可以使不同的表
语法格式
select 字段1,字段2 from 表名1 where 字段 in (select 字段 from 表名 where 条件);
主语句:select 字段1,字段2 from 表名1 where 字段;
in:将主表和字表关联/连接的语法
子语句(集合):select 字段 from 表名 where 条件;
1.1 相同表查询
select name,属性,level from pokemon where level in (select level from pm where level>50);
#先查询子语句表中等级大于50的,然后根据所得等级得出等级集合作为主语句的过滤条件
1.2 多表查询
select name,属性,level from pokemon where name in (select name from pm where level>50);
#查询pm中等级高于50级的名字,然后根据查到的名字,来查询pokemon表中对应的name,属性,level
1.3 not取反,将子查询的结果,进行取反操作
select name,属性,level from pokemon where name not in (select name from pm where level>50);
#查询pm中等级高于50级的名字,然后根据取反,来查询pokemon表中对应的name,属性,level
1.4 结合as别名进行子查询
当我们将一个查询的结果集作为一个新表再进行查询时。直接使用会进行报错,我们需要使用到别名。
- 如果直接使用select id from (select id,name from info); 此时会报错,因为select * from 表名 ,此格式为标准格式,而以上的查询的语句, “表名”的位置其实是一个完整结果集,mysql并不能直接识别,而此时给与结果集设置一个别名,
- 所以可以使用select a.id from (select id,name from info) a;
select id from (select * from pokemon);
#此条报错
select id from (select * from pokemon)a;
#设置了别名,然后再作为表
select a.id from (select * from pokemon where level>50)a;
#将查询结果集设置别名,然后将结果集作为表进行查询,
2、insert插入
子查询还可以用在insert语句中,子查询的结果集可以通过insert语句插入到其它表中。
create table pm2 like pm;
#复制pm表的表结构(这样克隆,约束也会克隆)
insert into pm2 select * from pm where name in (select name from info where level>1);
#将查询出来的内容,插入到pm2表中
3、update修改
update语句也可以使用子查询,update内的子查询,在set更新内容时,可是单独一列,也可以是多列。
update pm set level=50 where name in(select name from pm2 where level=45);
#将等级为45的名字改为等级为50
注意:(子语句中的表不能与主语句表重复,如果是针对相同表操作,那么子语句中可以进行省略)。
4、delete删除
----操作不同的表-----
delete from pm where name in (select name from pm2 where level>69);
----操作相同的表-----
delete from pm where name in (select name where level<50);
5、exists布尔输出
exists 关键字在子查询时,主要用于判断子查询的结果集是否为空,如果不为空。则返回true,反之返回false。
注意: 在使用exists时,当子查询有结果时,不关心子查询的内容,执行主查询操作,当子查询没有结果时,则不执行主查询操作。子查询只是作为布尔的输出。
select count(*) from pokemon where exists(select id from pokemon where level=10);
#子语句执行失败,输出为false,那么主语句不会进行执行,默认输出0
select count(*) from pokemon where exists(select id from pokemon where level=50);
#子语句执行成功,输出为true,那么主语句会执行,输出info表的行数
三、MySQL视图
视图是一张虚拟的表,这张虚拟表中不包含真实数据,只是做了真实数据的映射。
功能:
- 简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
- 本质而言,视图是一种select(结果集的呈现)
注:视图适合于多表浏览时使用,不适合增、删、改
1、视图与表的区别
- 视图是已经编译好的sql语句,而表不是
- 视图没有实际的物理记录,而表有
- 表占用物理空间而视图不占用物理空间
- 视图只是逻辑概念的存在,表可以及时对它进行修改,但视图只能有创建的语句来修改
- 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。
- 从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构
- 表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表
- 视图的建立和删除只影响视图本身,不影响对应的基本表。(但是更新视图数据,是会影响到基本表的)
2、视图与表的联系
区别
- 视图是已编译好的sql语句。而表不是
- 视图没有实际的物理空间,而表有
- 表示只用物理空间,而视图不占物理空间,视图只是逻辑概念的存在,表可以及时对他进行修改,mysql5.7 通过更改视图也可以直接更改表数据
- 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些sql语句的集合。从安全角度来说,视图可以不给用户接触数据表,从而不知道结构。
- 表属于全局模式中的表,是实表,视图属于局部模式的表,是虚表。
联系
- 视图(view)是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有数据行)都是来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
3、单表创建视图
语法格式:create view 视图表名 as select * from 表名 where 条件;
create view v_pokemon as select * from pokemon where level <70;
#创建一个视图表,视图内容为pokemon表中等级小于70的记录(动态监控主表)
select * from v_pokemon;
#查看视图表
show table status\G
查看表状态
desc v_pokemon;
#查看表结构
4、多表创建视图
现在有两个表,需要频繁查看其中数据,那就可以使用视图的方式将要看的内容生成一个视图,要查看时,直接查看视图内容即可。
create view v_pm(id,name,特性,属性,level) as select pm.name,pm.特性,pm.level,pokemon.n.id,pokemon.属性 from pm,pokemon where pm.name=pokemon.name;
#创建视图,将pm表和pokemon表中name相同的指定记录映射到视图v_pokemon中
5、修改视图或原表内容
当数据发生变化时,若数据与之前创建视图表时的关联条件不一致时,视图表的数据将会发生改变