Mysql_高阶语句

实验准备

• 创建表

mysql> use school;

create table info(
 id int(10) not null,
 name varchar(20),
 score decimal(5,2),
 adress varchar(20),
 hobby varchar(10));

insert into info values(2,'李四','90','上海','2');
insert into info values(3,'王五','60','南京','4');
insert into info values(4,'赵六','99','苏州','5');
insert into info values(5,'赵七','100','广州','3');
insert into info values(6,'赵八','10','珠海','3');


 select * from info;

一.按关键字排序

使用select语句可以讲需要的数据从Mysql数据库中查询出来，
如果对查询的结果进行排序，可以使用 ORDER BY 语句来完成排序，
并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，可以针对多个字段

• 使用ORDER BY语句来实现排序
• 排序可针对一个或者多个字段
• ASC：升序，默认方式排序
• DESC：是降序

1）单字段排序

1.降序排序和升序

降序：DESC

DESC 是按降序方式进行排列，ORDER BY前面可以使用 where子句对查询结果进一步过滤

 select * from info;
select id,name,hobby from info order by hobby desc;

ORDER BY前面可以使用 where子句对查询结果进一步过滤

select id,name,hobby from info where hobby>=3 order by hobby;

默认升序：ASC

ASC是按照升序进行排序的，是默认的排序方式，即ASC可以省略。
select语句中如果没有具体的排序方式，默认按ASC方式进行排序

select id,name,hobby from info order by hobby asc;
或
select id,name,hobby from info order by hobby;

2）多字段排序

order by 之后的参数，使用“，”分割，优先级是按先后顺序而定。
order by 之后的一个参数只有在出现相同的数值，第二个字段才有意义

select id,name,hobby from info order by hobby desc,id desc;

