MYSQL 高阶语句

目录

1、排列查询

2、区间判断

3、对结果进行分组查询

4、limit和distinct

5、设置别名

通配符

6、子查询

 7、exists语句，判断子查询的结果是否为空

 8、视图表

9、连接查询

1. 内连接

2. 左连接

3. 右连接

---

create table info (
id int primary key,
name varchar(10)  not null,
score decimal(5,2),
address varchar(20),
hobbid int(5)
);INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('1', '高', '92', '南京西路', '12');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('2', '徐', '93', '北京西路', '10');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('3', '杨', '85', '云南西路', '11');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('4', '沈', '90', '福建北路', '13');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('5', '李', '84', '湖南北路', '14');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('6', '林', '85', '天津西路', '15');
INSERT INTO `xy104`.`info` (`id`, `name`, `score`, `address`, `hobbid`) VALUES ('7', '杰', '82', '江苏西路', '16');

1、排列查询

select id,name from info order by score; 
#我们查询的是name，按照成绩实现默认升序的操作
select id,name from info order by score desc; 
#从大到小进行排序，降序select name,score from info where address='天津西路' order by id;  
#查找相同字段select name,score from info order by hobbid,id;
#第一个字段必须要有相同的值，第二个字段才会有意义。#查询ID,姓名，成绩，根据爱好都是10，按照id的进行降序排列:
select id,name,score from info where hobbid=10 order by id desc;

2、区间判断

根据where的条件，来对数据进行逻辑的区分
and or
and表示逻辑且，and的所有条件都要为真。
or表示逻辑或，只要有一个条件满足即为真。

select * from info where score > 80 and score <=90;select * from info where score <80 or score > 90;区间嵌套:
select * from info where score > 80 and ( score > 90 and score < 95);

3、对结果进行分组查询

group by 语句group by都是和聚合函数一起使用的。

count()统计行数
sum()求和
avg()求平均值
max()最大值
min()最小值
select count(name),hobbid from info group by hobbid;
1、不要使用聚合函数的字段来进行分组
2、要有多个字段，按照非统计的字段来进行分组
3、对聚合函数的结果进行过滤，使用having语句

select avg(score),hobbid from info group by hobbid;
#按照hobbid分组查询
select avg(score),hobbid from info group by hobbid having avg(score)> 85;
#求聚合函数的平均值用having

