MySQL：基本操作进阶（一）

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中