2. or / and (或/且）

SELECT "栏位" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;

select * from info where score >70 and score <=90;

select * from info where score >70 or score <=90;

1） 嵌套/多条件查询

select * from info where score >70 or (score >75 and score <90);

3.查询不重复记录

• distinct 必须放在最开头
• distinct 只能使用需要去重的字段进行操作
• distinct去重几个字段时，含义是：几个字段同时重复才会被过滤

select distinct 字段 from 表名;
select distinct hobby from info;

4.对结果进行分组

通过SQL查询出来的结果，可以对其进行分组，分组的时候可以按一个或多个字段对结果进行分组处理

• 使用group by 来实现分组
• 通常结合聚合函数一起使用
• 可以按一个或多个字段对结果进行分组

 select 字段，聚合函数 from 表名，（where 字段名（匹配）数值）group by 字段名；


select * from info;

 select count(name),hobby from info group by hobby;

select count(name),hobby from info group by hobby order by count(name) desc;

5.限制条目结果

在使用 mysql select 语句进行查询时，结果集返回的是所有匹配的记录，
有的时候仅需要返回第一行或者前几行，需要用到limit子句

• 只返回select查询结果的第一行或前几行
• 使用limit语句限制条目

select * from info order by id desc limit 3,4;

 select * from info order by id limit 3,4;

6.设置别名

在mysql 查询时，当表的名字比较长或者表内某些字段比较长时，
为了方便书写或者多次使用相同的表，可以给字段列或者表设置别名。
使用的时候直接使用别名，简洁明了，增强可读性

• 对复杂的表进行查询的时候，别名可以缩短查询语句的书写
• 用于多表相连查询的时候（通俗易懂、剪短sql语句）

列别名：
select 列名  as 设置名称，列名 as 设置名称 from 表名；
例：
select name as 姓名,adress as 地址 from info;

表数据别名设置：

select 字段 as 列名，字段 as 列明 from 表名 as i;

例：
select i.name as 姓名,i.score as 成绩 from info as i;

7.连接语句

此处AS的作用：
1.创建一个新表tnp.定义表结构，插入表数据（与info表相同）
2.约束没有被“复制”过来，主表设置了主键，那么附表，default上会默认0

AS作为连接语句
create table tnp as select * from info;

也可以加入where判断语句：
 create table info1 as select * from info where score > 60;

8.通配符

主要用于替换字符串中的部分字符，通过部分字符的匹配将相关的结果查询出来
通常通配符都是跟LIKE（模糊查询）一起使用的，并协同WHERE子句共同来完成查询任务

常用的通配符有两个：

• %：百分号表示零个、1个或者多个字符
• _:下划线表示单个字符

模糊查询‘%’示例：
select * from where name like 'l%';

模糊查询’_'示例：
select * from info where name 

结合使用示例：
select * from info where name like 'l_%_';

9.子查询

子查询也被称为内查询或者嵌套查询，是指再一个查询语句里还嵌套着另一个查询语句
子查询是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询规律

主语句：select name,score from info where id
子语句：select id from info where score > 80
in:将主表和子表关联起来的作用（结果集）


select name,score from info where id in (select id from info where score > 80);

示例：查询info 表id为1，3，5，7的数据（通过子查询方式）

单表查询方式：

select * from info where (1,3,5,7);

• 子查询方式

create table num(
 id int(4));

select * from num;
 select * from info where id in ( select id from num);

子查询可以再select 语句中使用，在insert 、update 、delete中使用
支持多层嵌套
in 语句是用来判断某个值是否在给定的集合内（结果集），in往往和select 搭配使用
可以使用not in 表示对结果集取反

10.子查询—别名as

select id,name from info;
 select id from (select id,name from info);
select a.id from (select id,name from info) a;

select * from 表名，此为标准格式，
而以上的查询语句，“表名”的位置其实是一个结果集，
mysql不能识别结果集，给结果集设置一个别名，
将结果集看作一张表，就可以查询数据了

11.子查询-exists

as number 将count 统计的结果做为number (列名）返回
exists:布尔值判断，后面内查询（返回结果集）是否成立
where：后面跟条件判断

exists :为了判断exists之后的条件是否成立，如果成立，则执行主语句匹配，
不成立，不会执行主语句查询
 count 为计数，sum 为求和，
 sum 结合exists 查询结果不成立，输出为null

 select count(*) as number from info where exists (select id from info where name='ll');

select sum(score) from info where exists (select * from info where name='1');

12.视图

数据库中的虚拟表，虚拟表中不包含真真实数据，只是做了映射
 动态保存结果集 
源数据改变，视图也随着改变
可以定义展示的条件
结果动态变化，可以给不同的人群 展示不同的视图
视图更加安全，不占用资源

1）创建视图

create view V_score as select * from info where score > 75;

2）查看视图表结构信息

show table status\G;

3）查看视图

select * from V_score;

4） 修改原表数据

update info set score='99' where name='lisi';

5)查看视图

select * from V_score;

13.NULL 值

 NULL值表示缺失的值，
  表中该字段没有值
创建表，限制某些字段不为空，
可以使用not null ,关键字，
 不使用则默认可以为空

• 向表内插入记录或者更新记录时，字段没有not null ,并且没有值，新记录的该字段将会被保存为null,
• null的字段是没有值的
• is null 可以判断某个字段是不是null值
• is not null可以判断不是null值

1）null值和空值的区别（空气和真空）

null值：表示没有值，但是占空间，长度为null
count()计算时，null值会忽略

空值：不占空间 长度为0，
count()计算时，空值会加入计算
空值使用“=”或者“<>"来处理（!=)
 is null ：无法判断空值。

null验证

• count() 计算时，null会忽略，空值加入计算

alter table info add column addr varchar(50);
select * from info;

null统计数量，检测null是否会被加入统计中

select count(addr) from info;

空值验证

update info set addr='' where name='lisi';

select * from info;

空值统计数量，检测空值是否会被添加到统计中

select count(addr) from info;