CREATE TABLE `test` (`id` int NOT NULL,`name` varchar(10) NOT NULL,`score` decimal(5,2) DEFAULT NULL,`address` varchar(20) DEFAULT NULL,`hobbid` varchar(10) DEFAULT NULL,`sex` varchar(5) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('1', '高', '92', '南京西路', '足球', ' 男');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('2', '徐', '93', '南京西路', '羽毛球', '女');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('3', '杨', '85', '云南西路', '乒乓球', '男');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('4', '沈', '90', '福建北路', '乒乓球', '男');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('5', '李', '84', '天津北路', '乒乓球', '女');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('6', '林', '85', '天津北路', '冰球', '男');
INSERT INTO `xy104`.`test` (`id`, `name`, `score`, `address`, `hobbid`, `sex`) VALUES ('7', '杰', '82', '江苏西路', '冰球', '女');#1、先根据where条件过滤出成绩大于等于80分然后查询姓名，性别，按照性别来进行分组，统计姓名。
select count(name),sex from jx1 where score >= 80 group by sex;#2、求出男生组和女生组平均成绩
select avg(score),sex from jx1 group by sex;#3、分别统计处男生和女生组的最大成绩和最小成绩
select max(score),min(score),sex from jx1 group by sex;#4、根据地址进行分组，统计平均成绩大于85分的地址
select avg(score),address from jx1 group by address having avg(score)> 85;

4、limit和distinct

select * from test
只显示前三个最高的成绩。
select * from test order by score desc limit 3;

5、设置别名

select name as 姓名,score as 成绩 from test;
#as:就是用来起别名的命令，表名和列名过长的时候可以使用别名进行替代select name 姓名,score 成绩 from info;
#尤其是在多表联查时，可以不用申明表名
select a.hobbid,a.address,a.sex from test a,info b where a.name=b.name;#创建表时，根据另一张表的结果，直接创建，主键的约束无法继承
create table test2 as select * from test where score >90;
select * from test2;desc test2;
#查看结构

通配符

%表示0个，1个，多个字符
_下划线表示单个字符
通配符一般是和1ike一起使用，并且是配合where条件进行过滤
select id,name from jx1 where name like '_';

6、子查询

内查询或者是嵌套查询，就是在查询语句当中又嵌套着另外一个select。
先查询子语句,然后把子语句的结果传给主语句进行执行
子查询的表可以是同一张表，也可以是不同的表

select where (select)select name,score from test where id in (select id from test where score > 85);
#前后的条件要一致select name,score from test where id not in (select id from test where score > 85);
#多表联查不要超过三张!更新info表，设置成绩的值是65，根据子查询的语句，只修改地址是由南京的记录的值。
update test set score=65 where address in (select address from info where address like '%南京%');
#不能 查自己 改自己。

 7、exists语句，判断子查询的结果是否为空

select count(*) from test where exists(select id from test where score > 90);
#不指定字段名称时。可以不使用group by
判断返回的结果是真还是假:
select count(*)from info;

 8、视图表

视图是一个虚拟表，数据基于检索的查询结果
把复杂的查询语句，简单化的呈现给用户。
查询视图就可以获取数据，避免找到真正的表，提高了数据安全。

create view test6 as select * from test1 where score >= 90;
select * from test6;
drop view test2;  #删除视图

view和table之间的区别
存储方式，表都是实际数据，保存在硬盘，视图存储的不是数据行，而是结果的集合
数据更新，更新表可以更新视图，更新视图也可以更新表，一般情况下，视图仅仅用于展示数据
占用空间，表是实际空间，视图不占用数据库的空间，就是一个的结果。
注：视图表的主要作用就是通过把复杂的查询语句简化的一个查询集合

9、连接查询

把两个或者三个表的记录行结合起来，基于这些表之间共同的字段，进行数据的拼接，首先确定一个主表的结果集(主表的列)，然后把其他表的行进行选择性的连接到主表的结果上。

1. 内连接

结合两个表之间基于一个字段或者多个字段之间，将两个表中结果进行组合。

create table test1 (
a_id int(11) default null,
a_name varchar(32) default null,
a_level int(11) default null);create table test2 (
b_id int(11) default null,
b_name varchar(32) default null,
b_level int(11) default null);insert into test1 values (1,'aaaa',10);
insert into test1 values (2,'bbbb',20);
insert into test1 values (3,'cccc',30);
insert into test1 values (4,'dddd',40);insert into test2 values (2,'bbbb',20);
insert into test2 values (3,'cccc',30);
insert into test2 values (5,'eeee',50);
insert into test2 values (6,'ffff',60);select a.a_id,a.a_name from test1 a INNER JOIN test2 b on a.a_name=b.b_name;

2. 左连接

左外连接，在from之后使用left join 或者 LEET OUTER JOIN 用为键字来匹配,
左连接以左侧表为基础，接受左表所有的行，并用这些行，与右侧表一起参与，
显示坐标以及右表符合条件的行，不满足的显示为nu11
select * from test1 a left join test2 b on a.a_name=b.b_name;
left左边即为左侧表

3. 右连接

右外连接，在from之后使用right join 或者 right OUTER JOIN 用为键字来匹配,
右连接以右侧表为基础，接受右表所有的行，并用这些行，与左侧表一起参与，
显示坐标以及左表符合条件的行，不满足的显示为nu11
select * from test1 a right join test2 b on a.a_name=b.b_name;
right右边即为右侧表