Mysql高阶语句

1.使用select语句，用order by来进行排序

asc：升序排列 默认就是升序，可以不加

desc：降序排列，需要添加

select id,name from info order by id; #升序

select id,name from info order by id desc; #降序

1.1 order by 结合where条件进行过滤

select name,score from info where address='南京西路' order by score desc;

查id 姓名 成绩 根据性别=v 按照id进行降序排序

select id,name,score from info where sex='女' order by id desc;

select id,name,score from info where sex='女' order by score desc,id desc;

只有第一个参数出现相同值时，第二个参数才会按照要求排序

2. 区间判断查询和去重查询

and /or

且 或

select * from info;

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

SELECT * from info where score > 80 or score < 90;

2.1 嵌套条件

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

嵌套条件，满足性别是男，然后进行筛选成绩 70 -90

select * from info where sex='男' and (score > 70 and score < 90);

2.2 去重查询

select distinct address from info;

根据address去重，然后过滤出成绩=90且性别是男

select distinct address,name,score,sex from info where score=90 and sex='男';

3.分组查询 group by 语句

一般是结合聚合函数一块使用。

count() 统计有多少行

sum()列的值相加，求和

avg()列的值求平均值

max()过滤出列的最大值

min()过滤出列的最大值

分组的时候可以按照一个字段，也可以按照多个字段对结果进行分组处理

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

根据where条件筛选，score>=80

select count(name),sex,score from info where score >=80 GROUP BY sex;

根据地址来分组,来求和

SELECT sum(score),address from info GROUP BY address;

算出男生女生的平均成绩

select avg(score),sex from info GROUP BY sex;

3.1 group by 实现条件过滤 having

select avg(score),address from info group by address having avg(score) >60;

根据按照地址分组，求成绩的平均值，然后>50 按照id的降序排列。

select avg(score),address,id from info group by address having avg(score) >50 order by id desc;

统计name的行数，计算出学生的个数，把成绩也查出来，然后安装统计出来的学生个数，升序排序，按照地址分组，

学生的成绩>=70

select count(name),address,score from info group by address having score >=70 ORDER BY count(name);

按照性别分组，求出男生和女生的最大成绩，最大成绩是否超过75分，

select max(score),sex from info GROUP BY sex having max(score) > 75;

使用聚合函数必须要加group by 分组的条件，要选用有多个重复值的列，过滤条件要用having

4. limit 限制输出的结果记录，查看表中的指定行

elect * from info limit 3; 只看前三行

select * from info limit 1,4; 2-5行

select * from info limit 5,3; 6-8行

select * from info order by id desc limit 3; 快速的查看后几行

5.通配符

通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关的结果查询出来

通配符和like一起使用，使用where语句一起来完成查询

%：表示0个，1个，或者多个

_:表示单个字符

select * from info where address like '山%' 已山为开头

select * from info where address like '%路' 已路为结尾

select * from info where address like '%西%' 包含西

以山开头，匹配后面两个字符

select * from info where address like '山%__';

6.别名

查询时，表的名字或者字段名太长，可以使用别名来进行替代，方便书写，增加可读性

SELECT name as 姓名,score as 成绩 from info;

SELECT name 姓名,score 成绩 from info;

create table test as select * from info; 使用as复制表，约束不会被复制过来

select * from test;

可以给表起别名，但是要注意别名不能和数据库中其他的表明冲突。

列的别名在结果中可以显示，但是表的别名在结果中没有显示，只能用于查询。

7.子查询：内查询，嵌套查询

括号里面的查询语句会于主查询语句执行。然后再把子查询的结果作为条件返回给主查询条件进行过滤

子查询返回的结果只能是1列

where 条件 in 什么 子查询的列就是什么

SELECT id,name,score from info where id in (SELECT id from ky32);

SELECT id,name,score from info where name in (SELECT name from ky32);

多表联查，不要超过三张表

子查询语句还可以用在insert update delete

insert into test SELECT * from info where id in (select id from info where sex='女');

插入数据，要求按地址，包含南京插入到test;

insert into test select * from info where address in (select address from info where address like '%南京%')

update info set score=50 where id in (select id from ky32 where id =2);

修改info表score =100 根据子查询语句 not in 子查询的语句 id>1

update info set score =100 where id not in (select id from ky32 where id > 1 )

delete from info where id in (select id where score > 80 )

8.EXISTS关键字

关键字是子查询时，主用用于判断子查询的结果集是否为空，不为空，返回true

为空 为flase

根据info 表 查询出大于80分的同学，然后统计有多少个

select count(*) from info a where exists(select id from info where score > 80 and info.id =a.id)

9.视图

mysql当中的视图view

视图在mysql当中是一个虚拟表。基于查询结果得出的一个虚拟表

在工作当中，我们查询的表未必是真表。 有可能就是视图表。有可能是基于真表查询结果的一个虚拟表

可以简化复杂的查询语句，隐藏表的细节提供安全的数据访问

创建视图表可以是一张表的结果集，也是多个表共同的查询的结果集

9.1 视图表和基本表的区别

1.存储方式不一样的，真表存储实际数据，真正写在磁盘当中的。视图不存储任何数据仅仅是一个查询的结果集的虚拟表间的

2.表可以增删改查，但是视图一般情况只能用于查，展示数据

3.占用空间，真表真实占用空间，视图不占用数据库空

查看视图表

show full tables in kgc where table_type like 'view'

删除

drop view 视图表

根据info表的id，name，score 在加上test01的age 创建

create view v_info(id,name,score,age) as

select a.id,a.name,a.score,b.age from info a ,test01 b

where a.name=b.name;

select * from v_info;

update info set score=90 where name='胡晋' #原表数据变化，视图表同步更新

update v_info set score=100 where name='王宇杭' #修改了视图表，源表的数据会发生变化，一般情况我们是不对视图进行改的操作。

真表占了80% 视图适用于安全性要求比较高的场景，对外访问，基本都是视图

10.连接查询

内连接：

是把两张表或者多张表（3张），同时符合特定条件的数据记录的组合

一个或者多个列相同值才会有查询的结果 inner join

select * from test01 a inner join info b on a.name=b.name#on 后面跟 条件语句

左连接

左外连接 在 left join关键字来表示，在左连接当中，左侧表是基础表，，接收左边的所有行，然后和右表（参考表）记录进行匹配。

匹配左表当中的所有行，以及右表中符合条件的行 匹配的记录，不匹配的记录null值

select * from test01 a left join info b on a.name=b.name

右连接

右外连接，right join 已右侧表为基础表 接收右侧表的所有记录。匹配的记录，不匹配的记录null值

select * from test01 a right join info b on a.name=b.name;